mysql主从复制

1.普通文件的数据同步与数据库同步

1.1）普通文件的数据同步方案

NFS网络文件共享可以同步存储数据（挂载到新服务器）；

samba共享数据 （http://taokey.blog.51cto.com/4633273/1203553）；

定时任务或守护进程结合rsync，scp；

inotify（sersync）+rsync触发式实时数据同步；

ftp 数据同步；

ssh key+scp/rsync;

1.2）数据库同步方案

自身同步机制——mysql replication，mysql主从复制（逻辑的SQL从写）；oracle dataguard（物理的磁盘块，逻辑的SQL语句从写）

第三方drbd，参考http://oldboy.blog.51cto.com/2561410/1240412

两个服务器同时写数据，也是一种复制机制

2.mysql主从复制架构

2.1）架构模式                                                # mysql主从同步都是异步同步的方式

主从同步：一主一从，一主两从......

主主同步

线性级联单项双主同步（主-主-从）

环状级联单向多主同步

环状级联单向多主多从同步（主主构成环状，每个主再附加从）

2.2）对于主从存在不一致可采取的方法

找一台从库只做备份用，主库能起来，把主库的binlog拉到从库，把宕机一瞬间主库上的写恢复到从库

双写可以相对保证数据一致

通过应用程序写一个短时间的日志，只记录宕机一瞬间发生的事放在内存里

把异步同步换成实时同步，mysql有个Google开发的半同步的插件，要求如果在主上写，主成功，且某一个半同步的从成功，才算真正的成功

3.主从同步的作用

主从服务器互为备份，从而达到实时备份的目的

主从服务器读写分离分担网站压力（主库写，从库读，对于读多写少的，再把从库横向扩展，推荐用程序实现读写分离，小公司会用proxy、Amoeba实现，门户网站会开发dbproxy实现）

分布式dbproxy（读写分离，hash负载均衡，健康检查） ——有难度！

根据服务器拆分业务，独立分担压力

4.mysql主从同步的原理

主库由一个线程完成主从同步——IO线程；

从库由两个线程完成主从同步——SQL线程和IO线程；

要实现主从同步，主库必须开启binlog；

当用户执行增删改的请求时，数据会写到数据文件中，把更新的结果写到binlog中；

主从同步是从库找主库，在change master的点之前，需把主库的数据都给到从库，从库中change master去配置连到主库：包括master_host,master_user,master_password,master_port,master_log_file,master_log_pos等（# change master to master_log_file='mysql-bin.000020',master_log_pos=1191;）；

配置完成后，需创建同步的账号，需在从库上打开start slave的开关，开启同步；

【同步原理】开启同步后，从库向主库的mysql主进程发出请求，主库通过验证用户名、IP地址、端口、密码等判断是否允许连接，然后交给主库的IO处理，主库IO收到从哪个位置点同步，给从库发binlog，从库收到log日志后将其写入relay-log，称为中继日志，并把最后得到的日志及位置点记录master info，这样再次向主库提取log时，接着上次的位置点向主库索要，以便告诉master服务器从哪个文件开始读。IO把binlog放入中继日志之后就不管了，由从库的SQL实时的读中继日志，然后及时的把log文件中的内容解析成在master端曾经执行的SQL语句的内容，并在自身slave服务器上按语句的顺序执行应用这些SQL语句，存放在数据文件中，应用完毕后清理应用过的日志，因此这个主从同步是异步的。

 官方版图：

 5.实战

【实战环境】

选择一台服务器上3306和3307两个mysql实例来模拟主从同步过程

主库 master 3306

从库 slave 3307

5.1）确保主库的log-bin，server-id开启，log-bin可指定路径，如：/data/3306/mysql-bin

#上述两个参数要放在my.cnf的【mysqld】模块下，否则会出错

从库的log-bin可开可不开，server-id主从要不一样！

（补充：从库需要开启binlog的情况：

1.当前从库作为其他从库的主库，即级联同步；

2.把从库作为数据库备份服务器时；

在从库的my.cnf中做以下步骤：

1.打开log-bin

2.添加参数：log-slave-updates

3.expire_logs_days=7 #binlog保留时间设置）

确认log-bin开启，一是可以ll /data/3306/看是否有mysql-bin文件，二是可以进库查看log_bin参数的ON/OFF设置情况

5.2）在主库上建立用于从库复制的账号rep

use oldboy

grant replication slave on *.* to 'rep'@'192.168.6.%' identified by '123456';               #这里要改成mysql使用的虚拟机IP

show grants for rep@'192.168.6.%';                                                #查看是否授权成功该用户

5.3）在主库上做备份

mysql> flush table with read lock;    #锁表，窗口不能退出，退出无效

mysql> show master status;            #拿到备份的位置点（这里为602），临界点，将来恢复就从0010开始

mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events|gzip >/opt/rep.sql.gz       #重新开一个窗口备份数据

或mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events --master-data=2 >/opt/rep.sql.gz  #也能拿到备份的起始位置点

再回到原来窗口，看看bin-log有没有变，确保主库是锁住了的

mysql> show master logs; 

mysql> show master status; 

mysql> unlock tables；

将主库备份同步到从库：

mysql -uroot -poldboy123 -S /data/3307/mysql.sock </opt/rep.sql.gz

5.4）建立主从复制：

登录到从库：

mysql -uroot -p456 -S /data/3307/mysql.sock

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.6.128',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysqlbin_coco.000001',

MASTER_LOG_POS=107;

#如果master-data指定=1，则上述最后两步可省略，自动找到位置，相当于利用mysqldump在半夜做一次全备数据并恢复到从库

#会在3307下产生master.info文件

mysql> start slave;                       #启动从库

mysql> show slave status\G        #看IO，SQL两个线程是否都正常工作，两个yes表示主从搭建成功

5.5）验证主从同步

在主库上建立一个新的database：

mysql> show databases;

mysql> create database oldboy;

mysql>  show databases;

换从库：

mysql>  show databases；                                      #同样生成了oldgril库表示成功

【此过程会产生一系列文件】

在relay-bin.XXXXXX中查看是否有‘create database oldboy’，应该是有的

relay-bin.index记录了relay-bin log更新到哪了

relay-log.info记录了SQL读到relay-bin log的位置和IO取log即master.info中截止的位置

至此，模拟一次主从同步完成了。

6.mysql主从复制常见问题

1）show master status时没有位置点

查看bin-log有没有打开；

2）Last_IO_Error：Got fatal errror 1236 from master when reading data from binary log:'Counld not find first log file name in binary log index file'

查看change master 配置时，MASTER_LOG_FILE指定的文件是否多了空格；

3）flush tables with read lock无法锁表

mysql5.1的锁表是tables，mysql5.5是table，根据不同版本写法不同；

另外，锁表命令的时间会受到两个参数的控制，如果超过设置时间不操作会自动解锁：interactive_timeout = 60、wait_timeout = 60;

show variables like '%timeout%';

4）服务无法正常启动故障

mysql意外关闭，杀了mysql进程后，启动mysql仍然显示mysql is running

ps -ef|grep mysqld

killall mysqld

chkconfig mysqld off

ps -ef|grep mysqld

/data/3306/mysql start   >>>>显示mysql is running...

这是由于启动脚本中的相应配置导致，此时需将之前启动产生的文件删除：rm -f /data/3306/mysql.sock /data/3306/*.pid

/data/3306/mysql start  >>>>显示Starting mysql...

5）由于切换binlog导致show master status位置变化无影响

6）从库IO显示connecting，SQL同步为yes，需检查：

mysql配置文件my.cnf中log-bin是否打开；

主从server-id是否为不同的设置；

主从的网络是否畅通

7）从库IO同步正常，但SQL同步异常显示为NO

比如在从库先创建个数据库butongbu，然后再在主库上创建个butongbu，这时看从库会发现报错

解决方法1：stop slave; set global sql_slave_skip_counter=1; start slave；即让slave跳过一步

解决方法2：根据错误号跳过指定的错误，每个错误号都有不同的含义。在配置文件中添加slave-skip-errors=1032,1062,1007;一般由于入库重复的失败就可以忽略

7.查看复制线程状态

show processlists可以分别在主库、从库上查看主从复制线程状态，从而判断数据库同步是否完成。

7.1）主服务器的常见状态

sending binlog event to slave

finished reading one binlog, switching to next binlog

has sent all binlog to slave, waiting for binlog to be updated

waiting to finalize termination                                        #线程停止时发生的一个很简单的状态

7.2）从服务器的常见状态

从I/O状态：

connecting to master

checking master version

registering slave on master

repquesting binlog dump

waiting to reconnect after a failed binlog dump request

reconnecting after a failed binlog dump request

waiting for master to send event

queneing master event to the relay log

waiting to reconnect after a failed master event read

从SQL线程状态：

reading event from the relay log

has read all relay log ,waiting for the salve I/O thread to update it

waiting for slave mutex on exit                                       #线程停止时发生的一个很简单的状态

8.可实现生产场景主从读写分离的方法

实现从库只能读的方式：

1）生产权限方案1-主库不能再更改了

主库：web oldboy123 10.0.0.1 3306 (select insert delete update)

从库：主库的web用户同步到从库，然后回收insert delete update 权限。

开发只需知道10.0.0.1是主，10.0.0.2 是从。运维则需要回收从的权限

不收回从库权限的话，在从库设置read-only参数防止从库写的操作

# read-only设置方式-在从库的my.cnf中的【mysqld】下添加一行read-only，并重启mysql，但是对于具有super或all privilege权限的用户不生效，可以尝试创建一个只有增删改查询权限的用户。

2） 生产权限方案2

主库：web_w oldboy123 10.0.0.1 3306 (select insert delete update)

从库：web_r oldboy456 10.0.0.2 3306 (select)

风险：web_w连接从库！可能把主库同步到从库了，因此也可设read-only保证从库只读

开发多套用户密码相对不专业

3） 生产权限方案3

mysql库不同步（在配置文件等随便修改一点点参数）：主从库分别进行如下授权

主库：web oldboy123 10.0.0.1 3306 (select insert delete update)

从库：web oldboy123 10.0.0.2 3306 (select)

缺陷：当从库切换主库时，连接用户权限问题。因此可借鉴百度的做法，保留一个从库专门接替主（或者只做简单的备份）。

9.忽略授权表，限制哪些同步，哪些不同步

主库执行：

vi /data/3306/my.cnf

配置-

replicate-ignore-db = mysql

binlog-ignore-db = mysql

binlog-ignore-db = performance_schema

binlog-ignore-db = information_schema

（补充：

master端：

binlog-do-db 二进制记录的数据库，多个数据库用逗号分隔

binlog-ignore-db 二进制忽略的数据库，多个数据库用逗号分隔

slave端：

replication-do-db 设定需要复制的数据库，多个数据库用逗号分隔

replication-ignore-db 设定忽略复制的数据库，多个数据库用逗号分隔

replication-do-table 设定需要复制的表，多个表用逗号分隔

replication-ignore-table 设定忽略复制的表，多个表用逗号分隔

replication-wild-do-table 设定要复制的表，但是可以加通配符

replication-wild-ignore-table 设定忽略复制的表，但可以加通配符）

/data/3306/mysql stop

/data/3306/mysql start

mysql> select user.host from mysql.user;

mysql> flush privileges;

mysql> create user oldgirl@localhost identified by 'oldboy123';

从库查看是否有相同的用户创建出来：

mysql> show slave status\G                         #从库依旧两个yes，同步

mysql> select user.host from mysql.user;     #从库没有相应的用户，即忽略了授权表

10.主从复制原理总结

异步方式同步；

逻辑同步模式，多种模式，默认是通过SQL语句执行（主从不能保证完全一致）；

主库通过记录binlog实现对从库的同步，binlog记录数据库的更新语句；

主库1个IO线程，从库由1个IO线程和1个SQL线程完成的；

从库关键文件master info，relay-log，relay-info等有各自的功能；

如果从库还想级联从库，需要打开从库的log-bin和log-slave-updates参数。

 11.主从同步下宕机问题&调整为双主同步的解决方式

11.1）主库master宕机（数据库/服务器）-假设一主多从

登录从库：

show processlist\G      #查看复制状态

cat  /data/3307/data/master.info  #分别查看从库的master.info，其中mysql-bin对应序号及下面位置号更大的从库，代表其同步性越高，丢失的数据相对少，将此从库作为新的主库

                                                    #如果主库还可以拉起来，则可以把主库的binlog拉取到从库中，再拉起从库为主库

                                                    #或者利用半同步功能，直接选择了实时同步的这个从库（半同步待了解）

>>>确保所有relay log全部更新完毕

在每个从库上执行stop slave io_thread; show processlist;                #直到查看到has read all relay log表示从库更新都执行完毕

>>>登录新的主库-如果该从库配置过授权表，read-only等，都需要注销掉

如：mysql -uroot -p456 -S /data/3307/mysql.sock

stop slave;

retset master;

quit;

>>>进到数据库数据目录，删除master.info和relay-log.info

cd /data/3307/data

rm -f master.info relay-log.info

>>>3307 提升该从库为主库

vi /data/3307/my,cnf

开启log-bin = /data/3307/mysql-bin

重启数据库：/data/3307/mysql restart

>>>其他从库操作

已检查（同步user rep均存在）

登录新的从库：

stop slave；

change master to master_host = '192.168.1.32';              #如果之前在master上设定过VIP，则此步骤可以省略，VIP可以自动漂浮；如果不同步，可以指定位置点进行同步

start slave;

show slave status\G

>>>修改程序配置文件从主库指向从库（新的主库）-开发层面对

>>>修理损坏的主库，作为从库使用

【补充】

如果不是宕机，而是有计划的切换主从

>>>主库锁表

>>>登录所有的库查看同步状态是否完成

>>>

在之前的从库上执行以下：

#在执行reset master时有风险，从库原来的mysqlbin log都会不在了

在之前的主库上执行以下：

 11.2）从库slave宕机（数据库/服务器）-假设一主多从

若从库宕机，则需要重新做从库：

stop slave；

gzip -d oldboy_data_5.sql.gz

mysql -uroot -p456 -S /data/3307/mysql.sock < oldboy_data_5.sql &  //直接灌入数据

change master to master_host='192.168.1.35' , master_user='repl' , master_password='slavepass' , master_log_file='mysql-bin.000004', master_log_pos=333;

start slave;

show slave status\G

11.3）双主模式主从同步-假设原来3306是主，3307是从

>>>解决表的主键自增长变量冲突【mysqld】：

Master1：

auto_increment_increment = 2       #自增ID的间隔，如 1 3 5间隔为2

auto_increment_offset        = 1       #ID的初始位置

Master2：

auto_increment_increment = 2       #自增ID的间隔，如 2 4 6间隔为2

auto_increment_offset        = 2       #ID的初始位置

生成序列为： 1 3 5 6 8 10 11 13 这种，ID可能会不连续

//或者通过程序控制主键自增形式

>>>设置互为主从参数

log-slave-updates                          #开启从库即3307的binlog日志

log-bin=/data/3307/mysqlcoco_bin

>>>原主从数据不同步的解决

如果不同步，则需要通过MySQLdump  -master-data=1 -x 双向做一下同步

>>>配置3306为从库，其主库为3307

mysql> CHANGE MASTER TO

MASTER_HOST='3307的IP',

MASTER_PORT=3307,

MASTER_USER='rep',

MASTER_PASSWORD='123456'；                             #因为做了一次master-data=1，已自动记录了文件读取位置，无需再添加标识位置的参数行

然后查看3306作为3307的从库的状态正常即可 show slave status；            #需重启slave

至此完成了双主模式的主从同步模拟~

 

2018年11月1日

祝好！