Introduction to master-slave replication
1. Master slave replication is implemented based on binlog
2. When new operations occur in the main database, binlog will be recorded
3. Obtain the binlog of the main library from the library for playback
4. The master-slave process is asynchronous
Construction of master-slave replication
Prerequisites for master-slave replication
1. 2 or more database instances
2. The main library needs to open binary logs
3,server_ Different IDS should be used to distinguish different nodes
4. The master database needs to establish a dedicated replication user
5. The slave database restores some data of the master database by backing up the master database
6. Manually tell the library to copy some information (ip port user passwd, binary log starting point)
7. Open a dedicated replication thread from the library
build
- Prepare multiple instances
[root@test01 3307]# cd /data01/3307/data/ [root@test01 data]# ll total 122920 -rw-r-----. 1 mysql mysql 56 Dec 1 20:52 auto.cnf -rw-r-----. 1 mysql mysql 356 Dec 1 21:21 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Dec 6 20:53 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Dec 6 20:53 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Dec 1 20:52 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 Dec 6 20:53 ibtmp1 drwxr-x---. 2 mysql mysql 4096 Dec 1 20:52 mysql drwxr-x---. 2 mysql mysql 8192 Dec 1 20:52 performance_schema drwxr-x---. 2 mysql mysql 8192 Dec 1 20:52 sys -rw-r-----. 1 mysql mysql 6 Dec 6 20:53 test01.pid [root@test01 data]# rm -rf * [root@test01 data]# cd .. [root@test01 3307]# ls data my.cnf mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.log mysql.sock mysql.sock.lock [root@test01 3307]# rm -rf mysql-bin.* [root@test01 3307]# mysqld --initialize-insecure --user=mysql --basedir=/aplication/mysql --datadir=/data01/3307/data [root@test01 3307]# systemctl start mysqld3307 [root@test01 3307]# mysql -S /data01/3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> quit Bye [root@test01 3307]# mysql -S /data01/3307/mysql.sock -e "select @@port" +--------+ | @@port | +--------+ | 3307 | +--------+
- Check configuration file
Binary log on
Two node server_id
- Master library create replication user
[root@test01 3307]# mysql -uroot -p1 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.26-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> grant replication slave on *.* to repl@'192.168.184.%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select user,host from mysql.user; +---------------+---------------+ | user | host | +---------------+---------------+ | repl | 192.168.184.% | | root | 192.168.184.% | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+---------------+ 5 rows in set (0.00 sec)
- Back up the master library and restore to the slave library
#Main library backup data [root@test01 3307]# mysqldump -uroot -p1 -A --master-data=2 --single-transaction -R -E --triggers > /tmp/full.sql #Import data from library ```bash [root@test01 3307]# mysql -S /data01/3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.26-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source /tmp/full.sql
- Tell me to copy key information from the library
Login from library
help change master to --see change master to usage
CHANGE MASTER TO
MASTER_HOST='192.168.184.128', --Host name of main library
MASTER_USER='repl', --Primary library user name
MASTER_PASSWORD='123', --Password for the primary library user name
MASTER_PORT=3306, --Main library port
MASTER_LOG_FILE='master2-bin.004',
MASTER_LOG_POS=449,
--MASTER_LOG_FILE and MASTER_LOG_POS You can see from line 22 in the backup file
--For example:
--[root@test01 3307]# sed -n '22p' /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000004', MASTER_LOG_POS=449;
MASTER_CONNECT_RETRY=10; --10 reconnections after master-slave disconnection
Actual operation:
[root@test01 3307]# mysql -S /data01/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.184.128',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master2-bin.004',
-> MASTER_LOG_POS=449,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
#Start replication thread (IO, SQL)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
6. Check master-slave replication status
db01 [mysql]>show slave status \G Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_IO_Errno: 0 Last_IO_Error: --Detailed error information Last_SQL_Errno: 0 Last_SQL_Error: --Detailed error information
Principle of master-slave replication
Files involved in master-slave replication
Main library
binlog
From library
relaylog relay log
master.info master database information file
relaylog.info relaylog application information
Threads involved in master-slave replication
Main library:
binlog_dump Thread: DUMP_T
From library:
SLAVE_IO_THREAD: IO_T
SLAVE_SQL_THREAD: SQL_T
How master-slave replication works
Master slave replication monitoring
[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave status \G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
##########################Information about master library(master.info)###########
Master_Host: 192.168.184.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql_bin.000005 #binlog information of main database
Read_Master_Log_Pos: 154 #binlog information of main database
###############From library relay Related to application information(relay.info)############
Relay_Log_File: test01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000005
########################From library thread running state (troubleshooting)##############
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
###################Filter replication related information###########################
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
###################Slave library delay master library time (s)#####################
Seconds_Behind_Master: 0
########################Delay slave Library############################
SQL_Delay: 0
SQL_Remaining_Delay: NULL
######################GTID Copy status information about################
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Master slave replication failure
From library:
-
IO_T thread failure
- Connect to main library
Network, connection information error or change, firewall, upper limit of connections
Troubleshooting scheme:
1. Log in to the primary database manually using the replication user
solve:
1,stop slave
2,reset slave all；
3. change master to update information again
4,start slave- Request binlog
The binlog is not opened in the main database. The binlog is damaged and does not exist. The main database executes reset master
The solution to the reset master of the master database:
When the master database views the binlogr log, execute show master status;
Execute from library:
1,stop slave
2,reset slave all；
3. change master to update information again
4,start slave- Store binlog to relaylog
Viewing relaylog permissions and other issues
Restart the master-slave, stop slave and start slave -
SQL_T thread failure
- The relay log is corrupted
Replay relaylog
This is mainly caused by SQL execution failure
Scene simulation: the test database is established in the slave database, and then the test database is established in the master database. The table is established in the test database of the master database. It is found that the slave database does not get the table
Problem Description: it is found that the test table already exists when synchronizing the master library from the library
resolvent:
Delete test table from library
Grasp a principle and try to take the master database as the standard and reverse the operation
Or rebuild the master-slave
Master slave delay monitoring and causes
Main warehouse reasons
- binlog is not written in time
sync_binlog=1 optimization parameter, write to binlog in 1 second
- Dump by default_ T is serial transmission binlog
The amount of concurrent transactions is large due to dump_t is serial operation, resulting in slow delivery of logs
Solution:
GTID is required. Dump can be supported by Group commit_ T parallel
Reasons from the library
- Traditional replication
If the number of concurrent transactions in the master database is large, or large transactions occur, because the slave database is a single SQL thread, no matter how many logs are transmitted, only one transaction can be executed at a time
In version 5.7, there is an enhanced GTID, a new concurrent SQL thread mode (logical_clock) and MTS technology
- Great difference between master and slave hardware
- Master-slave parameter configuration
- The master-slave and slave database indexes are inconsistent
- Version difference
Monitoring of master-slave delay
Main library
Main library:
mysql> show master status \G
*************************** 1. row ***************************
File: mysql_bin.000005
Position: 154
From library
[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave status \G"
*************************** 1. row ***************************
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 154
From the aspect of Library
[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave status \G"
*************************** 1. row ***************************
#How much did you take
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 154
#How many were executed
Relay_Log_File: test01-relay-bin.000002
Relay_Log_Pos: 320
[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave status \G"
*************************** 1. row ***************************
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 154
Exec_Master_Log_Pos: 154
Relay_Log_Space: 528
#Compare Exec_Master_Log_Pos and Read_Master_Log_Pos looks at how many statements are executed to determine whether the delay is not completed and whether it is SQL_ Delay of T