Migrate SpringCloude microservices, install and configure data storage warehouse MySQL

Install and configure data storage warehouse MySQL 1. Introduction to MySQL 2. MySQL features 3. Install and configure MySQL 4. Import data in MySQL database 5. Authorize the MySQL database

1. Introduction to MySQL


MySQL is a secure, cross-platform, and efficient database system that is closely integrated with mainstream programming languages ​​such as PHP and Java. The database system was developed, released and supported by MySQL AB in Sweden, and was established in 1995 by the original developers of MySQL, David Axmark and Michael Monty Widenius. The symbol of MySQL is a dolphin named Sakila, which represents the speed, ability, precision, and excellent nature of the MySQL database.

MySQL logo:

At present, MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, high speed, and low total cost of ownership, especially its open source characteristics, many companies have adopted MySQL databases to reduce costs. The MySQL database can be regarded as one of the fastest running SQL language databases. In addition to having many functions that other databases do not have, MySQL database is also a completely free product. Users can download MySQL database directly through the Internet without having to pay any fees.

2. MySQL features


1) Powerful     MySQL provides a variety of database storage engines. Each engine has its own strengths and is suitable for different applications. Users can choose the most suitable engine to get the highest performance. It can handle the high-intensity search Web with more than hundreds of millions of daily visits. Site. MySQL5 supports transactions, views, stored procedures, triggers, etc.    2) Support cross-platform     MySQL supports at least 20 development platforms, including Linux, Windows, FreeBSD, IBMAIX, AIX, FreeBSD, etc. This makes the program written under any platform can be transplanted without any modification to the program.    3) Fast running speed     High speed is a distinguishing feature of MySQL. In MySQL, extremely fast B-tree disk table (MyISAM) and index compression are used; through the use of optimized single scan and multiple connections, connections can be realized extremely quickly; SQL functions are implemented using highly optimized class libraries and run extremely fast .    4) Support object-oriented     PHP supports mixed programming. Programming methods can be divided into three methods: pure object-oriented, purely process-oriented, and mixed with face-to-face object and process-oriented.    5) High security     Flexible and secure authority and password system, allowing basic host authentication. When connecting to the server, all password transmissions are encrypted to ensure the security of the password.    6) Low cost     MySQL database is a completely free product, and users can download it directly through the Internet.     7) Support various development languages      MySQL provides support for various popular programming languages ​​and provides them with many API functions, including PHP, ASP.NET, Java, Eiffel, Python, Ruby, Tcl, C, C++, Perl, etc.       8) Large database storage capacity       The maximum effective table size of a MySQL database is usually determined by the operating system's file size limit, not by MySQL's internal limits. The InnoDB storage engine stores InnoDB tables in a table space. The table space can be created by several files. The maximum capacity of the table space is 64TB, which can easily handle large databases with tens of millions of records.      9) Support powerful built-in functions       PHP provides a large number of built-in functions, covering almost all functions in web application development. It has built-in functions such as database connection and file upload. MySQL supports a large number of extended libraries, such as MySQLi, which can provide convenience for the rapid development of Web applications.

3. Install MySQL


The projects deployed later in k8s need to use the database, and some data used need to be stored in the database, because the projects deployed later are order items and inventory databases, which need to be stored in MySQL. These modules are all Need to store data.

Download and install MySQL

[[email protected] ~]# wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm[[email protected] ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm[[email protected] ~]# yum install mysql-server -y

Start MySQL

[[email protected] ~]# chown mysql:mysql -R /var/lib/mysql[[email protected] ~]# systemctl start mysqld[[email protected] ~]# systemctl  status mysqld?.mysqld.service - MySQL Community Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)   Active: active (running) since Thu 2021-06-03 09:21:56 CST; 16min ago  Process: 10478 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)  Process: 10409 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Main PID: 10477 (mysqld_safe)   CGroup: /system.slice/mysqld.service           ?..10477 /bin/sh /usr/bin/mysqld_safe --basedir=/usr           ?..10645 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var...

set password

#mysql 安装成功后,默认的 root 用户密码为空,你可以使用以下命令来创建 root 用户的密码,密码设置成 111111 [[email protected] ~]# mysqladmin -u root password "111111"Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -h 127.0.0.1 -u root -P 3306 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.6.51 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Create database tb_order, tb_product, tb_stock

mysql> create database tb_product;Query OK, 1 row affected (0.00 sec) mysql> create database tb_stock;Query OK, 1 row affected (0.00 sec) mysql> create database tb_order;Query OK, 1 row affected (0.00 sec)

4. Import data in Mysql database


Upload the corresponding sql statement to the root directory of the mysql machine. The sql files are order.sql, product.sql, stock.sql, and import them as follows:

mysql> use tb_orderDatabase changedmysql> source /mysql/order.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)  mysql> use tb_stockDatabase changedmysql> source /mysql/stock.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec) mysql> use tb_productDatabase changedmysql> source /mysql/product.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)  mysql> show tables;+----------------------+| Tables_in_tb_product |+----------------------+| product              |+----------------------+1 row in set (0.00 sec) mysql> desc product;+--------------+--------------+------+-----+---------+----------------+| Field        | Type         | Null | Key | Default | Extra          |+--------------+--------------+------+-----+---------+----------------+| id           | int(11)      | NO   | PRI | NULL    | auto_increment || product_name | varchar(100) | YES  |     | NULL    |                || price        | double(15,3) | YES  |     | NULL    |                |+--------------+--------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

5. Authorize the MySQL database


Other machines do not allow MySQL connections. Now, several segments are allowed to be accessible. One is that the pod can access mysql, the other is that the node where the pod is located can access, and the last one is to let go of the permissions on all network segments. This is an experiment, so let go

[[email protected] ~]# kubectl get pod -o wide -n ingress-nginxNAME                             READY   STATUS    RESTARTS   AGE    IP            NODE    NOMINATED NODE   READINESS GATESnginx-ingress-controller-hj7pn   1/1     Running   0          134m   192.168.0.4   node2   <none>           <none>nginx-ingress-controller-km8gv   1/1     Running   0          134m   192.168.0.3   node1   <none>           <none> [[email protected] ~]# kubectl get pod -o wideNAME                                     READY   STATUS    RESTARTS   AGE   IP             NODE    NOMINATED NODE   READINESS GATESnfs-client-provisioner-cb667d665-x9nhf   1/1     Running   0          13d   10.233.90.16   node1   <none>           <none>
[[email protected] ~]# mysql -h 192.168.0.1X -u root -P 3306 -pEnter password: ERROR 1130 (HY000): Host '192.168.0.1X' is not allowed to connect to this MySQL server  mysql> grant all on *.* to 'root'@'10.233.%.%' identified by '111111';Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to 'root'@'192.168.%.%' identified by '111111';Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to 'root'@'%' identified by '111111';Query OK, 0 rows affected (0.00 sec) mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)