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>
grant replication slave on *.* to 'repl'@'%' identified by '123456';
flush privileges;
说明: 默认只要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', 'li2si', '123456');
INSERT INTO user VALUES ('5', 'abc', '123456');
INSERT INTO user VALUES ('6', 'tom', '123456');
INSERT INTO user VALUES ('7', 'jk', '123456');
INSERT INTO user VALUES ('8', 'xb', '123456');
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
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> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 353 |
+-------------------+-----------+
1 row in set (0.00 sec)
mysql>
(2) 主库备份[root@master ~]# mysqldump -uroot -p -B mydb > mydb.sql
说明:-B参数有建库语句。
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>
# mysql -uroot -padmin < mydb.sql
7. 从库配置同步过程 (1) 配置同步,手动执行同步参数,该配置会写入master.info文件中。mysql >