Shulou(Shulou.com)06/01 Report--

This article mainly introduces the abnormal case analysis of online mysql master-slave architecture recovery. The content of the article is carefully selected and edited by the author. The analysis of abnormal cases of online mysql master-slave architecture recovery has a certain pertinence and is of great reference significance to everyone. Let's learn about the next topic with the author.

Premise: a client in charge of a former colleague was transferred to the devops team later. So hand over this user to me, later found that there is a set of database master-slave environment, slave library has been unable to use normally. To view the slave status is:

Where:

Master_Log_File:# the bin-log shown here can no longer be found on master

There is no point in the number of rows displayed by Read_Master_Log_Pos:#.

The Slave_IO_Running:NO # salve io process appears as no and cannot synchronize data from master

Therefore, it is determined that the slave library is no longer available and needs to be repaired in time. Ensure the normal use of the master-slave architecture.

The following is the whole process of recovery:

# # #

Main ideas: 1. Backup all data files of master database without locking tables. 2. Stop the process of slave database. And transfer the backup file from master to the server, decompress 3, re-execute change master to set the bin-log file name, and position###

First, use percona-xtrabackup on the Master side of the database for file-level database backup.

Execute the following command in the master database: (it needs to be modified according to the actual situation)

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password=51idc-- no-lock-- use-memory=4G-- compress--compress-threads=8-- stream=xbstream-- parallel=4 / backup > / backup/$ (date +% Y-%m-%d_%H-%M-%S)

Note: where

1. / backup this directory can be customized, which represents the location where the backup files are stored.

2. / etc/my.cnf is the default configuration file read when the database is started, which needs to be modified according to the actual situation. I use / etc/my.cnf here.

3. Modify the database connection password

4.-- no-lock means to back up the data without locking the table to ensure the normal operation of the online business.

This operation time depends on the amount of data, my own backup cost about 30min (130G data). A file appears after the backup is complete:

2019-02-27 11-12-21.xbstream

Second, use the command on the slave side of the database to recover data, because you need to ensure that the data of the master-slave database is consistent before recovery, but because the data of master has not been synchronized from the slave database for a long time, there is a big difference in the amount of master-slave data.

A. You need to stop the database first.

/ etc/init.d/mysql stop # stop the database

B. Delete the previous data files, which are under / var/lib/mysql by default; delete all files in the mysql directory, because next we need to extract the backup data to this directory.

C. Perform two decompression operations on the machine of the slave database to extract the backup files locally.

Xbstream-x

< /backup/2019-02-27_11-12-21.xbstream -C /backup/2019-02-27_11-12-21 innobackupex --decompress --parallel=4 /backup/2019-02-27_11-12-21 d、删除所有以 .qp结尾的文件 find /backup/2019-02-27_11-12-21 -name "*.qp" -delete e、创建完备份之后数据被没有马上可以被还原，需要回滚未提交事务前滚提交事务，让数据库文件保持一致性。 innobackupex使用-apply-log来做预备备份 --user-memory：指定预备阶段可使用的内存，内存多则速度快，默认为10MB innobackupex --apply-log --use-memory=4G /backup/2016-03-16_15-25-55 f、再将还原的数据文件拷贝到/var/lib/mysql目录下，其中/var/lib/mysql目录在/etc/my.cnf文件中指定的datadir innobackupex --defaults-file=/etc/my.cnf --copy-back --use-memory=4G /backup/2019-02-27_11-12-21 此过程需要的时间较长，我这边还原大概是2H左右。还原完成后，在/var/lib/mysql目录下有两个文件，可以看到salve目前保存的最近的bin-log文件和position

If you can't find the qpress command, you can install the repo.percona source and use yum-y install qpress to install it.

Repo.percona source installation command:

Yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

G, modify the main group of / var/lib/mysql

Chown-R mysql.mysql / var/lib/mysql

H. Start the database

/ etc/init.d/mysql start

I. Redo the master-slave configuration

Mysql-uroot-p # goes into the database

Change master to master_host=' Master xxx.xxx.xxx.xx',master_port=3306,master_user='root',master_password='51idc',master_lo_file='master-bin.xxx',master_log_pos=xxx

Among them: master IP, master_password are determined according to the actual situation.

The file name of the bin-log log and the location of the master_log_pos need to be viewed in these two files.

H. Start slave

Mysql > start slave

J. View slave status:

Mysql > show slave status\ G

You can see that the Slave_IO_Running: Yes and Slave_SQL_Running: Yes of slave from the database are all yes

And there are new bin-log files and position locations. After that, it can work normally and synchronize the master and slave.

After reading the above abnormal case analysis on the restoration of the master-slave architecture of online mysql, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.