使用Innobackupex快速搭建(修復)MySQL主從架構

1、基于主庫做一個完整備份  #?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、復制數據庫到備機  #?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.cnf    3、在備機上恢復  ###備機解壓打包的備份文件  #?mv?/home/robin/bakforslave.tar.gz?/data  #?cd?/data  #?tar?-xvf?bakforslave.tar.gz    ###?prepare?備份  #?innobackupex?--user=root?-password=***?--socket=/tmp/mysql.sock?--defaults-file=/home/robin/mymaster.cnf?  ?--apply-log?--use-memory=4GB?/data/bakforslave    ###如果是修復從庫，從庫為啟動狀態應先停止從庫，再做如下操作，否則可以跳過?  #?service?mysqld?stop?    ###還原備份的數據文件  #?mv?mysqldata?mysqldatabk  #?mv?bakforslave?mysqldata  #?chown?-R?mysql:mysql?mysqldata    ###如果是新搭建的從庫，此時可以修改主庫的my.cnf為本機的my.cnf，  ###如果為修復，則可以直接使用原有的配置文件或根據需要修改。  #?cp?/home/robin/mymaster.cnf?/etc/my.cnf  #?vi?/etc/my.cnf?????????###此處應修改使用一個不同的server_id，同時可以根據需要修改相關路徑及端口配置等。  #?service?mysqld?start???###修改完畢后可以啟動mysqld    4、主庫授權用于復制的用戶  mysql>?grant?replication?slave,replication?client?on?*.*?to?repl2@'172.16.10.%'?identified?by?'***';    ###?驗證shell?提示符下登陸到主庫  #?mysql?-urepl2?-p?-h172.16.10.88    5、啟動slave  #?more?/data/mysqldata/xtrabackup_binlog_info  mysql-bin.000136????????73752825    mysql>?CHANGE?MASTER?TO  MASTER_HOST='172.16.10.88',????????????--Author:?Leshami  MASTER_USER='repl2',???????????????????--Blog??:?http://www.php.cn/  MASTER_PASSWORD='***',  MASTER_LOG_FILE='mysql-bin.000136',  MASTER_LOG_POS=73752825;    mysql>?start?slave;    6、驗證結果  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  ??????????Read_Master_Log_Pos:?96592981  ???????????????Relay_Log_File:?mysqld-relay-bin.000002  ????????????????Relay_Log_Pos:?72113  ????????Relay_Master_Log_File:?mysql-bin.000136  ?????????????Slave_IO_Running:?Yes  ????????????Slave_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:?3815  Master_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  ??????????????????Master_Bind:?  ??????Last_IO_Error_Timestamp:?  ?????Last_SQL_Error_Timestamp:?  ???????????????Master_SSL_Crl:?  ???????????Master_SSL_Crlpath:?  ???????????Retrieved_Gtid_Set:?  ????????????Executed_Gtid_Set:?  ????????????????Auto_Position:?0  1?row?in?set?(0.00?sec)