mysqlxtrabackup線上搭建主從

轉身淚傾城發表於2016-05-13
MySql

使用xtrabackup進行線上的主從搭建： 
[root@mysqlserver var]# tar -xvf Percona-XtraBackup-2.3.4-re80c779-el6-x86_64-bundle.tar   –解壓包 
percona-xtrabackup-2.3.4-1.el6.x86_64.rpm 
percona-xtrabackup-debuginfo-2.3.4-1.el6.x86_64.rpm 
percona-xtrabackup-test-2.3.4-1.el6.x86_64.rpm 

[root@mysqlserver var]# rpm -ivh percona-xtrabackup-2.3.4-1.el6.x86_64.rpm 
warning: percona-xtrabackup-2.3.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY 
error: Failed dependencies: 
libev.so.4()(64bit) is needed by percona-xtrabackup-2.3.4-1.el6.x86_64 
perl(DBD::mysql) is needed by percona-xtrabackup-2.3.4-1.el6.x86_64 


http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4()(64bit)  –下載libev.so 

[root@mysqlserver var]# rpm -ivh libev-4.04-2.el6.x86_64.rpm 
warning: libev-4.04-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 66534c2b: NOKEY 
Preparing…                ########################################### [100%] 
   1:libev                  ########################################### [100%] 

[root@mysqlserver var]# yum list |grep  perl-DBD 
perl-DBD-MySQL.x86_64                  4.013-3.el6                      base    
perl-DBD-Pg.x86_64                     2.15.1-4.el6_3                   base    
perl-DBD-SQLite.x86_64                 1.27-3.el6                       base   

[root@mysqlserver var]# yum install perl-DBD-MySQL.x86_64 

[root@mysqlserver var]# rpm -ivh percona-xtrabackup-2.3.4-1.el6.x86_64.rpm   —安裝成功！ 
warning: percona-xtrabackup-2.3.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY 
Preparing…                ########################################### [100%] 
   1:percona-xtrabackup     ########################################### [100%] 

5、備份主庫： 

兩步都要做： 
innobackupex –defaults-file=/etc/my.cnf  –user=`root`    –password=`root`  /home/bak | gzip > /home/bak/`date +%F_%H-%M-%S`.tar.gz 
innobackupex –user=root    –password=`root`    –apply-log  /home/bak/2015-06-15_14-57-24  （步驟一備份完成後會產生2015-06-15_14-57-24目錄） 

[root@mysqlserver home]# innobackupex –defaults-file=/etc/my.cnf  –user=`root`    –password=`root`  /home/bak  –執行報錯！ 
Could not open required defaults file: /etc/my.cnf 
Fatal error in defaults handling. Program aborted 

[root@mysqlserver home]# innobackupex –defaults-file=/usr/local/mysql/etc/my.cnf  –user=`root`    –password=`root`  /home/bak –剛剛目錄錯誤 


[root@mysqlserver home]# xtrabackup –version 
xtrabackup version 2.3.4 based on MySQL server 5.6.24 Linux (x86_64) (revision id: e80c779)  –白費力啊，不知道是mysql5.7.10的版本啊！！ 
[root@mysqlserver home]# 


解除安裝後重新安裝2.4.1 
rpm -e percona-xtrabackup-2.3.4-1.el6.x86_64 

[root@mysqlserver var]# rpm -ivh percona-xtrabackup-24-2.4.1-1.el6.x86_64.rpm 
warning: percona-xtrabackup-24-2.4.1-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY 
Preparing…                ########################################### [100%] 
   1:percona-xtrabackup-24  ########################################### [100%] 


重新備份：innobackupex –defaults-file=/usr/local/mysql/etc/my.cnf  –user=`root`    –password=`root`  /home/bak   成功備份！ 
160326 10:59:11 [00] Writing backup-my.cnf 
160326 10:59:11 [00]        …done 
160326 10:59:11 [00] Writing xtrabackup_info 
160326 10:59:11 [00]        …done 
xtrabackup: Transaction log of lsn (62233854362) to (62344287480) was copied. 
160326 10:59:11 completed OK!  –注意結尾！ 

測試庫196的整個庫大小為：data目錄為22g    10:46–>11:00   大約15分鐘，壓縮30分鐘，解壓縮5分鐘 
[root@mysqlserver bak]# du -sh *   –備份目錄跟data目錄差不多大小 
24G 2016-03-26_10-46-17 
而生產庫的大小為：data目錄466g   約為21倍的時間啊！！ 


在實現“準備”的過程中，innobackupex 通常還可以使用 –use-memory 選項來指定其可以使用的記憶體的大小，預設通常為 100M。如果有足夠的記憶體可用， 
可以多劃分一些記憶體給 prepare 的過程，以提高其完成速度。 

應用日誌：innobackupex –apply-log /home/bak/2016-03-26_10-46-17 
InnoDB: 5.7.10 started; log sequence number 62344294933 
xtrabackup: starting shutdown with innodb_fast_shutdown = 1 
InnoDB: not started 
InnoDB: FTS optimize thread exiting. 
InnoDB: Starting shutdown… 
InnoDB: Shutdown completed; log sequence number 62344294997 
160326 11:01:47 completed OK! 

檢視當前的binlog日誌位置： 
[root@mysqlserver 2016-03-26_10-46-17]# more xtrabackup_binlog_info 
mysql-bin.000023 371727729 
[root@mysqlserver 2016-03-26_10-46-17]# 

到1.194準備進行恢復！ 

壓縮傳輸：[root@mysqlserver bak]# tar -zcvf   2016-03-26_10-46-17.tar.gz  2016-03-26_10-46-17/  
innobackupex –copy-back /backup/liubak/ 
–copy-back    把檔案按照/etc/my.cnf copy到資料目錄 


關閉mysql服務，並且保證data目錄是空的 
[root@javatx mysql]# service mysql57 stop 
Shutting down MySQL…..                                   [ok] 
[root@javatx mysql]# innobackupex –copy-back /home/2016-03-26_10-46-17   —恢復操作 
160326 11:44:47 innobackupex: Starting the copy-back operation 

IMPORTANT: Please check that the copy-back run completes successfully. 
           At the end of a successful copy-back run innobackupex 
           prints “completed OK!”. 

innobackupex version 2.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4) 
Original data directory /usr/local/mysql/data is not empty! 

[root@javatx mysql]# mv data data_old 

[root@javatx mysql]# innobackupex –copy-back /home/2016-03-26_10-46-17   11:46—>11.52  6分鐘 
160326 11:52:02 [01] Copying ./testtina/runningtest#P#p201503.ibd to /usr/local/mysql/data/testtina/runningtest#P#p201503.ibd 
160326 11:52:02 [01]        …done 
160326 11:52:02 [01] Copying ./testtina/t_timetest.frm to /usr/local/mysql/data/testtina/t_timetest.frm 
160326 11:52:02 [01]        …done 
160326 11:52:02 completed OK!   

改好引數後，啟動從庫： 
[root@javatx mysql]# chown -R mysql:mysql data  –注意修改屬主 
[root@javatx mysql]# service mysql57 start 
Starting MySQL…                                          [ok]  –啟動成功 
[root@javatx mysql]# 

CHANGE MASTER TO 
MASTER_HOST=`192.168.1.196`, 
MASTER_USER=`repli`, 
MASTER_PASSWORD=`repli`, 
MASTER_LOG_FILE=`mysql-bin.000023`, 
MASTER_LOG_POS=371727729; 

grant replication slave,replication client on *.* to repli@`192.168.1.196` identified by “repli”; 

start slave; 

mysql> show slave status G 
*************************** 1. row *************************** 
               Slave_IO_State: Queueing master event to the relay log  —其實已經成功了，可以正常追日誌就行了。 
                  Master_Host: 192.168.1.196 
                  Master_User: repli 
                  Master_Port: 3306 
                Connect_Retry: 60 
              Master_Log_File: mysql-bin.000023 
          Read_Master_Log_Pos: 400878447 
               Relay_Log_File: mysql-relay-bin.000002 
                Relay_Log_Pos: 45666 
        Relay_Master_Log_File: mysql-bin.000023 
             Slave_IO_Running: Yes 
            Slave_SQL_Running: Yes 
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 371773075 
              Relay_Log_Space: 29151245 
              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: 3525 
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: 1 
                  Master_UUID: 701cbadc-ba33-11e5-9091-305a3a78baf2 
             Master_Info_File: /usr/local/mysql/data/master.info 
                    SQL_Delay: 0 
          SQL_Remaining_Delay: NULL 
      Slave_SQL_Running_State: Reading event from the relay log 
           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 
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec) 

從庫搭建完成，發現有個表空間的檔案/var/my_tb/my_tb1.ibd 沒過來，我們可以直接傳過來，然後再change master 

重新啟動同步報錯： 
2016-03-26T04:05:05.043051Z 7 [Note] Slave SQL thread for channel “ initialized, starting replication in log `mysql-bin.000023` at position 371727729, relay log `/home/mysql/mysql-relaybinlog/mysql-relay-bin.000001` position: 4 
2016-03-26T04:05:05.324797Z 7 [ERROR] Slave SQL for channel “: Error `Duplicate entry `4331729` for key `PRIMARY“ on query. Default database: “. Query: `insert into vehiclerunninginfo.gpsrecord ( `SystemNo`,`Longitude`,`Latitude`,`Speed`,`Direction`,`Elevation`,`Acc`,`IsLocation`,`Mileage`,`Oil`,`CurrentTime`,`GPS_OprationLock`,`SouthLatitude`,`EastWest` ) values ( `LGXCE6CCXF0997107`,`114.356775`,`22.680342`,`0`,`0`,`0`,`1`,`1`,`124`,`0`,`2016-03-26 10:53:18`,“,`0`,`0` )`, Error_code: 1062 
2016-03-26T04:05:05.324835Z 7 [Warning] Slave: Duplicate entry `4331729` for key `PRIMARY` Error_code: 1062 
2016-03-26T04:05:05.324850Z 7 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log `mysql-bin.000023` position 371727729 


表空間在別的目錄，比較麻煩，刪掉表空間後重新做一次： 

innobackupex –copy-back /home/2016-03-26_13-20-33 
innobackupex –apply-log /home/2016-03-26_13-20-33 

[root@mysqlserver 2016-03-26_13-20-33]# more xtrabackup_binlog_info 
mysql-bin.000024 9850868 
[root@mysqlserver 2016-03-26_13-20-33]# 

CHANGE MASTER TO 
MASTER_HOST=`192.168.1.196`, 
MASTER_USER=`repli`, 
MASTER_PASSWORD=`repli`, 
MASTER_LOG_FILE=`mysql-bin.000024`, 
MASTER_LOG_POS=9850868; 

Error `Duplicate entry `3970019` for key `PRIMARY“ on query. Default database: “. Query: `insert into vehiclerunninginfo.mileagerecord ( `SystemNo`,`Longitude`,`Latitude`,`Speed`,`Direction`,`Elevation`,`Acc`,`IsLocation`,`Mileage`,`Oil`,`CurrentTime` ,`IC_TotalOdmeter`,`BMS_SOC`,`VCU_Keyposition`,`IC_Odmeter`,`VCU_BrakePedalSt`,`VCU_BrakeEnergy`,`VCU_Fault`,`VCU_CruisingRange` )  values ( `160316000000DFBE4`,`110.814669`,`32.610279`,`51`,`313`,`0`,`1`,`1`,`7785`,`0`,`2016-03-26 13:30:19` ,`7785`,`90`,`0`,`0`,`0`,`0`,`0`,`0` )` 
3952932 
   

因為主從一直不同步，且從庫的延時越來越長，重新快速備份恢復試試！—-下面是正確步驟 


innobackupex  –defaults-file=/usr/local/mysql/etc/my.cnf –use-memory=1G –user=`root`    –password=`root`  /home/bak  
非必要步驟–增量備份innobackupex   –defaults-file=/usr/local/mysql/etc/my.cnf –use-memory=1G  –user=`root`    –password=`root`  –incremental –incremental-basedir=/home/bak/2016-03-28_09-46-27  /home/bak/zl  
innobackupex –use-memory=500m –apply-log –redo-only –user=`root`    –password=`root`  /home/bak/2016-03-28_09-46-27 
scp  -r 2016-03-28_09-46-27 root@192.168.1.194:/home 

壓縮備份，注意是在當前目錄 
innobackupex  –defaults-file=/etc/my.cnf  –use-memory=2g –user=`root` –password=`root`  –stream=tar ./ |gzip -> 0330_fullbk.tar.gz 

還原： 
innobackupex –use-memory=1G –apply-log –redo-only  –user=`root` –password=`root`  /home/2016-03-28_09-46-27 
非必要步驟–增量還原 
innobackupex –use-memory=1G –copy-back /home/2016-03-28_09-46-27 

修改屬主： 
chown -R mysql:mysql data 

重啟服務： 
service mysql57 start 

[root@mysqlserver 2016-03-28_09-46-27]# more xtrabackup_binlog_info 
mysql-bin.000027 299109445 

配置同步： 
CHANGE MASTER TO 
MASTER_HOST=`192.168.1.196`, 
MASTER_USER=`repli`, 
MASTER_PASSWORD=`repli`, 
MASTER_LOG_FILE=`mysql-bin.000027`, 
MASTER_LOG_POS=299109445, 
MASTER_CONNECT_RETRY=10; 

關閉同步binlog_cache中的資料到磁碟–不太安全 
sync_binlog=0 
innodb_flush_log_at_trx_commit=0 

同步正常： 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
Seconds_Behind_Master: 0 
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 


速度果然非常快，一下子就同步了，同步後最好將引數調整回來。 
測試1：將從庫停掉，主庫插入資料，再重啟從庫，發現資料沒有同步過來，但是start slave後，資料同步 
測試2：將從庫的引數調整回來後，service mysql57 reload 並不生效，重啟mysql後 start slave，資料同步 
測試3：在從庫進行增刪改查，發現是可以的，問題出在哪裡？ 
mysql> insert into t1 values (11); 
Query OK, 1 row affected (0.10 sec) 

mysql> delete from t1 where id=11; 
Query OK, 1 row affected (0.14 sec)   –刪掉後不影響同步狀態。因為同步內容沒涉及到這個表資料，所以可能不衝突，但很明顯我們不是要的這個效果。 

原來要修改許可權： 
1.建立一個超級管理員使用者： grant all privileges on *.* to mydba@`%` identified by “mydba#246” with grant option; （主從都建立） 
                          grant all privileges on mysql.* to mydba@`%` with grant option; 
                          grant replication slave,replication client on *.* to repli@`192.168.1.196` identified by “repli”; 
2.回收root super許可權即可：revoke super ON *.* FROM `root`@`localhost`; 
                         revoke super ON *.* FROM `root`@`%`; 

3.以mydba啟動slave 
mysql> start slave;  –root已經無許可權了！ 
ERROR 1045 (28000): Access denied for user `root`@`localhost` (using password: YES) 

4.以root登入操作： 
mysql> insert into t1 values(11);  –從庫變成只讀許可權了，好開心！ 
ERROR 1290 (HY000): The MySQL server is running with the –read-only option so it cannot execute this statement 


read-only選項：對所有的非臨時表進行只讀控制。但是有兩種情況例外： 

                             1. 對replication threads例外，以保證slave能夠正常的進行replication。 
                             2. 對於擁有super許可權的使用者，可以ignore這個選項。 

SUPER 許可權 ： 1. 可以有change master to, kill其他使用者的執行緒的許可權。 
                             2. Purge binary logs 來刪除binary log, set global來動態設定變數的許可權。 
                             3. 執行mysqladmin debug命令，開啟或者關閉log，在read-only開啟時執行update/insert操作。 
                             4. 執行start slave, stop slave. 
                             5. 當連線數已經達到max_connections的最大值時，也可以連線到server。

相關文章