mysql主从配置备忘录

最近在弄某些功能的时候，需要了解一下mysql的主从同步。了解了一部分，记录一下当个备忘。

1.master 和 slave的server_id不能相同。

2.binary log的类型有三种，Statement、ROW、Mixed

　　statement：默认类型，会记录每条sql的执行。

　　row：记录产生变化的行。

　　mixed：混合型

　　配置方式在my.ini中指定：

　　#选择row模式

　　binlog-format=ROW

3.开启binlog的配置：

　　my.ini中添加：

　　log-bin=mysql-bin

　　binlog-do-db=a111cnnet # 要同步的数据库，多可写多行
　　binlog-ignore-db=mysql #忽略的数据库，多可写多行

4.配置完master 可以通过 show master status进行查看。

5.根据show master status的信息可以配置slave，命令是：

　　CHANGE MASTER TO MASTER_HOST='172.18.135.185',MASTER_PORT=3306,MASTER_USER='repl',
　　MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000028',MASTER_LOG_POS=5408;

　　其中master_log_file，master_log_pos就是show master status中显示的。

6.配置主从的时候，需要在主上面给从授权。

　　GRANT REPLICATION SLAVE ON . to 'repl'@'192.168.8.11' identified by 'passwd';

7.用show slave status可以查看slave的状态，如果是

　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes

　　则配置成功。

8.查看binlog命令：show binlog events in 'mysql-bin.000023';

9.主从原理：

主要的实现原理、具体操作、示意图

1)、在master机器上的操作：
　  当master上的数据发生变化时，该事件变化会按照顺序写入bin-log中。当slave链接到master的时候，master机器会为slave开启binlog dump线程。当master的binlog发生变化的时候，bin-log dump线程会通知slave，并将相应的binlog内容发送给slave。
2)、在slave机器上操作：

　  当主从同步开启的时候，slave上会创建两个线程：I\O线程。该线程连接到master机器，master机器上的binlog dump 线程会将binlog的内容发送给该I\O线程。该I/O线程接收到binlog内容后，再将内容写入到本地的relay log；sql线程。该线程读取到I/O线程写入的ralay log。并且根据relay log。并且根据relay log 的内容对slave数据库做相应的操作。

3)、MySQL主从复制原理图如下:

 

从库生成两个线程，一个I/O线程，一个SQL线程；
i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；
主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；
SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

 

10.MySql数据库从库同步的延迟问题

1)、MySQL数据库主从同步延迟原理mysql主从同步原理：主库针对写操作，顺序写binlog，从库单线程去主库顺序读”写操作的binlog”，从库取到binlog在本地原样执行（随机写），来保证主从数据逻辑上一致。mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。

2)、MySQL数据库主从同步延迟是怎么产生的？当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高次要原因：读写binlog带来的性能影响，网络传输延迟。

 

11.备忘：

　　1）当主键是auto_increment时，在丛库写入以后，主库同步过来的数据，会因为主键冲突，在成主从不在同步。

　　2）主库宕机，再次启动，主从同步不会自动开启。

　　3）主库宕机后重新启动，丛库start slave，会将没有同步完成的数据，都在同步一遍。