mysql xtrabackup在线搭建主从

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql xtrabackup在线搭建主从

转身泪倾城 2016-05-13 10:20:48 浏览3560
展开阅读全文

使用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。

网友评论

登录后评论
0/500
评论
转身泪倾城
+ 关注