##====================================================================##
# MySQL Dump导出数据和权限脚本
# 如果在主库上备份使用--master-data=2参数
# 如果在从库上备份使用--dump-slave=2参数
##====================================================================##
mysql_exe="/export/servers/mysql/bin/mysql"
mysqldump_exe="/export/servers/mysql/bin/mysqldump"
mysql_host="127.0.0.1"
mysql_port=3306mysql_user="root"
mysql_password="root_psw"
working_dir="/export/mysql_update/"
data_file="${working_dir}/data_script.sql"
user_file="${working_dir}/user_script.sql"
log_file="${working_dir}/mysql_dump_log.txt"
err_file="${working_dir}/mysql_dump_err.txt"
master_slave_data="--master-data=2"
mysql_version="mysql57"
##====================================================##
##1. create folder and file for mysql dump##====================================================##functioncrete_dump_file()
{if [-d ${data_file}]
thenecho'data file is exists, please check and remove it'.exit 1fi/bin/mkdir -p ${working_dir}>${data_file}>${user_file}>${log_file}>${err_file}
}
##====================================================##
##1. get mysql version
##====================================================##functionget_mysql_version()
{
master_version_tmp=`${mysql_exe} \--host="${mysql_host}" --port=${mysql_port} \
--user="${mysql_user}" --password="${mysql_password}" \
-e "select @@version;"`if [[ master_version_tmp == 5.5.*]]thenmysql_version="mysql55"
elif[[ master_version_tmp == 5.6.*]]thenmysql_version="mysql56"elsemysql_version="mysql57"
fi
}
##====================================================##
##1. change global long_query_time=100##2、change session sql_log_bin=0##3. change global sync_binlog=0##4. change global innodb_flush_log_at_trx_commit=0##====================================================##functionwrite_load_option()
{
echo "SET SESSION long_query_time=100;" >>${data_file}
echo "SET GLOBAL sync_binlog=2;" >>${data_file}
echo "SET GLOBAL innodb_flush_log_at_trx_commit=0;" >>${data_file}
}
##====================================================##
##1. if this is master server, user option master-data=2##2. if this is slave server, use option dump-slave=2##3. if this is slave server, get slave status andchange master_host
##====================================================##functioncreate_master_slave_option()
{
master_host_ip=`${mysql_exe} \--host="${mysql_host}" --port=${mysql_port} \
--user="${mysql_user}" --password="${mysql_password}" \
-e "show slave status \G" |grep "Master_Host"|head -n 1|awk -F":" '{gsub(" ","",$2);print $2}'`if [[ "$master_host_ip" == "1.1.1.1"]]thenecho "Thisis master server,use --master-data=2" >> ${log_file}
master_slave_data="--master-data=2"
elif [[ "$master_host_ip" == ""]]thenecho "Thisis master server,use --master-data=2" >> ${log_file}
master_slave_data="--master-data=2"
elseecho "Thisis slave server,use --dump-slave=2" >> ${log_file}
master_slave_data="--dump-slave=2"
get_slave_status
fi
}
##====================================================##
##1. dump data from userdatabases.
##====================================================##functiondump_user_data(){
databases=`${mysql_exe} \--host="${mysql_host}" --port=${mysql_port} \
--user="${mysql_user}" --password="${mysql_password}" \
-Ne "SELECTSCHEMA_NAMEFROMinformation_schema.SCHEMATAWHERE SCHEMA_NAME NOT IN ('information_schema','performance_schema','sys','mysql');"`
echo "databases:${databases}">>${log_file}if [[ mysql_version == "mysql55"]]thengtid_purged_option=""elsegtid_purged_option="--set-gtid-purged=OFF"
fi
## 导出建表语句和数据
((echo "Start mysqldump data at "`date "+%y-%m-%d %H:%M:%S"`) \&&( ${mysqldump_exe} \--host="${mysql_host}" \
--port=${mysql_port} \
--user="${mysql_user}" \
--password="${mysql_password}" \
--default-character-set=utf8 \
--hex-blob --opt --quick \
--events --routines --triggers \
--single_transaction \
${gtid_purged_option} \
${master_slave_data} \--databases $databases \
>>${data_file} ) \&& (echo "MySQLdump data success at "`date "+%y-%m-%d %H:%M:%S"`)) \1>>${log_file} \2>>${err_file}
}
##====================================================##
##1. dump user script onmysql
##2. this script only can be used on mysql 5.7##====================================================##functiondump_user_script_5_7()
{
((echo "start mysqldumpuser at "`date "+%y-%m-%d %H:%M:%S"`) \&& (echo "select concat('show create user''',user,'''@''',host, ''';','show grants for''',user,'''@''',host, ''';') from mysql.user where user <>'root' and user<>'' and host <> '' " |\
${mysql_exe}--host="${mysql_host}" --port=${mysql_port} \
--user="${mysql_user}" --password="${mysql_password}" -N | \
${mysql_exe} --host="${mysql_host}" --port=${mysql_port} \
--user="${mysql_user}" --password="${mysql_password}" -N | \
sed "s/$/;/" >>${user_file}) \&& (echo "MySQLdump user success at "`date "+%y-%m-%d %H:%M:%S"`)) \1>>${log_file} \2>>${err_file}
}
##====================================================##
##1. dump user script onmysql
##2. this script only can be used on mysql 5.5##====================================================##functiondump_user_script_5_5()
{
((echo "start mysqldumpuser at "`date "+%y-%m-%d %H:%M:%S"`) \&& (echo "select concat('show grants for''',user,'''@''',host, ''';') from mysql.user where user <>'root' and user<>'' and host <> '' " |\
${mysql_exe}--host="${mysql_host}" --port=${mysql_port} --user="${mysql_user}" --password="${mysql_password}" -N | \
${mysql_exe} --host="${mysql_host}" --port=${mysql_port} --user="${mysql_user}" --password="${mysql_password}" -N | \
sed "s/$/;/" >>${user_file}) \&& (echo "MySQLdump user success at "`date "+%y-%m-%d %H:%M:%S"`)) \1>>${log_file} \2>>${err_file}
}functiondump_user_script()
{if [[ mysql_version == "mysql55"]]thendump_user_script_5_5elsedump_user_script_5_7
fi
}
echo "check and create folder and file"
crete_dump_file
echo "writeload option"
write_load_option
echo "checkmysql version"
get_mysql_version
echo "dump userdata"
dump_user_data
echo "dump user right"
dump_user_script
echo "MySQLdump finished"