MySQL實(shí)現(xiàn)主從復(fù)制
主要內(nèi)容：
簡(jiǎn)介
主從復(fù)制
雙主復(fù)制
半同步復(fù)制
基于SSL認(rèn)證的主從服務(wù)
讀寫分離
實(shí)驗(yàn)環(huán)境
CentOS 7.3坦弟，使用MariaDB-5.5.52-1
CentOS 6.9戚揭，使用MySQL-5.1.73-8
簡(jiǎn)介
MySQL數(shù)據(jù)庫支持同步復(fù)制辛萍、單向、異步復(fù)制集群棉浸，在集群一個(gè)服務(wù)器作為主服務(wù)，而一個(gè)或多個(gè)服務(wù)器充當(dāng)從服務(wù)器截酷。用戶向主服務(wù)器寫入數(shù)據(jù)的操作涮拗，主服務(wù)執(zhí)行操作寫入數(shù)據(jù)并將操作指令記錄于二進(jìn)制日志文件中。從服務(wù)器向主服務(wù)器發(fā)出請(qǐng)求迂苛，獲取主服務(wù)器的二進(jìn)制日志的內(nèi)容三热，然后把獲取的內(nèi)容紀(jì)錄到自己的中繼日志中并按照中繼進(jìn)行重放操作。
主從復(fù)制的優(yōu)點(diǎn)：
加快用戶訪問數(shù)據(jù)的數(shù)據(jù)三幻，在使用復(fù)制可以使用一個(gè)從服務(wù)器執(zhí)行備份就漾，而不會(huì)干擾主服務(wù)器。在備份過程中主服務(wù)器可以繼續(xù)處理更新念搬。
注意：使用主從復(fù)制時(shí)抑堡，所有對(duì)表的寫入必須在主服務(wù)器上進(jìn)行。在從服務(wù)器上面的寫入操作是無法同步到其他服務(wù)器上面的朗徊，為了避免用戶將數(shù)據(jù)寫入從服務(wù)器首妖，需要在前端進(jìn)行調(diào)度實(shí)現(xiàn)讀寫分離。
mysql支持的復(fù)制類型：
基于語句的復(fù)制：在主服務(wù)器上執(zhí)行的SQL語句爷恳，在從服務(wù)器上執(zhí)行同樣的語句有缆。MySQL默認(rèn)采用基于語句的復(fù)制，效率比較高温亲。一旦發(fā)現(xiàn)沒法精確復(fù)制時(shí)棚壁，會(huì)自動(dòng)選著基于行的復(fù)制。
基于行的復(fù)制 ：把改變的內(nèi)容復(fù)制過去栈虚，而不是把命令在從服務(wù)器上執(zhí)行一遍. 從mysql5.0開始支持
混合類型的復(fù)制：默認(rèn)采用基于語句的復(fù)制袖外，一旦發(fā)現(xiàn)基于語句的無法精確的復(fù)制時(shí)，就會(huì)采用基于行的復(fù)制魂务。
主/從架構(gòu)：
異步復(fù)制：
半同步復(fù)制：
一主多從曼验；
一從一主；
級(jí)聯(lián)復(fù)制粘姜；
循環(huán)復(fù)制鬓照；
雙主復(fù)制；
一從多主：
配置主從復(fù)制：
注意事項(xiàng)
各個(gè)服務(wù)器之間的時(shí)間同步相艇；
復(fù)制的開始位置：
從0開始；
從備份中恢復(fù)到從節(jié)點(diǎn)后啟動(dòng)的復(fù)制纯陨，復(fù)制的起始點(diǎn)備份操作時(shí)主節(jié)點(diǎn)所處的日志文件及其事件位置坛芽；
主從服務(wù)器mysqld程序版本不一致留储？
從的版本號(hào)高于主的版本號(hào)；
主服務(wù)器：
[root@CentOS7.3 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=1 #設(shè)置當(dāng)前服務(wù)器的ID號(hào)
log_bin=log-bin #啟動(dòng)二進(jìn)制日志并指定文件名
skip_name_resolve=on #跳過主機(jī)名解析咙轩。在CentOS 6自帶的mysql后面的=on不用寫
innodb_file_per_table=on #innodb的每個(gè)表是用單獨(dú)的文件
啟動(dòng)服務(wù)
[root@CentOS7.3 ~]#systemctl start mariadb #啟動(dòng)數(shù)據(jù)庫
[root@CentOS7.3 ~]#mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.166.%' IDENTIFIED BY 'test123';
Query OK, 0 rows affected (0.00 sec) #創(chuàng)建一個(gè)用于從服務(wù)器連接復(fù)制主服務(wù)器的用戶
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
從服務(wù)器：
[root@centos7.3-1 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=2
relay_log=relay-log #啟用中繼日志获讳。在數(shù)據(jù)目錄下有一個(gè)relay-kog.info里面保存了當(dāng)前的中繼日志和位置會(huì)主節(jié)點(diǎn)二進(jìn)制文件的名字和位置。
read_only=on #禁止用戶寫入數(shù)據(jù)活喊，這一項(xiàng)的管理員和復(fù)制重放無效丐膝。
在從服務(wù)器啟動(dòng)read_only，但僅對(duì)非SUPER權(quán)限的用戶有效钾菊；
阻止所有用戶：
mysql> FLUSH TABLES WITH READ LOCK; 添加全局讀鎖
啟動(dòng)服務(wù)：
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.166.130',MASTER_USER='repluser',MASTER_PASSWORD='test123',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=498;
Query OK, 0 rows affected (0.03 sec)
設(shè)置主服務(wù)器的地址和用于連接主服務(wù)器的用戶名和密碼并指定從服務(wù)器的那個(gè)二進(jìn)制文件的那個(gè)位置開始復(fù)制帅矗。獲取主服務(wù)器當(dāng)前所在二進(jìn)制文件和位置執(zhí)行下面這條指令。
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 498 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
這設(shè)置會(huì)保存在數(shù)據(jù)目錄下的一個(gè)加master.info的文件內(nèi)
MariaDB [(none)]> START SLAVE; #啟動(dòng)SLAVE線程
#從服務(wù)器有兩個(gè)線程煞烫，IO_THREAD是復(fù)制線程浑此，SQL_THREAD是重放線程，不指定全部開啟滞详。
MariaDB [(none)]> SHOW SLAVE STATUS\G; #查看從服務(wù)器的狀態(tài)
Slave_IO_Running是復(fù)制線程凛俱，Slave_SQL_TRunning是重放線程。
測(cè)試
MariaDB [mydb]> create database mydb; #在主服務(wù)器上面添加數(shù)據(jù)
MariaDB [mydb]> use mydb;
MariaDB [mydb]> create table test1 (id int,neme char(100));
MariaDB [mydb]> insert into test1 values (1,'Xiao Ming');
MariaDB [mydb]> select * from test1;
+------+-----------+
| id | neme |
+------+-----------+
| 1 | Xiao Ming |
+------+-----------+
1 row in set (0.00 sec)
查看從服務(wù)器
image.png
在上圖中可以看到數(shù)據(jù)已經(jīng)同步到里從服務(wù)器
雙主復(fù)制：互為主從：兩個(gè)節(jié)點(diǎn)各自都要開啟binlog和relay log料饥；
配置：
1蒲犬、server_id必須要使用不同值；
2岸啡、均啟用binlog和relay log原叮；
3、存在自動(dòng)增長(zhǎng)id的表凰狞，為了使得id不相沖突篇裁，需要定義其自動(dòng)增長(zhǎng)方式；
服務(wù)啟動(dòng)后執(zhí)行如下兩步：
4赡若、都授權(quán)有復(fù)制權(quán)限的用戶賬號(hào)达布；
5、各把對(duì)方指定為主節(jié)點(diǎn)逾冬；
復(fù)制時(shí)應(yīng)該注意的問題：
自動(dòng)增長(zhǎng)id黍聂；
定義一個(gè)節(jié)點(diǎn)使用奇數(shù)id
auto_increment_offset=1 #自動(dòng)增長(zhǎng)ID初始數(shù)
auto_increment_increment=2 #每次增長(zhǎng)幾位數(shù)
另一個(gè)節(jié)點(diǎn)使用偶數(shù)id
auto_increment_offset=2
auto_increment_increment=2
先在第二臺(tái)服務(wù)器上面也創(chuàng)建一個(gè)用于連接復(fù)制的用戶
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.166.%' IDENTIFIED BY 'test123';
修改第一臺(tái)服務(wù)器配置文件
[mysqld]
server_id=1
log_bin=master-log
skip_name_resolve=on
innodb_file_per_table=on
relay_log=relay-log
auto_increment_offset=1
auto_increment_increment=2
重啟服務(wù)并為服務(wù)器指定主服務(wù)器
[root@CentOS7.3 ~]#systemctl restart mariadb
[root@CentOS7.3 ~]#mysql
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.166.132',MASTER_USER='repluser',MASTER_PASSWORD='test123',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=428;
Query OK, 0 rows affected (0.03 sec) #上面演示過了，這里就不演示如何獲取主服務(wù)器的二進(jìn)制日志信息了
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G;
image.png
修改第二臺(tái)服務(wù)器配置文件
[mysqld]
server_id=2
skip_name_resolve=on
innodb_file_per_table=on
relay_log=relay-log
log_bin=master-log
auto_increment_offset=2
auto_increment_increment=2
重啟服務(wù)并為服務(wù)器指定主服務(wù)器
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.166.130',MASTER_USER='repluser',MASTER_PASSWORD='test123',MASTER_LOG_FILE='master-log.000006',MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.07 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
測(cè)試
MariaDB [mydb]> CREATE TABLE test2 (id int auto_increment primary key,name char(100) not null,age int);
#新建一張表身腻，設(shè)置ID字段的值可以自動(dòng)增長(zhǎng)用于測(cè)試产还。
MariaDB [mydb]> desc test2;
+-------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(100) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-----------+------+-----+---------+----------------+
MariaDB [mydb]> insert into test2 (name,age) values ('test1',10),('test2',11);
Query OK, 2 rows affected (0.00 sec) #向表內(nèi)插入數(shù)據(jù)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [mydb]> select * from test2; #查詢test2的所有數(shù)據(jù)，id的增長(zhǎng)和我們?cè)O(shè)置的一樣嘀趟，從1開始每次增長(zhǎng)兩位數(shù)脐区。
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | test1 | 10 |
| 3 | test2 | 11 |
+----+-------+------+
2 rows in set (0.00 sec)
查看第二臺(tái)服務(wù)器
image.png
半同步復(fù)制，（同步加異步她按，一部分從服務(wù)器進(jìn)行同步復(fù)制牛隅，一部分從服務(wù)器進(jìn)行異步復(fù)制炕柔。）
這個(gè)功能需要安裝插件方可使用，mysql安裝包內(nèi)自帶了這個(gè)插件媒佣，mysql的插件文件的默認(rèn)存放路徑：/usr/lib64/mysql/plugins/
安裝插件的語法
mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name';
mysql> show pluggins; 查看mysql啟用的插件
半同步復(fù)制所需的插件：
semisync_master.so
semisync_slave.so
主節(jié)點(diǎn)安裝：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.06 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%'; #查看以rpl_semi開頭的全局設(shè)置
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF | #查看已經(jīng)安裝匕累，但是功能并未啟動(dòng)
| rpl_semi_sync_master_timeout | 10000 | #把數(shù)據(jù)寫入從服務(wù)器的超時(shí)時(shí)間，超過這個(gè)時(shí)間從服務(wù)器未響應(yīng)數(shù)據(jù)寫入默伍，從服務(wù)器降級(jí)為異步欢嘿。單位毫秒
| rpl_semi_sync_master_trace_level | 32 | #s設(shè)置跟蹤級(jí)別
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON; #啟動(dòng)插件
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON | #功能已經(jīng)啟用
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
從節(jié)點(diǎn)：
MariaDB [mydb]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF | #客戶端插件未啟動(dòng)
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
MariaDB [mydb]> SET @@global.rpl_semi_sync_slave_enabled=on; #啟動(dòng)插件
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
MariaDB [mydb]> STOP SLAVE IO_THREAD; #開啟同步功能后，從服務(wù)器線程不會(huì)自動(dòng)切換到同步也糊，需要手動(dòng)關(guān)閉再開啟線程炼蹦。
MariaDB [mydb]> START SLAVE IO_THREAD;
主節(jié)點(diǎn)：
MariaDB [(none)]> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 | #當(dāng)前主機(jī)有幾臺(tái)同步從主機(jī)
| Rpl_semi_sync_master_net_avg_wait_time | 0 | #網(wǎng)絡(luò)IO平均等待時(shí)間
| Rpl_semi_sync_master_net_wait_time | 0 | #等待時(shí)間
| Rpl_semi_sync_master_net_waits | 0 | #網(wǎng)絡(luò)IO網(wǎng)絡(luò)等待發(fā)生了幾次
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| 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 | #事務(wù)網(wǎng)絡(luò)IO網(wǎng)絡(luò)等待發(fā)生了幾次
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
測(cè)試
主服務(wù)器
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use mydb
Database changed
MariaDB [mydb]> create table test1;
MariaDB [mydb]> create table test1 (id int auto_increment primary key,name char(100),age int);
Query OK, 0 rows affected (0.25 sec)
MariaDB [mydb]> insert into test1 (name,age) values ('Xiao Ming',10);
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 674 | #平均時(shí)間
| Rpl_semi_sync_master_net_wait_time | 2022 | #等待時(shí)間
| Rpl_semi_sync_master_net_waits | 3 | #剛才執(zhí)行3次寫入
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 772 |
| Rpl_semi_sync_master_tx_wait_time | 2318 |
| Rpl_semi_sync_master_tx_waits | 3 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 3 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
注：因?yàn)閕nndb引擎是事務(wù)性引擎，而且默認(rèn)開啟每執(zhí)行一條語句自動(dòng)提交事務(wù)显设，所以上面顯示的總計(jì)數(shù)器的等待次數(shù)和事務(wù)計(jì)數(shù)器的值相同框弛。
從服務(wù)器
MariaDB [mydb]> select * from test1;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | Xiao Ming | 10 |
+----+-----------+------+
1 row in set (0.00 sec)
MariaDB [mydb]> stop slave io_thread; #關(guān)閉從服務(wù)器的IO線程
主服務(wù)器
MariaDB [mydb]> insert into test1 (name,age) values ('Xiao Hong',9); #在主服務(wù)器內(nèi)插入一行數(shù)據(jù)。
Query OK, 1 row affected (10.00 sec) #語句執(zhí)行時(shí)間
因?yàn)閯偛抨P(guān)閉了從服務(wù)器的IO線程捕捂，從服務(wù)器無法執(zhí)行這條語句瑟枫，就無法顯示語句執(zhí)行完成。主服務(wù)器只有等過了超時(shí)時(shí)間指攒，判斷從服務(wù)器故障慷妙，把從服務(wù)器降級(jí)為異步，才能反饋顯示語句執(zhí)行成功允悦。
基于SSL復(fù)制的復(fù)制
- 創(chuàng)建證書
[root@CentOS7.3 ~]#(umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem ) #密鑰默認(rèn)是1024位加密膝擂，也可以在命令尾部指定
自簽名證書
[root@CentOS7.3 ~]#touch /etc/pki/CA/index.txt #創(chuàng)建證書索引文件
[root@CentOS7.3 ~]#echo 01 > /etc/pki/CA/serial #指定證書頒發(fā)的序號(hào)，序號(hào)必須是兩位16進(jìn)制數(shù)
[root@CentOS7.3 ~]#echo 01 > /etc/pki/CA/crlnumber #指定證書吊銷的序號(hào)
[root@CentOS7.3 ~]#touch /etc/pki/CA/private/.rand #創(chuàng)建偽隨機(jī)數(shù)文件
[root@CentOS7.3 ~]#openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -days 7300 -out /etc/pki/CA/cacert.pem
#創(chuàng)建自簽證書 -new：生成新證書簽署請(qǐng)求隙弛；-x509：生成自簽格式證書架馋，專用于創(chuàng)建私有CA時(shí)；-key：生成請(qǐng)求時(shí)用到的私有文件路徑
# -out：生成的請(qǐng)求文件路徑全闷；如果自簽操作將直接生成簽署過的證書叉寂；-days：證書的有效時(shí)長(zhǎng)，單位是day总珠；
Country Name (2 letter code) [XX]:CN #填寫CA所在的國家屏鳍，格式為國家代碼
State or Province Name (full name) []:xxxxxx #填寫所在的州或省
Locality Name (eg, city) [Default City]:xxxxxx #填寫所在的城市
Organization Name (eg, company) [Default Company Ltd]:CentOS #機(jī)構(gòu)名稱
Organizational Unit Name (eg, section) []:CA #部門名稱
Common Name (eg, your name or your server's hostname) []: #通用名稱，服務(wù)器的主機(jī)名
Email Address []: #郵箱地址
給主服務(wù)器MariaDB頒發(fā)證書
密鑰和證書申請(qǐng)文件
[root@CentOS7.3 ~]#mkdir /var/lib/mysql/ssl
[root@CentOS7.3 ~]#(umask 077;openssl genrsa -out /var/lib/mysql/ssl/master.key) #在master生成私鑰
[root@CentOS7.3 ~]#openssl req -new -key /var/lib/mysql/ssl/master.key -out /var/lib/mysql/ssl/master.pem
#生成證書申請(qǐng)文件
Country Name (2 letter code) [XX]:CN #國家局服，必須和CA相同
State or Province Name (full name) []:HeNan #州或者省份钓瞭，必須和CA相同
Locality Name (eg, city) [Default City]:zz #城市，可以自定義
Organization Name (eg, company) [Default Company Ltd]:CAServer #機(jī)構(gòu)名必須和CA相同
Organizational Unit Name (eg, section) []:test #部門名稱淫奔，可以自定義
Common Name (eg, your name or your servers hostname) []:test.com #服務(wù)器名稱
Email Address []: #郵箱地址可以忽略
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:admin #設(shè)置密碼加密
An optional company name []:admin
CA頒發(fā)證書
[root@CentOS7.3 ~]#openssl ca -in /var/lib/mysql/ssl/master.pem -out /var/lib/mysql/ssl/master.crt -days 365 #CA簽發(fā)證書
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: Jul 17 16:14:46 2017 GMT
Not After : Jul 17 16:14:46 2018 GMT
Subject:
countryName = CN
stateOrProvinceName = HeNan
organizationName = xxxxxx
organizationalUnitName = xxxxxx
commonName = xxxxxx
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
3B:F1:1B:6B:88:C2:17:35:19:2E:35:90:C6:22:6E:69:10:FF:B4:02
X509v3 Authority Key Identifier:
keyid:60:3C:95:CB:A1:63:DC:0C:FC:1B:85:22:B3:4D:FD:FB:3B:5E:A4:B8
Certificate is to be certified until Jul 17 16:14:46 2018 GMT (365 days)
Sign the certificate? [y/n]:y #證書的有效期到xxx是否簽發(fā)
1 out of 1 certificate requests certified, commit? [y/n]y #是否提交認(rèn)證
Write out database with 1 new entries
Data Base Updated
修改證書和密鑰的屬主山涡、屬組
[root@CentOS7.3 ~]#chown mysql.mysql /var/lib/mysql/ssl/ -R
給從服務(wù)器頒發(fā)證書
創(chuàng)建密鑰和證書申請(qǐng)文件
[root@centos7.3-1 ~]#mkdir /var/lib/mysql/ssl/ #在Mariadb工作目錄創(chuàng)建一個(gè)ssl目錄用于存放證書和私鑰
[root@centos7.3-1 ~]#(umask 077;openssl genrsa -out /var/lib/mysql/ssl/slave.key) #在從服務(wù)器端生成私鑰
Generating RSA private key, 1024 bit long modulus
..............................++++++
.......++++++
e is 65537 (0x10001)
[root@centos7.3-1 ~]#openssl req -new -key /var/lib/mysql/ssl/slave.key -out /var/lib/mysql/ssl/slave.pem #生成證書申請(qǐng)文件
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 #國家，必須和CA相同
State or Province Name (full name) []:HeNan #州或者省份，必須和CA相同
Locality Name (eg, city) [Default City]:zz #城市鸭丛，可以自定義
Organization Name (eg, company) [Default Company Ltd]:CAServer #機(jī)構(gòu)名必須和CA相同
Organizational Unit Name (eg, section) []:test #部門名稱霍殴，可以自定義
Common Name (eg, your name or your servers hostname) []:test.com #服務(wù)器名稱
Email Address []: #郵箱地址可以忽略
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:admin #設(shè)置密碼加密
An optional company name []:admin
將證書申請(qǐng)文件發(fā)送到CA服務(wù)器
[root@centos7.3-1 ~]#scp /var/lib/mysql/ssl/slave.pem 192.168.166.130:/etc/pki/CA/
CA頒發(fā)證書
[root@CentOS7.3 ~]#openssl ca -in /etc/pki/CA/slave.pem -out /etc/pki/CA/certs/slave.crt -days 365 #CA頒發(fā)證書
Check that the request matches the signature
ignature ok
Certificate Details:
Serial Number: 2 (0x2)
Validity
Not Before: Sep 17 04:17:06 2017 GMT
Not After : Sep 17 04:17:06 2018 GMT
Subject:
countryName = CN
stateOrProvinceName = HeNan
organizationName = xxxxx
organizationalUnitName = slave.xxxxxx.com
commonName = slave
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
8E:F3:29:4B:56:72:CE:0F:F0:88:31:21:49:59:67:06:1D:52:63:15
X509v3 Authority Key Identifier:
keyid:90:85:07:D5:53:B4:25:64:0C:D2:10:DA:6D:23:70:3A:49:78:AF:F4
Certificate is to be certified until Jul 17 16:14:46 2018 GMT (365 days)
Sign the certificate? [y/n]:y #證書的有效期到xxx是否簽發(fā)
1 out of 1 certificate requests certified, commit? [y/n]y #是否提交認(rèn)證
Write out database with 1 new entries
Data Base Updated
把從服務(wù)器和CA證書發(fā)送給從服務(wù)器
[root@CentOS7.3 ~]#scp /etc/pki/CA/certs/slave.crt 192.168.166.132:/var/lib/mysql/ssl/
在從服務(wù)器上面修改屬主、屬組和權(quán)限
[root@centos7.3-1 mysql]#chown mysql.mysql ssl/ -R
[root@centos7.3-1 mysql]#cd ssl/
[root@centos7.3-1 ssl]#chmod 600 *
修改master的配置文件
[root@CentOS7.3 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld] #找到這一項(xiàng)在其下面添加如下選項(xiàng)
log-bin=master-bin #開啟二進(jìn)制日志并指定名字
sync_binlog = 1 #二進(jìn)制日志文件同步寫入磁盤
server-id = 1 #服務(wù)器的ID
innodb_flush_log_at_trx_commit=1
skip_name_resolve=on
ssl
ssl_ca =/etc/pki/CA/cacert.pem #CA的證書
ssl_cert= /var/lib/mysql/ssl/master.crt #master的證書
ssl_key = /var/lib/mysql/ssl/master.key #master的密鑰
啟動(dòng)主服務(wù)器
[root@CentOS7.3 ssl]#systemctl start mariadb
MariaDB [(none)]> show variables like '%ssl%'; #查看ssl信息
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | /etc/pki/CA/cacert.pem |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/ssl/master.crt |
| ssl_cipher | |
| ssl_key | /var/lib/mysql/ssl/master.pem |
+---------------+-------------------------------+
MariaDB [(none)]> grant replication slave,replication client on *.* to 'ssluser'@'192.168.166.%' require ssl;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
修改從服務(wù)器的配置文件
[root@CentOS7.3-1 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld] #找到這一項(xiàng)在其下面添加如下選項(xiàng)
relay-log=relay-log #開啟中繼日志并指定名字
server-id = 2 #服務(wù)器的ID
innodb_flush_log_at_trx_commit=1
skip_name_resolve=on
ssl
ssl_ca =/var/lib/mysql/ssl/cacert.pem #CA的證書
ssl_cert= /var/lib/mysql/ssl/slave.crt #master的證書
ssl_key = /var/lib/mysql/ssl/slave.key #master的密鑰
啟動(dòng)msyql
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.166.130',MASTER_USER='ssluser',MASTER_LOG_FILE='master-log.000004',MASTER_LOG_POS=485,MASTER_SsL=1,MASTER_SSL_CA='/app/ssl/cacert.pem',MASTER_SSL_CERT='/app/ssl/slave.crt',master_ssl_key='/aap/ssh/slave.key';
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
讀寫分離
ProxySQL
ProxySQL是一個(gè)高性能系吩，高可用性的協(xié)議感知代理，可以用于MySQL和Percona Server和MariaDB妒蔚。
官方網(wǎng)站：http://www.proxysql.com/
雙主或多主模型是無須實(shí)現(xiàn)讀寫分離穿挨，僅需要負(fù)載均衡：haproxy, nginx, lvs, ...
實(shí)驗(yàn)環(huán)境
ProxySQL：CentOS 7.3-1
內(nèi)網(wǎng)ip：192.168.166.132
服務(wù)IP：172.16.251.77
主服務(wù)器：CentOS 7.3-2
IP地址：192.168.166.133
從服務(wù)器：
CentOS 6.9 ：IP地址：192.168.166.129
CentOS 6.9-1：IP地址：192.168.166.131
ProxySQL安裝
官方網(wǎng)站下載安裝包，官網(wǎng)提供rpm包
[root@centos7.3-1 ~]#yum -y install ./proxysql-1.4.2-1-centos7.x86_64.rpm
主要文件
/etc/init.d/proxysql #啟動(dòng)腳本
/etc/proxysql.cnf #配置文件
/usr/bin/proxysql #主程序文件
/usr/share/proxysql/tools/proxysql_galera_checker.sh #和PXC有關(guān)腳本
/usr/share/proxysql/tools/proxysql_galera_writer.pl
配置文件示例：
datadir="/var/lib/proxysql" #文件保存位置
admin_variables= #proxy管理配置端
{
admin_credentials="admin:admin" #管理proxysql的賬戶和密碼
mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" #管理監(jiān)聽地址
}
mysql_variables= #msyql代理服務(wù)配置項(xiàng)
{
threads=4 #線程數(shù)
max_connections=2048 #最大連接數(shù)
default_query_delay=0 #默認(rèn)查詢延遲
default_query_timeout=36000000 #默認(rèn)查詢超時(shí)時(shí)間
have_compress=true #是否啟用壓縮
poll_timeout=2000 #輪詢超時(shí)時(shí)長(zhǎng)
interfaces="0.0.0.0:3306;/tmp/mysql.sock" #代理服務(wù)監(jiān)聽地址
default_schema="" #用戶連接數(shù)據(jù)庫默認(rèn)進(jìn)入數(shù)據(jù)庫
stacksize=1048576 #棧大小
server_version="5.5.30" #模擬的服務(wù)器版本
connect_timeout_server=3000 #連接的超時(shí)時(shí)間
monitor_history=600000 #監(jiān)控歷史的保留時(shí)長(zhǎng)
monitor_connect_interval=60000 #監(jiān)控后端主機(jī)健康狀態(tài)
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers = #mysql后端主機(jī)配置端
(
{
address = "172.18.0.67" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 0 # no default, required #后端主機(jī)所屬的組
status = "ONLINE" # default: ONLINE #指定后端服務(wù)器的狀態(tài)肴盏，ONLINE表示在線
weight = 1 # default: 1 #權(quán)重
compression = 0 # default: 0 #是否啟用壓縮科盛，1為啟用。 max_replication_lag =0 #默認(rèn)為0菜皂，如果設(shè)置了值贞绵，從服務(wù)器落后主服務(wù)器的時(shí)間達(dá)到這個(gè)值，這個(gè)從服務(wù)器將被下線恍飘。
},
{
address = "172.18.0.68"
port = 3306
hostgroup = 1
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
},
{
address = "172.18.0.69"
port = 3306
hostgroup = 1
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
}
#{ address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },
#還可以是使用這樣的格式
)
mysql_users: #proyx連接后端主機(jī)進(jìn)行管理的用戶
(
{
username = "root" #用戶
password = "admi123" #密碼
default_hostgroup = 0 #所屬組
max_connections=1000 #最大連接數(shù)
default_schema="mydb" #用戶連接上去的默認(rèn)庫
active = 1 #是否是活動(dòng)狀態(tài)
}
)
mysql_query_rules: #代理請(qǐng)求
(
)
scheduler= #調(diào)度器
(
)
mysql_replication_hostgroups= #復(fù)制的主機(jī)組榨崩，主要用于高可用
(
{
writer_hostgroup=0 #可寫的組
reader_hostgroup=1 #只讀的組
comment="test repl 1" #注釋信息
}
)
注意：在每個(gè)配置端的最后一個(gè)配置的不要加上“，”號(hào)
后端服務(wù)器
主節(jié)點(diǎn)
MariaDB [(none)]> GRANT ALL ON *.* TO 'proxysql'@'192.168.166.%' IDENTIFIED BY 'admin';
測(cè)試
啟動(dòng)
service proxysql start #開啟proxysql
客戶端連接測(cè)試
[root@CentOS7.3 ~]#mysql -h 192.168.166.132 -u proxysql -padmin
[root@CentOS7.3 ~]#mysql -h 192.168.166.132 -u proxysql -padmin
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.30 (ProxySQL)
MySQL [mydb]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MySQL [mydb]> create table test1 (id int,name char(100));
Query OK, 0 rows affected (0.16 sec)
MySQL [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
從服務(wù)器
mysql> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
復(fù)制過濾器：
僅復(fù)制有限一個(gè)或幾個(gè)數(shù)據(jù)庫相關(guān)的數(shù)據(jù)章母，而非所有母蛛；由復(fù)制過濾器進(jìn)行；
有兩種實(shí)現(xiàn)思路：
(1) 主服務(wù)器
主服務(wù)器僅向二進(jìn)制日志中記錄有關(guān)特定數(shù)據(jù)庫相關(guān)的寫操作乳怎；
問題：其它庫的time-point recovery將無從實(shí)現(xiàn)彩郊；時(shí)間點(diǎn)恢復(fù)無法實(shí)現(xiàn)。
binlog_do_db= 白名單
binlog_ignore_db= 黑名單蚪缀，二者不要同時(shí)使用
(2) 從服務(wù)器
從服務(wù)器的SQL THREAD僅重放關(guān)注的數(shù)據(jù)庫或表相關(guān)的事件秫逝，并將其應(yīng)用于本地；
問題：網(wǎng)絡(luò)IO和磁盤IO询枚；后綴為DB的為庫列表违帆，table為表列表
Replicate_Do_DB= 庫白名單
Replicate_Ignore_DB=
Replicate_Do_Table= 表級(jí)白名單
Replicate_Ignore_Table= 黑名單
Replicate_Wild_Do_Table= 可以使用通配符進(jìn)行通配
Replicate_Wild_Ignore_Table= 可以使用通配符匹配
復(fù)制的監(jiān)控和維護(hù)：
(1) 清理日志：PURGE
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr };
PURGE BINARY LOGS TO 'mysql-bin.001' 清理這個(gè)文件之前的二進(jìn)制日志文件
PURGE BINARY LOGS BEFORE '2017-01-01' 清理這個(gè)時(shí)間點(diǎn)之前的二進(jìn)制日志文件
(2) 復(fù)制監(jiān)控
MASTER:
SHOW MASTER STATUS;
SHOW BINLOG EVENTS;
SHOW BINARY LOGS;
SLAVE:
SHOW SLAVE STATUS;
判斷從服務(wù)器是否落后于主服務(wù)器：
Seconds_Behind_Master: 0
(3) 如何確定主從節(jié)點(diǎn)數(shù)據(jù)是否一致？
通過表的CHECKSUM檢查哩盲；
使用percona-tools中pt-table-checksum前方；
(4) 主從數(shù)據(jù)不一致時(shí)的修復(fù)方法？
重新復(fù)制廉油；