OS:centos 7.4
主:5.6.41-log MySQL Community Server (GPL)
从:5.6.34-log Source distribution
master:
systemctl stop mysqld
vim /etc/my.cnf
在 [mysqld] 下添加
server_id= 100
log-bin=mysql-bin
#GTID
gtid_mode = on
enforce_gtid_consistency=on
binlog_format=STATEMENT #设置 binlog 为 STATEMENT / row
log-slave-updates=1
#再开启GTID之前一定要确定已经开启了bin-log功能,否则启动会报错,没有开启就先将gtid功能注释,然后启动
systemctl start mysqld
master-mysql>show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 100 |
| server_id_bits | 32 |
+----------------+-------+
master-mysql>show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
master-mysql>GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'%' IDENTIFIED BY 'xiaojuzi';
slave:
/etc/init.d/mysqld stop
server-id = 200 #服务 ID,主从实例 server-id 需不同
log-bin= mysql-bin
log_bin = /usr/local/mysql/mysql-bin.log#随意指定,注意属组和属主
expire_logs_days = 10
max_binlog_size = 100M
replicate-do-db = artemis #需要同步的数据库
replicate-do-db = didi_credit #需要同步的数据库
replicate-do-db = auto_call #需要同步的数据库
replicate-do-db = middleware #需要同步的数据库
replicate-ignore-db = mysql #不需要同步的数据库
replicate-ignore-db = information_schema #不需要同步的数据库
replicate-ignore-db = performance_schema #不需要同步的数据库
#需要同步的数据库,已master库为准。
#GTID
gtid_mode = on
enforce_gtid_consistency=on
binlog_format=STATEMENT #设置 binlog 为 STATEMENT / row
log-slave-updates=1
#再开启GTID之前一定要确定已经开启了bin-log功能,否则启动会报错,没有开启就先将gtid功能注释,然后启动。
/etc/init.d/mysqld start
slave-mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 200 |
| server_id_bits | 32 |
+----------------+-------+
slave-mysql>show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
将master库数据全备或者单个库备份,个人喜欢逐个库备份,然后导入slave库。
mysqldump -uroot -p123456789 artemis --default-character-set=utf8 | gzip > /root/artemis.sql.gz
gunzip < artemis.sql.gz | mysql -uroot -p123456Ms3 artemis
slave-mysql>change master to master_host = '10.59.223.56', master_port = 3306, master_user = 'myslave', master_password='xiaojuzi', master_auto_position = 1;
slave-mysql>start slave;
slave-mysql>show slave status\G;
测试:
mysql> use auto_call
mysql> create table t1(id int,ename varchar(20));
Query OK, 0 rows affected (0.18 sec)
mysql> insert into t1 values(1,'leshami');
Query OK, 1 row affected (0.01 sec)
mysql> select * from auto_call.t1;
+------+---------+
| id | ename |
+------+---------+
| 1 | leshami |
+------+---------+
1 row in set (0.00 sec)
#END
转载于:https://blog.51cto.com/amunlinux/2167647