网上有很多的配置,发现都有一些问题,所以记录下!
环境
Mysql 5.6
CentOS 7 2台
服务器 | IP |
---|---|
master1 | 192.168.2.11 |
master2 | 192.168.2.12 |
配置
- 两台服务器分别安装Mysql 5.6,并测试连接成功。;
- 两台机器上都建立用户(username),并赋予复制权限:
master1:
登录mysql
1. mysql> CREATE USER 'username'@'%';
2. mysql> GRANT REPLICATION SLAVE ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
3. mysql> FLUSH PRIVILEGES;
master2:
登录mysql
1. mysql> CREATE USER 'username'@'%';
2. mysql> GRANT REPLICATION SLAVE ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
3. mysql> FLUSH PRIVILEGES;
- 分别配置my.cnf文件
master1:
1. [mysqld]
2. server-id = 11
3. log-bin = mysql-bin
4. auto-increment-increment= 2 # 应设为整个结构中服务器的总数
5. auto-increment-offset = 1 # 设定数据库中自动增长的起点,避免两台服务器数据同步时出现主键冲突
6. replicate-do-db=iSec_Platform #设置只同步iSec_Platform表
7. replicate-ignore-db = mysql #设置不同步mysql表
master2:
1. [mysqld]
2. server-id = 12
4. log-bin = mysql-bin
5. auto-increment-increment= 2
6. auto-increment-offset = 2
7. replicate-do-db=iSec_Platform
8. replicate-ignore-db = mysql #设置不同步mysql表
- 重启两台机器上的服务。 service mysqld restart (centos6),systemctl restart mysql(centos7)
- master1上指定master2为主:
master1:
6. mysql> change master to master_host='192.168.2.12',master_user='‘username’',master_password='password';
7. mysql> START SLAVE;
- master2上指定master1为主:
9. mysql> change master to master_host='192.168.2.11',master_user='‘username’',master_password='password';
10. mysql> START SLAVE;
- 分别查看是否配置成功
show slave status\G;