1. 环境
- 操作系统
CentOS-7 - MySQL
mysql-5.7 - 三台虚拟机
192.168.33.13 master
192.168.33.12 relay
192.168.33.11 slave
复制架构
M-S-S(多级复制架构)
2.master(192.168.33.13)节点配置
- 配置文件开启binlog
未配置之前检查,log_bin是OFF状态
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.01 sec)
#这是我mysql安装的位置
[root@localhost mysql]& pwd
/usr/local/mysql
vim /etc/my.cnf文件
//增加下面三行，我让log-bin指向我的mysql安装位置
server-id=1
binlog-format=Row //使用此模式时，Mysql实际上在binlog中逐行记录数据的变更。Row比Statement更能保证从库的一致性（复制的是记录，而非单纯操作sql），日志量也可能会增大非常多，需要考虑到磁盘空间问题
log-bin=/usr/local/mysql/binlog-files/mysql-bin
expire_logs_days = 10 //日志保留时间
重启mysql
[root@localhost mysql]& service mysqld restart
再次检查，log_bin是ON状态
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/mysql-bin |
| log_bin_index | /usr/local/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------+
6 rows in set (0.00 sec)
并且/usr/local/mysql/binlog-files目录会生成以下文件
log-bin
- 给relay创建使用的用户
mysql> CREATE USER 'relay'@'192.168.33.12' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'relay'@'192.168.33.12';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
注：
drop user 'repl'@'192.168.33.12'; //删除user用户语句
- 查看master状态,获取binlog名字和position。记住file名字和position，后面会用到。
这个时候master不要发生写操作，否则position和file可能会变化。
mysql>
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 761 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
3.relay(192.168.33.12)节点配置
vim /etc/my.cnf文件
server-id=2
binlog-format=ROW
log-bin=/usr/local/mysql/binlog-files/mysql-bin
log-slave-updates //配置从库上的更新操作是否写二进制日志，目的是作为slave的主库，搭建一个链式的复制
expire_logs_days = 10 //日志保留时间
重启mysql
[root@localhost mysql]& service mysqld restart
开启主从同步。file和pos是从主节点获取的
mysql> change master to master_host='192.168.33.13',
master_user='relay',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=761;
查看relay节点状态。注意，主从同步成功的标志：IO线程和SQL线程都是成功运行的：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.33.13
Master_User: relay
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 761
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
1 row in set (0.00 sec)
- 给slave创建使用的用户
mysql> CREATE USER 'slave'@'192.168.33.11' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.33.11';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
4.slave(192.168.33.11)节点配置
vim /etc/my.cnf文件
server-id=2
binlog-format=ROW
log-bin=/usr/local/mysql/mysql-bin
expire_logs_days = 10 //日志保留时间
重启mysql
[root@localhost mysql]& service mysqld restart
开启主从同步。file和pos是从主节点获取的
mysql> change master to master_host='192.168.33.12',
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=761;
查看从节点状态。注意，主从同步成功的标志：IO线程和SQL线程都是成功运行的：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.33.11
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 761
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
1 row in set (0.00 sec)
现在可以在master节点上面做任意对于库表的修改操作，slave会自动同步。
不要直接操作slave。
如果IO线程not running
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; //跳过一个错误
start slave;
show slave status\G
网友评论