MySQL之复制(十二)

复制

复制的问题和解决方案

未定义的服务器ID

如果没有在my.cnf里面定义服务器ID,可以通过CHANGE MASTER TO 来设置备库，但却无法启动复制。

mysql>START SLAVE;
ERROR 1200(HY000):The server is not configured as slave;fix in config file or with CHANGE MASTER TO

这个报错可能会让人困惑，因为刚刚执行CHNAGE MASTER TO 设置了备库，并且通过SHOW MASTER STATUS也确认了。执行SELECT @@server_id也可以获得一个值，但这只是默认值，必须为备库显式地设置服务器ID.

对未复制数据的依赖性

如果在主库上有备库不存在的数据库或表，复制会很容易意外中断，反之亦然。假设主库上有一个备库不存在的数据库，命令为scratch。如果在主库上发生对该数据库中表的更新，备库会在尝试重放这些更新时中断。同样的，如果在主库上创建一个备库上已存在的表，复制也可能中断。
没有什么好的解决办法，唯一的办法就是避免在主库上创建备库上没有的表。这样的表是如何创建的呢？有很多可能的方式，其中一些可能更难防范。例如，假设先在备库上创建一个数据库scratch，该数据库在主库上不存在，然后因为某些原因切换了主备。当完成这些后，可能忘记了移除scratch数据库以及它的权限。这时候一些人就可以连接到该数据库并执行一些查询，或者一些定期的任务会发现这些表，并在每个表上执行OPTIMIZE TABLE命令。当提升备库未主库时，或者决定如何配置备库时，需要注意这一点。任何导致主备不同的行为都会产生潜在的问题。

丢失的临时表

临时表在某些时候比较有用，但不幸的是，它与基于语句的复制是不相容的。如果备库崩溃或者正常关闭，任何复制线程拥有的临时表都会丢失。重启备库后，所有依赖于该临时表的语句都会失败。当基于语句进行复制时，在主库上并没有安全使用临时表的方法。许多人确实很喜欢临时表，所以很难说服他们，但这是不可否认的。(已经有人尝试各种方法来解决这个问题，但对于基于语句的复制并没有安全的临时表创建方法，起码一段时期时这样，不管你如何认为，起码已经证明了这是不可行的)。不管它们的存在多么短暂，都会使得备库的启动和停止以及崩溃恢复变得困难，即使是在一个事务内使用也一样。(如果在备库使用临时表可能问题会少些，但如果备库本身也是一个主库，问题依然存在)。
如果备库重启后复制因找不到临时表而停止，可能需要做以下一些事情:可以直接跳过错误，或者手动地创建一个名字和结构相同的表来代替消失的临时表。不管用什么办法，如果写入查询依赖于临时表，都可能造成数据不一致。避免使用临时表没有看起来那么难，临时表主要有两个比较有用的特性:

• 1.只对创建临时表的连接可见。所以不会和其他拥有相同名字临时表的链接起冲突
• 2.随者连接关闭而消失，所以无须显式地移除它们。
  可以保留一个专用的数据库，在其中创建持久表，把它们作为伪临时表，以模拟这些特性。只需要为它们选择一个唯一的名字，还好这容易做到:简单将连接ID拼接到表名之后，例如，之前创建临时表的语句为:CREATE TEMPORARY TABLE top_user(…),现在则可以执行CREATE TABLE temp.top_users_1234(…),其中1234是函数CONECTION_ID()返回值。当应用不再使用伪临时表后，可以将其删除或使用一个清理线程来将其移除。表名中使用连接ID可以用于确定哪些表不再被使用——可以通过SHOW PROCESSLIST命令来获得活跃连接列表，并将其与表名中的连接ID相比较。使用实体表而非临时表还有别的好处。例如，能够帮助你更容易调试应用程序，因为可能通过别的连接来查看应用正在维护的数据。如果使用的是临时表，可能就没这么容易做到。到那时实体表可能会比临时表多一些开销，例如创建会更慢，因为这些表分配的.frm文件需要刷新到磁盘。可以通过进制sync_frm选项来加速，但这可能会导致潜在的风险。
  如果确实需要使用临时表，也应该在关闭备库前确保Slave_open_temp_tables状态变量的值为0.如果不是0，在重启备库后就可能会出现问题。合适的流程是执行STOP SLAVE,检查变量，然后再关闭备库。如果在停止复制前检查变量，可能会发生竞争条件的风险

不复制所有的更新

如果错误地使用SET SQL_LOG_BIN=0或者没有理解过滤规则，备库可能会丢失主库上已经发生的更新。有时候希望利用此特性来做归档，但常常会导致意外并出现不好的结果。例如假设设置了replicate_do_db规则，把sakila数据库的数据复制到某一台备库上，如果在主库上执行如下语句，会导致主备数据不一致:

mysql>USE test;
mysql>UPDATE sakila.actor ....

其他类型的语句甚至会因为没有复制依赖导致备库复制抛出错误而失败。

InnoDB加锁引起的锁争用

正常情况下，InnoDB的读操作是非阻塞的，但在某些情况下需要加锁。特别是在使用基于语句的复制方式时，执行INSERT…SELECT操作会锁定源表上的所有行。MySQL需要加锁以确保该语句的执行结果在主库和备库上是一致的。实际上，加锁导致主库上的语句串行化，以确保和备库上执行的方式相符。
这种设计可能导致锁竞争、阻塞，以及锁等待超时等情况。一种缓解的办法就是避免让事务开启太久以减少阻塞。可以在主库上尽快地提交事务以释放锁。把大命令拆分成小命令，使其尽可能简短。这也是一种减少锁竞争的有效方法。即使有时很难做到，但也是值得的。
另一种办法是替换掉INSERT … SELECT语句，在主库上先执行SELECT INTO OUTFILE,再执行LOAD DATA INFILE。这种方法根块，并且不需要加锁。这种方法很特殊，但有时还是有用的。最大的问题是为输出文件选择一个唯一的名字，并在完成后清理掉文件。可以通过之前讨论过的CONNECTION_ID()来保证文件名的唯一性，并且可以使用定时任务(UNIX的crontab, Windows平台的计划任务)在连接不再使用这些文件后进行自动清理。也可以尝试关闭上面的这种锁机制，而不是使用上面的变通方法。有一种方法可以做到，但在大多数场景下并不是好办法，备库可能会在不知不觉间就失去和主库的数据同步。这也会导致在做恢复时二进制日志变得毫无用处。但如果确实觉得这么做的利大于弊，可以使用下面的办法来关闭这种锁机制:

# THIS IS NOT SAFE!
innodb_locks_unsafe_for_binlog =1

这使得查询的结果所依赖的数据不再加锁。如果第二条查询修改了数据并在第一条查询之前先提交。在主库和备库上执行这两条语句的结果可能不同。对于复制和基于时间点的恢复都是如此。为了了解锁定读取是如何防止混乱的，假设有两张表:一个没有数据，另一个只有一行数据，值为99.有两个事务更新数据。事务1将第二张表的数据插入到第一张表，事务2更新第二张表(源表)，如图所示:
第二步非常重要，事务2尝试去更新源表，这需要在更新的行上加排他锁(写锁)。排他锁与其他锁是不相容的，包括事务1在行记录上加的共享锁。因此事务2需要等待直到事务1完成。事务按照其提交的顺序在二进制日志中记录，所以在备库重放这些事务时产生相同的结果。但从另一方面来说，如果事务1没有在读取的行上加共享锁，就无法保证了，如图显示了在没有锁的情况下的可能的事件序列。
如果没有加锁，记录在日志中的事务顺序在主备上可能会产生不同的结果。MySQL会先记录事务2，这会影响到事务1在备库上的结果，而主库上则不会发生，从而导致了主备的数据不一致。强烈建议在大多数情况下将innodb_locks_unsafe_for_binlog的值设置为0。基于行的复制由于记录了数据的变化而非语句，因此不会存在这个问题。