MySQL主从同步实践
2012-10-30 16:18:08 
阿炯
MySQL Replication
Replicating a master database mydatabase to slave servers, this is compatible with both InnoDB and MyISAM.
mysql--主多从负载均衡架构示意图
一、mysql 主从服务的配置
A服务器： 192.168.2.124 主服务器master
B服务器： 192.168.2.125 副服务器slave
A: 主服务器设置(主服务器设置)
1. #mysql -u root -p
Mysql>grant replication slave,reload,super on *.* To back@192.168.2.125 identified by ‘123’ ;
上面是Master开放一个账号back密码'123'给IP:192.168.2.125有档案处理的权限 ,查看刚才开放的账号
#mysql mysql
Mysql> select * from user; 可以查到用户的信息
2. 关闭数据库
#mysqladmin -u root -p shutdown
3. 同步mysql主和从的数据库.把主服务器下面的数据拷贝到从服务器下,我是使用源码编译的mysql.数据库在/usr/mysql目录下,数据就在/usr/mysql/var下,就是把主服务器/usr/mysql/var下的所有数据拷贝到从服务器的/usr/mysql/var里
#scp -r /usr/mysql/var/* root@192.168.2.125:/usr/mysql/var/
上面是把主服务器的数据拷贝到从的服务器中
4.修改主服务器的配置文件/etc/my.cnf
确认master上的 `my.cnf` 文件 [mysqld] 区间有 log-bin 选项。这个区间还必须有 server-id=master_id 选项，的值必须是 1 到 2^32-1 之间的正整数。在此设置为：
[mysqld]
log-bin
server-id=1
启动主服务器的mysql
5.查看主服务器的同步配置情况,如下图所示：
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| darkstar-bin.000024 | 316 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
可做总结：如果数据量大的话不太适合使用mysqldump(慢)，如果是myisam表的话，加上--lock-all-tables参数，如果是innodb表的话，加上--single-transaction参数。
而应该采用拷贝文件的方式，请按如下操作步骤：
先在主服务器上锁定所有的表，以免在复制过程中数据发生变化：
mysql> flush tables with read lock;
然后在主服务器上查询当前二进制文件的文件名及偏移位置：
mysql > show master status;
然后停止主服务器上的MySQL服务：
shell> mysqladmin -u root shutdown
注意：如果仅是MyISAM的话，可以不停止MySQL服务，但要在复制数据文件的过程中保持只读锁，如果是InnoDB的话，必须停止MySQL服务。
再拷贝数据文件：
shell> tar -cvf /tmp/mysql-snapshot.tar .
拷贝完别忘了启动主服务上的MySQL服务了。 然后把数据文件应用到从服务器上，再次启动slave的时候使用，记得启动时加上skip-slave-start选项，使之不会立刻去连接master，再在从服务器上设置相关的二进制日志信息。
B: 从服务器设置(从服务器设置)
1.关闭从服务器数据库
#mysqladmin -u root -p shutdown
2.修改从服务器的/etc/my.cnf在my.cnf文件中增加如下选项：
[mysqld]
server-id=slave_id # slave_id 的值和 master_id 类似，是 1 到 2^32-1 之间的正整数。另外，这个ID必须不能和master的ID一样。
在此设置为：
[mysqld]
server-id=2
注意：由于设置了slave的配置信息，mysql在数据库目录下生成master.info 所以如有要修改相关slave的配置要先删除该文件。否则修改的配置不能生效.此文件在/usr/mysql/var/下
3.启动从数据库服务
# mysqld_safe &
4.在slave上执行如下语句，把各个选项的值替换成真实值：
mysql> CHANGE MASTER TO
MASTER_HOST='master_host_name', //主服务器的IP地址
MASTER_USER='replication_user_name', //同步数据库的用户
MASTER_PASSWORD='replication_password', ////同步数据库的密码
MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名
MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置
recorded_log_file_name和recorded_log_position是在主服务器里面查看的,在192.168.2.124机器上查看主服务器的同步配置,在192.168.2.124上:
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| darkstar-bin.000024 | 316 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
如上面显示: recorded_log_file_name就是上面的 File，recorded_log_position就是上面的 Position。
在从服务器192.168.2.125上执行sql语句如下:
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.2.124',
MASTER_USER='back',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='darkstar-bin.000024',
MASTER_LOG_POS=316;
5. 启动从数据库的slave
mysql> slave start;
总结如下：
启动从服务器上的复制线程：
mysql> start slave;
验证主从设置是否已经成功，可以输入如下命令：
mysql> show slave status\G
会得到类似下面的列表：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果这两个选项不全是Yes，那就说明你前面某个步骤配置错了。 应该保证从服务器上任何数据的修改都是通过从主服务器上复制操作获取的，换句话说，从服务器应该是只读的，如果不能保证这一点，则可能造成主从数据不一致。可以在从服务器的my.cnf里加入read-only参数来实现这一点，唯一需要注意的一点事read-only仅对没有super权限的用户有效。所以最好核对一下连接从服务器的用户，确保其没有super权限。
至此配制完成，相关配置文件选项描述如下：
binlog-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
binlog-ignore-db=不需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
C.常见错误解决办法
1、1062 Duplicate entry
从理想角度看，主从数据库应该无故障的运转下去，可以有时候还是会出现一些莫名其妙的问题，比如说即便从未在从服务器上手动更新过数据，但还是可能遇到“Error: 1062 Duplicate entry”错误，具体原因不详，可能是MySQL本身的问题。遇到这类问题的时候，从服务器会停止复制操作，我们只能手动解决问题，具体的操作步骤如下：
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
同样的操作可能需要进行多次，也可以设置自动处理此类操作，在从服务器的my.cnf里设置：
slave-skip-errors=1062
最后说一下日志的问题：时间长了，数据库服务器上的二进制文件会越来越多，清理是必要的，你可以设置自动清理，相关参数是expire_logs_days，也可以使用手动删除的方式，这里说的手动不是指rm，而是指PURGE BINARY LOGS，删除任何日志前，最好在所有的从服务器上通过show slave status命令确认一下相关日志是否已经无用。
2、[ERROR] Error in Log_event::read_log_event(): 'Event too big'
在使用主从复制的时候，出现的问题多半是和日志(主服务器的二进制日志，从服务器的延迟日志)相关的。比如说加入你遇到了上面的错误，你可以根据错误日志的信息在主从数据库服务器上分别执行：
mysqlbinlog 日志文件 > /dev/null
查看错误，如果没有错误，则不会有任何输出，反之会输出错误信息，如果确定了错误是出现在主服务器二进制日志上，可以跳过适当的位置，再在从服务器上重新设定LOG_POS，如果确定了错误是出现在从服务器延迟日志上，则可以删除从服务器的延迟日志(使用CHANGE TO MASTER的时候，除非设定了延迟日志信息，否则会自动删除延迟日志)，并在从服务器上重新设定LOG_POS。期间也可以考虑手动执行不能自动执行的SQL日志。
3、sync_binlog选项
如果版本允许最好在主服上打开sync_binlog选项
补充：有时候，从服务器延迟日志可能已经损坏，这时需要执行CHANGE MASTER TO设置新的日志文件信息，但是在从服务器上SHOW SLAVE STATUS会显示很多日志信息，他们的含义有所不同：
Master_Log_File:Read_Master_Log_Pos 是IO相关的日志信息
Relay_Master_Log_File:Exec_Master_Log_Pos 是SQL相关的日志信息
从服务器需要设置的是SQL相关的日志信息：
slave stop;
change master to master_log_file=’(binlog name in relay_master_log_file)’, master_log_pos=(exec_master_log_pos number);
slave start;
1) When you are using the master as a consistent snapshot, use SHOW MASTER STATUS to determine the position.
2) When you are using a slave as a consistent snapshot, use SHOW SLAVE STATUS and Exec_Master_Log_Pos.
4、主机名变更而引起警告信息
缺省情况下，从服务器会以主机名命名延迟日志，所以一旦你修改了从服务器的主机名就会造成问题，新版MySQL会提示你这个情况：
[Warning] Neither --relay-log nor --relay-log-index were used;
so replication may break when this MySQL server acts as a slave and has his hostname changed!!
Please use '--relay-log=name-relay-bin' to avoid this problem.
设定relay-log可以避免此类问题。
D. 双机互备模式
1. 如果在A加入slave设置，在B加入master设置，则可以做B->A的同步。在A的配置文件中 mysqld 配置项加入以下设置，vi /etc/my.cnf：
master-host=192.168.2.125
master-user=back
master-password=123
master-port=3306
2. #mysql -u root -p
Mysql>grant replication slave,reload,super on *.* To back@192.168.2.125 identified by‘123’;
在B的配置文件中 mysqld 配置项加入以下设置：/etc/my.cnf：
log-bin=
这样设置就能使其B数据库同步A数据库
二、mysql 数据同步相关说明
1.同步机制实现概述
MySQL同步机制基于master把所有对数据库的更新操作(更新、删除等)都记录在二进制日志里。因此，想要启用同步机制，在master就必须启用二进制日志。
每个slave接受来自master上在二进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。应该非常重要地意识到，二进制日志只是从启用二进制日志开始的时刻才记录更新操作的。所有的slave必须在启用二进制日志时把master上已经存在的数据拷贝过来。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的话，那么slave同步就会失败。
2. 同步实现细节
MySQL同步功能由3个线程(master上1个，slave上2个)来实现。执行 START SLAVE 语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志中的语句。master创建一个线程来把日志的内容发送到slave上。这个线程在master上执行 SHOW PROCESSLIST 语句后的结果中的 Binlog Dump 线程便是。slave上的I/O线程读取master的 Binlog Dump 线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是SQL线程，salve用它来读取中继日志，然后执行它们来更新数据。
如上所述，每个mster/slave上都有3个线程。每个master上有多个线程，它为每个slave连接都创建一个线程，每个slave只有I/O和SQL线程。
在MySQL 4.0.2以前，同步只需2个线程(master和slave各一个)。slave上的I/O和SQL线程合并成一个了，它不使用中继日志。
slave上使用2个线程的优点是，把读日志和执行分开成2个独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。例如，如果slave停止了一段时间，那么I/O线程可以在slave启动后很快地从master上读取全部日志，尽管SQL线程可能落后I/O线程好几的小时。如果slave在SQL线程没全部执行完就停止了，不过I/O线程却已经把所有的更新日志都读取并且保存在本地的中继日志中了，因此在slave再次启动后就会继续执行它们了。这就允许在master上清除二进制日志，因为slave已经无需去master读取更新日志了。在mysql中执行 SHOW PROCESSLIST 语句就会告诉我们所关心的master和slave上发生的情况，在master上，SHOW PROCESSLIST 的结果如下：
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 21
User: root
Host: localhost
db: mysql
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 25
User: back
Host: 192.168.2.125:34085
db: NULL
Command: Binlog Dump
Time: 746
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
2 rows in set (0.01 sec)
在这里，线程2是为一个slave连接创建的。结果表明所有未完成的更新日志已经都发送到slave了，master正等待新的更新日志发生。在slave上，SHOW PROCESSLIST 的结果如下：
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 70479
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 16971
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 8
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
3 rows in set (0.00 sec)
这表明线程2是I/O线程，它正连接到master上；线程3是SQL线程，它执行中继日志中的更新操作。现在，这2个线程都处于空闲状态，正等待新的更新日志。
注意：Time 字段的值告诉我们slave上的日志比master晚了多久。
3.Master 同步线程状态
以下列出了master的 Binlog Dump 线程 State 字段中最常见的几种状态。如果在master上没有 Binlog Dump 线程，那么同步就没有在运行。也就是说，没有slave连接上来。
Sending binlog event to slave
事件是由二进制日志构成，一个事件通常由更新语句加上其他信息。线程读取到一个事件并正发送到slave上。
Finished reading one binlog; switching to next binlog
读取完了一个二进制日志，正切换到下一个。
Has sent all binlog to slave; waiting for binlog to be updated
已经读取完全部未完成更新日志，并且全部都发送到slave了。它处于空闲状态，正等待在master上执行新的更新操作以在二进制日志中产生新的事件，然后读取它们。
Waiting to finalize termination
当前线程停止了，这个时间很短
4.Slave的I/O线程状态
以下列出了slave的I/O线程 State 字段中最常见的几种状态。从MySQL 4.1开始，这个状态在执行 SHOW SLAVE STATUS 语句结果的 Slave_IO_State 字段也会出现。这意味着可以只执行 SHOW SLAVE STATUS 语句就能了解到更多的信息。
Connecting to master
该线程证尝试连接到master上。
Checking master version
确定连接到master后出现的一个短暂的状态。
Registering slave on master
确定连接到master后出现的一个短暂的状态。
Requesting binlog dump
确定连接到master后出现的一个短暂的状态。该线程向master发送一个请求，告诉它要请求的二进制文件以及开始位置。
Waiting to reconnect after a failed binlog dump request
如果二进制日志转储(binary log dump)请求失败了(由于连接断开)，该线程在休眠时进入这个状态，并定期重连。重连的时间间隔由'--master-connect-retry'选项来指定。
Reconnecting after a failed binlog dump request
该线程正尝试重连到master。
Waiting for master to send event
已经连接到master，正等待它发送二进制日志。如果master闲置时，这个状态可能会持续较长时间，如果它等待超过 slave_read_timeout 秒，就会发生超时。这时，它就会考虑断开连接，然后尝试重连。
Queueing master event to the relay log
已经读取到一个事件，正把它拷贝到中继日志中以备SQL线程处理。
Waiting to reconnect after a failed master event read
读日志时发生错误(由于连接断开)。该线程在重连之前休眠 master-connect-retry 秒。
Reconnecting after a failed master event read
正尝试重连到master。当连接确定后，状态就变成 Waiting for master to send event。
Waiting for the slave SQL thread to free enough relay log space
relay_log_space_limit 的值非零，中继日志的大小总和超过这个值了。I/O线程等待SQL线程先处理中继日志然后删除它们以释放足够的空间。
Waiting for slave mutex on exit
当前线程停止了，这个时间很短。
5.Slave的SQL线程状态
以下列出了slave的SQL线程 State 字段中最常见的几种状态：
Reading event from the relay log
从中继日志里读到一个事件以备执行。
Has read all relay log; waiting for the slave I/O thread to update it
已经处理完中继日志中的全部事件了，正等待I/O线程写入更新的日志。
Waiting for slave mutex on exit
当前线程停止了，这个时间很短。
SQL线程的 State 字段有时候也可能是一个SQL语句。这意味着它从中继日志中读取到一个事件了，从中提取出SQL语句，并执行它。
简单表述其过程：
master
grant replication slave,reload,super on *.* to repl@'192.168.6.%' identified by 'repl_paswd';
将数据从主库上导出：
for i in cap freeoa groupoffice oa unixaid; do echo export db $i && mysqldump --databases $i --flush-logs --user=dba --verbose --password='password' --opt>$i.sql; done
在导入到从数据库中，当时此时的主库是不能被写的。
for i in $(ls); do echo import db $i && mysql -udba -ppassword<$i; done
在从服上定义那些库不被同步
binlog-ignore-db = mysql
binlog-ignore-db = groupoffice
配置参数说明
server-id
ID值唯一的标识了复制群集中的主从服务器，因此它们必须各不相同。master_id必须为1到23^2–1之间的一个正整数值，slave_id值必须为2到232–1之间的一个正整数值。
log-bin
表示打开binlog,打开该选项才可以通过I/O写到Slave的relay-log,也是可以进行replication的前提;
binlog-do-db
表示需要记录进制日志的数据库。如果有多个数据库可用逗号分隔，或者使用多个binlog-do-db选项
binlog-ignore-db
表示不需要记录二进制日志的数据库。如果有多个数据库可用逗号分隔，或者使用多个binlog-do-db选项
replicate-do-db
表示需要同步的数据库，如果有多个数据库可用逗号分隔，或者使用多个replicate-do-db选项
replicate-ignore-db=mysql
表示不需要同步的数据库，如果有多个数据库可用逗号分隔，或者使用多个replicate-ignore-db=mysql选项
log-slave-updates
配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步
slave-skip-errors
在复制过程，由于各种原因导致binlog中的sql出错，默认情况下，从库会停止复制，要用户介入。可以设置Slave-skip-errors来定义错误号，如果复制过程中遇到的错误号是定义的错误号，便可以跳过。如果从库是用来做备份，设置这个参数会存在数据不一致，不要使用。如果是分担主库的查询压力，可以考虑。
sync_binlog=1 or N
sync_binlog的默认值是0，这种模式下，MySQL不会同步到磁盘中去。这样的话，MySQL依赖操作系统来刷新二进制日志binary log，就像操作系统刷其他文件的机制一样。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量，使binlog在每N次binlog写入后与硬盘同步。当sync_binlog变量设置为1是最安全的，因为在crash崩溃的情况下，你的二进制日志binary log只有可能丢失最多一个语句或者一个事务。但是，这也是最慢的一种方式(除非磁盘有使用带蓄电池后备电源的缓存cache，使得同步到磁盘的操作非常快)。
即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表，MySQL服务器处理COMMIT语句，它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃，重启时，事务被InnoDB回滚，但仍然存在binlog中。可以用–innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释：在MySQL 5.1中不需要–innodb-safe-binlog；由于引入了XA事务支持，该选项作废了)，该选项可以提供更大程度的安全，使每个事务的 binlog(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步，该选项的效果是崩溃后重启时，在滚回事务后，MySQL服务器从binlog剪切回滚的 InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等，并使从服务器保持与主服务器保持同步(不接收回滚的语句)。
auto_increment_offset和auto_increment_increment
auto_increment_increment和auto_increment_offset用于主－主服务器(master-to-master)复制，并可以用来控制AUTO_INCREMENT列的操作。两个变量均可以设置为全局或局部变量，并且假定每个值都可以为1到65,535之间的整数值。将其中一个变量设置为0会使该变量为1。
这两个变量影响AUTO_INCREMENT列的方式：auto_increment_increment控制列中的值的增量值,auto_increment_offset确定AUTO_INCREMENT列值的起点。
如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值被忽略。例如：表内已有一些数据，就会用现在已有的最大的自增值做为初始值。
二进制日志清除
主同步服务器产生的二进制日志会占据大量的磁盘空间，应定期删除过期的bin-log。
A、通过PURGE MASTER LOGS删除
如果您有一个在用的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是停止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。
要清理日志，需按照以下步骤：
1、在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
2、使用SHOW MASTER LOGS获得主服务器上的一系列日志。
3、在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的，这是清单上的最后一个日志。
4、制作您将要删除的所有日志的备份。(建议备份)
5、清理所有的日志，但是不包括目标日志。
PURGE 语法
PURGE {MASTER | BINARY} LOGS TO ‘log_name’
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’
用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。
BEFORE变量的date自变量可以为’YYYY-MM-DD hh:mm:ss’格式。MASTER和BINARY是同义词。
例如：
#删除binlog.000002之前的而不包含binlog.000002
mysql> PURGE MASTER LOGS TO 'binlog.000002';
#删除2011-05-28 1:35:00之前的
mysql> PURGE MASTER LOGS BEFORE '2011-05-28 1:35:00';
#清除3天前的binlog
mysql> PURGE MASTER LOGS BEFORE DATE_SUB(NOW( ), INTERVAL 3 DAY);
B、设置expire-logs-days参数
缺省expire-logs-days为30天。这里设为7天，可根据自己情况调整。
[mysqld]
expire-logs-days = 7
用于控制主、从服务器的SQL语句
A、用于控制主服务器的SQL语句
PURGE MASTER LOGS
用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。
RESET MASTER
可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个新的二进制日志文件。
SET SQL_LOG_BIN
如果客户端使用一个有SUPER权限的账户连接，则可以禁用或启用当前连接的二进制日志记录。如果客户端没有此权限，则语句被拒绝，并伴随有错误。
SHOW BINLOG EVENTS
用于在二进制日志中显示事件。如果您不指定’log_name’，则显示第一个二进制日志。
SHOW MASTER LOGS
用于列出服务器中的二进制日志文件。
SHOW MASTER STATUS
用于提供主服务器二进制日志文件的状态信息。
SHOW SLAVE HOSTS
用于显示当前使用主服务器注册的复制从属服务器的清单。
B、用于控制从服务器的SQL语句
CHANGE MASTER TO
可以更改从属服务器用于与主服务器进行连接和通讯的参数。
LOAD DATA FROM MASTER
用于对主服务器进行快照，并拷贝到从属服务器上。
LOAD TABLE tbl_name FROM MASTER
用于把表的拷贝从主服务器转移到从属服务器。
MASTER_POS_WAIT()
这实际上是一个函数，而不是一个语句。它被用于确认，从属服务器已读取并执行了到达主服务器二进制日志的给定位置。
RESET SLAVE
用于让从属服务器忘记其在主服务器的二进制日志中的复制位置。
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
从主服务器中跳过后面的n个事件。要复原由语句导致的复制中止，这是有用的。
SHOW SLAVE STATUS
用于提供有关从属服务器线程的关键参数的信息。
START SLAVE
用于启动从属服务器线程
STOP SLAVE
用于中止从属服务器线程
以上内容摘自MySQL官方手册，具体用法详见此处。
主从复制如何提高可靠性
主从单向复制，从服务器只是实时的保存了主服务器的一个副本。当主服务器发生故障时，可以切换到从服务器继续做查询，但不能更新。
如果采用双向复制，即两台mysql服务器即作为主服务器，又作为从服务器。那么两者都可以执行更新操作并能实现负载均衡，当一方出现故障时，另一方不受影响。但是，除非能保证任何更新操作顺序都是安全的，否则双向复制会导致失败。
为了更好的提高可靠性和可用性，需要当主服务器不可用时，令从服务器成为Master。原来的主服务器设定为Slave，并从新的Master上同步更新。
-----------------------------------------------------
主从延迟设置
MySQL 5.6以上支持延迟复制，可通过设置Slave上的MASTER TO MASTER_DELAY参数实现：
CHANGE MASTER TO MASTER_DELAY = N；
N为多少秒，设置从数据库延时N秒后，再与主数据库进行数据复制。
具体操作：
登录到Slave数据库服务器
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 600；
mysql>start slave;
mysql>show slave status\G;
查看SQL_Delay的值为600时表示设置成功。
注释：
SQL_Delay：一个非负整数，表示秒数，Slave滞后多少秒于master。
SQL_Remaining_Delay：当 Slave_SQL_Running_State 等待，直到MASTER_DELAY秒后，Master执行的事件。
此字段包含一个整数，表示大概有多少秒的延迟。在其它时候该字段是0。
参考：MySQL-5.6-replication-delayed
-----------------------------------------------------
-----------------------------------------------------
各版本 MySQL 并行复制的实现及优缺点
备库有两个线程与复制相关：io_thread 负责从主库拿binlog并写到relaylog， sql_thread 负责读relaylog并执行。
多线程的思路就是把sql_thread 变成分发线程，然后由一组worker_thread来负责执行。几乎所有的并行复制都是这个思路，有不同的，便是sql_thread 的分发策略。而这些策略里面又分成两类：利用传统binlog格式、修改binlog。
使用传统的binlog格式的几类，由于binlog里面的信息就那些，因此只能按照粒度来分，也就是：按库、按表、按行。另外有两个策略是修改了binlog格式的，在binlog里面增加了别的信息，用于体现提交分组。下面我们分别介绍几个并行复制的实现。
5.5
MySQL官方5.5是不支持并行复制的。但是在阿里的业务需要并行复制的年份，还没有官方版本支持，只好自己实现。而且从兼容性角度说，不修改binlog格式，所以采用的是利用传统binlog格式的改造。
阿里的版本支持两种分发策略：按表和按行。
前情说明，由于MySQLbinlog日志还有用于别的系统的要求，因此阿里的binlog格式都是row----这也给并行复制的实现减少了难度。
按表分发策略：row格式的binlog，每个DML前面都是有Table_map event的。因此很容易拿到库名/表名。一个简单的思路是，不同表的更新之间是不需要严格按照顺序的。
因此按照表名hash，hash key是 库名+表名，相同的表的更新放到同一个worker上。这样就保证同一个表的更新顺序，跟主库上是一样的。
应用场景：对于多表更新的场景效果特别好。缺点是反之的，若是热点表更新，则本策略无效。而且由于hash表的维护，性能反而下降。
按行分发策略：row格式的binlog中，也不难拿到主键ID. 有同学说如果没有主键怎么办，答案是"起开，现在谁还没主键"。好吧，正经答案是没有主键就不支持这个策略。
同样的，我们认为不同行的更新，可以无序并发的。只要保证同一行的数据更新，在备库上的顺序与主库上的相同即可。因此按照主键id hash，所以这个hash key更长，必须是 库名+表名+主键id。相同行的更新放到同一个worker上。
需要注意的是，上面的描述看上去都是对单个event的操作，实际上并不能！因为备库可能接受读，因此事务的原子性是要保证的，也就是说，对于涉及多个更新操作的事务，每次用于决策的不是一个hash key，而是一组。
应用场景：热点表更新。缺点，hash key计算冲突的代价大。尤其是大事务，计算hash key的cpu消耗大，而且耗内存。这需要业务DBA做判断得失。
5.6
官方的5.6支持的是按库分发。有了上面的背景，大家就知道，这个特性出来以后，在国内并没有什么反响；但是这个策略也要说也是有优点的：
1、对于可以按表分发的场景，可以通过将表迁到不同的库，来应用此策略，有可操作性。
2、速度更快，因为hash key就一个库名。
3、不要求binlog格式，大家知道不论是row还是statement格式，都是能够轻松获取库名的。
所以并不是完全没有用的。还是习惯问题。
MariaDB
MariaDB的并行复制策略看上去有好几个选项，然而生产上可用的也就是默认值的 CONSERVATIVE。
由于MaraiaDB支持多主复制，一个domain_id字段是用来标示事务来源的。如果来自于不同的主，自然可以并行这个其实也是通用概念，还得业务DBA自己判断。对于同一个主库来的binlog，用commit_id 来决定分组。
想法是这样的：在主库上同时提交的事务设置成相同的commit_id。在备库上apply时，相同的commit_id可以并行执行，因为这意味着这些事务之间是没有行冲突的否则不可能同时提交。这个思路跟最初从单线程改成多线程一样，个人认为是划时代的。但是也并没有解决了所有的问题。这个策略最怕的是，拖后腿事务。
设想一下这个场景，假设某个DB里面正在作大量小更新事务比如每个事务更新一行，这样在备库就并行得很欢乐。然后突然在同一个实例，另外一个库下，或者同一个库的另外一个跟目前的更新无关的表，突然有一个delte操作删除了10w行。
delete事务在提交的时候，跟当时一起提交的事务都算同一个commit_id。假设为N，之后的小事务更新提交组commit_id为N+1。
到备库apply时，就会发现N这个组里面，其他小事务都执行完了，线程进入空闲状态，但是不能继续执行N+1这个commit_id的事务，因为N里面还有一个大事务没有执行完成，这个我们认为是拖后腿的。而基于传统binlog格式的上面三个策略，反而没有这个问题。只要是策略上能够判断不冲突，大事务自己有个线程跑，其他事务继续并行。
5.7
MySQL官方5.7版本也是及时跟进，先引入了上述MariaDB的策略。当然从版权安全上，oracle是不会允许直接port代码的。实际上按组直接分段这个策略略显粗暴。实际上事务提交并不是一个点，而是一个阶段。至少我们可以分成：准备提交、提交中、提交完成。这三个阶段都是在事务已经完成了主要操作逻辑，进入commit状态了。
同时进入“提交中”状态的算同一个commit_id. 但是实际上，在任意时刻，处于”准备提交”的事务，与“提交中”的事务，也是可以并行的。但是明显他们会被分成两个不同的commit_id。这意味着这个策略还有提升并发度的空间。
小结
按粒度区分的三个策略，粒度从粗到细是按库、按表、按行。
这三个的对比中，并行度越来越大，额外损耗也是。无关大事务不会影响并发度。
按照commit_id 的两个策略，适用范围更广，额外消耗也低。
5.7的改进策略并发性更优，但出现大事务会拖后腿。
另外很重要的一点，5.7的策略目的是“模拟主库并发”，所以对于主库单线程更新是无加速作用的。而基于冲突的前三个策略，若满足并发条件，会出现备库比主库执行速度快的情况。这种需求在搭备库或者延迟复制的场景中可能触发。
Replicating a master database mydatabase to slave servers, this is compatible with both InnoDB and MyISAM.
mysql--主多从负载均衡架构示意图
一、mysql 主从服务的配置
A服务器： 192.168.2.124 主服务器master
B服务器： 192.168.2.125 副服务器slave
A: 主服务器设置(主服务器设置)
1. #mysql -u root -p
Mysql>grant replication slave,reload,super on *.* To back@192.168.2.125 identified by ‘123’ ;
上面是Master开放一个账号back密码'123'给IP:192.168.2.125有档案处理的权限 ,查看刚才开放的账号
#mysql mysql
Mysql> select * from user; 可以查到用户的信息
2. 关闭数据库
#mysqladmin -u root -p shutdown
3. 同步mysql主和从的数据库.把主服务器下面的数据拷贝到从服务器下,我是使用源码编译的mysql.数据库在/usr/mysql目录下,数据就在/usr/mysql/var下,就是把主服务器/usr/mysql/var下的所有数据拷贝到从服务器的/usr/mysql/var里
#scp -r /usr/mysql/var/* root@192.168.2.125:/usr/mysql/var/
上面是把主服务器的数据拷贝到从的服务器中
4.修改主服务器的配置文件/etc/my.cnf
确认master上的 `my.cnf` 文件 [mysqld] 区间有 log-bin 选项。这个区间还必须有 server-id=master_id 选项，的值必须是 1 到 2^32-1 之间的正整数。在此设置为：
[mysqld]
log-bin
server-id=1
启动主服务器的mysql
5.查看主服务器的同步配置情况,如下图所示：
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| darkstar-bin.000024 | 316 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
可做总结：如果数据量大的话不太适合使用mysqldump(慢)，如果是myisam表的话，加上--lock-all-tables参数，如果是innodb表的话，加上--single-transaction参数。
而应该采用拷贝文件的方式，请按如下操作步骤：
先在主服务器上锁定所有的表，以免在复制过程中数据发生变化：
mysql> flush tables with read lock;
然后在主服务器上查询当前二进制文件的文件名及偏移位置：
mysql > show master status;
然后停止主服务器上的MySQL服务：
shell> mysqladmin -u root shutdown
注意：如果仅是MyISAM的话，可以不停止MySQL服务，但要在复制数据文件的过程中保持只读锁，如果是InnoDB的话，必须停止MySQL服务。
再拷贝数据文件：
shell> tar -cvf /tmp/mysql-snapshot.tar .
拷贝完别忘了启动主服务上的MySQL服务了。 然后把数据文件应用到从服务器上，再次启动slave的时候使用，记得启动时加上skip-slave-start选项，使之不会立刻去连接master，再在从服务器上设置相关的二进制日志信息。
B: 从服务器设置(从服务器设置)
1.关闭从服务器数据库
#mysqladmin -u root -p shutdown
2.修改从服务器的/etc/my.cnf在my.cnf文件中增加如下选项：
[mysqld]
server-id=slave_id # slave_id 的值和 master_id 类似，是 1 到 2^32-1 之间的正整数。另外，这个ID必须不能和master的ID一样。
在此设置为：
[mysqld]
server-id=2
注意：由于设置了slave的配置信息，mysql在数据库目录下生成master.info 所以如有要修改相关slave的配置要先删除该文件。否则修改的配置不能生效.此文件在/usr/mysql/var/下
3.启动从数据库服务
# mysqld_safe &
4.在slave上执行如下语句，把各个选项的值替换成真实值：
mysql> CHANGE MASTER TO
MASTER_HOST='master_host_name', //主服务器的IP地址
MASTER_USER='replication_user_name', //同步数据库的用户
MASTER_PASSWORD='replication_password', ////同步数据库的密码
MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名
MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置
recorded_log_file_name和recorded_log_position是在主服务器里面查看的,在192.168.2.124机器上查看主服务器的同步配置,在192.168.2.124上:
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| darkstar-bin.000024 | 316 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
如上面显示: recorded_log_file_name就是上面的 File，recorded_log_position就是上面的 Position。
在从服务器192.168.2.125上执行sql语句如下:
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.2.124',
MASTER_USER='back',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='darkstar-bin.000024',
MASTER_LOG_POS=316;
5. 启动从数据库的slave
mysql> slave start;
总结如下：
启动从服务器上的复制线程：
mysql> start slave;
验证主从设置是否已经成功，可以输入如下命令：
mysql> show slave status\G
会得到类似下面的列表：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果这两个选项不全是Yes，那就说明你前面某个步骤配置错了。 应该保证从服务器上任何数据的修改都是通过从主服务器上复制操作获取的，换句话说，从服务器应该是只读的，如果不能保证这一点，则可能造成主从数据不一致。可以在从服务器的my.cnf里加入read-only参数来实现这一点，唯一需要注意的一点事read-only仅对没有super权限的用户有效。所以最好核对一下连接从服务器的用户，确保其没有super权限。
至此配制完成，相关配置文件选项描述如下：
binlog-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
binlog-ignore-db=不需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
C.常见错误解决办法
1、1062 Duplicate entry
从理想角度看，主从数据库应该无故障的运转下去，可以有时候还是会出现一些莫名其妙的问题，比如说即便从未在从服务器上手动更新过数据，但还是可能遇到“Error: 1062 Duplicate entry”错误，具体原因不详，可能是MySQL本身的问题。遇到这类问题的时候，从服务器会停止复制操作，我们只能手动解决问题，具体的操作步骤如下：
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
同样的操作可能需要进行多次，也可以设置自动处理此类操作，在从服务器的my.cnf里设置：
slave-skip-errors=1062
最后说一下日志的问题：时间长了，数据库服务器上的二进制文件会越来越多，清理是必要的，你可以设置自动清理，相关参数是expire_logs_days，也可以使用手动删除的方式，这里说的手动不是指rm，而是指PURGE BINARY LOGS，删除任何日志前，最好在所有的从服务器上通过show slave status命令确认一下相关日志是否已经无用。
2、[ERROR] Error in Log_event::read_log_event(): 'Event too big'
在使用主从复制的时候，出现的问题多半是和日志(主服务器的二进制日志，从服务器的延迟日志)相关的。比如说加入你遇到了上面的错误，你可以根据错误日志的信息在主从数据库服务器上分别执行：
mysqlbinlog 日志文件 > /dev/null
查看错误，如果没有错误，则不会有任何输出，反之会输出错误信息，如果确定了错误是出现在主服务器二进制日志上，可以跳过适当的位置，再在从服务器上重新设定LOG_POS，如果确定了错误是出现在从服务器延迟日志上，则可以删除从服务器的延迟日志(使用CHANGE TO MASTER的时候，除非设定了延迟日志信息，否则会自动删除延迟日志)，并在从服务器上重新设定LOG_POS。期间也可以考虑手动执行不能自动执行的SQL日志。
3、sync_binlog选项
如果版本允许最好在主服上打开sync_binlog选项
补充：有时候，从服务器延迟日志可能已经损坏，这时需要执行CHANGE MASTER TO设置新的日志文件信息，但是在从服务器上SHOW SLAVE STATUS会显示很多日志信息，他们的含义有所不同：
Master_Log_File:Read_Master_Log_Pos 是IO相关的日志信息
Relay_Master_Log_File:Exec_Master_Log_Pos 是SQL相关的日志信息
从服务器需要设置的是SQL相关的日志信息：
slave stop;
change master to master_log_file=’(binlog name in relay_master_log_file)’, master_log_pos=(exec_master_log_pos number);
slave start;
1) When you are using the master as a consistent snapshot, use SHOW MASTER STATUS to determine the position.
2) When you are using a slave as a consistent snapshot, use SHOW SLAVE STATUS and Exec_Master_Log_Pos.
4、主机名变更而引起警告信息
缺省情况下，从服务器会以主机名命名延迟日志，所以一旦你修改了从服务器的主机名就会造成问题，新版MySQL会提示你这个情况：
[Warning] Neither --relay-log nor --relay-log-index were used;
so replication may break when this MySQL server acts as a slave and has his hostname changed!!
Please use '--relay-log=name-relay-bin' to avoid this problem.
设定relay-log可以避免此类问题。
D. 双机互备模式
1. 如果在A加入slave设置，在B加入master设置，则可以做B->A的同步。在A的配置文件中 mysqld 配置项加入以下设置，vi /etc/my.cnf：
master-host=192.168.2.125
master-user=back
master-password=123
master-port=3306
2. #mysql -u root -p
Mysql>grant replication slave,reload,super on *.* To back@192.168.2.125 identified by‘123’;
在B的配置文件中 mysqld 配置项加入以下设置：/etc/my.cnf：
log-bin=
这样设置就能使其B数据库同步A数据库
二、mysql 数据同步相关说明
1.同步机制实现概述
MySQL同步机制基于master把所有对数据库的更新操作(更新、删除等)都记录在二进制日志里。因此，想要启用同步机制，在master就必须启用二进制日志。
每个slave接受来自master上在二进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。应该非常重要地意识到，二进制日志只是从启用二进制日志开始的时刻才记录更新操作的。所有的slave必须在启用二进制日志时把master上已经存在的数据拷贝过来。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的话，那么slave同步就会失败。
2. 同步实现细节
MySQL同步功能由3个线程(master上1个，slave上2个)来实现。执行 START SLAVE 语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志中的语句。master创建一个线程来把日志的内容发送到slave上。这个线程在master上执行 SHOW PROCESSLIST 语句后的结果中的 Binlog Dump 线程便是。slave上的I/O线程读取master的 Binlog Dump 线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是SQL线程，salve用它来读取中继日志，然后执行它们来更新数据。
如上所述，每个mster/slave上都有3个线程。每个master上有多个线程，它为每个slave连接都创建一个线程，每个slave只有I/O和SQL线程。
在MySQL 4.0.2以前，同步只需2个线程(master和slave各一个)。slave上的I/O和SQL线程合并成一个了，它不使用中继日志。
slave上使用2个线程的优点是，把读日志和执行分开成2个独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。例如，如果slave停止了一段时间，那么I/O线程可以在slave启动后很快地从master上读取全部日志，尽管SQL线程可能落后I/O线程好几的小时。如果slave在SQL线程没全部执行完就停止了，不过I/O线程却已经把所有的更新日志都读取并且保存在本地的中继日志中了，因此在slave再次启动后就会继续执行它们了。这就允许在master上清除二进制日志，因为slave已经无需去master读取更新日志了。在mysql中执行 SHOW PROCESSLIST 语句就会告诉我们所关心的master和slave上发生的情况，在master上，SHOW PROCESSLIST 的结果如下：
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 21
User: root
Host: localhost
db: mysql
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 25
User: back
Host: 192.168.2.125:34085
db: NULL
Command: Binlog Dump
Time: 746
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
2 rows in set (0.01 sec)
在这里，线程2是为一个slave连接创建的。结果表明所有未完成的更新日志已经都发送到slave了，master正等待新的更新日志发生。在slave上，SHOW PROCESSLIST 的结果如下：
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 70479
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 16971
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 8
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
3 rows in set (0.00 sec)
这表明线程2是I/O线程，它正连接到master上；线程3是SQL线程，它执行中继日志中的更新操作。现在，这2个线程都处于空闲状态，正等待新的更新日志。
注意：Time 字段的值告诉我们slave上的日志比master晚了多久。
3.Master 同步线程状态
以下列出了master的 Binlog Dump 线程 State 字段中最常见的几种状态。如果在master上没有 Binlog Dump 线程，那么同步就没有在运行。也就是说，没有slave连接上来。
Sending binlog event to slave
事件是由二进制日志构成，一个事件通常由更新语句加上其他信息。线程读取到一个事件并正发送到slave上。
Finished reading one binlog; switching to next binlog
读取完了一个二进制日志，正切换到下一个。
Has sent all binlog to slave; waiting for binlog to be updated
已经读取完全部未完成更新日志，并且全部都发送到slave了。它处于空闲状态，正等待在master上执行新的更新操作以在二进制日志中产生新的事件，然后读取它们。
Waiting to finalize termination
当前线程停止了，这个时间很短
4.Slave的I/O线程状态
以下列出了slave的I/O线程 State 字段中最常见的几种状态。从MySQL 4.1开始，这个状态在执行 SHOW SLAVE STATUS 语句结果的 Slave_IO_State 字段也会出现。这意味着可以只执行 SHOW SLAVE STATUS 语句就能了解到更多的信息。
Connecting to master
该线程证尝试连接到master上。
Checking master version
确定连接到master后出现的一个短暂的状态。
Registering slave on master
确定连接到master后出现的一个短暂的状态。
Requesting binlog dump
确定连接到master后出现的一个短暂的状态。该线程向master发送一个请求，告诉它要请求的二进制文件以及开始位置。
Waiting to reconnect after a failed binlog dump request
如果二进制日志转储(binary log dump)请求失败了(由于连接断开)，该线程在休眠时进入这个状态，并定期重连。重连的时间间隔由'--master-connect-retry'选项来指定。
Reconnecting after a failed binlog dump request
该线程正尝试重连到master。
Waiting for master to send event
已经连接到master，正等待它发送二进制日志。如果master闲置时，这个状态可能会持续较长时间，如果它等待超过 slave_read_timeout 秒，就会发生超时。这时，它就会考虑断开连接，然后尝试重连。
Queueing master event to the relay log
已经读取到一个事件，正把它拷贝到中继日志中以备SQL线程处理。
Waiting to reconnect after a failed master event read
读日志时发生错误(由于连接断开)。该线程在重连之前休眠 master-connect-retry 秒。
Reconnecting after a failed master event read
正尝试重连到master。当连接确定后，状态就变成 Waiting for master to send event。
Waiting for the slave SQL thread to free enough relay log space
relay_log_space_limit 的值非零，中继日志的大小总和超过这个值了。I/O线程等待SQL线程先处理中继日志然后删除它们以释放足够的空间。
Waiting for slave mutex on exit
当前线程停止了，这个时间很短。
5.Slave的SQL线程状态
以下列出了slave的SQL线程 State 字段中最常见的几种状态：
Reading event from the relay log
从中继日志里读到一个事件以备执行。
Has read all relay log; waiting for the slave I/O thread to update it
已经处理完中继日志中的全部事件了，正等待I/O线程写入更新的日志。
Waiting for slave mutex on exit
当前线程停止了，这个时间很短。
SQL线程的 State 字段有时候也可能是一个SQL语句。这意味着它从中继日志中读取到一个事件了，从中提取出SQL语句，并执行它。
简单表述其过程：
master
grant replication slave,reload,super on *.* to repl@'192.168.6.%' identified by 'repl_paswd';
将数据从主库上导出：
for i in cap freeoa groupoffice oa unixaid; do echo export db $i && mysqldump --databases $i --flush-logs --user=dba --verbose --password='password' --opt>$i.sql; done
在导入到从数据库中，当时此时的主库是不能被写的。
for i in $(ls); do echo import db $i && mysql -udba -ppassword<$i; done
在从服上定义那些库不被同步
binlog-ignore-db = mysql
binlog-ignore-db = groupoffice
配置参数说明
server-id
ID值唯一的标识了复制群集中的主从服务器，因此它们必须各不相同。master_id必须为1到23^2–1之间的一个正整数值，slave_id值必须为2到232–1之间的一个正整数值。
log-bin
表示打开binlog,打开该选项才可以通过I/O写到Slave的relay-log,也是可以进行replication的前提;
binlog-do-db
表示需要记录进制日志的数据库。如果有多个数据库可用逗号分隔，或者使用多个binlog-do-db选项
binlog-ignore-db
表示不需要记录二进制日志的数据库。如果有多个数据库可用逗号分隔，或者使用多个binlog-do-db选项
replicate-do-db
表示需要同步的数据库，如果有多个数据库可用逗号分隔，或者使用多个replicate-do-db选项
replicate-ignore-db=mysql
表示不需要同步的数据库，如果有多个数据库可用逗号分隔，或者使用多个replicate-ignore-db=mysql选项
log-slave-updates
配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步
slave-skip-errors
在复制过程，由于各种原因导致binlog中的sql出错，默认情况下，从库会停止复制，要用户介入。可以设置Slave-skip-errors来定义错误号，如果复制过程中遇到的错误号是定义的错误号，便可以跳过。如果从库是用来做备份，设置这个参数会存在数据不一致，不要使用。如果是分担主库的查询压力，可以考虑。
sync_binlog=1 or N
sync_binlog的默认值是0，这种模式下，MySQL不会同步到磁盘中去。这样的话，MySQL依赖操作系统来刷新二进制日志binary log，就像操作系统刷其他文件的机制一样。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量，使binlog在每N次binlog写入后与硬盘同步。当sync_binlog变量设置为1是最安全的，因为在crash崩溃的情况下，你的二进制日志binary log只有可能丢失最多一个语句或者一个事务。但是，这也是最慢的一种方式(除非磁盘有使用带蓄电池后备电源的缓存cache，使得同步到磁盘的操作非常快)。
即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表，MySQL服务器处理COMMIT语句，它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃，重启时，事务被InnoDB回滚，但仍然存在binlog中。可以用–innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释：在MySQL 5.1中不需要–innodb-safe-binlog；由于引入了XA事务支持，该选项作废了)，该选项可以提供更大程度的安全，使每个事务的 binlog(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步，该选项的效果是崩溃后重启时，在滚回事务后，MySQL服务器从binlog剪切回滚的 InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等，并使从服务器保持与主服务器保持同步(不接收回滚的语句)。
auto_increment_offset和auto_increment_increment
auto_increment_increment和auto_increment_offset用于主－主服务器(master-to-master)复制，并可以用来控制AUTO_INCREMENT列的操作。两个变量均可以设置为全局或局部变量，并且假定每个值都可以为1到65,535之间的整数值。将其中一个变量设置为0会使该变量为1。
这两个变量影响AUTO_INCREMENT列的方式：auto_increment_increment控制列中的值的增量值,auto_increment_offset确定AUTO_INCREMENT列值的起点。
如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值被忽略。例如：表内已有一些数据，就会用现在已有的最大的自增值做为初始值。
二进制日志清除
主同步服务器产生的二进制日志会占据大量的磁盘空间，应定期删除过期的bin-log。
A、通过PURGE MASTER LOGS删除
如果您有一个在用的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是停止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。
要清理日志，需按照以下步骤：
1、在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
2、使用SHOW MASTER LOGS获得主服务器上的一系列日志。
3、在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的，这是清单上的最后一个日志。
4、制作您将要删除的所有日志的备份。(建议备份)
5、清理所有的日志，但是不包括目标日志。
PURGE 语法
PURGE {MASTER | BINARY} LOGS TO ‘log_name’
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’
用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。
BEFORE变量的date自变量可以为’YYYY-MM-DD hh:mm:ss’格式。MASTER和BINARY是同义词。
例如：
#删除binlog.000002之前的而不包含binlog.000002
mysql> PURGE MASTER LOGS TO 'binlog.000002';
#删除2011-05-28 1:35:00之前的
mysql> PURGE MASTER LOGS BEFORE '2011-05-28 1:35:00';
#清除3天前的binlog
mysql> PURGE MASTER LOGS BEFORE DATE_SUB(NOW( ), INTERVAL 3 DAY);
B、设置expire-logs-days参数
缺省expire-logs-days为30天。这里设为7天，可根据自己情况调整。
[mysqld]
expire-logs-days = 7
用于控制主、从服务器的SQL语句
A、用于控制主服务器的SQL语句
PURGE MASTER LOGS
用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。
RESET MASTER
可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个新的二进制日志文件。
SET SQL_LOG_BIN
如果客户端使用一个有SUPER权限的账户连接，则可以禁用或启用当前连接的二进制日志记录。如果客户端没有此权限，则语句被拒绝，并伴随有错误。
SHOW BINLOG EVENTS
用于在二进制日志中显示事件。如果您不指定’log_name’，则显示第一个二进制日志。
SHOW MASTER LOGS
用于列出服务器中的二进制日志文件。
SHOW MASTER STATUS
用于提供主服务器二进制日志文件的状态信息。
SHOW SLAVE HOSTS
用于显示当前使用主服务器注册的复制从属服务器的清单。
B、用于控制从服务器的SQL语句
CHANGE MASTER TO
可以更改从属服务器用于与主服务器进行连接和通讯的参数。
LOAD DATA FROM MASTER
用于对主服务器进行快照，并拷贝到从属服务器上。
LOAD TABLE tbl_name FROM MASTER
用于把表的拷贝从主服务器转移到从属服务器。
MASTER_POS_WAIT()
这实际上是一个函数，而不是一个语句。它被用于确认，从属服务器已读取并执行了到达主服务器二进制日志的给定位置。
RESET SLAVE
用于让从属服务器忘记其在主服务器的二进制日志中的复制位置。
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
从主服务器中跳过后面的n个事件。要复原由语句导致的复制中止，这是有用的。
SHOW SLAVE STATUS
用于提供有关从属服务器线程的关键参数的信息。
START SLAVE
用于启动从属服务器线程
STOP SLAVE
用于中止从属服务器线程
以上内容摘自MySQL官方手册，具体用法详见此处。
主从复制如何提高可靠性
主从单向复制，从服务器只是实时的保存了主服务器的一个副本。当主服务器发生故障时，可以切换到从服务器继续做查询，但不能更新。
如果采用双向复制，即两台mysql服务器即作为主服务器，又作为从服务器。那么两者都可以执行更新操作并能实现负载均衡，当一方出现故障时，另一方不受影响。但是，除非能保证任何更新操作顺序都是安全的，否则双向复制会导致失败。
为了更好的提高可靠性和可用性，需要当主服务器不可用时，令从服务器成为Master。原来的主服务器设定为Slave，并从新的Master上同步更新。
-----------------------------------------------------
主从延迟设置
MySQL 5.6以上支持延迟复制，可通过设置Slave上的MASTER TO MASTER_DELAY参数实现：
CHANGE MASTER TO MASTER_DELAY = N；
N为多少秒，设置从数据库延时N秒后，再与主数据库进行数据复制。
具体操作：
登录到Slave数据库服务器
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 600；
mysql>start slave;
mysql>show slave status\G;
查看SQL_Delay的值为600时表示设置成功。
注释：
SQL_Delay：一个非负整数，表示秒数，Slave滞后多少秒于master。
SQL_Remaining_Delay：当 Slave_SQL_Running_State 等待，直到MASTER_DELAY秒后，Master执行的事件。
此字段包含一个整数，表示大概有多少秒的延迟。在其它时候该字段是0。
参考：MySQL-5.6-replication-delayed
-----------------------------------------------------
-----------------------------------------------------
各版本 MySQL 并行复制的实现及优缺点
备库有两个线程与复制相关：io_thread 负责从主库拿binlog并写到relaylog， sql_thread 负责读relaylog并执行。
多线程的思路就是把sql_thread 变成分发线程，然后由一组worker_thread来负责执行。几乎所有的并行复制都是这个思路，有不同的，便是sql_thread 的分发策略。而这些策略里面又分成两类：利用传统binlog格式、修改binlog。
使用传统的binlog格式的几类，由于binlog里面的信息就那些，因此只能按照粒度来分，也就是：按库、按表、按行。另外有两个策略是修改了binlog格式的，在binlog里面增加了别的信息，用于体现提交分组。下面我们分别介绍几个并行复制的实现。
5.5
MySQL官方5.5是不支持并行复制的。但是在阿里的业务需要并行复制的年份，还没有官方版本支持，只好自己实现。而且从兼容性角度说，不修改binlog格式，所以采用的是利用传统binlog格式的改造。
阿里的版本支持两种分发策略：按表和按行。
前情说明，由于MySQLbinlog日志还有用于别的系统的要求，因此阿里的binlog格式都是row----这也给并行复制的实现减少了难度。
按表分发策略：row格式的binlog，每个DML前面都是有Table_map event的。因此很容易拿到库名/表名。一个简单的思路是，不同表的更新之间是不需要严格按照顺序的。
因此按照表名hash，hash key是 库名+表名，相同的表的更新放到同一个worker上。这样就保证同一个表的更新顺序，跟主库上是一样的。
应用场景：对于多表更新的场景效果特别好。缺点是反之的，若是热点表更新，则本策略无效。而且由于hash表的维护，性能反而下降。
按行分发策略：row格式的binlog中，也不难拿到主键ID. 有同学说如果没有主键怎么办，答案是"起开，现在谁还没主键"。好吧，正经答案是没有主键就不支持这个策略。
同样的，我们认为不同行的更新，可以无序并发的。只要保证同一行的数据更新，在备库上的顺序与主库上的相同即可。因此按照主键id hash，所以这个hash key更长，必须是 库名+表名+主键id。相同行的更新放到同一个worker上。
需要注意的是，上面的描述看上去都是对单个event的操作，实际上并不能！因为备库可能接受读，因此事务的原子性是要保证的，也就是说，对于涉及多个更新操作的事务，每次用于决策的不是一个hash key，而是一组。
应用场景：热点表更新。缺点，hash key计算冲突的代价大。尤其是大事务，计算hash key的cpu消耗大，而且耗内存。这需要业务DBA做判断得失。
5.6
官方的5.6支持的是按库分发。有了上面的背景，大家就知道，这个特性出来以后，在国内并没有什么反响；但是这个策略也要说也是有优点的：
1、对于可以按表分发的场景，可以通过将表迁到不同的库，来应用此策略，有可操作性。
2、速度更快，因为hash key就一个库名。
3、不要求binlog格式，大家知道不论是row还是statement格式，都是能够轻松获取库名的。
所以并不是完全没有用的。还是习惯问题。
MariaDB
MariaDB的并行复制策略看上去有好几个选项，然而生产上可用的也就是默认值的 CONSERVATIVE。
由于MaraiaDB支持多主复制，一个domain_id字段是用来标示事务来源的。如果来自于不同的主，自然可以并行这个其实也是通用概念，还得业务DBA自己判断。对于同一个主库来的binlog，用commit_id 来决定分组。
想法是这样的：在主库上同时提交的事务设置成相同的commit_id。在备库上apply时，相同的commit_id可以并行执行，因为这意味着这些事务之间是没有行冲突的否则不可能同时提交。这个思路跟最初从单线程改成多线程一样，个人认为是划时代的。但是也并没有解决了所有的问题。这个策略最怕的是，拖后腿事务。
设想一下这个场景，假设某个DB里面正在作大量小更新事务比如每个事务更新一行，这样在备库就并行得很欢乐。然后突然在同一个实例，另外一个库下，或者同一个库的另外一个跟目前的更新无关的表，突然有一个delte操作删除了10w行。
delete事务在提交的时候，跟当时一起提交的事务都算同一个commit_id。假设为N，之后的小事务更新提交组commit_id为N+1。
到备库apply时，就会发现N这个组里面，其他小事务都执行完了，线程进入空闲状态，但是不能继续执行N+1这个commit_id的事务，因为N里面还有一个大事务没有执行完成，这个我们认为是拖后腿的。而基于传统binlog格式的上面三个策略，反而没有这个问题。只要是策略上能够判断不冲突，大事务自己有个线程跑，其他事务继续并行。
5.7
MySQL官方5.7版本也是及时跟进，先引入了上述MariaDB的策略。当然从版权安全上，oracle是不会允许直接port代码的。实际上按组直接分段这个策略略显粗暴。实际上事务提交并不是一个点，而是一个阶段。至少我们可以分成：准备提交、提交中、提交完成。这三个阶段都是在事务已经完成了主要操作逻辑，进入commit状态了。
同时进入“提交中”状态的算同一个commit_id. 但是实际上，在任意时刻，处于”准备提交”的事务，与“提交中”的事务，也是可以并行的。但是明显他们会被分成两个不同的commit_id。这意味着这个策略还有提升并发度的空间。
小结
按粒度区分的三个策略，粒度从粗到细是按库、按表、按行。
这三个的对比中，并行度越来越大，额外损耗也是。无关大事务不会影响并发度。
按照commit_id 的两个策略，适用范围更广，额外消耗也低。
5.7的改进策略并发性更优，但出现大事务会拖后腿。
另外很重要的一点，5.7的策略目的是“模拟主库并发”，所以对于主库单线程更新是无加速作用的。而基于冲突的前三个策略，若满足并发条件，会出现备库比主库执行速度快的情况。这种需求在搭备库或者延迟复制的场景中可能触发。