基于ssl的mysql的主从复制及排错

原创

单人旅行 2012-08-07 15:54:48 ©著作权

文章标签 mysql ssl 主从复制 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者单人旅行的原创作品，谢绝转载，否则将追究法律责任

为了方便大家阅读，快速找到对自己有用的资料，先讲明我做此实验所用的硬件环境，和所使用的软件版本，同时中间也列出了试验中遇到的问题，及个人的解决方法，希望对大家有所帮助，同时也希望留下你的宝贵意见和建议。

实验环境：

linux-2.6.18-308.el5

mysql-5.5.24-linux2.6-i686.tar.gz 编译安装好的

实验拓扑图：

实验思路：

1、主从服务器的基本设置及主从复制，先确保简单的主从复制没有问题

2、准备证书和私钥

3、配置基于ssl的主从复制

具体操作步骤：

一、主从服务器的基本设置及简单的主从复制

1、master的配置：

mysql> grant replication slave,replication client on *.* to
 'slave'@'172.16.2.2' identified by 'redhat'; 授权用户 
mysql> flush privileges; 
mysql> show master status;  
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000008 |      353 |      这两个值需要记着  
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)

2、slave的配置：

# vim /etc/my.cnf  
    server-id       = 12     修改server-id 只要两个服务器不一样  
    relay-log=mysql-relay     开启中继日志  
# service mysqld restart    修改配置文件 需要重启服务  
mysql> change master to master_host='172.16.2.1',连入mysql 修改参数
        -> master_user='slave',                      
        -> master_password='redhat', 
        -> master_log_file='mysql-bin.000008',  
        -> master_log_pos=353;      这些参数都要和master上的对应  
    Query OK, 0 rows affected (0.27 sec)  
 
  注意：5.5以后必须使用命令 不可以写入配置文件。

注：此时如果出现一下类似错误，很可能就是你的主服务器版本高于从服务器，配置主从服务器，主服务器的版本不能高于从服务器的。

ERROR 1201 (HY000): Could not initialize master info structure;
 more error messages can be found in the MySQL error log

3、启动slave 前提必须是上面的命令执行成功

mysql> start slave;    启动slave  
    Query OK, 0 rows affected (0.07 sec)     
mysql> show slave status\G 
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                      Master_Host: 172.16.2.1 
                      Master_User: slave 
                      Master_Port: 3306 
                    Connect_Retry: 60 
                  Master_Log_File: mysql-bin.000008 
              Read_Master_Log_Pos: 353 
                   Relay_Log_File: mysql-relay.000002 
                    Relay_Log_Pos: 253 
            Relay_Master_Log_File: mysql-bin.000008 
                 Slave_IO_Running: Yes 
                Slave_SQL_Running: Yes 这两项都为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: 353 
                  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: 12 
    1 row in set (0.00 sec)

4、此时一个简单的主从复制就做好了，可以测试一下，在主服务器上创建数据库，到从服务器上查看。但是此刻如果你在从服务器上写入数据，也是可以的，但主服务器不会到从服务器上复制数据，这样就导致了主从的数据不一样，所以我们要在从服务器上修改一个参数，让从服务器只读不可以写。

mysql> show global variables like 'read_only';

| read_only | OFF | 默认是关闭的

mysql> set global read_only=1; 设置为只读。也可以写入配置文件中

建议：如果在现实环境中，很可能是主服务器运行很长时间了，才创建从服务器，此时开启从服务器就需要同步很长时间，我们可以先把主服务器备份一下，把数据导入到从服务器，在开启从服务器。

二、准备证书和私钥

1、在master上配置CA服务器

#vim /etc/pki/tls/openssl.cnf  
将  dir             = ../../CA 
修改为dir             = /etc/pki/CA 
#(umask 077;openssl genrsa 2048 > private/cakey.pem) 
#openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650 
#mkdir certs crl newcerts 
#touch index.txt 
#echo 01 > serial

2、为mysql准备私钥和证书

在主服务器上：

#mkdir /usr/local/mysql/ssl
#cd ssl/ 
#(umask 077;openssl genrsa 1024 > mysql.key) 
#openssl req -new -key mysql.key -out mysql.csr 
#openssl ca -in mysql.csr -out mysql.crt 
#cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/ 
#chown -R mysql.mysql ssl/       一定要记得修改属主和属组

在从服务器上：

#mkdir /usr/local/mysql/ssl
#chown -R mysql.mysql ssl/
#cd ssl/ 
#(umask 077;openssl genrsa 1024 > mysql.key) 
#openssl req -new -key mysql.key -out mysql.csr 
#scp ./mysql.csr node1:/root  把证书申请传到CA服务器上

在master为slave签署证书

#openssl ca -in mysql.csr -out mysql.crt 
#scp ./mysql.crt node2:/usr/local/mysql/ssl 
#cd /etc/pki/CA 
#scp ./cacert.pem node2:/usr/local/mysql/ssl

注： node1 ：172.16.2.1 node2: 172.16.2.2

到此证书和私钥我们都准备好了，为了下面的实验顺利，我们最好每做一步都验证一下，这样也好排错。

确保主从服务器上都有以下文件，及属主和属组都正确。

路径可以自己定义，只要和后面的保持一致就可以

[root@node1 ssl]# ll 
total 16 
-rw-r--r-- 1 mysql mysql 1468 Aug  7 10:23 cacert.pem 
-rw-r--r-- 1 mysql mysql 3682 Aug  7 10:23 mysql.crt 
-rw-r--r-- 1 mysql mysql  643 Aug  7 10:22 mysql.csr 
-rw------- 1 mysql mysql  887 Aug  7 10:21 mysql.key 
[root@node1 ssl]# pwd 
/usr/local/mysql/ssl

三、配置基于ssl 的主从复制

1、在主从服务器上都开启ssl功能：在主从服务器上都操作

mysql> show variables like '%ssl%'; 
+---------------+----------+ 
| Variable_name | Value    | 
+---------------+----------+ 
| have_openssl  | DISABLED |  
| have_ssl      | DISABLED |  默认是disabled 关闭的 
#vim /etc/my.cnf 
在[mysqld]段，添加 
ssl                    #表示开启mysql的ssl功能 
# service mysqld restart          一定要重启服务 
mysql> show variables like '%ssl%'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| have_openssl  | YES   | 
| have_ssl      | YES   |  验证一下 为yes 表示启动了  
注： 在两台服务器上做同样的操作，且确保两台都启动了

2、配置ssl参数：（在主从服务器上都操作）

#vim /etc/my.cnf    在之间添加的ssl下面添加以下内容： 
ssl-ca=/usr/local/mysql/ssl/cacert.pem 
ssl-cert=/usr/local/mysql/ssl/mysql.crt 
ssl-key=/usr/local/mysql/ssl/mysql.key 
 注：  这里使用绝对路径和前面你放置证书和私钥的位置保持一致

验证配置

# service mysqld restart 每修改一次配置文件都要重启服务  
mysql> show variables like '%ssl%'; 
+---------------+---------------------------------+ 
| Variable_name | Value                           | 
+---------------+---------------------------------+ 
| have_openssl  | YES                             | 
| have_ssl      | YES                             | 
| ssl_ca        | /usr/local/mysql/ssl/cacert.pem | 
| ssl_capath    |                                 | 
| ssl_cert      | /usr/local/mysql/ssl/mysql.crt  | 
| ssl_cipher    |                                 | 
| ssl_key       | /usr/local/mysql/ssl/mysql.key  | 
+---------------+---------------------------------+ 
7 rows in set (0.09 sec)

3、在master上：记录主服务器的二进制文件及时间，授权用户

mysql> show master status; 
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000007 |      107 |              |                  | 
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec) 
mysql> grant replication slave,replication client on *.* to
 'slave'@'172.16.2.2' identified by 'redhat' require ssl; 
Query OK, 0 rows affected (0.04 sec)  
require ssl 只能ssl认证才可以复制

4、在slave 上修改参数，启动slave

mysql> change master to  
      master_host='172.16.2.1', 
      master_user='slave',  
      master_password='redhat',  
      master_log_file='mysql-bin.000007', 
      master_log_pos=107,  和master的对应 
      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/mysql.crt', 
      master_ssl_key='/usr/local/mysql/ssl/mysql.key'; 
mysql> start slave; 
    Query OK, 0 rows affected (0.01 sec) 
mysql> show slave status \G 
*************************** 1. row *************************** 
                   Slave_IO_State: Waiting for master to send event  
                      Master_Host: 172.16.2.1 
                      Master_User: slave 
                      Master_Port: 3306 
                    Connect_Retry: 60 
                  Master_Log_File: mysql-bin.000007 
              Read_Master_Log_Pos: 107 
                   Relay_Log_File: mysql-relay.000002 
                    Relay_Log_Pos: 253 
            Relay_Master_Log_File: mysql-bin.000007 
                 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: 405 
                  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/mysql.crt 
                Master_SSL_Cipher:  
                   Master_SSL_Key: /usr/local/mysql/ssl/mysql.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.00 sec)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Allowed: Yes 三个都输出为yes 才表明配置成功

注：出现的错误：

Slave_IO_State: Connecting to master    
Slave_IO_Running: Connecting  一直显示 正在连接   
Slave_SQL_Running: Yes 
Last_IO_Error: error connecting to master 'slave@172.16.2.1:3306' - retry-time: 60  retries: 86400 
问题原因：两个服务器的时间不一致，同步一下时间就OK，排查一个上午原来就是这的原因，让我很是郁闷

此时基于ssl的mysql的主从复制配置成功

四、测试

在slave服务器上：

# mysql -uslave -h172.16.2.1 -predhat  
--sslca=/usr/local/mysql/ssl/cacert.pem 
 --ssl-cert=/usr/local/mysql/ssl/mysql.crt 
 --ssl-key=/usr/local/mysql/ssl/mysql.key  这是一行命令  连接master mysql 
mysql> \s  显示信息  
    -------------- 
    mysql  Ver 14.14 Distrib 5.5.24, for linux2.6 (i686) using readline 5.1 
     
    Connection id:      5 
    Current database:    
    Current user:       slave@node2.magedu.com 
    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.2.1 via TCP/IP 
    Server characterset:    latin1 
    Db     characterset:    latin1 
    Client characterset:    utf8 
    Conn.  characterset:    utf8 
    TCP port:       3306 
    Uptime:         14 min 6 sec 
     
    Threads: 3  Questions: 13  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.015 
    --------------