mysql主从复制--mysql-5.6基于GTID及多线程复制

mysql主从复制--mysql-5.6基于GTID及多线程复制

标签：服务器   server   多线程   mysql   style   

小编还为您整理了以下内容，可能对您也有帮助：

mysql怎么实现主从复制

mysql主从复制的配置

1. 准备工作

有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器。

初始状态时，Master和slave中的数据信息相同，当Master中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。

要点：

负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。

因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

环境：

Master和slave的MySQL数据库版本同为5.6.28

操作系统：CentOS release 6.5 (Final)

IP地址：master:120.77.153.204 slave:120.77.148.74

2. master的配置

（1） 创建复制帐号

mysql> CREATE USER'masterslave'@'120.77.148.74' IDENTIFIED BY '123456';Query OK, 0 rows affected (0.00 sec)

（2） 主从复制授权

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'masterslave'@'120.77.148.74' IDENTIFIED BY '123456';

（3） 拷贝数据

（假如是你完全新安装mysql主从服务器，这一步就不需要。因为新安装的master和slave有相同的数据）

关停Master服务器，将Master中的数据拷贝到B服务器中，使得Master和slave中的数据同步，并且确保在全部设置操作结束前，

禁止在Master和slave服务器中进行写操作，使得两数据库中的数据一定要相同！

3. 修改配置文件

对master进行配置，包括打开二进制日志，指定唯一的servr ID

[root@myserver ~]# find / -type f -name my.cnf/usr/my.cnf[root@myserver ~]# vim /usr/my.cnfserver_id=1log-bin=mysql-bin

4. 重启master，运行SHOW MASTER STATUS，输出如下：

[root@myserver ~]# service mysql stopShutting down MySQL.... SUCCESS! [root@myserver ~]# service mysql startStarting MySQL. SUCCESS! [root@myserver ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 3Server version: 5.6.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> mysql> show master status; mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql>

配置slave

1. 修改my.cnf文件

[root@wenhaijin mysql-5.6.28]# find / -type f -name my.cnf/usr/my.cnf[root@wenhaijin mysql-5.6.28]# vim /usr/my.cnf log_bin = mysql-binserver_id = 2relay_log = mysql-relay-binlog_slave_updates = 1read_only = 1

server_id是必须的，而且唯一。

slave没有必要开启二进制日志，但是在一些情况下，必须设置。

例如，如果slave为其他slave的master，必须设置bin_log。

在这里，我们开启了二进制日志，而且显示了命名（默认名称为hostname，但是，如果hostname改变则会出现问题）。

relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志（后面会看到它的用处）。

有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。

所以，尽量使用read_only，它防止改变数据（除了特殊的线程）。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。

2. 启动slave

接下来就是让slave连接master，并开始重做master二进制日志中的事件。

你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。如下：

mysql> CHANGE MASTER TO MASTER_HOST='120.77.153.204', -> MASTER_USER='masterslave', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=0;Query OK, 0 rows affected, 2 warnings (0.01 sec)

MASTER_LOG_POS的值为0，因为它是日志的开始位置。

你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确：

mysql> SHOW SLAVE STATUSG*************************** 1. row *************************** Slave_IO_State: Master_Host: 120.77.153.204 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 4 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No 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: Skip_Counter: 0 Exec_Master_Log_Pos: 4 Relay_Log_Space: 120 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No

表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。

实际上，MySQL知道的第一个事件的位置是4。

为了开始复制，你可以运行START SLAVE；

mysql> START SLAVE; mysql> SHOW SLAVE STATUSG;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 120.77.153.204 Master_User: masterslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1762 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 1925 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: Skip_Counter: 0 Exec_Master_Log_Pos: 1762 Relay_Log_Space: 2098 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4df9a165-03fd-11e7-b234-00163e0243e8 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)

在这里主要是看：

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。

日志的位置增加了，意味着一些事件被获取并执行了。

如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。

你可通过show processlistG；查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接。

3. 授权masterslave用户远程登入，然后使用navicat远程连接

在master的test数据库中创建user表。

然后在slave中进行刷新发现user表已经同步过来。

刷新前：

刷新后：

在master的test库中user表添加一条数据。

然后打开slave中test库的user表，发现该数据也被同步过来了。

说明已经成功实现mysql的主从复制。

mysql怎么实现主从复制

mysql主从复制的配置

1. 准备工作

有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器。

初始状态时，Master和slave中的数据信息相同，当Master中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。

要点：

负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。

因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

环境：

Master和slave的MySQL数据库版本同为5.6.28

操作系统：CentOS release 6.5 (Final)

IP地址：master:120.77.153.204 slave:120.77.148.74

2. master的配置

（1） 创建复制帐号

mysql> CREATE USER'masterslave'@'120.77.148.74' IDENTIFIED BY '123456';Query OK, 0 rows affected (0.00 sec)

（2） 主从复制授权

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'masterslave'@'120.77.148.74' IDENTIFIED BY '123456';

（3） 拷贝数据

（假如是你完全新安装mysql主从服务器，这一步就不需要。因为新安装的master和slave有相同的数据）

关停Master服务器，将Master中的数据拷贝到B服务器中，使得Master和slave中的数据同步，并且确保在全部设置操作结束前，

禁止在Master和slave服务器中进行写操作，使得两数据库中的数据一定要相同！

3. 修改配置文件

对master进行配置，包括打开二进制日志，指定唯一的servr ID

[root@myserver ~]# find / -type f -name my.cnf/usr/my.cnf[root@myserver ~]# vim /usr/my.cnfserver_id=1log-bin=mysql-bin

4. 重启master，运行SHOW MASTER STATUS，输出如下：

[root@myserver ~]# service mysql stopShutting down MySQL.... SUCCESS! [root@myserver ~]# service mysql startStarting MySQL. SUCCESS! [root@myserver ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 3Server version: 5.6.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> mysql> show master status; mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql>

配置slave

1. 修改my.cnf文件

[root@wenhaijin mysql-5.6.28]# find / -type f -name my.cnf/usr/my.cnf[root@wenhaijin mysql-5.6.28]# vim /usr/my.cnf log_bin = mysql-binserver_id = 2relay_log = mysql-relay-binlog_slave_updates = 1read_only = 1

server_id是必须的，而且唯一。

slave没有必要开启二进制日志，但是在一些情况下，必须设置。

例如，如果slave为其他slave的master，必须设置bin_log。

在这里，我们开启了二进制日志，而且显示了命名（默认名称为hostname，但是，如果hostname改变则会出现问题）。

relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志（后面会看到它的用处）。

有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。

所以，尽量使用read_only，它防止改变数据（除了特殊的线程）。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。

2. 启动slave

接下来就是让slave连接master，并开始重做master二进制日志中的事件。

你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。如下：

mysql> CHANGE MASTER TO MASTER_HOST='120.77.153.204', -> MASTER_USER='masterslave', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=0;Query OK, 0 rows affected, 2 warnings (0.01 sec)

MASTER_LOG_POS的值为0，因为它是日志的开始位置。

你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确：

mysql> SHOW SLAVE STATUSG*************************** 1. row *************************** Slave_IO_State: Master_Host: 120.77.153.204 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 4 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No 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: Skip_Counter: 0 Exec_Master_Log_Pos: 4 Relay_Log_Space: 120 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No

表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。

实际上，MySQL知道的第一个事件的位置是4。

为了开始复制，你可以运行START SLAVE；

mysql> START SLAVE; mysql> SHOW SLAVE STATUSG;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 120.77.153.204 Master_User: masterslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1762 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 1925 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: Skip_Counter: 0 Exec_Master_Log_Pos: 1762 Relay_Log_Space: 2098 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4df9a165-03fd-11e7-b234-00163e0243e8 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)

在这里主要是看：

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。

日志的位置增加了，意味着一些事件被获取并执行了。

如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。

你可通过show processlistG；查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接。

3. 授权masterslave用户远程登入，然后使用navicat远程连接

在master的test数据库中创建user表。

然后在slave中进行刷新发现user表已经同步过来。

刷新前：

刷新后：

在master的test库中user表添加一条数据。

然后打开slave中test库的user表，发现该数据也被同步过来了。

说明已经成功实现mysql的主从复制。

mysql主从复制原理

主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。

对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

扩展资料

Mysq主从复制的类型

1、基于语句的复制：

主服务器上面执行的语句在从服务器上面再执行一遍，在MySQL-3.23版本以后支持。存在的问题：时间上可能不完全同步造成偏差，执行语句的用户也可能是不同一个用户。

2、基于行的复制：

把主服务器上面改变后的内容直接复制过去，而不关心到底改变该内容是由哪条语句引发的，在MySQL-5.0版本以后引入。

mysql主从复制原理

主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。

对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

扩展资料

Mysq主从复制的类型

1、基于语句的复制：

主服务器上面执行的语句在从服务器上面再执行一遍，在MySQL-3.23版本以后支持。存在的问题：时间上可能不完全同步造成偏差，执行语句的用户也可能是不同一个用户。

2、基于行的复制：

把主服务器上面改变后的内容直接复制过去，而不关心到底改变该内容是由哪条语句引发的，在MySQL-5.0版本以后引入。

Mysql主从复制详解

最近接的项目，帮人维护mysql服务器，临时来了个需求，帮忙搭一个主从复制，用于备机和备份使用，弄了半天，终于弄好了，但是觉得很多东西还是都忘记的差不多了，所以这里也记录一下。下次忘记了，不用在网上搜了。 主从复制目的： 主从服务器设置的稳健性得以提升，如果主服务器发生故障，可以把本来作为备份的从服务器提升为新的主服务器。 在主从服务器上分开处理用户的请求，读的话，可以直接读取备机数据，可获得更短的响应时间。 用从服务器做数据备份而不会占用主服务器的系统资源。 场景描述 主服务器：IP地址192.168.56.128，mysql已经安装，无用户数据。 从服务器：IP地址192.168.56.129，mysql已经安装。 主服务器的mysql服务已经正常启动。 主从复制配置 主服务器上进行操作 （1）创建用户并授权 1  mysql -uroot -p密码 2  mysqlgrant replication slave on *.* to rep1@192.168.56.129 identified by'mysql'; 3  mysqlflush privileges; （2）查询主数据库的状态 1  mysql show master status; 记下File以及Position的值，在后面进行从服务器操作的时候需要使用。 配置从服务器 （1）修改从服务器的配置文件/etc/my.cnf 使得“server-id=2”，并确定这个id没有被别的mysql服务所使用。 （2）启动mysql服务 1  mysqld_safe --user=mysql （3）mysql登录并执行同步的sql语句，最后并启动slave 1  #mysql -uroot -p 2  mysql change master to master_host='192.168.56.128',master_user='rep1',master_password='mysql',master_log_file='mysql-bin.000004',master_log_pos=477; 3  mysql start slave; 就启动了slave同步功能。 （5）主从同步检查 执行如下语句 1  mysqlshow slave status\G 来检查从服务器的同步情况，一个正常的输出结果应该如下面的形式： Slave_IO进程以及slave_SQL进程都必须正常运行，在状态输出中表现为：“slave”；否则都是不正确的状态（如一个值是Yes，另一个是No则不行）。 如果主数据库服务器已经存在用户数据，那么在进行主从复制时，需要做以下处理。 （1）主数据库锁表操作，不让数据再进行写入动作。 1  mysqlflush tableswithread lock; （2）查看主数据库的状态 1  mysqlshow master status; 记下File以及Position的值，以备从服务器使用。 （3）把主服务器的数据文件复制到从服务器，最好先用tar归档压缩处理一下 （4）取消主数据库锁定 1  mysqlunlock tables; 从服务器的操作和前面的步骤一样，略过。 实验一 在主数据库服务器上创建库和表，然后插入记录，再登录到从服务器，查看是否也建立相一致的库和表以及记录。 （1）主服务器上的操作 1  mysqlcreate database first_tb; ①在主数据库服务器创建库first_tb; 1  mysqlcreate table first_db(idint(3)),name char (10)); ②在主数据库服务器创建表first_tb; 1  mysqlinsert into first_tb values (001,'myself'); ③在主数据服务器的表first_tb中插入记录; （2）从服务器上查看 1  mysqlshow databases; ①数据库first_db自动生成 ②表first_tb也自动生成，数据记也录存在。 实验二 导入数据库，在主库上查看现在的数据库 创建数据库buy并且导入数据 在从库上查看，数据库已经完全导入。

MySQL如何实现主从复制过程的示例详解（图）

这篇文章主要为大家详细介绍了MySQL主从复制的实现过程，具有一定的参考价值，感兴趣的小伙伴们可以参考一下

一、什么是主从复制

将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

二、主从复制的作用

1、主数据库出现问题，可以切换到从数据库。

2、可以进行数据库层面的读写分离，

3、可以在从数据库上进行日常备份

三、复制过程

Binary log：主数据库的二进制日志

Relay log：从服务器的中继日志

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。

第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog mp process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

四、主从复制的具体操作

我是在同一个windows上不同的路径下安装两个msyql实例。建议这里主从两个mysql的安装版本一致，尽管我自己的是不一致的。

1、分别修改主从数据库的配置文件my.ini

master

3306是mysql默认端口号，这里master实例中可以不用修改；server-id用来指定唯一id，不同的mysql实例不重复即可；binlog-do-db指定需要复制的数据库；log-bin用来开启二进制日志文件。

salve

由于主从数据库待会都是在通一台电脑上运行，所以端口需要设置成不一样，这里是3307

replicate-do-db：需要同步的数据库名称，与master上的配置保持一致。

2、在master上创建一个专门用于复制的账户：weidai/123456

这个新增的账户可以在表mysql.user中进行查询：

我第一次操作的时候，到这里就完成了这个账号的创建，但是到真正复制的时候，却发现复制没有成功，排查错误的时候发现master生成的binlong没有问题，然后查看slave的状态：

在结尾处有这样一行错误：

使用weidai这个账号无法连接到master，所以应该是没有获取到master的binlog，导致中继日志无法生成。

我反复检查了账号和密码都没有发现问题，然后查找相关资料，才发现是因为在master创建新用户的时候少了一步操作：

新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问。这就是出现前面错误的原因。另外还有一种办法是重新启动mysql服务器，来使新设置生效。

3、获取主数据库中此刻数据的位置，主要用于从数据启动后，复制数据的起始位置，但是在获取这个状态值之前，主数据库就不能再有数据的修改操作，所以需要先设置读锁定有效

4、主库进行数据备份，备份的手段有很多种，这里不展开介绍，可以参考我上一篇文章，备份结束后可以释放读锁，主库就可以进行写操作

5、启动从数据库，对刚才备份的数据进行还原，这个时候主从数据库在备份那个时间点的数据是一致的。

6、在从数据库上进行复制行为的相关配置

7、这个时候配置完成，但是从数据库还不能进行同步，需要启动slave线程

8、在master中创建表和新增数据，在slave中观察：

可以看出，我在master中进行的操作，都能在slave中体现出来，这个时候slave就如同是master的镜子一样。

五、主从同步状态解读

在slave上使用命令进行查看：

由于排版太过于难看，我整理如下：

Slave_IO_STATE:Waiting for master to send event

Master_host:127.0.0.1

Master_user:weidai

Master_port:3306

connnect_retry:60

Master_log_file:mysql-bin.000005

Read_Master_log_pos:1662

Relay_log_file:AE6Z*****-relay-bin.000002

Relay_log_pos:1415

Slave_IO_Running:yes

Slave_SQL_Running:yes----------------------------------------------------------华丽丽分割线-------------------------------------------

Slave_IO_Running:yes

Slave_SQL_Running:yes

这两个线程前面有提到，是slave上参与复制过程中两个很重要的线程。YES表示正常，NO表示异常。

Slave_IO线程主要是将master上的binlong日志内容复制到slave的中继日志中（Relay_log），一般出现问题的概率不大， 出现问题大多数是因为权限或者网络等问题，导致连接不上master。如同前面提到的那个错误。

Slave_SQL线程负责将中继日志中的SQL执行一遍，相对来说出错的概率大些。如有人手动的在从库中插入一些记录，导致主从同步的时候出现主键冲突。

Slave_IO_STATE:Waiting for master to send event

这个状态表示中继日志同步完成，等待master有新的事件产生。