mysql主从同步完成标志,centos7 mysql 主从复制

1. 环境

操作系统

CentOS-7

MySQL

mysql-5.7

三台虚拟机

192.168.33.13      master

192.168.33.12      slave

192.168.33.11      slave

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

log-bin=/usr/local/mysql/mysql-bin

重启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)

并且mysql安装目录会生成以下文件

log-bin

给slave创建使用的用户

mysql> CREATE USER 'repl'@'192.168.33.%' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.33.%';

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.salve(192.168.33.12和192.168.33.11)节点配置

vim /etc/my.cnf文件

server-id=2

binlog-format=ROW

log-bin=/usr/local/mysql/mysql-bin

重启mysql

[root@localhost mysql]& service mysqld restart

开启主从同步。file和pos是从主节点获取的

mysql> change master to master_host='192.168.33.13',

master_user='repl',

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.13

Master_User: repl

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)

另一个节点同样操作，只需要将/etc/my.cnf文件中的server-id修改以下即可

现在可以在master节点上面做任意对于库表的修改操作，slave会自动同步。

不要直接操作slave。

如果IO线程not running：

stop slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

start slave;

show slave status\G