参考: https://www.jianshu.com/p/fba84c613e88
机器列表
系统 | ip | 主/从 |
---|---|---|
centos 7.1 | 192.168.200.210 | master |
centos 7.1 | 192.168.200.211 | slave |
centos 7.1 | 192.168.200.212 | slave |
一、master配置
[mysqld]
log-bin=mysql-bin #主节点需要开启二进制日志
server-id=100 #
创建用于复制操作的用户
# 创建用户cluster,并且只能给你是ip为192.168.200.%的可以登录
mysql> CREATE USER 'cluster'@'192.168.200.%' IDENTIFIED WITH mysql_native_password BY 'cluster';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'cluster'@'192.168.200.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
或者不做ip段限制
# 创建用户cluster,任意ip可以登录
mysql> CREATE USER 'cluster'@'%' IDENTIFIED WITH mysql_native_password BY 'cluster';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'cluster'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
可以通过
use mysql;
select host,user from user;
查看新建用户
获取 主节点 binary log文件名和位置(position)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 855 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
文件名:mysql-bin.000001
位置(Position ):855
二、slave0配置
[mysqld]
server-id=101
三、slave1配置
[mysqld]
server-id=102
四、slave0\1节点上设置主节点参数
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.200.210',
-> MASTER_PORT=3306,
-> MASTER_USER='cluster',
-> MASTER_PASSWORD='cluster',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=855;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
MASTER_HOST:主机ip
MASTER_PORT: 端口
MASTER_USER:帐号
MASTER_PASSWORD:密码
MASTER_LOG_FILE:日志文件
MASTER_LOG_POS=855:日志position
开启主从同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec
查看从机状态
mysql> show slave status\G;
两个参数都是yes说明从服务器配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
现在可以尝试主库写入,查看从库是否自动同步数据
如果主节点参数填写错误,需要修改,可以先关闭同步,再修改参数
# 关闭同步
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
# 重新指定主机参数
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.200.210',
-> MASTER_PORT=3306,
-> MASTER_USER='cluster',
-> MASTER_PASSWORD='cluster',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=855;
# 再次开启同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec
五、如果主库已经存在数据,先把主库数据库备份再导入从库后,再进行同步
# 导出主库需要同步的数据库名
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
# 导入到从库中
mysql -u root -p
use 数据库名 (先创建数据库)
source var/local/xxx.sql
导入到从库后,再做上面的同步处理