mysql 主从复制

mysql复制

        MySQL复制支持单向，异步复制。通过一台主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。MySQL主从复制是异步进行的。同步需要版本为5.5,使用google提供的插件来实现。

        MySQL主从复制操作很灵活既可以实现整个服务的级别的复制，也可以实现对某个库，甚至某个数据库中的指定的某个对象进行复制。

MySQL主从复制应用场景

  1. 提高性能。通过一(多)主多从的部署方案，将涉及数据写的操作放在Master端操作，而将数据读的操作分散到众多的Slave当中。降低了Master的负载，提高数据写入的响应效率；多台从服务器提供读，分担了请求，提高了读的效率。
  2. 数据安全。数据复制到Slave节点，即使Master宕机，Slave节点还保存着完整数据。
  3. 数据分析。将数据分析，放在slave上。

主从复制的原理

        MySQL的Replication是一个异步的复制过程，从一个MySQL实例(Master)复制到另一台MySQL实例上。在Master和Slave之间复制的整个过程主要由3个线程完成，其中两个线程（SQL线程和IO线程）在Slave端，另外一个线程(IO线程)在Master端。

        要实现主从复制，首先要在Master端打开Binary Log功能。因为整个复制过程实际上 就是Slave从Master上获取二进制日志，然后在自己身上完全按照产生的顺序一次执行日志中记录的各种操作的过程。

        复制的具体过程如下：

  1. Slave的IO线程连上Master，并请求日志文件指定位置(或从开始的日志)之后的日志的内容。
  2. Master接收到来自Slave的IO线程请求后，负责复制IO线程根据请求的信息读取指定日志之后的日志信息，返回给Slave端的IO线程。返回信息中除了日志所包含的信息，还包含了包括本次返回的信息在Master端的Binary Log文件的名称和位置。
  3. Slave的IO线程接受到信息后，将日志内容一次写入Slave端的Relay Log文件(mysql-relay-bin.xxxx)的末端，并将读取到的Master端的bin-log的文件和位置记录到master-info文件中，以便在下一次读取时能够清楚地告诉Master，下次从bin-log哪个位置开始往后的日志内容。
  4. Slave的SQL线程检测检测到Relay Log中更新内容后，会马上解析该Log文件中的内容，还原成在Master端真实执行时的可执行的SQL语句，并执行这些SQK语句。实际上Master和Slave执行同样的语句。

Binary Log 记录方式

Row Level

        Binary Log会记录成每一行数据被修改的形式，然后在Slave端再对相同的数据进行修改。

        优点：在Row Level模式下，Binnary Log可以不记录执行的Query语句的上下文相关信息，只要记录哪一行修改了，修改成什么样子。Row Level会详细的记录下每一行数据的修改细节，而且不会出现某个特定情况下的存储过程，或Function，以及Trigger的调用和触发无法被正确复制问题。

        缺点：产生大量的日志内容。

Statment Level

        每一条会修改的SQL语句都会记录到Master的Binnary中。Slave端在复制的时候，SQL线程会解析成和原来Master端执行过相同的SQL语句，并再次执行。

        优点：首先，解决了Row Level下的缺点，不须要记录每一行的数据变化，减少了Binnary Log日志量，节约了IO成本，提高了性能。

        缺点：由于它是记录的执行语句，为了让这些语句在Slave端也能正确执行。那么它还必须记录每条语句在执行时的一些相关信息，即上下文信息，以保证所有语句在Slave端被执行的时候能够得到和在Master端执行时相同的结果。另外，由于MySQL发展比较快，很多新功能不断加入，使得MySQL复制遇到了不小的挑战，复制时设计的内容岳父在，越容易出bug。在Statement Level下，目前已发现不少的情况下会造成MySQL的复制问题。主要是在修改数据使用了某些特定的函数货功能后，出现，比如：Sleep()函数在有些版本中就不能正确的复制，在存储过程中使用了last_insert_id()函数，可能会使Slave和Master的到不一致的ID，等等。

Mixed Level

        在Mixed模式下，MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。除了MySQL认为通过Statement方式可能造成复制过程中Master和Slave之间产生不一致数据。(如特殊Procedure和Funtion的使用，UUID()函数的使用等特殊情况)时，它会选择ROW的模式来记录变更之外，都会使用Statement方式。

设置主从

        主从设置的主要步骤是

  1. 修改Master和Slave的my.cnf中的server-id，使之唯一,开启binlog。
  2. 若不停Master时，加入全局锁，记录bin-log文件和bin-log文件的位置，全备要同步的数据库；解除全局锁
  3. 若可以停库时，停止主库，物理备份所需要同步的数据库。
  4. 在Slave端恢复备份的数据。
  5. 用change master在Slave建立与master的联系。
  6. 启动Slave。
  7. 检查Slave的状态。

实例

  1. 不停主库的建立主从复制。

        第一部分 对主库的操作

  1. 修改主库的文件，开启MySQL的bin-log。（一般安装的时候最好先做好，这样可以不停库。）
    修改主库的配置文件my.cnf：
1
2
3
4
5
6
vim /etc/my.cnf
server-id = 1 # 设置server-id 确保id为唯一，最好用ip地址，后三位
bin-log=mysql-bin # 设置 bin-log，这地方可以指定为bin-log的目录？
/etc/init.d/mysqld restart

        修改完成后，重启mysql

  1. 登录主库，添加从库的同步账号。
1
2
mysql –uroot –p
grant replication slave on *.* to 'rep'@'%' identified by 'passwd';
  1. 对主库进行锁表操作，禁止更新，（为了防止备份时的数据不一致问题）。并查看bin-log的名字和日志的（position）。
1
2
3
4
5
6
7
8
9
10
11
12
flush tables with read lock；
show master status；
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000107 | 38874616 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>

        记录这两个值

1
2
mysql-bin.000107
38874616

        取得bin-log 和 position

1
mysql -uroot -p'yz11235' -e "show master status" | awk '{if (NR == 2){ print $1 "\n" $2;}}' > temp.txt
  1. 对主库进行全备份。
1
mysqldump -h hostname -uroot -p’’ -A -B -F | gzip >alldb.sql.gz
  1. 导出数据库后，进行对数据库表解锁。
1
unlock tables

        第二部分 从库上操作

  1. 在从库上设置my.cnf，设置server-id，和注释bin-log。
1
2
server-id = 2
# bin-log = mysql-bin

        重启 mysql。

  1. 登录从库，并设置从库信息
1
2
3
4
5
6
7
8
CHANGE MASTER TO 
MASTER_HOST='10.143.39.174',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='passwd',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=151348020,
MASTER_CONNECT_RETRY=10;
  1. 启动 从库
1
start slave;
  1. 查看从库状态，
1
show slave status\G;

        观察：

1
2
slave_IO_Running : Yes
Slave_SQL_Running : Yes

        以上两个为Yes 说明主从已经成功。

1
Second_behind_master = 0

        这是从库落后主库的秒数。