MySQL 5.6主从同步配置案例分享,希望对大家有所帮助。
本文环境
主库:CentOS6.5 x64 192.168.0.65 mysql-5.6.29
备库:CentOS6.5 x64 192.168.0.66 mysql-5.6.29
一、常规配置方式一
1. mysql主服务器配置
# vi /etc/my.cnf
[mysqld]
log-bin = master-bin
log-bin-index = master-bin.index
binlog_format = mixed
server-id = 1
# service mysqld restart
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 353 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
2. 主服务器配置同步复制帐号
grant replication slave on *.* to 'repl'@'%' identified by '123456';
flush privileges;
3. mysql从服务器配置
说明: 默认只要server-id不相同即可。
# vi /etc/my.cnf
[mysqld]
log-bin = mysql-bin
binlog_format = mixed
server-id = 11
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
配置说明: 不同步mysql库可以实现主从库有不同的帐号权限,经过测试,mysql5.6.29中只在从库中配置有效。
其它参数:
binlog-do-db = mydb 仅同步一个数据库
#replicate-ignore-db = mysql 忽略掉mysql库,该参数产生很多意外的同步问题,还是不使用。
replicate_wild_ignore_table = mysql.% 忽略掉mysql库
# service mysqld restart
4. 测试示例
CREATE DATABASE `mydb`;
CREATE TABLE `user` (
`id` varchar(20) NOT NULL,
`username` varchar(20) NOT NULL,
`password` char(32) NOT NULL,
PRIMARY KEY (`id`)
) ;
INSERT INTO user VALUES ('1', 'koumm', '123456');
INSERT INTO user VALUES ('2', 'zhangsan', '123456');
INSERT INTO user VALUES ('3', 'lisi', '123456');
INSERT INTO user VALUES ('4', 'li2s