看完这篇，学会MySQL数据复制（含配置教程）

1.简介

MySQL 是企业应用程序中使用最多的SQL数据库之一，其能够管理事务和内核中的ACID行为，且数据库本身的使用及相关命令的使用都很便利。

在开源Web应用程序框架LAMP(包括Linux，Apache，MySQL和PHP)中，MySQL服务器是一个核心和重要的组件。MySQL数据库服务器使用C和C ++编写的，内部使用词法分析器来解析和理解SQL查询。

随着系统变得分散、可扩展且高度容错时，我们越来越无法承受数据库中的故障，例如数据库服务器发生故障且无法自动管理。所以，本文就将和大家讨论一下数据库复制。

当系统的MySQL数据库发生故障，利用数据库复制我们可以转移到其副本并从中管理数据，甚至用户都感知不到数据库中发生了错误。不同的企业使用数据库复制的初衷包括但不限于以下原因：

• 确保直接从数据库备份数据

• 在不干扰主数据库的情况下运行分析或检查数据

• 扩展数据库以获得更好的性能

2. MySQL设置

我们创建了两个具有不同IP的新服务器，在副本集中将其分别用作主服务器和从服务器。为了进一步研究，我们在它们上面设置了MySQL服务器和客户端工具。

安装MySQL服务器和客户端：

sudo apt-get install mysql-server mysql-client

运行此命令后，服务器上即安装了上述程序，然后在两台服务器上进行相同的配置并设置MySQL root密码：

设置Root密码

安装过程完成后，使用以下命令确认MySQL服务器是否已启动并运行：

sudo service mysql status

输出：

检查MySQL服务器状态

MySQL服务器已启动并运行，使用在安装过程中的用户名和密码连接。

登录MySQL

mysql -u root -p

此时，MySQL服务器会等待我们输入密码，出于安全考虑，密码不会回显给终端。登录MySQL命令行后，会出现以下提示：

MySQL登录

进入MySQL命令提示符时，我们可以使用给定的命令来显示系统中存在的数据库并确保MySQL运行正常：

显示所有数据库

show databases;

输出：

检查MySQL数据库

在输出中，MySQL只显示用于管理目的的MySQL默认数据库列表。只要在两台服务器上看到Active状态，我们就可以继续进行Master和Slave数据库的配置。

3.掌握MySQL服务器配置

MySQL安装完之后，我们就可以进行master数据库的配置，即在主MySQL配置文件中添加配置，在Ubuntu上使用nano编辑器打开并执行以下命令：

编辑配置文件

sudo nano /etc/mysql/mysql.conf.d/my.cnf

该文件包含许多选项，利用它们可以修改和配置在系统上运行的MySQL服务器的行为。首先，我们需要在文件中找到bind-address属性：

绑定地址属性

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address = 127.0.0.1

将此IP修改为当前服务器IP：

更新Bind Address属性

bind-address = <server_ip_here>

查看server-id属性：

服务器ID属性

# note: if you are setting up a replication slave, see README.Debian about

# other settings you may need to change.

#server-id = 1

更新服务器ID属性

server-id = 1

log_bin属性通知实际保存副本集详细信息的文件。

Log Bin属性

log_bin = /var/log/mysql/mysql-bin.log

在这个文件中，从服务器记录它从主数据库中容纳的变化。现在我们将取消对属性的注释，编辑binlog_do_db属性，该属性通知从数据库服务器在从数据库中复制哪个数据库。我们可以通过对我们需要的所有数据库重复此行来包含多个数据库：

DB备份：

binlog_do_db = jcg_database

配置文件中显示的更新属性：

更新了配置文件

完成所有属性后，我们可以保存文件并重新启动MySQL服务器，以便这些更新反映在服务器中。要重新启动MySQL服务器，请运行以下命令：

重启MySQL：

sudo service mysql restart

一旦MySQL服务器重新启动，我们需要做的下一个更改是在MySQL shell本身内部。再次登录MySQL命令行。

授权给Slave DB，以便它可以访问和复制我们在配置文件中提到的数据库中的数据jcg_database。

授予权限

GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

刷新权限：

FLUSH PRIVILEGES;

切换到创建之后要复制的数据库：

mysql> CREATE SCHEMA jcg_database;

Query OK, 1 row affected (0.00 sec)

mysql> USE jcg_database;

Database changed

锁定数据库，禁止更改：

Read Lock：

FLUSH TABLES WITH READ LOCK;

在应用锁之前，我们需要制定一些新表并插入数据。

检查主状态

SHOW MASTER STATUS;

输出：

主数据库状态

需要注意的是，因为这是从属DB开始复制数据库的位置。如果我们对DB进行任何更改，它将自动解锁，所以不要在同一窗口中进行任何新的更改。下一部分有点棘手，打开一个新的终端窗口或选项卡(不关闭当前选项卡)并登录MySQL服务器并执行以下命令：

转储MySQL

mysqldump -u root -p --opt jcg_database > jcg_database.sql

输出：

MySQL转储

退出单独打开的新选项卡并返回到旧选项卡。在该选项卡上，解锁数据库并退出MySQL：

解锁并退出

UNLOCK TABLES;

QUIT;

如此，我们就完成了在master数据库上所需的所有配置。

4.从属MySQL服务器配置

现在，我们准备开始配置复制数据的从数据库，登录Slave服务器并在其中打开MySQL命令行。创建一个具有相同名称的数据库，复制并退出MySQL终端：

MySQL Slave DB

使用我们制作的SQL文件将原始数据库导入Slave MySQL服务器，确保将该文件带到此新服务器上并运行以下命令将其导入到从属MySQL数据库中：

导入数据库

mysql -u root -p jcg_database < /root/jcg_database.sql

点击Enter后，数据库内容和元数据将导入从数据库。完成之后，我们也可以配置Slave MySQL DB：

配置DB

nano /etc/mysql/mysql.conf.d/mysqld.cnf

我们需要确保此配置文件中的某些属性已设置，server-id设置的默认值为1，也可利用下面命令设置为其它值：

这个财产是server-id。它当前设置为1，这是默认值。将其设置为其他值：

Server ID for Slave

server-id = 2

Slace的其他属性设置：

relay-log = /var/log/mysql/mysql-relay-bin.log

log_bin = /var/log/mysql/mysql-bin.log

binlog_do_db = jcg_database

添加relay-log属性，因为默认情况下它不在配置文件中。完成此操作后，还需要重新启动Slave MySQL DB，配置更改才能生效。

重启MySQL

sudo service mysql restart

一旦MySQL服务器重新启动，我们需要做的下一个更改是在MySQL shell本身内部。所以再次登录MySQL命令行。

在MySQL shell中，执行以下命令：

启用复制

CHANGE MASTER TO MASTER_HOST='<master-server-ip>',MASTER_USER='root', MASTER_PASSWORD='hello123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 1306;

此命令一次完成各个步骤，包括：

• 通知当前MySQL服务器，它是给定的MySQL主服务器的Slave

• 为Slave提供了Master Server的登录凭据

• 通知Slave需要启动复制过程的位置以及日志文件详细信息

使用以下命令最终激活从服务器：

激活MySQL Slave Server

START SLAVE;

使用以下命令查看一些主要细节：

MySQL主状态

SHOW SLAVE STATUS\G;

输出：

MySQL主状态信息

mysql> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 206.189.133.122

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 1306

Relay_Log_File: mysql-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:

Skip_Counter: 0

Exec_Master_Log_Pos: 1306

Relay_Log_Space: 527

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: 0

Master_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: 3b890258-be5d-11e8-88c2-422b77d8526c

Master_Info_File: /var/lib/mysql/master.info

Slave_SQL_Running_State: Slave has read all relay log;

waiting for more updates

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: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

如果在连接时出现问题，可以尝试使用命令启动slave：

MySQL主状态

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

这样，我们就完成了MySQL复制的配置，数据正在MySQL服务器上复制，并尝试将一些数据插入Master数据库，并检查数据是否也复制到从数据库。

5.复制滞后

MySQL复制利用两个线程来完成主数据库和从属数据库之间的复制：

1. IO_THREAD

2. SQL_THREAD

IO_THREAD连接到主MySQL服务器，读取二进制日志以跟踪和更改数据库中的事件，将它们复制到本地中继日志文件，Slave数据库的SQL_THREAD读取并跟踪更改，将它们复制到Slave数据库。

如果我们观察到任何复制延迟，首先要确定此延迟是来自Slave的IO_THREAD还是Slave的SQL_THREAD。

通常，I / O线程不会导致任何重大的复制延迟，因为它只是从主数据库读取二进制日志，但有些因素会影响其性能，如网络连接，网络延迟以及通信网络的速度等等。如果Master上存在大量写入，由于带宽问题，复制可能会很慢。

另一方面，如果SQL线程在Slave延迟了，那么最可能的原因是主数据库的SQL查询需要在Slave数据库执行执行较长时间。另外， MySQL 5.6之前slave是单线程的，这也是导致从属SQL_THREAD延迟的另一个原因。

6.复制的优点

MySQL复制在生产环境中具备一些明显优势：

• 性能：Slave服务器可以很容易地用于向任何请求数据的客户端提供READ支持。这意味着Master数据库上的负载会减少很多，因为没有对它进行读取。

• 备份性能：如果有任何运行的备份任务，则可以在复制数据时通过Slave数据库运行它。这意味着备份作业根本不会影响Master数据库。

• 灾难恢复：在Master数据库完全脱机的事件中，如果以这种方式配置，Slave数据库可以快速取代它并开始执行写操作。这将允许在重建和恢复主服务器时最小的站点停机时间。

7.复制的缺点

从上文看下来，MySQL Replication是很不错的，但是它也有很多缺点：

• 复杂性：如果管理不正确，具有大量Slave进行复制的应用程序可能会造成维护噩梦。

• 性能：要完成复制过程，需要将二进制日志写入磁盘，尽管它的影响可能很小，但是在查看整体服务器性能时仍需要考虑。可以通过将二进制日志写入磁盘的单独分区来解决，以限制IO性能问题。

8.复制的局限性

除了上述内容，还有一些数据复制的限制点需要说明：

• 复制不是应用程序逻辑的备份，并且在Master数据库上执行的任何更改将始终复制到Slave数据库，并且不能限制它。如果用户删除master数据库上的数据，它也将在Slave数据库中删除。

• 在多个Slaves的情况下，性能不会增加，反而会降低，因为数据库连接分布在多个服务器上，并且在任何服务器发生故障时出现问题的风险都会增加。

9. MySQL复制的类型

从本质上讲，MySQL支持三种不同的方法将数据从主服务器复制到从属服务器。所有这些方法都使用二进制日志，但它与日志的写入方式不同。以下是复制的方法：

• 基于语句的复制：使用此方法，数据库中每次更改的SQL语句都存储在二进制日志文件中。从属设备将读取这些SQL语句并在自己的MySQL数据库上执行它们，以便从主服务器生成完全相同的数据副本。这是MySQL 5.1.11和MySQL 5.1.29中的默认复制方法。

• 基于行的复制：在此方法中，二进制日志文件存储主数据库表中发生的所有记录级更改。从服务器读取此数据并根据主数据更新其自己的记录，以生成主数据库的精确副本。

• 混合格式复制：在此方法中，服务器将在基于语句的复制和基于行的复制之间动态选择，具体取决于某些条件，如使用用户定义的函数(UDF)，使用带DELAYED子句的INSERT命令，临时表，或使用使用系统变量的语句。这是MySQL 5.1.12到MySQL 5.1.28中的默认复制方法。

在用例中，当你不确定要使用哪种复制方法时，最好使用基于语句的复制，因为它是最常用和最简单的执行方式。如果你有一个写入繁重的系统，则不建议使用基于语句的复制，因为它也应用表锁。在这种情况下，可以使用基于行的复制方法。

10.对业绩的影响

如前所述，复制可能会影响数据库的性能，但与其他事情相比，复制对主服务器的影响通常非常小，因为master只需要在复制环境中完成两件重要事情：

• 制定事件并将事件写入本地硬盘驱动器上的二进制日志

• 将它写入二进制日志的每个事件副本发送给每个连接的从站

即使没有复制，二进制日志也是要始终打开的，所以在考虑复制成本时也不需要列入二进制日志。

另外，复制事件发送到从设备的成本也可以忽略不计，因为从设备负责维护与主设备的持久TCP连接，主设备只需在事件发生时将数据复制到套接字上。除此之外，主设备丝毫不关心从设备是否或合适执行。

最后一条语句的部分异常是半同步复制，这不是默认值。在这种模式下，主服务器等待至少一个从服务器确认来自每个事务的二进制日志事件的接收和持久存储(尽管不是实际执行)，然后主服务器在每次提交时将控制权返回给客户端。

在任何情况下，主服务器都不负责实际执行从服务器上的更新，它只向从服务器发送两件事：运行的实际输入查询的副本(基于语句的模式)或数据对于每个查询实际插入/更新/删除的行(在基于行的模式下)。在混合模式下，查询优化器将决定在每个事件的基础上使用哪种格式。

11. 综述MySQL复制

在确保生产系统运行时具有故障转移可靠性使其成为容错系统时，MySQL Replication是一个很好的选择，同时这也是当今分布式和高可用系统必须具备的。

本文向大家介绍了在单个从属服务器上复制数据需要进行的重要的配置更改、系统更改。当然，因为主服务器上没有和从服务器相关或绑定的配置，所以我们可以在不影响主服务器的情况下设置任意数量的从服务器。