MySQL--使用mysqldump进行数据库版本升级

在MySQL跨版本升级时，建议使用mysqldump方式导出用户权限和用户数据，即使是小版本升级，导出过程中也应忽略系统数据库，避免系统表不兼容。

导出用户数据库脚本和用户创建脚本

##====================================================================### 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##====================================================##function crete_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##====================================================##function get_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##====================================================##function write_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 and change master_host##====================================================##function create_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 "This is master server,use --master-data=2" >> ${log_file}master_slave_data="--master-data=2"elif [[ "$master_host_ip" == "" ]]thenecho "This is master server,use --master-data=2" >> ${log_file}master_slave_data="--master-data=2"elseecho "This is slave server,use --dump-slave=2" >> ${log_file}master_slave_data="--dump-slave=2"get_slave_statusfi}##====================================================#### 1. dump data from user databases.##====================================================##function dump_user_data(){databases=`${mysql_exe} \--host="${mysql_host}" --port=${mysql_port} \--user="${mysql_user}" --password="${mysql_password}" \-Ne "SELECT SCHEMA_NAME FROM information_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 on mysql## 2. this script only can be used on mysql 5.7##====================================================##function dump_user_script_5_7(){((echo "start mysqldump user 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 on mysql## 2. this script only can be used on mysql 5.5##====================================================##function dump_user_script_5_5(){((echo "start mysqldump user 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} }function dump_user_script(){if [[ mysql_version == "mysql55" ]]thendump_user_script_5_5elsedump_user_script_5_7fi}echo "check and create folder and file"crete_dump_fileecho  "write load option"write_load_optionecho "check mysql version"get_mysql_versionecho "dump user data"dump_user_dataecho "dump user right"dump_user_scriptecho "MySQL dump finished"

 

导入用户数据库和用户脚本

 

##====================================================================##mysql_exe="/export/servers/mysql/bin/mysql"mysqldump_exe="/export/servers/mysql/bin/mysqldump"mysql_host="127.0.0.1"mysql_port=3358mysql_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_load_log.txt"err_file="${working_dir}/mysql_load_err.txt"##====================================================#### 1. init_env##====================================================##function init_env(){echo "init env"echo > ${log_file}echo > ${err_file}}##====================================================#### 1. load user data ##====================================================##function load_user_data(){echo "start to load user data"((echo "Start load data at "`date "+%y-%m-%d %H:%M:%S"`) \&& ( ${mysql_exe} \--host="${mysql_host}" --port=${mysql_port} \--user="${mysql_user}" --password="${mysql_password}" \--batch < ${data_file} ) \&& (echo "load data success at "`date "+%y-%m-%d %H:%M:%S"`)) \1>>${log_file} \2>>${err_file} echo "end to load user data"}##====================================================#### 1. load user right##====================================================##function load_user_right(){echo "start to load user right"((echo "Start load use right at "`date "+%y-%m-%d %H:%M:%S"`) \&& ( ${mysql_exe} \--host="${mysql_host}" --port=${mysql_port} \--user="${mysql_user}" --password="${mysql_password}" \--batch < ${user_file} ) \&& (echo "load user right at "`date "+%y-%m-%d %H:%M:%S"`)) \1>>${log_file} \2>>${err_file} echo "end to load user right"}init_envload_user_dataload_user_right