MySQL Master-Slaves 主从复制

MySQL Master-Slaves 主从复制

1 环境

MySQL5.7
Centos7
主库主机地址：192.168.100.218
从库主机地址：192.168.100.219

2 主库配置

1 在主数据库上创建同步账号。

grant replication slave on *.* to 'repl'@'192.168.100.219' identified by 'Mike666!';

2 配置主节点，开启binlog
配置如下：开启binlog，设置节点id
vi /etc/my.cfg
[mysqld]
log-bin=mysql-bin
#日志文件名前缀
server-id=1
#在集群中的唯一id,值可以为1~2^32-1的整数

重启mysql 服务
systemctl restart mysqld
查看主库状态
mysql > SHOW MASTER STATUS;

注意：
The following options have an impact on this procedure:
if you omit server-id (or set it explicitly to its default value of 0), the master refuses
any connections from slaves.
For the greatest possible durability and consistency in a replication setup using
InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and
sync_binlog=1 in the master my.cnf file.
Ensure that the skip-networking option is not enabled on your replication master. If
networking has been disabled, the slave cannot communicate with the master and
replication fails.

3 从库配置

从库可以多个，可以是主-从结构，也可是主-从-从结构，这里选择主-从结构。每个从库的配置
是一样的。
1 配置从库server-id
vi /etc/my.cfg
[mysqld]
server-id=2
#在集群中的唯一id,值可以为1~2^32-1的整数

重启mysql服务
systemctl restart mysqld
2 执行 CHANGE MASTER TO 语句设置同步的主库

CHANGE MASTER TO MASTER_HOST='192.168.100.218', MASTER_USER='repl', MASTER_PASSWORD='Mike666!'; /* MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4; */

3 开启从库
mysql > START SLAVE;
查看从库状态
mysql > SHOW SLAVE STATUS;
参考链接：
https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html
https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
5 测试
1 、创建数据库
在主库上分别创建 orders 数据库
CREATE DATABASE orders CHARACTER SET ‘utf8’;
2、在主库 orders 上创建表 t_order

CREATE TABLE t_order ( order_id BIGINT PRIMARY KEY, order_time DATETIME, customer_id BIGINT, order_amount DECIMAL(8,2) );

3、在主库orders上插入数据
INSERT INTO t_order VALUES (2,NOW(),1001,201); COMMIT;
4、查看从库上有没有orders库、t_order 表、以及数据。