MySQL-5.5.33主從複制，半同步複制，以及基于SSL的複制

一，Mysql複制概述

Mysql内建的複制功能是建構大型，高性能應用程式的基礎。将Mysql的資料分布到多個系統上去，這種分布的機制，是通過将Mysql的某一台主機的資料複制到其它主機（slaves）上，并重新執行一遍來實作的。複制過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當從伺服器。主伺服器将更新寫入二進制日志檔案，并維護檔案的一個索引以跟蹤日志循環。這些日志可以記錄發送到從伺服器的更新。當一個從伺服器連接配接主伺服器時，它通知主伺服器從伺服器在日志中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新,然後封鎖并等待主伺服器通知新的更新。

1.複制能解決的問題

資料分布(多個地區的資料分發)

負載均衡(讀寫分離)

備份

高可用和故障切換的核心功能

測試mysql更新

2.複制的原理

mysql複制的原理現階段都是一樣的，master将操作記錄到bin-log中，slave的一個線程去master讀取bin-log，并将他們儲存到relay-log中，slave的另外一個線程去重放relay-log中的操作來實作和master資料同步。

3.複制的曆史

mysql-3.2 開始支援基于指令的複制，也就是statement-based replication。mysql-5.1 開始支援基于行的複制和混合複制，也就是row-based replication和mixed-based relication，mysql-5.5 開始支援semi-synchronous的複制，也叫半同步複制，目的在于事務環境下保持主從一緻，mysql-5.6 開始支援延時複制。

下面是複制的基本原理：

二，配置主從複制

為了示範友善，實驗使用通用的二進制包來安裝mysql:

mysql版本：mysql-5.5.33-linux2.6-x86_64

OS版本：Centos 6.4 x86:64

首先來安裝master伺服器

<code>[root@localhost ~]</code><code># useradd -r -u 120 mysql</code>

<code>[root@localhost ~]</code><code># tar zxvf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/</code>

<code>[root@localhost </code><code>local</code><code>]</code><code># ln -s mysql-5.5.33-linux2.6-x86_64 mysql</code>

<code>[root@localhost </code><code>local</code><code>]</code><code># cd mysql</code>

<code>[root@localhost mysql]</code><code># mkdir /mydata/data -p</code>

<code>[root@localhost mysql]</code><code># chown -R root.mysql ./*</code>

<code>[root@localhost mysql]</code><code># ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data</code>

<code>[root@localhost mysql]</code><code># cp support-files/my-large.cnf /etc/my.cnf</code>

<code>[root@localhost mysql]</code><code># cp support-files/mysql.server /etc/rc.d/init.d/mysqld</code>

<code>[root@localhost mysql]</code><code># chmod +x /etc/init.d/mysqld</code>

<code>[root@localhost mysql]</code><code># chkconfig --add mysqld</code>

<code>[root@localhost mysql]</code><code># chkconfig mysqld on</code>

<code>[root@localhost mysql]</code><code># vi /etc/my.cnf</code>

<code># 在mysqld中添加如下倆行</code>

<code>datadir=</code><code>/mydata/data</code>

<code>innodb_file_per_table=1</code>

<code>[root@localhost mysql]</code><code># service mysqld start</code>

<code>Starting MySQL...... SUCCESS!</code>

在從伺服器上執行上面相同的操作

在master伺服器上啟用二進制日志,設定server-id（必須保證和從伺服器不同）

<code>log-bin = </code><code>/mydata/data/master-bin</code>

<code>log-bin-index = </code><code>/mydata/data/master-bin</code><code>.index</code>

<code>server-</code><code>id</code> <code>= 1</code>

建立具體複制權限的使用者

<code>[root@localhost ~]</code><code># mysql</code>

<code>Welcome to the MySQL monitor. Commands end with ; or \g.</code>

<code>Your MySQL connection </code><code>id</code> <code>is 1</code>

<code>Server version: 5.5.33-log MySQL Community Server (GPL)</code>

<code>Oracle is a registered trademark of Oracle Corporation and</code><code>/or</code> <code>its</code>

<code>affiliates. Other names may be trademarks of their respective</code>

<code>owners.</code>

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

<code>mysql> grant replication slave,replication client on *.* to </code><code>'repl'</code><code>@</code><code>'192.168.30.%'</code> <code>identified by </code><code>'123456'</code><code>;</code>

<code>mysql> flush privileges;</code>

<code>Query OK, 0 rows affected (0.00 sec)</code>

<code>[root@localhost ~]</code><code># service mysqld restart</code>

<code>Shutting down MySQL.. SUCCESS!</code>

<code>Starting MySQL... SUCCESS!</code>

登入從伺服器

<code># 開啟中繼日志</code>

<code>relay-log=</code><code>/mydata/data/relay-log</code>

<code>relay-log-index=</code><code>/mydata/data/relay-log</code><code>.index</code>

<code>server-</code><code>id</code><code>=2</code>

<code># 關閉二進制日志</code>

<code>#log-bin=mysql-bin</code>

<code>Shutting down MySQL. SUCCESS!</code>

連接配接至主伺服器，并開始複制

<code># 檢視主伺服器正在使用的binlog和目前的position</code>

<code>mysql> show master status;</code>

<code># 在從伺服器連接配接主伺服器</code>

<code>mysql> change master to master_host=</code><code>'192.168.30.115'</code>

<code> </code><code>-> master_port=3306</code>

<code> </code><code>-> master_log_file=</code><code>'mysql-bin.000002'</code>

<code> </code><code>-> master_user=</code><code>'repl'</code>

<code> </code><code>-> master_password=</code><code>'123456'</code>

<code> </code><code>-> master_log_pos=107;</code>

<code>Query OK, 0 rows affected (0.49 sec)</code>

<code>mysql> start slave;</code>

<code>mysql> show slave status\G;</code>

<code> </code><code>Slave_IO_State: Waiting </code><code>for</code> <code>master to send event</code>

<code> </code><code>Master_Host: 192.168.30.115</code>

<code> </code><code>Master_User: repl</code>

<code> </code><code>Master_Port: 3306</code>

<code> </code><code>Connect_Retry: 60</code>

<code> </code><code>Master_Log_File: mysql-bin.000002</code>

<code> </code><code>Read_Master_Log_Pos: 107</code>

<code> </code><code>Relay_Log_File: relay-log.000002</code>

<code> </code><code>Relay_Log_Pos: 253</code>

<code> </code><code>Relay_Master_Log_File: mysql-bin.000002</code>

<code> </code><code>Slave_IO_Running: Yes</code>

<code> </code><code>Slave_SQL_Running: Yes</code>

<code> </code><code>Replicate_Do_DB:</code>

<code> </code><code>Replicate_Ignore_DB:</code>

<code> </code><code>Replicate_Do_Table:</code>

<code> </code><code>Replicate_Ignore_Table:</code>

<code> </code><code>Replicate_Wild_Do_Table:</code>

<code> </code><code>Replicate_Wild_Ignore_Table:</code>

<code> </code><code>Last_Errno: 0</code>

<code> </code><code>Last_Error:</code>

<code> </code><code>Skip_Counter: 0</code>

<code> </code><code>Exec_Master_Log_Pos: 107</code>

<code> </code><code>Relay_Log_Space: 403</code>

<code> </code><code>Until_Condition: None</code>

<code> </code><code>Until_Log_File:</code>

<code> </code><code>Until_Log_Pos: 0</code>

<code> </code><code>Master_SSL_Allowed: No</code>

<code> </code><code>Master_SSL_CA_File:</code>

<code> </code><code>Master_SSL_CA_Path:</code>

<code> </code><code>Master_SSL_Cert:</code>

<code> </code><code>Master_SSL_Cipher:</code>

<code> </code><code>Master_SSL_Key:</code>

<code> </code><code>Seconds_Behind_Master: 0</code>

<code>Master_SSL_Verify_Server_Cert: No</code>

<code> </code><code>Last_IO_Errno: 0</code>

<code> </code><code>Last_IO_Error:</code>

<code> </code><code>Last_SQL_Errno: 0</code>

<code> </code><code>Last_SQL_Error:</code>

<code> </code><code>Replicate_Ignore_Server_Ids:</code>

<code> </code><code>Master_Server_Id: 1</code>

以上資訊發現

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

說明主從伺服器配置成功了。

三，配置半同步複制

登入主伺服器

<code># 加載半同步複制子產品</code>

<code>mysql> </code><code>install</code> <code>plugin rpl_semi_sync_master soname </code><code>'semisync_master.so'</code><code>;</code>

<code># 開啟半同步複制功能</code>

<code>mysql> </code><code>set</code> <code>global rpl_semi_sync_master_enabled=1;</code>

<code># 設定主伺服器等待從伺服器多長時間，開始轉為異步複制</code>

<code>mysql> </code><code>set</code> <code>global rpl_semi_sync_master_timeout=1000;</code>

登入從伺服器

<code>mysql> </code><code>install</code> <code>plugin rpl_semi_sync_slave soname</code><code>'semisync_slave.so'</code><code>;</code>

<code>mysql> </code><code>set</code> <code>global rpl_semi_sync_slave_enabled=1;</code>

<code>mysql> stop slave IO_THREAD;</code>

<code>Query OK, 0 rows affected (0.07 sec)</code>

<code>mysql> start slave IO_THREAD;</code>

在Master和Slave的my.cnf中編輯

<code># On Master</code>

<code>[mysqld]</code>

<code>rpl_semi_sync_master_enabled=1</code>

<code>rpl_semi_sync_master_timeout=1000 </code><code># 1 second</code>

<code># On Slave</code>

<code>rpl_semi_sync_slave_enabled=1</code>

<code># 也可通過設定全局變量的方式來設定,如下：</code>

<code>set</code> <code>global rpl_semi_sync_master_enabled=1</code>

<code># 取消加載插件</code>

<code>mysql> UNINSTALL PLUGIN rpl_semi_sync_master;</code>

檢視從伺服器上的semi_sync是否開啟:

<code>mysql> show global status like </code><code>'rpl_semi%'</code><code>;</code>

<code>| Variable_name | Value |</code>

<code>| Rpl_semi_sync_master_clients | 1 |</code>

<code>| Rpl_semi_sync_master_net_avg_wait_time | 0 |</code>

<code>| Rpl_semi_sync_master_net_wait_time | 0 |</code>

<code>| Rpl_semi_sync_master_net_waits | 0 |</code>

<code>| Rpl_semi_sync_master_no_times | 0 |</code>

<code>| Rpl_semi_sync_master_no_tx | 0 |</code>

<code>| Rpl_semi_sync_master_status | ON |</code>

<code>| Rpl_semi_sync_master_timefunc_failures | 0 |</code>

<code>| Rpl_semi_sync_master_tx_avg_wait_time | 0 |</code>

<code>| Rpl_semi_sync_master_tx_wait_time | 0 |</code>

<code>| Rpl_semi_sync_master_tx_waits | 0 |</code>

<code>| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |</code>

<code>| Rpl_semi_sync_master_wait_sessions | 0 |</code>

<code>| Rpl_semi_sync_master_yes_tx | 0 |</code>

<code># 注意clients 變為1 ，證明主從半同步複制連接配接成功</code>

在主-從架構上，建議使用的配置：

主伺服器：

sync_binlog=1 # 主伺服器崩潰，任何一個事務送出之後就立即寫入到磁盤中的二進制檔案

innodb_flush_logs_at_trx_commit=1 #任何一個事物送出之後就立即寫入到磁盤中的日志檔案

從伺服器：

skip_slave_start=1 #重新開機從伺服器時不自動開啟slave程序

read_only=1 #設定從伺服器為隻讀模式

四，配置基于SSL主從複制

配置Master為CA伺服器

<code>[root@localhost ~]</code><code># cd /etc/pki/CA</code>

<code>[root@localhost CA]</code><code># (umask 077;openssl genrsa 2048 > private/cakey.pem)</code>

<code>[root@localhost CA]</code><code># openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650</code>

<code>[root@localhost CA]</code><code># mkdir certs crl newcerts</code>

<code>[root@localhost CA]</code><code># touch index.txt</code>

<code>[root@localhost CA]</code><code># echo 01 > serial</code>

在主伺服器上為mysql準備私鑰和證書

<code>[root@localhost ~]</code><code># mkdir /usr/local/mysql/ssl</code>

<code>[root@localhost ~]</code><code># cd /usr/local/mysql/ssl/</code>

<code>[root@localhost ssl]</code><code># (umask 077;openssl genrsa 1024 > mysql.key)</code>

<code># 這裡生成的證書請求要去CA的一緻</code>

<code>[root@localhost ssl]</code><code># openssl req -new -key mysql.key -out mysql.csr</code>

<code>[root@localhost ssl]</code><code># openssl ca -in mysql.csr -out mysql.crt</code>

<code>[root@localhost ssl]</code><code># cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/</code>

<code>[root@localhost ssl]</code><code># chown -R mysql.mysql /usr/local/mysql/ssl/</code>

在從伺服器上：

<code>[root@localhost ~]</code><code># chown -R mysql.mysql /usr/loca/mysql/ssl</code>

<code># 這裡填寫也要與CA一緻</code>

<code>root@localhost ssl]</code><code># openssl req -new -key mysql.key -out mysql.csr</code>

<code># 把證書申請傳到CA伺服器上</code>

<code>[root@localhost ssl]</code><code># scp mysql.csr [email protected]:/root/</code>

在master為slave簽署證書

<code>[root@localhost ~]</code><code># openssl ca -in mysql.csr -out mysql.crt</code>

<code>[root@localhost ~]</code><code># scp ./mysql.crt [email protected]:/usr/local/mysql/ssl</code>

<code>[root@localhost ~]</code><code># scp /etc/pki/CA/cacert.pem [email protected]:/usr/local/mysql/ssl/</code>

<code>#在從伺服器，設定ssl屬主屬組為mysql</code>

<code>[root@localhost ~]</code><code># chown -R mysql.mysql /usr/local/mysql/ssl/</code>

在主從伺服器上都開啟ssl功能：

<code>[root@localhost ~]</code><code># vi /etc/my.cnf</code>

<code># 添加如下幾行</code>

<code>ssl-ca=</code><code>/usr/local/mysql/ssl/cacert</code><code>.pem</code>

<code>ssl-cert=</code><code>/usr/local/mysql/ssl/mysql</code><code>.crt</code>

<code>ssl-key=</code><code>/usr/local/mysql/ssl/mysql</code><code>.key</code>

<code>[root@localhost ssl]</code><code># mysql</code>

<code>mysql> show variables like </code><code>'%ssl%'</code><code>;</code>

<code>ERROR 2006 (HY000): MySQL server has gone away</code>

<code>No connection. Trying to reconnect...</code>

<code>Connection </code><code>id</code><code>: 3</code>

<code>Current database: *** NONE ***</code>

<code>| Variable_name | Value |</code>

<code>| have_openssl | YES |</code>

<code>| ssl_ca | </code><code>/usr/local/mysql/ssl/cacert</code><code>.pem |</code>

<code>| ssl_capath | |</code>

<code>| ssl_cert | </code><code>/usr/local/mysql/ssl/mysql</code><code>.crt |</code>

<code>| ssl_cipher | |</code>

<code>| ssl_key | </code><code>/usr/local/mysql/ssl/mysql</code><code>.key |</code>

以上資訊顯示master伺服器ssl功能已經開啟

在slave伺服器執行上面同樣的操作，開啟ssl功能

在master伺服器删除原來的複制賬号，添加新的複制賬号

<code>mysql> delete from user where User=</code><code>'repl'</code><code>;</code>

<code>mysql> grant replication client,replication slave on *.* to </code><code>'sslrepl'</code><code>@</code><code>'192.168.30.%'</code> <code>identified by </code><code>'123456'</code> <code>require ssl;</code>

在slave伺服器連接配接master

<code>mysql> stop slave;</code>

<code> </code><code>-> master_log_file=</code><code>'mysql-bin.000005'</code>

<code> </code><code>-> master_user=</code><code>'sslrepl'</code>

<code> </code><code>-> master_log_pos=107</code>

<code> </code><code>-> master_ssl=1</code>

<code> </code><code>-> master_ssl_ca=</code><code>'/usr/local/mysql/ssl/cacert.pem'</code>

<code> </code><code>-> master_ssl_cert=</code><code>'/usr/local/mysql/ssl/mysql.crt'</code>

<code> </code><code>-> master_ssl_key=</code><code>'/usr/local/mysql/ssl/mysql.key'</code><code>;</code>

<code> </code><code>Master_User: sslrepl</code>

<code> </code><code>Master_Log_File: mysql-bin.000005</code>

<code> </code><code>Relay_Master_Log_File: mysql-bin.000005</code>

<code> </code><code>Master_SSL_Allowed: Yes</code>

<code> </code><code>Master_SSL_CA_File: </code><code>/usr/local/mysql/ssl/cacert</code><code>.pem</code>

<code> </code><code>Master_SSL_CA_Path: </code><code>/usr/local/mysql/ssl</code>

<code> </code><code>Master_SSL_Cert: </code><code>/usr/local/mysql/ssl/mysql</code><code>.crt</code>

<code> </code><code>Master_SSL_Key: </code><code>/usr/local/mysql/ssl/mysql</code><code>.key</code>

<code>Slave_IO_Running: Yes</code>

<code>Slave_SQL_Running: Yes</code>

<code>Master_SSL_Allowed: Yes 三個都輸出為</code><code>yes</code> <code>才表明配置成功</code>

五，在slave主機測試

<code>[root@localhost ~]</code><code># mysql -u sslrepl -h 192.168.30.115 -p123456 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/mysql.crt --ssl-key=/usr/local/mysql/ssl/mysql.key</code>

<code>mysql> \s</code>

<code>mysql Ver 14.14 Distrib 5.1.66, </code><code>for</code> <code>redhat-linux-gnu (x86_64) using readline 5.1</code>

<code>Connection </code><code>id</code><code>: 6</code>

<code>Current database:</code>

<code>Current user: [email protected]</code>

<code>SSL: Cipher </code><code>in</code> <code>use is DHE-RSA-AES256-SHA</code>

<code>Current pager: stdout</code>

<code>Using outfile: </code><code>''</code>

<code>Using delimiter: ;</code>

<code>Server version: 5.5.33-log MySQL Community Server (GPL)</code>

<code>Protocol version: 10</code>

<code>Connection: 192.168.30.115 via TCP</code><code>/IP</code>

<code>Server characterset: latin1</code>

<code>Db characterset: latin1</code>

<code>Client characterset: latin1</code>

<code>Conn. characterset: latin1</code>

<code>Uptime: 24 min 25 sec</code>

<code>Threads: 2 Questions: 23 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.015</code>

資訊：SSL: Cipher in use is DHE-RSA-AES256-SHA 表明連接配接是加密的

Mysql-5.5.33主從複制，半同步複制，以及基于ssl的複制已經配置完成

本文轉自ljl_19880709 51CTO部落格，原文連結：http://blog.51cto.com/luojianlong/1387371，如需轉載請自行聯系原作者

MySQL-5.5.33主從複制，半同步複制，以及基于SSL的複制

繼續閱讀

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

windows不能在本地計算機上運作oracleDbConsoleorcl

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

Bugku-WEB-web33

mysql使用source指令導入.sql檔案

Oracle 批量查詢傳入List 傳回List