Summary of MYSQL full backup, master-slave replication, cascading replication, and semi-synchronization

Summary of MYSQL full backup, master-slave replication, cascading replication, and semi-synchronization

mysql full backup

1. Enable binary log and separate it from the database and store it separately

 vim /etc/my.cnf

Add to

log_bin=/data/bin/mysql-bin

Create the /data/bin folder and authorize it

chown mysql.mysql /data/bin

2. Complete the backup database

mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz

3. Add, delete and modify the database

 INSERT hellodb.students(stuid,name,gender,age) VALUE(27,'Lujunyi','M',30);

4. Stop MySQL

 systemctl stop mariadb.service

5. Unzip the backup file

unxz /data/all.sql.xz

6. Find the location of the binary log during full backup

vim /data/all.sql 
  HANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=468

7. Export the binary log after the backup is completed

mysqlbinlog --start-position=468 /data/bin/mysql-bin.000001 > /data/inc.sql

8. Restore data

mysql -e 'source /data/all.sql'
mysql -e 'source /data/inc.sql'

9. Verification completed.

Recovery from accidental deletion

1. Enable binary log and store it separately from the database

vim /etc/my.cnf

Add to

 log_bin=/data/bin/mysql-bin

Create the /data/bin folder and authorize it

 chown mysql.mysql /data/bin

2. Perform a full backup of the database

mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz

3. Add, delete and modify the database

mysql -e "drop table hellodb.students"
  mysql -e "insert hellodb.teachers value (5,'wangqi',50,'M')"

4. Stop the service

 systemctl stop mariadb.service

5. Delete the database

rm -rf /var/lib/mysql/*

6. Unzip the backup file

unxz /data/all.sql.xz

7. View the backup file and find the binary node

 vim /data/all.sql
   -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=521902;

8. Export binary log node data

mysqlbinlog --start-position=521902 /data/bin/mysql-bin.000004 >/data/inc.sql

9. Delete the command that caused the error in the binary log node data

 vim /data/inc.sql
  DROP TABLE `hellodb`.`students` /* generated by server */

10. Start the service

systemctl start mariadb.service

11. Turn off binary logging

mysql -e "SET sql_log_bin=off"

12. Import backup data

 mysql </data/all.sql 
 mysql </data/inc.sql

13.Verification completed.

Master-slave replication

# Primary Server

1. Enable binary logging on the master server and change the binary directory

 vim /etc/my.cnf
  log_bin=/data/bin/mysql-bin
  binlog-format=row
  server-id=1 (the master and slave servers must be different)

Change directory as above

2. Restart the service

service mysql restart

3. Create an account to copy data

mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"

4. View the binary log being used by the master server

 show master logs;
  +------------------+-----------+
  | Log_name | File_size |
  +------------------+-----------+
  |mysql-bin.000001 | 14383 |
  +------------------+-----------+
  1 row in set (0.00 sec)

# From the server

5. More configuration

 vim /etc/my.cnf
  server-id=2
  read-only
  #log-bin=/data/bin/mysql-bin

6. Start the service

service mysql restart

7. Associate the main service

 MariaDB [(none)]>
 CHANGE MASTER TO MASTER_HOST='172.22.7.70', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=14383;

8. Check the slave server status

show slave status\G;

9. Start the thread

start slave;

#test

10. Add, delete and modify the data on the master server and check whether the data on the slave server is synchronized.

Solution to master-slave replication error - sql_slave_skip_counter

#master service ip=172.22.7.70

1. Enable binary logging on the master server and change the binary directory

 vim /etc/my.cnf
  log_bin=/data/bin/mysql-bin
  binlog-format=row
  server-id=1 (the master and slave servers must be different)

Change directory as above

2. Restart the service

service mysql restart

3. Create an account to copy data

mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"

4. View the binary log being used by the master server

 mysql -e 'show master logs;'
  +------------------+-----------+
  | Log_name | File_size |
  +------------------+-----------+
  |mysql-bin.000001 | 264 |
  |mysql-bin.000002 | 245 |
  +------------------+-----------+

#slave service ip=172.22.7.71

5. Modify the configuration file and start the service

 vim /etc/my.cnf
  [mysqld]
  server-id = 2
  read-only
 systemctl start mariadb

#Configuration error master service information

6. Configuration, change master to

 CHANGE MASTER TO 
 MASTER_HOST='172.22.7.77', 
 MASTER_USER='wang',  
 MASTER_PASSWORD='lodman',
 MASTER_PORT=3306, 
 MASTER_LOG_FILE=log-bin.001', 
 MASTER_LOG_POS=4, 
 MASTER_CONNECT_RETRY=10;

7. Check slave status

 mysql -e 'show slave status\G'
  Slave_IO_State: 
    Master_Host: 172.22.7.77
    Master_User: wang
    Master_Port: 3306
   Connect_Retry: 10
   Master_Log_File: log-bin.001
  Read_Master_Log_Pos: 4
   Relay_Log_File: ct7m1-relay-bin.000001
   Relay_Log_Pos: 4
 Relay_Master_Log_File: log-bin.001
   Slave_IO_Running: No
  Slave_SQL_Running: No

       ·········slightly

8. Start the copy thread

mysql -e 'start slave'

9. Check the slave status again

 mysql -e 'show slave status\G'
  Slave_IO_State: 
    Master_Host: 172.22.7.77
    Master_User: wang
    Master_Port: 3306
   Connect_Retry: 10
   Master_Log_File: log-bin.001
  Read_Master_Log_Pos: 4
   Relay_Log_File: ct7m1-relay-bin.000001
   Relay_Log_Pos: 4
 Relay_Master_Log_File: log-bin.001
   Slave_IO_Running: Connecting
  Slave_SQL_Running: Yes

       ·········slightly

10.Master service adds, deletes and modifies data

11. Check whether the slave service is synchronized. Failed!

#Solve the error

12. Stop and reset the slave replication thread service

 mysql -e 'stop slave'
 mysql -e 'reset slave'

13. Configure the correct change master to information

 CHANGE MASTER TO MASTER_HOST='172.22.7.70',
 MASTER_USER='repluser',
 MASTER_PASSWORD='centos',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.000002',
 MASTER_LOG_POS=245;

14. Check the slave status

show slave status\G;
   Slave_IO_State: Waiting for master to send event
    Master_Host: 172.22.7.70
    Master_User: repluser
    Master_Port: 3306
   Connect_Retry: 10
   Master_Log_File:mysql-bin.000002
  Read_Master_Log_Pos: 7382
   Relay_Log_File: ct7m1-relay-bin.000002
   Relay_Log_Pos: 540
 Relay_Master_Log_File: mysql-bin.000002
   Slave_IO_Running: Yes
  Slave_SQL_Running: No

15. Found Slave_SQL_Running: No in slave status, execute the following command to change it to YES

 MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
   Slave_IO_State: Waiting for master to send event
    Master_Host: 172.22.7.70
    Master_User: repluser
    Master_Port: 3306
    Connect_Retry: 10
   Master_Log_File:mysql-bin.000002
  Read_Master_Log_Pos: 7382
   Relay_Log_File: ct7m1-relay-bin.000003
    Relay_Log_Pos: 540
  Relay_Master_Log_File: mysql-bin.000002
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
        ·········slightly

16. Check whether the slave service data is synchronized

17. Synchronization completed.

Mysql cascading replication

In production, there is a master-slave replication method in which the master node first synchronizes the data to an intermediate slave node, and then the slave node copies the data to subsequent slave nodes. This replication method is called cascading replication.

The benefit of cascading replication is that it can greatly reduce the pressure on the primary node.

Cascading replication requires the log_slave_updates option to be enabled on the intermediate nodes when configuring.

#Environment server master slave slave
 System centos7 centos7 centos7
 ip 172.22.7.70 172.22.7.70 172.22.7.71

#mater

1. Enable binary logging on the master server and change the binary directory

 vim /etc/my.cnf
  log_bin=/data/bin/mysql-bin
  binlog-format=row
  server-id=1 (the master and slave servers must be different)

Change directory as above

2. Restart the service

service mysql restart

3. Create an account to copy data

mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"

4. View the binary log being used by the master server

 mysql -e 'show master logs;'
  +------------------+-----------+
  | Log_name | File_size |
  +------------------+-----------+
  |mysql-bin.000001 | 264 |
  |mysql-bin.000002 | 7488 |
  | mysql-bin.000003 | 402 |
  +------------------+-----------+

 
#slave

5. Modify the configuration file and create a binary log directory

 vim /etc/my.cnf
  [mysqld]
  log-bin=/data/bin/mysql-bin
  binlog-format=row
  read-only
  log_slave_updates
  server-id=2

Change directory see above

6. Start the service

 systemctl restart mariadb

7. Configure the change master to information

 CHANGE MASTER TO MASTER_HOST='172.22.7.70',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=402;

8. Start the slave thread

 mysql -e 'start slave'

9. Check slave status

 show slave status\G;
 *************************** 1. row ***************************
   Slave_IO_State: Waiting for master to send event
    Master_Host: 172.22.7.70
    Master_User: repluser
    Master_Port: 3306
   Connect_Retry: 60
   Master_Log_File: mysql-bin.000003
  Read_Master_Log_Pos: 7539
   Relay_Log_File: ct7m1-relay-bin.000002
   Relay_Log_Pos: 7677
 Relay_Master_Log_File: mysql-bin.000003
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
   Replicate_Do_DB:

10. Add, delete and modify data on the master to test and view

#slave1

11. Copy the data on the slave to the slave

 mysqldump -A --single-transaction -F --master-data=1 > /data/all.sql
 scp /data/all.sql 172.22.7.72:/data

12. Slave creates an account to copy data

 mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"

13. Modify slave1 configuration

 vim /etc/my.cnf
  [mysqld]
  read-only
  server-id=3

14. Start the service

 systemctl start mariadb

15. View the binary log of the slave server

 mysql -e 'show master logs'
  +------------------+-----------+
  | Log_name | File_size |
  +------------------+-----------+
  |mysql-bin.000001 | 351 |
  |mysql-bin.000002 | 351 |
  |mysql-bin.000003 | 351 |
  |mysql-bin.000004 | 25552 |
  |mysql-bin.000005 | 586 |
  +------------------+-----------+

16. Open the all.sql file and modify the change master on information

 CHANGE MASTER TO MASTER_HOST='172.22.7.71',MASTER_USER='repluser',MASTER _PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=586;

17. Import slave data

mysql < /data/all.sql

18. Start the thread

 mysql -e "START SLAVE;"

19. Check slave status

mysql -e "show slave status\G;"

20. Add, delete, and modify the master object to check whether it is synchronized

21. Synchronization completed.

MySQL semi-synchronous

Asynchronous replication means that when a user writes a record, the data is first written to the master node, and then a successful write message is replied to the user, and then the data is slowly copied to other slave nodes behind it. The advantage of this is that the efficiency is relatively high, but the disadvantage is also very obvious. The delay between the master server and the slave server is too large and the master server suddenly fails, which will cause data loss.

Synchronous replication means that when a user writes a record, the master node writes the data to the database and then copies the data to other slave nodes behind it. When all slave nodes return a message that the data has been successfully copied, the master node replies to the user that the data has been successfully accessed. The advantage of this is that it ensures data security, but at the expense of efficiency.

Semi-synchronous replication is a replication method between synchronous replication and asynchronous replication. Its working principle is: when the user performs a write operation, the master node will send the data to other slave nodes behind it. As long as one slave node returns a message of successful replication, the master node will directly return a successful write. If the slave node behind the master node fails to return a successful replication message, there will be a timeout period. Once the timeout period is reached, the master node will first return a message to tell the user that the replication is successful, and then continue to replicate the data to the slave node.

#Configure master-slave replication. See above for steps.

#Configure semi-synchronization

##master

1. Modify the configuration file to enable the plug-in

 vim /etc/my.cnf
  [mysqld]
  log-bin=/data/bin/mariadb-bin
  binlog-format=row
  server-id=1
  rpl_semi_sync_master_enabled

2. Restart the service.

 systemctl restart mariadb

3. Check whether the plug-in is started

 SHOW GLOBAL VARIABLES LIKE '%semi%';
  +------------------------------------+-------+
  | Variable_name | Value |
  +------------------------------------+-------+
  | rpl_semi_sync_master_enabled | ON |
  | rpl_semi_sync_master_timeout | 3000 |
  | rpl_semi_sync_master_trace_level | 32 |
  | rpl_semi_sync_master_wait_no_slave | ON |
  +------------------------------------+-------+
  4 rows in set (0.00 sec)

4. Set the timeout period

 SET GLOBAL rpl_semi_sync_master_timeout=3000;
 Query OK, 0 rows affected (0.00 sec)

##slave

5. Modify the configuration file to enable the plug-in

 vim /etc/my.cnf
  [mysqld]
  log-bin=/data/bin/mariadb-bin
  binlog-format=row
  server-id=2
  rpl_semi_sync_master_enabled

6. Restart the service.

systemctl restart mariadb

7. Check whether the plug-in is started

 SHOW GLOBAL VARIABLES LIKE '%semi%';
  +------------------------------------+--------------+
  | Variable_name | Value |
  +------------------------------------+--------------+
  | rpl_semi_sync_master_enabled | ON |
  | rpl_semi_sync_master_timeout | 1000 |
  | rpl_semi_sync_master_trace_level | 32 |
  | rpl_semi_sync_master_wait_no_slave | ON |
  | rpl_semi_sync_master_wait_point | AFTER_COMMIT |
  +------------------------------------+--------------+
  5 rows in set (0.00 sec)

8. Start the copy thread

mysql -e "START SLAVE";

9. Test and Inspection

10. Synchronization completed

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:
  • MySQL semi-synchronous replication principle configuration and introduction detailed explanation
  • A brief talk about MySQL semi-synchronous replication
  • Mysql semi-synchronous replication principle and troubleshooting
  • In-depth analysis of semi-synchronous and asynchronous MySQL master-slave replication configuration
  • Detailed explanation of MySQL semi-synchronization

<<:  Detailed steps to implement the Excel import function in Vue

>>:  Detailed tutorial on installing centos8 on VMware

Recommend

Docker Getting Started Installation Tutorial (Beginner Edition)

Doccer Introduction: Docker is a container-relate...

In-depth explanation of Mysql deadlock viewing and deadlock removal

Preface I encountered a Mysql deadlock problem so...

The best way to start a jar package project under Centos7 server

Preface Everyone knows how to run a jar package o...

Detailed Introduction to Nginx Installation and Configuration Rules

Table of contents 1. Installation and operation o...

Detailed explanation of jQuery's copy object

<!DOCTYPE html> <html lang="en"...

Solution to invalid Nginx cross-domain setting Access-Control-Allow-Origin

nginx version 1.11.3 Using the following configur...

The whole process record of Vue export Excel function

Table of contents 1. Front-end leading process: 2...

Use of Linux ls command

1. Introduction The ls command is used to display...

React Principles Explained

Table of contents 1. setState() Description 1.1 U...

Issues and precautions about setting maxPostSize for Tomcat

1. Why set maxPostSize? The tomcat container has ...

Example of implementing circular progress bar in Vue

Data display has always been a demand that all wa...

SQL Aggregation, Grouping, and Sorting

Table of contents 1. Aggregate Query 1. COUNT fun...

Solution to the problem that the MySQL configuration file cannot be modified (Win10)

Record the problems you solve for others. Problem...

How to use physics engine joints in CocosCreator

Table of contents mousejoint mouse joint distance...

Teach you step by step to develop a brick-breaking game with vue3

Preface I wrote a few examples using vue3, and I ...