MySQL/MariaDB/Percona数据库升级脚本

平湖正月

mysql/mariadb/percona数据库升级脚本

mysql/mariadb/percona数据库升级脚本截取《oneinstack》中upgrade_db.sh，一般情况下不建议升级数据库版本，该脚本专提供给各位版本控们。为防止大版本之间兼容问题，脚本默认仅支持同一大版本之间的升级，如：mysql-5.6.25升级到mysql-5.6.26、mysql-5.5.44升级到mysql-5.5.45、mariadb-10.0.20升级到mariadb-10.0.21；不能跨分支版本且不能跨大版本，如从mysql-5.5.44升级到perconal-5.5.44-37.3、mysql-5.5.44升级到mysql-5.6.25。

2015-07-16之前版本如何支持数据库版本升级？

《oneinstack》：

?

1 2 3 4 5 6 7 8

cd /root/ cp oneinstack/options.conf ~ #备份options.conf rm -rf oneinstack #删除旧版 wget http://mirrors.linuxeye.com/oneinstack-full.tar.gz #下载最新版 tar xzf oneinstack-full.tar.gz /bin/mv ~/options.conf ./oneinstack #还原options.conf, 必须使用旧版options.conf文件 cd oneinstack ./upgrade db #升级数据库版本，如下图：

《lnmp一键安装包》：

?

1 2 3 4 5 6 7

cp lnmp/options.conf ~ #备份options.conf rm -rf lnmp #删除旧版 wget http://mirrors.linuxeye.com/lnmp-full.tar.gz #下载最新版 tar xzf lnmp-full.tar.gz /bin/mv ~/options.conf ./lnmp #还原options.conf, 必须使用旧版options.conf文件 cd lnmp ./upgrade db #升级数据库版本

ps：mysql/mariadb/percona数据库升级脚本内容如下（cat upgrade_db.sh）：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223

#!/bin/bash # author: yeho <lj2007331 at gmail.com> # blog: https://blog.linuxeye.com # # notes: oneinstack for centos/radhat 5+ debian 6+ and ubuntu 12+ # # project home page: #    http://oneinstack.com #    https://github.com/lj2007331/oneinstack   upgrade_db() { cd $oneinstack_dir/src [ ! -e "$db_install_dir/bin/mysql" ] && echo "${cwarning}the mysql/mariadb/percona is not installed on your system! ${cend}" && exit 1 old_db_version_tmp=`$db_install_dir/bin/mysql -v | awk '{print $5}' | awk -f, '{print $1}'` db_tmp=`echo $old_db_version_tmp | awk -f'-' '{print $2}'` if [ "$db_tmp" == 'mariadb' ];then   [ "$ipaddr_state"x == "cn"x ] && down_addr=http://mirrors.aliyun.com/mariadb || down_addr=https://downloads.mariadb.org/f   libc_version=`getconf -a | grep gnu_libc_version | awk '{print $nf}'`   libc_yn=`echo "$libc_version < 2.14" | bc`   [ $libc_yn == '1' ] && glibc_flag=linux || glibc_flag=linux-glibc_214   db=mariadb   old_db_version=`echo $old_db_version_tmp | awk -f'-' '{print $1}'` elif [ -n "$db_tmp" -a "$db_tmp" != 'mariadb' ];then   db=percona   old_db_version=$old_db_version_tmp else   db=mysql   old_db_version=$old_db_version_tmp fi   #backup while : do   $db_install_dir/bin/mysql -uroot -p${dbrootpwd} -e "quit" >/dev/null 2>&1   if [ $? -eq 0 ];then     break   else     echo     read -p "please input the root password of database: " new_dbrootpwd     $db_install_dir/bin/mysql -uroot -p${new_dbrootpwd} -e "quit" >/dev/null 2>&1     if [ $? -eq 0 ];then       dbrootpwd=$new_dbrootpwd       sed -i "s+^dbrootpwd.*+dbrootpwd='$dbrootpwd'+" ../options.conf       break     else       echo "${cfailure}$db root password incorrect,please enter again! ${cend}"     fi   fi done   echo echo "${csuccess}starting $db backup${cend}......" $db_install_dir/bin/mysqldump -uroot -p${dbrootpwd} --opt --all-databases > db_all_backup_$(date +"%y%m%d").sql [ -f "db_all_backup_$(date +"%y%m%d").sql" ] && echo "$db backup success, backup file: ${msg}`pwd`/db_all_backup_$(date +"%y%m%d").sql${cend}"   #upgrade echo echo "current $db version: ${cmsg}$old_db_version${cend}" [ -e /usr/local/lib/libtcmalloc.so ] && { je_tc_malloc=2; exe_linker="-dcmake_exe_linker_flags='-ltcmalloc'"; } [ -e /usr/local/lib/libjemalloc.so ] && { je_tc_malloc=1; exe_linker="-dcmake_exe_linker_flags='-ljemalloc'"; }   while : do   echo   read -p "please input upgrade $db version(example: $old_db_version): " new_db_version   if [ `echo $new_db_version | awk -f. '{print $1"."$2}'` == `echo $old_db_version | awk -f. '{print $1"."$2}'` ]; then     if [ "$db" == 'mariadb' ];then       db_name=mariadb-${new_db_version}-${glibc_flag}-${sys_bit_b}       db_url=$down_addr/mariadb-${new_db_version}/bintar-${glibc_flag}-$sys_bit_a/$db_name.tar.gz     elif [ "$db" == 'percona' ];then       db_name=percona-server-$new_db_version       db_url=http://www.percona.com/redir/downloads/percona-server-`echo $new_db_version | awk -f. '{print $1"."$2}'`/latest/source/tarball/$db_name.tar.gz     elif [ "$db" == 'mysql' ];then       db_name=mysql-$new_db_version       db_url=http://cdn.mysql.com/downloads/mysql-`echo $new_db_version | awk -f. '{print $1"."$2}'`/$db_name.tar.gz     fi       [ ! -e "$db_name.tar.gz" ] && wget --no-check-certificate -c $db_url > /dev/null 2>&1               if [ -e "$db_name.tar.gz" ];then         echo "download [${cmsg}$db_name.tar.gz${cend}] successfully! "       else         echo "${cwarning}$db version does not exist! ${cend}"       fi       break   else       echo "${cwarning}input error! ${cend}please only input '${cmsg}${old_db_version%.*}.xx${cend}'"   fi done   if [ -e "$db_name.tar.gz" ];then   echo "[${cmsg}$db_name.tar.gz${cend}] found"   echo "press ctrl+c to cancel or press any key to continue..."   char=`get_char`   if [ "$db" == 'mariadb' ];then     service mysqld stop     mv ${mariadb_install_dir}{,_old_`date +"%y%m%d"`}     mv ${mariadb_data_dir}{,_old_`date +"%y%m%d"`}     mkdir -p $mariadb_data_dir;chown mysql.mysql -r $mariadb_data_dir     tar xzf $db_name.tar.gz     [ ! -d "$mariadb_install_dir" ] && mkdir -p $mariadb_install_dir     mv mariadb-${new_db_version}-*-${sys_bit_b}/* $mariadb_install_dir     if [ "$je_tc_malloc" == '1' -a "`echo $old_db_version_tmp | awk -f'.' '{print $1"."$2}'`" != '10.1' ];then       sed -i 's@executing mysqld_safe@executing mysqld_safe\nexport ld_preload=/usr/local/lib/libjemalloc.so@' $mariadb_install_dir/bin/mysqld_safe     elif [ "$je_tc_malloc" == '2' -a "`echo $old_db_version_tmp | awk -f'.' '{print $1"."$2}'`" != '10.1' ];then       sed -i 's@executing mysqld_safe@executing mysqld_safe\nexport ld_preload=/usr/local/lib/libtcmalloc.so@' $mariadb_install_dir/bin/mysqld_safe     fi     $mariadb_install_dir/scripts/mysql_install_db --user=mysql --basedir=$mariadb_install_dir --datadir=$mariadb_data_dir     chown mysql.mysql -r $mariadb_data_dir     service mysqld start     $mariadb_install_dir/bin/mysql < db_all_backup_$(date +"%y%m%d").sql      service mysqld restart     $mariadb_install_dir/bin/mysql -uroot -p${dbrootpwd} -e "drop database test;" >/dev/null 2>&1     $mariadb_install_dir/bin/mysql -uroot -p${dbrootpwd} -e "reset master;" >/dev/null 2>&1     [ $? -eq 0 ] && echo "you have ${cmsg}successfully${cend} upgrade from ${cmsg}$old_db_version${cend} to ${cmsg}$new_db_version${cend}"   elif [ "$db" == 'percona' ];then     tar zxf $db_name.tar.gz      cd $db_name     make clean     if [ "`echo $new_db_version | awk -f. '{print $1"."$2}'`" == '5.5' ];then       cmake . -dcmake_install_prefix=$percona_install_dir \ -dmysql_datadir=$percona_data_dir \ -dsysconfdir=/etc \ -dwith_innobase_storage_engine=1 \ -dwith_partition_storage_engine=1 \ -dwith_federated_storage_engine=1 \ -dwith_blackhole_storage_engine=1 \ -dwith_myisam_storage_engine=1 \ -dwith_archive_storage_engine=1 \ -dwith_readline=1 \ -denable_dtrace=0 \ -denabled_local_infile=1 \ -ddefault_charset=utf8mb4 \ -ddefault_collation=utf8mb4_general_ci \ $exe_linker     else       cmake . -dcmake_install_prefix=$percona_install_dir \ -dmysql_datadir=$percona_data_dir \ -dsysconfdir=/etc \ -dwith_innobase_storage_engine=1 \ -dwith_partition_storage_engine=1 \ -dwith_federated_storage_engine=1 \ -dwith_blackhole_storage_engine=1 \ -dwith_myisam_storage_engine=1 \ -dwith_archive_storage_engine=1 \ -denabled_local_infile=1 \ -denable_dtrace=0 \ -ddefault_charset=utf8mb4 \ -ddefault_collation=utf8mb4_general_ci \ $exe_linker     fi     make -j `grep processor /proc/cpuinfo | wc -l`     service mysqld stop     mv ${percona_install_dir}{,_old_`date +"%y%m%d"`}     mv ${percona_data_dir}{,_old_`date +"%y%m%d"`}     [ ! -d "$percona_install_dir" ] && mkdir -p $percona_install_dir     mkdir -p $percona_data_dir;chown mysql.mysql -r $percona_data_dir     make install     cd ..     $percona_install_dir/scripts/mysql_install_db --user=mysql --basedir=$percona_install_dir --datadir=$percona_data_dir     chown mysql.mysql -r $percona_data_dir     service mysqld start     $percona_install_dir/bin/mysql < db_all_backup_$(date +"%y%m%d").sql     service mysqld restart     $percona_install_dir/bin/mysql -uroot -p${dbrootpwd} -e "drop database test;" >/dev/null 2>&1     $percona_install_dir/bin/mysql -uroot -p${dbrootpwd} -e "reset master;" >/dev/null 2>&1     [ $? -eq 0 ] && echo "you have ${cmsg}successfully${cend} upgrade from ${cmsg}$old_db_version${cend} to ${cmsg}$new_db_version${cend}"   elif [ "$db" == 'mysql' ];then     tar zxf $db_name.tar.gz     cd $db_name     make clean     if [ "`echo $new_db_version | awk -f. '{print $1"."$2}'`" == '5.5' ];then       cmake . -dcmake_install_prefix=$mysql_install_dir \ -dmysql_datadir=$mysql_data_dir \ -dsysconfdir=/etc \ -dwith_innobase_storage_engine=1 \ -dwith_partition_storage_engine=1 \ -dwith_federated_storage_engine=1 \ -dwith_blackhole_storage_engine=1 \ -dwith_myisam_storage_engine=1 \ -dwith_archive_storage_engine=1 \ -dwith_readline=1 \ -denabled_local_infile=1 \ -denable_dtrace=0 \ -ddefault_charset=utf8mb4 \ -ddefault_collation=utf8mb4_general_ci \ -dwith_embedded_server=1 \ $exe_linker     else       cmake . -dcmake_install_prefix=$mysql_install_dir \ -dmysql_datadir=$mysql_data_dir \ -dsysconfdir=/etc \ -dwith_innobase_storage_engine=1 \ -dwith_partition_storage_engine=1 \ -dwith_federated_storage_engine=1 \ -dwith_blackhole_storage_engine=1 \ -dwith_myisam_storage_engine=1 \ -denabled_local_infile=1 \ -denable_dtrace=0 \ -ddefault_charset=utf8mb4 \ -ddefault_collation=utf8mb4_general_ci \ -dwith_embedded_server=1 \ $exe_linker     fi     make -j `grep processor /proc/cpuinfo | wc -l`     service mysqld stop     mv ${mysql_install_dir}{,_old_`date +"%y%m%d"`}     mv ${mysql_data_dir}{,_old_`date +"%y%m%d"`}     [ ! -d "$mysql_install_dir" ] && mkdir -p $mysql_install_dir     mkdir -p $mysql_data_dir;chown mysql.mysql -r $mysql_data_dir     make install     cd ..     $mysql_install_dir/scripts/mysql_install_db --user=mysql --basedir=$mysql_install_dir --datadir=$mysql_data_dir     chown mysql.mysql -r $mysql_data_dir     service mysqld start     $mysql_install_dir/bin/mysql < db_all_backup_$(date +"%y%m%d").sql     service mysqld restart     $mysql_install_dir/bin/mysql -uroot -p${dbrootpwd} -e "drop database test;" >/dev/null 2>&1     $mysql_install_dir/bin/mysql -uroot -p${dbrootpwd} -e "reset master;" >/dev/null 2>&1     [ $? -eq 0 ] && echo "you have ${cmsg}successfully${cend} upgrade from ${cmsg}$old_db_version${cend} to ${cmsg}$new_db_version${cend}"   fi fi }

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！