mysql master slaves_MySQL Master-Slaves 主从复制

MySQL Master-Slaves 主从复制

环境

MySQL5.7

Centos7

主库主机地址：192.168.1.201

从库主机地址：192.168.1.202/203主库配置

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

grant replication slave on *.* to 'repl1'@'192.168.1.202' identified by '123456';

grant replication slave on *.* to 'repl2'@'192.168.1.203' identified by '123456';

配置主节点，开启binlog

vi /etc/my.cnf

[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.从库配置

从库可以多个，可以是主-从结构，也可是主-从-从结构，这里选择主-从结构。每个从库的配置是一样的。

1 配置从库server-id

[root@dev202 ~]# vim /etc/my.cnf

[mysqld]

server-id=2 # 在集群中的唯一id,值可以为1~2^32-1的整数

重启mysql

systemctl restart mysqld

2 执行 CHANGE MASTER TO 语句设置同步的主库

CHANGE MASTER TO MASTER_HOST='192.168.1.201', MASTER_USER='repl1', MASTER_PASSWORD='123456', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154

CHANGE MASTER TO MASTER_HOST='192.168.1.201', MASTER_USER='repl2', MASTER_PASSWORD='123456', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154

3 开启从库

mysql> start slave;

4 查看从库状态

mysql> show slave status;

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 (1,NOW(),1001,201);

COMMIT;

4、查看从库上有没有orders库、t_order 表、以及数据。

参考链接：