MySQL数据库复制的三种方法mysql三种复制方式

一、MySQL数据库复制的三种方法mysql三种复制方式

MySQL数据库的复制是指将一个MySQL数据库的全部或部分数据复制到另一个MySQL数据库中。MySQL数据库复制可以用于数据备份、测试和负载均衡等方面。在这篇文章中，我们将介绍MySQL数据库复制的三种方法。

方法一：基于二进制日志文件的复制（Binary Log Replication）

二进制日志文件是MySQL数据库中记录所有SQL语句的二进制格式的文件。基于二进制日志文件的复制是指将一个MySQL服务器的二进制日志文件复制到另一个MySQL服务器上，并且在另一个MySQL服务器上执行相同的SQL语句，从而复制原始数据库。这种方法需要在两台服务器之间通过网络进行二进制日志传输。

实现基于二进制日志文件的复制，需要在原始MySQL服务器和目标MySQL服务器上分别进行以下步骤：

1.在原始MySQL服务器上，打开二进制日志记录功能，并设置二进制日志文件的名称和位置。可以通过在my.cnf文件中添加以下行来完成此操作：

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

2.在目标MySQL服务器上，设置server-id。可以通过在my.cnf文件中添加以下行来完成此操作：

3.在目标MySQL服务器上，使用CHANGE MASTER TO命令配置从原始MySQL服务器复制数据。例如：

MASTER_HOST=’192.168.0.1′,

MASTER_USER=’replication’,

MASTER_PASSWORD=’123456′,

MASTER_LOG_FILE=’mysql-bin.000001′,

4.在目标MySQL服务器上，执行START SLAVE命令开始复制数据。例如：

方法二：基于全局事务标识符的复制（GTID Replication）

全局事务标识符（GTID）是MySQL服务器为每个事务分配的唯一标识符。GTID标识符可以跨多个MySQL服务器进行跟踪，从而简化了MySQL复制拓扑的管理。基于GTID的复制可以保证复制的事务在原始MySQL服务器和目标MySQL服务器之间是唯一的，并且可以减少由于网络故障导致的数据不一致性。

实现基于GTID的复制，需要在原始MySQL服务器和目标MySQL服务器上分别进行以下步骤：

1.在原始MySQL服务器上，打开GTID模式。可以通过在my.cnf文件中添加以下行来完成此操作：

2.在目标MySQL服务器上，设置server-id和GTID执行器。可以通过在my.cnf文件中添加以下行来完成此操作：

3.在目标MySQL服务器上，使用CHANGE MASTER TO命令配置从原始MySQL服务器复制数据。例如：

MASTER_HOST=’192.168.0.1′,

MASTER_USER=’replication’,

MASTER_PASSWORD=’123456′,

4.在目标MySQL服务器上，执行START SLAVE命令开始复制数据。例如：

方法三：基于多源复制（Multi-Source Replication）

MySQL 5.7引入了多源复制功能，可以将多个原始MySQL服务器的数据复制到一个目标MySQL服务器上。这种方法可以提高数据复制的效率，减少网络带宽的占用，并且可以支持异构数据库复制。多源复制可以通过使用CHANGE REPLICATION SOURCE命令来添加或删除原始MySQL服务器。

实现多源复制，需要在目标MySQL服务器上分别进行以下步骤：

1.在目标MySQL服务器上，打开gtid_mode和enforce_gtid_consistency模式。可以通过在my.cnf文件中添加以下行来完成此操作：

2.使用CHANGE REPLICATION SOURCE命令添加原始MySQL服务器。例如：

SOURCE_HOST=’192.168.0.1′,

SOURCE_USER=’replication’,

SOURCE_PASSWORD=’123456′;

3.在目标MySQL服务器上，执行START REPLICA命令开始复制数据。例如：

MySQL数据库的复制是MySQL服务器的一个重要功能，可以帮助用户实现数据备份、测试和负载均衡等方面的需求。本文介绍了MySQL数据库复制的三种方法，包括基于二进制日志文件的复制、基于全局事务标识符的复制和基于多源复制。根据实际需求选择适合的方法进行MySQL数据库的复制。

二、怎么复制MySQL数据库

项目上 MySQL还原 SQL备份经常会碰到一个错误如下，且通常出现在导入视图、函数、存储过程、事件等对象时，其根本原因就是因为导入时所用账号并不具有SUPER权限，所以无法创建其他账号的所属对象。ERROR 1227(42000): Access denied; you need(at least one of) the SUPER privilege(s) for this operation常见场景：1.还原 RDS时经常出现，因为 RDS不提供 SUPER权限；2.由开发库还原到项目现场，账号权限等有所不同。

1.在原库中批量修改对象所有者为导入账号或修改SQL SECURITY为Invoker；2.使用 mysqldump导出备份，然后将 SQL文件中的对象所有者替换为导入账号。

二、问题原因我们先来看下为啥会出现这个报错，那就得说下 MySQL中一个很特别的权限控制机制，像视图、函数、存储过程、触发器等这些数据对象会存在一个DEFINER和一个SQL SECURITY的属性，如下所示：

--视图定义CREATEALGORITHM=UNDEFINEDDEFINER=`root`@`%`SQLSECURITYDEFINERVIEWv_test

--函数定义CREATEDEFINER=`root`@`%`FUNCTION`f_test()`RETURNSvarchar(100)SQLSECURITYDEFINER

--存储过程定义CREATEDEFINER=`root`@`%`PROCEDURE`p_test`()SQLSECURITYDEFINER

--触发器定义CREATE DEFINER=`root`@`%` trigger t_test

--事件定义CREATE DEFINER=`root`@`%` EVENT `e_test`

DEFINER：对象定义者，在创建对象时可以手动指定用户，不指定的话默认为当前连接用户；

SQL SECURITY：指明以谁的权限来执行该对象，有两个选项，一个为DEFINER，一个为INVOKER，默认情况下系统指定为 DEFINER；DEFINER：表示按定义者的权限来执行；INVOKER：表示按调用者的权限来执行。

如果导入账号具有 SUPER权限，即使对象的所有者账号不存在，也可以导入成功，但是在查询对象时，如果对象的SQL SECURITY为DEFINER，则会报账号不存在的报错。ERROR 1449(HY000): The user specified as a definer('root'@'%') does not exist

改写好处：1.可以避免还原时遇到 DEFINER报错相关问题；2.根据输出信息知道备份是否正常进行，防止备份中遇到元数据锁无法获取然后一直卡住的情况。

三、mysql 8.0数据库迁移(直接复制文件)

caching_sha2_password认证插件提供更多的密码加密方式，并且在加密方面具有更好的表现，目前MySQL8.0选用caching_sha2_password作为默认的认证插件，MySQL5.7的认证插件是MySQL_native_password。如果客户端版本过低，会造成无法识别MySQL8.0的加密认证方式，最终导致连接问题。

MySQL存储引擎现在负责提供自己的分区处理程序，而MySQL服务器不再提供通用分区支持，InnoDB和NDB是唯一提供MySQL8.0支持的本地分区处理程序的存储引擎。如果分区表用的是别的存储引擎，存储引擎必须进行修改。要么将其转换为InnoDB或NDB，要么删除其分区。通过MySQLdump从5.7获取的备份文件，在导入到8.0环境前，需要确保创建分区表语句中指定的存储引擎必须支持分区，否则会报错。

MySQL8.0的默认字符集utf8mb4，可能会导致之前数据的字符集跟新建对象的字符集不一致，为了避免新旧对象字符集不一致的情况，可以在配置文件将字符集和校验规则设置为旧版本的字符集和校验规则。

MySQL8.0启动使用的lower_case_table_names值必须跟初始化时使用的一致。使用不同的设置重新启动服务器会引入与标识符的排序和比较方式不一致的问题。

<lower_case_table_names>

要避免MySQL8.0上的启动失败，MySQL配置文件中的sql_mode系统变量不能包含NO_AUTO_CREATE_USER。

从MySQL5.7.24和MySQL8.0.13开始，MySQLdump从存储程序定义中删除了NO_AUTO_CREATE_USER。必须手动修改使用早期版本的MySQLdump创建的转储文件，以删除NO_AUTO_CREATE_USER。

在MySQL8.0.11中，删除了这些不推荐使用的兼容性SQL Mode：DB2，MAXDB，MSSQL，MySQL323，MySQL40，ORACLE，POSTGRESQL，NO_FIELD_OPTIONS，NO_KEY_OPTIONS，NO_TABLE_OPTIONS。从5.7到8.0的复制场景中，如果语句使用到废弃的SQL Mode会导致复制异常。

在执行到MySQL8.0.3或更高版本的in-place升级时，BACKUP_ADMIN权限自动授予具有RELOAD权限的用户。

本文对MySQL 5.7到MySQL 8.0的升级过程中出现部分易出现问题进行整理：升级对MySQL版本的要求、升级都做了哪些内容、数据库升级做了哪些步骤以及注意事项，希望对大家版本升级有帮助。