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》：

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一键安装包》：

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）：

#!/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 
} 

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