MySQL 主从复制详解

一、MySQL复制原理
二：MySQL主从复制的实现
三、基于事件点的复制
四、半同步复制
五、基于ssl完成MySQL的主从复制

一、Mysql复制原理

对于主服务器来讲，会不停的吧当前数据库中产生的信息记录进二进制日志中，如果服务器端接收到客户端一个需要改变数据的语句之后，这个数据如果采用的是InnoDB的话，这些数据本身首先被记录进事务日志，这些会影响数据的语句会被记录进二进制日志中，而复制讲的是，另外一个从服务器上，被动的接受数据，本身并不接受客户端链接进来做任何写操作，复制是单向的，只能从主服务器到从服务器，因此，从服务器中的数据都是来自于主服务器，主服务是监听在3306端口上，从服务器启动了一个客户端进程，这个进程会向服务器点提供数据服务的端口发起请求，请求读取二进制日志中的事件，在主服务器点上，mysql主要提供认证服务，同时会启动一个sqldump线程，这个线程在接收到用户的请求后，并确保这个用户具有权限之后，会去读取二进制日志中的事件，如果客户端是第一次来请求，那么这个进程就读取日志中的第一个事件，读一个就发送给客户端一个，从服务的进程在接收到主服务器发行过来的数据之后，会保存在本地的中继日志，然后再启动一个具有特殊功能的线程，去中继之日中读取事件，读一行，在本地执行一下，以此类推，最终生成本地的数据库。mysql的复制时异步的，因此在接收到用户的请求之后，将结果返回给用户。并将信息存放在内存中，之后再记录进二进制文件中，这样会造成从服务器落后与主服务器，但是这样也是有好处的，如果你有一个错误操作，在这个时间内，立即去从服务器上备份数据就OK，这需要mysql管理员把握。

二：mysql主从复制的实现

1、思路：

主服务器：

创建有复制权限的用户账号

设置server-id

启用二进制日志

从服务器：

启用中继日志。（可选择关闭二进制日志）

设置server-id

启动复制线程

2、过程实现：

2.1测试环境：

虚拟主机版本：

CentOS6.4-i686

两个节点：

node1.limian.com 172.16.6.10

node2.limian.com 172.16.6.1

2.2在两个节点上同步时间

1 [root@node1 ~]# ntpdate 172.16.0.1

2.3把mysql源码复制到另一个节点

1 [root@node1 ~]# scp mysql-5.5.33-linux2.6-i686.tar.gz 172.16.6.10:/root/

2.4源码编译mysql

节点1

[root@node1 ~]# tar xf mysql-5.5.33-linux2.6-i686.tar.gz -C /usr/local/  //解压mysql
[root@node1 ~]# mkdir -pv /mydata/data  //创建数据目录
[root@node1 ~]# useradd -r -u 306 mysql  //创建用户
[root@node1 ~]# chown -R mysql.mysql /mydata/data/ //修改数据目录的属主属组
[root@node1 ~]# cd /usr/local/
[root@node1 local]# ln -sv mysql-5.5.33-linux2.6-i686 mysql  //创建连接
[root@node1 local]# chown -R root.mysql mysql/*  //改mysql目录下的属主属组
[root@node1 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/                //初始化mysql
[root@node1 mysql]# cp support-files/my-large.cnf /etc/my.cnf  //提供配置文件
[root@node1 mysql]# vim /etc/my.cnf
datadir = /mydata/data              //指定数据目录
innodb_file_per_table = 1            //开启单表一个空间
[root@node1 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql              //提供启动脚本
[root@node1 mysql]# chmod +x /etc/rc.d/init.d/mysqld  //赋予脚本执行权限
[root@node1 mysql]# vim /etc/profile.d/mysql.sh  //更改环境变量
export PATH=/usr/local/mysql/bin:$PATH
[root@node1 mysql]# . /etc/profile.d/mysql.sh  //重读下该文件
[root@node1 mysql]# chkconfig --add mysqld  //添加到服务列表
[root@node1 mysql]# service mysqld start      //启动mysql

2.5节点2同样执行上边操作

2.6配置主服务器

[root@node1~]# vim /etc/my.cnf
server-id      = 1            //设置ID
log-bin=mysql-bin              //启动二进制日志
binlog_format=mixed            //指定二进制日志的记录格式为混合模式
[root@node1 mysql]# mysql
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO reuser@'172.16.%.%' IDENTIFIED BY 'repass';  //创建具有复制权限的用户
mysql> FLUSH PRIVILEGES;        //重读授权表
mysql> show master status;    //查看二进制日志中事件的位置
+------------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      352 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

2.7配置从服务器:

[root@node2 ~]# vim /etc/my.cnf
添加以下两行
server-id      = 20                //指定serverID
relay-log = /mydata/data/relay-bin  //指定中继日志的位置
read-only = 1  //开启只读模式
[root@node2 ~]# service mysqld restart  //重启mysql
[root@node2 ~]# mysql
mysql> show slave status;            //查看从服务器的状态信息
Empty set (0.02 sec)              //结果显示没启动任何线程
mysql> show processlist;            //查看启动线程
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db  | Command | Time | State | Info            |
+----+------+-----------+------+---------+------+-------+------------------+
|  1 | root | localhost | NULL | Query  |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.03 sec)
结果显示没启动任何线程

2.8、配置从服务器连接主服务器

mysql> help change master to  //查看命令
option:
MASTER_BIND = 'interface_name'  //设置连接接口
  | MASTER_HOST = 'host_name'      //指定主节点名称
  | MASTER_USER = 'user_name'        //指定具有复制权限的用户（刚才创建的）
  | MASTER_PASSWORD = 'password'    //具有复制权限的用户的密码
  | MASTER_PORT = port_num        //主服务所监听的端口，默认为3306的话可以省略
  | MASTER_CONNECT_RETRY = interval //无法连接主服务器的时候可以重试连接多少次
  | MASTER_HEARTBEAT_PERIOD = interval  //多长时间探测一次主服务器是否在线
  | MASTER_LOG_FILE = 'master_log_name'    //从哪个二进制日志中读取事件
  | MASTER_LOG_POS = master_log_pos    //指定哪个二进制日志中事件的位置
  | RELAY_LOG_FILE = 'relay_log_name'    //读取哪个中继日志
  | RELAY_LOG_POS = relay_log_pos      //中继日志中的位置
  | MASTER_SSL = {0|1}                //是否启动ssl功能
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'
  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
  | IGNORE_SERVER_IDS = (server_id_list)

2.9配置从服务器连接主服务器

mysql> CHANGE MASTER TO MASTER_HOST='172.16.6.10', MASTER_USER='reuser', MASTER_PASSWORD='repass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;

2.10启动线程

1 mysql> start slave;

2.11查看从服务器工作状态

mysql> show slave status\G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event  //I/O状态
                  Master_Host: 172.16.6.10    //主服务器ip
                  Master_User: reuser          //具有复制权限的用户
                  Master_Port: 3306          //默认监听的端口
                Connect_Retry: 60            //重试时间
              Master_Log_File: mysql-bin.000003  //读取的二进制日志文件
          Read_Master_Log_Pos: 435              //二进制日志中的位置
              Relay_Log_File: relay-bin.000002    //当前读取的中继日志文件
                Relay_Log_Pos: 581              //中继日志中事件的位置
        Relay_Master_Log_File: mysql-bin.000003
            Slave_IO_Running: Yes              //I/O线程已启动
            Slave_SQL_Running: Yes                //SQL线程已启动
              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: 435
              Relay_Log_Space: 731
              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:
  Replicate_Ignore_Server_Ids:
            Master_Server_Id: 1
1 row in set (0.07 sec)

2.12测试

在主服务器上创建数据库

[root@node1 data]# mysql
mysql> create database mydb;
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mydb              |
| mysql              |
| performance_schema |
| test              |
+--------------------+
5 rows in set (0.07 sec)
mysql>

2.13在从服务器上查看是否复制创建的表

mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mydb              |
| mysql              |
| performance_schema |
| test              |
+--------------------+
5 rows in set (0.00 sec)
结果显示已经完成复制

推荐阅读：