def reset_slave(master_db_args, slave_db_args, master_cnf_dir, master_os_args,
slave_os_args, slave_cnf_dir):
rds_version = get_all(master_db_args, "show variables like 'rds_version';")
if rds_version == []:
check_mas_cnf_exsits = ssh_input_noprint(
master_os_args, f"ls {master_cnf_dir}_ori.bak")[0]
if " cannot access " not in check_mas_cnf_exsits:
ssh_input(master_os_args,
f"mv {master_cnf_dir}_ori.bak {master_cnf_dir}")
else:
pass
check_sla_cnf_exsits = ssh_input_noprint(slave_os_args,
f"ls {slave_cnf_dir}_ori.bak")[0]
if " cannot access " not in check_sla_cnf_exsits:
ssh_input(slave_os_args, f"mv {slave_cnf_dir}_ori.bak {slave_cnf_dir}")
set_readonly_master(master_db_args, 'off', master_cnf_dir, master_os_args)
set_readonly_master(slave_db_args, 'off', slave_cnf_dir, slave_os_args)
select_drop_sql = '''select concat("drop user '",user,"'@'",host,"';") from mysql.user where user like 'mcbak_%' ;'''
select_drop_res_m = get_all(master_db_args, select_drop_sql)
for sql in select_drop_res_m:
run_noprint(master_db_args, sql[0])
select_drop_res_s = get_all(slave_db_args, select_drop_sql)
for sql_s in select_drop_res_s:
run_noprint(slave_db_args, sql_s[0])
set_con_warn = "SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;"
set_con_off = "SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;"
set_mode_off_p = "SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;"
set_mode_on_p = "SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;"
set_mode_off = "SET @@GLOBAL.GTID_MODE = OFF;"
run_noprint(master_db_args, "set global rpl_semi_sync_master_enabled=off;")
run_noprint(slave_db_args, "set global rpl_semi_sync_slave_enabled=off;")
run_noprint(master_db_args, "reset master;")
run_noprint(slave_db_args, "stop slave;")
get_dict(master_db_args, set_mode_on_p)
get_dict(master_db_args, set_mode_off_p)
get_dict(master_db_args, set_mode_off)
get_dict(master_db_args, set_con_warn)
get_dict(master_db_args, set_con_off)
get_dict(slave_db_args, set_mode_on_p)
get_dict(slave_db_args, set_mode_off_p)
get_dict(slave_db_args, set_mode_off)
get_dict(slave_db_args, set_con_warn)
get_dict(slave_db_args, set_con_off)
run_noprint(slave_db_args, "reset slave all;")
run_noprint(slave_db_args, "reset master;")
rep_status(master_db_args, slave_db_args)
print("===============INFO:RESET COMPLETE====================")
return "reset complete"
def restore_db(slave_db_args, slave_os_args, slave_cnf_dir, xbakup_dir,
data_dir, redo_dir, undo_dir, bin_dir):
ssh_input_noprint(slave_os_args, f"sudo rm -rf {data_dir}")
ssh_input_noprint(slave_os_args, f"sudo rm -rf {redo_dir}")
ssh_input_noprint(slave_os_args, f"sudo rm -rf {undo_dir}")
db_dir = '/'.join((data_dir + '/').replace('//', '/').split('/')[0:-2])
ssh_input_noprint(
slave_os_args,
f"sudo chmod -R 777 {xbakup_dir}/data_dir_{slave_db_args[0]}_{slave_db_args[2]}"
)
ssh_input_noprint(
slave_os_args,
f"sudo mv {xbakup_dir}/data_dir_{slave_db_args[0]}_{slave_db_args[2]}/* {db_dir} "
)
start_db(slave_os_args, slave_db_args, bin_dir, slave_cnf_dir)
return 0
def get_pos(slave_os_args, slave_db_args):
s_ip, s_db_user, s_db_port, s_db_pwd = slave_db_args
dump_file_dir = ssh_input_noprint(slave_os_args,
f'cat /tmp/{s_ip}_{s_db_port}.log')[0]
if 'No such' not in dump_file_dir and '没有' not in dump_file_dir:
if 'exp_' in dump_file_dir:
change_pos_sql = ssh_input(
slave_os_args,
f'''grep "CHANGE MASTER TO MASTER_LOG_FILE='" {dump_file_dir}'''
)[0]
else:
pos_info = dump_file_dir.replace('\n', '').split(' ')
change_pos_sql = f"-- CHANGE MASTER TO MASTER_LOG_FILE='{pos_info[0]}', MASTER_LOG_POS={pos_info[-1]};"
ssh_input_noprint(slave_os_args,
f'rm -rf /tmp/{s_ip}_{s_db_port}.log')
return change_pos_sql
else:
return "file not access"
def start_db(os_args, db_args, bin_dir, cnf_dir):
ip, db_user, db_port, db_pwd = db_args
start_sh = f'nohup {bin_dir}mysqld --defaults-file={cnf_dir} > /dev/null 2>&1 >>/tmp/startmysql{ip}{db_port}.log &'
ssh_input_noprint(os_args, start_sh)
time.sleep(3)
start_res = ''.join(
ssh_input_noprint(os_args, f'cat /tmp/startmysql{ip}{db_port}.log'))
if 'ready for connections' in start_res or start_res == '':
print(f"INFO:\ndatabase IP:{ip} PORT:{db_port} start complete.")
ssh_input_noprint(os_args, f'rm -f /tmp/startmysql{ip}{db_port}.log')
return 'start success'
else:
print(f"WARNING:\ndatabase IP:{ip} PORT:{db_port} start failed.")
ssh_input_noprint(os_args, f'rm -f /tmp/startmysql{ip}{db_port}.log')
return 'start failed'
def stop_db(os_args, db_args):
base_dir = get_all(db_args, " show variables like 'basedir';")[0][1]
socket_dir = get_all(db_args, "show global variables like 'socket'")[0][1]
bin_dir = f"{base_dir}/bin/"
ip, db_user, db_port, db_pwd = db_args
db_pwd = db_pwd.replace('!', '\\!')
stop_res = ''.join(
ssh_input_noprint(
os_args,
f"{bin_dir}mysqladmin -u {db_user} -p{db_pwd} -P {db_port} -S {socket_dir} shutdown"
))
if 'error' not in stop_res:
print(f"INFO:\ndatabase IP:{ip} PORT:{db_port} shutdown complete.")
return 'stop db s', bin_dir
else:
print(f"INFO:\ndatabase IP:{ip} PORT:{db_port} shutdown failed.")
return 'stop db f', bin_dir
def innobackup(master_os_args, master_db_args, master_cnf_dir, xbakup_dir,
slaves):
mount_res = mount_nfs(xbakup_dir, slaves, master_os_args)
if mount_res != 'nfs mount complete':
os._exit(0)
else:
m_ip, m_db_user, m_db_port, m_db_pwd = master_db_args
m_db_pwd = m_db_pwd.replace('!', '\\!')
m_socket_dir = get_all(master_db_args,
"show global variables like 'socket'")[0][1]
master_bakup_cmd = f"{xbakup_dir}/percona-xtrabackup-2.4.13-Linux-x86_64/bin/innobackupex --defaults-file={master_cnf_dir} \
-u{m_db_user} -p{m_db_pwd} --socket={m_socket_dir} --slave-info {xbakup_dir}"
ssh_input_noprint(master_os_args, f"sudo rm -rf {xbakup_dir}/20*")
res = ''.join(ssh_input_noprint(master_os_args, master_bakup_cmd))
# ssh_input_noprint(master_os_args,f"chmod -R 777 {xbakup_dir}/20*")
if ' completed OK!' in res:
print("INFO:master db innobackup completed.\n")
print(
"===============INFO:INNOBACKUP MASTER COMPLETE===================="
)
# for slave in slaves:
# slave_db_args, slave_os_args, slave_cnf_dir = slave
# check_dir_res = ssh_input_noprint(slave_os_args,f'cat {xbakup_dir}/{slave_db_args[0]}_{slave_db_args[2]}')[0]
# if 'No such' not in check_dir_res and '没有' not in check_dir_res:
# pass
# else:
# data_dir = get_all(slave_db_args,"show variables like 'datadir'")[0][1]
# if data_dir != check_dir_res:
# ssh_input_noprint(master_os_args,f"rm -rf {xbakup_dir}/{slave_db_args[0]}_{slave_db_args[2]}\necho '{data_dir}'>>{xbakup_dir}/{slave_db_args[0]}_{slave_db_args[2]}")
apply_res = ''.join(
ssh_input_noprint(
master_os_args,
f"{xbakup_dir}/percona-xtrabackup-2.4.13-Linux-x86_64/bin/innobackupex --apply-log {xbakup_dir}/20*"
))
ssh_input_noprint(master_os_args,
f"sudo chmod -R 777 {xbakup_dir}/20*")
for slave in slaves:
slave_db_args, slave_os_args, slave_cnf_dir = slave
print(
F"###INFO:IP:{slave_db_args[0]} PORT:{slave_db_args[2]} Innobackup start:\n"
)
# data_dir = ''.join(ssh_input_noprint(slave_os_args,f"cat {xbakup_dir}/{slave_db_args[0]}_{slave_db_args[2]}")).replace('\n','')
#base_dir = [i for i in ssh_input_noprint(slave_os_args,f"cat {slave_cnf_dir} |grep -i basedir") if '#' not in i][0].replace('\n','').replace(' ','').split('=')[-1]
#data_dir = [i for i in ssh_input_noprint(slave_os_args,f"cat {slave_cnf_dir} |grep -i datadir") if '#' not in i][0].replace('\n','').replace(' ','').split('=')[-1]
base_dir = get_all(slave_db_args,
" show variables like 'basedir';")[0][1]
data_dir = get_all(slave_db_args,
" show variables like 'datadir';")[0][1]
redo_dir = get_all(
slave_db_args,
" show variables like 'innodb_log_group_home_dir';")[0][1]
undo_dir = get_all(
slave_db_args,
" show variables like 'innodb_undo_directory';")[0][1]
db_dir = '/'.join(
(data_dir + '/').replace('//', '/').split('/')[0:-2])
#ssh_input_noprint(slave_os_args,"ps -aux | grep mysqld| awk '{print $2}' | xargs kill")
#ssh_input_noprint(slave_os_args,f"mysqladmin -u{slave_db_args[1]} -p{slave_db_args[3]} -h{slave_db_args[0]} -P{slave_db_args[2]} shutdown ")
stop_res, bin_dir = stop_db(slave_os_args, slave_db_args)
ssh_input_noprint(
slave_os_args,
f"sudo rm -rf {xbakup_dir}/data_dir_{slave_db_args[0]}_{slave_db_args[2]}"
)
ssh_input_noprint(
slave_os_args,
f"sudo mkdir -p {xbakup_dir}/data_dir_{slave_db_args[0]}_{slave_db_args[2]}\n\
sudo chmod -R 777 {xbakup_dir}/data_dir_{slave_db_args[0]}_{slave_db_args[2]}"
)
ssh_input_noprint(slave_os_args, f"sudo chmod -R 777 {db_dir}")
ssh_input_noprint(
slave_os_args,
f"sudo mv {data_dir} {xbakup_dir}/data_dir_{slave_db_args[0]}_{slave_db_args[2]}"
)
ssh_input_noprint(
slave_os_args,
f"sudo mv {redo_dir} {xbakup_dir}/data_dir_{slave_db_args[0]}_{slave_db_args[2]}"
)
ssh_input_noprint(
slave_os_args,
f"sudo mv {undo_dir} {xbakup_dir}/data_dir_{slave_db_args[0]}_{slave_db_args[2]}"
)
print(
"INFO:Shutdown mysqld server and run innobackupex --apply-log.\n"
)
if ' completed OK!' in apply_res:
print("INFO:Run innobackupex --apply-log complete\n")
copy_res = ''.join(
ssh_input_noprint(
slave_os_args,
f'''{xbakup_dir}/percona-xtrabackup-2.4.13-Linux-x86_64/bin/innobackupex --defaults-file={slave_cnf_dir} \
--copy-back {xbakup_dir}/20*'''))
if ' completed OK!' in copy_res:
print(f"INFO: copy back complete\n")
ssh_input_noprint(
slave_os_args,
f'cp {xbakup_dir}/20*/xtrabackup_binlog_info /tmp/{slave_db_args[0]}_{slave_db_args[2]}.log'
)
start_db(slave_os_args, slave_db_args, bin_dir,
slave_cnf_dir)
print(
f"===============INFO:INNOBACKUP IP:{slave_db_args[0]} PORT:{slave_db_args[2]} COMPLETE===================="
)
else:
print("INFO:Run innobackupex copy back failed\n")
restore_db(slave_db_args, slave_os_args, slave_cnf_dir,
xbakup_dir, data_dir, redo_dir, undo_dir,
bin_dir)
print("INFO:Restore databse now\n")
else:
print("INFO:Run innobackupex --apply-log failed\n")
restore_db(slave_db_args, slave_os_args, slave_cnf_dir,
xbakup_dir, data_dir, redo_dir, undo_dir,
bin_dir)
print("INFO:Restore databse now\n")
return 0
else:
print("INFO:master db innobackup falied.\n")
return "backup failed"
def mount_nfs(xbakup_dir, slaves, master_os_args):
dir_counts = len(xbakup_dir.split('/'))
if dir_counts > 2:
print("INFO:The directory of backup are available.\n")
ssh_input_noprint(
master_os_args,
f'sudo mkdir -p {xbakup_dir}\nsudo chmod 777 {xbakup_dir}')
check_soft = ''.join(ssh_input(master_os_args,
f"sudo ls {xbakup_dir}"))
if 'percona-xtrabackup' in check_soft:
print("INFO:There have xtrabackup software in directory.")
else:
ssh_ftp(master_os_args,
f'{xbakup_dir}/percona-xtrabackup-2.4.13.tar.gz',
'percona-xtrabackup-2.4.13.tar.gz', 'put')
ssh_input_noprint(
master_os_args,
f'sudo tar -zxvf {xbakup_dir}/percona-xtrabackup-2.4.13.tar.gz -C {xbakup_dir}'
)
exports_text = ssh_input(master_os_args, 'sudo cat /etc/exports')
for slave in slaves:
slave_db_args, slave_os_args, slave_cnf_dir = slave
if xbakup_dir not in ''.join(
exports_text) and slave_os_args[0] != master_os_args[0]:
exports_text.append(
f'{xbakup_dir} {slave_db_args[0]}(rw,sync,all_squash)')
ssh_input_noprint(
slave_os_args,
f'sudo mkdir -p {xbakup_dir}\nsudo chmod 777 {xbakup_dir}')
ssh_input_noprint(
master_os_args,
f'''sudo mv /etc/exports /etc/exports.bak\nsudo sh -c "echo '{''.join(exports_text)}' >> /etc/exports"'''
)
rhel_version = ssh_input(master_os_args, 'uname -r')[0]
if 'el7' in rhel_version:
ssh_input_noprint(
master_os_args,
'sudo exportfs -r\nsudo systemctl stop nfs\nsudo rpcbind stop\nsudo rpcbind reload\nsudo systemctl start nfs'
)
else:
ssh_input_noprint(
master_os_args,
'sudo exportfs -r\nsudo nfs stop\nsudo rpcbind stop\nsudo rpcbind reload\nsudo nfs start'
)
check_nfs = ssh_input(master_os_args, 'sudo showmount -e localhost')
for slave in slaves:
slave_db_args, slave_os_args, slave_cnf_dir = slave
if slave_os_args[0] != master_os_args[0]:
mount_res = ''.join(
ssh_input(
slave_os_args,
f'sudo mount -t nfs {master_os_args[0]}:{xbakup_dir} {xbakup_dir} -o proto=tcp -o nolock'
))
if 'already mounted' in mount_res or mount_res == '':
print(f"INFO: IP:{slave_db_args[0]} already mounted.")
print(
"===============INFO:NFS MOUNTED COMPLETE===================="
)
return "nfs mount complete"
else:
print(f"WARNING: IP:{slave_db_args[0]} were mount failed.")
return "nfs mount failed"
else:
print("ERROR:Please choose a Two level directory!\n")
return "invaild dir"
def mysqldump(master_db_args, slave_db_args, master_os_args, slave_os_args,
xbakup_dir):
m_ip, m_db_user, m_db_port, m_db_pwd = master_db_args
s_ip, s_db_user, s_db_port, s_db_pwd = slave_db_args
m_db_pwd = m_db_pwd.replace('!', '\\!')
s_db_pwd = s_db_pwd.replace('!', '\\!')
slave_base_dir = get_all(slave_db_args,
" show variables like 'basedir';")[0][1]
print(f"INFO:\nIP:{s_ip} PORT:{s_db_port} MYSQLDUMP NOW.\n")
exp_dir = xbakup_dir
check_exp_dir = ssh_input_noprint(slave_os_args, f"ls {exp_dir}")
if check_exp_dir != [] and 'no ac' in check_exp_dir[0]:
ssh_input_noprint(
slave_os_args,
f"sudo mkdir -p {exp_dir}\nsudo chmod -R 777 {exp_dir}")
print(f"INFO:\nmkdir the directory :{exp_dir} now.\n")
elif exp_dir == '/':
print("PLEASE DONT CHOOSE THE '/' DIRECTORY!\n")
os._exit(0)
else:
ssh_input_noprint(slave_os_args, f"sudo chmod -R 777 {exp_dir}")
ssh_input_noprint(slave_os_args,
f"rm -rf {exp_dir}/exp_{s_ip}_{s_db_port}.txt")
exp_dbs_yn = 'Y'
if exp_dbs_yn.upper() == 'Y':
exp_dbs = ssh_input(
slave_os_args,
f"{slave_base_dir}/bin/mysql -e 'show databases;' -u{m_db_user} -p{m_db_pwd} -P{m_db_port} -h{m_ip}| \
grep -Ev 'Database|information_schema|mysql|performance_schema|sys' |xargs"
)
if exp_dbs != []:
exp_dbs = exp_dbs[0].replace('\n', '')
exp_cmd = f"{slave_base_dir}/bin/mysqldump -u{m_db_user} -p{m_db_pwd} -P{m_db_port} -h{m_ip} \
--databases {exp_dbs} --single-transaction --master-data=2 -E -R > {exp_dir}/exp_{s_ip}_{s_db_port}.txt"
else:
print("INFO:There are no business databases.")
os._exit(0)
else:
exp_cmd = f"{slave_base_dir}/bin/mysqldump -u{m_db_user} -p{m_db_pwd} -P{m_db_port} -h{m_ip} \
--single-transaction --master-data=2 -E -R --all-databases > {exp_dir}/exp_{s_ip}_{s_db_port}.txt"
imp_cmd = f"{slave_base_dir}/bin/mysql -u{s_db_user} -p{s_db_pwd} -P{s_db_port} -h{s_ip} <{exp_dir}/exp_{s_ip}_{s_db_port}.txt"
exp_res = ''.join(ssh_input(slave_os_args, exp_cmd))
if 'ERROR' not in exp_res.upper():
exp_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
print(
f"INFO:\n{exp_time} IP:{s_ip} PORT:{s_db_port} EXPORT DATABASE COMPLETE.\n"
)
run_noprint(slave_db_args, "reset master;")
imp_res = ''.join(ssh_input(slave_os_args, imp_cmd))
imp_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
if 'ERROR' not in imp_res.upper() and 'bash:' not in imp_res:
print(
f"INFO:\n{imp_time} IP:{s_ip} PORT:{s_db_port} IMPORT DATABASE COMPLETE.\n"
)
print(
f"===============INFO:MYSQLDUMP IP:{s_ip} PORT:{s_db_port} COMPLETE===================="
)
ssh_input_noprint(
slave_os_args,
f"rm -fr /tmp/{s_ip}_{s_db_port}.log\necho '{exp_dir}/exp_{s_ip}_{s_db_port}.txt'>/tmp/{s_ip}_{s_db_port}.log"
)
return "imp complete"
else:
print(
f"ERROR:\n{imp_time} IP:{s_ip} PORT:{s_db_port} IMPORT DATABASE FAILED.\n"
)
ssh_input_noprint(
slave_os_args,
f"rm -fr /tmp/{s_ip}_{s_db_port}.log\necho 'imp failed'>/tmp/{s_ip}_{s_db_port}.log"
)
return "imp failed"
else:
print(f"ERROR:\nIP:{s_ip} PORT:{s_db_port} EXPORT DATABASE FAILED.\n")
ssh_input_noprint(
slave_os_args,
f"rm -fr /tmp/{s_ip}_{s_db_port}.log\necho 'exp failed'>/tmp/{s_ip}_{s_db_port}.log"
)
return "exp failed"