CentOS6.5 MySQL5.6 双主模式配置

08/08 系统架构 阅读 290 views 次 人气 0
摘要:

CentOS6.5 MySQL5.6 双主模式配置

服务器:CentOS6.5

MySQL服务:MySQL5.6.37

安装主机:192.168.39.14、192.168.39.15


1、创建目录,且授权mysql

mkdir /usr/local/mysql/relaylog/
chown -R mysql:mysql /usr/local/mysql/relaylog/


2、主机(192.168.39.14),MySQL增加配置(/etc/my.cnf)

log-bin=mysql-bin
server-id=1
binlog_format=mixed
relay-log=/usr/local/mysql/relaylog/relay
relay-log-index=/usr/local/mysql/relaylog/relay
relay_log_purge=on
auto_increment_offset=1 
auto_increment_increment=2


3、主机(192.168.39.15),MySQL增加配置(/etc/my.cnf)

log-bin=mysql-bin
server-id=2
binlog_format=mixed
relay-log=/usr/local/mysql/relaylog/relay
relay-log-index=/usr/local/mysql/relaylog/relay
relay_log_purge=on
auto_increment_offset=2 
auto_increment_increment=2


4、创建MySQL用户且授权(192.168.39.14、192.168.39.15)

grant replication slave, replication client on *.* to 'slave'@'192.168.39.%' identified by 'slave123';
flush privileges;

grant replication slave, replication client on *.* to 'slave'@'192.168.39.%' identified by 'slave123';
flush privileges;


5、主机(192.168.39.15)同步操作

a、主机(192.168.39.14)操作

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

b、主机(192.168.39.15)操作

mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> change master to master_host='192.168.39.14', master_port=3306,  master_user='slave',master_password='slave123',master_log_file='mysql-bin.000004',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> 
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql>


6、主机(192.168.39.14)同步操作

a、主机(192.168.39.15)操作

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

b、主机(192.168.39.14)操作

mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> change master to master_host='192.168.39.15', master_port=3306, master_user='slave',master_password='slave123',master_log_file='mysql-bin.000006',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> 
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql>


7、其他参考命令

show slave status\G;


评论

该文章不支持评论!

分享到: