Directory
1. Master-slave replication
1. Master-slave replication
2. The principle of master-slave replication
3. Three important threads
4. Realization of master-slave replication
5. Master-slave replication actual implementation scenario 2
6. Master-slave replication actual implementation scenario 2 Solution 2
7. Master-slave replication error solution
2. Cascade master-slave replication
1. The principle of cascade replication
2. Realization of cascading replication
One, master-slave replication
1, master-slave replication
Master-slave replication: The master device transmits to the slave device through the binary log, and the slave device synchronizes data with the master through the binary log.
Role: load balancing read operations, backup (real-time backup, cannot replace manual backup), high availability and failover, data distribution, Mysql upgrade.
2. Principle of master-slave replication
① The slave database handles user read operations, and the master database handles user write operations.
② When the user writes data to the master database, the master database is updated and written into the binlog binary log.
③ The master database starts the dump thread while reading the binlog log and transferring the log to the slave database through the network.
④ Receive the binlog log from the database through the io thread and save it as a relay log (that is, the binlog log just changed its name).
⑤ Start the sql thread from the database and write the relay log to the slave database. The master-slave database replication is completed.
3, three important threads
Dump thread: Started by the master database, used to read the master’s binary log and transmit it to the slave database.
io thread: Started by the slave database, used to receive binary logs and save them as relay logs.
SQL thread: started by the slave database, used to write the relay log to the standby database to complete the master-slave replication.
4. Actual implementation of master-slave replication
①Environment preparation: 2 servers with the same version of mysql database installed
Main server (pc1): 192.168.30.11
Slave server (pc2): 192.168.30.12
② First close the firewalld and selinux of the two servers, and reset the mysql password to Admin@123
pc1, pc2 execution: systemctl stop firewalld.service setenforce 0 systemctl start mysqld #Open the database grep password /var/log/mysqld.log #Find the mysql initial password in the error log, as shown below mysql -uroot -p'initial password' #login to the database alter user root@'localhost' identified by 'Admin@123'; quit; #Modify root default password and exit
③Master node configuration:
vim /etc/my.cnf [mysqld]Add the next line: server_id=11 #serverid is recommended to be configured as the last 8 digits of the ip address log-bin=/data/mysql/mysql-bin #Specify the storage location of the binary log file as /data/mysql/, save and exit mkdir -p /data/mysql #Create a directory to save the binaries chown mysql:mysql -R /data/ #The owner and group of the directory are given to the mysql user systemctl restart mysqld #Restart the database mysql -uroot -p'Admin@123' #login to the database grant replication slave on *.* to test@'192.168.30.%' identified by 'Admin@123'; #Create a copy user, as long as the address starts with 192.168.30, you can use the username test and password Admimin@123 to log in to the database. show master status; #View the master-slave replication from the initial node of the binary log, the slave server configuration needs to rely on this item to view the results, as shown below
④Slave node configuration:
vim /etc/my.cnf [mysqld]Add the next line: server_id=12 #serverid is recommended to be configured as the last 8 digits of the ip address log-bin=/data/mysql/mysql-bin #Specify the storage location of the binary log file as /data/mysql/ relay-log=relay-log-bin#Open relay log relay-log-index=slave-relay-bin.index#Add, define the location and name of the relay log file, save and exit mkdir -p /data/mysql #Create a directory to save the binaries chown mysql:mysql -R /data/ #The owner and group of the directory are given to the mysql user systemctl restart mysqld #Restart the database mysql -uroot -p'Admin@123' #login to the database help change master to #You can view the configuration help, copy it out and modify it, or directly copy the template below CHANGE MASTER TO MASTER_HOST='192.168.30.11', #Main server host ip MASTER_USER='test', #Master-slave replication login username MASTER_PASSWORD='Admin@123', #Master-slave copy login username password MASTER_PORT=3306, #Main server database port MASTER_LOG_FILE='mysql-bin.000001', #Start copying from that binary log, show master status; see the result MASTER_LOG_POS=448; #Start copying from the at node of the binary log, show master status; see the result start slave; #Enable master-slave replication show slave status\G #Check the master-slave replication status, find Slave_IO_Running: Yes Slave_SQL_Running: Yes If these two parameters are yes, the master-slave replication is started and completed.
⑤ Verification of experimental results:
Primary database: create databases test1; #Create a test1 test library Standby database: show databases; #Check whether it is synchronized to the test1 library
5. Master-slave replication actual implementation scenario 2
①Implementation background: Before the start of master-slave replication, the master database already has some data, and now it needs to do master-slave replication.
②Experimental environment:
Environment preparation: 2 servers with the same version of mysql database installed
Main server (pc1): 192.168.30.11
Slave server (pc2): 192.168.30.12
② First, close the firewalld and selinux of the two servers, and reset the mysql password to Admin@123.
pc1, pc2 execution: systemctl stop firewalld.service setenforce 0 systemctl start mysqld #Open the database grep password /var/log/mysqld.log #Find the mysql initial password in the error log, as shown below mysql -uroot -p'initial password' #login to the database alter user root@'localhost' identified by 'Admin@123'; quit; #Modify root default password and exit
③Master node configuration:
pc1: mysql -uroot -pAdmin@123 #login to the database create database test; use test; create table lhj(id int,name char(11)); insert lhj() values(1,'abc'),(2,'xyz'); quit #Create test library Enter test library to create lhj table and write 2 pieces of data into lhj table, exit the database vim /etc/my.cnf [mysqld]Add the next line: server_id=11 #serverid is recommended to be configured as the last 8 digits of the ip address log-bin=/data/mysql/mysql-bin #Specify the storage location of the binary log file as /data/mysql/, save and exit mkdir -p /data/mysql #Create a directory to save the binaries chown mysql:mysql -R /data/ #The owner and group of the directory are given to the mysql user systemctl restart mysqld #Restart the database mysqldump -uroot -p'Admin@123' -A -F --master-data=1 --single-transaction > /data/all.sql #Back up the existing content of the main data to the /data/all.sql file #-A --all-databases (represents all libraries) #-F refresh log #--master-data=1 will write the modified statement of master into the dump file #--single-transaction is backed up in the form of an open transaction scp /data/all.sql 192.168.30.12:/ #Transfer the file all.sql backed up by the master database to the / directory of the slave database mysql -uroot -pAdmin@123 grant replication slave on *.* to test@'192.168.30.%' identified by 'Admin@123'; show master status; #View the master-slave replication from the node of the binary log, the slave server configuration needs to rely on this item to view the results quit; #Log in to the database to create a master-slave replication login user and then exit
④Slave node configuration:
vim /etc/my.cnf [mysqld]Add the next line: server_id=12 #serverid is recommended to be configured as the last 8 digits of the ip address log-bin=/data/mysql/mysql-bin #Specify the storage location of the binary log file as /data/mysql/ relay-log=relay-log-bin#Open relay log relay-log-index=slave-relay-bin.index#Add, define the location and name of the relay log file, save and exit mkdir -p /data/mysql #Create a directory to save the binaries chown mysql:mysql -R /data/ #The owner and group of the directory are given to the mysql user systemctl restart mysqld #Restart the database vim /all.sql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154; #Find this line, add the main server ip after MSATER TO, and the content of the remote account password port is as follows: CHANGE MASTER TO MASTER_HOST='192.168.30.13', MASTER_USER='test', MASTER_PASSWORD='Admin@123', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154; #Save and exit after the content is written, this backup file can be understood as a script mysql -uroot -pAdmin@123 #login to the database source /all.sql; start slave; #Execute the all.sql script under the root and enable master-slave replication
⑤Practical results:
Log in from the server to verify the result: select * from test.lhj; #Check whether it is backed up to the table in the master database when the master-slave backup is not enabled
6. Master-slave replication actual implementation scenario 2 solution
①Implementation background: Before the start of master-slave replication, the master database already has some data, and now it needs to do master-slave replication.
②Experimental environment:
Environment preparation: 2 servers with the same version of mysql database installed
Main server (pc1): 192.168.30.11
Slave server (pc2): 192.168.30.12
③Solution
#You can directly scp the binlog in the master database to a certain directory of the slave server and then mysqlbinlog --no-defaults --start-position='start node' log position |mysql -u user -p'password' #Use this command to perform operations from the binary log of the master database at the very beginning, and perform all the operations done by the master on the slave database once. For detailed instructions, please refer to the previous blog mysql database log, backup and recovery. #Then perform the master-slave replication operation to ensure that the master-slave server data is fully synchronized
7. Master-slave replication error solution
Method 1: Temporarily skip the error stop slave; set global sql_slave_skip_counter=1; start slave;
Method 2: Permanently skip the error and modify the configuration file vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slave_skip_errors=1007|ALL
Restart the database
Second, cascading master-slave replication
1. Principle of cascade replication
① The first-level master is the master database of the second-level master, and the second-level master is the master server of the slave database
② During configuration, the master server ip log of the secondary master configures the information of the primary master, such as remote users, and the secondary master forwards the binary log of the primary master to the slave database
③When configuring the slave database, the master server ip log remote users and other configuration secondary master information
2. Actual implementation of cascading replication
①Environment preparation: 3 servers with the same version of mysql database installed
Primary primary server (pc1): 192.168.30.11
Secondary primary server (pc2): 192.168.30.12
Slave server (pc3): 192.168.30.13
② First close the firewalld and selinux of the three servers, and reset the mysql password to Admin@123
③Level master configuration: exactly the same as master-slave replication master server configuration
④Secondary master configuration: the same as the master-slave replication slave server configuration, only need to add one more item when modifying the configuration file: log_slave_updates (this item is to enable log forwarding update, that is, to forward the binlog of the first-level master to the slave server)
⑤ Slave server configuration: The same as the master-slave replication slave server configuration, note that the master of the slave server is the secondary master, so the information of the secondary master should be written when configuring the host ip, remote user password, port and log location.