mysql主从复制

如下两台机器, 做一主一从复制:

1. Master: 192.168.1.7
2. Slave: 192.168.1.6

Master上的my.cnf:

log-bin=mysql-bin
sync_binlog=1
innodb_support_xa=1
binlog_format=ROW
server-id=7
innodb_flush_log_at_trx_commit=1
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql

Slave上的my.cnf:

log-bin=mysql-bin
sync_binlog=1
innodb_support_xa=1
binlog_format=ROW
server-id=6
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

 

Master配置, 先登录mysq: mysql -uroot -p, 然后配置账号:

grant FILE on *.* to 'root'@'192.168.1.6' identified by 'xxx';
grant replication slave on *.* to 'root'@'192.168.1.6' identified by 'xxx';

配置好后重启Master:

systemctl restart mysqld.service

 进入mysql查看状态:

这里的File和Position在接下来在slave上使用.

Slave配置, 先登录mysql: mysql -uroot -p, 再进行如下操作:

stop slave;
change master to master_host='192.168.1.7', master_user='root', master_password='1qaz@WSX3edc', master_log_file='mysql-bin.000005', master_log_pos=154;
start slave;

 

 然后在Master机器上添加测试数据库, 添加测试表, 添加测试数据. 可以看到slave上也会有相应数据了.

create database mytestdb;
create table account(id int, primary key(id));
use mytestdb;
create table account(id int, primary key(id));
insert into account select 1;
insert into account select 2;
insert into account select 3;
insert into account select 4;
insert into account select 5;

 

主主复制也简单, 把上面对应Master和slave上的操作再在对方机器上操作一下, 主主复制就完成了.