扩展:主从复制:xtrabackup部署mysql主从
xtrabackup部署mysql主从
A:首先在主从库上都安装xtrabackup
B:主库机器要做的事情
a、添加防火墙规则(省略)
b、添加mysql用户用于复制binlog
shell > /usr/local/mysql/bin/mysql -uroot -p -S /data/mysql-3306/mysql.sock
mysql > select host,user,password from mysql.user;
mysql > grant replication slave on . to backup@‘192.168.100.76’ identified by ‘123456’;
mysql > flush privileges;
主和从的my.cnf中的server-id要不相同,主启动二进制日志记录。
用命令备份mysql的datadir下的所有文件
shell > innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --password=123456 --port=3306 /data/backup/dbbackup/
将备份文件夹scp传输到从库机器上,备份出来后是整个文件夹,如2015-04-25_16-14-21
shell > rsync -avprP -e ssh /data/backup/dbbackup/2015-04-25_16-14-21 192.168.100.76:/data/backup/dbbackup #确保/data/backup/dbbackup目录在从库上存在
c、从库上用命令先把事务日志恢复(–apply-log),然后再复制到从库的datadir(–copy-back)
shell > innobackupex --defaults-file=/usr/local/mysql-slave/my.cnf --user=root --password=123456 --port=3307 --apply-log /data/backup/dbbackup/2015-04-25_16-14-21/
shell > innobackupex --defaults-file=/usr/local/mysql-slave/my.cnf --user=root --password=123456 --port=3307 --copy-back --rsync /data/backup/dbbackup/2015-04-25_16-14-21/
注意:
(1)datadir文件夹不存在时会报错
Warning: option ‘innodb_autoextend_increment’: unsigned value 33554432 adjusted to 1000
Original data directory does not exist! at /usr/bin/innobackupex line 546.
(2)一定要把从库原来datadir下的所有文件删除掉再进行上面第二条命令的复制(–copy-back),不然会报错
d、删除datadir下的事务日志log文件(innodb引擎才会有)
shell > cd /data/mysql-slave/data
shell > rm -rf ib_logfile*
e、设置权限,启动从库mysql
shell > chown -R mysqlslave:mysqlslave /data/mysql-slave/data
shell > /usr/local/mysql-slave/start_mysql.sh
查看备份时的binlog-file
shell > cat /data/backup/dbbackup/2015-04-27_17-28-16/xtrabackup_binlog_info
mysql-bin.000003 170301293
f、再change master to就可以了
shell > /usr/local/mysql-slave/bin/mysql -uroot -p -S /data/mysql-slave/mysql.sock
mysql > change master to master_host=‘192.168.100.77’,master_user=‘backup’,master_password=‘123456’,master_log_file=‘mysql-bin.000003’,master_log_pos=170301293;
mysql > start slave;
mysql > show slave status\G
本文地址:https://blog.csdn.net/xiaoleinb/article/details/111997495