Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "MYSQL full backup, master-slave replication, cascade replication, semi-synchronous knowledge points summary". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Mysql full backup

1. Enable binary logs, separate them from the database, and store them separately

Vim / etc/my.cnf

Add

Log_bin=/data/bin/mysql-bin

Create / data/bin folder and authorize

Chown mysql.mysql / data/bin

2. Complete the backup of the 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)

4, stop MySQL

Systemctl stop mariadb.service

5. Extract the backup file

Unxz / data/all.sql.xz

6. Find the location of the binary log during the 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, the verification is complete.

Recovery of erroneous deletion

1, enable binary logs and store them separately from the database

Vim / etc/my.cnf

Add

Log_bin=/data/bin/mysql-bin

Create / data/bin folder and authorize

Chown mysql.mysql / data/bin

2. 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"

4, stop the service

Systemctl stop mariadb.service

5. Delete the database

Rm-rf / var/lib/mysql/*

6. Extract the backup files

Unxz / data/all.sql.xz

7, check 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 misoperation command in the binary log node data

Vim / data/inc.sqlDROP 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 scp / data/all.sql 172.22.7.72:/data

Create an account to copy the 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 to 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 the slave status

Mysql-e "show slave status\ G;"

20. Add, delete and modify master objects to check whether they are synchronized.

21, synchronous complete.

MySQL semi-synchronization

Asynchronous replication means that when a user writes a record, he first writes the data to the master node, then replies to the user with a successful message, and then slowly copies the data to the other slave nodes behind it. The advantage is high efficiency, but the disadvantage is also very obvious. The delay between the master server and slave server is too large and the master server suddenly has an exception, which will cause data loss.

Synchronous replication is that when a user writes a record, the master node writes the data to the database, and then copies the data to the other slave nodes behind it. When all the slave nodes return the data to copy successfully, the master node replies to the message that the user's data access is successful. The advantage of this is that the security of the data is ensured, but the efficiency is lost.

Semi-synchronous replication is a replication method between synchronous replication and asynchronous replication. its working principle is that when a user performs a write operation, the master node will send data to other slave nodes behind it. As long as a slave node returns a successful replication message, the master node directly returns a successful write. If the slave node behind the master node delays returning the replication success message, there will be a timeout. Once the timeout period is reached, the master node first returns a message informing the user that the replication is successful, and then continues to copy the data to the slave node.

# for the steps of configuring master-slave replication, see above.

# 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 to see if 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 replication thread

Mysql-e "START SLAVE"

9, test check

10, synchronous completion

"MYSQL full backup, master-slave replication, cascade replication, semi-synchronous summary of knowledge points" is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.