mysql xtrabackup在线搭建主从

[size=small]
使用xtrabackup进行在线的主从搭建：
# 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

# 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

# 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%]

# 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

# yum install perl-DBD-MySQL.x86_64

# 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目录）

# 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

# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user='root' --password='root' /home/bak --刚刚目录错误


# xtrabackup --version
xtrabackup version 2.3.4 based on MySQL server 5.6.24 Linux (x86_64) (revision id: e80c779) --白费力啊，不知道是mysql5.7.10的版本啊！！
#


卸载后重新安装2.4.1
rpm -e percona-xtrabackup-2.3.4-1.el6.x86_64

# 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分钟
# 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日志位置：
# more xtrabackup_binlog_info
mysql-bin.000023 371727729
#

到1.194准备进行恢复！

压缩传输：# 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目录是空的
# service mysql57 stop
Shutting down MySQL..... [ok]
# 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!

# mv data data_old

# 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!

改好参数后，启动从库：
# chown -R mysql:mysql data --注意修改属主
# service mysql57 start
Starting MySQL... [ok] --启动成功
#

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

# more xtrabackup_binlog_info
mysql-bin.000024 9850868
#

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

# 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。[/size]