MySQL(二):主从复制结构、半同步复制、双主复制结构、利用SSL实现安全的MySQL主从复制

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL(二):主从复制结构、半同步复制、双主复制结构、利用SSL实现安全的MySQL主从复制

技术小阿哥 2017-11-26 14:31:00 浏览1131
展开阅读全文

主从复制结构、半同步复制、双主复制结构、利用SSL实现安全的MySQL主从复制


一、主从复制结构实验


1.主服务器配置

可以先更改server_id


在/etc/my.cnf文件中,添加

server_id=11


重启服务,在mysql命令行查看


MariaDB [(none)]> select @@server_id;

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

| @@server_id |

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

|        11 |

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

1 row in set (0.00 sec)


授权从服务器

MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'172.16.1.10'identified by 'replpass';

Query OK, 0 rows affected (0.00 sec)


刷新

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)


2.从服务器配置

在[mysqld]段落添加


server_id=22

relay_log=/var/log/mariadb/relay-log

skip_name_resolve=ON

innodb_file_per_table=ON


重启服务,在mysql命令行查看


MariaDB [(none)]> select @@server_id;

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

| @@server_id |

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

|        22 |

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

1 row in set (0.00 sec)


指定主服务器

MariaDB [(none)]> change master to master_host='172.16.1.4',master_user='repluser',master_password='replpass',master_log_file='binlog.000001',master_log_pos=245;


查看相关信息

MariaDB [(none)]> show slave status\G;


开启io线程

MariaDB [(none)]> start slave io_thread;


开启sql线程

MariaDB [(none)]> start slave sql_thread;


于是就可以在主服务器上插入信息,在从服务器上看到了。

-----------------------------------------------------------------------------------------------------------------------------------------


二、半同步复制结构实验

从MySQL5.5开始,MySQL以插件的形式支持半同步复制。如何理解半同步呢?首先我们来看看异步,全同步的概念


异步复制(Asynchronous replication)

MySQL默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从库是否已经接收并处理,这样就会有一个问题,主如果crash掉了,此时主上已经提交的事务可能并没有传到从上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。


全同步复制(Fully synchronous replication)

指当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。


半同步复制(Semisynchronous replication)

介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。


在主从复制搭建的基础上,做配置修改


1.主服务器:配置文件


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

server_id=11

relay_log=/var/log/mariadb/relay-log

skip_name_resolve=ON

innodb_file_per_table=ON

log_bin=/var/log/mariadb/binlog


重启

[root@zrs6 ~]# systemctl restart mariadb


安装插件

MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master';


查看相应变量

MariaDB [(none)]> show global variables like '%rpl_semi%';

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

| Variable_name                      | Value |

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

| rpl_semi_sync_master_enabled       | OFF   |

| rpl_semi_sync_master_timeout       | 10000 |

| rpl_semi_sync_master_trace_level   | 32    |

| rpl_semi_sync_master_wait_no_slave | ON    |

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


查看半同步的状态

MariaDB [(none)]> show global status like '%rpl_semi%';

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

| Variable_name                              | Value |

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

| Rpl_semi_sync_master_clients               | 0     |

| 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                | OFF   |

| 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     |

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


开启半同步复制

MariaDB [(none)]> set @@global.rpl_semi_sync_master_enabled=ON;



2.从服务器:配置文件


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

server_id=22

relay_log=/var/log/mariadb/relay-log

skip_name_resolve=ON

innodb_file_per_table=ON

log_bin=/var/log/mariadb/bin-log


重启

[root@zrs5 ~]# systemctl restart mariadb


安装插件

MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';


查看相应变量

MariaDB [(none)]> show global variables like '%rpl_semi%';

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

| Variable_name                   | Value |

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

| rpl_semi_sync_slave_enabled     | OFF   |

| rpl_semi_sync_slave_trace_level | 32    |

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


开启半同步

MariaDB [(none)]> set @@global.rpl_semi_sync_slave_enabled=ON;


3.查看主服务器的状态

MariaDB [(none)]> show global status like '%rpl_semi%';

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

| Variable_name                              | Value |

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

| Rpl_semi_sync_master_clients               | 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     |

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


4.当在主服务器添加数据时

MariaDB [(none)]> use zrs05;

MariaDB [zrs05]> insert into tb1 (Username,Age) values ('Zhang san',30),('Li si',27);

MariaDB [zrs05]> select * from tb1;

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

| UID | Username  | Age  |

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

|   1 | Zhang san |   30 |

|   2 | Li si     |   27 |

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


在从服务器查看,数据已经同步了。

MariaDB [(none)]> use zrs05;

MariaDB [zrs05]> select * from tb1;

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

| UID | Username  | Age  |

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

|   1 | Zhang san |   30 |

|   2 | Li si     |   27 |

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


5.验证半同步

需要在从服务器上先停止slave io_thread

MariaDB [zrs05]> stop slave io_thread;


当在主服务器上再次插入一条信息的时候,可以看到并不能立即插入成功,过了十秒钟后,才成功,主服务器被阻塞一段时间,说明半同步复制已经正常工作。

MariaDB [zrs05]> insert into tb1 (Username,Age) values ('Wang wu',33);

Query OK, 1 row affected (10.01 sec)


在从服务器上查看,并没有刚才插入的信息

MariaDB [zrs05]> select * from tb1;

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

| UID | Username  | Age  |

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

|   1 | Zhang san |   30 |

|   2 | Li si     |   27 |

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


当开启slave io_thread后,那条信息就出现了

MariaDB [zrs05]> start slave io_thread;


MariaDB [zrs05]> select * from tb1;

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

| UID | Username  | Age  |

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

|   1 | Zhang san |   30 |

|   2 | Li si     |   27 |

|   3 | Wang wu   |   33 |

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

-----------------------------------------------------------------------------------------------------------------------------------------


三、主主复制模型实验


1.两台服务器的配置文件

Server1:/etc/my.cnf

[mysqld]


innodb-file-per-table = ON

skip-name-resolve=ON

log_bin=/var/log/mariadb/binlog

server_id=11

relay_log=/var/log/mariadb/relaylog

auto_increment_offset=1

auto_increment_increment=2


Server2:/etc/my.cnf

[mysqld]


innodb-file-per-table = ON

skip-name-resolve=ON

log_bin=/var/log/mariadb/binlog

server_id=22

relay_log=/var/log/mariadb/relaylog

auto_increment_offset=2

auto_increment_increment=2


2.两台服务器的SQL语句

Server1:

 mysql> grant replication slave,replication client on *.* to 'repluser'@'172.16.1.3' identified by 'replpass';

 mysql> change master to master_host='172.16.1.3',master_user='repluser',master_password='replpass',master_log_file='bin-log.000001',master_log_pos=427;

 mysql> start slave [io_thread|sql_thread];


Server2:

 mysql> grant replication slave,replication client on *.* to 'repluser'@'172.16.1.2' identified by 'replpass';

 mysql> change master to master_host='172.16.1.2',master_user='repluser',master_password='replpass',master_log_file='bin-log.000024',master_log_pos=245;

 mysql> start slave [io_thread|sql_thread];

--------------------------------------------------------------------------------------------------------------------------------------


四、利用SSL实现安全的MySQL主从复制


MySQL的主从复制是直接利用明文传输的,如果我们在生产环境下,跨网络传输会非常不安全,所以就需要基于SSL加密传输,提高安全性。


1.修改配置文件

  在Master上:

vim /etc/my.cnf

[mysqld]

log-bin=master-bin

binlog_format=mixed

server-id=1

ssl

  

[root@zj07 ~]# systemctl restart mariadb


   在Slave上:

vim /etc/my.cnf

[mysqld]

log-bin=slave-bin

binlog_format=mixed

server-id=10

ssl


[root@zj08 ~]# systemctl restart mariadb


2.将master服务器自己做成CA服务器

[root@zj07 ~]# cd /etc/pki/CA/

[root@zj07 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)

Generating RSA private key, 2048 bit long modulus

...........+++

................................................................+++

e is 65537 (0x10001)

[root@zj07 CA]# 

[root@zj07 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 365

You are about to be asked to enter information that will be incorporated

into your certificate request.

What you are about to enter is what is called a Distinguished Name or a DN.

There are quite a few fields but you can leave some blank

For some fields there will be a default value,

If you enter '.', the field will be left blank.

-----

Country Name (2 letter code) [XX]:CN

State or Province Name (full name) []:HeBei

Locality Name (eg, city) [Default City]:QinHuangdao

Organization Name (eg, company) [Default Company Ltd]:zrs.com

Organizational Unit Name (eg, section) []:tech

Common Name (eg, your name or your server's hostname) []:master.zrs.com

Email Address []:

[root@zj07 CA]# touch index.txt

[root@zj07 CA]# echo 01 > serial


3.为master创建证书申请并由CA服务器签发证书

[root@zj07 CA]# mkdir -pv /usr/local/mysql/ssl

mkdir: 已创建目录 "/usr/local/mysql"

mkdir: 已创建目录 "/usr/local/mysql/ssl"

[root@zj07 CA]# 

[root@zj07 CA]# cd /usr/local/mysql/ssl/

[root@zj07 ssl]# (umask 077;openssl genrsa -out master.key 2048)

Generating RSA private key, 2048 bit long modulus

...........................................................................................................................................+++

..............+++

e is 65537 (0x10001)

[root@zj07 ssl]# openssl req -new -key master.key -out master.csr -days 365

You are about to be asked to enter information that will be incorporated

into your certificate request.

What you are about to enter is what is called a Distinguished Name or a DN.

There are quite a few fields but you can leave some blank

For some fields there will be a default value,

If you enter '.', the field will be left blank.

-----

Country Name (2 letter code) [XX]:CN

State or Province Name (full name) []:HeBei

Locality Name (eg, city) [Default City]:QinHuangdao

Organization Name (eg, company) [Default Company Ltd]:zrs.com

Organizational Unit Name (eg, section) []:tech

Common Name (eg, your name or your server's hostname) []:master.zrs.com

Email Address []:


Please enter the following 'extra' attributes

to be sent with your certificate request

A challenge password []:

An optional company name []:

[root@zj07 ssl]# 


[root@zj07 ssl]# openssl ca -in master.csr -out master.crt -days 365

Using configuration from /etc/pki/tls/openssl.cnf

Check that the request matches the signature

Signature ok

Certificate Details:

        Serial Number: 1 (0x1)

        Validity

            Not Before: Jan 15 13:35:39 2018 GMT

            Not After : Jan 15 13:35:39 2019 GMT

        Subject:

            countryName               = CN

            stateOrProvinceName       = HeBei

            organizationName          = zrs.com

            organizationalUnitName    = tech

            commonName                = master.zrs.com

        X509v3 extensions:

            X509v3 Basic Constraints: 

                CA:FALSE

            Netscape Comment: 

                OpenSSL Generated Certificate

            X509v3 Subject Key Identifier: 

                31:F0:A8:85:CD:6C:29:EF:3A:70:E6:C8:BB:5F:B4:04:61:32:86:48

            X509v3 Authority Key Identifier: 

                keyid:66:56:36:59:90:6D:94:DC:4E:19:A3:BA:2C:1E:53:DC:1A:8A:58:AE


Certificate is to be certified until Jan 15 13:35:39 2019 GMT (365 days)

Sign the certificate? [y/n]:y



1 out of 1 certificate requests certified, commit? [y/n]y

Write out database with 1 new entries

Data Base Updated

[root@zj07 ssl]# 


4.为slave服务器创建证书申请

[root@zrs08 ~]# mkdir -pv /usr/local/mysql/ssl

mkdir: 已创建目录 "/usr/local/mysql"

mkdir: 已创建目录 "/usr/local/mysql/ssl"

[root@zrs08 ~]# cd /usr/local/mysql/ssl/

[root@zrs08 ssl]# (umask 077;openssl genrsa -out slave.key 2048)

Generating RSA private key, 2048 bit long modulus

....+++

...................................................................................................+++

e is 65537 (0x10001)

[root@zrs08 ssl]# openssl req -new -key slave.key -out slave.csr -days 365

You are about to be asked to enter information that will be incorporated

into your certificate request.

What you are about to enter is what is called a Distinguished Name or a DN.

There are quite a few fields but you can leave some blank

For some fields there will be a default value,

If you enter '.', the field will be left blank.

-----

Country Name (2 letter code) [XX]:CN

State or Province Name (full name) []:HeBei

Locality Name (eg, city) [Default City]:QinHuangdao

Organization Name (eg, company) [Default Company Ltd]:zrs.com

Organizational Unit Name (eg, section) []:tech

Common Name (eg, your name or your server's hostname) []:slave.zrs.com

Email Address []:


Please enter the following 'extra' attributes

to be sent with your certificate request

A challenge password []:

An optional company name []:

[root@zrs08 ssl]# 


5.为slave服务器签署证书

将证书申请请求拷贝到CA服务器签署


在slave上

[root@zrs08 ssl]# scp slave.csr 172.16.1.7:/tmp/


在master上

[root@zj07 ssl]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 365

Using configuration from /etc/pki/tls/openssl.cnf

Check that the request matches the signature

Signature ok

Certificate Details:

        Serial Number: 2 (0x2)

        Validity

            Not Before: Jan 15 13:41:19 2018 GMT

            Not After : Jan 15 13:41:19 2019 GMT

        Subject:

            countryName               = CN

            stateOrProvinceName       = HeBei

            organizationName          = zrs.com

            organizationalUnitName    = tech

            commonName                = slave.zrs.com

        X509v3 extensions:

            X509v3 Basic Constraints: 

                CA:FALSE

            Netscape Comment: 

                OpenSSL Generated Certificate

            X509v3 Subject Key Identifier: 

                90:B1:B5:44:92:99:24:4A:50:A7:AB:3F:36:B9:CD:C7:87:1E:CE:4A

            X509v3 Authority Key Identifier: 

                keyid:66:56:36:59:90:6D:94:DC:4E:19:A3:BA:2C:1E:53:DC:1A:8A:58:AE


Certificate is to be certified until Jan 15 13:41:19 2019 GMT (365 days)

Sign the certificate? [y/n]:y


1 out of 1 certificate requests certified, commit? [y/n]y

Write out database with 1 new entries

Data Base Updated


6.签署好证书申请拷贝到slave服务器

[root@zj07 ssl]# scp /tmp/slave.crt 172.16.1.8:/usr/local/mysql/ssl


7.将CA证书拷贝到slave服务器一份并为master拷贝一份

[root@zj07 ssl]# scp /etc/pki/CA/cacert.pem 172.16.1.8:/usr/local/mysql/ssl/   

[root@zj07 ssl]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/


8.修改master和slave服务器证书属主、属组为"mysql"用户

在master上

[root@zj07 ssl]# chown -R mysql.mysql /usr/local/mysql/ssl/

[root@zj07 ssl]# ll /usr/local/mysql/ssl/

总用量 20

-rw-r--r--. 1 mysql mysql 1334 1月  15 21:45 cacert.pem

-rw-r--r--. 1 mysql mysql 4471 1月  15 21:36 master.crt

-rw-r--r--. 1 mysql mysql 1013 1月  15 21:34 master.csr

-rw-------. 1 mysql mysql 1679 1月  15 21:33 master.key


在slave上

[root@zrs08 ssl]# chown -R mysql.mysql /usr/local/mysql/ssl/

[root@zrs08 ssl]# ll /usr/local/mysql/ssl/

总用量 20

-rw-r--r--. 1 mysql mysql 1334 1月  15 21:45 cacert.pem

-rw-r--r--. 1 mysql mysql 4466 1月  15 21:43 slave.crt

-rw-r--r--. 1 mysql mysql 1009 1月  15 21:38 slave.csr

-rw-------. 1 mysql mysql 1679 1月  15 21:37 slave.key


9.在master与slave服务器修改主配置文件开启SSL加密功能

修改master数据库配置文件

[root@zj07 ssl]# vim /etc/my.cnf

ssl_ca=/usr/local/mysql/ssl/cacert.pem

ssl_cert=/usr/local/mysql/ssl/master.crt

ssl_key=/usr/local/mysql/ssl/master.key


[root@zj07 ssl]# systemctl restart mariadb


修改slave数据库配置文件

[root@zrs08 ssl]# vim /etc/my.cnf

ssl_ca=/usr/local/mysql/ssl/cacert.pem

ssl_cert=/usr/local/mysql/ssl/slave.crt

ssl_key=/usr/local/mysql/ssl/slave.key


[root@zrs08 ssl]# systemctl restart mariadb


10.在master服务器查看SSL加密是否开启,然后创建授权一个基于密钥认证的用户


MariaDB [(none)]> 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/master.crt |

| ssl_cipher    |                      |

| ssl_key       | /usr/local/mysql/ssl/master.key |

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


MariaDB [(none)]> grant replication client,replication slave on *.* to 'slave'@'172.16.1.8' identified by '12345678' require ssl;


MariaDB [(none)]> flush privileges;


11.查看master服务器二进制日志文件和事件位置用于的slave服务器链接从这个位置开始复制

MariaDB [(none)]> show master status;

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

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| master-bin.000002 |    502 |         |             |

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


12.测试使用加密用户指定的密钥链接服务器

在slave上


[root@zrs08 ~]# mysql -uroot -p12345678 -h 172.16.1.7 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 41

Server version: 5.5.56-MariaDB MariaDB Server


Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [(none)]> 


13.查看slave服务器SSL是否开启并连接master服务器

查看是否开启SSL

MariaDB [(none)]> 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/master.crt |

| ssl_cipher    |                       |

| ssl_key       | /usr/local/mysql/ssl/master.key |

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


链接master服务器

MariaDB [(none)]> change master to

    -> master_host='172.16.1.7',master_user='slave',master_password='12345678',

    -> master_log_file='master-bin.000002',master_log_pos=502,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';

Query OK, 0 rows affected (0.04 sec)


帮助文档

MariaDB [(none)]> help change master to;

  | MASTER_SSL = {0|1}                     #是否使用SSL功能

  | MASTER_SSL_CA = 'ca_file_name'            #CA证书位置

  | MASTER_SSL_CERT = 'cert_file_name'        #指定自己的证书文件

  | MASTER_SSL_KEY = 'key_file_name'          #指定自己的密钥文件


14.查看slave服务器的状态

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.02 sec)


MariaDB [(none)]> show slave status\G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.16.1.7

                  Master_User: slave

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000002

          Read_Master_Log_Pos: 2028

               Relay_Log_File: mariadb-relay-bin.000002

                Relay_Log_Pos: 2056

        Relay_Master_Log_File: master-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: 2028

              Relay_Log_Space: 2352

              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

1 row in set (0.00 sec)


15.利用SSL实现安全的MySQL主从复制

在master服务器上创建数据库

[root@zj07 ~]# mysql -uroot -p12345678 -e 'create database this_is_a_test_db;'

[root@zj07 ~]# mysql -uroot -p12345678 -e 'show databases';

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

| Database        |

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

| information_schema |

| mysql          |

| performance_schema |

| this_is_a_test_db  |

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


登录slave服务器验证slave数据库是否存在

[root@zrs08 ~]# mysql -uroot -p12345678 -e 'show databases';

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

| Database        |

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

| information_schema |

| mysql           |

| performance_schema |

| this_is_a_test_db  |

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




本文转自 Runs_ 51CTO博客,原文链接:http://blog.51cto.com/12667170/2061494,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
技术小阿哥
+ 关注