MairaDB之基于openssl的主從復制

kang ? ? Linux干貨

一、配置openssl 

 1、   在master server(node0)上根CA的搭建(及生成自簽名證書)

[root@node0 ~]# cd /etc/pki/CA/
[root@node0 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)
Generating RSA private key, 2048 bit long modulus
.................+++
..................+++
e is 65537 (0x10001)

[root@node0 CA]# touch index.txt 
[root@node0 CA]# echo 01 > serial

[root@node0 CA]# openssl req -new -x509 -key private/cakey.pem -out ./cacert.pem -days 3650
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) []:BJ
Locality Name (eg, city) [Default City]:BJ
Organization Name (eg, company) [Default Company Ltd]:rj
Organizational Unit Name (eg, section) []:rj
Common Name (eg, your name or your server's hostname) []:www.rj.com
Email Address []:

2、為master主機生成密鑰，并名為之簽名

[root@node0 CA]# cd /etc/my.cnf.d/
[root@node0 my.cnf.d]# (umask 077;openssl genrsa -out master.key 2048)
Generating RSA private key, 2048 bit long modulus
...............................................+++
................+++
e is 65537 (0x10001)

[root@node0 my.cnf.d]# openssl req -new -key /etc/my.cnf.d/master.key -out /etc/my.cnf.d/master.csr
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) []:BJ  
Locality Name (eg, city) [Default City]:BJ
Organization Name (eg, company) [Default Company Ltd]:rj
Organizational Unit Name (eg, section) []:rj
Common Name (eg, your name or your server's hostname) []:www.rj.com
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

3、根CA簽發master請求

[root@node0 my.cnf.d]# openssl ca -in master.csr -out master.crt -days 3650
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: Feb 16 06:55:06 2017 GMT
            Not After : Feb 14 06:55:06 2027 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = BJ
            organizationName          = rj
            organizationalUnitName    = rj
            commonName                = www.rj.com
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                B4:A1:66:8C:5B:2B:F9:59:9D:F6:4F:F7:35:72:E2:87:9C:A5:95:F9
            X509v3 Authority Key Identifier: 
                keyid:71:DD:03:78:51:12:5F:58:C2:1B:53:76:A0:2B:E9:BF:60:D8:67:36

Certificate is to be certified until Feb 14 06:55:06 2027 GMT (3650 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

3、為slave結點(node1)配置openssl 

[root@node1 CA]# cd /etc/my.cnf.d/
[root@node1 my.cnf.d]# (umask 077;openssl genrsa -out slave.key 2048)
Generating RSA private key, 2048 bit long modulus
.............+++
......................................................................................................................................................................................................................................................+++
e is 65537 (0x10001)
[root@node1 my.cnf.d]# openssl req -new -key slave.key -out slave.csr
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) []:BJ
Locality Name (eg, city) [Default City]:BJ
Organization Name (eg, company) [Default Company Ltd]:rj
Organizational Unit Name (eg, section) []:rj
Common Name (eg, your name or your server's hostname) []:www.rj.com
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@node1 my.cnf.d]# scp slave.csr node0:/tmp
The authenticity of host 'node0 (172.16.23.10)' can't be established.
ECDSA key fingerprint is 2b:98:49:35:5b:78:24:ed:f0:ab:fa:54:b1:8e:df:29.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'node0,172.16.23.10' (ECDSA) to the list of known hosts.
root@node0's password: 
slave.csr

4、為slave結點簽發請求

[root@node0 my.cnf.d]# mv /etc/pki/CA/serial /root/
[root@node0 my.cnf.d]# mv /etc/pki/CA/index.txt /root/
[root@node0 my.cnf.d]# touch /etc/pki/CA/index.txt
[root@node0 my.cnf.d]# echo 01 > /etc/pki/CA/serial
[root@node0 my.cnf.d]# openssl ca -in /tmp/slave.csr -out slave.crt -days 3650
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: Feb 16 07:07:14 2017 GMT
            Not After : Feb 14 07:07:14 2027 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = BJ
            organizationName          = rj
            organizationalUnitName    = rj
            commonName                = www.rj.com
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                A5:FE:11:EB:4D:B5:F1:85:61:E7:18:E3:1D:B7:25:C6:1B:24:97:AF
            X509v3 Authority Key Identifier: 
                keyid:71:DD:03:78:51:12:5F:58:C2:1B:53:76:A0:2B:E9:BF:60:D8:67:36

Certificate is to be certified until Feb 14 07:07:14 2027 GMT (3650 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@node0 my.cnf.d]# scp slave.crt node1:/etc/my.cnf.d/
slave.crt                                                                                                                                                    100% 4382     4.3KB/s   00:00

此時主從結點的證書都已經準備好

二、配置mariadb主從服務器

1、工作拓撲圖

MairaDB之基于openssl的主從復制

2、主服務器配置

[root@node0 ~]# yum -y install mariadb
[root@node0 my.cnf.d]# vim /etc/my.cnf

將mysqld段中的配置修改為以下內容

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb-file-per-table = ON
skip-name-resolve = ON
server-id = 1 
log-bin = master-log
ssl
ssl_ca=/etc/my.cnf.d/cacert.pem 
ssl_cert=/etc/my.cnf.d/master.crt
ssl_key=/etc/my.cnf.d/master.key 

[root@node0 my.cnf.d]# cp /etc/pki/CA/cacert.pem  .
[root@node0 my.cnf.d]# chown mysql.mysql cacert.pem master.*
[root@node0 my.cnf.d]# systemctl start mariadb

3、從服務配置

[root@node0 ~]# yum -y install mariadb
[root@node0 my.cnf.d]# vim /etc/my.cnf

將mysqld段中的配置修改為以下內容

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
skip_name_resolve = ON 
relay-log = relay-log
server-id = 2 注id號不能與主服務的一樣
ssl
ssl-ca = /etc/my.cnf.d/cacert.pem
ssl-cert = /etc/my.cnf.d/slave.crt
ssl-key = /etc/my.cnf.d/slave.key

[root@node0 my.cnf.d]# scp /etc/pki/CA/cacert.pem node1:/etc/my.cnf.d/
cacert.pem 
[root@node1 my.cnf.d]# cd /etc/my.cnf.d/ && chown mysql.mysql cacert.pem slave.*

[root@node1 my.cnf.d]# systemctl start mariadb

4、主服務器授權一個用戶可連接mysql拉取二進制文件

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rj'@'172.16.23.11' IDENTIFIED BY 'centos.123' REQUIRE ssl;

5、配置從服務器連接到主服務器，并拉取數據

    先查看主結點的二進制日志



    MariaDB [(none)]>  SHOW MASTER STATUS;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-log.000007 |      342 |              |                  |+-------------------+----------+--------------+------------------+


MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.23.10', MASTER_USER='rj', MASTER_PASSWORD='centos.123', MASTER_LOG_FILE='master-log.000007', MASTER_LOG_POS=245, MASTER_SSL=1, MASTER_SSL_CA='/etc/my.cnf.d/cacert.pem', MASTER_SSL_CERT='/etc/my.cnf.d/slave.crt', MASTER_SSL_KEY='/etc/my.cnf.d/slave.key';
Query OK, 0 rows affected (0.03 sec)

查看主從服務器的openssl的是用否用

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| have_openssl  | YES                      |
| have_ssl      | YES                      |
| ssl_ca        | /etc/my.cnf.d/cacert.pem |
| ssl_capath    |                          |
| ssl_cert      | /etc/my.cnf.d/master.crt |
| ssl_cipher    |                          |
| ssl_key       | /etc/my.cnf.d/master.key |
+---------------+--------------------------+
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| have_openssl  | YES                      |
| have_ssl      | YES                      |
| ssl_ca        | /etc/my.cnf.d/cacert.pem |
| ssl_capath    |                          |
| ssl_cert      | /etc/my.cnf.d/slave.crt  |
| ssl_cipher    |                          |
| ssl_key       | /etc/my.cnf.d/slave.key  |
+---------------+--------------------------+

6、啟用從服務器

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.23.10
                  Master_User: rj
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000007
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-log.000007
             Slave_IO_Running: Yes  注:IO SQL這兩項表示主從同步已經正學進行
            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: 245
              Relay_Log_Space: 818
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/my.cnf.d/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/my.cnf.d/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/my.cnf.d/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)

7、由于從服務器只能讀，所以需要開啟mariadb的只讀 

MariaDB [(none)]>  SHOW VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL read_only=ON;
Query OK, 0 rows affected (0.00 sec)

8、openssl進行測試

[root@node1 my.cnf.d]# mysql -urj -pcentos.123 -h172.16.23.10 --ssl-ca=/etc/my.cnf.d/cacert.pem --ssl-cert=/etc/my.cnf.d/slave.crt --ssl-key=/etc/my.cnf.d/slave.key
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.44-MariaDB-log MariaDB Server

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

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

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

9、主從同步測試

MariaDB [(none)]> CREATE DATABASE node0create;在主服務上創建了一個庫
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> SHOW DATABASES; 在從服務器上也可以查看到了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| node0create        |
| performance_schema |
| test               |
+--------------------+

原創文章，作者：kang，如若轉載，請注明出處：http://www.www58058.com/79175

贊 (0)
kangkang
上一篇 2016-11-19
下一篇 2016-11-19

相關推薦

  • rpm與yum的初步學習

    rpm與yum的初步學習   我們在使用linux系統時需要安裝和卸載軟件，我們這節就先來看一下rpm和yum的初步簡單實用。 linux軟件安裝：   安裝程序的方式：      通用二進制格式：直接解壓壓縮文件，就可以使用。但一定要注意安裝平臺。     &nbsp…

    Linux干貨 2016-08-24

  • DNS

    DNS DNS解析 DNS名稱解析方式：名稱 –> IP：正向解析IP –> 名稱：反向解析注意：二者的名稱空間，非為同一個空間，即非為同一棵樹；因此，也不是同一個解析庫； DNS查詢類型：遞歸查詢：一般是客戶機和服務器之間的查詢；即只發送一個請求，其他的工作交由上層服務器去解決；迭代查詢：一般是服務器和服務器之間的凡是，…

    Linux干貨 2017-05-31

  • Linux用戶和組管理

    使用對象：Linux初學者   Linux系統中用戶和組管理是很重要的一部分內容。許多初學者在學習或剛接觸到用戶管理的時候會覺得很難理解，命令多、選項多、配置文件也多，用命令可以修改，用配置文件也可以修改，三兩下就被繞進去了。其實完全沒必要暈，只要了解了用戶管理的方式，就會很容易理解命令選項與配置文件之間的關系。下面我們就來了解下用戶管理命令與配置…

    Linux干貨 2016-10-23

  • $@與$*的不同之處

    $@與$*的不同之處 $*: 把我們傳遞給腳本的參數全部合為一個字節，當成一個字符串或者參數來使用。 $@: 把我們傳遞給腳本的所有參數，每個參數都為獨立字符串，當我們用 \(（n） 來調用的時候， 輸入參數的位置為第幾個，那我們就用用\)n來調用第幾個，一對應。 下面我們用代碼來看一下這兩個的區別： 創建一個簡單的腳本讓他輸出我們輸入的多個參數：&#822…

    2017-06-06

  • Redis集群明細文檔

    Redis目前版本是沒有提供集群功能的，如果要實現多臺Redis同時提供服務只能通過客戶端自身去實現(Memchached也是客戶端實現分布式)。目前根據文檔已經看到Redis正在開發集群功能，其中一部分已經開發完成，但是具體什么時候可以用上，還不得而知。文檔來源：http://redis.io/topics/cluster-spec 一、介紹 　　該文檔是…

    Linux干貨 2015-04-04

  • 20160802課堂作業

    作業： 在/data/testdir里創建的新文件自動屬于g1組，組g2的成員如：alice能對這些新文件有讀寫權限，組g3的成員如：tom只能對新文件有讀權限，其它用戶（不屬于g1,g2,g3）不能訪問這個文件夾。 #groupadd g1 #groupadd g2 #groupadd g3 #useradd -G g2 alice #useradd -G…

    Linux干貨 2016-08-04
欧美性久久久久