MySQL 8.0.17 clone plugin 本地遠(yuǎn)程備份，并搭建主從總結(jié)

? ? ? ?克隆插件允許在本地或從遠(yuǎn)程MySQL服務(wù)器實(shí)例克隆數(shù)據(jù)。也可以看作一種備份方法，所以我歸到備份整理里面了。 克隆數(shù)據(jù)是InnoDB中存儲(chǔ)的數(shù)據(jù)的物理快照，包括模式，表，表空間和數(shù)據(jù)字典元數(shù)據(jù)。 克隆的數(shù)據(jù)包含一個(gè)功能齊全的數(shù)據(jù)目錄，允許使用克隆插件進(jìn)行MySQL服務(wù)器配置。

? ? ? ?本地克隆操作

本地克隆操作將啟動(dòng)克隆操作的 MySQL 服務(wù)器實(shí)例中的數(shù)據(jù)克隆到同服務(wù)器或同節(jié)點(diǎn)上的一個(gè)目錄里（要注意這個(gè)配置secure_file_priv）。這個(gè)功能讓我想起tukodb的hotbackup，我前面文章有寫到。

? ? ? ?遠(yuǎn)程克隆

默認(rèn)情況下，遠(yuǎn)程克隆操作會(huì)刪除接受者(recipient)數(shù)據(jù)目錄中的數(shù)據(jù)，并將其替換為捐贈(zèng)者(donor)的克隆數(shù)據(jù)。（可選）您也可以將數(shù)據(jù)克隆到接受者的其他目錄，以避免刪除現(xiàn)有數(shù)據(jù)。

遠(yuǎn)程克隆操作和本地克隆操作克隆的數(shù)據(jù)沒有區(qū)別，數(shù)據(jù)是相同的。

克隆插件支持復(fù)制。除克隆數(shù)據(jù)外，克隆操作還從捐贈(zèng)者中提取并傳輸復(fù)制位置信息，并將其應(yīng)用于接受者，從而可以使用克隆插件來配置組復(fù)制或主從復(fù)制。使用克隆插件進(jìn)行配置比復(fù)制大量事務(wù)要快得多，效率更高。

這個(gè)功能讓我想起postgresql 的pg_basebackup，以后的文章會(huì)寫

1、兩臺(tái)機(jī)器分別安裝MySQL

192.168.56.16???es3
192.168.56.15???es2

[root@es2?~]#?yum?-y?install?mysql-community-*
[root@es3?~]#?yum?-y?localinstall?mysql-community-*

2、修改配置文件

[root@es2?~]#?grep?-Ev?"^$|^[#;]"?/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
gtid-mode?=?ON
enforce-gtid-consistency?=?ON
log-slave-updates?=?ON
server-id=1
[root@es2?~]#

[root@es3?~]#?grep?-Ev?"^$|^[#;]"?/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
gtid-mode?=?ON
enforce-gtid-consistency?=?ON
log-slave-updates?=?ON
server-id=2
[root@es3?~]#

3、安裝clone 插件，并創(chuàng)建克隆用戶和復(fù)制用戶

mysql>?INSTALL?PLUGIN?clone?SONAME?'mysql_clone.so';

mysql>?CREATE?USER?clone_user@'%'?identified?by?'iwSeuFagt0&31';

mysql>?GRANT?BACKUP_ADMIN?ON?*.*?TO?'clone_user';

mysql>?CREATE?USER?repl_user@'%'?identified?by?'iwSeuFagt0&31';

mysql>??grant?replication?slave?on?*.*?to?repl_user;

mysql>?flush?privileges;

4、查看secure_file_priv并開始第一次本地克隆，注意/var/lib/mysql-files/clone_dir1中的clone_dir1要不存在

mysql>?show?variables?like?'secure_file_priv';
+------------------+-----------------------+
|?Variable_name????|?Value?????????????????|
+------------------+-----------------------+
|?secure_file_priv?|?/var/lib/mysql-files/?|
+------------------+-----------------------+
1?row?in?set?(0.00?sec)

mysql>??CLONE?LOCAL?DATA?DIRECTORY?=?'/var/lib/mysql-files/clone_dir1';

5、通過創(chuàng)建不同標(biāo)識(shí)，進(jìn)行多次克隆測(cè)試

mysql>?create?database?after_clone1;
mysql>??CLONE?LOCAL?DATA?DIRECTORY?=?'/var/lib/mysql-files/clone_dir2';
mysql>?create?database?after_clone2;

6、切換克隆數(shù)據(jù)目錄，查看相關(guān)標(biāo)識(shí)情況

mysql>?show?variables?like?'datadir';
+---------------+----------------------------------+
|?Variable_name?|?Value????????????????????????????|
+---------------+----------------------------------+
|?datadir???????|?/var/lib/mysql-files/clone_dir1/?|
+---------------+----------------------------------+
1?row?in?set?(0.01?sec)

mysql>?SELECT?BINLOG_FILE,?BINLOG_POSITION?FROM?performance_schema.clone_status;
+---------------+-----------------+
|?BINLOG_FILE???|?BINLOG_POSITION?|
+---------------+-----------------+
|?binlog.000001?|????????????1741?|
+---------------+-----------------+
1?row?in?set?(0.00?sec)

mysql>?SELECT?@@GLOBAL.GTID_EXECUTED;
+------------------------------------------+
|?@@GLOBAL.GTID_EXECUTED???????????????????|
+------------------------------------------+
|?dbda28c9-c970-11e9-b268-0800275c8ec3:1-6?|
+------------------------------------------+
1?row?in?set?(0.00?sec)

mysql>?exit

mysql>?show?variables?like?'datadir';
+---------------+----------------------------------+
|?Variable_name?|?Value????????????????????????????|
+---------------+----------------------------------+
|?datadir???????|?/var/lib/mysql-files/clone_dir2/?|
+---------------+----------------------------------+
1?row?in?set?(0.01?sec)

mysql>?SELECT?BINLOG_FILE,?BINLOG_POSITION?FROM?performance_schema.clone_status;
+---------------+-----------------+
|?BINLOG_FILE???|?BINLOG_POSITION?|
+---------------+-----------------+
|?binlog.000001?|????????????1950?|
+---------------+-----------------+
1?row?in?set?(0.00?sec)

mysql>?SELECT?@@GLOBAL.GTID_EXECUTED;
+------------------------------------------+
|?@@GLOBAL.GTID_EXECUTED???????????????????|
+------------------------------------------+
|?dbda28c9-c970-11e9-b268-0800275c8ec3:1-7?|
+------------------------------------------+
1?row?in?set?(0.00?sec)

mysql>?exit

mysql>?show?variables?like?'datadir';
+---------------+-----------------+
|?Variable_name?|?Value???????????|
+---------------+-----------------+
|?datadir???????|?/var/lib/mysql/?|
+---------------+-----------------+
1?row?in?set?(0.01?sec)

mysql>?SELECT?BINLOG_FILE,?BINLOG_POSITION?FROM?performance_schema.clone_status;
Empty?set?(0.00?sec)

mysql>?SELECT?@@GLOBAL.GTID_EXECUTED;
+------------------------------------------+
|?@@GLOBAL.GTID_EXECUTED???????????????????|
+------------------------------------------+
|?dbda28c9-c970-11e9-b268-0800275c8ec3:1-8?|
+------------------------------------------+
1?row?in?set?(0.00?sec)

mysql>?exit

遠(yuǎn)程克隆

7、遠(yuǎn)程克隆數(shù)據(jù)接收端環(huán)境準(zhǔn)備，創(chuàng)建接收者用戶和授權(quán)。?CLONE_ADMIN權(quán)限 = BACKUP_ADMIN權(quán)限 + SHUTDOWN權(quán)限。SHUTDOWN僅限允許用戶shutdown和restart mysqld。授權(quán)不同是因?yàn)椋邮苷咝枰猺estart mysqld，特別提示，本地克隆用戶使用的是BACKUP_ADMIN即可。這里我們將上面創(chuàng)建的本地克隆用戶作為捐贈(zèng)者用戶使用。

mysql>?INSTALL?PLUGIN?clone?SONAME?'mysql_clone.so';
Query?OK,?0?rows?affected?(0.09?sec)

mysql>?CREATE?USER?clone_user@'%'?identified?by?'iwSeuFagt0&31';
Query?OK,?0?rows?affected?(0.32?sec)

mysql>?GRANT?CLONE_ADMIN?on?*.*?to??clone_user;
Query?OK,?0?rows?affected?(0.01?sec)

mysql>?flush?privileges;

8、嘗試遠(yuǎn)程克隆，注意clone_valid_donor_list設(shè)置，當(dāng)然不設(shè)置會(huì)提醒

mysql>?set?global?clone_valid_donor_list?='192.168.56.15:3306';
mysql>?clone?instance?from?clone_user@'192.168.56.15':3306?????
????->??identified?by?'iwSeuFagt0&31';
????
mysql>?show?databases;
+--------------------+
|?Database???????????|
+--------------------+
|?after_clone1???????|
|?after_clone2???????|
|?information_schema?|
|?mysql??????????????|
|?performance_schema?|
|?sys????????????????|
+--------------------+
mysql>?SELECT?BINLOG_FILE,?BINLOG_POSITION?FROM?performance_schema.clone_status;
+---------------+-----------------+
|?BINLOG_FILE???|?BINLOG_POSITION?|
+---------------+-----------------+
|?binlog.000002?|?????????????195?|
+---------------+-----------------+
1?row?in?set?(0.01?sec)

mysql>?SELECT?@@GLOBAL.GTID_EXECUTED;
+------------------------------------------+
|?@@GLOBAL.GTID_EXECUTED???????????????????|
+------------------------------------------+
|?dbda28c9-c970-11e9-b268-0800275c8ec3:1-8?|
+------------------------------------------+
1?row?in?set?(0.00?sec)

9、嘗試做主從復(fù)制，注意修改主庫(kù)的密碼驗(yàn)證插件

主庫(kù)

?alter?USER?repl_user@'%'?identified??WITH?mysql_native_password?by?'iwSeuFagt0&31'?;

從庫(kù)

mysql>?CHANGE?MASTER?TO?MASTER_HOST?=?'192.168.56.15',?
mysql>??MASTER_PORT?=?3306,MASTER_AUTO_POSITION?=?1;
mysql>?START?SLAVE?USER?=?'repl_user'?PASSWORD?=??'iwSeuFagt0&31'?;
mysql>?show?slave?status\G
***************************?1.?row?***************************
???????????????Slave_IO_State:?Waiting?for?master?to?send?event
??????????????????Master_Host:?192.168.56.15
??????????????????Master_User:?repl_user
??????????????????Master_Port:?3306
????????????????Connect_Retry:?60
??????????????Master_Log_File:?binlog.000003
??????????Read_Master_Log_Pos:?195
???????????????Relay_Log_File:?es3-relay-bin.000004
????????????????Relay_Log_Pos:?403
????????Relay_Master_Log_File:?binlog.000003
?????????????Slave_IO_Running:?Yes
????????????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:?195
??????????????Relay_Log_Space:?1271
??????????????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:?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
??????????????????Master_UUID:?dbda28c9-c970-11e9-b268-0800275c8ec3
?????????????Master_Info_File:?mysql.slave_master_info
????????????????????SQL_Delay:?0
??????????SQL_Remaining_Delay:?NULL
??????Slave_SQL_Running_State:?Slave?has?read?all?relay?log;?waiting?for?more?updates
???????????Master_Retry_Count:?86400
??????????????????Master_Bind:?
??????Last_IO_Error_Timestamp:?
?????Last_SQL_Error_Timestamp:?
???????????????Master_SSL_Crl:?
???????????Master_SSL_Crlpath:?
???????????Retrieved_Gtid_Set:?dbda28c9-c970-11e9-b268-0800275c8ec3:9-10
????????????Executed_Gtid_Set:?dbda28c9-c970-11e9-b268-0800275c8ec3:1-10
????????????????Auto_Position:?1
?????????Replicate_Rewrite_DB:?
?????????????????Channel_Name:?
???????????Master_TLS_Version:?
???????Master_public_key_path:?
????????Get_master_public_key:?0
????????????Network_Namespace:?
1?row?in?set?(0.00?sec)

mysql>?show?databases;
+--------------------+
|?Database???????????|
+--------------------+
|?after_clone1???????|
|?after_clone2???????|
|?information_schema?|
|?mysql??????????????|
|?performance_schema?|
|?sys????????????????|
+--------------------+
6?rows?in?set?(0.00?sec)

mysql>?exit

10、同步測(cè)試

10.1、主庫(kù)操作

[root@es2?~]#?mysql?-p'iwSeuFagt0&31'?

mysql>?show?databases;
+--------------------+
|?Database???????????|
+--------------------+
|?after_clone1???????|
|?after_clone2???????|
|?information_schema?|
|?mysql??????????????|
|?performance_schema?|
|?sys????????????????|
+--------------------+
6?rows?in?set?(0.00?sec)

mysql>?use?after_clone2
Database?changed
mysql>?create?table?t_clone(id?int?not?null?auto_increment?primary??key?,name?varchar(255));
Query?OK,?0?rows?affected?(0.36?sec)

mysql>?insert?into?t_clone(name)?values('c'),('l'),('o'),('n'),('e');
Query?OK,?5?rows?affected?(0.29?sec)
Records:?5??Duplicates:?0??Warnings:?0

mysql>?select?*?from?after_clone2.t_clone;
+----+------+
|?id?|?name?|
+----+------+
|??1?|?c????|
|??2?|?l????|
|??3?|?o????|
|??4?|?n????|
|??5?|?e????|
+----+------+
5?rows?in?set?(0.00?sec)

mysql>?drop?database?after_clone1;
Query?OK,?0?rows?affected?(0.31?sec)

mysql>?exit

10.2、從庫(kù)觀察

[root@es3?~]#?mysql?-p'iwSeuFagt0&31';
mysql>?show?databases;
+--------------------+
|?Database???????????|
+--------------------+
|?after_clone2???????|
|?information_schema?|
|?mysql??????????????|
|?performance_schema?|
|?sys????????????????|
+--------------------+
5?rows?in?set?(0.00?sec)

mysql>?select?*?from?after_clone2.t_clone;
+----+------+
|?id?|?name?|
+----+------+
|??1?|?c????|
|??2?|?l????|
|??3?|?o????|
|??4?|?n????|
|??5?|?e????|
+----+------+
5?rows?in?set?(0.00?sec)

mysql>?exit

11、遠(yuǎn)程克隆先決條件

????????要執(zhí)行遠(yuǎn)程克隆操作，數(shù)據(jù)提供方和數(shù)據(jù)接收方的MySQL服務(wù)器實(shí)例都處于活動(dòng)狀態(tài)
????????執(zhí)行遠(yuǎn)程克隆操作需要數(shù)據(jù)提供方和數(shù)據(jù)接收方上的MySQL用戶：
	????數(shù)據(jù)提供方上，克隆用戶需要BACKUP_ADMIN訪問和傳輸來自捐贈(zèng)者的數(shù)據(jù)的特權(quán)，
	以及在克隆操作期間阻止DDL?的?特權(quán)；
	????數(shù)據(jù)接收方上，克隆用戶需要具有CLONE_ADMIN替換收件人數(shù)據(jù)，
	在克隆操作期間阻止DDL以及自動(dòng)重新啟動(dòng)服務(wù)器的權(quán)限，
	該?CLONE_ADMIN權(quán)限包括隱式?BACKUP_ADMIN和?SHUTDOWN特權(quán)。
	????數(shù)據(jù)提供方和數(shù)據(jù)接收方必須具有相同的MySQL服務(wù)器版本。MYSQL?8.0.17及更高版本支持克隆插件。
	????數(shù)據(jù)提供方和數(shù)據(jù)接收方必須在同一操作系統(tǒng)和平臺(tái)上運(yùn)行。
	例如，如果捐贈(zèng)者實(shí)例在Linux?64位平臺(tái)上運(yùn)行，則收件人實(shí)例也必須在該平臺(tái)上運(yùn)行。
	????數(shù)據(jù)提供方和數(shù)據(jù)接收方必須具有相同的MySQL服務(wù)器字符集和排序規(guī)則
	????克隆插件僅克隆存儲(chǔ)的數(shù)據(jù)?InnoDB。不克隆其他存儲(chǔ)引擎數(shù)據(jù)。
	MyISAM并且?CSV存儲(chǔ)在包括sys模式的任何模式中的表都被克隆為空表。
	????克隆插件不支持克隆MySQL服務(wù)器配置my.cnf；克隆插件不支持克隆二進(jìn)制日志；
	不支持通過MySQL?router連接到捐贈(zèng)者實(shí)例。
	????克隆加密或頁(yè)面壓縮數(shù)據(jù)，則捐贈(zèng)者和接收者必須具有相同的文件系統(tǒng)塊大?。?	克隆加密數(shù)據(jù)，則需要安全連接
	????默認(rèn)情況下，克隆數(shù)據(jù)后會(huì)自動(dòng)重新啟動(dòng)（停止并啟動(dòng)）數(shù)據(jù)接收方MySQL服務(wù)器實(shí)例。
	要自動(dòng)重新啟動(dòng)，必須在接收方上提供監(jiān)視進(jìn)程以檢測(cè)服務(wù)器是否已關(guān)閉。
	否則，在克隆數(shù)據(jù)后，克隆操作將停止并出現(xiàn)以下錯(cuò)誤，并且關(guān)閉數(shù)據(jù)接收方MySQL服務(wù)器實(shí)例：
????ERROR?3707?(HY000):?Restart?server?failed?(mysqld?is?not?managed?by?supervisor?process).