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

MySQL's master-slave building There are many ways, the traditional mysqldump way is one of many people's choice. However, this approach is not an ideal choice for larger databases. Use Xtrabackup to quickly and easily build or repair MySQL master-slave architectures. This article describes the use of Innobackupex to quickly build or repair a master-slave architecture. For everyone's reference.

1. Make a full backup based on the main library # 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 Database to standby # TAR-CZVF bakforslave.tar.gz./bakforslave/# SCP bakforslave.tar.gz [email protected]:~# scp/etc/my.cnf [email  PROTECTED]:~/MYMASTER.CNF3, Recovery on standby # # #备机解压打包的备份文件 # 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=4gb/data/bakforslave## #如果是修复从库, the starting state from the library should stop from the library, do the following, or you can skip the # Service mysqld Stop # # #还原备份的数据文件 # mv Mysqldata mysqldatabk# mv Bakforslave mysqldata# chown-r mysql:mysql mysqldata## #如果是 Newly built from the library, you can modify the main library my.cnf to native my.cnf,## #如果为修复, you can directly use the original configuration file or modify as needed. # cp/home/robin/mymaster.cnf/etc/my.cnf# VI/ETC/MY.CNF # # #此处应修改使用一个不同的server_id, you can modify the relevant path and port configuration as needed. # service Mysqld Start # # #修改完毕后User mysql> Grant replication slave,replication Client on *. * to [email protected] can start mysqld4, the primary library authorized for replication 172.16.10.% ' identified by ' * * *; validate shell prompt login to main Library # Mysql-urepl2-p-h172.16.10.885, start slave# more/data/mysqldata/xt rabackup_binlog_infomysql-bin.000136 73752825mysql> Change MASTER tomaster_host= ' 172.16.10.88 ',--aut Hor:leshamimaster_user= ' repl2 ',--blog:http://blog.csdn.net/leshamimaster_password= ' * * *, Master_log_ File= ' mysql-bin.000136 ',master_log_pos=73752825;mysql> start slave;6, verification results mysql> show slave status \g********** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:172.16.10.88 master_user:repl2 master_port:3306 connect_retry:60 master_log_file:mysql-bin.000136 read_master_log_pos:96592981 Relay_log_file:mysqld-r        elay-bin.000002        relay_log_pos:72113 relay_master_log_file:mysql-bin.000136 Slave_io_running:yes S           Lave_sql_running:yes Replicate_do_db:test,bs_com,bs_sysmsg,bs_bak Replicate_ignore_db:mysql                    Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table: last_errno:0 last_error:skip_counter:0 Exec_master_log_pos:                 73824655 relay_log_space:22840613 until_condition:none Until_log_file:               until_log_pos:0 Master_ssl_allowed:no Master_ssl_ca_file:master_ssl_ca_path: master_ssl_cert:master_ssl_cipher:master_ssl_key:seconds_behind_master:38 15master_ssl_verify_server_cert:no last_io_errno:0 Last_io_error:last_sql_ errno:0 LAst_sql_error:replicate_ignore_server_ids:master_server_id:2 master_uuid:afd6bca4-6636          -11e3-9d60-74867ae1c47c master_info_file:/data/mysqldata/master.info sql_delay:0 Sql_remaining_delay:null slave_sql_running_state:updating master_retry_count:86400 Mas Ter_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_S Sl_crlpath:retrieved_gtid_set:executed_gtid_set:auto_position:01 row in Set (0. XX sec)

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