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

Using innobackupex to build MySQL slave library

Knowledge preparation:

1. In the process of backing up InnoDB, the recorded changes are saved in the xtrabackup_logfile file, so when prepare (- apply-log), you need to replay this part of the data to the tablespace.

2. If only innodb or XtraDB engine is used in the library, use the xtrabackup_binlog_pos_innodb file to determine the pos information during recovery.

3. If there are other engines (such as MyISAM), use xtrabackup_binlog_info to determine the pos information when restoring.

4. The process diagram of innobackupex

Redo logs in xtrabackup_logfile

1. Complete the main library, and then prepare.

Backup

[root@zabbix-server 2017-05-22 July 07-24-07] # innobackupex-- defaults-file='/etc/my.cnf'-- user=root-- password=zhagyilig@mysql-- user-memory=2048M-- no-timetamp-- backup / opt/

Prepare

[root@zabbix-server opt] # innobackupex-- defaults-file='/etc/my.cnf'-- apply-log 2017-05-26 15-53-59 /

two。 Compress the backup and copy it to slave

Tar-czvf 2017-05-26 15-53-59-mysql.tar.gz 2017-05-26 15-53-59 / scp mysql_full_backup.tar.gz xxx@xxx:/path/

3. Create replication users in master

Grant replication slave on *. * to 'rep'@'192.168.21.%' identified by' replication'; flush privileges

4. Configure slave

[root@beiyong-server data] # cat xtrabackup_binlog_info mysql-bin.000006 216752542 stop slave;CHANGE MASTER TO MASTER_HOST='192.168.21.161', MASTER_PORT=3306,MASTER_USER='rep', MASTER_PASSWORD='replication', MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=216752542; start slave Show slave status\ Master_User * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.21.161 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 216816993 Relay_Log_File: beiyong-server-relay-bin.000002 Relay_Log_Pos: 64734 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes. / / build MySQL using innobackupex to finish the slave library.

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.