Use innobackupex to build a mysql master-slave architecture based on the slave database, innobackupexmysql

Use innobackupex to build a mysql master-slave architecture based on the slave database, innobackupexmysql

There are many ways to build MySQL Master/Slave databases. The traditional mysqldump mode is one of the choices of many people. However, this method is not ideal for large databases. With Xtrabackup, you can quickly and easily build or repair the mysql Master/Slave architecture. This article describes how to quickly build a master-slave database based on the existing slave database, that is, a new slave database of the original master database. The advantage of this method is that the performance pressure caused by backup is not required for the master database. The quick stream backup method is used to accelerate Master/Slave building and describe several parameters for accelerating stream backup for your reference.

For more information about stream backup, see Xtrabackup stream backup and recovery.

1. Backup slave Database
### The equivalence verification is used during the remote backup. Therefore, you must configure it first. Here we use the mysql user
$ Innobackupex -- user = root -- password = xxx -- slave-info -- safe-slave-backup \
-- Compress-threads = 3 -- parallel = 3 -- stream = xbstream \
-- Compress/log | ssh-p50021 mysql@172.16.16.10 "xbstream-x-C/log/recover"

### The safe-slave-backup parameter is used during backup. You can see that the SQL thread is stopped and started after completion.
$ Mysql-uroot-p-e "show slave status \ G" | egrep 'slave _ IO_Running | Slave_ SQL _Running'
Enter password:
Slave_IO_Running: Yes
Slave_ SQL _Running: No
Slave_ SQL _Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

### Copy the my. cnf file to the new slave Database
$ Scp-P50021/etc/my. cnf mysql@172.16.16.10:/log/recover

2. The master database grants the new slave database copy account
Master @ MySQL> grant replication slave, replication client on *. * to repl @ '192. 16. %. % 'identified by 'repl ';

3. New Slave database prepare
### Because stream compression backup is used, you need to decompress it first
### Http://www.quicklz.com/
# Tar-xvf qpress-11-linux-x64.tar
Qpress
# Cp qpress/usr/bin/
$ Innobackupex -- decompress/log/recover ### decompress
$ Innobackupex -- apply-log -- use-memory = 2G/log/recover ### prepare backup

4. Prepare the slave database configuration file my. cnf
### Modify the parameters as needed. The modifications are as follows,
Skip-slave-start
Datadir =/log/recover
Port = 3307
Server_id = 24
Socket =/tmp/mysql3307.sock
Pid-file =/log/recover/mysql3307.pid
Log_error =/log/recover. err

5. Start slave database and modify the change master
# Chown-R mysql: mysql/log/recover
#/App/soft/mysql/bin/mysqld_safe -- defaults-file =/log/recover/my. cnf &

Mysql> system more/log/recover/xtrabackup_slave_info
Change master to MASTER_LOG_FILE = 'mysql-bin.000658 ', MASTER_LOG_POS = 925384099
Mysql> CHANGE MASTER
-> MASTER_HOST = '2017. 16.16.10 ', ### Author: Leshami
-> MASTER_USER = 'repl', ### Blog: http://blog.csdn.net/leshami
-> MASTER_PASSWORD = 'repl ',
-> MASTER_PORT = 3306,
-> MASTER_LOG_FILE = 'mysql-bin.000658 ',
-> MASTER_LOG_POS = 925384099;
Query OK, 0 rows affected, 2 warnings (0.31 sec)

Mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

6. Slave Database Backup parameters and acceleration stream backup Parameters

The -- slave-info option This option is useful when backing up a replication slave server. It prints the binary
Log position and name of the master server. It also writes this information to the xtrabackup_slave_info role le
As a change master statement.
This is useful for setting up a new slave for this master can be set up by starting a slave server on this backup and
Issuing the statement saved in the xtrabackup_slave_info role le.

The -- safe-slave-backup option In order to assure a consistent replication state, this option stops the slave
SQL thread and wait to start backing up until Slave_open_temp_tables in SHOW STATUS is zero. If there are
No open temporary tables, the backup will take place, otherwise the SQL thread will be started and stopped until there
Are no open temporary tables. The backup will fail if Slave_open_temp_tables does not become zero after
-- Safe-slave-backup-timeout seconds (ults to 300 seconds). The slave SQL thread will be restarted
When the backup exceeds nishes.
Using this option is always recommended when taking backups from a slave server.

Warning: Make sure your slave is a true replica of the master before using it as a source for backup. A good tool
To validate a slave is pt-table-checksum.

-- Compress
This option instructs xtrabackup to compress backup copies of InnoDB
Data files. It is passed directly to the xtrabackup child process.
### Note: compress is a relatively rough compression method, which is compressed into. gp files without a high gzip compression ratio.

-- Compress-threads
This option specifies the number of worker threads that will be used
For parallel compression. It is passed directly to the xtrabackup
Child process. Try 'xtrabackup -- help' for more details.

-- Decompress
Decompresses all files with the. qp extension in a backup previusly
Made with the -- compress option.

-- Parallel = NUMBER-OF-THREADS
On backup, this option specifies the number of threads
Xtrabackup child process shocould use to back up files concurrently.
The option accepts an integer argument. It is passed directly
Xtrabackup's -- parallel option. See the xtrabackup documentation
Details.

On -- decrypt or -- decompress it specifies the number of parallel
Forks that shoshould be used to process the backup files.