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 表、以及数据。
参考链接: