mysql之SSL複製/主從/主主/半同步，mysqlssl

mysql之SSL複製/主從/主主/半同步，mysqlssl

本實驗的目的是實現兩台主機上的MySQL資料複製，以及基於SSL的複製。

*要注意的兩點問題所在：

1，版本問題：複製雙方的版本最好一致，若不同，從節點的版本必須高於主節點的版本

2，複製起點問題：（1），從0開始，使用於均為建立伺服器。（2），中間開始，就需要完全備份主服務上的資料，並將資料恢複至從伺服器； 從伺服器從備份時主伺服器二進位日誌所在位置開始複製。

實驗環境：

node1：MASTER MariaDB 172.16.18.1

node2：SALVE    MariaDB 172.16.18.2

配置過程：
master:
1、啟用二進位日誌
2、定義server-id
3、建立有複製許可權的帳號
REPLICATION SLAVE, REPLICATION CLIENT
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repluser@'172.16.%.%' IDENTIFIED BY 'replp@ss';
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
slave:
1、啟動中繼日誌
2、定義server-id
3、使用有複製許可權的帳號串連master
4、啟動io thread以及sql thread

實驗模型一：從零複製MySQL

安裝MySQL的方法此處不做講解，不會的可以查看此連結http://aolens.blog.51cto.com/7021142/1539522

首先配置node1，主MySQL

首先指定自己的二進位日誌存放位置。

log-bin=/mydata/logs/mysql-bin         #指定二進位日誌存放位置

mkdir /mydata/logs -pv                 #建立二進位檔案存放目錄

chown -R mysql.mysql /mydata/          #修改logs屬主屬組為mysql

此節點的server-id = 1 不用修改。但要保證從節點可次server-id的不一樣。

配置node2：從MySQL

修改設定檔

server-id = 2                              #指定server-id號

relay-log = /mydata/relaylogs/relay-log    #指定relay-log的位置以及名稱

#log-bin=mysql-bin                         #關閉二進位日誌。

啟動node1，node2的MySQL

去主MySQL建立有複製許可權的帳號。node1

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO relay@'172.16.18.2' IDENTIFIED BY 'relay';

Query OK, 0 rows affected (0.24 sec)

注意授權帳號時要最小許可權，最好指定授權給那個IP，不要給出多餘的許可權。

MariaDB [(none)]> FLUSH PRIVILEGES;                            #重新整理。

Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> show master status; 查看當前日誌和位置。

好的，主節點的複製許可權帳號建立好了，下邊去從節點串連主節點。

串連主節點的命令CHANGE MASTER TO

MariaDB [(none)]> HELP CHANGE MASTER TO                #查看一下命令的協助

   Name: 'CHANGE MASTER TO'

   Description:

   Syntax:

   CHANGE MASTER TO option [, option] ...

   option:

       MASTER_BIND = 'interface_name'

     | MASTER_HOST = 'host_name'                   # 指明要串連的主節點，值類型字串

     | MASTER_USER = 'user_name'                   # 具有複製許可權的帳號，值類型為字串

     | MASTER_PASSWORD = 'password'                # 上述使用者的密碼，值類型為字串

     | MASTER_PORT = port_num                      #如果MySQL修改了連接埠，此處也要指定

     | 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'                 #CA檔案名稱

     | MASTER_SSL_CAPATH = 'ca_directory_name'        #CA路徑

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

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.18.1',MASTER_USER='relay',MASTER_PASSWORD='relay',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=489;

Query OK, 0 rows affected (0.08 sec)

查看從節點狀態資訊：

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

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

Slave_IO_State: 
Master_Host: 172.16.18.1             #主節點
Master_User: relay                   #使用者名稱
Master_Port: 3306                    #連接埠
Connect_Retry: 60                    #多長時間重試一次
Master_Log_File: mysql-bin.000001    #當前複製的記錄檔
Read_Master_Log_Pos: 489             #複製的位置
Relay_Log_File: relay-log.000001     #當前自己的中繼記錄檔
Relay_Log_Pos: 4                     #目前記錄的位置
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No                 #啟動複製線程必須啟動為YES
Slave_SQL_Running: No                #啟動複製線程必須啟動為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: 489
Relay_Log_Space: 245
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.01 sec)

啟動Slave_IO_Running和Slave_SQL_Running

可以單個線程啟動：

START SLAVE IO_THREAD;

START SLAVE SQL_THREAD;

全部啟動：

START SLAVE;

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

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 529

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

可以看到中繼日誌在動了。我們現在去master上建立一些資料，看看能不能同步過來。

mysql> create database db1;

Query OK, 1 row affected (0.00 sec)

mysql> use db1

Database changed

mysql> create table tb1( id int);

Query OK, 0 rows affected (0.16 sec）

在slave上查看。

模型二：MySQL在運行中複製

此時你要做的就是先對啟動並執行資料庫進程一次全備份

mysqldump --all-databases --lock-all-tables  --master-data=2  > /backup/all.sql

記錄下master-data的位置。

發送all.sql到node2

當然此時的node2應該是一個新安裝的MySQL

MySQL < all.sql  即可

此時再串連master資料庫

CHANGE MASTER TO MASTER_HOST='172.16.18.1',MASTER_USER='relay',MASTER_PASSWORD='relay',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=3809;

start slave 即可。

複製中應該注意的問題：

1、如何限制從伺服器唯讀？

更改slave的全域伺服器變數read-only為YES；

注意：此限制對於擁有SUPER許可權使用者無效；

MariaDB [mysql]> SET GLOBAL read_only = 1;   #臨時修改，永久修改需要卸載設定檔中mysqld段

[mysqld]

read_only = 1

阻止所有使用者執行寫操作：

MariaDB [mysql]> flush tables with read lock;     #對超級使用者同樣有效。

2、如何保證主從複製時的事務安全？

事務提交時會緩衝到記憶體，以減小I/O壓力，但是這種情況可能導致主節點掛掉時事務為寫入二進位日誌，同步不到從節點，導致事務丟失。為瞭解決這一問題，我們要是他及時同步到磁碟寫入二進位日誌中國

前提：mysql對二進位日誌事件數目據會有緩衝;

在master上設定如下參數：

sync_binlog = 1            #開啟寫入二進位日誌

3、半同步複製？

半同步：master要等待一個從節點把資料完整複製過去；

由google貢獻的補丁；以外掛程式的方式存在

外掛程式位置/usr/local/mysql/lib/plugin/

semisync_master.so     #主節點外掛程式

semisync_slave.so        #從節點外掛程式

在主節點上安裝外掛程式。

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.16 sec)

rpl_semi_sync_master_wait_no_slave   #沒有從節點要不要等待，值為ON

mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;            #開啟模組

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_timeout=1000;        #修改時間

Query OK, 0 rows affected (0.00 sec)

此為臨時設定，需要寫在設定檔。

slave從節點node2 

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; #安裝模組

Query OK, 0 rows affected (0.06 sec)

MariaDB [(none)]> set global rpl_semi_sync_slave_enabled = 1; #開啟模組

Query OK, 0 rows affected (0.00 sec)

同樣不會永久有效

安裝模組時需要停止主從，此時啟動主從start slave，注意：只關閉並啟動IO_THREAD也可；

主節點在聯絡從節點逾時後，會自動降紙為非同步模式；

在主節點查看是否進入半同步。

net_avg_wait_time #平均網路等待時間

net_wait_time    #網路等待時間

net_waits          #網路等待次數

no_time            #沒有時間

no_tx                #沒有事務

4、複製過濾器

讓slave僅複製有限的幾個資料庫，甚至於僅複製某資料庫內有限的幾張表的機制；

有兩種方案： 

1、在主節點上過濾

     在向二進位日誌記錄事件時，僅記錄指定資料庫的相關操作； 這對以後的還原時間點時致命的，不可取。

      binlog_do_db =                       # 資料庫白名單

      binlog_ignore_db =                  # 資料庫黑名單

      黑名單中有，白名單中也有，黑名單有效。不要黑白名單同時啟用

2、在從節點上過濾

     從節點都接收，存在中繼日誌。僅從中繼日誌中讀取指定的資料庫或表的相關事件並應用於本地；

      replicate_do_db =                    #資料庫白名單

      replicate_ignore_db =               #資料庫黑名單

      replicate_db_table = DB_NAME.TB_NAME  #表白名單

      replicate_ignore_table =

      replicate_wild_do_table =          #萬用字元白名單

      replicate_wild_ignore_table =

5、雙主模型複製

先stop slave  在主節點建立資料

mysql> create database db2;

Query OK, 1 row affected (1.02 sec)

mysql> use db2

Database changed

mysql> create table tb1(id int);

Query OK, 0 rows affected (0.09 sec)

mysql> insert into tb1(id) values (20);

在node2中建立資料

MariaDB [(none)]> create database db3;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use db3

Database changed

MariaDB [db3]> create table tb1(id int);

Query OK, 0 rows affected (0.02 sec)

MariaDB [db3]> insert into tb1(id) values (888);

Query OK, 1 row affected (0.04 sec)

(1) 雙方節點都得建立具有複製許可權使用者；

node1上建立使用者

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO relay@'172.16.18.%' IDENTIFIED BY 'relay';

Query OK, 0 rows affected (0.02 sec)

node2上建立使用者

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO relay@'172.16.18.%' IDENTIFIED BY 'relay';

Query OK, 0 rows affected (0.02 sec)

(2) 雙節點都得啟用中繼日誌和二進位日誌；

(3) 為保證具有自動成長功能的欄位能正確產生ID，需要配置兩個節點分別使用偶數或奇數ID號；

node1 為奇數 ，node2選擇偶數。

(4) 都要把對方配置為自己的主節點；

   node1節點：

   [mysqld]

   server-id=1

   log-bin=/mydata/logs/master-bin

   relay-log=/mydata/relaylogs/relay-log

   auto-increment-increment = 2

   auto-increment-offset = 1

   node2節點：

   [mysqld]

   server-id=2

   log-bin=/mydata/logs/mysql-bin

   relay-log=/mydata/relaylogs/relay-log

   auto-increment-increment = 2

   auto-increment-offset = 2

(5)查看兩個節點都到什麼位置，在兩個節點各自串連，各自啟動slave

node1：mysql> CHANGE MASTER TO MASTER_HOST='172.16.18.2',MASTER_USER='relay',MASTER_PASSWORD='relay',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=245;

Query OK, 0 rows affected (0.13 sec)

node2：MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.18.1',MASTER_USER='relay',MASTER_PASSWORD='relay',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=245;

Query OK, 0 rows affected (0.30 sec)

在node1，node2上執行：start slave 

分別在node1，node2上建立資料，查看是否同步過去了。

6、基於SSL的複製

（1），在node1上配置CA服務

cd /etc/pki/CA/

mkdir certs newcerts crl
touch index.txt
echo 01 > serial

產生CA 自簽署認證

[root@node1 CA]# (umask 077;openssl genrsa -out private/cakey.pem 1024)   #產生密鑰
Generating RSA private key, 1024 bit long modulus
...........................................++++++
...............................................................++++++
e is 65537 (0x10001)

[root@node1 CA]# openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 3655    #自簽署

[root@node1 CA]# cd /usr/local/mysql/            

[root@node1 mysql]# mkdir ssl

[root@node1 ssl]# （umask 077;openssl genrsa -out /usr/local/mysql/ssl/master.key 2048）   #在ssl目錄下產生密鑰
-bash: （umask: command not found
Generating RSA private key, 2048 bit long modulus
.....................................+++
.................................................................+++
e is 65537 (0x10001)

[root@node1 ssl]# openssl req -new -key /usr/local/mysql/ssl/master.key -out /usr/local/mysql/ssl/master.csr     #產生認證簽署請求

[root@node1 ssl]# openssl ca -in master.csr -out master.crt -days 365     #簽署認證

# vim /etc/my.cnf  

skip-slave-start=1           #設定重啟服務不自動開啟線程，需要手動開啟  

ssl-ca=/usr/local/mysql/ssl/cacert.pem  

ssl-cert=/usr/local/mysql/ssl/master1.crt  

ssl-key=/usr/local/mysql/ssl/master1.key  

cp /etc/pki/cacert.pem .     #複製到/usr/local/mysql/ssl/

chown -R mysql.mysql ssl

建立一個可基於SSL複製的使用者。

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO relay@'172.16.18.%' IDENTIFIED BY 'relay' REQUIRE SSL;

（1），在node2上配置CA服務

[root@node2 ssl]# (umask 077;openssl genrsa 1024 > master1slave.key)
Generating RSA private key, 1024 bit long modulus
.............................++++++
..................................................................................++++++
e is 65537 (0x10001)
[root@node2 ssl]# openssl req -new -key master1slave.key -out master1slave.csr
發送 master1slave.csr 去node1簽署

得到 master1slave.crt發回來，放到/usr/local/mysql/ssl/

在Master2上配置Master1的slave資訊

mysql> CHANGE MASTER TO MASTER_HOST = '172.16.18.2' ,  //指定主伺服器  

    -> MASTER_USER = 'relay' ,   //指定使用者  

    -> MASTER_PASSWORD = 'relay' ,    //密碼  

    -> MASTER_LOG_FILE = 'master-bin.000005' ,  //指定日誌  

    -> MASTER_LOG_POS = 245 ,    //指定日誌位  

    -> MASTER_SSL = 1 ,  

    -> MASTER_SSL_CA = '/usr/local/mysql/ssl/cacert.pem' ,  

    -> MASTER_SSL_CERT = '/usr/local/mysql/ssl/master1slave.crt' ,  

    -> MASTER_SSL_KEY = '/usr/local/mysql/ssl/master1slave.key';

start slave 啟動從節點複製。

在主節點建立庫表，在從節點查看。

mysql主從同步問題，幫忙解釋下，

沒問題的。

你在從伺服器上SHOW SLAVE STATUS\G可以查看同步的狀態。

建議你讀讀MySQL文檔中的複製（Replication）部分
參考資料：dev.mysql.com/...s.html
 
mysql的主從複製，linux重啟後遇到問題

主機的mysql重啟，但是你的從機mysql肯定是沒重啟才出現binlog索引不一致的現象，我認為是，從機mysql在主機重啟之前slave_io_running線程始終保持和主機通訊，傳輸binlog的更新。當主機掛掉之後，slave_io_running一直等待，狀態變成NO，整個複製就會停掉，應該說是處於sleep狀態，後來主機恢複，從機是沒法辨別的，只能stop slave 然後再start，或者change master操作下，實在不行，只能重啟從機的mysql了