mysql xtrabackup在线搭建主从

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

时间： 2024-03-30 15:06:17

mysql xtrabackup在线搭建主从的相关文章

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

使用Xtrabackup在线备份及恢复MySQL

Xtrabackup是由percona开发的一个开源软件,它是innodb热备工具ibbackup(收费的商业软件)的一个开源替代品 .Xtrabackup由个部分组成:xtrabackup和innobackupex,其中xtrabackup工具用于备份innodb和 xtraDB引擎的表:而 innobackupex工具用于备份myisam和innodb引擎的表,本文将介绍如何用innobackupex工具做全量和增量备份. 官网:http://www.percona.com/docs/wik

mysql 主备切换 主从互换

问题描述 mysql 主备切换 主从互换 如题,我有两个数据库 主库 A 192.168.30.128 备库 B 192.168.28.129 现在的情况是我把两台数据库用主从复制模式,主库开启binlog,备库通过主库binlog重演主库,生产主库备份.主库master 备库slave ,备库作为了只读数据库,现在主库挂了,有何办法能使主库失效,应用直接启动备库 解决方案 MYSQL主备复制结构搭建与切换mysql 主从热备,主主互备mysql主从切换

MYSQL实战-MHA搭建及问题总结

##################MHA部署#################### 1.服务器  192.168.56.21 mydb1   #Master  192.168.56.22 mydb2   #Slave  192.168.56.23 mydb3   #MHA manager 2. 所有机器修改vim /etc/hosts 192.168.56.21   mydb1 192.168.56.22   mydb2 192.168.56.23   mydb3 3.设置SSH公钥免密码登

网站搭建-用mysql+apache+phpmyadmin搭建php环境

问题描述 用mysql+apache+phpmyadmin搭建php环境 用mysql+apache+phpmyadmin搭建php环境,进入phpadmin提示用户名密码错误,但我确实是对的请问有大神知道吗?错误提示1045 解决方案 工欲善其事必先利其器,先准备好软件: Apache: httpd-2.2.15-win32-x86-openssl-0.9.8m-r2.msi PHP: php-5.2.11-Win32.zip,之所以选择这个版本是因为网上都是配置的php5apache2_2.

IIS+PHP+MYSQL服务器环境搭建教程

中介交易 http://www.aliyun.com/zixun/aggregation/6858.html">SEO诊断 淘宝客 云主机 技术大厅 IIS+PHP+MYSQL服务器环境搭建教程 : 1.安装IIS:插入WINDOWS安装盘-控制面板-添加或删除程序-添加/删除windows组件-选择Internet信息服务安装. 2.安装PHP:到www.php.net下载win平台压缩包(注意不要选msi自动安装不利于维护),解压到任意目录,如c:/php. 3.配置php环境:复制P

HybridDB for MySQL 实现在线与离线数据分离的实践

本文将重点介绍HybridDB for MySQL 实现在线与离线数据分离的实践,特别推荐! 核心业务简介 任务中心汇聚了集团的所有工作流任务,并提供统一的入口给用户处理集团的工作任务. 面临主要问题 1.单表存储量超高目前已有4千万的数据,并且在急速的增长.预计年增长在200%以上. 2.业务需要大范围的查询由于业务需要查询多张表,比如查询在线,再查离线表.而且频率和复杂度在提升.会导致慢sql的出现. 如何架构改造 在线数据与离线数据隔离,在数据访问层面不相互影响  在线数据到离线数据必须实

打不开ubuntu服务器端的mysql，在线求解答！

问题描述 打不开ubuntu服务器端的mysql,在线求解答! 查询状态的时候打印如下 xxx@r10f17332:~$ service mysql status mysql stop/waiting 启动的时候打印如下 xxx@r10f17332:~$ service mysql start start: Rejected send message, 1 matched rules; type="method_call", sender=":1.114" (uid

详解MySQL集群搭建_Mysql

概述 MySQL Cluster 是MySQL 适合于分布式计算环境的高实用.可拓展.高性能.高冗余版本,其研发设计的初衷就是要满足许多行业里的最严酷应用要求,这些应用中经常要求数据库运行的可靠性要达到99.999%.MySQL Cluster允许在无共享的系统中部署"内存中"数据库集群,通过无共享体系结构,系统能够使用廉价的硬件,而且对软硬件无特殊要求.此外,由于每个组件有自己的内存和磁盘,不存在单点故障. 实际上,MySQL集群是把一个叫做NDB的内存集群存储引擎集成与标准的MyS