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

This article mainly introduces how to build a slave database through backup recovery in mysql, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

There are many problems in MYSQL slave database. When the database is relatively large, it will be slow to use mysqldump to rebuild. After testing, you can do it with mysqlbackup. After recovering from the master database, you can configure the master-slave relationship. This method has also been used to rebuild in the existing environment, and the mysqlbackup can achieve 20G/MIN. You can also use mysqlbackup to create a new slave library, eliminating the step of initializing the slave library.

Steps

1 make a full backup of the main database

The backup will lock the myisam table. For example, if master is a production library, you should pay attention to whether the business process is active.

Mysqlbackup-defaults-file=/xxx/my.cnf-socket=/xxx/mysql.sock-user=root-password-with-timestamp-backup-dir=/xxx/

2 copy the backup files to the slave host for recovery

Make sure that the data and log directories of the slave library are empty, and restore with the my.cnf of the slave library.

During restore, the binlog and my.cnf backed up by the main library may also be restored directly to the data directory, which can be cleaned up.

Mysqlbackup-defaults-file=/xxx/my.cnf-backup-dir=/xxx/

3 start the slave library

4 execute the SET @ @ GLOBAL.GTID_PURGED statement of the backup_gtid_executed.sql file under the backup file meta directory

5 according to standard change master to

6 start slave .

May report ERROR 1872, can reset slave, and then re-change master.

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

Thank you for reading this article carefully. I hope the article "how to build a slave database through backup recovery in mysql" shared by the editor will be helpful to you. At the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.