在MySQL的主从复制环境中，我们可以通过在slave上执行show slave status来查看slave的一些状态信息，其中有一个比较重要的参数Seconds_Behind_Master。然而通常有人会忽略这个参数，下面我就给大家介绍一些这个参数的含义和计算过程。
 
在之前我一直误以为Seconds_Behind_Master是表示slave比master落后多少，如果这个值为0的表示主从已经处于一致了（在非同步模式下，现在官方最多也只在5.5中增加了半同步复制）。但是最近我终于认识到之前的错误理解。
 
首先我们需要明白的一点：Seconds_Behind_Master表示slave上SQL thread与IO thread之间的延迟，我们都知道在MySQL的复制环境中，slave先从master上将binlog拉取到本地（通过IO thread），然后通过SQL thread将binlog重放，而Seconds_Behind_Master表示本地relaylog中未被执行完的那部分的差值。手册上的定义：

In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

所以如果slave拉取到本地的relaylog（实际上就是binlog，只是在slave上习惯称呼relaylog而已）都执行完，此时通过show slave status看到的会是0，那么Seconds_Behind_Master的值为0是否表示主从已经处于一致了呢?
 
答案几乎是否定的！为什么几乎是否定的？因为绝大部分的情况下复制都是异步的，异步就意味着master上的binlog不是实时的发送到slave上，所以即使Seconds_Behind_Master的值为0依然不能肯定主从处于一致，这也是我之前强调非同步复制的原因（现在已经有公司在做同步复制了，比如网易自己实现了VSR，VirtualSynchronized Replication，由于同步复制性能较差，所以网易再实现同步复制的同时还打了group commit的补丁）。所以如果我们要以这个参数来估计主从延迟多久的话至少得在一个比较好的网络环境中，这样才能保证几乎master上的binlog都已经发送到slave上。
 
上面解释了Seconds_Behind_Master这个值的真正含义，那么它的值到底是怎么计算出来的呢？实际上在binlog中每个binlog events都会附上执行时的timestamp，所以在在确定Seconds_Behind_Master的值时MySQL是通过比较当前系统的时间戳与当前SQL thread正在执行的binlog event的上的时间戳做比较，这个差值就是Seconds_Behind_Master的值。也许你会有疑问那要是两台服务器之间的时钟不一致怎么办？确实会存在这种情况，那么此时这个值的可靠性就不大了，手册上对此也进行了说明：

This time difference computation works even if the master and slave do not have identical clock times, provided that the difference,

computed when the slave I/O thread starts, remains constant from then on. Any changes—including NTP updates—can lead to clock

skews that can make calculation of Seconds_Behind_Master less reliable

Seconds_Behind_Master的值除了是非负数之外还可能是NULL，它是由如下几种情况导致的：

[]SQL thread没运行[/][]IO thread没运行[/][]slave没有连接到master。[/]

 
接下来再简单介绍一下异步复制/半同步复制之间的区别。
 
         异步复制，master上的操作记录binlog的同时不关心binlog是否已经被slave接收。

         半同步复制，master上的操作记录binlog的同时会关心binlog是否被slave接受。但是由于它的处理逻辑问题可能丢一个事务，如下图所示：

这样的处理流程存在一个问题，当存储引擎提交（storage commit）后，此时如果master挂了那么会存在主从不一致，对于这个问题orczhou好像自己对源码进行了修改更改storage commit的顺序来达到一个增强的半同步复制。

一、引子
研发反应，有台从库和主库不同步。由于业务读操作是针对从库的，数据不同步必定会带来数据的不一致，业务获取的结果会受影响，所以这个问题必须尽快解决。

登上服务器，查看 MySQL 的从库状态，并没有任何报错信息。刷新从库状态，发现状态没有任何变化，Exec_Master_Log_Pos 卡住不动。

二、故障分析
为了安全起见，此文略去 MySQL 版本以及其他可能会带来安全问题的信息，接下来逐步分析问题。

首先查看从库状态

mysql> SHOW SLAVE STATUS \G

***********************[i][b][/i] 1. row [/b]*************************

               Slave_IO_State: Queueing master event to the relay log

                  Master_Host: masterIP

                  Master_User: replUser

                  Master_Port: masterPort

                Connect_Retry: 60

              Master_Log_File: binlog.000296

          Read_Master_Log_Pos: 364027786

               Relay_Log_File: relaylog.000002

                Relay_Log_Pos: 250

        Relay_Master_Log_File: binlog.000283

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

              Relay_Log_Space: 13803486573

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

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

1 row in set (0.00 sec)

此时的 Slave_IO_State 为 Queueing master event to the relay log，而不是正常状态下的 Waiting for master to send event。刷新多次，状态没有任何变化，Exec_Master_Log_Pos 不变，从而导致 Seconds_Behind_Master 一直不变。
 
接下来查看 PROCESSLIST 状态：

mysql> SHOW FULL PROCESSLIST;

+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+

| Id     | User        | Host             | db                 | Command | Time    | State           | Info                  |

+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+

|  51378 | system user |   | NULL | Connect | 1121183 | Waiting for master to send event | NULL   |

|  88917 | system user |   | NULL | Connect |  245327 | Reading event from the relay log | NULL    |

| 106029 | userA     | xxx.xxx.xxx.xxx:14057 | NULL               | Sleep   |   14504 |   | NULL          |

| 106109 | userA     | xxx.xxx.xxx.xxx:15077 | databaseA | Sleep   |      79 |     | NULL                  |

| 106110 | userA     | xxx.xxx.xxx.xxx:15081 | databaseA | Sleep   |   13000 |   | NULL                  |

| 106116 | userB    | xxx.xxx.xxx.xxx:15096 | databaseA | Sleep   |     357 |  | NULL                  |

| 106117 | userB    | xxx.xxx.xxx.xxx:15097 | NULL               | Sleep   |   12964 |     | NULL         |

| 106119 | root        | localhost    | NULL   | Query   |    0 | NULL     | SHOW FULL PROCESSLIST |

| 106126 | userB    | xxx.xxx.xxx.xxx:15173 | NULL               | Sleep   |   12856 |      | NULL      |

| 106127 | userB    | xxx.xxx.xxx.xxx:15180 | databaseA | Sleep   |   12849 |    | NULL                  |

| 106766 | userA     | xxx.xxx.xxx.xxx:17960 | databaseA | Sleep   |      64 |       | NULL                |

+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+

11 rows in set (0.00 sec)

从以上的结果来看，没有任何异常。

既然从上述信息中得不到任何对排查问题有帮助的信息，那么我们可以试着分析 MySQL 的 binlog，看 Pos 为 594374863 的点发生了什么操作。

分析日志我们可以使用 mysqlbinlog 命令，指定 start-position 为夯住的那个点，并重定向到文件。

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v --start-position="594374863" \

binlog.000283 > /XXX/binlog.sql

查看输出结果，发现端倪了，以下是摘抄的部分结果：

/[i]!40019 SET @@session.max_insert_delayed_threads=0[/i]/;

/[i]!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0[/i]/;

DELIMITER /[i]![/i]/;

# at 4

#150814 17:43:15 server id 21  end_log_pos 107  Start: binlog v 4, server v x.x.xx-log created 150814 17:43:15

BINLOG '

M7jNVQ8VAAAAZwAAAGsAAAAAAAQANS41LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/[i]![/i]/;

# at 594374863

#150814 18:09:36 server id 21  end_log_pos 594374945    Query   thread_id=210702841 exec_time=43    error_code=0

SET TIMESTAMP=1439546976/[i]![/i]/;

SET @@session.pseudo_thread_id=210702841/[i]![/i]/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/[i]![/i]/;

SET @@session.sql_mode=0/[i]![/i]/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/[i]![/i]/;

/[i]!\C utf8 [/i]//[i]![/i]/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/[i]![/i]/;

SET @@session.lc_time_names=0/[i]![/i]/;

SET @@session.collation_database=DEFAULT/[i]![/i]/;

BEGIN

/[i]![/i]/;

# at 594374945

# at 594375036

# at 594376047

# at 594377085

# at 594378123

# at 594379152

# at 594380187

# at 594381165

# at 594382194

# at 594383223

# at 594384252

# at 594385269

# at 594386307

# at 594387282

# at 594388299

# at 594389265

# at 594390270

# at 594391299

# at 594392310

# at 594393327

# at 594394344

# at 594395340

# at 594396336

# at 594397332

从以上输出中，我们可以知道，从夯住的那个点开始，binlog 记录的信息就出现了异常，可以推测在主库有大操作。另外，针对出现问题库，查看主库和从库的表数量，发现从库的表数量多于主库，有几个临时表出现。可以推测的，主库有删表的操作，从库同步夯住，导致同步异常，主库删表的操作还没来得及同步到从库。

经过和研发沟通，确认了两点。第一，确实有大操作，程序有大量的批量插入，而且是用的 LOAD DATA LOCAL INFILE；第二，主库确实有删表的操作，这几张表都是临时表。
 
三、故障解决
既然问题找到了，那解决办法自然就有了。既然从库的表多于主库，而且这几张表是临时数据，我们可以过滤掉对这几张表的同步操作。具体思路如下：在主库备份临时表（虽然研发说数据不重要，但还是以防万一，DBA 还是谨慎为好），然后通知研发临时切走从库的流量，修改配置文件，指定 replicate-ignore-table 参数，重启 MySQL。

接下来就是具体的解决步骤，首先备份数据。备份时不加 –master-data 参数和 –single-transaction。究其原因，–master-data 禁用 –lock-tables 参数，在和 –single-transaction 一起使用时会禁用 –lock-all-tables。在备份开始时，会获取全局 read lock。 –single-transaction 参数设置默认级别为 REPEATABLE READ，并且在开始备份时执行 START TRANSACTION。在备份期间， 其他连接不能执行如下语句：ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE。MySQL 同步夯住，如果加了上述参数，mysqldump 也会夯住。mysqldump 会 FLUSH TABLES、LOCK TABLES，如果有 –master-data 参数，会导致 Waiting for table flush。同样，有 –single-transaction 参数，仍然会导致 Waiting for table flush。另外，还可以看到 Waiting for table metadata lock，此时做了 DROP TABLE 的操作。此时可以停掉 MySQL 同步来避免这个问题。

为了保险起见，我们在主库加大 expire_logs_days 参数，避免 binlog 丢失。

mysql> SHOW VARIABLES LIKE '%expire%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| expire_logs_days | 3     |

+------------------+-------+

1 row in set (0.00 sec)



mysql> SET GLOBAL expire_logs_days=5;

Query OK, 0 rows affected (0.00 sec)



mysql> SHOW VARIABLES LIKE '%expire%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| expire_logs_days | 5     |

+------------------+-------+

1 row in set (0.00 sec)

接着修改从库的配置文件：

vim /xxx/xxxx/xxx/my.cnf

在 mysqld 后，加入如下配置：

replicate-ignore-table=databaseA.tableA

replicate-ignore-table=databaseA.tableB

replicate-ignore-table=databaseA.tableC

replicate-ignore-table=databaseA.tableD

然后重启 MySQL：

/xxx/xxx/xxx/xxx/mysqld restart

登录 MySQL 从库，查看从库状态，并定时刷新状态，我们可以看到的是，Exec_Master_Log_Pos 在递增，Seconds_Behind_Master 在递减，证明问题已经解决了。

mysql> SHOW SLAVE STATUS \G

***********************[i][b][/i] 1. row [/b]*************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: masterIP

                  Master_User: replUser

                  Master_Port: masterPort

                Connect_Retry: 60

              Master_Log_File: binlog.000319

          Read_Master_Log_Pos: 985656691

               Relay_Log_File: relaylog.000004

                Relay_Log_Pos: 709043542

        Relay_Master_Log_File: binlog.000284

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table: databaseA.tableA,databaseA.tableB,databaseA.tableC,databaseA.tableD

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 709043399

              Relay_Log_Space: 38579192969

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

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

1 row in set (0.00 sec)

需要注意的是，待同步完成后，需要把从库配置文件中增加的 replicate-ignore-table参数注释，并重启 MySQL。
 
四、原理探讨
在主库运行 LOAD DATA LOCAL INFILE，主库和从库时这样同步的：
在主节点：

[]执行 LOAD DATA LOCAL INFILE；[/][]拷贝使用的整个文本文件内容到二进制日志；[/][]添加 LOAD DATA LOCAL INFILE 到最新的二进制日志。[/]

复制所有主库的二进制日志到从库的中继日志；
在从节点：

[]检查中继日志中的文本文件；[/][]从多个中继日志文件中读取所有的块；[/][]文本文件存放在 /tmp 文件夹中；[/][]从中继日志中读取 LOAD DATA LOCAL INFILE；[/][]在 SQL 线程中执行 LOAD DATA LOCAL INFILE。[/]

 
在从节点执行的 1-4 步骤中，IO 线程会呈现 Reading event from the relay log 状态，持续地为下一个 LOAD DATA LOCAL INFILE 命令提取 CSV 行。此时从库会持续落后，一旦从库落后时间较长，会导致 SQL 线程阻塞，呈现 Queueing master event to the relay log 状态，从而复制夯住。

 
五、小结
这样的故障，归根结底还是研发写的程序还有优化的余地。大批量的数据插入，这在 MySQL 中是不推荐使用的。我们可以这样：第一，一条 SQL 语句插入多条数据；第二，在事务中进行插入处理；第三，分批插入，在程序中设置 auto_commit 为 0，分批插入完成后，手动 COMMIT；第四，需要使用 LOAD DATA LOCAL INFILE 时，设置 sync_binlog 为 1。
 
分享原文：https://dbarobin.com/2015/08/22/mysql-replication-hanging/
作者：温国兵老师

删除清空表数据有两种方法分别是delete和truncate，语法如下：

delete from Table_Name;

truncate table Table_Name;

不带where参数的delete语句可以删除mysql表中所有内容，使用truncate table也可以清空mysql表中所有内容。
 
效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。
 
delete的效果有点像将mysql表中所有记录一条一条删除到删完，而truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。

如果有时候你的数据库被误删除了，可以通过binlog来恢复。
 Mysql开启binlog记录如下：

[mysqld]

log-bin=/data/mysqldir/binlog/mysql-binlog         //定义binlog路径及名称

用mysqlbinlog可以查看binlog日志里的信息
1、根据位置恢复

# mysqlbinlog --start-position=144 --stop-position=240 mysql-binglog.000002 > /data/sql/pos.sql

然后导入就好

mysql> source /data/sql/pos.sql

2、根据日期

# ./mysqlbinlog --start-date="2016-06-23 19:30:07" --stop-date="2016-06-24 17:21:28" mysql-binlog.000003  > /root/sql/time.sql

导入数据

mysql> source /root/sql/time.sql

OR

#./mysqlbinlog --start-date=

"2016-06-23 19:30:07" --stop-date="2016-06-24 17:21:28" mysql-binlog.000003 |mysql -u root -p'admin@login'

如果有多个binlog文件，中间用空格隔开，打上完全路径。

架构说明：最近部署了MySQL的集群环境，详细情况如上图所示MMA和MMB为主主复制，MMA和MSA为主从复制；在测试的过程中发现了以下问题：

[]MMA和MMB的主主复制没有问题（从MMA写入数据能同步到MMB，从MMB写入数据也能够同步到MMA);[/][]MMA和MSA主从复制也没有问题，当从MMA写入的时候，数据可以写入到MSA；[/][]但是当从MMB写入的时候，数据就不能写入到MSA；[/]

 
问题原因是：MMA Mysql参数log_slave_updates参数的状态为NO。
 
MySQL的官网说明如下：

Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. Prior to MySQL 5.5, the server would not start when using the --log-slave-updates option without also starting the server with the --log-bin option, and would fail with an error; in MySQL 5.5, only a warning is generated. (Bug #44663) --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:

A -> B -> C

Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.

[]MMA从MMB同步数据过来的时候，log_slave_updates参数用来控制MMA是否把所有的操作写入到binary log，默认的情况下mysql是关闭的;[/]

[]MSA数据的更新需要通过读取到MMA的binary log才能进行更新，当从MMB写入数据的时候MMA是没有写binary log的，所以MRA也就没有更新操作。[/]

 
问题的解决方法：

log_slave_updates：默认值为OFF;    Dynamic Variable：NO 
 
处理方法：修改配置文件my.cnf，在mysql数据库MMA的配置文件[mysqld]下增加一行log_slave_updates=1，然后暂停所有数据库的同步> stop slave;  最后重启MMA数据库让参数生效，打开所有数据库的同步> start slave;
 
总结：
设置完该参数后，数据库的架构MMA和MMB为主主同步，MSA通过MMA进行主从同步就完善了！
应用的写操作中MMB上面进行，读操作中MSA上面进行（如果读操作很多的话，可以在MMA上面架设多台只读数据库），当MMB发生故障后，系统的写操作自动迁移到MMA上面。这种架构基本可以保证大部分公司的应用需求。
 

Binlog介绍
1、binlog,即二进制日志,它记录了数据库上的所有改变.

2、改变数据库的SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕.

3、binlog格式

[]基于语句,无法保证所有语句都在从库执行成功,比如update ... limit 1;[/][]基于行,将每一次改动记为binlog中的一行.在执行一个特别复杂的update或者delete操作时,基于行的格式会有优势.[/]

 查看binlog数据内容方式一：登录到mysql查看binlog 1、只查看第一个binlog文件的内容

show binlog events;

2、查看指定binlog文件的内容

show binlog events in 'mysql-bin.000013';

3、查看当前正在写入的binlog文件

show master status\G

4、获取binlog文件列表

show binary logs;

方式二：用mysqlbinlog工具查看需要注意的事件：[list=1][]不要查看当前正在写入的binlog文件[/][]不要加--force参数强制访问[/][]如果binlog格式是行模式的,请加 -vv参数[/]
 
本地查看
1、基于开始/结束时间

mysqlbinlog --start-datetime='2016-06-19 00:00:00' --stop-datetime='2016-09-19 02:01:01' -d 库名 二进制文件

2、基于pos值

mysqlbinlog --start-postion=307 --stop-position=1000 -d 库名 二进制文件

远程查看
指定开始/结束时间,并把结果重定向到本地t.binlog文件中.

mysqlbinlog -u username -p password -hMysql_MasterA -P3306 \

--read-from-remote-server --start-datetime='2016-06-10 23:00:00' --stop-datetime='2016-06-10 23:30:00' mysql-bin.000003 > t.binlog

一、为什么要迁移​
MySQL 迁移是 DBA 日常维护中的一个工作。迁移，究其本义，无非是把实际存在的物体挪走，保证该物体的完整性以及延续性。就像柔软的沙滩上，两个天真无邪的小孩，把一堆沙子挪向其他地方，铸就内心神往的城堡。

生产环境中，有以下情况需要做迁移工作，如下：

[]磁盘空间不够。比如一些老项目，选用的机型并不一定适用于数据库。随着时间的推移，硬盘很有可能出现短缺；[/][]业务出现瓶颈。比如项目中采用单机承担所有的读写业务，业务压力增大，不堪重负。如果 IO 压力在可接受的范围，会采用读写分离方案；[/][]机器出现瓶颈。机器出现瓶颈主要在磁盘 IO 能力、内存、CPU，此时除了针对瓶颈做一些优化以外，选择迁移是不错的方案；[/][]项目改造。某些项目的数据库存在跨机房的情况，可能会在不同机房中增加节点，或者把机器从一个机房迁移到另一个机房。再比如，不同业务共用同一台服务器，为了缓解服务器压力以及方便维护，也会做迁移。[/]

一句话，迁移工作是不得已而为之。实施迁移工作，目的是让业务平稳持续地运行。  二、MySQL 迁移方案概览MySQL 迁移无非是围绕着数据做工作，再继续延伸，无非就是在保证业务平稳持续地运行的前提下做备份恢复。那问题就在怎么快速安全地进行备份恢复。 一方面，备份。针对每个主节点的从节点或者备节点，都有备份。这个备份可能是全备，可能是增量备份。在线备份的方法，可能是使用 mysqldump，可能是 xtrabackup，还可能是 mydumper。针对小容量（10GB 以下）数据库的备份，我们可以使用 mysqldump。但针对大容量数据库（数百GB 或者 TB 级别），我们不能使用 mysqldump 备份，一方面，会产生锁；另一方面，耗时太长。这种情况，可以选择 xtrabackup 或者直接拷贝数据目录。直接拷贝数据目录方法，不同机器传输可以使用 rsync，耗时跟网络相关。使用 xtrabackup，耗时主要在备份和网络传输。如果有全备或者指定库的备份文件，这是获取备份的最好方法。如果备库可以容许停止服务，直接拷贝数据目录是最快的方法。如果备库不允许停止服务，我们可以使用 xtrabackup（不会锁定 InnoDB 表），这是完成备份的最佳折中办法。 另一方面，恢复。针对小容量（10GB 以下）数据库的备份文件，我们可以直接导入。针对大容量数据库（数百GB 或者 TB 级别）的恢复，拿到备份文件到本机以后，恢复不算困难。 三、MySQL 迁移实战我们搞明白为什么要做迁移，以及迁移怎么做以后，接下来看看生产环境是怎样操作的。不同的应用场景，有不同的解决方案。 阅读具体的实战之前，假设和读者有如下约定：[list=1][]为了保护隐私，本文中的服务器 IP 等信息经过处理；[/][]如果服务器在同一机房，用服务器 IP 的 D 段代替服务器，具体的 IP 请参考架构图；[/][]如果服务器在不同机房，用服务器 IP 的 C 段 和 D 段代替服务器，具体的 IP 请参考架构图；[/][]每个场景给出方法，但不会详细地给出每一步执行什么命令，因为一方面，这会导致文章过长；另一方面，我认为只要知道方法，具体的做法就会迎面扑来的，只取决于掌握知识的程度和获取信息的能力；[/][]实战过程中的注意事项[/] 3.1 场景一: 一主一从结构迁移从库遵循从易到难的思路，我们从简单的结构入手。A 项目，原本是一主一从结构。101 是主节点，102 是从节点。因业务需要，把 102 从节点迁移至 103，架构图如图一。102 从节点的数据容量过大，不能使用 mysqldump 的形式备份。和研发沟通后，形成一致的方案。  图一  一主一从结构迁移从库架构图  具体做法是这样：[list=1][]研发将 102 的读业务切到主库；[/][]确认 102 MySQL 状态（主要看 PROCESS LIST），观察机器流量，确认无误后，停止 102 从节点的服务；[/][]103 新建 MySQL 实例，建成以后，停止 MySQL 服务，并且将整个数据目录 mv 到其他地方做备份；[/][]将 102 的整个 mysql 数据目录使用 rsync 拷贝到 103；[/][]拷贝的同时，在 101 授权，使 103 有拉取 binlog 的权限（REPLICATION SLAVE, REPLICATION CLIENT）；[/][]待拷贝完成，修改 103 配置文件中的 server_id，注意不要和 102 上的一致；[/][]在 103 启动 MySQL 实例，注意配置文件中的数据文件路径以及数据目录的权限；[/][]进入 103 MySQL 实例，使用 SHOW SLAVE STATUS 检查从库状态，可以看到 Seconds_Behind_Master 在递减；[/][]Seconds_Behind_Master 变为 0 后，表示同步完成，此时可以用 pt-table-checksum 检查 101 和 103 的数据一致，但比较耗时，而且对主节点有影响，可以和开发一起进行数据一致性的验证；[/][]和研发沟通，除了做数据一致性验证外，还需要验证账号权限，以防业务迁回后访问出错；[/][]做完上述步骤，可以和研发协调，把 101 的部分读业务切到 103，观察业务状态；[/][]如果业务没有问题，证明迁移成功。[/] 3.2 场景二：一主一从结构迁移指定库 我们知道一主一从只迁移从库怎么做之后，接下来看看怎样同时迁移主从节点。因不同业务同时访问同一服务器，导致单个库压力过大，还不便管理。于是，打算将主节点 101 和从节点 102 同时迁移至新的机器 103 和 104，103 充当主节点，104 充当从节点，架构图如图二。此次迁移只需要迁移指定库，这些库容量不是太大，并且可以保证数据不是实时的。 图二  一主一从结构迁移指定库架构图  具体的做法如下：[list=1][]103 和 104 新建实例，搭建主从关系，此时的主节点和从节点处于空载；[/][]102 导出数据，正确的做法是配置定时任务，在业务低峰做导出操作，此处选择的是 mysqldump；[/][]102 收集指定库需要的账号以及权限；[/][]102 导出数据完毕，使用 rsync 传输到 103，必要时做压缩操作；[/][]103 导入数据，此时数据会自动同步到 104，监控服务器状态以及 MySQL 状态；[/][]103 导入完成，104 同步完成，103 根据 102 收集的账号授权，完成后，通知研发检查数据以及账户权限；[/][]上述完成后，可研发协作，将 101 和 102 的业务迁移到 103 和 104，观察业务状态；[/][]如果业务没有问题，证明迁移成功。[/] 3.3 场景三： 一主一从结构双边迁移指定库 接下来看看一主一从结构双边迁移指定库怎么做。同样是因为业务共用，导致服务器压力大，管理混乱。于是，打算将主节点 101 和从节点 102 同时迁移至新的机器 103、104、105、106，103 充当 104 的主节点，104 充当 103 的从节点，105 充当 106 的主节点，106 充当 105 的从节点，架构图如图三。此次迁移只需要迁移指定库，这些库容量不是太大，并且可以保证数据不是实时的。我们可以看到，此次迁移和场景二很类似，无非做了两次迁移。  图三  一主一从结构双边迁移指定库架构图  具体的做法如下：[list=1][]103 和 104 新建实例，搭建主从关系，此时的主节点和从节点处于空载；[/][]102 导出 103 需要的指定库数据，正确的做法是配置定时任务，在业务低峰做导出操作，此处选择的是 mysqldump；[/][]102 收集 103 需要的指定库需要的账号以及权限；[/][]102 导出103 需要的指定库数据完毕，使用 rsync 传输到 103，必要时做压缩操作；[/][]103 导入数据，此时数据会自动同步到 104，监控服务器状态以及 MySQL 状态；[/][]103 导入完成，104 同步完成，103 根据 102 收集的账号授权，完成后，通知研发检查数据以及账户权限；[/][]上述完成后，和研发协作，将 101 和 102 的业务迁移到 103 和 104，观察业务状态；[/][]105 和 106 新建实例，搭建主从关系，此时的主节点和从节点处于空载；[/][]102 导出 105 需要的指定库数据，正确的做法是配置定时任务，在业务低峰做导出操作，此处选择的是 mysqldump；[/][]102 收集 105 需要的指定库需要的账号以及权限；[/][]102 导出 105 需要的指定库数据完毕，使用 rsync 传输到 105，必要时做压缩操作；[/][]105 导入数据，此时数据会自动同步到 106，监控服务器状态以及 MySQL 状态；[/][]105 导入完成，106 同步完成，105 根据 102 收集的账号授权，完成后，通知研发检查数据以及账户权限；[/][]上述完成后，和研发协作，将 101 和 102 的业务迁移到 105 和 106，观察业务状态；[/][]如果所有业务没有问题，证明迁移成功。[/] 3.4 场景四 ：一主一从结构完整迁移主从 接下来看看一主一从结构完整迁移主从怎么做。和场景二类似，不过此处是迁移所有库。因 101 主节点 IO 出现瓶颈，打算将主节点 101 和从节点 102 同时迁移至新的机器 103 和 104，103 充当主节点，104 充当从节点。迁移完成后，以前的主节点和从节点废弃，架构图如图四。此次迁移是全库迁移，容量大，并且需要保证实时。这次的迁移比较特殊，因为采取的策略是先替换新的从库，再替换新的主库。所以做法稍微复杂些。 图四 一主一从结构完整迁移主从架构图  具体的做法是这样：

[]研发将 102 的读业务切到主库；[/][]确认 102 MySQL 状态（主要看 PROCESS LIST，MASTER STATUS），观察机器流量，确认无误后，停止 102 从节点的服务；[/][]104 新建 MySQL 实例，建成以后，停止 MySQL 服务，并且将整个数据目录 mv 到其他地方做备份，注意，此处操作的是 104，也就是未来的从库；[/][]将 102 的整个 mysql 数据目录使用 rsync 拷贝到 104；[/][]拷贝的同时，在 101 授权，使 104 有拉取 binlog 的权限（REPLICATION SLAVE, REPLICATION CLIENT）；[/][]待拷贝完成，修改 104 配置文件中的 server_id，注意不要和 102 上的一致；[/][]在 104 启动 MySQL 实例，注意配置文件中的数据文件路径以及数据目录的权限；[/][]进入 104 MySQL 实例，使用 SHOW SLAVE STATUS 检查从库状态，可以看到 Seconds_Behind_Master 在递减；[/][]Seconds_Behind_Master 变为 0 后，表示同步完成，此时可以用 pt-table-checksum 检查 101 和 104 的数据一致，但比较耗时，而且对主节点有影响，可以和开发一起进行数据一致性的验证；[/][]除了做数据一致性验证外，还需要验证账号权限，以防业务迁走后访问出错；[/][]和研发协作，将之前 102 从节点的读业务切到 104；[/][]利用 102 的数据，将 103 变为 101 的从节点，方法同上；[/][]接下来到了关键的地方了，我们需要把 104 变成 103 的从库；[/][]104 STOP SLAVE；[/]

103 STOP SLAVE IO_THREAD;103 STOP SLAVE SQL_THREAD，记住 MASTER_LOG_FILE 和 MASTER_LOG_POS；104 START SLAVE UNTIL 到上述 MASTER_LOG_FILE 和 MASTER_LOG_POS；104 再次 STOP SLAVE；104 RESET SLAVE ALL 清除从库配置信息；103 SHOW MASTER STATUS，记住 MASTER_LOG_FILE 和 MASTER_LOG_POS；103 授权给 104 访问 binlog 的权限；104 CHANGE MASTER TO 103；104 重启 MySQL，因为 RESET SLAVE ALL 后，查看 SLAVE STATUS，Master_Server_Id 仍然为 101，而不是 103；104 MySQL 重启后，SLAVE 回自动重启，此时查看 IO_THREAD 和 SQL_THREAD 是否为 YES；103 START SLAVE；此时查看 103 和 104 的状态，可以发现，以前 104 是 101 的从节点，如今变成 103 的从节点了。

[]业务迁移之前，断掉 103 和 101 的同步关系；[/][]做完上述步骤，可以和研发协调，把 101 的读写业务切回 102，读业务切到 104。需要注意的是，此时 101 和 103 均可以写，需要保证 101 在没有写入的情况下切到 103，可以使用 FLUSH TABLES WITH READ LOCK 锁住 101，然后业务切到 103。注意，一定要业务低峰执行，切记；[/][]切换完成后，观察业务状态；[/][]如果业务没有问题，证明迁移成功。[/]

 3.5场景五 ：双主结构跨机房迁移接下来看看双主结构跨机房迁移怎么做。某项目出于容灾考虑，使用了跨机房，采用了双主结构，双边均可以写。因为磁盘空间问题，需要对 A 地的机器进行替换。打算将主节点 1.101 和从节点 1.102 同时迁移至新的机器 1.103 和 1.104，1.103 充当主节点，1.104 充当从节点。B 地的 2.101 和 2.102 保持不变，但迁移完成后，1.103 和 2.101 互为双主。架构图如图五。因为是双主结构，两边同时写，如果要替换主节点，单方必须有节点停止服务。  图五 双主结构跨机房迁移架构图  具体的做法如下：[list=1][]1.103 和 1.104 新建实例，搭建主从关系，此时的主节点和从节点处于空载；[/][]确认 1.102 MySQL 状态（主要看 PROCESS LIST），注意观察 MASTER STATUS 不再变化。观察机器流量，确认无误后，停止 1.102 从节点的服务；[/][]1.103 新建 MySQL 实例，建成以后，停止 MySQL 服务，并且将整个数据目录 mv 到其他地方做备份；[/][]将 1.102 的整个 mysql 数据目录使用 rsync 拷贝到 1.103；[/][]拷贝的同时，在 1.101 授权，使 1.103 有拉取 binlog 的权限（REPLICATION SLAVE, REPLICATION CLIENT）；[/][]待拷贝完成，修改 1.103 配置文件中的 server_id，注意不要和 1.102 上的一致；[/][]在 1.103 启动 MySQL 实例，注意配置文件中的数据文件路径以及数据目录的权限；[/][]进入 1.103 MySQL 实例，使用 SHOW SLAVE STATUS 检查从库状态，可以看到 Seconds_Behind_Master 在递减；[/][]Seconds_Behind_Master 变为 0 后，表示同步完成，此时可以用 pt-table-checksum 检查 1.101 和 1.103 的数据一致，但比较耗时，而且对主节点有影响，可以和开发一起进行数据一致性的验证；[/][]我们使用相同的办法，使 1.104 变成 1.103 的从库；[/][]和研发沟通，除了做数据一致性验证外，还需要验证账号权限，以防业务迁走后访问出错；[/][]此时，我们要做的就是将 1.103 变成 2.101 的从库，具体的做法可以参考场景四；[/][]需要注意的是，1.103 的单双号配置需要和 1.101 一致；[/][]做完上述步骤，可以和研发协调，把 1.101 的读写业务切到 1.103，把 1.102 的读业务切到 1.104。观察业务状态；[/][]如果业务没有问题，证明迁移成功。[/]  3.6 场景六 ：多实例跨机房迁移 接下来我们看看多实例跨机房迁移证明做。每台机器的实例关系，我们可以参考图六。此次迁移的目的是为了做数据修复。在 2.117 上建立 7938 和 7939 实例，替换之前数据异常的实例。因为业务的原因，某些库只在 A 地写，某些库只在 B 地写，所以存在同步过滤的情况。 图六 多实例跨机房迁移架构图  具体的做法如下：

[]1.113 针对 7936 实例使用 innobackupex 做数据备份，注意需要指定数据库，并且加上 slave-info 参数；[/][]备份完成后，将压缩文件拷贝到 2.117；[/][]2.117 创建数据目录以及配置文件涉及的相关目录；[/][]2.117 使用 innobackupex 恢复日志；[/][]2.117 使用 innobackupex 拷贝数据；[/][]2.117 修改配置文件，注意如下参数：replicate-ignore-db、innodb_file_per_table = 1、read_only = 1、 server_id；[/][]2.117 更改数据目录权限；[/][]1.112 授权，使 2.117 有拉取 binlog 的权限（REPLICATION SLAVE, REPLICATION CLIENT）；[/][]2.117 CHANGE MASTE TO 1.112，LOG FILE 和 LOG POS 参考 xtrabackup_slave_info；[/][]2.117 START SLAVE，查看从库状态；[/][]2.117 上建立 7939 的方法类似，不过配置文件需要指定 replicate-wild-do-table；[/][]和开发一起进行数据一致性的验证和验证账号权限，以防业务迁走后访问出错；[/][]做完上述步骤，可以和研发协调，把相应业务迁移到 2.117 的 7938 实例和 7939 实例。观察业务状态；[/][]如果业务没有问题，证明迁移成功。[/]

 四、注意事项 介绍完不同场景的迁移方案，需要注意如下几点：

[]数据库迁移，如果涉及事件，记住主节点打开 event_scheduler 参数；[/][]不管什么场景下的迁移，都要随时关注服务器状态，比如磁盘空间，网络抖动；另外，对业务的持续监控也是必不可少的；[/][]CHANGE MASTER TO 的 LOG FILE 和 LOG POS 切记不要找错，如果指定错了，带来的后果就是数据不一致或者搭建主从关系失败；[/][]执行脚本不要在 $HOME 目录，记住在数据目录；[/][]迁移工作可以使用脚本做到自动化，但不要弄巧成拙，任何脚本都要经过测试；[/][]每执行一条命令都要三思和后行，每个命令的参数含义都要搞明白；[/][]多实例环境下，关闭 MySQL 采用 mysqladmin 的形式，不要把正在使用的实例关闭了；[/][]从库记得把 read_only = 1 加上，这会避免很多问题；[/][]每台机器的 server_id 必须保证不一致，否则会出现同步异常的情况；[/][]正确配置 replicate-ignore-db 和 replicate-wild-do-table；[/][]新建的实例记得把 innodb_file_per_table 设置为 1，上述中的部分场景，因为之前的实例此参数为 0，导致 ibdata1 过大，备份和传输都消耗了很多时间；[/][]使用 gzip 压缩数据时，注意压缩完成后，gzip 会把源文件删除；[/][]所有的操作务必在从节点或者备节点操作，如果在主节点操作，主节点很可能会宕机；[/][]xtrabackup 备份不会锁定 InnoDB 表，但会锁定 MyISAM 表。所以，操作之前记得检查下当前数据库的表是否有使用 MyISAM 存储引擎的，如果有，要么单独处理，要么更改表的 Engine。[/]

 五、技巧在 MySQL 迁移实战中，有如下技巧可以使用：

[]任何迁移 LOG FILE 以 relay_master_log_file（正在同步 master 上的 binlog 日志名）为准，LOG POS 以 exec_master_log_pos（正在同步当前 binlog 日志的 POS 点）为准；[/][]使用 rsync 拷贝数据，可以结合 expect、nohup 使用，绝对是绝妙组合；[/][]在使用 innobackupex 备份数据的同时可以使用 gzip 进行压缩；[/][]在使用 innobackupex 备份数据，可以加上 --slave-info 参数，方便做从库；[/][]在使用 innobackupex 备份数据，可以加上 --throttle 参数，限制 IO，减少对业务的影响。还可以加上 --parallel=n 参数，加快备份，但需要注意的是，使用 tar 流压缩，--parallel 参数无效；[/][]做数据的备份与恢复，可以把待办事项列个清单，画个流程，然后把需要执行的命令提前准备好；[/][]本地快速拷贝文件夹，有个不错的方法，使用 rsync，加上如下参数：-avhW --no-compress --progress；[/][]不同分区之间快速拷贝数据，可以使用 dd。或者用一个更靠谱的方法，备份到硬盘，然后放到服务器上。异地还有更绝的，直接快递硬盘。[/]

 六、总结本文从为什么要迁移讲起，接下来讲了迁移方案，然后讲解了不同场景下的迁移实战，最后给出了注意事项以及实战技巧。归纳起来，也就以下几点：

[]第一，迁移的目的是让业务平稳持续地运行；[/][]第二，迁移的核心是怎么延续主从同步，我们需要在不同服务器和不同业务之间找到方案；[/][]第三，业务切换需要考虑不同 MySQL 服务器之间的权限问题；需要考虑不同机器读写分离的顺序以及主从关系；需要考虑跨机房调用对业务的影响。[/]

读者在实施迁移的过程中，可以参考此文提供的思路。但怎样保证每个操作正确无误地运行，还需要三思而后行。
 
分享阅读原文：https://dbarobin.com/2015/09/15/migration-of-mysql-on-different-scenes/
文章作者：温国兵老师

Mysqldump适用于各类引擎表
 
对于大规模备份，考虑使用物理方法： 

[]mysqlbackup(适于InnoDB、MyISAM及其他表)[/][]mysqlhotcopy(适于MyISAM表)[/][]Xtrabackup(适于InnoDB及MyISAM表)[/][]LVM(适于各类表)[/]

MHA简介​
MHA是由日本人yoshinorim（原就职于DeNA现就职于FaceBook）开发的比较成熟的MySQL高可用方案。MHA能够在30秒内实现故障切换，并能在故障切换中，最大可能的保证数据一致性。目前淘宝也正在开发相似产品TMHA，目前已支持一主一从。
 
MHA架构
MHA由MHA Manager和MHA Node组成,如下图所示：

 
MHA Manager：
运行一些工具，比如masterha_manager工具实现自动监控MySQL Master和实现master故障切换，其它工具实现手动实现master故障切换、在线mater转移、连接检查等等。一个Manager可以管理多个master-slave集群
 
MHA Node：
部署在所有运行MySQL的服务器上，无论是master还是slave。主要作用有三个。
    Ⅰ、保存二进制日志
           如果能够访问故障master，会拷贝master的二进制日志

     II、应用差异中继日志
          从拥有最新数据的slave上生成差异中继日志，然后应用差异日志。

     III、清除中继日志
          在不停止SQL线程的情况下删除中继日志
 
MHA工作原理

当master出现故障时，通过对比slave之间I/O线程读取masterbinlog的位置，选取最接近的slave做为latestslave。其它slave通过与latest slave对比生成差异中继日志。在latest slave上应用从master保存的binlog，同时将latest slave提升为master。最后在其它slave上应用相应的差异中继日志并开始从新的master开始复制。
 
在MHA实现Master故障切换过程中，MHA Node会试图访问故障的master（通过SSH），如果可以访问（不是硬件故障，比如InnoDB数据文件损坏等），会保存二进制文件，以最大程度保证数据不丢失。MHA和半同步复制一起使用会大大降低数据丢失的危险。
 
当前高可用方案
Heartbeat+DRBD：

[]开销：需要额外添加处于被动状态的master server（并不处理应用流量）[/][]性能：为了实现DRBD复制环境的高可用，innodb-flush-log-at-trx-commit和sync-binlog必须设置为1，这样会导致写性能下降。[/][]一致性：在master上必要的binlog时间可能会丢失，这样slave就无法进行复制，导致产生数据一致性问题[/]

 MySQL Cluster：MySQL Cluster真正实现了高可用，但是使用的是NDB存储引擎，并且SQL节点有单点故障问题。 半同步复制(5.5+)

[]半同步复制大大减少了“binlog events只存在故障master上”的问题。[/][]在提交时，保证至少一个slave（并不是所有的）接收到binlog，因此一些slave可能没有接收到binlog。[/]

全局事务ID​

[]在二进制文件中添加全局事务ID（global transaction id）需要更改binlog格式，在5.1/5.5版本中不支持。[/][]在应用方面有很多方法可以直线全局事务ID，但是仍避免不了复杂度、性能、数据丢失或者一致性的问题。[/]

 
PXC：
PXC实现了服务高可用，数据同步时是并发复制。但是仅支持InnoDB引擎，所有的表都要有主键。锁冲突、死锁问题相对较多等等问题。
 
MHA的优势
1、故障切换快
在主从复制集群中，只要从库在复制上没有延迟，MHA通常可以在数秒内实现故障切换。9-10秒内检查到master故障，可以选择在7-10秒关闭master以避免出现裂脑，几秒钟内，将差异中继日志（relay log）应用到新的master上，因此总的宕机时间通常为10-30秒。恢复新的master后，MHA并行的恢复其余的slave。即使在有数万台slave，也不会影响master的恢复时间。

DeNA在超过150个MySQL（主要5.0/5.1版本）主从环境下使用了MHA。当mater故障后，MHA在4秒内就完成了故障切换。在传统的主动/被动集群解决方案中，4秒内完成故障切换是不可能的。
 
2、master故障不会导致数据不一致
当目前的master出现故障是，MHA自动识别slave之间中继日志（relay log）的不同，并应用到所有的slave中。这样所有的salve能够保持同步，只要所有的slave处于存活状态。和Semi-Synchronous Replication一起使用，（几乎）可以保证没有数据丢失。

3、无需修改当前的MySQL设置
MHA的设计的重要原则之一就是尽可能地简单易用。MHA工作在传统的MySQL版本5.0和之后版本的主从复制环境中。和其它高可用解决方法比，MHA并不需要改变MySQL的部署环境。MHA适用于异步和半同步的主从复制。
 
启动/停止/升级/降级/安装/卸载MHA不需要改变（包扩启动/停止）MySQL复制。当需要升级MHA到新的版本，不需要停止MySQL，仅仅替换到新版本的MHA，然后重启MHA　Manager就好了。
 
MHA运行在MySQL 5.0开始的原生版本上。一些其它的MySQL高可用解决方案需要特定的版本（比如MySQL集群、带全局事务ID的MySQL等等），但并不仅仅为了master的高可用才迁移应用的。在大多数情况下，已经部署了比较旧MySQL应用，并且不想仅仅为了实现Master的高可用，花太多的时间迁移到不同的存储引擎或更新的前沿发行版。MHA工作的包括5.0/5.1/5.5的原生版本的MySQL上，所以并不需要迁移。

4、无需增加大量的服务器
MHA由MHA Manager和MHA Node组成。MHA Node运行在需要故障切换/恢复的MySQL服务器上，因此并不需要额外增加服务器。MHA Manager运行在特定的服务器上，因此需要增加一台（实现高可用需要2台），但是MHA Manager可以监控大量（甚至上百台）单独的master，因此，并不需要增加大量的服务器。即使在一台slave上运行MHA Manager也是可以的。综上，实现MHA并没用额外增加大量的服务。

5、无性能下降
MHA适用与异步或半同步的MySQL复制。监控master时，MHA仅仅是每隔几秒（默认是3秒）发送一个ping包，并不发送重查询。可以得到像原生MySQL复制一样快的性能。

6、适用于任何存储引擎
MHA可以运行在只要MySQL复制运行的存储引擎上，并不仅限制于InnoDB，即使在不易迁移的传统的MyISAM引擎环境，一样可以使用MHA。
分享阅读：原文