MySQL 5.7 禁用GTID联机事务

在线禁用GTID事务
如何在已经在线的服务器上禁用GTID事务。此过程不需要使服务器离线,适合在生产环境中使用。但是,如果您有可能在禁用GTIDs模式时 使服务器离线,那么这个过程会更容易。

该过程类似于在服务器在线时启用GTID事务,但步骤相反。唯一不同的是等待已记录事务复制的时间点。

在开始之前,请确保服务器满足以下前提条件:
.拓扑中的所有服务器必须使用MySQL 5.7.6或更高版本。您不能在任何一台服务器上在线禁用GTID事务,除非拓扑中的所有服务器都使用此版本 。

.所有服务器都将gtid_mode设置为ON。

1.在每个从服务器上执行以下命令,如果您使用多源复制,请为每个通道执行此操作,并包含for channel channel子句:

STOP SLAVE [FOR CHANNEL 'channel'];
CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = file,MASTER_LOG_POS = position [FOR CHANNEL 'channel'];
START SLAVE [FOR CHANNEL 'channel'];


mysql> stop slave for channel 'master-1';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> stop slave for channel 'master-2';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE ='binlog.000008',MASTER_LOG_POS=194 for channel 'master-1';
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE ='binlog.000007',MASTER_LOG_POS=194 for channel 'master-2';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave for channel 'master-1';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave for channel 'master-2';
Query OK, 0 rows affected (0.00 sec)

2.在每个服务器上执行:

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)

3.在每个服务器上执行:

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.03 sec)

4.在每个服务器上,等待变量@@GLOBAL。GTID_OWNED等于空字符串。可以使用以下命令检查:

mysql> SELECT @@GLOBAL.GTID_OWNED;
+---------------------+
| @@GLOBAL.GTID_OWNED |
+---------------------+
|                     |
+---------------------+
1 row in set (0.00 sec)

在复制从机上,从理论上讲,它可能是空的,然后又变为非空的。这不是问题,空一次就足够了。

5.等待当前存在于任何二进制日志中的所有事务复制到所有从属日志
1.在主服务器执行:

SHOW MASTER STATUS;

主库1:10.18.30.50

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000010 |      194 |              |                  | bb8b95d1-6f47-11ef-9592-005056a390e6:1-10 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)


主库2:10.18.30.43

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000009 |      194 |              |                  | 684e1f7d-6f47-11ef-a6d5-005056a3a162:1-11 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

记下“文件和位置”列中的值

2.在每个从属服务器上,使用来自主服务器的文件和位置信息执行:

SELECT MASTER_POS_WAIT('source_log_file', source_log_pos [, timeout][, channel])

从库:10.18.30.39

mysql> SELECT MASTER_POS_WAIT('binlog.000010',194,0,'master-1');
+---------------------------------------------------+
| MASTER_POS_WAIT('binlog.000007',194,0,'master-1') |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT MASTER_POS_WAIT('binlog.000009',194,0,'master-2');
+---------------------------------------------------+
| MASTER_POS_WAIT('binlog.000006',194,0,'master-2') |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)

返回值为0 ,代表从库已经应用了主库1binlog.000010 194与 主库2binlog.000009 194位置的数据。

如果有一个主服务器和多层的从服务器,或者换句话说,有从服务器的从服务器,那么在每层都重复第2步,从主服务器开始,然后是所有的直 接从服务器,然后是所有从服务器的从服务器,以此类推。

6.如果您将二进制日志用于复制以外的其他用途,例如执行时间点备份或恢复:请等到不需要具有GTID事务的旧二进制日志时再使用。

例如,在步骤5完成之后,可以在进行备份的服务器上执行FLUSH LOGS。然后,要么显式地进行备份,要么等待您可能设置的任何定期备份例程 的下一次迭代。

理想情况下,等待服务器清除步骤5完成时存在的所有二进制日志。还要等待步骤5之前所做的备份过期。

这是整个过程中最重要的一点。重要的是要理解,包含GTID事务的日志在下一步之后不能使用。在继续之前,必须确保GTID事务不存在于拓扑中 的任何位置。

7.在每个服务器上执行:

mysql> SET @@GLOBAL.GTID_MODE = OFF;
Query OK, 0 rows affected (0.01 sec)

8.在每个服务器上,在my.cnf文件中设置gtid-mode=OFF
如果你想设置enforce_gtid_consistency=OFF,现在就可以这样做。设置好后,你应该在你的配置文件中添加enforce_gtid_consistency=OFF。

如果您想降级到MySQL的早期版本,现在就可以这样做,使用正常的降级过程。

MySQL 5.7 启用GTID联机事务

启用GTID联机事务
如何在已经在线并使用匿名事务的服务器上启用GTID事务,以及可选的自动定位功能。此过程不需要使服务器离线,适合在生产环境中 使用。但是,如果您可以在启用GTID事务时使服务器脱机,那么处理就会更容易。

在开始安之前,请确保服务器满足以下前提条件:
.拓扑中的所有服务器必须使用MySQL 5.7.6或更高版本。您不能在任何一台服务器上在线启用GTID事务,除非拓扑中的所有服务器都使用此版本 。

.所有服务器都将gtid_mode设置为默认值OFF

启用GTID事务:
1.在每个服务器上执行:

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

主库1:10.18.30.50

mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
Query OK, 0 rows affected (0.01 sec)

主库2:10.18.30.43

mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
Query OK, 0 rows affected (0.01 sec)

从库:10.18.30.39

mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
Query OK, 0 rows affected (0.01 sec)

让服务器在正常工作负载下运行一段时间,并监视日志。如果此步骤导致日志中出现任何警告,请调整应用程序,使其只使用gtid兼容的特性, 而不生成任何警告。

这是重要的第一步。在进入下一步之前,必须确保错误日志中没有生成警告。

2.在每个服务器上执行:

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

主库1:10.18.30.50

mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
Query OK, 0 rows affected (0.00 sec)

主库2:10.18.30.43

mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
Query OK, 0 rows affected (0.00 sec)

从库:10.18.30.39

mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
Query OK, 0 rows affected (0.00 sec)

3.在每个服务器上执行:

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

主库1:10.18.30.50

mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)

主库2:10.18.30.43

mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)

从库:10.18.30.39

mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)

哪个服务器首先执行此语句并不重要,但重要的是,所有服务器都要在任何服务器开始下一步之前完成此步骤。

4.在每个服务器上执行:

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

主库1:10.18.30.50

mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)

主库2:10.18.30.43

mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)


从库:10.18.30.39

mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)

哪个服务器先执行该语句并不重要。

5.在每个服务器上,等待状态变量ONGOING_ANONYMOUS_TRANSACTION_COUNT为零。可以使用以下命令进行检查:

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

主库1:10.18.30.50

mysql> SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+
1 row in set (0.01 sec)

主库2:10.18.30.43

mysql> SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+
1 row in set (0.01 sec)

从库:10.18.30.39

mysql> SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+
1 row in set (0.01 sec)

注意:在复制从机上,理论上有可能显示为零,然后再次显示为非零。这不是问题,它显示一次零就足够了。

6.等待步骤5之前生成的所有事务复制到所有服务器。您可以在不停止更新的情况下执行此操作:唯一重要的是所有匿名事务都会被复制。
1.在主服务器执行:

SHOW MASTER STATUS;

主库1:10.18.30.50

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000007 |      194 |              |                  | bb8b95d1-6f47-11ef-9592-005056a390e6:1-10 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)


主库2:10.18.30.43

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000006 |      194 |              |                  | 684e1f7d-6f47-11ef-a6d5-005056a3a162:1-10 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

记下“文件和位置”列中的值

2.在每个从属服务器上,使用来自主服务器的文件和位置信息执行:

SELECT MASTER_POS_WAIT('source_log_file', source_log_pos [, timeout][, channel])

从库:10.18.30.39

mysql> SELECT MASTER_POS_WAIT('binlog.000007',194,0,'master-1');
+---------------------------------------------------+
| MASTER_POS_WAIT('binlog.000007',194,0,'master-1') |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT MASTER_POS_WAIT('binlog.000006',194,0,'master-2');
+---------------------------------------------------+
| MASTER_POS_WAIT('binlog.000006',194,0,'master-2') |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT MASTER_POS_WAIT('binlog.000006',195,0,'master-2');
+---------------------------------------------------+
| MASTER_POS_WAIT('binlog.000006',194,0,'master-2') |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)

返回值为0 ,代表从库已经应用了主库1binlog.000007 194与 主库2binlog.000006 194位置的数据。

如果有一个主服务器和多层的从服务器,或者换句话说,有从服务器的从服务器,那么在每层都重复第2步,从主服务器开始,然后是所有的直 接从服务器,然后是所有从服务器的从服务器,以此类推。

如果使用循环复制拓扑,其中多个服务器可能有写客户端,则对每个主从连接执行步骤2,直到完成整个循环。重复整个过程,这样你就可以完 成整个循环两次。

例如,假设你有3个服务器A、B和C,它们在一个循环中复制A -> B -> C -> A。过程如下:

? Do step 1 on A and step 2 on B.
? Do step 1 on B and step 2 on C.
? Do step 1 on C and step 2 on A.
? Do step 1 on A and step 2 on B.
? Do step 1 on B and step 2 on C.
? Do step 1 on C and step 2 on A.

7.如果您将二进制日志用于除了复制之外的任何事情,例如时间点备份和恢复,请等待,直到您不需要具有没有gtid的事务的旧二进制日志。

例如,在步骤6完成后,可以在正在备份的服务器上执行FLUSH LOGS。然后,要么显式地进行备份,要么等待您可能设置的任何定期备份例程的 下一次迭代。理想情况下,等待服务器清除步骤6完成时存在的所有二进制日志。还要等待步骤6之前所做的备份过期。

这是第二点。理解包含匿名事务的二进制日志(没有gtid)在下一步中是无法使用的,这一点至关重要。在此步骤之后,您必须确保没有GTIDs的 事务在拓扑中不存在。

8.在每个服务器上

mysql> SET @@GLOBAL.GTID_MODE = ON;
Query OK, 0 rows affected (10.06 sec)

9.在每个服务器的my.cnf文件中加上gtid-mode=ON
现在可以保证所有的事务都有一个GTID(除了第5步或更早阶段生成的事务,它们已经被处理过)。要开始使用GTID协议以便稍后执行自动故障转 移,请在每个从服务器上执行以下操作。如果使用多源复制,可选地对每个通道执行此操作,并包括for channel channel子句:

STOP SLAVE [FOR CHANNEL 'channel'];
CHANGE MASTER TO MASTER_AUTO_POSITION = 1 [FOR CHANNEL 'channel'];
START SLAVE [FOR CHANNEL 'channel'];


mysql> stop slave for channel 'master-1';
Query OK, 0 rows affected (0.02 sec)

mysql> stop slave for channel 'master-2';
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 1 for channel 'master-1';
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 1 for channel 'master-2';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave for channel 'master-1';
Query OK, 0 rows affected (0.01 sec)

mysql> start slave for channel 'master-2';
Query OK, 0 rows affected (0.00 sec)

MySQL 5.7 多源复制

MySQL多源复制
MySQL多源复制,它使您能够从多个直接主服务器并行复制。介绍多源复制的配置、监控和故障排除方法。

MySQL多源复制概述
MySQL多源复制允许复制从服务器同时接收来自多个源的事务。多源复制可以将多台服务器备份到一台服务器,可以合并表分片,也可以将多台服务器的数据合并到一台服务器。在应用事务时,多源复制不实现任何冲突的检测或解决,如果需要,这些任务将留给应用程序。在多源复制拓扑中,从节点为应该从其接收事务的每个主节点创建复制通道。

配置多源复制
介绍多源复制拓扑的配置方法,以及配置主从拓扑的详细信息。这样的拓扑至少需要配置两个主节点和一个从节点。

可以将多源复制拓扑中的主机配置为使用基于全局事务标识符(GTID)的复制或基于二进制日志位置的复制。

多源复制拓扑中的从服务器需要基于TABLE的存储库。多源复制与基于文件的存储库不兼容。mysqld使用的存储库类型可以在启动时配置,也可以动态配置。

多源复制
主库1:10.18.30.25
主库2:10.18.30.43
从库: 10.18.30.39

1.在主库中创建用于复制的用户
主库1:10.18.30.25

[root@localhost mysql]#  mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

主库2:10.18.30.43
[root@localhost mysql]#  mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

2.对主库进行复制相关参数的设置
2.1 配置二进制日志和服务器ID选项

log-bin=mysql-bin
server-id=1

2.2 要配置复制从服务器启动时使用的存储库类型,请使用以下选项启动mysqld:

master-info-repository=TABLE
relay-log-info-repository=TABLE

要修改一个使用FILE存储库的复制从库,使其使用TABLE存储库,需要执行以下命令动态转换现有的复制存储库:

STOP SLAVE;
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';


mysql> stop slave;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: undo

Query OK, 0 rows affected (0.10 sec)

mysql> SET GLOBAL master_info_repository = 'TABLE';
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL relay_log_info_repository = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

2.3 如果使用基于全局事务标识符(GTID)的复制,还要检查全局事务标识符(GTID)的设置

mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 1                                    |
| server_id_bits | 32                                   |
| server_uuid    | f044fd89-6b6c-11ef-9f9f-005056a390e6 |
+----------------+--------------------------------------+
3 rows in set (0.01 sec)


mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 2                                    |
| server_id_bits | 32                                   |
| server_uuid    | 3f706e9b-6b6b-11ef-a6e3-005056a3a162 |
+----------------+--------------------------------------+
3 rows in set (0.01 sec)


mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 3                                    |
| server_id_bits | 32                                   |
| server_uuid    | b064fda1-6b68-11ef-8226-005056b9a980 |
+----------------+--------------------------------------+
3 rows in set (0.01 sec)

3.向多源复制从端添加基于GTID的主端
假设您已经使用gtid_mode= on在主服务器上启用了基于GTID的事务,启用了复制用户,并确保从服务器使用基于TABLE的复制存储库。使用CHANGE MASTER TO语句通过使用FOR channel通道子句向通道添加一个新的主通道。

mysql> CHANGE MASTER TO MASTER_HOST='10.18.30.25',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_AUTO_POSITION=1 FOR CHANNEL 'master-1';
Query OK, 0 rows affected, 2 warnings (0.05 sec)




mysql> CHANGE MASTER TO MASTER_HOST='10.18.30.43',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_AUTO_POSITION=1 FOR CHANNEL 'master-2';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

启动多源复制从服务器
添加了想要用作复制主机的所有通道后,使用START SLAVE thread_types语句启动复制。当您在从属服务器上启用了多个通道时,您可以选择启动所有通道,或者选择要启动的特定通道。

.启动所有当前配置的复制区域通道

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

从库日志信息显示如下:

2024-09-09T09:16:09.641054Z 11 [Note] Slave I/O thread for channel 'master-1': connected to master 'repl@10.18.30.25:3306',replication started in log 'binlog.000002' at position 1841
2024-09-09T09:16:09.645067Z 13 [Note] Slave I/O thread for channel 'master-2': connected to master 'repl@10.18.30.43:3306',replication started in log 'binlog.000002' at position 1841

主库日志信息显示如下:
主库1:10.18.30.25

2024-09-10T08:40:48.526865Z 4 [Warning] IP address '10.18.30.39' could not be resolved: Temporary failure in name resolution
2024-09-10T08:40:48.534271Z 4 [Note] Start binlog_dump to master_thread_id(4) slave_server(3), pos(binlog.000004, 194)

主库2:10.18.30.43

2024-09-10T08:33:01.936331Z 7 [Warning] IP address '10.18.30.39' could not be resolved: Temporary failure in name resolution
2024-09-10T08:33:01.943408Z 7 [Note] Start binlog_dump to master_thread_id(7) slave_server(3), pos(binlog.000003, 194)

查看从库日志发现出错了。

2024-09-10T07:55:34.529867Z 4 [ERROR] Slave SQL for channel 'master-1': Error 'Operation ALTER USER failed for 'root'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'', Error_code: 1396
2024-09-10T07:55:34.529945Z 4 [Warning] Slave: Operation ALTER USER failed for 'root'@'localhost' Error_code: 1396
2024-09-10T07:55:34.529980Z 4 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000002' position 154.
2024-09-10T07:55:34.530177Z 6 [ERROR] Slave SQL for channel 'master-2': Error 'Operation ALTER USER failed for 'root'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'', Error_code: 1396
2024-09-10T07:55:34.530231Z 6 [Warning] Slave: Operation ALTER USER failed for 'root'@'localhost' Error_code: 1396
2024-09-10T07:55:34.530249Z 6 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000002' position 154.


2024-09-10T07:55:34.529867Z 4 [ERROR] Slave SQL for channel 'master-1': Error 'Operation ALTER USER failed for 'root'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'', Error_code: 1396
2024-09-10T07:55:34.529945Z 4 [Warning] Slave: Operation ALTER USER failed for 'root'@'localhost' Error_code: 1396
2024-09-10T07:55:34.529980Z 4 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000002' position 154.
2024-09-10T07:55:34.530177Z 6 [ERROR] Slave SQL for channel 'master-2': Error 'Operation ALTER USER failed for 'root'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'', Error_code: 1396
2024-09-10T07:55:34.530231Z 6 [Warning] Slave: Operation ALTER USER failed for 'root'@'localhost' Error_code: 1396
2024-09-10T07:55:34.530249Z 6 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000002' position 154.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.18.30.25
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 194
               Relay_Log_File: localhost-relay-bin-master@002d1.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            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: 1396
                   Last_Error: Error 'Operation ALTER USER failed for 'root'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 3214
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1396
               Last_SQL_Error: Error 'Operation ALTER USER failed for 'root'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: bb8b95d1-6f47-11ef-9592-005056a390e6
             Master_Info_File: mysql.slave_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: 240910 15:55:34
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: bb8b95d1-6f47-11ef-9592-005056a390e6:1-8
            Executed_Gtid_Set: ca006ef3-6f46-11ef-8203-005056b9a980:1-5
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: master-1
           Master_TLS_Version:
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.18.30.43
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 194
               Relay_Log_File: localhost-relay-bin-master@002d2.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            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: 1396
                   Last_Error: Error 'Operation ALTER USER failed for 'root'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 2766
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1396
               Last_SQL_Error: Error 'Operation ALTER USER failed for 'root'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 684e1f7d-6f47-11ef-a6d5-005056a3a162
             Master_Info_File: mysql.slave_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: 240910 15:55:34
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 684e1f7d-6f47-11ef-a6d5-005056a3a162:1-8
            Executed_Gtid_Set: ca006ef3-6f46-11ef-8203-005056b9a980:1-5
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: master-2
           Master_TLS_Version:
2 rows in set (0.00 sec)

因为使用基于gtid事务复制,于是尝试使用基于二进制日志的复制

mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.02 sec)

mysql> change master to master_auto_position=0;
ERROR 3079 (HY000): Multiple channels exist on the slave. Please provide channel name as an argument.
mysql> change master to master_auto_position=0 for channel 'master-1';
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_auto_position=0 for channel 'master-2';
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO   MASTER_HOST='10.18.30.25',MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_LOG_FILE='binlog.000004',MASTER_LOG_POS=194 FOR CHANNEL 'master-1';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> CHANGE MASTER TO   MASTER_HOST='10.18.30.43',MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=194 FOR CHANNEL 'master-2';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看从库日志

2024-09-10T08:24:19.657675Z 2 [Note] 'CHANGE MASTER TO FOR CHANNEL 'master-1' executed'. Previous state master_host='10.18.30.25', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='10.18.30.25', master_port= 3306, master_log_file='binlog.000004', master_log_pos= 194, master_bind=''.
2024-09-10T08:24:32.746649Z 2 [Note] 'CHANGE MASTER TO FOR CHANNEL 'master-2' executed'. Previous state master_host='10.18.30.43', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='10.18.30.43', master_port= 3306, master_log_file='binlog.000003', master_log_pos= 194, master_bind=''.
2024-09-10T08:24:40.193771Z 7 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2024-09-10T08:24:40.200216Z 8 [Note] Slave SQL thread for channel 'master-1' initialized, starting replication in log 'binlog.000004' at position 194, relay log './localhost-relay-bin-master@002d1.000001' position: 4
2024-09-10T08:24:40.200505Z 9 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2024-09-10T08:24:40.203240Z 10 [Note] Slave SQL thread for channel 'master-2' initialized, starting replication in log 'binlog.000003' at position 194, relay log './localhost-relay-bin-master@002d2.000001' position: 4
2024-09-10T08:24:47.987545Z 9 [Note] Slave I/O thread for channel 'master-2': connected to master 'repl@10.18.30.43:3306',replication started in log 'binlog.000003' at position 194
2024-09-10T08:24:47.987835Z 7 [Note] Slave I/O thread for channel 'master-1': connected to master 'repl@10.18.30.25:3306',replication started in log 'binlog.000004' at position 194

测试同步
主库1:10.18.30.25

mysql> create table t_cs(id int ,name varchar(30));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t_cs values(1,'cs');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_cs;
+------+------+
| id   | name |
+------+------+
|    1 | cs   |
+------+------+
1 row in set (0.00 sec)

从库:

mysql> select * from t_cs;
+------+------+
| id   | name |
+------+------+
|    1 | cs   |
+------+------+
1 row in set (0.00 sec)

主库2:10.18.30.43

mysql> create table t_repl(t_id int,t_name varchar(50));
Query OK, 0 rows affected (0.19 sec)

mysql> insert into t_repl values(1,'jy');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_repl;
+------+--------+
| t_id | t_name |
+------+--------+
|    1 | jy     |
+------+--------+
1 row in set (0.00 sec)

从库:

mysql> select * from t_repl;
+------+--------+
| t_id | t_name |
+------+--------+
|    1 | jy     |
+------+--------+
1 row in set (0.00 sec)



mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.18.30.25
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 662
               Relay_Log_File: localhost-relay-bin-master@002d1.000002
                Relay_Log_Pos: 785
        Relay_Master_Log_File: binlog.000004
             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: 662
              Relay_Log_Space: 1009
              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: bb8b95d1-6f47-11ef-9592-005056a390e6
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      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: bb8b95d1-6f47-11ef-9592-005056a390e6:9-10
            Executed_Gtid_Set: 684e1f7d-6f47-11ef-a6d5-005056a3a162:9-10,
bb8b95d1-6f47-11ef-9592-005056a390e6:9-10,
ca006ef3-6f46-11ef-8203-005056b9a980:1-5
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: master-1
           Master_TLS_Version:
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.18.30.43
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 669
               Relay_Log_File: localhost-relay-bin-master@002d2.000002
                Relay_Log_Pos: 792
        Relay_Master_Log_File: binlog.000003
             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: 669
              Relay_Log_Space: 1016
              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: 2
                  Master_UUID: 684e1f7d-6f47-11ef-a6d5-005056a3a162
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      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: 684e1f7d-6f47-11ef-a6d5-005056a3a162:9-10
            Executed_Gtid_Set: 684e1f7d-6f47-11ef-a6d5-005056a3a162:9-10,
bb8b95d1-6f47-11ef-9592-005056a390e6:9-10,
ca006ef3-6f46-11ef-8203-005056b9a980:1-5
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: master-2
           Master_TLS_Version:

MySQL 5.7 使用全局事务标识符(GTIDs)进行主从复制

使用全局事务标识符进行复制
下面解释使用全局事务标识符(GTIDs)进行基于事务的复制。当使用GTIDs时,每个事务都可以被识别和跟踪,因为它是在原始服务器上提交的, 并由任何从服务器应用;这意味着在启动一个新的slave或故障转移到一个新的master时,使用gtid时不需要引用日志文件或这些文件中的位置, 这大大简化了这些任务。因为基于gtid的复制完全是基于事务的,所以很容易确定主节点和从节点是否一致;只要在主节点上提交的所有事务也 在从节点上提交,就可以保证两者之间的一致性。你可以在GTIDs中使用基于语句或基于行的复制;不过,为了获得最佳效果,我们建议您使用基 于行的格式。

GTID概念
全局事务标识符(GTID)是与原始服务器(主服务器)上提交的每个事务相关联的唯一标识符。此标识符不仅对其发起的服务器是唯一的,而且在给 定复制设置中的所有服务器上也是唯一的。在所有事务和所有gtid之间存在1对1的映射。

以下段落提供了gtid的基本描述。更高级的概念将在后面的章节中介绍:
.GTID集

.mysql.gtid_executed表

.mysql.gtid_executed表压缩

GTID表示为一对坐标,由冒号(:)分隔,如下所示:
GTID = source_id:transaction_id

source_id标识原始服务器。通常,服务器的server_uuid用于此目的。transaction_id是一个序号,由事务在此服务器上提交的顺序决定;例如 ,要提交的第一个事务的transaction_id为1,在同一个原始服务器上要提交的第10个事务的transaction_id为10。在GTID中,事务不可能有0作 为序号。例如,在服务器上提交的第23个事务的UUID是3E11FA47-71CA-11E1-9E33-C80AA9429562,其GTID是这样的:
3E11FA47-71CA-11E1-9E33-C80AA9429562:23

这种格式用于在SHOW SLAVE STATUS等语句的输出以及二进制日志中表示gtid。当使用mysqlbinlog –base64-output=DECODE-ROWS查看日志文件 或在SHOW BINLOG EVENTS的输出中也可以看到它们。

正如在SHOW MASTER STATUS或SHOW SLAVE STATUS等语句的输出中所写的那样,来自同一服务器的gtid序列可以折叠成单个表达式,如下所示。

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

刚才显示的示例表示在server_uuid为3E11FA47-71CA-11E1-9E33-C80AA9429562的MySQL服务器上发起的第一个到第五个事务。

这种格式还用于提供START SLAVE选项所需的参数SQL_BEFORE_GTIDS和SQL_AFTER_GTIDS。

GTID集
GTID集合是全局事务标识符的集合,表示如下:

gtid_set:
uuid_set [, uuid_set] ...
| ''
uuid_set:
uuid:interval[:interval]...
uuid:
hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh
h:
[0-9|A-F]
interval:
n[-n]
(n >= 1)

GTID集在MySQL服务器中有多种使用方式。例如,gtid_executed和gtid_purged系统变量存储的值表示为GTID集合。此外,函数GTID_SUBSET()和 GTID_SUBTRACT()也要求输入GTID集合。当GTID集从服务器变量返回时,uuid按字母顺序排列,数值区间合并并升序排列。

gtid总是保存在master和slave之间。这意味着您总是可以通过检查任何从属服务器上应用的任何事务的二进制日志来确定其来源。此外,一旦 具有给定GTID的事务在给定服务器上提交,该服务器将忽略具有相同GTID的后续事务。因此,在主节点上提交的事务只能在从节点上应用一次, 这有助于保证一致性。

当使用gtid时,从节点不需要任何非本地的数据,例如主节点上一个文件的名称和在该文件中的位置。所有与主节点同步的必要信息都可以直接 从复制数据流中获得。GTIDs替换了之前用来确定在主从之间启动、停止或恢复数据流的点的文件偏移量对。因此,不要在CHANGE MASTER TO语 句中包含MASTER_LOG_FILE或MASTER_LOG_POS选项,这些选项用于指导从主机进行复制;相反,只需要启用MASTER_AUTO_POSITION选项。

GTID的生成和生命周期包括以下步骤:
1. 事务在主服务器上执行和提交
这个事务使用主服务器的UUID和最小的非零事务序列号分配一个GTID;GTID被写入到master的二进制日志中(紧跟在事务本身之前)。

2.在二进制日志数据传输到从服务器并存储到从服务器的中继日志之后(使用针对该进程建立的机制),从服务器读取GTID,并将其gtid_next系 统变量的值设置为该GTID。这告诉从服务器必须使用这个GTID记录下下一个事务。

注意slave在会话上下文中设置gtid_next是很重要的。

3.从服务器验证这个GTID是否已经被用来在它自己的二进制日志中记录事务。如果这个GTID没有被使用,从服务器就会写入GTID,应用事务,并 将事务写入它的二进制日志。在处理事务本身之前,通过首先读取和检查事务的GTID,从服务器不仅保证没有先前拥有该GTID的事务应用于从服 务器,而且还保证没有其他会话已经读取该GTID但尚未提交相关事务。换句话说,不允许多个客户端并发地应用相同的事务。

4.因为gtid_next不为空,所以从服务器并不试图为该事务生成一个GTID,而是将存储在该变量中的GTID,即从主服务器获得的GTID,在其二进 制日志中紧接在事务之前。

mysql.gtid_executed表
mysql.gtid_execute表是在MySQL服务器安装或升级时创建的(如果它不存在),使用如下所示的create table语句:
CREATE TABLE gtid_executed (
source_uuid CHAR(36) NOT NULL,
interval_start BIGINT(20) NOT NULL,
interval_end BIGINT(20) NOT NULL,
PRIMARY KEY (source_uuid, interval_start)
)

警告
与其他MySQL系统表一样,不要尝试自己创建或修改该表。

只有当gtid_mode为ON或ON_PERMISSIVE时,gtid才会存储在mysql.gtid_executed表中。表中存储的gtid与是否启用二进制日志记录无关。但它 们存储的方式取决于log_bin是开启还是关闭。
.如果二进制日志被禁用(log_bin为OFF),服务器将属于每个事务的GTID与表中的事务一起存储。

此外,当二进制日志被禁用时,该表会以用户可配置的速率定期压缩;看mysql.gtid_executed表压缩,以获取更多信息。

.如果启用了二进制日志记录(打开log_bin),那么除了将gtid存储在mysql.gtid_executed表之外,每当二进制日志被轮换或服务器关闭时,服 务器都会将所有写入前一个二进制日志的事务的GTIDs写入到新的二进制日志中。

在服务器意外停止的情况下,之前的二进制日志中的gtid集合不会保存到mysql中。gtid_executed表。在这种情况下,这些gtid会被添加到表中 ,并在恢复期间被添加到gtid_executed系统变量中的gtid集合中。

当启用二进制日志记录时,mysql。gtid_executed表没有提供所有已执行事务的GTIDs的完整记录。该信息由gtid_executed系统变量的全局值提供。

mysql.gtid_executed表被RESET MASTER重置。

mysql.gtid_executed表压缩
随着时间的推移,mysql.gtid_execute表可能会被许多行填充,这些行引用来自同一服务器上的单个gtid,并且其事务id组成一个序列,类似于 下面所示:

mysql> SELECT * FROM mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
|--------------------------------------+----------------+--------------|
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 37             | 37           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 38             | 38           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 39             | 39           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 40             | 40           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 41             | 41           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 42             | 42           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 43             | 43           |
...

如果将该表定期压缩,可以将每组这样的行替换为跨越整个事务标识符间隔的单行,这样可以节省相当大的空间,如下所示:

+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
|--------------------------------------+----------------+--------------|
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 |             37 | 43           |
...

当启用GTIDs时,服务器会对mysql.gtid_executed表周期性地执行这种类型的压缩。通过设置executed_gtids_compression_period系统变量, 可以控制压缩表之前允许发生的事务数,从而控制压缩率。这个变量的默认值是1000;这意味着,默认情况下,每1000个事务之后都会对表进行 压缩。将executed_gtid_compression_period设置为0将禁止执行压缩;但是,用户应该做好准备,如果这样做,gtid_executed表可能会需要大 量的磁盘空间

注意:
当启用二进制日志记录时,不使用executed_gtids_compression_period时。每次二进制日志轮换时,mysql.gtid_executed表都会被压缩。

压缩mysql.gtid_execute表是由一个名为thread/sql/compress_gtid_table的专用前台线程执行。这个线程没有在SHOW PROCESSLIST的输出中列 出,但是它可以被看作是线程表中的一行,如下所示:

mysql> SELECT * FROM performance_schema.threads WHERE NAME LIKE '%gtid%'\G
*************************** 1. row ***************************
          THREAD_ID: 26
               NAME: thread/sql/compress_gtid_table
               TYPE: FOREGROUND
     PROCESSLIST_ID: 1
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Daemon
   PROCESSLIST_TIME: 631286
  PROCESSLIST_STATE: Suspending
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 24594
1 row in set (0.00 sec)

thread/sql/compress_gtid_table线程通常休眠,直到executed_gtids_compression_period事务被执行,如前所述,然后唤醒执行 mysql.gtid_performed表压缩。然后它休眠,直到另一个executed_gtids_compression_period事务发生,然后唤醒再次执行压缩,无限地重复 这个循环。当禁用二进制日志记录时,将此值设置为0意味着线程始终处于睡眠状态,永远不会醒来。

使用gtid设置复制
对于最简单的GTID复制拓扑(由一个主节点和一个从节点组成),启动过程中的关键步骤如下:
1.如果复制已经在运行,则通过将两台服务器设置为只读来同步它们。

2.停止两个服务器。

3.启用gtid并配置正确的选项并重新启动两个服务器。
启动所描述的服务器所需的mysqld选项将在本节后面的示例中讨论。

注意:
server_uuid必须存在,gtid才能正常工作。

4.指示从服务器使用主服务器作为复制数据源并使用自动定位,然后启动从服务器。完成此步骤所需的SQL语句将在本节后面的示例中描述。

5.做一个新的备份。包含没有gtid的事务的二进制日志不能在启用gtid的服务器上使用,因此在此之前进行的备份不能用于您的新配置。

6.启动从属服务器,然后在两台服务器上再次启用读取模式,以便它们可以接受更新。

在下面的示例中,三台服务器已经分别作为主服务器和从服务器运行,使用MySQL的二进制日志基于位置的复制协议。下面的例子展示了如何在 服务器的选项文件中存储mysqld启动选项。

下面的大多数步骤都需要使用MySQL root帐户或其他具有SUPER权限的MySQL用户帐户。mysqladmin shutdown需要SUPER权限或SHUTDOWN特权。

步骤1:同步服务器。对于已经在进行复制而不是使用gtid方式的服务器才需要执行此步骤。对于新服务器,请继续执行步骤3。通过在每个服务 器上发出以下命令,设置read_only系统变量为ON使服务器只读:

mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 1                                    |
| server_id_bits | 32                                   |
| server_uuid    | 7877044c-a8f0-11ec-be08-005056a390e6 |
+----------------+--------------------------------------+
3 rows in set (0.01 sec)

mysql> SET @@global.read_only = ON;
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 2                                    |
| server_id_bits | 32                                   |
| server_uuid    | 1c5eb4fb-9479-11ec-8f21-005056a390e6 |
+----------------+--------------------------------------+
3 rows in set (0.06 sec)

mysql> SET @@global.read_only = ON;
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 3                                    |
| server_id_bits | 32                                   |
| server_uuid    | f08c0957-3d11-11ef-bea6-005056b9a980 |
+----------------+--------------------------------------+
3 rows in set (0.05 sec)

mysql> SET @@global.read_only = ON;
Query OK, 0 rows affected (0.00 sec)

等待所有正在进行的事务提交或回滚。然后,让从服务器追上主服务器。在继续工作之前,确保从服务器已经处理了所有的更新是极其重要的。

如果您将二进制日志用于除复制之外的任何事情,例如执行时间点备份和恢复,请等待,直到您不需要包含没有gtid的事务的旧二进制日志。理 想情况下,应该等待服务器清除所有二进制日志,并等待任何现有的备份过期。

重点
重要的是要理解,包含没有gtid的事务的日志不能在启用了gtid的服务器上使用。在继续之前,必须确保没有gtid的事务在拓扑中的任何位置都 不存在。

步骤2:
停止所有服务器。使用如下所示的mysqladmin停止每个服务器,其中username是MySQL用户的用户名,该用户有足够的权限关闭服务器:

shell> mysqladmin -uusername -p shutdown

然后在提示符下输入该用户的密码。

关闭主库

[root@localhost ~]# mysqladmin -uroot -pxxzx7817600 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

关闭两个从库

[root@localhost /]# mysqladmin -uroot -pxxzx7817600 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

[root@localhost mysql]# mysqladmin -uroot -pxxzx7817600 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

步骤3:启动所有启用gtid的服务器。要启用基于GTID的复制,每个服务器必须以启用GTID模式的方式启动,即通过将gtid_mode变量设置为ON, 并启用enforce_gtid_consistency变量以确保只记录适用于基于GTID复制的语句。此外,在配置从服务器设置之前,应以“–skip-slave-start ”选项启动从服务器。

为了使用gtid,并不是必须启用二进制日志记录,因为在MySQL 5.7.5添加了mysql.gtid_execute表,这意味着从服务器可以只使用gtid而不需要 二进制日志记录。主服务器必须始终启用二进制日志记录,以便能够进行复制。例如,要启动启用gtid但没有二进制日志记录的从服务器,请在 服务器的选项文件中配置这些变量:

gtid_mode=ON
enforce-gtid-consistency=true

修改主服务器的参数配置

[root@localhost ~]# vi /mysqlsoft/mysql/my.cnf
gtid_mode=ON
enforce-gtid-consistency=true

修改两个从服务器的参数配置

[root@localhost /]# vi /mysqlsoft/mysql/my.cnf
gtid_mode=ON
enforce-gtid-consistency=true
skip-slave-start=1

[root@localhost mysql]# vi /mysqlsoft/mysql/my.cnf
gtid_mode=ON
enforce-gtid-consistency=true
skip-slave-start=1

根据您的配置,为mysqld提供额外的选项。

启动主服务器

[root@localhost ~]# service mysqld start
Starting MySQL.... SUCCESS!

启动两个从服务器

[root@localhost /]# service mysqld start
Starting MySQL.. SUCCESS!

[root@localhost mysql]# service mysqld start
Starting MySQL.. SUCCESS!

步骤4:配置从服务器使用基于gtid的自动定位。告诉从服务器使用基于GTID事务的主服务器作为复制数据源,并使用基于GTID的自动定位,而 不是基于文件的定位。在从服务器上发出CHANGE MASTER TO语句,包括MASTER_AUTO_POSITION选项告诉从服务器,主服务器的事务是由gtid标识 的。

您可能还需要为主服务器的主机名和端口号提供适当的值,以及用于从服务器连接到主服务器的复制用户帐户的用户名和密码;如果在步骤1之前 已经设置了这些选项,并且不需要进行进一步的更改,则可以安全地从这里显示的语句中省略相应的选项。

对所有从服务器执行以下命令

mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 2                                    |
| server_id_bits | 32                                   |
| server_uuid    | 1c5eb4fb-9479-11ec-8f21-005056a390e6 |
+----------------+--------------------------------------+
3 rows in set (0.06 sec)

mysql> CHANGE MASTER TO  MASTER_HOST='10.138.130.250',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)



mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 3                                    |
| server_id_bits | 32                                   |
| server_uuid    | f08c0957-3d11-11ef-bea6-005056b9a980 |
+----------------+--------------------------------------+
3 rows in set (0.00 sec)


mysql> CHANGE MASTER TO  MASTER_HOST='10.138.130.250',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

MASTER_LOG_FILE选项和MASTER_LOG_POS选项都不能在MASTER_AUTO_POSITION设置为1时使用。尝试这样做会导致CHANGE MASTER to语句失败并出现错误。

步骤5:做一个新的备份。在启用gtid之前所做的现有备份不能再在启用gtid后的这些服务器上使用。此时进行新的备份,这样就不会没有可用 的备份。

例如,您可以在进行备份的服务器上执行FLUSH LOGS。然后,要么显式地进行备份,要么等待您可能设置的任何定期备份例程的下一次迭代。
对主库执行备份

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)

[root@localhost ~]# mysqldump -uroot -pxxzx7817600 --all-databases --master-data > dbdump20240711.db
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that  changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete  dump, pass --all-databases --triggers --routines --events.

步骤6:启动从服务器并禁用只读模式。像这样启动slave:

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

只有在步骤1中将服务器配置为只读时,才需要执行以下步骤。要允许服务器再次开始接受更新,请发出以下语句:
从库

mysql> SET @@global.read_only = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@global.read_only = OFF;
Query OK, 0 rows affected (0.00 sec)

主库

mysql> SET @@global.read_only = OFF;
Query OK, 0 rows affected (0.00 sec)

基于gtid的复制现在应该正在运行,可以像以前一样在主服务器上开始(或恢复)进行操作。

下面来进行验证
主库

mysql> create table t5(id int primary key not null,name varchar(30),age int not null);
Query OK, 0 rows affected (0.07 sec)

mysql> desc t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

从库
1.

mysql> desc t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.

mysql> desc t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

主库:

mysql> insert into t5 values(1,'jingyong',39);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | jingyong |  39 |
+----+----------+-----+
1 row in set (0.01 sec)

从库
1.

mysql> select * from t5;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | jingyong |  39 |
+----+----------+-----+
1 row in set (0.00 sec)

2.

mysql> select * from t5;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | jingyong |  39 |
+----+----------+-----+
1 row in set (0.00 sec)

可以看到同步正常。

MySQL 5.7向主从复制环境增加从库

向复制环境添加从机
可以在不停止主复制的情况下向现有复制配置添加另一个从复制。相反,可以通过创建一个现有从属服务器的副本来设置新的从属服务器,只不 过要用不同的服务器id值来配置新的从属服务器。

复制一个已存在的从节点。
1.关闭现有从服务器:

[mysql@localhost ~]$ mysqladmin -uroot -pxxzx7817600 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

2.将数据目录从现有的从服务器复制到新的从服务器。您可以通过使用tar或WinZip创建存档,或者使用cp或rsync等工具执行直接复制来实现这 一点。确保还复制了日志文件和中继日志文件。

[root@localhost mysql]# rsync -avz * root@10.138.130.239:/mysqldata/mysql/
The authenticity of host '10.138.130.239 (10.138.130.239)' can't be established.
ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.239' (ECDSA) to the list of known hosts.
root@10.138.130.239's password:
sending incremental file list
binlog.006119
ib_buffer_pool
ib_logfile0
ibdata1
localhost-relay-bin.000004
localhost-relay-bin.000005
localhost-relay-bin.index
master.info
mysql.err
relay-log.info
.........

sent 86590 bytes  received 262429 bytes  4748.56 bytes/sec
total size is 22282098540  speedup is 63842.08

在添加新的复制从服务器时遇到的一个常见问题是,新的从服务器失败,并显示一系列警告和错误消息,如下所示:

071118 16:44:10 [Warning] Neither --relay-log nor --relay-log-index were used; so
replication may break when this MySQL server acts as a slave and has his hostname
changed!! Please use '--relay-log=new_slave_hostname-relay-bin' to avoid this problem.
071118 16:44:10 [ERROR] Failed to open the relay log './old_slave_hostname-relay-bin.003525'
(relay_log_pos 22940879)
071118 16:44:10 [ERROR] Could not find target log during relay log initialization
071118 16:44:10 [ERROR] Failed to initialize the master info structure

发生这种问题最可能的原因是启动mysql时没有指明–relay-log参数,在没有指定这个参数的情况下relay log会默认以主机名为前缀生成日志 文件。如果不指定–relay-log-index,relay log index文件也是如此。

要避免这个问题,把新从库的–relay-log的值设置为与原来的从库一致。如果原来的从库没有设置这个参数,那么把新从库的–relay-log参 数设置为existing_slave_hostname-relay-bin。如果不能这样设置,把原来从库的relay log索引文件复制到新从库,并设置新从库的– relay-log-index与原来从库的这个参数一致。如果原来从库没有设置–relay-log-index,把新从库的–relay-log-index设置为 existing_slave_hostname-relay-bin.index。

[root@localhost mysql]# vi /mysqlsoft/mysql/my.cnf
relay-log=localhost-relay-bin
relay-log-index=localhost-relay-bin.index

如果已经执行了后边步骤,在启动新从库的时候遇到这个问题,请执行以下步骤:

a.在新从库和原从库上执行stop slave

b.复制原来的从库的relay log索引文件到新从库,确保覆盖新从库的relay log索引文件。

c.执行后边的步骤。

3.复制原来从库的master info和relay log info文件到新从库。(这两个也可能存在表里边,跟如何配置的有关)这两个文件保存了当前的主 库binlog的坐标和从库relay log的坐标。

4.start原来的从库

[root@localhost mysql]# service mysqld start
Starting MySQL.. SUCCESS!

5.编辑新从库的配置文件,给新从库配置一个不同于现在主从结构中服务器的server-id

[root@localhost mysql]# vi /mysqlsoft/mysql/my.cnf
server-id=3

6.启动新从库。从服务器使用其主信息存储库中的信息启动复制进程。

[root@localhost mysql]# service mysqld start
Starting MySQL.. SUCCESS!

但是原来的从库出现以下错误

2024-07-08T09:50:20.245669Z 5 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary  log: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'binlog.000183'  at 1395, the last event read from '/mysqldata/mysql/binlog.000183' at 1848, the last byte read from  '/mysqldata/mysql/binlog.000183' at 1848.', Error_code: 1236


mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 2                                    |
| server_id_bits | 32                                   |
| server_uuid    | 1c5eb4fb-9479-11ec-8f21-005056a390e6 |
+----------------+--------------------------------------+
3 rows in set (0.00 sec)

mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 3                                    |
| server_id_bits | 32                                   |
| server_uuid    | 1c5eb4fb-9479-11ec-8f21-005056a390e6 |
+----------------+--------------------------------------+
3 rows in set (0.90 sec)

因为新从库是用原来的从库复制而来,所以在${data_dir}/auto.cnf中的server-uuid值是一样的,要处理该问题只需要删除新从库中的auto.cnf文件,就会自动创建和生成新的server-uuid值。

[root@localhost mysql]# ll auto.cnf
-rwxrwxr-x. 1 mysql mysql 56 Feb 23  2022 auto.cnf
[root@localhost mysql]# rm -rf auto.cnf
[root@localhost mysql]# ll auto.cnf
ls: cannot access auto.cnf: No such file or directory

[root@localhost mysql]# service mysqld start
Starting MySQL..... SUCCESS!
[root@localhost mysql]# ll auto.cnf
-rw-r-----. 1 mysql mysql 56 Jul  8 18:07 auto.cnf

mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 3                                    |
| server_id_bits | 32                                   |
| server_uuid    | f08c0957-3d11-11ef-bea6-005056b9a980 |
+----------------+--------------------------------------+
3 rows in set (0.00 sec)

验证主从同步
主库

mysql> create table t4(t_id int primary key not null,t_name varchar(30));
Query OK, 0 rows affected (0.02 sec)

mysql>  insert into t4 values(1,'jingyong');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4;
+------+----------+
| t_id | t_name   |
+------+----------+
|    1 | jingyong |
+------+----------+
1 row in set (0.00 sec)

原来从库

mysql> select * from t4;
+------+----------+
| t_id | t_name   |
+------+----------+
|    1 | jingyong |
+------+----------+
1 row in set (0.00 sec)

新从库

mysql> select * from t4;
+------+----------+
| t_id | t_name   |
+------+----------+
|    1 | jingyong |
+------+----------+
1 row in set (0.00 sec)

MySQL基于二进制日志文件的主从复制

基于二进制日志文件位置的复制配置概述
本节描述了基于二进制日志文件定位方法的MySQL服务器之间的复制,其中MySQL实例作为主(数据库更改的源)将更新和更改作为“事件”写入二 进制日志。根据记录的数据库变化,二进制日志中的信息以不同的日志格式存储。从节点被配置为从主节点读取二进制日志,并在从节点的本地 数据库上执行二进制日志中的事件。

每个从服务器接收二进制日志全部内容的副本。从服务器负责决定应该执行二进制日志中的哪些语句。除非您另行指定,否则主二进制日志中的 所有事件都在从二进制日志上执行。如果需要,可以将从服务器配置为只处理适用于特定数据库或表的事件。不能将主服务器配置为只记录某些事件

每个从服务器保存二进制日志坐标的记录:从服务器读取和处理的文件中的文件名和位置。这意味着可以将多个从服务器连接到主服务器,并执 行同一二进制日志的不同部分。因为从服务器控制这个过程,所以可以在不影响主服务器操作的情况下连接和断开各个从服务器。另外,由于每 个从服务器都在二进制日志中记录当前位置,因此有可能断开连接,重新连接,然后恢复处理。

主服务器和每个从服务器必须配置一个唯一的ID(使用server-id选项)。此外,每个从服务器必须配置有关主主机名、日志文件名和该文件中的 位置的信息。这些细节可以在从服务器上的MySQL会话中使用CHANGE MASTER TO的语句来控制。详细信息存储在从服务器的主信息存储库中,主 信息存储库可以是文件也可以是表。

设置基于二进制日志文件位置的复制
本节介绍如何设置MySQL服务器以使用基于二进制日志文件位置的复制。设置复制有许多不同的方法,具体使用哪种方法取决于设置复制的方式 ,以及主数据库中是否已经有数据。

有一些通用任务对所有设置都是通用的:
.在主服务器上,必须启用二进制日志记录并配置唯一的服务器ID。这可能需要重新启动服务器。

.在要连接到主服务器的每个从服务器上,必须配置唯一的服务器ID。这可能需要重新启动服务器。

.还可以为从服务器创建一个单独的用户,以便在与主服务器进行身份验证时读取二进制日志进行复制时使用。

.在创建数据快照或启动复制过程之前,应该在主服务器上记录二进制日志中的当前位置。在配置从服务器时需要这些信息,以便从服务器知道 在二进制日志的哪个位置开始执行事件。

.如果您已经在主服务器上有数据,并且希望使用它来同步从服务器,则需要创建数据快照将数据复制到从服务器上。您使用的存储引擎会影响 您创建快照的方式。在使用MyISAM时,必须停止处理主服务器上的语句以获得读锁,然后获取其当前二进制日志坐标并转储其数据,然后才允许 主服务器继续执行语句。如果不停止语句的执行,数据转储和主状态信息将不匹配,导致不一致。如果你使用的是InnoDB,你不需要一个读锁, 一个足够长的事务来传输数据快照就足够了。

.为从服务器配置连接到主服务器的设置,例如主机名、登录凭据、二进制日志文件名和位置。

注意:设置过程中的某些步骤需要SUPER权限。如果您没有此权限,则可能无法启用复制。

配置完基本选项后,选择您的场景:
.为不包含数据的主服务器和从服务器的新安装设置复制。

.使用现有MySQL服务器上的数据建立一个新的主服务器的复制。

.要向现有复制环境添加复制从节点。

设置复制主配置
要将主机配置为使用基于二进制日志文件位置的复制,必须启用二进制日志记录并建立唯一的服务器ID。如果还没有这样做,则需要重新启动服 务器。

必须在主服务器上启用二进制日志记录,因为二进制日志是将更改从主服务器复制到从服务器的基础。如果没有在使用log-bin选项的主机上启 用二进制日志记录,则无法进行复制。

复制组中的每个服务器必须配置唯一的服务器ID。该ID用于标识组内的各个服务器,必须是1 ~2的32次方?1之间的正整数。你如何组织和选择数 字是你的选择。

要配置二进制日志和服务器ID选项,请关闭MySQL服务器并编辑my.cnf或my.ini文件。在配置文件的[mysqld]部分中,添加log-bin和server-id 选项。如果这些选项已经存在,但是被注释掉了,取消这些选项的注释,并根据您的需要更改它们。例如,要使用日志文件名前缀mysql-bin启 用二进制日志记录,并配置服务器ID为1,使用以下行:

[mysqld]
log-bin=mysql-bin
server-id=1

进行更改后,重新启动服务器。

注意:
以下选项对该过程有影响:
.如果省略server-id(或将其显式设置为默认值0),主服务器将拒绝来自从服务器的任何连接。

.为了在使用InnoDB事务的复制设置中获得最大的持久性和一致性,您应该在主my.cnf文件中使用innodb_flush_log_at_trx_commit=1和 sync_binlog=1。

.确保在复制主机上未启用跳过网络连接选项。如果禁用网络连接,则从端无法与主端通信,导致复制失败。

创建复制用户
每个从服务器使用一个MySQL用户名和密码连接到主服务器,所以在主服务器上必须有一个从服务器可以用来连接的用户帐户。任何帐户都可以 用于此操作,前提是该帐户已被授予REPLICATION SLAVE特权。您可以选择为每个从服务器创建一个不同的帐户,或者为每个从服务器使用相同 的帐户连接到主服务器。

虽然不必专门为复制创建帐户,但应该知道复制用户名和密码以明文形式存储在主信息存储库文件或表中。因此,您可能需要创建一个单独的帐 户,该帐户仅对复制过程具有特权,以尽量减少危及其他帐户的可能性。

要创建一个新帐户,请使用create USER。要向该帐户授予复制所需的特权,请使用grant语句。如果仅为复制目的创建帐户,则该帐户只需要 replication SLAVE特权。例如,要设置一个新用户repl,它可以从任何主机连接进行复制,请在主服务器上发出以下语句:

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)


[root@localhost mysql]# mysql -h 10.13.13.50 -P 3306 -urepl -pslavepass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> exit

获取复制主二进制日志坐标
要配置从服务器以在正确的点启动复制过程,您需要主服务器的二进制日志中的当前坐标。

如果master之前没有启用二进制日志,那么SHOW MASTER STATUS或mysqldump –master-data显示的日志文件名和位置值都是空的。在这种情况 下,你需要在稍后指定从服务器的日志文件和位置时使用空字符串(“)和4。

如果之前主服务器已经有二进制日志记录,可以使用下面的过程来获取主服务器的二进制日志坐标:

警告
这个过程使用了FLUSH TABLES WITH READ LOCK,阻塞了InnoDB表的COMMIT操作。

1.通过命令行客户端连接到主服务器,在主服务器上启动会话,并通过执行FLUSH TABLES WITH READ LOCK语句来清空所有表和阻塞写语句。

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

警告
让发出FLUSH TABLES语句的客户端继续运行,这样读锁仍然有效。如果退出客户端,锁将被释放。

2.在主服务器上的另一个会话中,使用SHOW MASTER STATUS语句来确定当前二进制日志文件名和位置:

[root@localhost ~]# mysql -uroot -ptest12345 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000183 |      326 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

“File”列显示日志文件的名称,“Position”列显示在文件中的位置。在此例中,二进制日志文件为binlog.000183,位置是326。记录这些值 。稍后在设置slave时需要它们。它们表示从服务器开始处理来自主服务器的新更新的复制坐标。

现在,您已经获得了所需的信息,使从服务器能够从正确位置的二进制日志开始读取,从而开始复制。

下一步取决于您是否在主服务器上已有数据。选择下列选项之一:
.如果在开始复制之前有需要与从服务器同步的现有数据,请保持客户端运行,以便锁保持在适当位置。这可以防止任何进一步的更改,以便复 制到从服务器的数据与主服务器保持同步。

.如果您正在建立一个新的主从复制组,您可以退出第一个会话以释放读锁

选择数据快照的方法
如果主数据库包含现有数据,则有必要将这些数据复制到每个从数据库。从主数据库转储数据有不同的方法。以下部分描述了可能的选项。

要选择适当的转储数据库的方法,请在以下选项中进行选择:
.使用mysqldump工具创建要复制的所有数据库的转储。这是推荐的方法,特别是在使用InnoDB时。

.如果数据库存储在二进制可移植文件中,则可以将原始数据文件复制到从服务器。这可能比使用mysqldump并在每个slave上导入文件更有效, 因为它跳过了在重播INSERT语句时更新索引的开销。对于InnoDB这样的存储引擎,不建议这样做。

使用mysqldump创建数据快照
使用mysqldump工具对现有主数据库中的数据创建快照。完成数据转储后,在启动复制进程之前将该数据导入从服务器。

下面的例子将所有数据库转储到一个名为dbdump.db的文件中,并包含–masterdata选项,它会自动在slave上添加CHANGE MASTER TO语句以启动 复制过程:
[root@localhost ~]# mysqldump -uroot -ptest12345 –all-databases –master-data > dbdump.db
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll dbdump.db
-rw-r–r–. 1 root root 4657877334 Jul 2 16:49 dbdump.db
[root@localhost ~]# du -sh dbdump.db
4.5G dbdump.db

注意:
如果不使用–master-data,则必须手动锁定单独会话中的所有表。

使用mysqldump工具可以从转储中排除某些数据库。如果要选择转储中包含哪些数据库,请不要使用–all-databases。选择以下选项之一:
.使用–ignore-table选项排除数据库中的所有表

.使用–atabases选项只命名那些您想要转储的数据库

要导入数据,可以将转储文件复制到从服务器,或者在远程连接到从服务器时从主服务器访问该文件。

[root@localhost ~]# mysql -h 10.13.13.43 -P 3306 -uroot -ptest1234 < dbdump.db
mysql: [Warning] Using a password on the command line interface can be insecure.

使用原始数据文件创建数据快照
本节描述如何使用组成数据库的原始文件创建数据快照。对于使用具有复杂缓存或日志算法的存储引擎的表,使用这种方法需要额外的步骤来生 成完美的“时间点”快照:初始复制命令可能会忽略缓存信息和日志更新,即使您已经获得了全局读锁。存储引擎如何响应这取决于它的崩溃恢 复能力。

如果你使用的是InnoDB表,你可以使用MySQL企业版的mysqlbackup命令备份组件以生成一致性快照。该命令记录从机上要使用的快照对应的日志 名称和偏移量。

如果主服务器和从服务器的ft_stopword_file、ft_min_word_len或ft_max_word_len的值不同,并且您正在复制具有全文索引的表,那么这种方 法也不能可靠地工作。

假设上述异常不适用于您的数据库,请使用冷备份技术获取InnoDB表的可靠二进制快照:慢速关闭MySQL服务器,然后手动复制数据文件。

当您的MySQL数据文件存在于单个文件系统中时,要创建MyISAM表的原始数据快照,您可以使用标准的文件复制工具,如cp或copy,远程复制工 具,如scp或rsync,归档工具,如zip或tar,或文件系统快照工具,如dump。如果只复制某些数据库,则只复制与这些表相关的文件。对于 InnoDB,所有数据库中的所有表都存储在系统表空间文件中,除非你启用了innodb_file_per_table选项。

复制不需要以下文件:
.mysql数据库相关文件。

.主信息存储库文件(如果使用)。

.主机的二进制日志文件。

.任何中继日志文件。

根据您是否使用InnoDB表,选择以下一种:

如果你使用的是InnoDB表,并且想要获得与原始数据快照最一致的结果,请在此过程中关闭主服务器,如下所示:
1.获取读锁并获取主库状态

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

2.在一个单独的会话中,关闭主服务器:

shell> mysqladmin shutdown

3.复制MySQL数据文件。下面的示例展示了实现此目的的常用方法。你只需要选择其中一个:

shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata

4.重新启动主服务器。

如果您不使用InnoDB表,您可以从主服务器获取系统快照,而无需关闭服务器,如下所示:
1.获取读锁并获取主库状态

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

2.复制MySQL数据文件。下面的示例展示了实现此目的的常用方法。你只需要选择其中一个:

shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata

3.在获取读锁的客户端,释放该锁:

mysql> UNLOCK TABLES;

创建了数据库的存档或副本之后,在启动从复制进程之前,将文件复制到每个从服务器。

设置复制从服务器
下面描述如何设置从服务器。在你继续之前,确保你有:
.用必要的配置属性配置MySQL主机。

.获取主库状态信息。

.在主服务器上,释放读锁。

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

设置复制从服务器配置
每个复制从服务器必须有一个唯一的服务器ID。如果还没有这样做,从服务器设置的这一部分需要重新启动服务器。

如果从服务器ID尚未设置,或者当前值与您为主服务器选择的值冲突,请关闭从服务器并编辑配置文件的[mysqld]部分以指定唯一的服务器ID。 例如:

[root@localhost ~]# service mysqld stop
Shutting down MySQL. SUCCESS!

[mysql@localhost mysql]$ vi my.cnf
[mysqld]
server-id=2

进行更改后,重新启动服务器。

[root@localhost mysql]# service mysqld start
Starting MySQL... SUCCESS!

如果要设置多个从服务器,则每个从服务器必须具有唯一的服务器id值,该值与主服务器和任何其他从服务器的服务器id值不同。

注意:
如果省略server-id(或将其显式设置为默认值0),从服务器将拒绝连接到主服务器。

您不必在从服务器上启用二进制日志记录,就可以设置复制。但是,如果在从属服务器上启用二进制日志记录,则可以使用从属服务器的二进制 日志进行数据备份和崩溃恢复,还可以将从属服务器用作更复杂的复制拓扑的一部分。例如,这个从服务器充当其他从服务器的主库。

在从服务器上设置主配置
要设置从服务器与主服务器通信以进行复制,请使用必要的连接信息配置从服务器。为此,在从属服务器上执行以下语句,将选项值替换为与系 统相关的实际值:

mysql> CHANGE MASTER TO   MASTER_HOST='10.13.13.50',MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_LOG_FILE='binlog.000183',MASTER_LOG_POS=326 ;
Query OK, 0 rows affected, 2 warnings (0.14 sec)

注意:
复制不能使用Unix套接字文件。你必须能连接到主服务器MySQL服务器使用TCP/IP。CHANGE MASTER TO语句还有其他选项。例如,可以使用SSL设置安全复制。

启动从线程:

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

在执行此过程之后,从服务器连接到主服务器,并复制自拍摄快照以来在主服务器上发生的任何更新。

如果没有正确设置主服务器的server-id选项,则从服务器无法连接到主服务器。类似地,如果你没有为slave设置正确的server-id选项,你会 在slave的错误日志中得到以下错误:

Warning: You should set server-id to a non-0 value if master_host
is set; we will force server id to 2, but this MySQL server will
not act as a slave.

如果由于任何其他原因无法复制,您还可以在从属服务器的错误日志中找到错误消息。

从节点在其主服务器的信息存储库中存储了用户配置的关于主节点的信息。主服务器的信息存储库可以是文件的形式,也可以是表的形式,这由 –masterinfo- repository的值决定。当一个从节点使用–master-info-repository=FILE时,数据目录中会存储两个文件,分别名为 master.info和relay-log.info。相反,如果–master-info-repository=TABLE,则该信息保存在mysql数据库的master_slave_info表中。无论 哪种情况,都不要删除或编辑文件或表。始终使用CHANGE MASTER TO语句更改复制参数。从节点可以使用语句中指定的值自动更新状态文件。

注意:
主服务器的信息存储库的内容覆盖在命令行或my.cnf中指定的一些服务器选项。

一个主服务器的快照就可以满足多个从服务器的需求。要设置额外的从服务器,请使用相同的主服务器快照,并遵循上述过程的从服务器部分。

[root@localhost mysql]# pwd
/mysqldata/mysql
[root@localhost mysql]# ll master.info

[root@localhost mysql]# ll relay-log.info
-rw-r-----. 1 mysql mysql 62 Jul  4 17:50 relay-log.info

在主库中向表t9插入数据

mysql> select * from t9;
Empty set (0.00 sec)

mysql> desc t9;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into t9 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t9;
+------+
| c1   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

在从库上执行查询看数据是否已经同步过来

mysql> select * from t9;
+------+
| c1   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

主库日志文件内容如下:

[root@localhost ~]# tail -f /msyqldata/mysql/mysql.err
tail: cannot open ‘/msyqldata/mysql/mysql.err’ for reading: No such file or directory
tail: no files remaining
[root@localhost ~]# tail -f /mysqldata/mysql/mysql.err
2024-07-04T08:46:10.847512Z 9 [Warning] IP address '10.13.13.43' could not be resolved: Temporary failure in name  resolution
2024-07-04T08:46:10.859391Z 9 [Note] Start binlog_dump to master_thread_id(9) slave_server(2), pos(binlog.000183, 326)

从库日志文件内容如下:

[root@localhost ~]# tail -f /msyqldata/mysql/mysql.err
2024-07-04T07:57:29.132449Z 0 [Note] InnoDB: Buffer pool(s) load completed at 240704 15:57:29
2024-07-04T08:03:48.366164Z 2 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port=  3306, master_log_file='binlog.000183', master_log_pos= 326, master_bind=''. New state master_host='10.13.13.50',  master_port= 3306, master_log_file='binlog.000183', master_log_pos= 326, master_bind=''.
2024-07-04T08:44:33.000217Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 1981125ms. The settings might not be  optimal. (flushed=0 and evicted=0, during the time.)
2024-07-04T08:46:01.839437Z 3 [Warning] Storing MySQL user name or password information in the master info repository is not  secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see  the 'START SLAVE Syntax' in the MySQL Manual for more information.
2024-07-04T08:46:01.840179Z 4 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee  that the relay log info will be consistent, Error_code: 0
2024-07-04T08:46:01.840283Z 4 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'binlog.000183'  at position 326, relay log './localhost-relay-bin.000001' position: 4
2024-07-04T08:46:10.062847Z 3 [Note] Slave I/O thread for channel '': connected to master  'repl@10.13.13.50:3306',replication started in log 'binlog.000183' at position 326

MySQL InnoDB数据字典操作的故障处理

InnoDB数据字典操作的故障处理
表定义的信息存储在.frm文件和InnoDB数据字典中。如果您移动了.frm文件,或者服务器在数据字典操作过程中崩溃,这些信息源可能会变得不一致。如果数据字典损坏或一致性问题导致无法启动InnoDB

由于孤儿表导致CREATE TABLE失败
数据字典不同步的一个症状是CREATE TABLE语句失败。如果发生这种情况,请查看服务器的错误日志。如果日志显示这个表已经存在于InnoDB内 部数据字典中,那么在InnoDB表空间文件中就有一个孤儿表,没有相应的.frm文件。错误信息看起来像这样:

InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version < = 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

您可以按照错误消息中给出的说明删除孤立表。如果你仍然不能成功使用DROP TABLE,问题可能是由于mysql客户端的名称完成。要解决这个问 题,使用--skip-auto-rehash选项启动mysql客户端并再次尝试DROP TABLE表。(在名称完成的情况下,mysql试图构建一个表名列表,当存在上 述问题时,该列表会失败。)

无法打开文件错误
数据字典不同步的另一个症状是MySQL打印无法打开InnoDB文件的错误:

ERROR 1016: Can't open file: 'child2.ibd'. (errno: 1)

在错误日志中,您可以找到这样的消息:

InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?

这意味着在InnoDB中存在一个没有相应表的孤儿.frm文件。您可以通过手动删除.frm文件来删除它。

孤立中间表
如果MySQL在执行就地ALTER TABLE操作(ALGORITHM=INPLACE)的过程中退出,可能会留下一个孤立的中间表,占用系统空间。另外,在空的通用 表空间中存在孤立的中间表可以防止删除通用表空间。本节描述如何识别和删除孤立中间表。

中间表名以#sql-ib前缀开始(例如,#sql-ib87-856498050)。附带的.frm文件有一个#sql-*前缀,并且命名不同(例如,#sql-36ab_2.frm)。

要识别系统中的孤儿中间表,可以查询INFORMATION_SCHEMA.INNODB_SYS_TABLES。查找以#sql开头的表名。如果原始表驻留在file-per-table的 表空间中,对于孤儿中间表的表空间文件(#sql-*.ibd文件)应该在数据库目录中可见。

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

删除孤立中间表的步骤如下:
1.在数据库目录下,重命名#sql-*.frm文件,以匹配孤立中间表的基名:

#mv #sql-36ab_2.frm #sql-ib87-856498050.frm

注意:
如果没有.frm文件,您可以重新创建它。.frm文件必须具有与孤立中间表相同的表模式(它必须具有相同的列和索引),并且必须放置在孤立中间 表的数据库目录中。

2.通过drop table语句删除孤立的中间表,例如在表名前加上#mysql50#,并将表名括起来:

mysql> DROP TABLE `#mysql50##sql-ib87-856498050`;

#mysql50#前缀告诉MySQL忽略在MySQL5.1中引入的文件名安全编码。需要用反引号括起表名,以便对带有特殊字符(如“#”)的表名执行SQL语句 。

注意:
如果在将表移动到另一个表空间的就地ALTER TABLE操作期间发生崩溃,则恢复进程将表恢复到其原始位置,但在目标表空间中留下一个孤立的 中间表。

孤立临时表
如果MySQL在执行复制表的ALTER TABLE操作(ALGORITHM=COPY)时退出,可能会留下一个孤立的临时表,占用系统空间。另外,在一个空的通用表 空间中存在孤立临时表可以防止删除通用表空间。本节介绍如何识别和删除孤立临时表。

孤立临时表名以#sql-开头(例如,#sql-540_3)。附带的.frm文件具有与孤立临时表相同的基名。

注意:
如果没有.frm文件,您可以重新创建它。.frm文件必须具有与孤立临时表相同的表模式(它必须具有相同的列和索引),并且必须放置在孤立临时 表的数据库目录中

要识别系统上的孤立临时表,可以进行查询INFORMATION_SCHEMA.INNODB_SYS_TABLES。查找以#sql开头的表名。如果原始表驻留在file-per- table表空间中,则孤立临时表的表空间文件(#sql-*.ibd文件),在数据库目录中应该可以看到。

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

要删除孤立临时表,可以通过发出drop table语句删除表,例如在表名前加上#mysql50#,并将表名括起来:

mysql> DROP TABLE `#mysql50##sql-540_3`;

#mysql50#前缀告诉MySQL忽略在MySQL5.1中引入的文件名安全编码。需要用反引号括起表名,以便对带有特殊字符(如“#”)的表名执行SQL语句 。

表空间不存在
启用innodb_file_per_table后,如果.frm或.ibd文件(或两者)丢失,可能会出现以下消息:

InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.

如果出现这种情况,请尝试以下步骤来解决问题:
1.在其他数据库目录中创建一个匹配的.frm文件,并将其复制到孤立表所在的数据库目录中。

2.对原始表发出DROP TABLE命令。这样就可以成功地删除表,InnoDB应该会在错误日志中打印一个.ibd文件丢失的警告。

恢复孤立的File-Per-Table的ibd文件
这个过程描述了如何将孤立的file-per-table的ibd文件恢复到另一个MySQL实例。如果系统表空间丢失或不可恢复,并且希望在新的MySQL实例 上恢复.idb文件备份,则可以使用此过程。

通用表空间.ibd文件不支持此过程。

该过程假设您只有.ibd文件备份,您将恢复到最初创建.idb文件的MySQL的相同版本,并且.idb文件备份是干净的。
1.在新的MySQL实例上,在同名的数据库中重新创建表。

mysql> CREATE DATABASE test;
mysql> USE test;
mysql>  CREATE TABLE `user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

2.丢弃新创建表的表空间

mysql> ALTER TABLE test.user DISCARD TABLESPACE;

3.将孤立.idb文件从备份目录复制到新的数据库目录。

scp root@10.13.13.25:/mysqldata/mysql/test/user.ibd /mysqldata/mysql/test/

4.确保.ibd文件具有必要的文件权限。

[root@localhost test]# chown -R mysql:mysql user.ibd
[root@localhost test]# chmod 660 user.ibd

5.导入孤立.ibd文件。发出一个警告,表示InnoDB将尝试在没有模式验证的情况下导入文件。

mysql> ALTER TABLE test.user IMPORT TABLESPACE; SHOW WARNINGS;
Query OK, 0 rows affected, 1 warning (0.18 sec)

+---------+------ +----------------------------------------------------------------------------------------------------------------------------- --------------+
| Level   | Code | Message                                                                                                                                    |
+---------+------ +----------------------------------------------------------------------------------------------------------------------------- --------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/user.cfg', will attempt to  import without schema verification |
+---------+------ +----------------------------------------------------------------------------------------------------------------------------- --------------+
1 row in set (0.00 sec)

6.查询表以验证.ibd文件是否已成功恢复。

mysql> select * from test.user;
+-----+------------------------------------------------------------------------------+
| uid | data                                                                         |
+-----+------------------------------------------------------------------------------+
|   1 | {"mail": "jiangchengyao@gmail.com", "name": "David", "address": "Shangahai"} |
|   2 | {"mail": "amy@gmail.com", "name": "Amy"}                                     |
+-----+------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

MySQL InnoDB memcached插件

InnoDB memcached插件
InnoDB memcached插件(daemon_memcached)提供了一个集成的memcached守护进程,可以自动存储和检索InnoDB表中的数据,将MySQL服务器变成一个快速的“key value store”。您可以使用简单的get、set和incr操作来避免与SQL解析和构造查询优化计划相关的性能开销,而不是在SQL 中制定查询。您还可以通过SQL访问相同的InnoDB表,以获得便利性,复杂的查询,批量操作以及传统数据库软件的其他优势。

这个“nosql-style”的接口使用memcached API来加速数据库操作,让InnoDB使用其缓冲池机制处理内存缓存。在InnoDB表中,通过memcached操作(如add、set和incr)修改的数据被存储在磁盘中。

InnoDB memcached插件的好处
下面概述daemon_memcached插件的优点。InnoDB表和memcached的组合比单独使用任何一个都有优势。
.直接访问InnoDB存储引擎避免了SQL的解析和规划开销。

.在与MySQL服务器相同的进程空间中运行memcached可以避免来回传递请求的网络开销。

.使用memcached协议写入的数据被透明地写入到InnoDB表中,而不需要经过MySQL SQL层。更新非关键数据时,可以通过控制写频率来获得更高的原始性能。

.通过memcached协议请求的数据可以透明地从InnoDB表中查询,而无需经过MySQL SQL层。

.对相同数据的后续请求由InnoDB缓冲池提供。缓冲池处理内存中的缓存。您可以使用InnoDB配置选项来调优数据密集型操作的性能。

.根据应用程序的类型,数据可以是非结构化的,也可以是结构化的。您可以为数据创建一个新表,也可以使用现有的表。

.InnoDB可以将多个列值组合和分解为单个memcached项值,从而减少应用程序中需要解析和连接字符串的数量。例如,你可以将字符串值2|4| 6|8存储在memcached缓存中,并让InnoDB根据分隔符分割该值,然后将结果存储在4个数值列中。

.内存和磁盘之间的传输是自动处理的,简化了应用程序逻辑。

.数据存储在MySQL数据库中,以防止崩溃、中断和损坏。

.你可以通过SQL访问底层的InnoDB表,用于报告、分析、即席查询、批量加载、多步事务计算、集合操作(如union和intersection),以及其他适合SQL表现力和灵活性的操作。

.可以在主服务器上使用daemon_memcached插件并结合MySQL复制来确保高可用性。

.memcached与MySQL的集成提供了一种持久化内存数据的方法,这样您就可以将它用于更重要的数据类型。您可以在应用程序中使用更多的add、incr和类似的写操作,而不必担心数据可能丢失。您可以停止和启动memcached服务器,而不会丢失对缓存数据的更新。为了防止意外的中断,你可以利用InnoDB的崩溃恢复、复制和备份功能。

.InnoDB快速主键查找的方式非常适合memcached的单元素查询。daemon_memcached插件使用的直接、低级数据库访问路径对于键值查找来说比等价的SQL查询要高效得多。

.memcached的序列化特性可以将复杂的数据结构、二进制文件甚至代码块转换为可存储的字符串,它提供了一种将此类对象放入数据库的简单方 法。

.因为可以通过SQL访问底层数据,所以可以生成报告、跨多个键进行搜索或更新,并在memcached数据上调用AVG()和MAX()等函数。使用 memcached所有这些操作都是昂贵或复杂的。

.不需要在启动时手动将数据加载到memcached中。当应用程序请求特定的键时,值会自动从数据库中检索出来,并使用InnoDB缓冲池缓存在内存 中。

.因为memcached消耗的CPU相对较少,而且它的内存占用很容易控制,所以它可以在同一个系统上与MySQL实例一起轻松运行。

.因为数据一致性是由常规InnoDB表使用的机制强制执行的,所以你不必担心memcached数据过期,或者在缺少键的情况下查询数据库的回滚逻辑 。

InnoDB memcached架构
InnoDB memcached插件将memcached作为一个MySQL插件守护进程来实现直接访问InnoDB存储引擎,绕过MySQL SQL层。

daemon_memcached插件的特性:
.Memcached作为mysqld的守护程序插件。mysqld和memcached都运行在相同的进程空间中,对数据的访问延迟非常低。

.直接访问InnoDB表,绕过SQL解析器,优化器,甚至Handler API层。

.标准memcached协议,包括基于文本的协议和二进制协议。daemon_memcached插件通过memcapable命令的所有55个兼容性测试。

.多列支持。您可以将多个列映射到键/值存储的“值”部分,列值由用户指定的分隔符分隔。

.默认情况下,memcached协议用于直接向InnoDB读取和写入数据,让MySQL使用InnoDB缓冲池管理内存缓存。默认设置代表了高可靠性和数据库 应用程序最少意外的组合。例如,默认设置避免在数据库端提交未提交的数据,或者为memcached get请求返回过时的数据。

.高级用户可以将系统配置为传统的memcached服务器,所有数据只缓存在memcached引擎(内存缓存)中,或者使用“memcached引擎”(内存缓存) 和InnoDB memcached引擎(InnoDB作为后端持久存储)的组合。

.通过innodb_api_bk_commit_interval、daemon_memcached_r_batch_size和daemon_memcached_w_batch_size配置选项来控制数据在InnoDB和 memcached操作之间来回传递的频率。批量大小选项的默认值为1,表示最大可靠性。

.通过daemon_memcached_option配置参数指定memcached选项的能力。例如,您可以更改memcached监听的端口,减少最大同时连接数,更改键值 对的最大内存大小,或打开错误日志的调试消息。

.innodb_api_trx_level配置选项控制memcached处理的查询的事务隔离级别。尽管memcached没有事务的概念,但是您可以使用这个选项来控制 memcached对daemon_memcached插件使用的表发出的SQL语句所引起的更改的处理速度。默认情况下,innodb_api_trx_level设置为READ UNCOMMITTED。

.innodb_api_enable_mdl选项可以用来在MySQL级别锁定表,这样映射的表就不能被DDL通过SQL接口删除或修改。如果没有锁,表可以从MySQL层 删除,但仍然保留在InnoDB存储中,直到memcached或其他用户停止使用它。MDL代表“元数据锁定”。

InnoDB memcached与传统memcached的区别
您可能已经熟悉在MySQL中使用memcached,如使用MySQL与memcached中所述。本节描述集成的InnoDB memcached插件与传统memcached的不同之 处。
.安装: memcached库随MySQL服务器一起提供,使得安装和设置相对容易。安装过程中需要运行innodb_memcached_config.sql脚本创建一个 demo_test表供memcached使用,发出INSTALL PLUGIN语句以启用daemon_memcached插件,并将所需的memcached选项添加到MySQL配置文件或启动 脚本中。对于memcp、memcat和memcapable等其他实用程序,您可能仍然需要安装传统的memcached发行版。

.部署:对于传统的memcached,通常会运行大量低容量的memcached服务器。但是,典型的daemon_memcached插件部署只涉及少量已经在运行 MySQL的中等或高性能服务器。这种配置的好处是提高了单个数据库服务器的效率,而不是利用未使用的内存或在大量服务器上分布查找。在默 认配置中,memcached只使用很少的内存,而内存中的查找是从InnoDB缓冲池中提供的,它会自动缓存最新和频繁使用的数据。与传统的MySQL服 务器实例一样,保持innodb_buffer_pool_size配置选项的值尽可能高(不引起操作系统级别的分页),以便尽可能多的工作在内存中.终止执行。

.过期:默认情况下(即使用innodb_only缓存策略),总是返回InnoDB表中最新的数据,因此过期选项没有实际作用。如果将缓存策略更改为缓存 或仅缓存,则过期选项照常工作,但是如果在从内存缓存过期之前在基础表中更新了请求的数据,则请求的数据可能会过期。

.命名空间:memcached就像一个大目录,您可以为文件提供带有前缀和后缀的详细名称,以防止文件发生冲突。daemon_memcached插件允许对键 使用类似的命名约定,但增加了一点。Key名称格式为@@table_id.key.table_id被解码为引用一个特定的表,使用来自 innodb_memcache.containers表的映射数据。在指定的表中查找或写入键。

@@符号只适用于get、add和set函数的单独调用,而不适用于诸如incr或delete之类的其他调用。要为会话中的后续memcached操作指定一个默认 表,可以使用带table_id的@@符号执行get请求,但不带key部分。例如:

get @@table_id

后续的get、set、incr、delete等操作将使用innodb_memcache.containers.name列中table_id指定的表。

.哈希和分布: 默认配置使用innodb_only缓存策略,适合传统的部署配置,即所有数据都在所有服务器上可用,例如一组复制从服务器。

如果像在分片配置中那样物理地划分数据,就可以在运行daemon_memcached插件的多台机器上划分数据,并使用传统的memcached散列机制将请 求路由到特定的机器上。在MySQL端,通常通过add请求向memcached插入所有数据,以便将适当的值存储在适当的服务器上的数据库中。

.内存使用:默认情况下(在innodb_only缓存策略下),memcached协议在InnoDB表之间来回传递信息,InnoDB缓冲池处理内存查找,而不是 memcached内存使用的增长和收缩。memcached端使用的内存相对较少。

如果将缓存策略切换为缓存或仅缓存,则适用memcached内存使用的常规规则。用于memcached数据值的内存是按“slab”分配的。您可以控制 slab大小和用于memcached的最大内存。无论采用哪种方式,都可以使用熟悉的统计系统(例如,通过telnet会话通过标准协议访问)监视daemon_memcached插件并对其进行故障排除。额 外的实用程序不包含在daemon_memcached插件中。您可以使用memcached-tool脚本来安装完整的memcached发行版。

线程使用:MySQL线程和memcached线程共存于同一服务器上。操作系统对线程施加的限制适用于线程总数。

日志使用:由于memcached守护进程与MySQL服务器一起运行并写入stderr,因此用于记录日志的-v、-vv和-vvv选项将输出写入MySQL错误日志。

memcached操作:熟悉的memcached操作,如get、set、add和delete都是可用的。序列化(即表示复杂数据结构的确切字符串格式)取决于语言接口 。

使用memcached作为MySQL前端:这是InnoDB memcached插件的主要目的。集成的memcached守护进程提高了应用程序的性能,并且让InnoDB处理内 存和磁盘之间的数据传输简化了应用程序的逻辑。

工具:MySQL服务器包括libmemcached库,但不包括其他命令行实用程序。要使用memcp、memcat和memcapable等命令,请安装完整的memcached发 行版。当memm和memflush从缓存中删除项时,这些项也会从底层InnoDB表中删除。

编程接口:可以使用所有支持的语言(C和c++、Java、Perl、Python、PHP和Ruby),通过daemon_memcached插件访问MySQL服务器。与传统的 memcached服务器一样指定服务器主机名和端口。默认情况下,daemon_memcached插件在端口11211上监听。可以同时使用文本和二进制协议。您 可以在运行时自定义memcached函数的行为。序列化(即表示复杂数据结构的确切字符串格式)取决于语言接口。

常见问题:MySQL为传统的memcached提供了广泛的常见问题解答。常见问题解答是最适用的,除了使用InnoDB表作为memcached数据的存储介质意 味着您可以将memcached用于比以前更写密集型的应用程序,而不是作为只读缓存。

设置InnoDB memcached插件
下面描述如何在MySQL服务器上设置daemon_memcached插件。因为memcached守护进程与MySQL服务器紧密集成,以避免网络流量并最小化延迟, 所以您可以在使用此特性的每个MySQL实例上执行此过程。

先决条件
.daemon_memcached插件仅支持Linux、Solaris和OS X平台。不支持其他操作系统。

.当从源代码构建MySQL时,您必须使用-DWITH_INNODB_MEMCACHED=ON进行构建。这个构建选项在MySQL插件目录(plugin_dir)中生成两个共享库 ,用于运行daemon_memcached插件:
.libmemcached.so:MySQL的memcached守护进程插件。

.innodb_engine.so:memcached的一个InnoDB API插件。

.Libevent必须安装。
.如果您没有从源代码构建MySQL,则libevent库不会包含在您的安装中。请按照操作系统的安装方法安装libevent 1.4.12及以上版本。例如, 根据不同的操作系统,你可能会使用apt-get、yum或port install。例如,在Ubuntu Linux中,使用:

sudo apt-get install libevent-dev

.如果您从源代码版本安装MySQL,则libevent 1.4.12与软件包捆绑在一起,位于MySQL源代码目录的顶层。如果您使用的是捆绑版本的 libevent,则无需执行任何操作。如果您想使用本地系统版本的libevent,则必须将-DWITH_LIBEVENT build选项设置为system或yes来构建 MySQL。

安装和配置InnoDB memcached插件
1.运行位于MYSQL_HOME/share中的innodb_memcached_config.sql配置脚本配置daemon_memcached插件,使其可以与InnoDB表交互。这个脚本安 装了innodb_memcache数据库,包含三个必需的表(cache_policies, config_options和containers)。它还将demo_test样例表安装到test数据库 中。

mysql> source /mysqlsoft/mysql/share/innodb_memcached_config.sql
Query OK, 1 row affected (0.01 sec)

Database changed
Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.09 sec)

Query OK, 0 rows affected (0.09 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected, 1 warning (0.04 sec)

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Query OK, 0 rows affected (0.07 sec)

Query OK, 1 row affected (0.03 sec)

执行innodb_memcached_config.sql脚本是一次性操作。如果以后卸载并重新安装daemon_memcached插件,这些表将保留在原来的位置。

mysql> use innodb_memcache
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_innodb_memcache |
+---------------------------+
| cache_policies            |
| config_options            |
| containers                |
+---------------------------+
3 rows in set (0.00 sec)


mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| demo_test      |
+----------------+
1 row in set (0.00 sec)

在这些表中,innodb_memcache.containers表是最重要的。containers表中的条目提供了到InnoDB表列的映射。daemon_memcached插件使用的每 个InnoDB表都需要在containers表中添加一个条目。

innodb_memcached_config.sql脚本在容器表中插入单个条目,该条目为demo_test表提供映射。它还将一行数据插入demo_test表中。该数据允 许您在安装完成后立即验证安装。

mysql> select * from innodb_memcache.containers\G;
*************************** 1. row ***************************
                  name: aaa
             db_schema: test
              db_table: demo_test
           key_columns: c1
         value_columns: c2
                 flags: c3
            cas_column: c4
    expire_time_column: c5
unique_idx_name_on_key: PRIMARY
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select * from test.demo_test;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| AA | HELLO, HELLO |    8 |    0 |    0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)



mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.18 sec)

一旦安装了这个插件,每次MySQL服务器重新启动时,它就会自动激活。

验证InnoDB和memcached的安装
要验证daemon_memcached插件设置,请使用telnet会话发出memcached命令。默认情况下,memcached守护进程监听端口11211。

[root@localhost /]# netstat -ltnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:11211           0.0.0.0:*               LISTEN      998/mysqld
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1630/sshd
tcp6       0      0 :::3306                 :::*                    LISTEN      998/mysqld
tcp6       0      0 :::11211                :::*                    LISTEN      998/mysqld
tcp6       0      0 :::22                   :::*                    LISTEN      1630/sshd

1.从test.demo_test表中检索数据。demo_test表中的单行数据的键值为AA。

mysql> select * from test.demo_test;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| AA | HELLO, HELLO |    8 |    0 |    0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)


[root@localhost /]# telnet localhost 11211
Trying ::1...
Connected to localhost.
Escape character is '^]'.
get AA
VALUE AA 8 12
HELLO, HELLO
END

2.使用set命令插入数据。

set BB 10 0 16
GOODBYE, GOODBYE
STORED

说明:
.set命令用来存储值

.BB 是键

.10是操作标志;被memcached忽略,但可以被客户端用来表示任何类型的信息;如果未使用,则指定0

.0为过期时间(TTL);如果未使用,则指定0

.16是所提供值块的长度,以字节为单位

.GOODBYE, GOODBYE是存储的值

3.通过连接MySQL服务器,查询test.demo_test表,验证插入的数据是否存储在MySQL中。

mysql> select * from test.demo_test;
+----+------------------+------+------+------+
| c1 | c2               | c3   | c4   | c5   |
+----+------------------+------+------+------+
| AA | HELLO, HELLO     |    8 |    0 |    0 |
| BB | GOODBYE, GOODBYE |   10 |    1 |    0 |
+----+------------------+------+------+------+
2 rows in set (0.00 sec)

4.返回到telnet会话并检索您先前使用BB键插入的数据。

get BB
VALUE BB 10 16
GOODBYE, GOODBYE
END

如果关闭MySQL服务器,同时也关闭了集成的memcached服务器,那么进一步访问memcached数据的尝试将会失败,并出现连接错误。通常, memcached数据在此时也会消失,当memcached重启时,需要应用程序逻辑将数据加载回内存。但是,InnoDB memcached插件会自动完成这个过
程。

重新启动MySQL时,get操作再次返回存储在早期memcached会话中的键/值对。当一个键被请求并且相关的值不在内存缓存中时,该值将自动从 MySQL test.demo_test表中查询。

创建新的表和列映射
这个例子展示了如何使用daemon_memcached插件设置自己的InnoDB表。
1.创建一个InnoDB表。表必须有一个具有唯一索引的键列。city表的键列是city_id,它被定义为主键。该表还必须包括用于标记、cas和过期值 的列。可能有一个或多个值列。城市表有三个值列(名称、州、国家)。

mysql> create table test.city (
    -> city_id varchar(32),
    -> name varchar(1024),
    -> state varchar(1024),
    -> country varchar(1024),
    -> flags int,
    -> cas bigint unsigned,
    -> expiry int,
    -> primary key(city_id)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

2.在innodb_memcache中添加一个表项。以便daemon_memcached插件知道如何访问InnoDB表。该表项必须满足innodb_memcache.containers表定义。

mysql> desc innodb_memcache.containers;
+------------------------+--------------+------+-----+---------+-------+
| Field                  | Type         | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+-------+
| name                   | varchar(50)  | NO   | PRI | NULL    |       |
| db_schema              | varchar(250) | NO   |     | NULL    |       |
| db_table               | varchar(250) | NO   |     | NULL    |       |
| key_columns            | varchar(250) | NO   |     | NULL    |       |
| value_columns          | varchar(250) | YES  |     | NULL    |       |
| flags                  | varchar(250) | NO   |     | 0       |       |
| cas_column             | varchar(250) | YES  |     | NULL    |       |
| expire_time_column     | varchar(250) | YES  |     | NULL    |       |
| unique_idx_name_on_key | varchar(250) | NO   |     | NULL    |       |
+------------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

city表的innodb_memcache.containers表项定义为:

mysql> insert into innodb_memcache.containers (
    -> name, db_schema, db_table, key_columns, value_columns,
    -> flags, cas_column, expire_time_column, unique_idx_name_on_key)
    -> values ('default', 'test', 'city', 'city_id', 'name|state|country',
    -> 'flags','cas','expiry','primary');
Query OK, 1 row affected (0.04 sec)

.default指定给containers.name列以将ctiy表配置为默认值,InnoDB表与daemon_memcached插件一起使用。

.多个InnoDB表列(name, state, country)被映射containers.value_columns使用” | “分隔符。

.innodb_memcache.containers的flags、cas_column和expire_time_column字段在使用daemon_memcached插件的应用程序中通常不重要。但是, 每个表都需要一个指定的InnoDB表列。在插入数据时,如果这些列未使用,则将它们指定为0。

3.更新innodb_memcache.containers表后,重新启动daemon_memcache插件以应用更改。

mysql> UNINSTALL PLUGIN daemon_memcached;
Query OK, 0 rows affected (2.01 sec)

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.10 sec)

4.通过telnet,使用memcached set命令向city表插入数据。

[root@localhost ~]# telnet localhost 11211
Trying ::1...
Connected to localhost.
Escape character is '^]'.
set B 0 0 22
BANGALORE|BANGALORE|IN
STORED

5.使用MySQL查询test.city表,以验证插入的数据是否已存储。

mysql> select * from test.city;
+---------+-----------+-----------+---------+-------+------+--------+
| city_id | name      | state     | country | flags | cas  | expiry |
+---------+-----------+-----------+---------+-------+------+--------+
| B       | BANGALORE | BANGALORE | IN      |     0 |    2 |      0 |
+---------+-----------+-----------+---------+-------+------+--------+
1 row in set (0.00 sec)

6.使用MySQL,向test.city表插入额外的数据。

mysql> insert into city values ('c','chennai','tamil nadu','in', 0, 0 ,0);
Query OK, 1 row affected (0.02 sec)

mysql> insert into city values ('d','delhi','delhi','in', 0, 0, 0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into city values ('h','hyderabad','telangana','in', 0, 0, 0);

Query OK, 1 row affected (0.05 sec)

mysql> insert into city values ('m','mumbai','maharashtra','in', 0, 0, 0);
Query OK, 1 row affected (0.02 sec)

7.使用telnet,发出memcached get命令来检索使用MySQL插入的数据。

get H
VALUE H 0 22
hyderabad|telangana|in
END
get C
VALUE C 0 21
chennai|tamil nadu|in
END

MySQL InnoDB和MySQL复制

InnoDB和MySQL复制
MySQL复制适用于InnoDB表和MyISAM表。当从节点上的存储引擎与主节点上的原始存储引擎不同时,也可以使用复制。例如,用户可以将对主节点上的InnoDB表的修改复制到从节点上的MyISAM表。

在主服务器上失败的事务完全不会影响复制。MySQL复制基于二进制日志,MySQL在其中写入修改数据的SQL语句。一个失败的事务(例如,因为违反了外键,或者因为回滚)不会被写入二进制日志,因此它不会被发送到从服务器。

复制和级联
只有当共享外键关系的表同时在主从上使用InnoDB时,主上InnoDB表的级联操作才会在从上复制。无论使用基于语句的复制还是基于行的复制,都是如此。假设已经启动了复制,然后使用下面的create TABLE语句在master上创建了两个表:

CREATE TABLE fc1 (
i INT PRIMARY KEY,
j INT
) ENGINE = InnoDB;

CREATE TABLE fc2 (
m INT PRIMARY KEY,
n INT,
FOREIGN KEY ni (n) REFERENCES fc1 (i)
ON DELETE CASCADE
) ENGINE = InnoDB;

假设从服务器没有启用InnoDB支持。如果是这种情况,那么从节点上的表会被创建,但是它们使用MyISAM存储引擎,并且外键选项会被忽略。现在我们向master上的表中插入一些行:

master> INSERT INTO fc1 VALUES (1, 1), (2, 2);
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
master> INSERT INTO fc2 VALUES (1, 1), (2, 2), (3, 1);
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0

此时,在主节点和从节点上,表fc1包含2行,表fc2包含3行,如下所示:

master> SELECT * FROM fc1;
+---+------+
| i | j |
+---+------+
| 1 | 1 |
| 2 | 2 |
+---+------+
2 rows in set (0.00 sec)
master> SELECT * FROM fc2;
+---+------+
| m | n |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
+---+------+
3 rows in set (0.00 sec)
slave> SELECT * FROM fc1;
+---+------+
| i | j |
+---+------+
| 1 | 1 |
| 2 | 2 |
+---+------+
2 rows in set (0.00 sec)
slave> SELECT * FROM fc2;
+---+------+
| m | n |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
+---+------+
3 rows in set (0.00 sec)

现在假设你在主服务器上执行了下面的DELETE语句:

master> DELETE FROM fc1 WHERE i=1;
Query OK, 1 row affected (0.09 sec)

由于级联,主服务器表fc2现在只包含1行:

master> SELECT * FROM fc2;
+---+---+
| m | n |
+---+---+
| 2 | 2 |
+---+---+
1 row in set (0.00 sec)

但是,级联不会传播到从节点上,因为从节点上对fc1的DELETE不会从fc2中删除任何行。从节点的fc2副本仍然包含原来插入的所有行:

slave> SELECT * FROM fc2;
+---+---+
| m | n |
+---+---+
| 1 | 1 |
| 3 | 1 |
| 2 | 2 |
+---+---+
3 rows in set (0.00 sec)

这种差异是由于级联删除是由InnoDB存储引擎在内部处理的,这意味着没有任何更改被记录下来。

Linux 使用源码安装mysqlfrm

由于mysqlfrm是mysql-utilities工具一部分,那么我们安装mysql-utilities即可,下载好对应的源码包,进行编译安装
1、mysqlfrm安装

[root@localhost soft]# tar -xvzf mysql-utilities-1.6.5.tar.gz
mysql-utilities-1.6.5/
mysql-utilities-1.6.5/PKG-INFO
mysql-utilities-1.6.5/README.txt
mysql-utilities-1.6.5/mysql/
mysql-utilities-1.6.5/mysql/utilities/
mysql-utilities-1.6.5/mysql/utilities/common/
mysql-utilities-1.6.5/mysql/utilities/common/pattern_matching.py
mysql-utilities-1.6.5/mysql/utilities/common/server.py
mysql-utilities-1.6.5/mysql/utilities/common/user.py
mysql-utilities-1.6.5/mysql/utilities/common/variables.py
mysql-utilities-1.6.5/mysql/utilities/common/ip_parser.py
mysql-utilities-1.6.5/mysql/utilities/common/replication_ms.py
mysql-utilities-1.6.5/mysql/utilities/common/audit_log_parser.py
mysql-utilities-1.6.5/mysql/utilities/common/__init__.py
mysql-utilities-1.6.5/mysql/utilities/common/lock.py
mysql-utilities-1.6.5/mysql/utilities/common/tools.py
mysql-utilities-1.6.5/mysql/utilities/common/topology.py
mysql-utilities-1.6.5/mysql/utilities/common/table.py
mysql-utilities-1.6.5/mysql/utilities/common/charsets.py
mysql-utilities-1.6.5/mysql/utilities/common/parser.py
mysql-utilities-1.6.5/mysql/utilities/common/messages.py
mysql-utilities-1.6.5/mysql/utilities/common/gtid.py
mysql-utilities-1.6.5/mysql/utilities/common/audit_log_reader.py
mysql-utilities-1.6.5/mysql/utilities/common/database.py
mysql-utilities-1.6.5/mysql/utilities/common/grants_info.py
mysql-utilities-1.6.5/mysql/utilities/common/options.py
mysql-utilities-1.6.5/mysql/utilities/common/my_print_defaults.py
mysql-utilities-1.6.5/mysql/utilities/common/utilities.py
mysql-utilities-1.6.5/mysql/utilities/common/options_parser.py
mysql-utilities-1.6.5/mysql/utilities/common/binary_log_file.py
mysql-utilities-1.6.5/mysql/utilities/common/daemon.py
mysql-utilities-1.6.5/mysql/utilities/common/binlog.py
mysql-utilities-1.6.5/mysql/utilities/common/frm_reader.py
mysql-utilities-1.6.5/mysql/utilities/common/sql_transform.py
mysql-utilities-1.6.5/mysql/utilities/common/dbcompare.py
mysql-utilities-1.6.5/mysql/utilities/common/console.py
mysql-utilities-1.6.5/mysql/utilities/common/topology_map.py
mysql-utilities-1.6.5/mysql/utilities/common/format.py
mysql-utilities-1.6.5/mysql/utilities/common/rpl_sync.py
mysql-utilities-1.6.5/mysql/utilities/common/replication.py
mysql-utilities-1.6.5/mysql/utilities/__init__.py
mysql-utilities-1.6.5/mysql/utilities/command/
mysql-utilities-1.6.5/mysql/utilities/command/dbexport.py
mysql-utilities-1.6.5/mysql/utilities/command/rpl_admin.py
mysql-utilities-1.6.5/mysql/utilities/command/__init__.py
mysql-utilities-1.6.5/mysql/utilities/command/userclone.py
mysql-utilities-1.6.5/mysql/utilities/command/binlog_admin.py
mysql-utilities-1.6.5/mysql/utilities/command/serverclone.py
mysql-utilities-1.6.5/mysql/utilities/command/grep.py
mysql-utilities-1.6.5/mysql/utilities/command/indexcheck.py
mysql-utilities-1.6.5/mysql/utilities/command/grants.py
mysql-utilities-1.6.5/mysql/utilities/command/check_rpl.py
mysql-utilities-1.6.5/mysql/utilities/command/setup_rpl.py
mysql-utilities-1.6.5/mysql/utilities/command/dbimport.py
mysql-utilities-1.6.5/mysql/utilities/command/failover_console.py
mysql-utilities-1.6.5/mysql/utilities/command/dbcopy.py
mysql-utilities-1.6.5/mysql/utilities/command/proc.py
mysql-utilities-1.6.5/mysql/utilities/command/rpl_sync_check.py
mysql-utilities-1.6.5/mysql/utilities/command/serverinfo.py
mysql-utilities-1.6.5/mysql/utilities/command/failover_daemon.py
mysql-utilities-1.6.5/mysql/utilities/command/dbcompare.py
mysql-utilities-1.6.5/mysql/utilities/command/read_frm.py
mysql-utilities-1.6.5/mysql/utilities/command/diskusage.py
mysql-utilities-1.6.5/mysql/utilities/command/utilitiesconsole.py
mysql-utilities-1.6.5/mysql/utilities/command/audit_log.py
mysql-utilities-1.6.5/mysql/utilities/command/diff.py
mysql-utilities-1.6.5/mysql/utilities/command/show_rpl.py
mysql-utilities-1.6.5/mysql/utilities/exception.py
mysql-utilities-1.6.5/mysql/__init__.py
mysql-utilities-1.6.5/mysql/connector/
mysql-utilities-1.6.5/mysql/connector/custom_types.py
mysql-utilities-1.6.5/mysql/connector/__init__.py
mysql-utilities-1.6.5/mysql/connector/charsets.py
mysql-utilities-1.6.5/mysql/connector/errors.py
mysql-utilities-1.6.5/mysql/connector/cursor_cext.py
mysql-utilities-1.6.5/mysql/connector/abstracts.py
mysql-utilities-1.6.5/mysql/connector/utils.py
mysql-utilities-1.6.5/mysql/connector/constants.py
mysql-utilities-1.6.5/mysql/connector/django/
mysql-utilities-1.6.5/mysql/connector/django/introspection.py
mysql-utilities-1.6.5/mysql/connector/django/validation.py
mysql-utilities-1.6.5/mysql/connector/django/__init__.py
mysql-utilities-1.6.5/mysql/connector/django/schema.py
mysql-utilities-1.6.5/mysql/connector/django/client.py
mysql-utilities-1.6.5/mysql/connector/django/operations.py
mysql-utilities-1.6.5/mysql/connector/django/compiler.py
mysql-utilities-1.6.5/mysql/connector/django/features.py
mysql-utilities-1.6.5/mysql/connector/django/creation.py
mysql-utilities-1.6.5/mysql/connector/django/base.py
mysql-utilities-1.6.5/mysql/connector/fabric/
mysql-utilities-1.6.5/mysql/connector/fabric/__init__.py
mysql-utilities-1.6.5/mysql/connector/fabric/caching.py
mysql-utilities-1.6.5/mysql/connector/fabric/connection.py
mysql-utilities-1.6.5/mysql/connector/fabric/balancing.py
mysql-utilities-1.6.5/mysql/connector/dbapi.py
mysql-utilities-1.6.5/mysql/connector/pooling.py
mysql-utilities-1.6.5/mysql/connector/network.py
mysql-utilities-1.6.5/mysql/connector/conversion.py
mysql-utilities-1.6.5/mysql/connector/connection.py
mysql-utilities-1.6.5/mysql/connector/version.py
mysql-utilities-1.6.5/mysql/connector/connection_cext.py
mysql-utilities-1.6.5/mysql/connector/cursor.py
mysql-utilities-1.6.5/mysql/connector/optionfiles.py
mysql-utilities-1.6.5/mysql/connector/authentication.py
mysql-utilities-1.6.5/mysql/connector/catch23.py
mysql-utilities-1.6.5/mysql/connector/locales/
mysql-utilities-1.6.5/mysql/connector/locales/eng/
mysql-utilities-1.6.5/mysql/connector/locales/eng/__init__.py
mysql-utilities-1.6.5/mysql/connector/locales/eng/client_error.py
mysql-utilities-1.6.5/mysql/connector/locales/__init__.py
mysql-utilities-1.6.5/mysql/connector/protocol.py
mysql-utilities-1.6.5/mysql/connector/errorcode.py
mysql-utilities-1.6.5/unit_tests/
mysql-utilities-1.6.5/unit_tests/__init__.py
mysql-utilities-1.6.5/unit_tests/test_decode_encode.py
mysql-utilities-1.6.5/unit_tests/test_binary_log_file.py
mysql-utilities-1.6.5/unit_tests/test_utilities_console.py
mysql-utilities-1.6.5/unit_tests/test_server_version.py
mysql-utilities-1.6.5/unit_tests/test_parsers.py
mysql-utilities-1.6.5/unit_tests/test_options.py
mysql-utilities-1.6.5/unit_tests/test_ip_parser.py
mysql-utilities-1.6.5/unit_tests/test_user_privileges.py
mysql-utilities-1.6.5/unit_tests/test_missing_connector.py
mysql-utilities-1.6.5/unit_tests/test_my_print_defaults.py
mysql-utilities-1.6.5/unit_tests/test_gtid.py
mysql-utilities-1.6.5/docs/
mysql-utilities-1.6.5/docs/man/
mysql-utilities-1.6.5/docs/man/mysqlrplcheck.1
mysql-utilities-1.6.5/docs/man/mysqlmetagrep.1
mysql-utilities-1.6.5/docs/man/mysqlfailover.1
mysql-utilities-1.6.5/docs/man/mysqlserverclone.1
mysql-utilities-1.6.5/docs/man/mysqlbinlogmove.1
mysql-utilities-1.6.5/docs/man/mysqlrplms.1
mysql-utilities-1.6.5/docs/man/mysqldbimport.1
mysql-utilities-1.6.5/docs/man/mysqldiskusage.1
mysql-utilities-1.6.5/docs/man/mysqlreplicate.1
mysql-utilities-1.6.5/docs/man/mysqldbexport.1
mysql-utilities-1.6.5/docs/man/mysqlfrm.1
mysql-utilities-1.6.5/docs/man/mysqlauditgrep.1
mysql-utilities-1.6.5/docs/man/mysqluc.1
mysql-utilities-1.6.5/docs/man/mysqldiff.1
mysql-utilities-1.6.5/docs/man/mysqlslavetrx.1
mysql-utilities-1.6.5/docs/man/mysqlbinlogpurge.1
mysql-utilities-1.6.5/docs/man/mysqlgrants.1
mysql-utilities-1.6.5/docs/man/mysqldbcopy.1
mysql-utilities-1.6.5/docs/man/mysqlrpladmin.1
mysql-utilities-1.6.5/docs/man/mysqlprocgrep.1
mysql-utilities-1.6.5/docs/man/mysqlauditadmin.1
mysql-utilities-1.6.5/docs/man/mysqlindexcheck.1
mysql-utilities-1.6.5/docs/man/mysqldbcompare.1
mysql-utilities-1.6.5/docs/man/mysqlrplsync.1
mysql-utilities-1.6.5/docs/man/mysqlserverinfo.1
mysql-utilities-1.6.5/docs/man/mysqlbinlogrotate.1
mysql-utilities-1.6.5/docs/man/mysqlrplshow.1
mysql-utilities-1.6.5/docs/man/mysqluserclone.1
mysql-utilities-1.6.5/CHANGES.txt
mysql-utilities-1.6.5/scripts/
mysql-utilities-1.6.5/scripts/mysqlrplms.py
mysql-utilities-1.6.5/scripts/mysqldbcompare.py
mysql-utilities-1.6.5/scripts/mysqlserverclone.py
mysql-utilities-1.6.5/scripts/mysqluc.py
mysql-utilities-1.6.5/scripts/mysqldiskusage.py
mysql-utilities-1.6.5/scripts/mysqlauditadmin.py
mysql-utilities-1.6.5/scripts/mysqluserclone.py
mysql-utilities-1.6.5/scripts/mysqlbinlogrotate.py
mysql-utilities-1.6.5/scripts/mysqlreplicate.py
mysql-utilities-1.6.5/scripts/mysqlfailover.py
mysql-utilities-1.6.5/scripts/mysqlbinlogmove.py
mysql-utilities-1.6.5/scripts/mysqlindexcheck.py
mysql-utilities-1.6.5/scripts/mysqldbexport.py
mysql-utilities-1.6.5/scripts/mysqlrplcheck.py
mysql-utilities-1.6.5/scripts/mysqlslavetrx.py
mysql-utilities-1.6.5/scripts/mysqlmetagrep.py
mysql-utilities-1.6.5/scripts/mysqlprocgrep.py
mysql-utilities-1.6.5/scripts/mysqlrplsync.py
mysql-utilities-1.6.5/scripts/mysqlbinlogpurge.py
mysql-utilities-1.6.5/scripts/mysqldiff.py
mysql-utilities-1.6.5/scripts/mysqlrplshow.py
mysql-utilities-1.6.5/scripts/mysqlfrm.py
mysql-utilities-1.6.5/scripts/mysqldbimport.py
mysql-utilities-1.6.5/scripts/mysqlauditgrep.py
mysql-utilities-1.6.5/scripts/mysqlgrants.py
mysql-utilities-1.6.5/scripts/mysqldbcopy.py
mysql-utilities-1.6.5/scripts/mysqlserverinfo.py
mysql-utilities-1.6.5/scripts/mysqlrpladmin.py
mysql-utilities-1.6.5/info.py
mysql-utilities-1.6.5/setup.py
mysql-utilities-1.6.5/LICENSE.txt
[root@localhost soft]# cd mysql-utilities-1.6.5/
[root@localhost mysql-utilities-1.6.5]# ll
total 132
-rw-r--r--. 1 7161 31415 37814 Jan 18  2017 CHANGES.txt
drwxr-xr-x. 3 7161 31415    16 Jan 18  2017 docs
-rw-r--r--. 1 7161 31415  6680 Jan 18  2017 info.py
-rw-r--r--. 1 7161 31415 17987 Jan 18  2017 LICENSE.txt
drwxr-xr-x. 4 7161 31415    56 Jan 18  2017 mysql
-rw-r--r--. 1 7161 31415   928 Jan 18  2017 PKG-INFO
-rw-r--r--. 1 7161 31415 34819 Jan 18  2017 README.txt
drwxr-xr-x. 2 7161 31415  4096 Jan 18  2017 scripts
-rw-r--r--. 1 7161 31415 14232 Jan 18  2017 setup.py
drwxr-xr-x. 2 7161 31415  4096 Jan 18  2017 unit_tests
[root@localhost mysql-utilities-1.6.5]# python ./setup.py build
checking mysql for packages to distribute
packages found: ['mysql', 'mysql.connector', 'mysql.connector.django', 'mysql.connector.fabric', 'mysql.connector.locales', 'mysql.connector.locales.eng', 'mysql.utilities', 'mysql.utilities.command', 'mysql.utilities.common']
scripts found: ['scripts/mysqlrplms.py', 'scripts/mysqldbcompare.py', 'scripts/mysqlserverclone.py', 'scripts/mysqluc.py', 'scripts/mysqldiskusage.py', 'scripts/mysqlauditadmin.py', 'scripts/mysqluserclone.py', 'scripts/mysqlbinlogrotate.py', 'scripts/mysqlreplicate.py', 'scripts/mysqlfailover.py', 'scripts/mysqlbinlogmove.py', 'scripts/mysqlindexcheck.py', 'scripts/mysqldbexport.py', 'scripts/mysqlrplcheck.py', 'scripts/mysqlslavetrx.py', 'scripts/mysqlmetagrep.py', 'scripts/mysqlprocgrep.py', 'scripts/mysqlrplsync.py', 'scripts/mysqlbinlogpurge.py', 'scripts/mysqldiff.py', 'scripts/mysqlrplshow.py', 'scripts/mysqlfrm.py', 'scripts/mysqldbimport.py', 'scripts/mysqlauditgrep.py', 'scripts/mysqlgrants.py', 'scripts/mysqldbcopy.py', 'scripts/mysqlserverinfo.py', 'scripts/mysqlrpladmin.py']
package set set(['mysql.utilities.command', 'mysql.utilities.common', 'mysql.connector.fabric', 'mysql.connector.locales', 'mysql.connector', 'mysql.utilities', 'mysql.connector.django', 'mysql', 'mysql.connector.locales.eng'])
running build
running build_py
creating build
creating build/lib
creating build/lib/mysql
creating build/lib/mysql/utilities
creating build/lib/mysql/utilities/command
copying mysql/utilities/command/dbexport.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/rpl_admin.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/__init__.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/userclone.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/binlog_admin.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/serverclone.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/grep.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/indexcheck.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/grants.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/check_rpl.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/setup_rpl.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/dbimport.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/failover_console.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/dbcopy.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/proc.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/rpl_sync_check.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/serverinfo.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/failover_daemon.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/dbcompare.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/read_frm.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/diskusage.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/utilitiesconsole.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/audit_log.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/diff.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/show_rpl.py -> build/lib/mysql/utilities/command
creating build/lib/mysql/utilities/common
copying mysql/utilities/common/pattern_matching.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/server.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/user.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/variables.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/ip_parser.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/replication_ms.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/audit_log_parser.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/__init__.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/lock.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/tools.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/topology.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/table.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/charsets.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/parser.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/messages.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/gtid.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/audit_log_reader.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/database.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/grants_info.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/options.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/my_print_defaults.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/utilities.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/options_parser.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/binary_log_file.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/daemon.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/binlog.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/frm_reader.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/sql_transform.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/dbcompare.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/console.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/topology_map.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/format.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/rpl_sync.py -> build/lib/mysql/utilities/common
copying mysql/utilities/common/replication.py -> build/lib/mysql/utilities/common
creating build/lib/mysql/connector
creating build/lib/mysql/connector/fabric
copying mysql/connector/fabric/__init__.py -> build/lib/mysql/connector/fabric
copying mysql/connector/fabric/caching.py -> build/lib/mysql/connector/fabric
copying mysql/connector/fabric/connection.py -> build/lib/mysql/connector/fabric
copying mysql/connector/fabric/balancing.py -> build/lib/mysql/connector/fabric
creating build/lib/mysql/connector/locales
copying mysql/connector/locales/__init__.py -> build/lib/mysql/connector/locales
copying mysql/connector/custom_types.py -> build/lib/mysql/connector
copying mysql/connector/__init__.py -> build/lib/mysql/connector
copying mysql/connector/charsets.py -> build/lib/mysql/connector
copying mysql/connector/errors.py -> build/lib/mysql/connector
copying mysql/connector/cursor_cext.py -> build/lib/mysql/connector
copying mysql/connector/abstracts.py -> build/lib/mysql/connector
copying mysql/connector/utils.py -> build/lib/mysql/connector
copying mysql/connector/constants.py -> build/lib/mysql/connector
copying mysql/connector/dbapi.py -> build/lib/mysql/connector
copying mysql/connector/pooling.py -> build/lib/mysql/connector
copying mysql/connector/network.py -> build/lib/mysql/connector
copying mysql/connector/conversion.py -> build/lib/mysql/connector
copying mysql/connector/connection.py -> build/lib/mysql/connector
copying mysql/connector/version.py -> build/lib/mysql/connector
copying mysql/connector/connection_cext.py -> build/lib/mysql/connector
copying mysql/connector/cursor.py -> build/lib/mysql/connector
copying mysql/connector/optionfiles.py -> build/lib/mysql/connector
copying mysql/connector/authentication.py -> build/lib/mysql/connector
copying mysql/connector/catch23.py -> build/lib/mysql/connector
copying mysql/connector/protocol.py -> build/lib/mysql/connector
copying mysql/connector/errorcode.py -> build/lib/mysql/connector
copying mysql/utilities/__init__.py -> build/lib/mysql/utilities
copying mysql/utilities/exception.py -> build/lib/mysql/utilities
creating build/lib/mysql/connector/django
copying mysql/connector/django/introspection.py -> build/lib/mysql/connector/django
copying mysql/connector/django/validation.py -> build/lib/mysql/connector/django
copying mysql/connector/django/__init__.py -> build/lib/mysql/connector/django
copying mysql/connector/django/schema.py -> build/lib/mysql/connector/django
copying mysql/connector/django/client.py -> build/lib/mysql/connector/django
copying mysql/connector/django/operations.py -> build/lib/mysql/connector/django
copying mysql/connector/django/compiler.py -> build/lib/mysql/connector/django
copying mysql/connector/django/features.py -> build/lib/mysql/connector/django
copying mysql/connector/django/creation.py -> build/lib/mysql/connector/django
copying mysql/connector/django/base.py -> build/lib/mysql/connector/django
copying mysql/__init__.py -> build/lib/mysql
creating build/lib/mysql/connector/locales/eng
copying mysql/connector/locales/eng/__init__.py -> build/lib/mysql/connector/locales/eng
copying mysql/connector/locales/eng/client_error.py -> build/lib/mysql/connector/locales/eng
running build_scripts
copying scripts/mysqldbimport.py -> scripts/mysqldbimport
copying scripts/mysqlbinlogpurge.py -> scripts/mysqlbinlogpurge
copying scripts/mysqlfrm.py -> scripts/mysqlfrm
copying scripts/mysqlserverclone.py -> scripts/mysqlserverclone
copying scripts/mysqlindexcheck.py -> scripts/mysqlindexcheck
copying scripts/mysqldiff.py -> scripts/mysqldiff
copying scripts/mysqlbinlogrotate.py -> scripts/mysqlbinlogrotate
copying scripts/mysqlrpladmin.py -> scripts/mysqlrpladmin
copying scripts/mysqlgrants.py -> scripts/mysqlgrants
copying scripts/mysqlrplcheck.py -> scripts/mysqlrplcheck
copying scripts/mysqlprocgrep.py -> scripts/mysqlprocgrep
copying scripts/mysqlauditgrep.py -> scripts/mysqlauditgrep
copying scripts/mysqlrplms.py -> scripts/mysqlrplms
copying scripts/mysqlslavetrx.py -> scripts/mysqlslavetrx
copying scripts/mysqlreplicate.py -> scripts/mysqlreplicate
copying scripts/mysqldbexport.py -> scripts/mysqldbexport
copying scripts/mysqlrplshow.py -> scripts/mysqlrplshow
copying scripts/mysqluc.py -> scripts/mysqluc
copying scripts/mysqlserverinfo.py -> scripts/mysqlserverinfo
copying scripts/mysqlauditadmin.py -> scripts/mysqlauditadmin
copying scripts/mysqlrplsync.py -> scripts/mysqlrplsync
copying scripts/mysqldbcompare.py -> scripts/mysqldbcompare
copying scripts/mysqldiskusage.py -> scripts/mysqldiskusage
copying scripts/mysqlfailover.py -> scripts/mysqlfailover
copying scripts/mysqluserclone.py -> scripts/mysqluserclone
copying scripts/mysqlbinlogmove.py -> scripts/mysqlbinlogmove
copying scripts/mysqlmetagrep.py -> scripts/mysqlmetagrep
copying scripts/mysqldbcopy.py -> scripts/mysqldbcopy
creating build/scripts-2.7
copying and adjusting scripts/mysqldbimport -> build/scripts-2.7
copying and adjusting scripts/mysqlbinlogpurge -> build/scripts-2.7
copying and adjusting scripts/mysqlfrm -> build/scripts-2.7
copying and adjusting scripts/mysqlserverclone -> build/scripts-2.7
copying and adjusting scripts/mysqlindexcheck -> build/scripts-2.7
copying and adjusting scripts/mysqldiff -> build/scripts-2.7
copying and adjusting scripts/mysqlbinlogrotate -> build/scripts-2.7
copying and adjusting scripts/mysqlrpladmin -> build/scripts-2.7
copying and adjusting scripts/mysqlgrants -> build/scripts-2.7
copying and adjusting scripts/mysqlrplcheck -> build/scripts-2.7
copying and adjusting scripts/mysqlprocgrep -> build/scripts-2.7
copying and adjusting scripts/mysqlauditgrep -> build/scripts-2.7
copying and adjusting scripts/mysqlrplms -> build/scripts-2.7
copying and adjusting scripts/mysqlslavetrx -> build/scripts-2.7
copying and adjusting scripts/mysqlreplicate -> build/scripts-2.7
copying and adjusting scripts/mysqldbexport -> build/scripts-2.7
copying and adjusting scripts/mysqlrplshow -> build/scripts-2.7
copying and adjusting scripts/mysqluc -> build/scripts-2.7
copying and adjusting scripts/mysqlserverinfo -> build/scripts-2.7
copying and adjusting scripts/mysqlauditadmin -> build/scripts-2.7
copying and adjusting scripts/mysqlrplsync -> build/scripts-2.7
copying and adjusting scripts/mysqldbcompare -> build/scripts-2.7
copying and adjusting scripts/mysqldiskusage -> build/scripts-2.7
copying and adjusting scripts/mysqlfailover -> build/scripts-2.7
copying and adjusting scripts/mysqluserclone -> build/scripts-2.7
copying and adjusting scripts/mysqlbinlogmove -> build/scripts-2.7
copying and adjusting scripts/mysqlmetagrep -> build/scripts-2.7
copying and adjusting scripts/mysqldbcopy -> build/scripts-2.7
changing mode of build/scripts-2.7/mysqldbimport from 644 to 755
changing mode of build/scripts-2.7/mysqlbinlogpurge from 644 to 755
changing mode of build/scripts-2.7/mysqlfrm from 644 to 755
changing mode of build/scripts-2.7/mysqlserverclone from 644 to 755
changing mode of build/scripts-2.7/mysqlindexcheck from 644 to 755
changing mode of build/scripts-2.7/mysqldiff from 644 to 755
changing mode of build/scripts-2.7/mysqlbinlogrotate from 644 to 755
changing mode of build/scripts-2.7/mysqlrpladmin from 644 to 755
changing mode of build/scripts-2.7/mysqlgrants from 644 to 755
changing mode of build/scripts-2.7/mysqlrplcheck from 644 to 755
changing mode of build/scripts-2.7/mysqlprocgrep from 644 to 755
changing mode of build/scripts-2.7/mysqlauditgrep from 644 to 755
changing mode of build/scripts-2.7/mysqlrplms from 644 to 755
changing mode of build/scripts-2.7/mysqlslavetrx from 644 to 755
changing mode of build/scripts-2.7/mysqlreplicate from 644 to 755
changing mode of build/scripts-2.7/mysqldbexport from 644 to 755
changing mode of build/scripts-2.7/mysqlrplshow from 644 to 755
changing mode of build/scripts-2.7/mysqluc from 644 to 755
changing mode of build/scripts-2.7/mysqlserverinfo from 644 to 755
changing mode of build/scripts-2.7/mysqlauditadmin from 644 to 755
changing mode of build/scripts-2.7/mysqlrplsync from 644 to 755
changing mode of build/scripts-2.7/mysqldbcompare from 644 to 755
changing mode of build/scripts-2.7/mysqldiskusage from 644 to 755
changing mode of build/scripts-2.7/mysqlfailover from 644 to 755
changing mode of build/scripts-2.7/mysqluserclone from 644 to 755
changing mode of build/scripts-2.7/mysqlbinlogmove from 644 to 755
changing mode of build/scripts-2.7/mysqlmetagrep from 644 to 755
changing mode of build/scripts-2.7/mysqldbcopy from 644 to 755
[root@localhost mysql-utilities-1.6.5]# python ./setup.py install
checking mysql for packages to distribute
packages found: ['mysql', 'mysql.connector', 'mysql.connector.django', 'mysql.connector.fabric', 'mysql.connector.locales', 'mysql.connector.locales.eng', 'mysql.utilities', 'mysql.utilities.command', 'mysql.utilities.common']
scripts found: ['scripts/mysqlrplms.py', 'scripts/mysqldbcompare.py', 'scripts/mysqlserverclone.py', 'scripts/mysqluc.py', 'scripts/mysqldiskusage.py', 'scripts/mysqlauditadmin.py', 'scripts/mysqluserclone.py', 'scripts/mysqlbinlogrotate.py', 'scripts/mysqlreplicate.py', 'scripts/mysqlfailover.py', 'scripts/mysqlbinlogmove.py', 'scripts/mysqlindexcheck.py', 'scripts/mysqldbexport.py', 'scripts/mysqlrplcheck.py', 'scripts/mysqlslavetrx.py', 'scripts/mysqlmetagrep.py', 'scripts/mysqlprocgrep.py', 'scripts/mysqlrplsync.py', 'scripts/mysqlbinlogpurge.py', 'scripts/mysqldiff.py', 'scripts/mysqlrplshow.py', 'scripts/mysqlfrm.py', 'scripts/mysqldbimport.py', 'scripts/mysqlauditgrep.py', 'scripts/mysqlgrants.py', 'scripts/mysqldbcopy.py', 'scripts/mysqlserverinfo.py', 'scripts/mysqlrpladmin.py']
package set set(['mysql.utilities.command', 'mysql.utilities.common', 'mysql.connector.fabric', 'mysql.connector.locales', 'mysql.connector', 'mysql.utilities', 'mysql.connector.django', 'mysql', 'mysql.connector.locales.eng'])
running install
running build
running build_py
running build_scripts
running install_lib
creating /usr/lib/python2.7/site-packages/mysql
creating /usr/lib/python2.7/site-packages/mysql/utilities
creating /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/dbexport.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/rpl_admin.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/__init__.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/userclone.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/binlog_admin.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/serverclone.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/grep.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/indexcheck.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/grants.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/check_rpl.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/setup_rpl.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/dbimport.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/failover_console.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/dbcopy.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/proc.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/rpl_sync_check.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/serverinfo.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/failover_daemon.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/dbcompare.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/read_frm.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/diskusage.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/utilitiesconsole.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/audit_log.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/diff.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
copying build/lib/mysql/utilities/command/show_rpl.py -> /usr/lib/python2.7/site-packages/mysql/utilities/command
creating /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/pattern_matching.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/server.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/user.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/variables.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/ip_parser.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/replication_ms.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/audit_log_parser.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/__init__.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/lock.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/tools.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/topology.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/table.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/charsets.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/parser.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/messages.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/gtid.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/audit_log_reader.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/database.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/grants_info.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/options.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/my_print_defaults.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/utilities.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/options_parser.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/binary_log_file.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/daemon.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/binlog.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/frm_reader.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/sql_transform.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/dbcompare.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/console.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/topology_map.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/format.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/rpl_sync.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/replication.py -> /usr/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/__init__.py -> /usr/lib/python2.7/site-packages/mysql/utilities
copying build/lib/mysql/utilities/exception.py -> /usr/lib/python2.7/site-packages/mysql/utilities
creating /usr/lib/python2.7/site-packages/mysql/connector
creating /usr/lib/python2.7/site-packages/mysql/connector/fabric
copying build/lib/mysql/connector/fabric/__init__.py -> /usr/lib/python2.7/site-packages/mysql/connector/fabric
copying build/lib/mysql/connector/fabric/caching.py -> /usr/lib/python2.7/site-packages/mysql/connector/fabric
copying build/lib/mysql/connector/fabric/connection.py -> /usr/lib/python2.7/site-packages/mysql/connector/fabric
copying build/lib/mysql/connector/fabric/balancing.py -> /usr/lib/python2.7/site-packages/mysql/connector/fabric
creating /usr/lib/python2.7/site-packages/mysql/connector/locales
copying build/lib/mysql/connector/locales/__init__.py -> /usr/lib/python2.7/site-packages/mysql/connector/locales
creating /usr/lib/python2.7/site-packages/mysql/connector/locales/eng
copying build/lib/mysql/connector/locales/eng/__init__.py -> /usr/lib/python2.7/site-packages/mysql/connector/locales/eng
copying build/lib/mysql/connector/locales/eng/client_error.py -> /usr/lib/python2.7/site-packages/mysql/connector/locales/eng
copying build/lib/mysql/connector/custom_types.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/__init__.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/charsets.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/errors.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/cursor_cext.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/abstracts.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/utils.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/constants.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/dbapi.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/pooling.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/network.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/conversion.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/connection.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/version.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/connection_cext.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/cursor.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/optionfiles.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/authentication.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/catch23.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/protocol.py -> /usr/lib/python2.7/site-packages/mysql/connector
copying build/lib/mysql/connector/errorcode.py -> /usr/lib/python2.7/site-packages/mysql/connector
creating /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/connector/django/introspection.py -> /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/connector/django/validation.py -> /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/connector/django/__init__.py -> /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/connector/django/schema.py -> /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/connector/django/client.py -> /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/connector/django/operations.py -> /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/connector/django/compiler.py -> /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/connector/django/features.py -> /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/connector/django/creation.py -> /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/connector/django/base.py -> /usr/lib/python2.7/site-packages/mysql/connector/django
copying build/lib/mysql/__init__.py -> /usr/lib/python2.7/site-packages/mysql
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/dbexport.py to dbexport.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/rpl_admin.py to rpl_admin.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/userclone.py to userclone.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/binlog_admin.py to binlog_admin.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/serverclone.py to serverclone.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/grep.py to grep.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/indexcheck.py to indexcheck.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/grants.py to grants.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/check_rpl.py to check_rpl.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/setup_rpl.py to setup_rpl.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/dbimport.py to dbimport.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/failover_console.py to failover_console.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/dbcopy.py to dbcopy.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/proc.py to proc.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/rpl_sync_check.py to rpl_sync_check.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/serverinfo.py to serverinfo.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/failover_daemon.py to failover_daemon.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/dbcompare.py to dbcompare.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/read_frm.py to read_frm.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/diskusage.py to diskusage.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/utilitiesconsole.py to utilitiesconsole.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/audit_log.py to audit_log.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/diff.py to diff.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/command/show_rpl.py to show_rpl.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/pattern_matching.py to pattern_matching.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/server.py to server.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/user.py to user.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/variables.py to variables.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/ip_parser.py to ip_parser.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/replication_ms.py to replication_ms.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/audit_log_parser.py to audit_log_parser.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/lock.py to lock.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/tools.py to tools.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/topology.py to topology.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/table.py to table.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/charsets.py to charsets.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/parser.py to parser.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/messages.py to messages.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/gtid.py to gtid.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/audit_log_reader.py to audit_log_reader.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/database.py to database.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/grants_info.py to grants_info.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/options.py to options.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/my_print_defaults.py to my_print_defaults.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/utilities.py to utilities.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/options_parser.py to options_parser.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/binary_log_file.py to binary_log_file.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/daemon.py to daemon.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/binlog.py to binlog.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/frm_reader.py to frm_reader.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/sql_transform.py to sql_transform.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/dbcompare.py to dbcompare.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/console.py to console.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/topology_map.py to topology_map.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/format.py to format.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/rpl_sync.py to rpl_sync.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/common/replication.py to replication.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/utilities/exception.py to exception.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/fabric/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/fabric/caching.py to caching.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/fabric/connection.py to connection.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/fabric/balancing.py to balancing.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/locales/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/locales/eng/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/locales/eng/client_error.py to client_error.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/custom_types.py to custom_types.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/charsets.py to charsets.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/errors.py to errors.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/cursor_cext.py to cursor_cext.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/abstracts.py to abstracts.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/utils.py to utils.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/constants.py to constants.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/dbapi.py to dbapi.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/pooling.py to pooling.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/network.py to network.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/conversion.py to conversion.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/connection.py to connection.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/version.py to version.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/connection_cext.py to connection_cext.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/cursor.py to cursor.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/optionfiles.py to optionfiles.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/authentication.py to authentication.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/catch23.py to catch23.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/protocol.py to protocol.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/errorcode.py to errorcode.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/django/introspection.py to introspection.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/django/validation.py to validation.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/django/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/django/schema.py to schema.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/django/client.py to client.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/django/operations.py to operations.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/django/compiler.py to compiler.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/django/features.py to features.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/django/creation.py to creation.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/connector/django/base.py to base.pyc
byte-compiling /usr/lib/python2.7/site-packages/mysql/__init__.py to __init__.pyc
running install_scripts
copying build/scripts-2.7/mysqldbimport -> /usr/bin
copying build/scripts-2.7/mysqlbinlogpurge -> /usr/bin
copying build/scripts-2.7/mysqlfrm -> /usr/bin
copying build/scripts-2.7/mysqlserverclone -> /usr/bin
copying build/scripts-2.7/mysqlindexcheck -> /usr/bin
copying build/scripts-2.7/mysqldiff -> /usr/bin
copying build/scripts-2.7/mysqlbinlogrotate -> /usr/bin
copying build/scripts-2.7/mysqlrpladmin -> /usr/bin
copying build/scripts-2.7/mysqlgrants -> /usr/bin
copying build/scripts-2.7/mysqlrplcheck -> /usr/bin
copying build/scripts-2.7/mysqlprocgrep -> /usr/bin
copying build/scripts-2.7/mysqlauditgrep -> /usr/bin
copying build/scripts-2.7/mysqlrplms -> /usr/bin
copying build/scripts-2.7/mysqlslavetrx -> /usr/bin
copying build/scripts-2.7/mysqlreplicate -> /usr/bin
copying build/scripts-2.7/mysqldbexport -> /usr/bin
copying build/scripts-2.7/mysqlrplshow -> /usr/bin
copying build/scripts-2.7/mysqluc -> /usr/bin
copying build/scripts-2.7/mysqlserverinfo -> /usr/bin
copying build/scripts-2.7/mysqlauditadmin -> /usr/bin
copying build/scripts-2.7/mysqlrplsync -> /usr/bin
copying build/scripts-2.7/mysqldbcompare -> /usr/bin
copying build/scripts-2.7/mysqldiskusage -> /usr/bin
copying build/scripts-2.7/mysqlfailover -> /usr/bin
copying build/scripts-2.7/mysqluserclone -> /usr/bin
copying build/scripts-2.7/mysqlbinlogmove -> /usr/bin
copying build/scripts-2.7/mysqlmetagrep -> /usr/bin
copying build/scripts-2.7/mysqldbcopy -> /usr/bin
changing mode of /usr/bin/mysqldbimport to 755
changing mode of /usr/bin/mysqlbinlogpurge to 755
changing mode of /usr/bin/mysqlfrm to 755
changing mode of /usr/bin/mysqlserverclone to 755
changing mode of /usr/bin/mysqlindexcheck to 755
changing mode of /usr/bin/mysqldiff to 755
changing mode of /usr/bin/mysqlbinlogrotate to 755
changing mode of /usr/bin/mysqlrpladmin to 755
changing mode of /usr/bin/mysqlgrants to 755
changing mode of /usr/bin/mysqlrplcheck to 755
changing mode of /usr/bin/mysqlprocgrep to 755
changing mode of /usr/bin/mysqlauditgrep to 755
changing mode of /usr/bin/mysqlrplms to 755
changing mode of /usr/bin/mysqlslavetrx to 755
changing mode of /usr/bin/mysqlreplicate to 755
changing mode of /usr/bin/mysqldbexport to 755
changing mode of /usr/bin/mysqlrplshow to 755
changing mode of /usr/bin/mysqluc to 755
changing mode of /usr/bin/mysqlserverinfo to 755
changing mode of /usr/bin/mysqlauditadmin to 755
changing mode of /usr/bin/mysqlrplsync to 755
changing mode of /usr/bin/mysqldbcompare to 755
changing mode of /usr/bin/mysqldiskusage to 755
changing mode of /usr/bin/mysqlfailover to 755
changing mode of /usr/bin/mysqluserclone to 755
changing mode of /usr/bin/mysqlbinlogmove to 755
changing mode of /usr/bin/mysqlmetagrep to 755
changing mode of /usr/bin/mysqldbcopy to 755
running install_egg_info
Writing /usr/lib/python2.7/site-packages/mysql_utilities-1.6.5-py2.7.egg-info

安装完成后,在相应的python执行目录下,就能mysqlfrm等执行文件了。

[root@localhost mysql-utilities-1.6.5]# ll
total 140
drwxr-xr-x. 4 root root     34 Mar 19 15:34 build
-rw-r--r--. 1 7161 31415 37814 Jan 18  2017 CHANGES.txt
drwxr-xr-x. 3 7161 31415    16 Jan 18  2017 docs
-rw-r--r--. 1 7161 31415  6680 Jan 18  2017 info.py
-rw-r--r--. 1 root root   5579 Mar 19 15:34 info.pyc
-rw-r--r--. 1 7161 31415 17987 Jan 18  2017 LICENSE.txt
drwxr-xr-x. 4 7161 31415    75 Mar 19 15:34 mysql
-rw-r--r--. 1 7161 31415   928 Jan 18  2017 PKG-INFO
-rw-r--r--. 1 7161 31415 34819 Jan 18  2017 README.txt
drwxr-xr-x. 2 7161 31415  4096 Mar 19 15:34 scripts
-rw-r--r--. 1 7161 31415 14232 Jan 18  2017 setup.py
drwxr-xr-x. 2 7161 31415  4096 Jan 18  2017 unit_tests
[root@localhost mysql-utilities-1.6.5]# cd build
[root@localhost build]# ll
total 4
drwxr-xr-x. 3 root root   18 Mar 19 15:34 lib
drwxr-xr-x. 2 root root 4096 Mar 19 15:34 scripts-2.7
[root@localhost build]# cd scripts-2.7/
[root@localhost scripts-2.7]# ll
total 336
-rwxr-xr-x. 1 root root 11966 Mar 19 15:34 mysqlauditadmin
-rwxr-xr-x. 1 root root 12207 Mar 19 15:34 mysqlauditgrep
-rwxr-xr-x. 1 root root 15938 Mar 19 15:34 mysqlbinlogmove
-rwxr-xr-x. 1 root root  7993 Mar 19 15:34 mysqlbinlogpurge
-rwxr-xr-x. 1 root root  3761 Mar 19 15:34 mysqlbinlogrotate
-rwxr-xr-x. 1 root root 17957 Mar 19 15:34 mysqldbcompare
-rwxr-xr-x. 1 root root 16193 Mar 19 15:34 mysqldbcopy
-rwxr-xr-x. 1 root root 14790 Mar 19 15:34 mysqldbexport
-rwxr-xr-x. 1 root root 14160 Mar 19 15:34 mysqldbimport
-rwxr-xr-x. 1 root root 12368 Mar 19 15:34 mysqldiff
-rwxr-xr-x. 1 root root  7383 Mar 19 15:34 mysqldiskusage
-rwxr-xr-x. 1 root root 17246 Mar 19 15:34 mysqlfailover
-rwxr-xr-x. 1 root root 18219 Mar 19 15:34 mysqlfrm
-rwxr-xr-x. 1 root root 10954 Mar 19 15:34 mysqlgrants
-rwxr-xr-x. 1 root root  6459 Mar 19 15:34 mysqlindexcheck
-rwxr-xr-x. 1 root root  5355 Mar 19 15:34 mysqlmetagrep
-rwxr-xr-x. 1 root root  5972 Mar 19 15:34 mysqlprocgrep
-rwxr-xr-x. 1 root root  7692 Mar 19 15:34 mysqlreplicate
-rwxr-xr-x. 1 root root 16910 Mar 19 15:34 mysqlrpladmin
-rwxr-xr-x. 1 root root  6405 Mar 19 15:34 mysqlrplcheck
-rwxr-xr-x. 1 root root 15545 Mar 19 15:34 mysqlrplms
-rwxr-xr-x. 1 root root  6693 Mar 19 15:34 mysqlrplshow
-rwxr-xr-x. 1 root root 12063 Mar 19 15:34 mysqlrplsync
-rwxr-xr-x. 1 root root  8981 Mar 19 15:34 mysqlserverclone
-rwxr-xr-x. 1 root root  5959 Mar 19 15:34 mysqlserverinfo
-rwxr-xr-x. 1 root root  6412 Mar 19 15:34 mysqlslavetrx
-rwxr-xr-x. 1 root root  6956 Mar 19 15:34 mysqluc
-rwxr-xr-x. 1 root root  8046 Mar 19 15:34 mysqluserclone