Collection again! How can MySQL maintain master-slave synchronization without downtime? This is enough

Preface

In May 17, 2013, after the last IBM small computer of Alibaba group was released from Alipay, there is no doubt that MySQL is already the most widely used database in Internet Co nowadays. Mysql is free and open source, and it can be conveniently extended horizontally to support high concurrency traffic. Small and medium-sized enterprises use Mysql as the main and multiple slave, and can also conveniently read and write programs.

How to maintain master-slave synchronization when the master database is not down in an enterprise? Today we will share with you the actual case of the enterprise.

Actual combat environment

Mysql-5.7
Xtrabackup-2.4

Xtrabackup introduction

Percona XtraBackup is an open source hot backup utility based on MySQL server, which will not lock the database during the backup process.

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

Xtraback xtraback up tools

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

innobackupex encapsulates the perl script of xtrabackup, covering the function of xtrabackup. It can not only back up the tables of the two engines, nnodb and xtradb, but also the tables of the myisam engine (a read lock needs to be added when backing up the myisam tables).

Xtrabackup features

Fast and reliable backup process;

The backup process will not interrupt the ongoing transaction;

Save disk space and traffic based on compression and other functions;

Automatic backup inspection;

Fast reduction speed;

Implementation principle of Xtrabackup backup

innobackupex starts xtrabackup log monitoring thread to monitor the change of redolog file in real time, and copies the new backup process to innobackup log;

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

Principle of full recovery

Perform xtrabackup log playback on the full file, and redo the committed transactions. At the same time, rollback the uncommitted transactions. And copy the full file to the data directory under mysql.

Master database

1. Install 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. Master database full backup

# Full volume backup $ innobackupex --defaults-file=/etc/my.cnf --user=root --password=magedu /data/backup
# The purpose of keeping the transaction consistency is to merge the submitted transactions into the ibdata file $innobackupex -- apply log / data / backup / 2020-03-28_19-13-07 by rolling back the uncommitted transactions and synchronizing the submitted transactions to the data file for consistency

3. Compress and copy the backup files of the primary database to the secondary database

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

4. Master database authorization synchronization account

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

5. View the backup of the primary library to the location, and synchronize from the backup location

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

Operation from 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 from database

$ systemctl restart mysqld

4. Check the location of the fifth step of the main database backup to 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. Turn on master-slave synchronization

mysql> start slave;mysql> show slave status\G;
# The following two Yes means that slave? IO? Running = yessslave? SQL? Running = Yes has been synchronized normally

summary

Mysql master-slave synchronization, dual machine hot standby and data backup technology have become one of the indispensable technologies for engineers, and Mysql is always used in any company.

Posted by zoozle on Tue, 14 Apr 2020 02:23:11 -0700

Programmer Group