分类： MySQL

解决mysql导入数据文件过慢的问题

目前遇到一个问题，mysql 使用 source 命令导入  *.sql  数据文件时，运行的很慢，大概一秒钟插入一两百条左右的样子，对于大的文件来说这个太慢了。

1.登入mysql

2.查看mysql中对于参数 innodb_flush_log_at_trx_commit 的配置

3.修改

修改完成后在次执行相同的文件，200M大约200w+条的数据在1分钟左右。

对于该参数的不同值的说明：

1.innodb_flush_log_at_trx_commit参数为 0
        binlog_group_flush && thd_flush_log_at_trx_commit(NULL) == 0 条件成立，因此直接return了，那么这种情况下log_buffer_flush_to_disk函数不会调用，因此不会做redo刷盘。依赖master线程。
    2.innodb_flush_log_at_trx_commit参数为 1
        !binlog_group_flush || thd_flush_log_at_trx_commit(NULL) == 1 返回为1即为True，因此调用log_buffer_flush_to_disk(True)，因此需要做redo刷盘，也要做sync。
    3.innodb_flush_log_at_trx_commit参数为 2
        !binlog_group_flush || thd_flush_log_at_trx_commit(NULL) == 1 返回为0即为Flase，因此调用log_buffer_flush_to_disk(Flase)，因此需要做redo刷盘，不做sync。依赖OS的刷盘机制。

参考例子如下：

实际测试结果感觉还是不够快。

参考链接

PHP 7.x连接MySQL 8.x报错“The server requested authentication method unknown to the client [caching_sha2_password]”

在PHP 7.x使用如下代码连接MySQL 8.x

结果出现如下错误信息

原因为 MySQL 支持 caching_sha2_password  / 用户名密码 两种验证方式，但是从 MySQL 8 开始默认使用caching_sha2_password 的验证方式，导致以前版本的客户端不能成功连接。更详细的解释如下：

The server validates the user and returns the connection status. MySQL uses caching_sha2_password and auth_socket plugins for validation.

The caching_sha2_password plugin uses an SHA-2 algorithm with 256-bit password encryption. MySQL 8 prefers this auth method.

Whereas the auth_socket plugin checks if the socket username matches with the MySQL username. If the names don’t match, it checks for the socket username of the mysql.user. If a match is found, the plugin permits the connection.

But to serve the pre 8.0 clients and avoid compatibility errors, it is preferred to revert back the auth method. Older versions of MySQL use mysql_native_password plugin for validation.

解决方案为修改用户默认的认证方式，如下：

参考链接

[RESOLVED] MySQL the server requested authentication method unknown to the client

Error 'Character set '#255' is not a compiled ch aracter set and is not specified in the '/usr/local/mariadb10/share/mysql/charse ts/Index.xml' file' on query. Default database: 'wordpress'. Query: 'BEGIN'

以前通过 家里ADSL上网无固定外网IP的群晖NAS安全实现与公网MySQL服务器主从同步 配置之后，群晖自带的 MariaDB 10.3.21 可以非常流畅的与服务器上的 MySQL 7.x 版本进行主从同步。

前两天系统从 ubuntu 18.04 升级到 ubuntu 20.04 (MySQL 8.x)之后，发现已经无法进行主从同步。

报告如下错误：

网上查询很久，找到原因 MySQL 8.016 master with MariaDB 10.2 slave on AWS RDS, Character set '#255' is not a compiled character set

This could be a serious problem when replicating between MySQL 8.0 and MariaDB 10.x.

The default (for good technical reasons) COLLATION for the 8.0 is utf8mb4_0900_ai_ci. Note the "255" associated with it. MariaDB has not yet adopted the Unicode 9.0 collations.

Furthermore, Oracle (MySQL 8.0) did a major rewrite of the collation code, thereby possibly making collation tables incompatible.

A probable fix is to switch to the next best general-purpose collation, utf8mb4_unicode_520_ci (246) (based on Unicode 5.20). This would require ALTERing all the columns' collations. ALTER TABLE .. CONVERT TO .. might be the fastest way. Those could be generated via a SELECT .. information_schema.tables ....

大致原因就是MySql 8.0默认使用了最新的utf8mb4_0900_ai_ci字符集，然而MariaDB 10.3.x版本不支持这个字符集，导致无法同步。

8.0.17:

MariaDB 10.2.30:

目前暂时没打算修改主服务器上的数据库，暂时等 MariaDB 更新吧。

参考链接

MySQL延时复制(Delayed Replication)

延迟复制简介

即使通常MySQL复制很快，但MySQL缺省的复制存在延迟，并且用户无法缩短延迟时间。另一方面，有时却需要特意增加复制的延迟。设想这样一种场景，用户在主库上误删除了一个表，并且该操作很快被复制到从库。当用户发现这个错误时，从库早就完成了该事件重放。此时主库、从库都没有那个被误删的表了，如何恢复？如果有备份，可以幸运地从备份恢复，丢失的数据量取决于备份的新旧和从备份时间点到表被删除时间点之间该表上数据的变化量。如果没有备份呢？这种情况下，延迟复制或许可以帮上忙，作为一种恢复数据的备选方案。如果在发现问题时，从库还没有来得及重放相应的中继日志，那么就有机会在从库获得该表，继而进行恢复。这里忽略一些其它数据恢复方案，例如已经存在类似Oracle闪回技术（Flashback）在MySQL上的实现，实现方式为解析相应的二进制日志事件，生成反向的SQL语句。这些程序多为个人作品，并没有被加入MySQL发行版本中，因此在易用性、适用性、可靠性等方面还不能与原生的功能相提并论。

        MySQL支持延迟复制，以便从库故意执行比主库晚至少在指定时间间隔的事务。在MySQL 8.0中，延迟复制的方法取决于两个时间戳：immediate_commit_timestamp和original_commit_timestamp。如果复制拓扑中的所有服务器都运行MySQL 8.0.1或更高版本，则使用这些时间戳测量延迟复制。如果从库未使用这些时间戳，则执行MySQL 5.7的延迟复制。

        复制延迟默认为0秒。使用CHANGE MASTER TO MASTER_DELAY = N语句将延迟设置为N秒。从主库接收的事务比主库上的提交至少晚N秒才在从库上执行。每个事务发生延迟（不是以前MySQL版本中的事件），实际延迟仅强制在gtid_log_event或anonymous_gtid_log_event事件上。二进制日志中的每个GTID事务始终都以Gtid_log_event开头，匿名事务没有分配GTID，MySQL确保日志中的每个匿名事务都以Anonymous_gtid_log_event开头。对于事务中的其它事件，不会对它们施加任何等待时间，而是立即执行。注意，START SLAVE和STOP SLAVE立即生效并忽略任何延迟，RESET SLAVE将延迟重置为0。       

例如，下面将实验环境中一主两从半同步复制中的一个从库设置为延迟60秒复制：

        联机设置延迟复制时，需要先停止sql_thread线程。现在主库执行一个事务，观察从库的变化：

        主库上建立了一个表test.t3，DDL语句自成一个事务。60秒后，从库上才出现该表。

        从库上performance_schema模式下的replication_applier_configuration.desired_delay表列显示使用master_delay选项配置的延迟，replication_applier_status.remaining_delay表列显示剩余的延迟秒数。

        延迟复制可用于多种目的：

  • 防止用户在主库上出错。延迟复制时，可以将延迟的从库回滚到错误之前的时间。
  • 测试滞后时系统的行为方式。例如，在应用程序中，延迟可能是由从库设备上的重负载引起的。但是，生成此负载级别可能很困难。延迟复制可以模拟滞后而无需模拟负载。它还可用于调试与从库滞后相关的条件。
  • 检查数据库过去的快照，而不必重新加载备份。例如，通过配置延迟为一周的从库，如果需要看一下最近几天开发前的数据库样子，可以检查延迟的从库。

延迟复制的简单理解就是，客户端始终保持每条数据的同步时间不低于指定的间隔，同步是持续的。跟计划任务不同，想达到跟计划任务一样，每隔一段时间，集中执行一次是做不到的，比如一小时执行同步一次，这个是做不到的。想实现定时同步，还是写计划任务吧。

延迟复制时间戳

        MySQL 8.0提供了一种新方法，用于测量复制拓扑中的延迟，或称复制滞后。该方法取决于与写入二进制日志的每个事务（不是每个事件）的GTID相关联的以下时间戳：

  • original_commit_timestamp：将事务写入（提交）到主库二进制日志之后的自1970年1月1日00:00:00 UTC以来的微秒数。
  • immediate_commit_timestamp：将事务写入（提交）到从库的二进制日志之后的自1970年1月1日00:00:00 UTC以来的微秒数。

        mysqlbinlog的输出以两种格式显示这些时间戳，从epoch开始的微秒和TIMESTAMP格式，后者基于用户定义的时区以获得更好的可读性。例如：

        通常，original_commit_timestamp在应用事务的所有副本上始终相同。在主从复制中，主库二进制日志中事务的original_commit_timestamp始终与其immediate_commit_timestamp相同。在从库的中继日志中，事务的original_commit_timestamp和immediate_commit_timestamp与主库的二进制日志中的相同，而在其自己的二进制日志中，事务的immediate_commit_timestamp对应于从库提交事务的时间。

        在组复制设置中，当原始主服务器是组的成员时，将在事务准备好提交时生成original_commit_timestamp。再具体说，当事务在原始主服务器上完成执行并且其写入集准备好发送给该组的所有成员以进行认证时，生成original_commit_timestamp。因此，相同的original_commit_timestamp被复制到所有服务器应用事务，并且每个服务器使用immediate_commit_timestamp在其自己的二进制日志中存储本地提交时间。

        组复制中独有的视图更改事件是一种特殊情况。包含该事件的事务由每个服务器生成，但共享相同的GTID。因此，这种事务不是先在主服务器中执行，然后复制到该组其它成员，而是该组的所有成员都执行并应用相同的事务。由于没有原始主服务器，因此这些事务的original_commit_timestamp设置为零。

监控延迟复制

        在MySQL 8之前的老版本中，监控复制的延迟（滞后）最常用的方法之一是依赖于show slave status输出中的seconds_behind_master字段。但是，当使用比传统主从复制更复杂的复制拓扑，例如组复制时，此度量标准不再适用。MySQL 8中添加的immediate_commit_timestamp和original_commit_timestamp可提供有关复制延迟的更精细的信息。监控支持这些时间戳的复制延迟的推荐方法是使用以下performance_schema模式中的表。

  • replication_connection_status：与主服务器连接的当前状态，提供有关连接线程排队到中继日志中的最后和当前事务的信息。
  • replication_applier_status_by_coordinator：协调器线程的当前状态，仅在使用多线程复制时显示该信息，提供有关协调器线程缓冲到工作队列的最后一个事务的信息，以及当前正在缓冲的事务。
  • replication_applier_status_by_worker：应用从主服务器接收事务的线程的当前状态，提供有关应用程序线程或使用多线程复制时每个工作线程应用的事务信息。

        使用这些表，可以监控相应线程处理的最后一个事务以及该线程当前正在处理的事务的信息，包括：

  • 事务的GTID。
  • 从库中继日志中检索的事务的original_commit_timestamp和immediate_commit_timestamp。
  • 线程开始处理事务的时间。
  • 对于上次处理的事务，线程完成处理它的时间。
            除Performance Schema表之外，show slave status的输出还有三个字段与延迟复制有关：
  • SQL_Delay：非负整数，表示使用CHANGE MASTER TO MASTER_DELAY = N配置的复制延迟，以秒为单位。与performance_schema.replication_applier_configuration.desired_delay值相同。
  • SQL_Remaining_Delay：当Slave_SQL_Running_State等待主执行事件后的MASTER_DELAY秒时，该字段包含一个整数，表示延迟剩余的秒数。在它他时候，此字段为NULL。与performance_schema.replication_applier_status.remaining_delay值相同。
  • Slave_SQL_Running_State：一个字符串，指示SQL线程的状态（类似于Slave_IO_State）。该值与SHOW PROCESSLIST显示的SQL线程的State值相同。
            当从库的SQL线程在执行事件之前等待延迟时，SHOW PROCESSLIST将其状态值显示为：Waiting until MASTER_DELAY seconds after master executed event。

参考链接

MySQL 5.7.27创建用户并授权

参考链接

家里ADSL上网无固定外网IP的群晖NAS安全实现与公网MySQL服务器主从同步

家里 ADSL 上网，没有办法分配固定外网 IP ，现在想使用群晖自带的MariaDB 安全实现与公网 MySQL 服务器主从同步。

最大的问题实际上是如果暴漏 MySQL 服务器的端口，但是不限制来源 IP 地址的话，会造成非常大的安全隐患。

但是， ADSL 恰恰不能提供固定的 IP 地址，我们需要解决这个问题。如果通过在公网数据库服务器上搭建 OpenVPN 服务器的方式，我们恰恰可以把内外网的设备影射到同一个子网中，而且 OpenVPN 提供的加密服务支持，刚刚好满足我们的安全需求。

注意：一旦配置主从同步，磁盘的休眠会被MySQL的同步写入打断，导致磁盘不能正常休眠，毕竟正常情况下数据库是持续写入的，不存在很长时间的停顿。这样会导致磁盘周期性的咔咔寻道声，尤其是晚上。

噪声问题，参考 群晖(Synology) DS718+希捷酷狼(Seagate IronWolf)12TB空闲发出持续噪声

1. 参考 Ubuntu架设OpenVPN实现内网穿透 搭建整个的 OpenVPN 服务器。映射完成后，设备上会新增一个名为 tun0 的网卡设备。同时所有连接到 VPN 服务器的设备都被被影射到 10.8.0.X 的网段。 公网服务器的地址默认是 10.8.0.1，本文中， 群晖NAS的地址被设定为 10.8.0.7。

2. 配置防火墙规则，许可来自指定网卡指定地址的设备的访问。注意，此处一定要指定网卡为 OpenVPN 创建的虚拟网卡，否则造成安全隐患。

3. 参照 ubuntu 16.04配置基于SSL的MySQL主从同步 配置服务器

4. 接下来是群晖NAS的配置

首先是群晖服务器上没有 MySQL，需要安装 MariaDB ，如下图：
继续阅读家里ADSL上网无固定外网IP的群晖NAS安全实现与公网MySQL服务器主从同步

解决ubuntu 16.04下更改MySQL的数据库位置

ubuntu 16.04下使用APT安装的MySQL的数据库，目录同时接受apparmor的管理，因此在修改数据库目录的时候，需要同步更新apparmor的配置文件。如果只是迁移数据库的话

对于日志文件一起迁移的情况

如果依旧启动失败，并且 MySQL 是从低版本升级上来的，并且目前正在使用的版本大于或者等于 MySQL 5.7 ，执行 journalctl -xe 观察到类似如下内容：

那么，此时的MySQL的 apparmor 配置文件可能还没有更新，这个时候，我们需要手工在/etc/apparmor.d/usr.sbin.mysqld 增加几个文件目录的权限，如下：

另外，注意到我这边出现

此时,如果检查目录权限，出现如下现象：

那么需要变更用户的所有者，早期版本支持所有者为 adm 用户组，新版本需要 mysql ,我们执行如下命令：

参考链接

ubuntu 16.04配置基于SSL的MySQL主从同步

首先参考 ubuntu 16.04配置MySQL主从同步 实现同步,接下来执行如下操作

主数据库master配置

从服务器slave配置

注意，偶尔在系统升级的时候，从库可能会丢失同步状态配置。这时候，我们需要重新同步，此时我们从从设备/var/lib/mysql/master.info中找到被中断的同步点。

里面的内容一般如下：

注意mysql-bin.000582下面的4765083就是同步位置，在恢复的时候，就是这两个关键数据。

参考链接

ubuntu 16.04配置MySQL主从同步

准备工作

1.主从数据库版本最好一致

2.主从数据库内数据保持一致

主数据库：121.199.27.227 /ubuntu 16.04 MySQL 5.7.21 (阿里云）

从数据库：182.254.149.39 /ubuntu 16.04 MySQL 5.7.21 (腾讯云）

防火墙配置

配置主服务器只允许特定IP访问数据库的端口，避免不必要的攻击。

主库防火墙配置

从库防火墙配置

主数据库master配置

1.修改mysql配置

在[mysqld]部分进行如下修改：

2.修改需要同步的表的引擎为INNODB，只有INNODB支持主从，MyISAM不支持

3.重启mysql，创建用于同步的用户账号

创建用户并授权：用户：repl 密码：slavepass

4.查看master状态，记录二进制文件名(mysql-bin.000001)和位置(333802)：

5.主库备份，为从库的第一次数据同步准备数据

使用如下脚本产生数据库备份文件

执行脚本,确保最后输出备份成功

从服务器slave配置

1.修改mysql配置

修改server-id，每个数据库的server-id要求是唯一的，不能相互冲突

2.首次还原数据库：

还原完成后，把数据库设置成只读模式,如果从库可写会出现冲突导致同步失败

添加如下语句：

3.重启mysql，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

4.启动slave同步进程：

5.查看slave状态：

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。

还可以用到的其他相关参数：

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：

如之前查看master状态时就可以看到只记录了test库，忽略了manual和mysql库。

注意，偶尔在系统升级的时候，从库可能会丢失同步状态配置。这时候，我们需要重新同步，此时我们从从设备/var/lib/mysql/master.info中找到被中断的同步点。

里面的内容一般如下：

注意mysql-bin.000582下面的4765083就是同步位置，在恢复的时候，就是这两个关键数据。

参考链接