mysql 把从库切换主库_MySQL主从复制《主库已经有数据的解决方案（之亲自动手））》...

MySQL主从复制架构

目前MySQL支持两种复制类型：1.传统方式：

基于主库的bin-log将日志事件和事件位置复制到从库，从库再加以应用来达到主从同步的目的。

2.Gtid方式(MySQL>=5.7推荐使用)：

基于GTID的复制中，从库会告知主库已经执行的事务的GTID的值，然后主库会将所有未执行的事务的GTID的列表返回给从库，并且可以保证同一个事务只在指定的从库执行一次。

MySQL复制有多种类型：1.异步复制

一个主库，一个或多个从库，数据异步同步到从库。

2.同步复制

在MySQL cluster中特有的复制方式。

3.半同步复制

在异步复制的基础上，确保任何一个主库上的事物在提交之前至少有一个从库已经收到该事物并日志记录下来。

4.延迟复制

在异步复制的基础上，人为设定主库和从库的数据同步延迟时间，即保证数据延迟至少是这个参数。

下面是基于二进制日志点的异步复制：

MySQL主从复制原理：

我们在MySQL中配置了主从之后，只要我们对Master节点进行了写操作，这个操作将会被保存到MySQL的binary-log(bin-log)日志当中，当slave连接到master的时候，master机器会为slave开启binlog dump线程。当master 的 binlog发生变化的时候，Master的dump线程会通知slave，并将相应的binlog内容发送给Slave。而Slave节点在主从同步开启的时候，会创建两个线程，一个I/O线程，一个SQL线程。I/O线程：该线程链接到master机器，master机器的binlog发送到slave的时候，IO线程会将该日志内容写在本地的中继日志(Relay log)中。

SQL线程：该线程读取中继日志中的内容，并且根据中继日志中的内容对Slave数据库做相应的操作。

可能造成的问题：在写请求相当多的情况下，可能会造成Slave数据和Master数据不一致的情况，这是因为日志传输过程中的短暂延迟、或者写命令较多，系统速度不匹配造成的。

这大致就是MySQL主从同步的原理，真正在其中起到作用的实际上就是这两个日志文件，binlog和中继日志(Relay log)。

一. 配置主从数据库服务器参数：Master服务器参数：

[mysqld]

log-bin = /www/server/data/mysql-bin

binlog_format = mixed

server-id = 100

#expire_logs_days = 10 #日志过期时间

#max_binlog_size = 200M #日志最大容量，可以不设置，有默认值，设置后MySQL无法重启，我遇到情况

binlog_do_db = test

#binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可

2. Slave服务器参数：

[mysqld]

log-bin = /www/server/data/mysql-bin

binlog_format = mixed

server-id = 200

#expire_logs_days = 10 #日志过期时间

#max_binlog_size = 200M #日志最大容量，可以不设置，有默认值，设置后MySQL无法重启，我遇到情况

relay_log = /www/server/data/relay-bin

#指定relay_log日志的存放路径和文件前缀 ，不指定的话默认以主机名作为前缀

read_only = on

skip_slave_start = on

#下面两个参数是把主从复制信息存储到innodb表中，默认情况下主从复制信息是存储到文件系统中的，如果从服务器宕机，很容易出现文件记录和实际同步信息不同的情况，存储到表中则可以通过innodb的崩溃恢复机制来保证数据记录的一致性

master_info_repository = TABLE

relay_log_info_repository = TABLE

二. 在Master服务器上建立复制账号：需要设置REPLICATION SLAVE权限:

CREATE USER '账号'@'2.7.4.5' IDENTIFIED BY '密码';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'1.1.1.1';

flush privileges; #刷新权限

三. 在Slave服务器的操作：查看master的 binlog的文件名和binlog偏移量

输入 show master status;

查看master的 binlog的文件名和binlog偏移量

2. 配置slave服务器：

mysql> CHANGE MASTER TO

-> MASTER_HOST='1.1.1.1',

-> MASTER_USER='账号',

-> MASTER_PASSWORD='密码',

-> MASTER_LOG_FILE='mysql-bin.00001',

-> MASTER_LOG_POS=66;

#注意，这里的master_log_file，就是binlog的文件名，输入上图中的mysql-bin.00001,每个人的都可能不一样。

#注意，这里的master_log_pos是binlog偏移量，输入上图中的66，每个人的都可能不一样。

四. master和salve数据库的数据保持一致(主库已经有数据的解决方案)

主从数据库的数据要保持一致，不然主从同步会出现bug...主库已经有数据的解决方案：

第一种方案是选择忽略主库之前的数据，不做处理。这种方案只适用于不重要的可有可无的数据，并且业务上能够容忍主从库数据不一致的场景。

第二种方案是对主库的数据进行备份，然后将主数据库中导出的数据导入到从数据库，然后再开启主从复制，以此来保证主从数据库数据一致。

下面是第二种方案的操作：锁定主数据库，只允许读取不允许写入，这样做的目的是防止备份过程中或备份完成之后有新数据插入，导致备份数据和主数据数据不一致。

mysql> flush tables with read lock;

2. 通过mysql主服务器上的全备初始化从服务器上数据：

[root@localhost data]# cd /data/db_backup/

[root@localhost db_backup]# mysqldump -uroot -p --master-data=1 --single-transaction --routines --triggers --events --all-databases > all.sql

Enter password:

3. 解锁主数据库：

unlock tables;

4. 把数据全量导入slave数据库，保证主从数据一致

五. 开始主从同步:

在从服务器MySQL命令行

输入 start slave;

开启从同步

然后输入 show slave status\G;

查看从节点状态

六. 注意事项：

如果出现IO线程一直在Connecting状态，可以看看是不是俩台机器无法相互连接，如果可以相互连接，那么有可能是Slave账号密码写错了，重新关闭Slave然后输入上面的配置命令再打开Slave即可。

如果出现SQL线程为NO状态，那么有可能是从数据库和主数据库的数据不一致造成的，或者事务回滚，如果是后者，先关闭 stop slave，然后先查看master的binlog和position，然后输入配置命令，再输入set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;，再重新start slave;即可，如通过是前者，那么就排查一下是不是存在哪张表没有被同步，是否存在主库存在而从库不存在的表，自己同步一下再重新配置一遍即可。

Could not find first log file name in binary log index file

如果查看从库状态发现此问题，请查看主库状态，将其中的File和Position字段通过在从库中执行以下SQL语句写入从库配置中。

change master to master_log_file='mysql-bin.000001',master_log_pos=3726

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

如果启动slave时出现此错误，主要可能是因为保存着以前slave用的relay-log，可以执行以下语句来启动slave。

reset slave;

start slave;

七. 总结：

1.MySQL 从5.6(MySQL>=5.7 在5.7完善，建议使用)引入了GTID(Global Transaction IDs)使其复制功能的配置、监控及管理变得更加易于实现，且更加健壮。配置方法和基于日志的主从复制大体一样，所以不做重复。MySQL5.7推荐使用GTID配置主从复制。

2.由于高并发下，MySQL主从复制，会导致主从同步延时的问题。MySQL 在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。具体的解决办法下篇文章论述。。。