我要评论1、捐赠者和接受者
install plugin clone soname 'mysql_clone.so';
2、创建用户及授权
捐赠者：
- 创建克隆所需用户：
create user `clone_user`@`192.168.1.%` identified by 'clone_user'; grant backup_admin on *.* to `clone_user`@`192.168.1.%` # backup_admin是mysql8.0 才有的备份锁的权限
接受者：
- 创建执行克隆权限的用户：
create user clone_user@'192.168.1.%' identified by 'clone_user'; grant clone_admin on *.* to 'clone_user'@'192.168.1.%';
clone_admin权限 = backup_admin权限 + shutdown权限。
shutdown权限允许用户shutdown和restart mysqld。
授权不同是因为，接受者需要restart mysqld。
3、接受者设置捐赠者列表清单
set global clone_valid_donor_list = '192.168.1.11:3306';
4、接受者执行
clone instance from clone_user@'192.168.1.11':3306 identified by 'clone_user';
注意：
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、查看进度
select stage, state, end_time from performance_schema.clone_progress;
6、在接受者查询捐赠款的日志信息
select binlog_file, binlog_position from performance_schema.clone_status;
7、查询进度的另一条sql
select
stage,
state,
cast(begin_time as datetime) as "start time",
cast(end_time as datetime) as "finish time",
lpad(sys.format_time(power(10,12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ') as duration,
lpad(concat(format(round(estimate/1024/1024,0), 0), "mb"), 16, ' ') as "estimate",
case when begin_time is null then lpad('%0', 7, ' ')
when estimate > 0 then
lpad(concat(round(data*100/estimate, 0), "%"), 7, ' ')
when end_time is null then lpad('0%', 7, ' ')
else lpad('100%', 7, ' ')
end as "done(%)"
from performance_schema.clone_progress;
8、 修改主从关系
change master to master_host='10.0.14.141', master_port=61106 ,master_user='repl',master_password='xxxxxxxx',master_auto_position = 1;
9、clone脚本
有一步重新初始化的操作，记得修改对应的目录
#!/usr/bin/env bash
clone_admin_user="clone_user@'192.168.x.%'"
clone_valid_donor_list="192.168.x.x"
mysql_port=3306
root_password="123456"
# 仅支持gtid模式
# gtid_mode=1
#删除旧文件
function del_old_file() {
systemctl stop mysqld && rm -rf /data/logs/* && rm -rf /data1/data* && rm -rf /data/data/binlog/* && rm -rf /data/data/relaylog/*
}
function start_mysqld() {
systemctl start mysqld
oldpassword=`grep 'temporary password' /data/logs//mysqld.log | awk '{printf $nf}'`
setpasswdtxt="set global validate_password.policy='low';alter user root@localhost identified by '${root_password}';"
mysql -h localhost -p${mysql_port} -uroot -p${oldpassword} -e "${setpasswdtxt}" --connect-expired-password
}
function install_plugin() {
install_plugin_sql="install plugin clone soname 'mysql_clone.so'"
mysql -h localhost -p${mysql_port} -uroot -p${root_password} -e "${install_plugin_sql}" --connect-expired-password
}
function set_clone_user() {
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};"
mysql -h localhost -p${mysql_port} -uroot -p${root_password} -e "${set_clone_user_sql}" --connect-expired-password
}
function begin_clone() {
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';"
mysql -h localhost -p${mysql_port} -uroot -p${root_password} -e "${clone_sql}" --connect-expired-password && echo "clone ends ... "
}
function change_master() {
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;"
mysql -h localhost -p${mysql_port} -uroot -p${root_password} -e "${change_master_sql}" --connect-expired-password
}
function execute_all() {
del_old_file
start_mysqld
install_plugin
set_clone_user
begin_clone
change_master
}
function usage() {
echo "mysql-clone {-h|-d|-s|-u|-c|-m|-a}"
echo "del_old_file(-d) -- stop mysqld and delete old mysql-datafiles"
echo "start_mysqld(-s) -- start mysqld and set newpassword"
echo "install_plugin(-i) -- install clone plgin"
echo "set_clone_user(-u) -- set clone user"
echo "begin_clone(-c) -- set clone donor and clone"
echo "change_master(-m) -- change master"
echo "execute_all(-a) -- execute all"
}
case "$1" in
'-d')
del_old_file
;;
'-s')
start_mysqld
;;
'-i')
install_plugin
;;
'-u')
set_clone_user
;;
'-c')
begin_clone
;;
'-m')
change_master
;;
'-a')
execute_all
;;
*)
usage
esac
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论