linux--about MySQL in more detail, the master-slave configuration of the database, automatic backup and the use of read and write separation

1. Master-Slave Introduction
Data in the enterprise is a very important part of the database to store data many multi-use samples, but there is a hidden danger
• Database Data loss
• A database cannot guarantee quality of service when the number of database accesses is large
2. The role of master and slave
• Real-time disaster preparedness for failover
• Read and write separation, providing query services
• Backup to avoid impacting your business
3. Master-Slave form
• One master and one bundle
• Primary master replication
• One master multi-slave
• Multi-Master One bundle
• Joint level replication

Master-slave replication principle

master-slave replication steps

The main library logs all write operations to the binlog log (binary log) and generates a log dump thread that passes the binlog log to the I/O thread from the library.
Generate two threads from the library, one I/O thread, one SQL thread
The I/O thread requests the binlog of the main library and writes the obtained binlog log to the relay log file.
· SQl thread, will read the log in the relay log file, and parse it into a specific operation, to achieve the same operation of the master and slave, to achieve the purpose of the final data consistency

Master-Slave Replication configuration steps:
1. Ensure that the database is the same as the data in the primary database
2. Create a sync account in the main database to be licensed to use from the database
3. Configure the primary database (modify the configuration file)
4. Configuration from the database (modify the configuration file)

Configuration requirements:
Environment: Set up two MySQL servers, one as the primary server, one as a slave server, the master server for the write operation from the server to read operations

Configuration information
Primary database ip192.168.56.11 CENTOS7 has data
No data from database ip192.168.56.138 CENTOS7

Master-Slave synchronization settings
Firewall settings

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
[root@localhost ~]# sed -ri ‘s/(SELINUX=).*/\1disabled/g‘ /etc/selinux/config
[root@localhost ~]# setenforce 0

Download MySQL package in binary format

[root@localhost ~]# cd /usr/src/
[root@localhost src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# ls
debug  kernels  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

Create and group

[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql
[root@localhost src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
"mysql" -> "mysql-5.7.22-linux-glibc2.12-x86_64/"

Modify the genus/usr/local/mysql of the directory

[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 September   7 11:43 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/

Adding environment variables

[root@localhost local]# ls /usr/local/mysql
bin  COPYING  docs  include  lib  man  README  share  support-files
[root@localhost local]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh
[root@localhost local]# . /etc/profile.d/mysql.sh
[root@localhost local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

Set up a data storage directory

[root@localhost local]# mkdir /opt/data
[root@localhost local]# chown -R mysql.mysql /opt/data/
[root@localhost local]# ll /opt/
Total usage 0
Drwxr-xr-x. 2 mysql mysql 6 September 7 11:47 data
Drwxr-xr-x. 2 root root 6 March 26 2015 rh

Initializing the database

[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2018-09-07T03:48:13.790964Z 1 [Note] A temporary password is generated for root@localhost: n*Fsa&a+O1kN / Finally a temporary password will be generated

From: Database initialization, same as previous steps, so omit

[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2018-09-07T06:25:11.123940Z 1 [Note] A temporary password is generated for root@localhost: QR(XhNPpk6Qi

Build configuration File/

[root@localhost ~]# cat > /etc/my.cnf <<EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
EOF

/ / Configure the service startup script
[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# sed -ri ‘s#^(basedir=).*#\1/usr/local/mysql#g‘ /etc/init.d/mysqld
[root@localhost ~]# sed -ri ‘s#^(datadir=).*#\1/opt/data#g‘ /etc/init.d/mysqld

Start MySQL

[root@localhost ~]# /etc/init.d/mysqld start
[root@localhost ~]# ps -ef|grep mysql
[root@localhost ~]# ss -antl
LISTEN 0 80 :::3306 :::*

Change Password
Login with temporary password

[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Mysql>
/ / Set a new password
Mysql> set password = password(‘guohui123‘);
The above is the configuration of the two database servers, the configuration is the same 

Master://view backup of master and slave libraries

[root@localhost local]# mysql -uroot -pguohui123 -e ‘show databases;‘
Mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
Create a library, table
Mysql> use guohui
Database changed
Mysql> create table student (id int(11) NOT NULL, name varchar(100) NOT NULL,age TINYINT(3));
Query OK, 0 rows affected (0.02 sec)
Mysql> insert into student (id,name,age) value (1,‘tom‘,20);
Query OK, 1 row affected (0.05 sec)
Mysql> desc student;
+-------+--------------+------+-----+---------+--- ----+
Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+--- ----+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(3) | YES | | NULL | |
+-------+--------------+------+-----+---------+--- ----+
3 rows in set (0.01 sec)
Mysql> select * from student;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | tom | 20 |
+----+------+------+
1 row in set (0.00 sec)

Mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| guohui |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

Master://Full backup Main Library
The full master library needs to open a separate terminal, to add a read lock to the data, to avoid other people in the backup during the write data caused inconsistent data, and the lock must be completed after the backup to exit, Exit is unlocked

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

Master://Back up the main library and transfer the files to the slave library

[root@localhost ~]# mysqldump -uroot -pguohui123 --all-databases > /opt/all-20180907-1.sql
Mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /opt
All-20180907-1.sql data rh//transmission
[root@localhost ~]# scp /opt/all-20180907-1.sql root@192.168.56.138:/opt/
The authenticity of host ‘192.168.56.138 (192.168.56.138)‘ can’t be established.
ECDSA key fingerprint is SHA256:by
E1DjsPUpqjJssSP+RcbkM7+wcjdzMkjW3u09KrSB8.
ECDSA key fingerprint is MD5:c6:d2:a1:34:73:2f:66:c9:ac:db:ca:2b:7a:78:4f:75.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.56.138’ (ECDSA) to the list of known hosts.
Root@192.168.56.138‘s password:1 //Enter the root password here
All-20180907-1.sql 100% 783KB 19.2MB/s 00:00
Main :// contact lock table status
Mysql> quit
Bye

From://Restore a backup of the main library from the library and see which libraries are available to ensure consistency with the main library

/ / View the files sent by the master and the backup
[root@localhost ~]# ls /opt
All-20180907-1.sql data rh
Import files from the database
[root@localhost ~]# mysql -uroot -pguohui123 < /opt/all-20180907-1.sql
Mysql: [Warning] Using a password on the command line interface can be insecure.
/ / View the database
[root@localhost ~]# mysql -uroot -pguohui123 -e ‘show databases;‘
Mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| guohui |
| mysql |
| performance_schema |
| sys |
+--------------------+

Master://Create a sync account in the main database to be licensed to use from the database

Create database user guohui, password: guohui123, can only log in remotely at 192.168.56.138
Mysql> CREATE USER ‘guohui‘@‘192.168.56.138‘ IDENTIFIED BY ‘guohui123’;
Query OK, 0 rows affected (0.00 sec)

To grant permission to the user guohui, you can access all the tables of all the databases of the machine at 192.168.56.138 without password:
Mysql> GRANT REPLICATION SLAVE ON *.* TO ‘guohui‘@‘192.168.56.138’;
Query OK, 0 rows affected (0.00 sec)

Reload privileges:
Mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Mysql>

Master://Configure the primary database

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
Basedir = /usr/local/mysql
Datadir = /opt/data
Socket = /tmp/mysql.sock
Port = 3306
Pid-file = /opt/data/mysql.pid
Log-error = /opt/data/mysqld.log //error log
User = mysql
Skip-name-resolve
#
Log-bin = mysql_bin / / start the binlog log
Server-id = 2 // database server unique identifier, the server-id value of the main library must be larger than the library

start up
[root@localhost ~]# /etc/init.d/mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to ‘/opt/data/mysqld.log‘.
. SUCCESS!

View the main library status:

Mysql> show master status;
Mysql_bin, the name of the binary file written in the file
000001 version
Position Position 154
+------------------+----------+--------------+---- --------------+-------------------+
File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+---- --------------+-------------------+
| mysql_bin.000001 | 154 | | | |
+------------------+----------+--------------+---- --------------+-------------------+
1 row in set (0.00 sec)

From://configuration from Database

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
Basedir = /usr/local/
Mysqldatadir = /opt/data
Socket = /tmp/mysql.sock
Port = 3306
Pid-file = /opt/data/mysql.pid
Log-error = /opt/data/mysql.log
User = mysql
Skip-name-resolve
#replication
Server-id = 5 //Set the unique identifier from the library. The server-id value from the library must be less than the value of the main library.
Relay-log = mysql_relay_log //Start relay log relay-log startup

[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.Logging to ‘/opt/data/mysql.log‘.
SUCCESS!

From://Configure and start master-slave replication

Primary server specified by CHANGE MASTER TO
MASTER_HOST=‘192.168.56.11’, the specified primary server ip
MASTER_USER=‘guohui‘,
MASTER_PASSWORD=‘guohui123’, password
MASTER_LOG_FILE=‘mysql-bin.000001’, the name and version of the binary file
MASTER_LOG_POS=154; Location 154

Mysql> change master to master_host='192.168.56.11', master_user=‘guohui‘, master_password=‘guohui123’, master_log_file=‘mysql_bin.000001’, master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

Start the slave service from the service
Mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

From://view from server status

Mysql> show slave status \G
*************************** 1. row ******************** *******
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.56.11
                   Master_User: guohui
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql_bin.000002
           Read_Master_Log_Pos: 154
                Relay_Log_File: mysql_relay_log.000004
                 Relay_Log_Pos: 367
         Relay_Master_Log_File: mysql_bin.000002
              Slave_IO_Running: Yes // must be yes
             Slave_SQL_Running: Yes // must be yes
Mysql>

Test:
Main://Insert data into the database

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)

mysql> insert into student value (1,‘jerry‘,23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  1 | jerry |   23 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql>

From://View Test results

mysql> use guohui;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  1 | jerry |   23 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql>

This shows that the database of the master-slave function, build success

linux--about MySQL in more detail, the master-slave configuration of the database, automatic backup and the use of read and write separation