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##====================================================##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"