MySQL设置主从备份

MySQL设置主从备份

两台主机地址分别为：
ServA：192.168.72.130
ServB：192.168.72.131

1.1创建同步账号

create user backups@'%' identified by '123456';
grant all privileges on *.* to backups@'%' with grant option;

1.2配置数据库参数ServA

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]的配置项中增加如下配置

log-bin=MySQL-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
server-id=1
binlog-do-db=wms      #需要同步的库
replicate-do-db=wms    #需要同步的库
binlog_ignore_db=mysql   #忽略写入binlog日志的库
binlog_ignore_db=information_schema   #忽略写入binlog日志的库
binlog_ignore_db=performation_schema   #忽略写入binlog日志的库
binlog_ignore_db=sys   #忽略写入binlog日志的库
slave-net-timeout=60

1.3 配置数据库参数 ServB

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]的配置项中增加如下配置

log-bin=MySQL-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
server-id=2                         #与主不同  如果已存在就删掉
binlog-do-db=wms      #需要同步的库
replicate-do-db=wms    #需要同步的库
binlog_ignore_db=mysql   #忽略写入binlog日志的库
binlog_ignore_db=information_schema   #忽略写入binlog日志的库
binlog_ignore_db=performation_schema   #忽略写入binlog日志的库
binlog_ignore_db=sys   #忽略写入binlog日志的库
slave-net-timeout=60

2.1重启服务查看配置信息

sudo service mysql restart 
mysql -uroot -p
show master status;
记录下 File 值 MySQL-bin.xxx 和 Position 值 xxx

2.2配置同步信息

// ServA运行
change master to master_host='192.168.72.131',master_user='backups',master_password='123456',master_log_file='MySQL-bin.000001',master_log_pos=157;
// ServB运行
change master to master_host='192.168.72.130',master_user='backups',master_password='123456',master_log_file='MySQL-bin.000001',master_log_pos=157;
// ServAServB运行
start slave;
// 查看是否配置成功
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes