mysq數據庫五

實驗：搭建主從級聯復制：（既中間一個服務器只作為主服務器和從服務器同步的工具）

拓撲結構圖：一個主服務器，中間一個中繼服務器（他的作用就是將主服務器上的數據同步過來，在同步到下面的從服務器上去，這樣可以避免主服務器同步多個從服務器時的負載過大）

以下為實驗具體過程：

實驗環境：selinux;iptables;

1 . 主服務器的配置
vim /etc/my.cnf? （添加的）

[mysqld]

log_bin ? ? (開啟二進制)
server_id=1 ?? （主服務器的id號）

配置好重啟服務：systemctl restart mariadb

2. show master logs; （查看此時主服務器在二進制日志所處的狀態）

3? .進入數據庫：grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’; （給從服務器授權并創建賬號）

4 .? 中繼服務器的配置文件的設置

vim /etc/my.cnf ? (添加的)

[mysqld]

server_id=2
log_bin
log_slave_updates
systemctl start mariadb? （配置好配置文件啟動數據庫的服務）

help change master to;（查詢配置從服務器的命令語句） （在數據庫中操作）

5? .CHANGE MASTER TO MASTER_HOST=’192.168.60.4′, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=245；

CHANGE MASTER TO
MASTER_HOST=’192.168.60.4′, ?? （主服務器的IP地址）
MASTER_USER=’lv’, ? ? ? ? ? ? ? ? ? ? ? ? ? ?? （剛剛授權用戶的名稱）
MASTER_PASSWORD=’centos’, ? ? ? ? ?? （授權用戶的密碼）
MASTER_PORT=3306,
MASTER_LOG_FILE=’mariadb-bin.000001′, ? ? （此位置文件查看剛才主服務器：show master logs;）
MASTER_LOG_POS=544, ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? (同上)
MASTER_CONNECT_RETRY=10

6 .?show slave status\G;? （查看本機此時的狀態）

7 . start slave ? ?? (開啟同步服務) ? ? ? ? ? ? reset slave (清掉復制過來的中繼日志)reset all (清掉所有當你在主的服務器地址設置錯誤或者其他方面設置錯誤時全部清掉后重新配置)

8 .? 在第三個從服務器上配置參數：

vim/etc/my.cnf

[mysql]

server_id=3
read_only ? (服務器只提供讀服務)

重啟服務

9 . 此時再次確認再主服務器上授權的用戶有沒有同步過來：select user,host,password from mysql.user

如果沒有同步過來在此授權同步給第三臺主機從服務器。

10 .CHANGE MASTER TO MASTER_HOST=’192.168.60.20′,MASTER_USER=’lv’,MASTER_PASSWORD=’centos’,MASTER_LOG_FILE=’mariadb-bin.000001′,MASTER_LOG_POS=245; （將中繼服務器作為主服務器進行同步設置）

11? .? start slave; ??show slave status\G; ? （啟動服務并查看狀態）

小結：至此完成級聯復制的搭建；如果主服務器宕機 再再中間服務器上首先stop slave,然后再清理：reset? slave? all ，之后重新指定就可以了。

 

 

 

實驗：實現主主服務器的搭建；（兩個都是主沒有從，倆個主服務器互相同步，一般不推薦使用）

考慮要點：自動增長id 配置一個節點使用奇數id

auto_increment_offset=1 開始點 （主鍵開始位置為1）

auto_increment_increment=2 增長幅度 （1，3，5，7的遞增）

另一個節點使用偶數id

auto_increment_offset=2 ? （主鍵的開始位置為2）

auto_increment_increment=2 ? （2，4，6，8 以增長幅度為2開始遞增）

如上述設置避免造成兩個主服務器數據的沖突。

主主復制的配置步驟： (1) 各節點使用一個惟一server_id (2) 都啟動binary log和relay log (3) 創建擁有復制權限的用戶賬號 (4) 定義自動增長id字段的數值范圍各為奇偶 (5) 均把對方指定為主節點，并啟動復制線程

實驗環境：selinux;iptables;

具體步驟如下：（思路：兩邊互相給對方授權，并互相同步信息，只是配置文件上略有不同而已）

1 . 修改第一個主服務器的配置文件：

vin /etc/my.cnf

server_id=1
log_bin
auto_increment_offset=1
auto_increment_increment=2

systemctl restart mariadb? （改完之后重啟服務）

2 .修改第二個主服務器的配置文件：

vin /etc/my.cnf
server_id=2
log_bin
auto_increment_offset=2
auto_increment_increment=2

systemctl restart mariadb （改完之后重啟服務）

3 .? 在一臺主機授權相互權限并創建用戶：(進入數據庫輸入下面命令)

show master logs;(查看此時二進制日志的位置狀態以便后面使用)

grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’;? （再第一臺主服務器上授權）

4 .在兩臺主機分別執行同步的任務：

CHANGE MASTER TO MASTER_HOST=’192.168.60.4′,MASTER_USER=’lv’,MASTER_PASSWORD=’centos’,MASTER_LOG_FILE=’mariadb-bin.000001′,MASTER_LOG_POS=245; (一臺主服務器上)

（’mariadb-bin.000001′,MASTER_LOG_POS=245; 此位置的具體信息應該差對方的show master logs;來獲得你想要的二進制日志的位置）

此時同步剛剛授權給自己的主機同步位置選在245位置既創建授權用戶前，同步之后自己有了相同的授權創建用戶的信息，否則需要這邊重新創建授權信息，這樣做省了這一步。

CHANGE MASTER TO MASTER_HOST=’192.168.60.20′,MASTER_USER=’lv’,MASTER_PASSWORD=’centos’,MASTER_LOG_FILE=’mariadb-bin.000001′,MASTER_LOG_POS=245; （另一臺主服務器上）

（’mariadb-bin.000002′,MASTER_LOG_POS=405; 此位置的具體信息應該差對方的show master logs;來獲得你想要的二進制日志的位置）

5 .兩邊主機都開啟同步服務：start slave;? 并查看此時的同步狀態show? slave status;

小結：上述實驗 是在兩臺服務器都是干凈系統的情況下實現的，現實中如果一臺機器是已經使用的，并且有數據，再搭建一臺新的服務器時，需要將舊服務器上的數據備份還原后再搭建主主系統。（還原只需還原最近一次全備份時的數據，以后的增量備份會在系統搭建好自動通過二進制日志進行同步）

 

 

實驗：實現半同步復制：

半同步復制的意思就是：當一臺主服務器帶多臺從服務器時，主從復制時等全部從同步后才提示同步完成，而半同步復制只保證一個從服務器同步完成就提示，提高效率。節省時間；

具體實現步驟：

1 . 首先實現一主一從服務器，并實現同步

主服務器修改配置文件：

vim/etc/my.cnf

server_id=1

log_bin

systemctl start? mariadb

2 . 從服務器修改配置文件：

vim/etc/my.cnf
server_id=2
log_bin
systemctl start mariadb

3 . 主服務器創建用戶并授權給從服務器用戶：
grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’;? (進入數據庫中輸入)

show master logs;? (查看此時二進制日志的位置狀態給從服務器同步時使用)

4 . 從服務器同步主服務器的數據：

CHANGE MASTER TO MASTER_HOST=’192.168.60.4′,MASTER_USER=’lv’,MASTER_PASSWORD=’centos’,MASTER_LOG_FILE=’mariadb-bin.000001′,MASTER_LOG_POS=245;

5 .開啟從服務器的同步功能：start? slave? ;查看同步的實時狀態：show slave status\G;

6 . 在主服務器上安裝插件：

install plugin rpl_semi_sync_master soname ‘semisync_master.so’;

show plugins;? （查看插件是否已經安裝好了；最后一行可以看見）

7 .? show global variables like ‘%semi%’; （查看插件的系統變量；沒有啟用）

show status like ‘%semi%’;? （查看插件的狀態變量；也是沒有啟用的）

set global rpl_semi_sync_master_enabled=on;? （啟用插件的系統變量）

8 . 在從服務器上安裝插件：

install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;

show plugins; （查看插件是否已經安裝好了；最后一行可以看見）

9 .? show global variables like ‘%semi%’; （查看插件的系統變量；沒有啟用）

show status like ‘%semi%’; （查看插件的狀態變量；也是沒有啟用的）

set global rpl_semi_sync_slave_enabled=on;? （啟用插件系統變量）

將插件變量開啟后再次查看show status like ‘%semi%’; （查看插件的狀態變量；也還是沒有開啟），我們要先stop slave；（停止同步服務）再start slave （再開啟同步服務）就應該可以了。

10 . 實測，在主服務器上添加更改信息，查看從服務器是否同步。到此已經完成了。

11 .?show status like ‘%semi%’;? （在服務器端查看狀態變量，可以看到詳細信息） 如下圖：

小結：此實驗是在一臺從服務器上安裝的插件實現的，實際工作中如果有多臺從服務器的話，每臺從服務器都需要按上述從服務器安裝插件和啟動插件變量。如果是級聯復制結構的話則只需要在中間的服務器上安裝從服務器插件就可以了，其他的從服務器就不需要安裝插件了。

 

 

MySQL復制

復制過濾器

兩種實現方式： (1) 主服務器僅向二進制日志中記錄與特定數據庫(特定表)相關的事件 問題：時間還原無法實現；不建議使用? （限定的二進制文件不會同步過來；這樣做會使二進制數據造成丟失）

(2) 從服務器SQL_THREAD在replay中繼日志中的事件時，僅讀取與特定數 據庫(特定表)相關的事件并應用于本地 問題：會造成網絡及磁盤IO浪費 （將所有的二進制日志文件都復制過來，但在同步到自己的數據庫時，會選擇不同步限制條件的內容）

復制過濾器在從服務器上的相關設置 ?

replicate_do_db= 指定復制庫的白名單 ?

replicate_ignore_db= 指定復制庫黑名單 ?（對整個庫進行忽略過濾）

replicate_do_table= 指定復制表的白名單 ?

replicate_ignore_table= 指定復制表的黑名單 ?（對單個表進行忽略過濾）

replicate_wild_do_table= foo%.bar% 支持通配符 ?

replicate_wild_ignore_table=

這里選擇第二種方法來模擬實現：

前提是實現主從服務器的搭建：

1 . 在從服務器上，進入數據庫后添加限制條件；

設定條件前需要先將同步服務停止：stop slave ;

set global replicate_ignore_db=dbbb;? (將dbbb表過濾掉，此表的增刪改都不同步)

show variables like ‘replicate_ignore_db’;? （設定完后查看限定條件的變量值）

2 . 將同步服務再次開啟：start slave;

3 . 到此已經設置完了，做個測試在主服務器上去對庫dbbb里的t1表進行添加內容，此時從服務器是看不到的。

小結：上述實驗中，主從同步一定要確保實現，然后dbbb為庫，t1為其下的一張表。

 

 

實戰：實現主從SSL加密復制（在遠程成數據傳輸備份時保證數據不會泄露）

實驗環境：selinux ;iptables;

拓撲結構圖：三臺機器；CA授權機；主服務器；從服務器；

具體步驟如下：

CA主機執行以下操作：

1 .mkdir /etc/my.cnf.d/ssl/ ? （在此目錄下建立新的文件夾ssl來存放證書文件，此文件夾為自定義目錄）

2 .?openssl genrsa 2048 > /etc/my.cnf.d/ssl/cakey.pem （生成私鑰文件并存放到規劃的目錄下）

3 . cd /etc/my.cnf.d/ssl/ (進入到此目錄下操作)

openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650? （生成自簽名的證書文件）

國家：CN

?。篵eijing

城市：beijing
公司：magedu.com
組織：cpt

地址：可以為空

4 . openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr （生成證書請求文件，）

國家：CN
?。篵eijing
城市：beijing
公司：magedu.com
組織：cpt

hostname: master.magedu.com

后面的可以不用寫；一下生成兩個文件，一個是私鑰文件另一個是請求文件。

5 . openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt（頒發證書文件）

6 .重復4和5 的操作給slave從服務器頒發證書文件；

openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr （如下圖）

openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt （給從服務器頒發證書）

7 .?/etc/my.cnf.d/ssl （此時文件夾下有如下文件）

cacert.pem? ；master.crt ；master.key ；slave.csr
cakey.pem ；master.csr ；slave.crt； slave.key

8 .?驗證證書是否合法的
openssl verify -CAfile cacert.pem master.crt slave.crt
9 . 將證書文件整個目錄都復制到主服務器和從服務器上去。

scp -r /etc/my.cnf.d/ssl/ 192.168.60.20:/etc/my.cnf.d

scp -r /etc/my.cnf.d/ssl/ 192.168.60.21:/etc/my.cnf.d

10 . 在主服務器上進入到復制過去的目錄下將用不到的文件都刪掉。

rm -rf cakey.pem master.csr slave.*

11 . 同上將從服務器的用不到的文件也給刪除。

rm -rf cakey.pem slave.csr master.*

12 .? 將主從服務器搭建起具體步驟找上面步驟就可以了

在master上
vim /etc/my.cnf
[mysqld]
log_bin
server_id=1
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key

systemctl restart mariadb? （重啟服務）

show variables like ‘%ssl%’;（查看變量）

13 .grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’ require ssl;（在主服務器上創建授權用戶并強制它使用加密的方式）

show master logs; （查看此時二進制日志的位置狀態）

14 . 此時如果從服務器想登錄主服務器的數據庫光輸入密碼是不行的。

mysql -ulv -pcentos -h192.168.60.4 –ssl-ca=cacert.pem –ssl-cert=slave.crt –ssl-key=slave.key

15 .從服務器的配置文件修改：
或者
vim /etc/my.cnf
[mysqld]
server_id=2
ssl

systemctl restart mariadb ? (重啟服務)

14 .進入數據庫查看ssl變量的狀態：show variables like ‘%ssl%’;? （應該是yes的）

15 . CHANGE MASTER TO MASTER_HOST=‘192.168.60.20’, MASTER_USER=’lv’,MASTER_PASSWORD=‘centos’,MASTER_PORT=3306,MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=406, MASTER_CONNECT_RETRY=10, MASTER_SSL=1, MASTER_SSL_CA = ‘/etc/my.cnf.d/ssl/cacert.pem’, MASTER_SSL_CERT = ‘/etc/my.cnf.d/ssl/slave.crt’, MASTER_SSL_KEY = ‘/etc/my.cnf.d/ssl/slave.key’;（在從服務器上同步主服務器設置）

show slave status\G;(查看狀態)

16 .?start slave （開啟同步）

 

 

 

實戰：實現主從高可用MHA （在主服務器宕機時，自動將從服務器切換成主服務器來使用）

此功能只是一次性的，切換之后，功能就停用了，如果想啟用需要再次開啟此功能。

結構拓撲圖：四臺主機；manager,master,slave1,slave2 （管理者，主服務器，兩個從服務器）

具體實驗步驟：

1 準備
selinux ,iptables ,time? （此實驗各臺服務器的時間要保持一致）

在vim /etc/ntp.conf? 添加server 172.20.0.1 ? iburst

systemctl? start ntpd

2 . 實現主從服務器的搭建

master:上

vim /etc/my.cnf

[mysqld]
server_id=1
log_bin

skip_name_resolve ? (跳過名字解析)

systemctl start mariadb? （重啟服務）

show master logs;? （查看此時的二進制日志狀態為后面從服務器同步時使用）

grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’; （授權用戶賬號)

3 .在slave1服務器上

vim /etc/my.cnf
[mysqld]
server_id=2
log_bin
read_only=1
relay_log_purge=0
skip_name_resolve=1

systemctl start mariadb? （重啟服務）

CHANGE MASTER TO MASTER_HOST=’192.168.60.20′, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=245; ? （連接同步）

start slave (開啟同步) ? ?? show slave status\G; (查看同步狀態)

4 在slave2服務器上：
[mysqld]
server_id=3
log_bin
read_only=1
relay_log_purge=0
skip_name_resolve=1
systemctl start mariadb （重啟服務）

CHANGE MASTER TO MASTER_HOST=’192.168.60.20′, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=394; ? ?（連接同步）

start slave (開啟同步) show slave status\G; (查看同步狀態)

5 . 在manager主機上：

實現基于key的驗證：

ssh-keygen? （生成私鑰對）

ssh-copy-id 192.168.60.4? （把自己的公鑰復制到自己的機器上）

cd ? .ssh? (進到此目錄下)

scp -pr .ssh 192.168.60.20:/root/

scp -pr .ssh 192.168.60.21:/root/

scp -pr .ssh 192.168.60.22:/root/ （將公鑰都復制到其他主機的家目錄下）

6 . 在master服務器上新建授權用戶：
grant all on *.* to mhauser@’192.168.60.%’ identified by ‘centos’;

（此賬號由于是現在建立的他會自動同步到其他兩個從服務器上）

7 . ?在管理節點上安裝兩個包： mha4mysql-manager ? mha4mysql-node ?（manager服務器上安裝）

在被管理節點安裝： mha4mysql-node? （主從服務器上安裝）

mha4mysql-manager-0.56-0.el6.noarch.rpm Public
Documents mha4mysql-node-0.56-0.el6.noarch.rpm? （網上下載兩個包）

yum install mha* ?? （安裝兩個包，要依賴于epel源）

8 . 在主從的三個服務器上安裝mha4mysql-manager-0.56-0.el6.noarch.rpm Public包

9.? 在manager服務器上創建一個文件：mkdir /etc/mha

vim /etc/mha/app1.conf
[server default]
user=mhauser ?? （此為在第6步創建授權的用戶）
password=centos
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=lv
repl_password=centos
ping_interval=1
[server1]
hostname=192.168.60.20 ? (主服務器的地址)
candidate_master=1
[server2]
hostname=192.168.60.21? （從服務器的地址）
candidate_master=1 ? ? （添加此行代表，主服務器宕機時此為備用的）
[server3]
hostname=192.168.60.22

10 ?Mha驗證和啟動 ? (檢查和驗證)

masterha_check_ssh –conf=/etc/mha/app1.conf? (探測ssh協議能否連接成功)

masterha_check_repl –conf=/etc/mha/app1.conf ? (檢查復制功能)

檢查復制功能時出現錯誤，提示lv用戶沒有復制的權限，添加上權限，在主服務器上：grant all on *.* to lv@’192.168.60.%’; 添加和后再查看一下權限：show grants for lv@’192.168.60.%’;

masterha_manager –conf=/etc/mha/app1.conf ? （開啟監控功能此命令為前臺運行命令，開啟后是不會退出的，以此執行詞條命令應該在終端上行，如果在遠程上執行，連接斷開會造成監控失?。?/p>

排錯日志： /data/mha/app1/manager.log

11 .只要不出錯監控會持續下去，現在模擬將主服務器掉電，看看會不會自動切換。

12 . 原來的主服務器修好之后，變成從服務器，需要再配置文件 /etc/my.cnf添加兩行：

read_only=1
relay_log_purge=0

重啟服務重新加載修改后的配置文件。

12 . 重新從現在的新主上獲得授權同步新主的文件；需要查看現在主服務器的二進制日志以便同步

CHANGE MASTER TO MASTER_HOST=’192.168.60.21‘, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000002′, MASTER_LOG_POS=245;

執行完了以后；start slave;? (開啟同步功能)

13 . 再次將manager主機上的程序跑起來：

masterha_manager –conf=/etc/mha/app1.conf? （里面的配置文件不需要更改）

14 . 這時再將現在的主服務器斷電，此時又應該切換到定義備用的主服務器上。

修復完成后，想重啟mha監控要刪除 /data/mastermha/app1/目錄下的complete結尾的文件，否則無法重啟監控