宝塔升级 mysql 从5.5到5.7

首先，对于整机做一个备份。

搬瓦工还是很棒的，做整机的 snapshot一分钟就能完成。

备份现有的mysql5.5的所有数据库

先用宝塔备份，并对所有数据库的名字和密码进行截屏保存

再用下面的代码，写入 mysql_dump.sh

把”must change” 对应的地方都修改一下

##====================================================================##
# MySQL Dump导出数据和权限脚本
# 如果在主库上备份使用--master-data=2参数
# 如果在从库上备份使用--dump-slave=2参数
##====================================================================##
### (must change) 自己的 mysql 对应的地方
mysql_exe="/www/server/mysql/bin/mysql"
mysqldump_exe="/www/server/mysql/bin/mysqldump"
mysql_host="127.0.0.1"
mysql_port=3306
mysql_user="root"

### (must change) 自己的root 密码
mysql_password="XXXXXXXXXXXX"
working_dir="/data/mysql_bak/"
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;"`
    echo "master_version_tmp:${master_version_tmp}" >> ${log_file}
    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
    echo "mysql_version:${mysql_version}" >> ${log_file}
}


##====================================================##
## 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_load.sh

##====================================================================##
### (must change)
mysql_exe="/www/server/mysql/bin/mysql"
mysqldump_exe="/www/server/mysql/bin/mysqldump"
mysql_host="127.0.0.1"
mysql_port=3306
mysql_user="root"
### (must change)
mysql_password="XXXXXXX"

working_dir="/data/mysql_bak/"
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

备份只要运行

bash mysql_dump.sh

可以看 tail -f mysql_dump_* 来查看是否正常；并且 data_script.sql 里面应该有足够多的 sql 就对了。

现在宝塔里可以升级 mysql 5.7了 – 2G 以上内存才适合

• 

• 

恢复mysql 数据

bash mysql_load.sh

验证 zyx.10244201.xyz

本博客能打开就是最好的验证。