溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
? ? ? ?克隆插件允許在本地或從遠程MySQL服務器實例克隆數據。也可以看作一種備份方法，所以我歸到備份整理里面了。 克隆數據是InnoDB中存儲的數據的物理快照，包括模式，表，表空間和數據字典元數據。 克隆的數據包含一個功能齊全的數據目錄，允許使用克隆插件進行MySQL服務器配置。
? ? ? ?本地克隆操作
本地克隆操作將啟動克隆操作的 MySQL 服務器實例中的數據克隆到同服務器或同節點上的一個目錄里（要注意這個配置secure_file_priv）。這個功能讓我想起tukodb的hotbackup，我前面文章有寫到。
? ? ? ?遠程克隆
默認情況下，遠程克隆操作會刪除接受者(recipient)數據目錄中的數據，并將其替換為捐贈者(donor)的克隆數據。（可選）您也可以將數據克隆到接受者的其他目錄，以避免刪除現有數據。
遠程克隆操作和本地克隆操作克隆的數據沒有區別，數據是相同的。
克隆插件支持復制。除克隆數據外，克隆操作還從捐贈者中提取并傳輸復制位置信息，并將其應用于接受者，從而可以使用克隆插件來配置組復制或主從復制。使用克隆插件進行配置比復制大量事務要快得多，效率更高。
這個功能讓我想起postgresql 的pg_basebackup，以后的文章會寫
1、兩臺機器分別安裝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 插件，并創建克隆用戶和復制用戶
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、通過創建不同標識，進行多次克隆測試
mysql>?create?database?after_clone1; mysql>??CLONE?LOCAL?DATA?DIRECTORY?=?'/var/lib/mysql-files/clone_dir2'; mysql>?create?database?after_clone2;
6、切換克隆數據目錄，查看相關標識情況
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
遠程克隆
7、遠程克隆數據接收端環境準備，創建接收者用戶和授權。?CLONE_ADMIN權限 = BACKUP_ADMIN權限 + SHUTDOWN權限。SHUTDOWN僅限允許用戶shutdown和restart mysqld。授權不同是因為，接受者需要restart mysqld，特別提示，本地克隆用戶使用的是BACKUP_ADMIN即可。這里我們將上面創建的本地克隆用戶作為捐贈者用戶使用。
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、嘗試遠程克隆，注意clone_valid_donor_list設置，當然不設置會提醒
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、嘗試做主從復制，注意修改主庫的密碼驗證插件
主庫
?alter?USER?repl_user@'%'?identified??WITH?mysql_native_password?by?'iwSeuFagt0&31'?;
從庫
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、同步測試
10.1、主庫操作
[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>?exit10.2、從庫觀察
[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、遠程克隆先決條件
????????要執行遠程克隆操作，數據提供方和數據接收方的MySQL服務器實例都處于活動狀態 ????????執行遠程克隆操作需要數據提供方和數據接收方上的MySQL用戶： ????數據提供方上，克隆用戶需要BACKUP_ADMIN訪問和傳輸來自捐贈者的數據的特權， 以及在克隆操作期間阻止DDL?的?特權； ????數據接收方上，克隆用戶需要具有CLONE_ADMIN替換收件人數據， 在克隆操作期間阻止DDL以及自動重新啟動服務器的權限， 該?CLONE_ADMIN權限包括隱式?BACKUP_ADMIN和?SHUTDOWN特權。 ????數據提供方和數據接收方必須具有相同的MySQL服務器版本。MYSQL?8.0.17及更高版本支持克隆插件。 ????數據提供方和數據接收方必須在同一操作系統和平臺上運行。 例如，如果捐贈者實例在Linux?64位平臺上運行，則收件人實例也必須在該平臺上運行。 ????數據提供方和數據接收方必須具有相同的MySQL服務器字符集和排序規則 ????克隆插件僅克隆存儲的數據?InnoDB。不克隆其他存儲引擎數據。 MyISAM并且?CSV存儲在包括sys模式的任何模式中的表都被克隆為空表。 ????克隆插件不支持克隆MySQL服務器配置my.cnf；克隆插件不支持克隆二進制日志； 不支持通過MySQL?router連接到捐贈者實例。 ????克隆加密或頁面壓縮數據，則捐贈者和接收者必須具有相同的文件系統塊大小； 克隆加密數據，則需要安全連接 ????默認情況下，克隆數據后會自動重新啟動（停止并啟動）數據接收方MySQL服務器實例。 要自動重新啟動，必須在接收方上提供監視進程以檢測服務器是否已關閉。 否則，在克隆數據后，克隆操作將停止并出現以下錯誤，并且關閉數據接收方MySQL服務器實例： ????ERROR?3707?(HY000):?Restart?server?failed?(mysqld?is?not?managed?by?supervisor?process).
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
