LinuxEye - Linux系统教程

LinuxEye - Linux系统教程

当前位置: 主页 > 数据库 >

MySQL主从复制全面解析及应用演练

时间:2013-09-20 15:51来源:pangge.blog.51cto.com 编辑:pangge 点击:
Mysql复制 Mysql内建的复制功能是构建大型、高性能应用程序的基础。Mysql支持单向、异步复制,复制过程中一台服务器充当主服务器,而另外一个或多个服务器充当从服务器。这不仅仅对
Mysql复制
Mysql内建的复制功能是构建大型、高性能应用程序的基础。Mysql支持单向、异步复制,复制过程中一台服务器充当主服务器,而另外一个或多个服务器充当从服务器。这不仅仅对高性能的程序有益,对其他任务也方便,比如远程共享数据、保持“热备”等等。

复制解决的基本问题是让一台服务器的数据和另外的服务器保持同步。主服务器和从服务器可以位于不同的网络拓扑中,还能对整台服务器、特定的数据库、甚至特定的表进行复制。

在进入详细设置复制之前,先看看它实际是如何复制数据的。总体来说,复制有3个步骤:
1、主服务器把数据更改记录到二进制日志中。(这叫做二进制日志事件)
2、从服务器把主服务器的二进制日志拷贝到自己的中继日志中。
3、从服务器重放中继日志中的事件,把更改应用到自己的数据上。

这只是概述,每一个步骤都很复杂。下图更清晰描述了复制的过程。

第一步是在主服务器上记录二进制日志。在每个更新数据的事务完成之前,主服务器都会将数据更改记录到二进制日志中。即使事务在执行期间是交错的,mysql也会串行地将事务写入到二进制日志中。在把事件写入二进制日志之后,主服务器告诉存储引擎提交事务。

第二步是从服务器把主服务器的二进制日志拷贝到自己的硬盘上,进入所谓的“中继日志”中。首先,它启动一个工作线程,叫I/O线程,这个I/O线程开启一个普通的客户端连接,然后启动一个特殊的二进制日志转储进程(它没有相应的SQL命令)。这个转储进程从主服务器的二进制日志中读取数据。它不会对事件进行轮询。如果3跟上了主服务器,就会进入休眠状态并等待有新的事件发生时主服务器发出的信号。I/O线程把数据写入从服务器的中继日志中。

第三步SQL线程读取中继日志,并且重放其中的事件,然后更新从服务器的数据。由于这个线程能跟上I/O线程,中继日志通常在操作系统的缓存中,所以中继日志的开销很低。SQL线程执行事件也可以被写入从服务器自己的二进制日志中,它对于有些场景很实用。

本篇博文主要讲解Mysql的主从复制、半同步复制、基于SSL复制。

环境介绍:
系统:CentoS6.4-x86_64
主服务器:node1:172.16.18.7
从服务器:node2:172.16.18.9
Mysql版本:mysql-5.5.33

主从复制:

思路解析:
主服务器:创建具有复制权限的用户账号;设置server-id;启动二进制日志。
从服务器:启动中继日志;设置server-id;启动复制线程。

过程解析:
1、创建主从服务器并实现双机互信:
主服务器:
[root@node1~]# ssh-keygen -t rsa -P ''
[root@node1~]##ssh-copy-id -i .ssh/id_rsa.pub root@172.16.18.9
从服务器:
[root@node2~]# ssh-keygen -t rsa -P ''
[root@node2~]##ssh-copy-id -i .ssh/id_rsa.pub root@172.16.18.7
2、编辑主从服务器的hosts文件:
[root@node1~]# vim /etc/hosts
#####添加如下两项:###############
172.16.18.7node1.magedu.com  node1
172.16.18.9node2.magedu.com node2
#####保存并复制至node2上:########
[root@node1~]# scp /etc/hosts 172.16.18.9:/etc
3、查看时间并同步时间:
[root@node1 ~]#date;ssh node2 'date'
注意:必须保证时间一致,这样主从复制才不会实现时间间隔。

4、安装Mysql:(主从服务器)
这里不在详细介绍了,请参考《lnmp一键安装包

5、配置主服务器:
(1)编辑配置文件:
[root@node1 ~]# vim /etc/my.cnf
server-id       = 1        #60行标识自己的ID号
log-bin=mysql-bin          #52行二进制日志,默认是启动的
binlog_format=mixed        #55行使用混合模式
(2)建立复制账号权限:
[root@node1 ~]# mysql
mysql>GRANT REPLICATIONCLIENT,REPLICATION SLAVE ON *.* TO rpuser@'172.16.%.%' IDENTIFIED BY 'rpuser';
mysql> FLUSHPRIVILEGES;                  #刷新权限
6、配置从服务器:
(1)编辑配置文件:
[root@node2 ~]# vim /etc/my.cnf
server-id       = 20        #60行标识自己的ID号,和主服务器决不能相同
#log-bin=mysql-bin           #52行关闭二进制日志
#binlog_format=mixed         #55行关闭使用混合模式
#####设置从服务器为只读模式######
read-only=1       #注意从服务器不可有写模式
#####添加中继日志################
relay-log=/mydata/data/relay-mysql      #中继日志
relay-log-index=relay-mysql.index       #中继日志索引文件
【relay-log日志记录的是在复制过程中,从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件,然后SQL线程会读取relay-log日志的内容并应用到从服务器。】

(2)、登录mysql查看线程:
查看启动线程
mysql> SHOWPROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User |Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  1 | root | localhost | NULL | Query   |    0| NULL  | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+-------+------------------+
根据结果显示:此时还未启动任何线程。

(3)、配置连接服务器:
命令参数解析:
mysql>helpCHANGE MASTER TO
    MASTER_BIND = 'interface_name'       #将控制绑定在那个接口上
  | MASTER_HOST = 'host_name'            #主服务器地址
  | MASTER_USER = 'user_name'            #主服务器用户(刚才创建的)
  | MASTER_PASSWORD = 'password'         #主服务器密码
  | MASTER_PORT = port_num               #工作端口
  | 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)     #将某些ID号忽略掉
配置从服务器连接主服务器:
mysql> CHANGEMASTER TO MASTER_HOST='172.16.18.7',
    -> MASTER_USER='rpuser',
    -> MASTER_PASSWORD='rpuser';
(4)启动从服务器线程:
mysql> START SLAVE;
(5)查看从服务器工作状态
mysql> SHOWSLAVE STATUS\G
***************************1. row ***************************
               Slave_IO_State: Waiting formaster to send event       #I/O状态;接受主服务器发送状态
                  Master_Host: 172.16.18.7           #主服务器用户账号
                  Master_User: rpuser                #用户
                  Master_Port: 3306                  #默认监听端口
                Connect_Retry: 60                    #重试时间间隔
              Master_Log_File: mysql-bin.000002   #读取的二进制日志文件
          Read_Master_Log_Pos: 107                   #事件位置
               Relay_Log_File:relay-mysql.000003 #当前读取的中继日志文件
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             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: 107
              Relay_Log_Space: 966
              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
7、测试
主服务器:
mysql> CREATE DATABASEyong;
Query OK, 1 row affected (0.16sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| yong               |
+--------------------+
5 rows inset (0.03 sec)
从服务器:
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| yong               |
+--------------------+
5 rows inset (0.16 sec)
这样主从复制功能就实现了。

半同步复制:
半同步复制:就是让从服务器从指定位置进行同步数据,而不是从头开始同步数据。
为了实现效果,这里将从服务器mysql的原有数据清除,其他配置不变:
[root@node2 ~]# service mysqld stop
Shutting down MySQL.                                       [  OK  ]
[root@node2 ~]# rm -rf /mydata/data/
[root@node2 ~]# cd /usr/local/mysql
[root@node2 mysql]# ./scripts/mysql_install_db --user=mysql--datadir=/mydata/data/
[root@node2 mysql]# service mysqld start
清理完成启动成功进行半同步复制:
思路:首先在主服务器上完全备份数据库,然后将数据导出在从服务器上应用即可。

1、主服务器数据库完全备份
[root@node1~]# mysqldump -uroot  --lock-all-tables--master-data=2 --events --all-databases > all.sql
2、备份的数据复制至从服务器上
[root@node1 ~]# scp all.sql node2:/tmp/
3、从服务器将数据导入mysql
mysql> source /tmp/all.sql;        导入数据
4、查看主服务器上二进制文件及事件位置
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      190 |              |                  |
+------------------+----------+--------------+------------------+
5、配置连接服务器
从服务器连接主服务器:
mysql> CHANGE MASTER TOMASTER_HOST='172.16.18.7',
    -> MASTER_USER='rpuser',
    -> MASTER_PASSWORD='rpuser',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    ->MASTER_LOG_POS=190;
Query OK,0 rows affected (0.16 sec)
6、启动从服务器
mysql>START SLAVE;
7、查看从服务器状态
mysql> SHOW SLAVE STATUS\G
*************************** 1.row ***************************
               Slave_IO_State: Waiting formaster to send event
                  Master_Host: 172.16.18.7
                  Master_User: rpuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002       #指定的二进制文件
          Read_Master_Log_Pos: 190                    #指定的事件位置
               Relay_Log_File:relay-mysql.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             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: 190
              Relay_Log_Space: 405
              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 inset (0.00 sec)
8、测试
主服务器:
mysql> CREATE DATABASEtestdb;
Query OK, 1 row affected (0.01sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
| yong               |
+--------------------+
6 rows inset (0.01 sec)
从服务器:
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
| yong               |
+--------------------+
6 rows inset (0.06 sec)
这样半同步主从复制就完成了!!

基于SSL的mysql主从复制
环境介绍:
Mysql版本:mysql-5.5.33
主服务器:node1:172.16.18.7 数据未改变
从服务器:node2:172.16.18.9 数据未改变

演示过程:
在主服务器上配置CA,然后分别为主从服务器颁发证书;

1、将主服务器做成CA服务器:
[root@node1 mysql]# cd /etc/pki/CA/
[root@node1 CA]# (umask 077;openssl genrsa -outprivate/cakey.pem 2048)
[root@node1 CA]# openssl req -x509 -new -key private/cakey.pem-out cacert.pem -days 3650
[root@node1 CA]# touch index.txt serial
[root@node1 CA]# echo 01 >serial
2、主服务器申请证书
[root@node1 mysql]#mkdir ssl
[root@node1mysql]# chown mysql.mysql ssl
[root@node1 mysql]#cd ssl
[root@node1ssl]# (umask 077;openssl genrsa -out master.key 1024)
[root@node1ssl]# openssl req -new -key master.key -out master.csr
[root@node1ssl]# openssl ca -in master.csr -out master.crt -days 365
[root@node1ssl]# chown -R mysql.mysql *
3、从服务器向主服务器申请证书
从服务器:
[root@node2 mysql]#mkdir ssl
[root@node2 mysql]#chown mysql:mysql ssl
[root@node2 ssl]#(umask 077;openssl genrsa -out slave.key 1024)
[root@node2ssl]# openssl req -new -key slave.key -out slave.csr -days 365
[root@node2ssl]# scp slave.csr node1:/tmp
主服务器:
[root@node1~]# cd /tmp/
[root@node1tmp]# openssl ca -in slave.csr -out slave.crt -days 365
[root@node1tmp]# scp slave.crt node2:/usr/local/mysql/ssl/
[root@node1tmp]# scp /etc/pki/CA/cacert.pem node2:/usr/local/mysql/ssl/
从服务器:
[root@node2ssl]# chown -R mysql.mysql *
注意:在无论是在Master服务器上还是在Slave服务器上一定要修改ssl目录及其文件的属主属主为mysql。

4、在主服务器上开启ssl功能:
[root@node1 ssl]#vim /etc/my.cnf
在mysqld字段中添加如下内容:
ssl
ssl-ca=/etc/pki/CA/cacert.pem
ssl-cert=/usr/local/mysql/ssl/master.crt
ssl-key=/usr/local/mysql/ssl/master.key
#####重启###########
[root@node1 ssl]# service mysqld restart
验证ssl是否开启成功:
[root@node1 ~]# mysql
mysql> SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| have_openssl  | YES                             |
| have_ssl      | YES                             |
| ssl_ca        | /etc/pki/CA/cacert.pem          |
| ssl_capath    |                                 |
| ssl_cert      | /usr/local/mysql/ssl/master.crt |
| ssl_cipher    |                                 |
| ssl_key       | /usr/local/mysql/ssl/master.key |
+---------------+---------------------------------+
7 rows inset (0.01 sec)
5、创建允许使用ssl连接的用户
mysql>help grant;   #查看权限的定义方法,关于ssl的定义方式。
mysql>GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO rpssl@172.16.18.9 IDENTIFIED BY 'redhat' REQUIRE ssl;   #这里的授权IP不可使用引号引起来,不然后期一定会报错。
mysql>FLUSH PRIVILEGES;
6、在从服务器上开启ssl功能
[root@node2~]# vim /etc/my.cnf
依然在mysqld字段中添加
innodb_file_per_table= 1       #实现单表存储,可忽略添加
relay-log=/mydata/data/relay-mysql   #中继日志
read-only=1                   #只读选项
ssl
#log-bin=mysql-bin           #注释使用二进制,可选择性
#binlog_format=mixed         #注释二进制类型,可选择性
######重启###########
[root@node2~]# service mysqld restart
验证ssl是否开启成功:
mysql> SHOW GLOBALVARIABLES LIKE '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES  |
| have_ssl      | YES  |
| ssl_ca        |      |
| ssl_capath    |      |
| ssl_cert      |      |
| ssl_cipher    |      |
| ssl_key       |      |
+---------------+-------+
7 rows inset (0.01 sec)
7、从服务器连接主服务器
mysql> change master tomaster_host='172.16.18.7',
    -> master_user='rpssl',
    -> master_password='redhat',
    -> master_log_file='mysql-bin.000004',
    -> master_ssl=1,
    ->master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',
    ->master_ssl_cert='/usr/local/mysql/ssl/slave.crt',
-> master_ssl_key='/usr/local/mysql/ssl/slave.key';
###########状态显示:#############
mysql> show slave status\G
*************************** 1.row ***************************
               Slave_IO_State:
                  Master_Host: 172.16.18.7
                  Master_User: rpssl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 4
               Relay_Log_File:relay-mysql.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 4
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File:/usr/local/mysql/ssl/cacert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert:/usr/local/mysql/ssl/slave.crt
            Master_SSL_Cipher:
               Master_SSL_Key:/usr/local/mysql/ssl/slave.key
        Seconds_Behind_Master: NULL
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: 0
8、启动从服务器线程:
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 172.16.18.7
                 Master_User: rpssl
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000004
         Read_Master_Log_Pos: 362
              Relay_Log_File: relay-mysql.000002
               Relay_Log_Pos: 508
       Relay_Master_Log_File: mysql-bin.000004
            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: 362
             Relay_Log_Space: 660
             Until_Condition: None
              Until_Log_File:
               Until_Log_Pos: 0
          Master_SSL_Allowed: Yes
          Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem
          Master_SSL_CA_Path:
              Master_SSL_Cert:/usr/local/mysql/ssl/slave.crt
           Master_SSL_Cipher:
              Master_SSL_Key: /usr/local/mysql/ssl/slave.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
9、测试:
[root@node2 ssl]#mysql --ssl-ca=/usr/local/mysql/ssl/cacert.pem  --ssl-cert=/usr/local/mysql/ssl/slave.crt--ssl-key=/usr/local/mysql/ssl/slave.key  -urpssl -h172.16.18.7 –predhat
mysql> \s
--------------
mysql  Ver 14.14 Distrib5.5.33, for linux2.6 (x86_64) using readline 5.1
Connection id:      3
Current database:
Current user:       rpssl@node2.magedu.com
SSL:            Cipher inuse is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.33-logMySQL Community Server (GPL)
Protocol version:   10
Connection:     172.16.18.7via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         29 min 24sec
Threads: 3  Questions:14  Slow queries: 0  Opens: 33 Flush tables: 1  Open tables:26  Queries per second avg: 0.007
--------------
至此,mysql基于ssl主从复制就完成了!!

转载请保留固定链接: https://linuxeye.com/database/1928.html

------分隔线----------------------------
标签:mysql主从复制
栏目列表
推荐内容