Mysql主从同步(1)-主从/主主环境部署梳理

主从数据完成同步的过程：
1）在Slave 服务器上执行sart slave命令开启主从复制开关，开始进行主从复制。
2）此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器，并请求从执行binlog日志文件的指定位置（日志文件名和位置就是在配置主从
  复制服务时执行change master命令指定的）之后开始发送binlog日志内容
3）Master服务器接收到来自Slave服务器的IO线程的请求后，其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog
  日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的IO线程。返回的信息中除了binlog中的下一个指定更新位置。
 
4）当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件
 （Mysql-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog
  日志的指定文件及位置开始读取新的binlog日志内容
 
5）Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句
  的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点
 
主从复制条件
1）开启Binlog功能
2）主库要建立账号
3）从库要配置master.info（CHANGE MASTER to...相当于配置密码文件和Master的相关信息）
4）start slave 开启复制功能
 
需要了解的：
1）3个线程，主库IO，从库IO和SQL及作用
2）master.info（从库）作用
3）relay-log 作用
4）异步复制
5）binlog作用（如果需要级联需要开启Binlog）
 
需要注意：
1）主从复制是异步的逻辑的SQL语句级的复制
2）复制时，主库有一个I/O线程，从库有两个线程，I/O和SQL线程
3）实现主从复制的必要条件是主库要开启记录binlog功能
4）作为复制的所有Mysql节点的server-id都不能相同
5）binlog文件只记录对数据库有更改的SQL语句（来自主库内容的变更），不记录任何查询（select，show）语句
...................................................................................................
彻底解除主从复制关系
1)stop slave;
2)reset slave; 或直接删除master.info和relay-log.info这两个文件；
3)修改my.cnf删除主从相关配置参数。

让slave不随MySQL自动启动
修改my.cnf 在[mysqld]中增加 skip-slave-start 选项。

做了MySQL主从复制以后，使用mysqldump对数据备份时，一定要注意按照如下方式：
mysqldump --master-data --single-transaction --user=username --password=password dbname> dumpfilename
这样就可以保留 file 和 position 的信息，在新搭建一个slave的时候，还原完数据库， file 和 position 的信息也随之更新，接着再start slave 就可以很迅速
的完成增量同步！

需要限定同步哪些数据库，有3个思路：
1）在执行grant授权的时候就限定数据库；
2）在主服务器上限定binlog_do_db = 数据库名；
3）主服务器上不限定数据库，在从服务器上限定replicate-do-db = 数据库名；

如果想实现 主-从（主）-从 这样的链条式结构，需要设置：
log-slave-updates      只有加上它，从前一台机器上同步过来的数据才能同步到下一台机器。
当然，二进制日志也是必须开启的：
log-bin=/opt/mysql/binlogs/bin-log
log-bin-index=/opt/mysql/binlogs/bin-log.index

还可以设置一个log保存周期：
expire_logs_days=14

mysql的安装可以参考：http://www.cnblogs.com/kevingrace/p/6109679.html
Centos6.8版本
master：182.148.15.238        
slave: 182.148.15.237   

注意下面几点：
1）要保证同步服务期间之间的网络联通。即能相互ping通，能使用对方授权信息连接到对方数据库（防火墙开放3306端口）。
2）关闭selinux。
3）同步前，双方数据库中需要同步的数据要保持一致。这样，同步环境实现后，再次更新的数据就会如期同步了。

为了测试效果，先在master机器上创建测试库
mysql> CREATE DATABASE huanqiu CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
   
mysql> use huanqiu;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.02 sec)
   
mysql> insert into huanqiu.haha values(1,"wangshibo"),(2,"guohuihui");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
   
mysql> select * from huanqiu.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | wangshibo |
|  2 | guohuihui |
+----+-----------+
2 rows in set (0.00 sec)
   
--------------------------------------------------------------------
温馨提示：
修改库或表的字符集
mysql> alter database huanqiu default character set utf8;    //修改huanqiu库的字符集
mysql> alter table huanqiu.haha default character set utf8;  //修改huanqiu.haha表的字符集
   
添加主键
mysql> Alter table huanqiu.haha add primary key(id);     //将huanqiu.haha表的id添加主键
mysql> Alter table huanqiu.haha change id id int(10) not null auto_increment;  //自增长属性
   
删除主键时要先删除自增长，再删除主键
mysql> Alter table huanqiu.haha change id id int(10);    //删除自增长
mysql> Alter table huanqiu.haha drop primary key;     //删除主建
--------------------------------------------------------------------
   
下面是master数据库上的操作：
1）设置master数据库的my.cnf文件（在[mysqld]配置区域添加下面内容）
[root@master ~]# vim /usr/local/mysql/my.cnf
.......
server-id=1        #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin    #开启bin-log，并指定文件目录和文件名前缀
binlog-do-db=huanqiu  #需要同步的数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。
binlog-ignore-db=mysql  #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1      ＃确保binlog日志写入后与硬盘同步
binlog_checksum = none  ＃跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed   ＃bin-log日志文件格式，设置为MIXED可以防止主键重复。
   
--------------------------------------------------------------------------------
温馨提示：在主服务器上最重要的二进制日志设置是sync_binlog，这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上，即使服务器崩溃也会把事件写入日志中。
sync_binlog这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于"sync_binlog"参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
    
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。
    
从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
--------------------------------------------------------------------------------
   
2）导出master数据库多余slave数据库中的数据，然后导入到slave数据库中。保证双方在同步环境实现前的数据一致。
  导出数据库之前先锁定数据库
  mysql> flush tables with read lock;    #数据库只读锁定命令，防止导出数据库的时候有数据写入。unlock tables命令解除锁定
   
  导出master数据库中多余的huanqiu库(master数据库的root用户登陆密码：123456)
  [root@master ~]# mysqldump -uroot huanqiu -p123456 >/opt/huanqiu.sql
  [root@master ~]# rsync  -e "ssh -p22" -avpgolr /opt/huanqiu.sql 182.148.15.237:/opt/   ＃将导出的sql文件上传到slave机器上
   
3）设置数据同步权限
  mysql> grant replication slave,replication client on *.* to slave@'182.148.15.237' identified by "slave@123";
  Query OK, 0 rows affected (0.02 sec)
  mysql> flush privileges;
  Query OK, 0 rows affected (0.00 sec)

--------------------------------------------------------------------------------
温馨提示：
权限查看方式
mysql> show grants;
mysql> show grants for slave@'182.148.1115.237';
--------------------------------------------------------------------------------

4）查看主服务器master状态(注意File与Position项，从服务器需要这两项参数)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      120 | huanqiu      | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
   
   
下面是slave数据库上的操作：
1）设置slave数据库的my.cnf配置文件
[root@master ~]# vim /usr/local/mysql/my.cnf
.......
server-id=2   #设置从服务器id，必须于主服务器不同
log-bin=mysql-bin   #启动MySQ二进制日志系统
replicate-do-db=huanqiu  #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
replicate-ignore-db=mysql  #不同步mysql系统数据库
slave-skip-errors = all   #跳过所有的错误错误，继续执行复制操作
   
-----------------------------------------------------------------------------------------------
温馨提示：
当只针对某些库的某张表进行同步时，如下，只同步huanqiu库的haha表和huanpc库的heihei表：
replicate-do-db = huanqiu
replicate-wild-do-table = huanqiu.haha       //当只同步几个或少数表时，可以这样设置。注意这要跟上面的库指定配合使用；
replicate-do-db = huanpc
replicate-wild-do-table = huanpc.heihei      //如果同步的库的表比较多时，就不能这样一一指定了，就把这个选项配置去掉，直接根据指定的库进行同步。
-----------------------------------------------------------------------------------------------
   
2）在slave数据库中导入从master传过来的数据。
mysql> CREATE DATABASE huanqiu CHARACTER SET utf8 COLLATE utf8_general_ci;   #先创建一个huanqiu空库，否则下面导入数据时会报错说此库不存在。
mysql> use huanqiu;
mysql> source /opt/huanqiu.sql;   ＃导入master中多余的数据。
.......
   
3）配置主从同步指令
mysql> stop slave;   ＃执行同步前，要先关闭slave
mysql> change  master to master_host='182.148.15.238',master_user='slave',master_password='slave@123',master_log_file='mysql-bin.000007',master_log_pos=120;
   
mysql> start slave;
mysql> show slave status \G;
.......
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.148.15.238
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 279
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: huanqiu
          Replicate_Ignore_DB: mysql
           .............
        Seconds_Behind_Master: 0
   
如上，当IO和SQL线程的状态均为Yes，则表示主从已实现同步了！
   
查看slave数据库中的数据情况
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| huanqiu            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
   
mysql> select * from huanqiu.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | wangshibo |
|  2 | guohuihui |
+----+-----------+
2 rows in set (0.00 sec)
   
下面测试下Mysql主从同步的效果
现在主数据库上写入新数据
mysql> unlock tables;    #解锁，否则新数据无法写入
mysql> insert into huanqiu.haha values(100,"anhui");
Query OK, 1 row affected (0.00 sec)
   
然后在slave数据库上查看，发现master上新写入的数据已经同步过来了
mysql> select * from huanqiu.haha;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | wangshibo |
|   2 | guohuihui |
| 100 | anhui     |
+-----+-----------+
3 rows in set (0.00 sec)
   
至此，主从同步环境已经实现！

根据上面的主从环境部署，master和slave已经实现同步，即在master上写入新数据，自动同步到slave。而从库只能读不能写，一旦从库有写入数据，就会造成主从数据不一致！
下面就说下Mysql主主复制环境，在slave上更新数据时，master也能自动同步过来。
---------------------------------------------------------------------------
温馨提示：
在做主主同步前，提醒下需要特别注意的一个问题：
主主复制和主从复制有一些区别，因为多主中都可以对服务器有写权限，所以设计到自增长重复问题，例如：
出现的问题（多主自增长ID重复）
1）首先在A和B两个库上创建test表结构;
2）停掉A，在B上对数据表test(存在自增长属性的ID字段)执行插入操作，返回插入ID为1;
3）然后停掉B，在A上对数据表test(存在自增长属性的ID字段)执行插入操作，返回的插入ID也是1;
4）然后 同时启动A,B，就会出现主键ID重复
 
解决方法：
只要保证两台服务器上的数据库里插入的自增长数据不同就可以了
如：A插入奇数ID，B插入偶数ID，当然如果服务器多的话，还可以自定义算法，只要不同就可以了
在下面例子中，在两台主主服务器上加入参数，以实现奇偶插入！
记住:在做主主同步时需要设置自增长的两个相关配置，如下：
auto_increment_offset       表示自增长字段从那个数开始，取值范围是1 .. 65535。这个就是序号。如果有n台mysql机器，则从第一台开始分为设1，2...n
auto_increment_increment    表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535。如果有n台mysql机器，这个值就设置为n。

在主主同步配置时，需要将两台服务器的：
auto_increment_increment     增长量都配置为2
auto_increment_offset        分别配置为1和2。这是序号，第一台从1开始，第二台就是2，以此类推.....
这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。（针对的是有自增长属性的字段）
---------------------------------------------------------------------------

现在记录下主主同步的操作过程：
1）在master上的my.cnf配置：
[root@master ~]# vim /usr/local/mysql/my.cnf
server-id = 1         
log-bin = mysql-bin   
binlog-ignore-db = mysql,information_schema 
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2     
auto-increment-offset = 1    
slave-skip-errors = all      
  
[root@master ~]# /etc/init.d/mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!

数据同步授权（iptables防火墙开启3306端口，要确保对方机器能使用下面权限连接到本机mysql）
mysql> grant replication slave,replication client on *.* to slave@'182.148.15.237' identified by "slave@123";
mysql> flush privileges;

最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁；锁住后，就不能往表里写数据，但是重启mysql服务后就会自动解锁！ 
mysql> FLUSH TABLES WITH READ LOCK;    //注意该参数设置后，如果自己同步对方数据，同步前一定要记得先解锁！
Query OK, 0 rows affected (0.00 sec)
  
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1970 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2）slave数据库上
[root@slave ~]# vim /usr/local/mysql/my.cnf
server-id = 2        
log-bin = mysql-bin   
binlog-ignore-db = mysql,information_schema 
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2     
auto-increment-offset = 2    
slave-skip-errors = all

[root@slave ~]# /etc/init.d/mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!

数据同步授权（iptables防火墙开启3306端口，要确保对方机器能使用下面权限连接到本机mysql）
同理，slave也要授权给master机器远程同步数据的权限
mysql> grant replication slave ,replication client on *.* to slave@'182.148.15.238' identified by "slave@123";   
mysql> flush privileges;

mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     4136 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3）执行主张同步操作
先在slave数据库上做同步master的设置。（确保slave上要同步的数据，提前在master上存在。最好双方数据保持一致）
mysql> unlock tables;     //先解锁，将对方数据同步到自己的数据库中
mysql> slave stop；
mysql> change master to master_host='182.148.15.238',master_user='slave',master_password='slave@123',master_log_file='master-bin.000001',master_log_pos=1970;

mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.148.15.238
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1970
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 750
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ..................

这样就实现了slave－>master的同步环境。


再在master数据库上做同步slave的设置。（确保slave上要同步的数据，提前在master上存在。最好双方数据保持一致）
mysql> unlock tables; 
mysql> slave stop；
mysql> change master to master_host='182.148.15.237',master_user='slave',master_password='slave@123',master_log_file='master-bin.000001',master_log_pos=4136;

mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.148.15.237
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4136
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 750
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ..................

这样就实现了master－>slave的同步环境。至此，主主双向同步环境已经实现！

在master上写入新数据
mysql> select * from huanqiu.haha;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | wangshibo |
|   2 | guohuihui |
| 100 | anhui     |
+-----+-----------+
3 rows in set (0.00 sec)

mysql> insert into huanqiu.haha values(15,"guocongcong");

在slave数据库中查看，发现master新写入的数据已经同步过来了
mysql> select * from huanqiu.haha;
+-----+------------+
| id  | name       |
+-----+------------+
|   1 | wangshibo  |
|   2 | guohuihui  |
|  15 | guocongcong|
| 100 | anhui      |
+-----+------------+
3 rows in set (0.00 sec)

在slave上删除数据
mysql> delete from huanqiu.haha where id=100;

在master数据库中查看
mysql> select * from huanqiu.haha;
+-----+------------+
| id  | name       |
+-----+------------+
|   1 | wangshibo  |
|   2 | guohuihui  |
|  15 | guocongcong|
+-----+------------+
3 rows in set (0.00 sec)