MySQL master-slave replication asynchronous replication
1, Master slave replication
1.1 what is master-slave replication
In fact, what is copied is the binary log of the master server. The slave server then operates again according to the log, so that it is the same as the data in the master server, but there will be a delay in time.
Cluster: many servers do the same thing
Ask the question here: how to solve the problem of delay?
Explanation of terms:
- Master: master server
- Slave: slave server
- Manager -- > master management location
- worker: work node
- Node: node, a node is a server
- Cluster: Cluster -- > many servers do the same thing
- Relay log: relay log
Talk about master-slave replication
Two mysql servers, one master and one slave
-
First of all, the master should open the binary log. When there is a change in data, the data will be stored in the binary log,
-
There is a log dump thread in the master. Once there is a change in the binary log, it will notify the I/O thread in the slave to read the data
-
After the I/o thread reads the data, it will write the data into the Relay log, and then the SQL thread in the slave will perform read and replay operations to achieve data consistency.
What is the role of the master info file?
Record the master's ip and connect to it
2, Asynchronous replication experiment of master-slave replication
Experimental steps
2.1 both the master and slave should install the same version of Mysql
Maste
[root@zkj-mysql ~]# mysql -V mysql Ver 14.14 Distrib 5.7.37, for linux-glibc2.12 (x86_64) using EditLine wrapper
Slave
[root@Slave ~]# mysql -V mysql Ver 14.14 Distrib 5.7.37, for linux-glibc2.12 (x86_64) using EditLine wrapper
2.2 first export the Mysql database in full, and then import it on the slave, so that the basic data from the servers on both sides is the same
# First, export the Mysql database in full [root@zkj-mysql ~]# mysqldump -u root -p 'Sanchuang123#' --all-databases > /backup/mysqlall_db.sql [root@zkj-mysql ~]# cd /backup/ [root@zkj-mysql backup]# ls all_da.sql mysqlall_db.sql zkj.sql # A secret free channel is established in advance to transfer the full backup sql file scp to the slave server [root@zkj-mysql backup]# scp mysqlall_db.sql 192.168.40.134:~/ mysqlall_db.sql 100% 203 247.6KB/s 00:00
# When you go to the slave, you can see that the full backup file of mysql has been received [root@Slave ~]# ls anaconda-ks.cfg mysqlall_db.sql # Import the full backup sql file on the slave server [root@Slave ~]# mysql -uroot -p'Sanchuang123#' < all_db.sql mysql: [Warning] Using a password on the command line interface can be insecure. # You can see that the basic data has been consistent root@(none) 10:01 mysql>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sc | | sys | | zengkaijie | | zkj | +--------------------+ 7 rows in set (0.02 sec)
2.3 make sure that the master is turned on Binary function
slave can also enable binary log or not, but the server needs to be specified_ id
master:
# binary log log_bin server_id = 1
slave:
server_id = 2
2.4 create an authorized user with copy permission on the master server, so that the slave can copy binary logs in the master server
root@(none) 10:17 mysql>grant replication slave on *.* to 'zeng'@'192.168.40.134%' identified by 'Sanchuang123#'; Query OK, 0 rows affected, 1 warning (0.00 sec)
2.5 add information of authorized users on the slave
root@(none) 10:24 mysql>CHANGE MASTER TO MASTER_HOST='192.168.40.134' ,
-> MASTER_USER='zeng',
-> MASTER_PASSWORD='Sanchuang123#',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='localhost-bin.000008',
-> MASTER_LOG_POS=6135;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
After the addition is successful, check the slave status and find that the two slave threads, I/O thread and SQL thread, are both closed, so you need to turn them on next
root@(none) 10:24 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.40.134
Master_User: zeng
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: localhost-bin.000008
Read_Master_Log_Pos: 6135
Relay_Log_File: Slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: localhost-bin.000008
Slave_IO_Running: No
Slave_SQL_Running: No
2.6 start the slave role
root@(none) 10:25 mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 10:25 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.40.134
Master_User: zeng
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: localhost-bin.000008
Read_Master_Log_Pos: 6135
Relay_Log_File: network-relay-bin.000001
Relay_Log_Pos: 324
Relay_Master_Log_File: localhost-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
After starting the slave role, it is found that both threads have succeeded.
2.7 verify whether the master-slave copied data can be synchronized
Create a new database zengkaijie1 and two tables t1 and t2 on the master machine
root@(none) 11:16 mysql>create database zengkaijie1; Query OK, 1 row affected (0.00 sec) root@(none) 11:16 mysql>use zengkaijie1 Database changed root@zengkaijie1 11:16 mysql>create table t1(id int,name varchar(10)); Query OK, 0 rows affected (0.01 sec) root@zengkaijie1 11:17 mysql>create table t2(id int,name varchar(10)); Query OK, 0 rows affected (0.00 sec) root@zengkaijie1 11:17 mysql>show tables; +----------------------+ | Tables_in_zengkaijie1| +----------------------+ | t1 | | t2 | +----------------------+ 2 rows in set (0.00 sec)
slave server: data found successfully synchronized
root@(none) 12:14 mysql>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | zengkaijie1 | | mysql | | performance_schema | | sc | | sys | | zengkaijie | | zkj | +--------------------+ 10 rows in set (0.00 sec) root@(none) 12:17 mysql>use zengkaijie1; 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 root@zengkaijie1 12:17 mysql>show tables; +----------------------+ | Tables_in_zengkaijie1| +----------------------+ | t1 | | t2 | +----------------------+ 2 rows in set (0.00 sec) root@zengkaijie1 12:18 mysql>show processlist; +----+-------------+-----------+------------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------------+---------+------+--------------------------------------------------------+------------------+ | 2 | root | localhost | zengkaijie1| Query | 0 | starting | show processlist | | 3 | system user | | NULL | Connect | 655 | Waiting for master to send event | NULL | | 4 | system user | | NULL | Connect | 428 | Slave has read all relay log; waiting for more updates | NULL | +----+-------------+-----------+------------+---------+------+--------------------------------------------------------+------------------+ 3 rows in set (0.00 sec)
Some other verifications:
1. Check whether the user just created is connected from the slave machine
root@zengkaijie1 12:19 mysql>show processlist; +----+------+----------------------+------------+-------------+------+---------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+----------------------+------------+-------------+------+---------------------------------------------------------------+------------------+ | 16 | root | localhost | zengkaijie1| Query | 0 | starting | show processlist | | 17 | zeng | 192.168.40.134:45246 | NULL | Binlog Dump | 669 | Master has sent all binlog to slave; waiting for more updates | NULL | +----+------+----------------------+------------+-------------+------+---------------------------------------------------------------+------------------+ 2 rows in set (0.00 sec)
2. View some related files on the slave machine
2.8 what should I do for master-slave replication in a production environment?
We were just master and slave The master-slave replication on the machine is completed in a non production state. What if the master-slave replication is performed in a production environment (that is, the database is providing services and the data is constantly refreshing)? In fact, the master-slave replication in the production environment and the master-slave replication in the non production environment are almost the same, but the difficulties to be solved are: 1,stay slave When adding user information to the machine, because the service is being provided, the binary log is changing, position The location point is also changing. 2,If we can't find the location point accurately, then master-slave replication can't guarantee the data consistency. 3,The best solution is to stop the service and perform master-slave backup.
What is the way to realize online master-slave replication------- XtraBackup
Mysqldump is also applicable and faster for exporting databases or tables below 10G. Once the data volume reaches 100-500G, mysqldump will not be able to cope with the pressure on the original library or the export performance.
Percona xtrabackup backup tool is the only choice for online hot backup of MySQL. It can perform full, incremental and single table backup and restore. (however, when the amount of data is larger, it may be necessary to consider splitting databases and tables, or use LVM snapshots to speed up backup.)
2.9 when will master-slave switching occur and how will master-slave switching be completed?
1.When to switch between master and slave, and how to achieve master-slave switching?
The master server is suspended, and the original slave server needs to be upgraded -->Master-slave switching
Completely manual operation:
Steps:
1.stop slave
2.reset master
3.Open binary log
4.Establish the user authorized to copy
5.Start another machine to do slave configuration master Information to pull binary log
How to switch the new traffic of the website to the new master?
- Directly modify the ip in the code in the web and replace it with the new master ip
- Modify the ip corresponding to the domain name to the ip of the new master
- If middleware is used, it needs to be adjusted in the middleware
Can master-slave switching be realized automatically?
Answer: Yes, using scripts.
1. Monitoring master
Scan port on another machine: nc 3306
Direct access: mysql -h ip -uroot -p '* *' - e 'show databases;'
Monitor every second
2. Execute the manual steps immediately, and the script will be executed automatically.
img-MI06jD7K-1662219930414)]