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

This article is about how MySQL maintains master-slave synchronization without downtime. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

Actual combat environment

Mysql-5.7

Xtrabackup-2.4

Xtrabackup introduction

Percona XtraBackup is an open source hot backup utility for MySQL-based servers that does not lock the database during backup.

It can back up data from InnoDB,XtraDB and MyISAM tables on MySQL5.1,5.5,5.6 and 5.7 servers, as well as Percona servers with XtraDB.

XtraBack XtraBackup tools up tools

Xtrabackup itself can only back up InnoDB and XtraDB, not MyISAM.

Innobackupex encapsulates the perl script of xtrabackup and overrides the functionality of Xtrabackup. It can back up not only the tables of the nnodb and xtradb engines, but also the tables of the myisam engine (a read lock is required when backing up the myisam table).

Characteristics of Xtrabackup

The backup process is fast and reliable

The backup process does not interrupt the transaction that is in progress

Ability to save disk space and traffic based on functions such as compression

Automatic backup verification

Fast reduction speed

Implementation principle of Xtrabackup backup

Innobackupex starts the xtrabackup_log monitoring thread, monitors the changes of redolog files in real time, and copies the logs newly written to the transaction log to innobackup_log during the new backup.

At the same time, start the xtrabackup copy thread, start copying innodb files, copy data structures, record the current binlog and position to complete the backup.

Full recovery principle

The complete file is played back in the xtrabackup_log log, the committed transaction is redone, and the uncommitted transaction is rollback. And copy the complete files to the data directory under mysql.

Master database

1. Install the xtrabackup tool

$yum install-y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm$ yum update percona-release$ yum install percona-xtrabackup-24-y

2. Full provision of the main database

# full backup $innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password=magedu / data/backup # keeping transaction consistency apply-log is used to merge committed transactions into the ibdata file $innobackupex-- apply-log / data/backup/2020-03-28 transactions 19-13-07 by rolling back uncommitted transactions and synchronizing committed transactions until the data file is in a consistent state

3. Compress and copy the backup files of the master database to the slave database

$tar czf backup.tar.gz 2020-03-288 19-13-07$ scp-r / data/backup/backup.tar.gz 172.21.0.8:/data/backup/

4. Main database authorizes synchronization account

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'slave'@'%' IDENTIFIED BY' slave_passport';mysql > FLUSH PRIVILEGES

5. Check the backup location of the main library, and synchronize from the library from the backup location.

$cat / data/backup/2020-03-289-13-07/xtrabackup_binlog_infomysql-bin.000001 1053

Operate from the database

1. Full recovery from data

$cd / data/backup/ & & tar xf backup.tar.gz$ innobackupex-- defaults-file=/etc/my.cnf-- user=root-- copy-back / data/backup/2020-03-28 19-13-07

2. Modify data file permissions

$chown-R mysql:mysql / var/lib/mysql

3. Start the slave database

$systemctl restart mysqld

4. View the location of the backup of the master database in step 5 and start synchronization

Mysql > CHANGE MASTER TO MASTER_HOST='172.21.0.9',\ MASTER_USER='slave',\ MASTER_PASSWORD='slave_passport',\ MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',\ MASTER_LOG_POS=1053

5. Enable master-slave synchronization

Mysql > start slave;mysql > show slave status\ G; # the following two Yes representatives have synchronized normally. The above is how MySQL maintains master-slave synchronization without downtime. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.