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=3306
mysql_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} ]
    then
        echo 'data file is exists, please check and remove it'.
        exit 1
    fi

    /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.* ]]
    then
        mysql_version="mysql55"
    elif [[ master_version_tmp == 5.6.* ]]
    then
        mysql_version="mysql56"
    else
        mysql_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" ]]
    then
        echo "This is master server,use --master-data=2" >> ${log_file}
        master_slave_data="--master-data=2"
    elif [[ "$master_host_ip" == "" ]]
    then
        echo "This is master server,use --master-data=2" >> ${log_file}
        master_slave_data="--master-data=2"
    else
        echo "This is slave server,use --dump-slave=2" >> ${log_file}
        master_slave_data="--dump-slave=2"
        get_slave_status
    fi
}

##====================================================##
## 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.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('information_schema','performance_schema','sys','mysql');"`

    echo "databases:${databases}" >> ${log_file}

    if [[ mysql_version == "mysql55" ]]
    then
        gtid_purged_option=""
    else
        gtid_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" ]]
    then
        dump_user_script_5_5
    else
        dump_user_script_5_7
    fi
}


echo "check and create folder and file"
crete_dump_file

echo  "write load option"
write_load_option

echo "check mysql version"
get_mysql_version

echo "dump user data"
dump_user_data

echo "dump user right"
dump_user_script

echo "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=3358
mysql_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_env
load_user_data
load_user_right