MySQL数据库如何克隆(带脚本)

上山打野

1、捐赠者和接受者

1. INSTALL PLUGIN clone SONAME 'mysql_clone.so';

复制代码

2、创建用户及授权

捐赠者：

• 创建克隆所需用户：
  

1. CREATE USER `clone_user`@`192.168.1.%` IDENTIFIED by 'clone_user';
   
2. GRANT BACKUP_ADMIN ON *.* TO `clone_user`@`192.168.1.%`   # BACKUP_ADMIN是MySQL8.0 才有的备份锁的权限

复制代码

接受者：

• 创建执行克隆权限的用户：
  

1. CREATE USER clone_user@'192.168.1.%' IDENTIFIED by 'clone_user';
   
2. GRANT CLONE_ADMIN ON *.* TO 'clone_user'@'192.168.1.%';

复制代码

CLONE_ADMIN权限 = BACKUP_ADMIN权限 + SHUTDOWN权限。
SHUTDOWN权限允许用户shutdown和restart mysqld。
授权不同是因为，接受者需要restart mysqld。

3、接受者设置捐赠者列表清单

1. SET GLOBAL clone_valid_donor_list = '192.168.1.11:3306';

复制代码

4、接受者执行

1. CLONE INSTANCE FROM clone_user@'192.168.1.11':3306 IDENTIFIED BY 'clone_user';

复制代码

注意：

1. ERROR 3870 (HY000): Clone Donor plugin validate_password is not active in Recipient.

复制代码

https://dev.mysql.com/doc/refman/5.6/en/validate-password-installation.html

5、查看进度

1. SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;

复制代码

6、在接受者查询捐赠款的日志信息

1. SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;

复制代码

7、查询进度的另一条SQL

1. select
   
2. stage,
   
3. state,
   
4. cast(begin_time as DATETIME) as "START TIME",
   
5. cast(end_time as DATETIME) as "FINISH TIME",
   
6. lpad(sys.format_time(power(10,12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ') as DURATION,
   
7. lpad(concat(format(round(estimate/1024/1024,0), 0), "MB"), 16, ' ') as "Estimate",
   
8. case when begin_time is NULL then LPAD('%0', 7, ' ')
   
9. when estimate > 0 then
   
10. lpad(concat(round(data*100/estimate, 0), "%"), 7, ' ')
    
11. when end_time is NULL then lpad('0%', 7, ' ')
    
12. else lpad('100%', 7, ' ')
    
13. end as "Done(%)"
    
14. from performance_schema.clone_progress;

复制代码

8、 修改主从关系

1. CHANGE MASTER TO MASTER_HOST='10.0.14.141', MASTER_PORT=61106 ,MASTER_USER='repl',MASTER_PASSWORD='xxxxxxxx',MASTER_AUTO_POSITION = 1;

复制代码

9、clone脚本

有一步重新初始化的操作，记得修改对应的目录

1. #!/usr/bin/env bash
   
2. 
   
3. CLONE_ADMIN_USER="clone_user@'192.168.x.%'"
   
4. CLONE_VALID_DONOR_LIST="192.168.x.x"
   
5. MYSQL_PORT=3306
   
6. ROOT_PASSWORD="123456"
   
7. 
   
8. # 仅支持GTID模式
   
9. # GTID_MODE=1
   
10. #删除旧文件
    
11. function  del_old_file() {
    
12.     systemctl stop mysqld && rm -rf /data/logs/* && rm -rf /data1/data* && rm -rf /data/data/binlog/* && rm -rf /data/data/relaylog/*
    
13. }
    
14. 
    
15. function start_mysqld() {
    
16.     systemctl start mysqld
    
17.     OLDPASSWORD=`grep 'temporary password' /data/logs//mysqld.log | awk '{printf $NF}'`
    
18.     SETPASSWDTXT="set global validate_password.policy='LOW';alter user root@localhost identified by '${ROOT_PASSWORD}';"
    
19.     mysql -h localhost -P${MYSQL_PORT} -uroot -p${OLDPASSWORD} -e "${SETPASSWDTXT}" --connect-expired-password
    
20. }
    
21. 
    
22. function install_plugin() {
    
23.     INSTALL_PLUGIN_SQL="INSTALL PLUGIN clone SONAME 'mysql_clone.so'"
    
24.     mysql -h localhost -P${MYSQL_PORT} -uroot -p${ROOT_PASSWORD} -e "${INSTALL_PLUGIN_SQL}" --connect-expired-password
    
25. }
    
26. function set_clone_user() {
    
27.     SET_CLONE_USER_SQL="set global validate_password.policy='LOW';CREATE USER IF NOT EXISTS ${CLONE_ADMIN_USER} IDENTIFIED by 'clone_user';GRANT BACKUP_ADMIN,CLONE_ADMIN ON *.* TO ${CLONE_ADMIN_USER};"
    
28.     mysql -h localhost -P${MYSQL_PORT} -uroot -p${ROOT_PASSWORD} -e "${SET_CLONE_USER_SQL}" --connect-expired-password
    
29. }
    
30. 
    
31. function begin_clone() {
    
32.     CLONE_SQL="SET GLOBAL clone_valid_donor_list = '${CLONE_VALID_DONOR_LIST}:${MYSQL_PORT}';CLONE INSTANCE FROM clone_user@'${CLONE_VALID_DONOR_LIST}':${MYSQL_PORT} IDENTIFIED BY 'clone_user';"
    
33.     mysql -h localhost -P${MYSQL_PORT} -uroot -p${ROOT_PASSWORD} -e "${CLONE_SQL}" --connect-expired-password && echo "CLONE ENDS ... "
    
34. }
    
35. function change_master() {
    
36.     CHANGE_MASTER_SQL="STOP SLAVE;CHANGE MASTER TO MASTER_HOST='${CLONE_VALID_DONOR_LIST}', MASTER_PORT=${MYSQL_PORT} ,MASTER_USER='repl',MASTER_PASSWORD='repl20150602',MASTER_AUTO_POSITION = 1;START SLAVE;"
    
37.     mysql -h localhost -P${MYSQL_PORT} -uroot -p${ROOT_PASSWORD} -e "${CHANGE_MASTER_SQL}" --connect-expired-password
    
38. }
    
39. 
    
40. function execute_all() {
    
41.     del_old_file
    
42.     start_mysqld
    
43.     install_plugin
    
44.     set_clone_user
    
45.     begin_clone
    
46.     change_master
    
47. }
    
48. function usage() {
    
49.     echo "mysql-clone {-h|-d|-s|-u|-c|-m|-a}"
    
50.     echo "del_old_file(-d)               -- stop mysqld and delete old mysql-datafiles"
    
51.     echo "start_mysqld(-s)               -- start mysqld and set newpassword"
    
52.     echo "install_plugin(-i)               -- install clone plgin"
    
53.     echo "set_clone_user(-u)             -- set clone user"
    
54.     echo "begin_clone(-c)                -- set clone donor and clone"
    
55.     echo "change_master(-m)                -- change master"
    
56.     echo "execute_all(-a)                -- execute all"
    
57. }
    
58. 
    
59. case "$1" in
    
60.     '-d')
    
61.         del_old_file
    
62.         ;;
    
63.     '-s')
    
64.         start_mysqld
    
65.         ;;
    
66.     '-i')
    
67.         install_plugin
    
68.         ;;
    
69.     '-u')
    
70.         set_clone_user
    
71.         ;;
    
72.     '-c')
    
73.         begin_clone
    
74.         ;;
    
75.     '-m')
    
76.         change_master
    
77.         ;;
    
78.     '-a')
    
79.         execute_all
    
80.         ;;
    
81.     *)
    
82.         usage
    
83. esac

复制代码

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/3188623j0.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！