欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

扩展:主从复制:xtrabackup部署mysql主从

程序员文章站 2022-06-17 08:14:40
xtrabackup部署mysql主从A:首先在主从库上都安装xtrabackupB:主库机器要做的事情a、添加防火墙规则(省略)b、添加mysql用户用于复制binlogshell > /usr/local/mysql/bin/mysql -uroot -p -S /data/mysql-3306/mysql.sockmysql > select host,user,password from mysql.user;mysql > grant replication sla...

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

相关标签: mysql