Use Innobackupex to quickly build (fix) the MySQL master-slave architecture, innobackupexmysql

Use Innobackupex to quickly build (fix) the MySQL master-slave architecture, 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 use innobackupex to quickly build or repair a master-slave architecture. For your reference.

 

1. Complete Backup Based on the master database # mkdir-p/log/bakforslave # innobackupex -- user = root-password = *** -- socket =/tmp/mysql. sock \ -- defaults-file =/etc/my. cnf/log/bakforslave -- parallel = 3 -- safe-slave-backup -- no-timestamp 2. Copy the database to the slave machine # tar-czvf bakforslave.tar.gz. /bakforslave/# scp bakforslave.tar.gz robin@172.16.10.51 :~ # Scp/etc/my. cnf robin@172.16.10.51 :~ /Mymaster. cnf3, restore on the slave machine ### decompress the packaged backup file on the slave machine # mv/home/robin/bakforslave.tar.gz/data # cd/data # tar-xvf bakforslave.tar.gz ### prepare backup # innobackupex -- user = root-password = *** -- socket =/tmp/mysql. sock -- defaults-file =/home/robin/mymaster. cnf \ -- apply-log -- use-memory = 4 GB/data/bakforslave ### if the slave database is fixed, stop the slave database first when the slave database is started, otherwise, you can skip # service mysqld stop ### restore the backup data file # mv mysqldata mysqldatabk # mv bakforslave m Ysqldata # chown-R mysql: mysql mysqldata ### if it is a newly built slave database, you can modify my. cnf is my. cnf, ### if it is fixed, you can directly use the original configuration file or modify it as needed. # Cp/home/robin/mymaster. cnf/etc/my. cnf # vi/etc/my. cnf ### use a different server_id here, and modify the relevant path and port configuration as needed. # Service mysqld start ### after modification, you can start mysqld4, master database Authorization User mysql for replication> grant replication slave, replication client on *. * to repl2 @ '2017. 16.10.% 'identified '***'; ### verify shell prompt login to the MASTER database # mysql-urepl2-p-h172.16.10.885, start slave # more/data/mysqldata/xtrabackup_binlog_infomysql-bin.000136 73752825 mysql> change master TOMASTER_HOST = '100. 16.10.88 ', -- Author: LeshamiMASTER_USER = 'repl2', -- Blog: Success> start slave; 6. Verification Result: mysql> show slave status \ G *************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.10.88 Master_User: repl2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000136 connector: 96592981 Relay_Log_File: mysqld-relay-bin.000002 connector: 72113 connector: mysql-bin.000136 connector: Yes connector: test, bs_com, bs_sysmsg, bs_bak Protocol: mysql Protocol: 0 Last_Error: Skip_Counter: 0 Protocol: 73824655 Relay_Log_Space: 22840613 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Protocol: No protocol: usage: Master_SSL_Cert: Usage: Master_SSL_Key: Seconds_Behind_Master: Usage: No usage: 0 Last_IO_Error: Usage: 0 Last_ SQL _Error: Usage: Master_Server_Id: 2 Master_UUID: afd6bca4-6636-11e3-9d60-74867ae1c47c Master_Info_File: /data/mysqldata/master.info SQL _Delay: 0 keys: NULL keys: updating Master_Retry_Count: 86400 Master_Bind: Keys: Master_SSL_Crl: Master_SSL_Crlpath: Keys: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)