MySQL主从复制详解

MySQL复制原理

MySQL主库在事务提交时会把数据变更作为事件Events记录在Binlog中，主库上的sync_binlog参数控制Binlog日志刷新到磁盘。MySQL复制就是将Binlog日志传到从库上，在从库重做一遍，从而使得从库和主库保持数据的同步的机制。MySQL通过3个线程来实现这一机制，其中Binlog Dump线程运行在主库上，I/O线程和SQL线程运行在从库上。主从复制开始时，主库会推送Binlog中的事件到从库的Relay Log，之后从库根据Relay Log进行重做，通过逻辑复制来达到主从库的数据一致。

MySQL复制过程

主服务器DDL、DML操作记录到binlog——>start slave从库创建I/O线程连接主库请求同步数据——>主库随后创建Binlog Dump线程读取数据库事件binlog并发送给I/O线程——>I/O线程获取到事件数据后更新到从库的Relay Log中——>从库上的SQL线程读取Relay Log中更新的数据库事件并应用——>完成从库复制（是否删除relaylog），同时I/O线程将读取到的二进制日志当前位置信息写在master.info中,SQL线程将读取到的当前relay log位置信息写在realy.info文件中

MySQL链式复制

从一台主库同时向多台从库进行复制，从库同时也可以作为其他从库的主库，同时向其它从库进行复制。

案例演示：

配置为innodb共享表空间模式，修改配置文件后重启服务器。

修改主服务器数据，查看MySQL主从复制过程

[root@test~]# vim /etc/my.cnf

innodb_file_per_table=0

#innodb_file_per_table=1

[root@wwwetc]# vim my.cnf

innodb_file_per_table=0  

#innodb_file_per_table=1

[root@test~]# service mysqld restart ;ssh www "service mysqldrestart"

停止 mysqld：                                             [确定]

正在启动 mysqld：                                         [确定]

停止 mysqld：[确定]

正在启动 mysqld：[确定]

1、master服务器

1）、直连服务器

[root@test~]# mysql -u root

mysql> show variables like'innodb_file_per_table';

+-----------------------+-------+

|Variable_name        | Value |

+-----------------------+-------+

|innodb_file_per_table | OFF  |

+-----------------------+-------+

<--可以看到，重启后已关闭独占表空间，配置为共享表空间

1 row inset (0.00 sec)

<--新建一个数据库ibdatax，并建表，观察主从复制

mysql>create database ibdatax;

Query OK,1 row affected (0.04 sec)

mysql>use ibdatax

Databasechanged

mysql>create table tb_dept( Id int primary key auto_increment,Namevarchar(18),description varchar(100));

Query OK,0 rows affected (0.02 sec)

mysql>insert into tb_dept value('1','hr','recruit employee');

Query OK,1 row affected (0.01 sec)

mysql>insert into tb_dept value('','SN','Service Network');

Query OK,1 row affected, 1 warning (0.00 sec)

mysql>select * from tb_dept;

+----+------+------------------+

| Id |Name | description     |

+----+------+------------------+

|  1| hr   |recruit employee |

|  2| SN   |Service Network  |

+----+------+------------------+

2 rows inset (0.00 sec)

mysql> show full processlist;

+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

| Id |User    | Host                | db     | Command    | Time | State                                                         | Info                 |

+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

|  2| repluser | 192.168.88.130:45975 | NULL   | Binlog Dump |  678 | Has sent all binlog to slave; waiting for binlog to beupdated | NULL                 |

|  3| root    | localhost           | ibdatax | Query      |   0 | NULL                                                          | show full processlist |

+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

查看进程状态，可以发现复制账户repluser状态为：Has sent all binlog to slave; waitingfor binlog to be updated

意即二进制日志文件以通过Binlog Dump线程传递给从服务器的I/O线程，等待数据库操作后更新binlog

2 rows inset (0.00 sec)

mysql>create table tb_emp(id int primary key auto_increment,Namevarchar(18),sex varchar(2),age int,address varchar(200),emailvarchar(100));

Query OK,0 rows affected (0.00 sec)

mysql>insert into tb_empvalue('1','Jones','Man','31','GZ','jone@163.com');

Query OK,1 row affected, 1 warning (0.00 sec)

mysql>insert into tb_empvalue('','Will','Man','28','GZ','will@126.com');

Query OK,1 row affected, 2 warnings (0.00 sec)

mysql>insert into tb_empvalue('3','Kin','Man','35','FS','kin@126.com');

Query OK,1 row affected, 1 warning (0.00 sec)

mysql>select * from tb_emp;

+----+-------+------+------+---------+--------------+

| id |Name  |sex  |age  |address | email       |

+----+-------+------+------+---------+--------------+

|  1| Jones | Ma  |   31 |GZ     | jone@163.com |

|  2| Will  |Ma  |   28 |GZ     | will@126.com |

|  3| Kin   |Ma  |   35 |FS     | kin@126.com  |

+----+-------+------+------+---------+--------------+

3 rows inset (0.00 sec)

mysql>SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+

|File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

|master-bin.000010 |    1740 |             |                 |

+-------------------+----------+--------------+------------------+

1 row inset (0.00 sec)

2）、查看数据存储目录

进入目录，可以看到，主服务器已经生产表文件。包括：

记录表结构文件：tb_dept.frm和tb_emp.frm

共享表空间文件：ibdata1，大小为10M的文件中。

二进制日志文件：master-bin.000010

二进制日志索引文件：master-bin.index

[root@wwwmysql]# pwd

/var/lib/mysql

[root@wwwmysql]# ls –lrt|tail –n 8

drwx------. 2 mysql mysql    4096 6月   818:36 mydb

-rw-rw----. 1 mysql mysql    2893 6月   822:21 master-bin.000009

srwxrwxrwx. 1 mysql mysql       0 6月   822:21 mysql.sock

-rw-rw----. 1 mysql mysql     200 6月   822:21 master-bin.index

drwx------. 2 mysql mysql    4096 6月   822:36 ibdatax

-rw-rw----. 1 mysql mysql    1740 6月   822:39 master-bin.000010

-rw-rw----. 1 mysql mysql  52428806月   822:39 ib_logfile0

-rw-rw----. 1 mysql mysql 10485760 6月   822:39 ibdata1

[root@wwwmysql]# cat master-bin.index

./master-bin.000001

…

./master-bin.000010

[root@wwwmysql]# cd ibdatax

[root@wwwibdatax]# ls -lrt

总用量 28

-rw-rw----. 1 mysql mysql   656月   822:23 db.opt

-rw-rw----. 1 mysql mysql 8630 6月   822:26 tb_dept.frm

-rw-rw----. 1 mysql mysql 8710 6月   822:36 tb_emp.frm

[root@wwwibdatax]# cat db.opt

default-character-set=latin1

default-collation=latin1_swedish_ci

[root@wwwmysql]# cd ..

[root@wwwmysql]# ls -lh|grep ibdata

-rw-rw----. 1 mysql mysql  10M6月   822:39 ibdata1

drwx------. 2 mysql mysql 4.0K 6月   822:36 ibdatax

2、slave从服务器

1）、直连服务器

mysql> show slave status\G

*************************** 1. row***************************

              Slave_IO_State: Waiting for master to send event

                 Master_Host: 192.168.88.131

                 Master_User: repluser

                 Master_Port: 3306

               Connect_Retry: 60

             Master_Log_File: master-bin.000010

         Read_Master_Log_Pos:1740 <--查看从服务器状态，已经同步完成

              Relay_Log_File: relay-log.000046

               Relay_Log_Pos: 1886

       Relay_Master_Log_File: master-bin.000010

            Slave_IO_Running: Yes

           Slave_SQL_Running: Yes

             Replicate_Do_DB:

         Replicate_Ignore_DB:

          Replicate_Do_Table:

      Replicate_Ignore_Table:

     Replicate_Wild_Do_Table:

 Replicate_Wild_Ignore_Table:

                  Last_Errno: 0

                  Last_Error:

                Skip_Counter: 0

         Exec_Master_Log_Pos: 1740

             Relay_Log_Space: 15846

             Until_Condition: None

              Until_Log_File:

               Until_Log_Pos: 0

          Master_SSL_Allowed: No

          Master_SSL_CA_File:

          Master_SSL_CA_Path:

             Master_SSL_Cert:

           Master_SSL_Cipher:

              Master_SSL_Key:

       Seconds_Behind_Master: 0 <--重点查看该数值，表示从服务器落后于主服务的时间

Master_SSL_Verify_Server_Cert: No

               Last_IO_Errno: 0

               Last_IO_Error:

              Last_SQL_Errno: 0

              Last_SQL_Error:

1 row inset (0.00 sec)

mysql> show full processlist;

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

| Id |User       | Host     | db   |Command | Time | State                                                                |Info                 |

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

|  2| system user |          | NULL | Connect | 1332 | Waiting formaster to send event                                      |NULL                 |

|  1| system user |          | NULL | Connect |  248 | Has read all relay log; waiting for the slave I/Othread to update it | NULL                 |

|  4| root       | localhost | NULL | Query  |   0 | NULL                                                                 | show full processlist |

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

3 rows inset (0.00 sec)

查看进程状态，可以发现

从服务器system user状态一为：Waiting for master to sendevent

意即等待主服务器传递事件，即等待主服务器传递binlog，然后经由I/O线程更新到relay log中；

另一个system user状态为：Has read all relay log; waiting forthe slave I/O thread to update it

意即二进制日志文件relay log中事件已重做完毕，等待I/O线程接收主服务器binlog后更新relay log

2）、查看数据存储目录

进入数据储存目录，可以看到，从服务器已经生产表文件。包括：

记录表结构文件：tb_dept.frm和tb_emp.frm

共享表空间文件：ibdata1，大小为10M的文件中。

二进制日志文件：relay-log.000046

记录主从最后一次同步信息的日志文件：master.info、relay-log.info

注意：该文件记录下一次复制开始位置，尤为重要

从服务器二进制日志索引文件：relay_log.index

[root@test~]# cd /var/lib/mysql/

[root@testmysql]# ls -lrt

总用量 20748

-rw-rw----1 mysql mysql     295 6月   817:35 relay-log.000040

-rw-rw----1 mysql mysql     149 6月   817:35 relay-log.000039

drwx------2 mysql mysql    4096 6月   818:36 mydb

-rw-rw----1 mysql mysql    1220 6月   821:10 relay-log.000041

-rw-rw----1 mysql mysql    2114 6月   822:20 relay-log.000042

-rw-rw----1 mysql mysql     244 6月   822:20 master-bin.000005

-rw-rw----1 mysql mysql     149 6月   822:21 relay-log.000043

srwxrwxrwx1 mysql mysql       0 6月   822:21 mysql.sock

-rw-rw----1 mysql mysql      1206月   822:21 master-bin.index

-rw-rw----1 mysql mysql     106 6月   822:21 master-bin.000006

-rw-rw----1 mysql mysql     779 6月   822:22 relay_log.index

-rw-rw----1 mysql mysql     295 6月   822:22 relay-log.000045

-rw-rw----1 mysql mysql      2956月   822:22 relay-log.000044

drwx------2 mysql mysql    4096 6月   822:36 ibdatax

-rw-rw----1 mysql mysql      47 6月   822:39 relay-log.info

-rw-rw----1 mysql mysql    1886 6月   822:39 relay-log.000046

-rw-rw----1 mysql mysql      77 6月  822:39 master.info

-rw-rw----1 mysql mysql  52428806月   822:39 ib_logfile0

-rw-rw----1 mysql mysql 10485760 6月   822:39 ibdata1

[root@testmysql]# cat master.info

15

master-bin.000010   <-- 主服务器binlog dump线程传递的给从服务器binlog文件

1740             <-- 主服务器Position

192.168.88.131    <--主服务地址

repluser          <--提供复制的账户

replpass          <--提供复制的账户密码

3306             <-- 主服务地址端口

60

0

0

[root@testmysql]# cat relay-log.info

./relay-log.000046     <--从服务器I/O线程更新event的从服务器relay-log文件

1886               <-- 从服务器Relay_Log_Pos

master-bin.000010    <--从服务器I/O线程传递的主服务器binlog文件

1740               <-- 主服务器Position

[root@test mysql]# cat relay_log.index

./relay-log.000006

./relay-log.000007

….

./relay-log.000051

./relay-log.000052

[root@testmysql]#

[root@testmysql]# cd ibdatax

[root@testibdatax]# ls -lrt

总用量 28

-rw-rw----1 mysql mysql   65 6月   822:23 db.opt

-rw-rw----1 mysql mysql 8630 6月   822:26 tb_dept.frm

-rw-rw----1 mysql mysql 8710 6月   822:36 tb_emp.frm