Mysql主从基本配置及基于ssl的配置详解

一、网络拓扑图

二、实验简介
    如图所示，本实验主要是实现两台Mysql服务器之间的主从复制，包括：设置主从服务器之间的半同步、mysql的复制过滤、和基于SSL的mysql主从复制功能，上图中
主服务器为：Master：172.16.76.1
从服务器为：Slave：172.16.77.1。

三、实验步骤
在进行主从服务器配置之前，应确保两台主机Mysql服务能正常运行，在此不再叙述。
1、Mysql的主从复制
（1）Master上的配置

mysql> show global variables like 'log_bin';//查看二进制是否开启,二进制日志一定要开启  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| log_bin       | ON    |  
+---------------+-------+  
#vim /etc/my.cnf   
[mysqld]  
    server-id = 1 //在此采用默认值  
    sync_binlog = 1//在事务提交时将为写进二进制日志中的语句，同步到二进制中  
#service mysqld restart  
mysql> grant replication slave,replication client on *.* to repluser@172.16.77.1 identified by 'redhat';//创建一个具有复制权限的用户  
mysql> flush privileges;  
mysql> show master status;    
+------------------+----------+--------------+------------------+  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+------------------+----------+--------------+------------------+  
| mysql-bin.000006 |      107 |              |                  |  
+------------------+----------+--------------+------------------+ 

（2）Slave上的配置

编辑主配置文件
#vim /etc/my.com //添加如下选项

[mysqld]  
    log-bin=mysql-bin //二进制可以开启也可关闭   
    relay-log=mysql-relay  //开启中继日志  
    skip_slave_start=1  //防止从服务器mysql重启时，start slave也自动运行  
    server-id = 21 //设置server-id  
    read_only=1 //只允许从服务器具有读操作 

重新启动mysql服务，在做如下操作，来验证配置结果

mysql> show global variables like '%server_id%';//验证server-id  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| server_id     | 21    |  
+---------------+-------+  
mysql>  show global variables like '%relay_log%';(验证中继日志是否开启)  
+-----------------------+----------------+  
| Variable_name         | Value          |  
+-----------------------+----------------+  
| max_relay_log_size    | 0              |  
| relay_log             | mysql-relay    |  
| relay_log_index       |                |  
| relay_log_info_file   | relay-log.info |  
| relay_log_purge       | ON             |  
| relay_log_recovery    | OFF            |  
| relay_log_space_limit | 0              |  
| sync_relay_log        | 0              |  
| sync_relay_log_info   | 0              |  
+-----------------------+----------------+ 

定义指向主服务器的信息如下：

mysql> change master to master_host='172.16.76.1',master_user='repluser',master_password='redhat',  
master_log_file='mysql-bin.000006',master_log_pos=107;//后两项一定要与Mater服务器上的相同 

 

查看从服务器的状态：

mysql> show slave status\G;  
*************************** 1. row ***************************  
               Slave_IO_State:   
                  Master_Host: 172.16.76.1  
                  Master_User: repluser  
                  Master_Port: 3306  
                Connect_Retry: 60  
              Master_Log_File: mysql-bin.000006  
          Read_Master_Log_Pos: 107  
               Relay_Log_File: mysql-relay.000001  
                Relay_Log_Pos: 4  
        Relay_Master_Log_File: mysql-bin.000006  
             Slave_IO_Running: No   //开机默认是No的，这就是skip_slave_start=1的效果  
            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: 107  
              Relay_Log_Space: 107  
              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: 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  
1 row in set (0.00 sec)  

启动Slave_IO和Slave_SQL的两个线程，在查看状态：

mysql> show slave status\G;  
*************************** 1. row ***************************  
               Slave_IO_State: Waiting for master to send event  
                  Master_Host: 172.16.76.1  
                  Master_User: repluser  
                  Master_Port: 3306  
                Connect_Retry: 60  
              Master_Log_File: mysql-bin.000006  
          Read_Master_Log_Pos: 194  
               Relay_Log_File: mysql-relay.000002  
                Relay_Log_Pos: 340  
        Relay_Master_Log_File: mysql-bin.000006  
             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: 194  
              Relay_Log_Space: 492  
              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.02 sec) 

这样一个简单的mysql的主从复制就构建好了。

2、设置Master-Slave之间的半同步：

在主服务器上，添加半同步模块：

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';    
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;    
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;   

在从服务器上，添加半同步模块：

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';    
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;    
mysql> STOP SLAVE;  
mysql> START SLAVE; 

验证半同步功能是否开启：

在Master服务器上，执行如下命令 mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';结果如下：

+--------------------------------------------+-------+  
| Variable_name                              | Value |  
+--------------------------------------------+-------+  
| Rpl_semi_sync_master_clients               | 1     |  为1则表示证明主从半同步复制连接成功
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |  
| Rpl_semi_sync_master_net_wait_time         | 0     |  
| Rpl_semi_sync_master_net_waits             | 0     |  
| Rpl_semi_sync_master_no_times              | 0     |  
| Rpl_semi_sync_master_no_tx                 | 0     |  
| Rpl_semi_sync_master_status                | ON    |  
| Rpl_semi_sync_master_timefunc_failures     | 0     |  
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |  
| Rpl_semi_sync_master_tx_wait_time          | 0     |  
| Rpl_semi_sync_master_tx_waits              | 0     |  
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |  
| Rpl_semi_sync_master_wait_sessions         | 0     |  
| Rpl_semi_sync_master_yes_tx                | 0     |  
+--------------------------------------------+-------+ 

3、基于ssl的mysql主从复制

首先在Master服务器上配置CA，然后分别给Master和Slave颁发证书，具体过程如下：

(1)将Master服务器做成CA服务器。

在Master服务器上
#cd /etc/pki/tls/  
#vim openssl.cnf //修改如下选项  
  dir             = /etc/pki/CA   
#cd /etc/pki/CA  
# mkdir certs newcerts crl  
#touch index.txt  
# (umask 077;openssl genrsa -out private/cakey.pem 2048)  
#openssl req -x509 -new -key private/cacert.key -out cacert.pem -days 3650  
#echo 01 >serial 

(2)Master服务器申请证书

在Master服务器上
#cd /usr/local/mysql/  
#mkdir ssl  
#chown mysql:mysql ssl  
#cd ssl  
#(umask 077;openssl genrsa -out master.key 1024)  
#openssl req -new -key master.key -out master.csr   
#openssl ca -in master.csr -out master.crt -days 365 

(3)Slave服务器向Master申请证书

在Slave服务器上：  
    #cd /usr/local/mysql/  
        #mkdir ssl  
    #chown mysql:mysql ssl  
    #cd ssl  
    #(umask 077;openssl genrsa -out slave.key 1024)  
    #openssl req -new -key slave.key -out slave.csr -days 365  
    #scp slave.csr node1:/tmp  
在Master服务器上：  
    #cd /tmp  
    #openssl ca -in slave.csr -out slave.crt -days 365  
    #scp slave.crt node2:/usr/local/mysql/ssl/  
    #scp /etc/pki/CA/cacert.pem node2:/usr/local/mysql/ssl/ 

注意在无论是在Master服务器上还是在Slave服务器上一定要修改ssl目录及其文件的属主属主为mysql。

(4)在Master上让Mysql开启ssl功能，过程如下：

#vim /etc/my.cnf    //在[mysqld]中添加如下内容  
[mysqld]  
    ssl  
    ssl-ca=/usr/local/mysql/ssl/cacert.pem  
    ssl-cert=/usr/local/mysql/ssl/master.crt  
    ssl-key=/usr/local/mysql/ssl/master.key  

重新启动服务，验证ssl功能是否开启：

mysql> show global variables like '%ssl%'; //验证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 |  
+---------------+---------------------------------+ 

创建仅允许使用ssl连接Mysqld的用户

mysql> grant replication client,replication slave on *.* to qingmu@'172.16.77.1'   
identified by 'redhat' require ssl;   
mysql> flush privileges;  

(5)在Slave服务器开启ssl功能

#vim /etc/my.cnf    //在[mysqld]中添加ssl  
[mysqld]  
    ssl  
保存退出后，重新启动mysql。  
验证ssl是否开启如下：  
mysql> show global variables like '%ssl%';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| have_openssl  | YES   |  
| have_ssl      | YES   |  
| ssl_ca        |       |  
| ssl_capath    |       |  
| ssl_cert      |       |  
| ssl_cipher    |       |  
| ssl_key       |       |  
+---------------+-------+ 

定义指向Master服务器的语句：

mysql> change master to \  
master_host='172.16.76.1',   
master_user='qingmu', \  
master_password='redhat', \  
master_log_file='mysql-bin.000007', \  
master_log_pos=365,master_ssl=1, \  
master_ssl_ca='/usr/local/mysql/ssl/cacert.pem', \  
master_ssl_capath='/usr/local/mysql/ssl/', \  
master_ssl_cert='/usr/local/mysql/ssl/slave.crt', \  
master_ssl_key='/usr/local/mysql/ssl/slave.key'; 

查看slave服务器的状态，验证是否开启成功。

mysql> show slave status\G;  
*************************** 1. row ***************************  
               Slave_IO_State:   
                  Master_Host: 172.16.76.1  
                  Master_User: qingmu  
                  Master_Port: 3306  
                Connect_Retry: 60  
              Master_Log_File: mysql-bin.000007  
          Read_Master_Log_Pos: 365  
               Relay_Log_File: mysql-relay.000001  
                Relay_Log_Pos: 4  
        Relay_Master_Log_File: mysql-bin.000007  
             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: 365  
              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: /usr/local/mysql/ssl/  
              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  
 
mysql> start slave;  
mysql> show slave status\G;  
*************************** 1. row ***************************  
               Slave_IO_State: Waiting for master to send event  
                  Master_Host: 172.16.76.1  
                  Master_User: qingmu  
                  Master_Port: 3306  
                Connect_Retry: 60  
              Master_Log_File: mysql-bin.000008  
          Read_Master_Log_Pos: 107  
               Relay_Log_File: mysql-relay.000003  
                Relay_Log_Pos: 253  
        Relay_Master_Log_File: mysql-bin.000008  
             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: 107  
              Relay_Log_Space: 551  
              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: /usr/local/mysql/ssl/  
              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  
 
 Slave_IO_Running: Yes ,Slave_SQL_Running: Yes, Master_SSL_Allowed: Yes(这三项为yes表示成功) 

（6）验证是否启用了ssl连接：

在Slave服务器上：

#/usr/local/mysql/bin/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   -uqingmu -h172.16.76.1 -predhat  
 
 mysql> \s  
--------------  
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.5.24, for linux2.6 (i686) using readline 5.1  
 
Connection id:      3  
Current database:     
Current user:       qingmu@node2  
SSL:            Cipher in use is DHE-RSA-AES256-SHA  
Current pager:      stdout  
Using outfile:      ''  
Using delimiter:    ;  
Server version:     5.5.24-log MySQL Community Server (GPL)  
Protocol version:   10  
Connection:     172.16.76.1 via TCP/IP  
Server characterset:    latin1  
Db     characterset:    latin1  
Client characterset:    utf8  
Conn.  characterset:    utf8  
TCP port:       3306  
Uptime:         11 min 26 sec  
 
Threads: 3  Questions: 10  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.01  
 
若SSL为：SSL:  Cipher in use is DHE-RSA-AES256-SHA 则表示成功  

这样基于ssl的mysql的主从复制就建立成功了。