MySQL主从复制配置详述

一、mysql主从复制的基本原理：
我们这个实验演示的是mysql的异步复制，何谓异步复制，即一条语句开始，主服务器不会等待从服务器执行完，而是主服务器一执行完就开始下一条语句，这也是出于主服务器并发型的考虑。slave端会开启两个thread，即Slave I/O thread和Slave SQL thread，I/O thread负责从master的二进制日志中读取事件并将这些时间信息存放发到relay-log中，Slave SQL thread负责从relay-log日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。
所以说：主服务器上必须开启二进制日志，而从服务器上只需开启中继日志。

二、mysql支持的复制类型：
1. 基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。   
2. 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
3. 混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

三、步骤：

主mysql服务器的配置：
1. 开启二进制日志
2. 创建具有复制权限的用户
3. Server-id唯一
从mysql服务器的配置：
1. 开启中继日志
2. Server-id唯一
3. 设为只读模式

假设主服务器上的数据比较大，同步会比较慢，所以要先用mysqldump导出主服务器的数据，再导入到从服务器上，然后再开始同步，这样比较合理。

四、准备：
在主服务器上创建一张测试表
mysql> create database data;
Query OK, 1 row affected (0.03 sec)
mysql> select * from data.info;
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|  1 | zhang |  23 |
|  2 | li    |  42 |
|  3 | wang  |  34 |
|  4 | qian  |  25 |
|  5 | cheng |  29 |
+-----+-------+-----+
5 rows in set (0.00 sec)

五、开始配置

主服务器：
1. 开启二进制日志并指定server-id

[root@Oracle ~]# vim /etc/my.cnf   
[client]
user=root
password=12345
[mysqld]
log-bin=mysql-bin
server-id=1
#添加这几行，mysql5.6默认没有开启二进制日志文件

[root@oracle ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

2. 创建具有复制权限的用户
mysql> grant replication slave on *.* to [email protected] identified by '12345';
Query OK, 0 rows affected (0.04 sec)

从服务器：
1. 开启中继日志并指定server-id和只读
[root@node2 ~]# vim /etc/my.cnf
[client]
user=root
password=12345
[mysqld]
relay-log=mysql-relay-bin
server-id=2
read-only=on
[root@node2 ~]# service mysqld restart
Shutting down MySQL....                                    [确定]
Starting MySQL.                                            [确定]

同步：
1. 在主务器上进行逻辑备份
[root@oracle ~]# mysqldump --master-data=2 --databases data --lock-all-tables > /root/bak.sql
[root@oracle ~]# scp /root/bak.sql [email protected]:/root/

2. 在从服务器上执行该脚本
[root@node2 ~]# mysql < /root/bak.sql
[root@node2 ~]# mysql -e 'select * from data.info;'
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|  1 | zhang |  23 |
|  2 | li    |  42 |
|  3 | wang  |  34 |
|  4 | qian  |  25 |
|  5 | cheng |  29 |
+-----+-------+-----+
#主服务器上的data数据库都导入到从服务器了

3. 让从服务器成为slave
[root@node2 ~]# vim /root/bak.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;
mysql> change master to master_host='192.168.2.93',master_user='slave',master_password='12345',MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看日志：
2014-02-23 20:45:47 17372 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='slave', master_port= 3306, master_log_file='mysql-bin.000004', master_log_pos= 120, master_bind=''. New state master_host='192.168.2.93', master_port= 3306, master_log_file='mysql-bin.000004', master_log_pos= 120, master_bind=''.
2014-02-23 20:45:52 17372 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-02-23 20:45:52 17372 [Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin.000004' at position 120
2014-02-23 20:45:52 17372 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2014-02-23 20:45:52 17372 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000004' at position 120, relay log './mysql-relay-bin.000001' position: 4
#slave I/O thread和Slave SQL thread都已经启动

1234567891011121314 mysql> show slave status\G;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.93
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 2058
              Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000005
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4. 在主服务器上再插入2行数据
[root@oracle ~]# mysql -e 'select * from data.info;'
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|  1 | zhang |  23 |
|  2 | li    |  42 |
|  3 | wang  |  34 |
|  4 | qian  |  25 |
|  5 | cheng |  29 |
|  6 | six  |  33 |
|  7 | seven |  41 |
+-----+-------+-----+
#第6行和第7行是新插入的，本来是没有的

5. 在从服务器上查看
[root@node2 ~]# mysql -e 'select * from data.info;'
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|  1 | zhang |  23 |
|  2 | li    |  42 |
|  3 | wang  |  34 |
|  4 | qian  |  25 |
|  5 | cheng |  29 |
|  6 | six  |  33 |
|  7 | seven |  41 |
+-----+-------+-----+

这样就实现了主从同步了。