A faster way to upgrade MySQL to another server

Although mysqldump is recommended for upgrading mysql server. The following process maybe faster and save you headache from issues relative to mysqldump.

Example: upgrade from 5.5.12 to 5.6.14

  1. Make a hot copy of 5.5.12 and restore it to another server.
  2. In the new server, un-install mysql server (keep the data of course).
  3. In the new server, install 5.6.14
  4. Start mysql server
  5. run mysql_upgrade
  6. Done

The following tested and works on CentOS 6:

Make a hot copy of 5.5.12 and restore it to another server using mysqlbackup from Oracle.

On the new database server:

/etc/init.d/mysql stop
rm -rf /var/lib/mysql/*
rpm -e MySQL-server-5.6.16-1.linux_glibc2.5.x86_64
rpm -e MySQL-client-5.6.16-1.linux_glibc2.5.x86_64
/opt/mysql/meb-3.8/bin/mysqlbackup --defaults-file=/backup/backup-my.cnf --backup-dir=/backup/  copy-back --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
yum -y install /opt/MySQL-server-5.5.36-1.linux2.6.x86_64.rpm
yum -y install /opt/MySQL-client-5.5.36-1.linux2.6.x86_64.rpm
/etc/init.d/mysql start
mysql_upgrade
/etc/init.d/mysql stop
rpm -e MySQL-server-5.5.36-1.linux2.6.x86_64
rpm -e MySQL-client-5.5.36-1.linux2.6.x86_64
yum -y install /opt/MySQL-server-5.6.16-1.linux_glibc2.5.x86_64.rpm
yum -y install /opt/MySQL-client-5.6.16-1.linux_glibc2.5.x86_64.rpm
/etc/init.d/mysql start
mysql_upgrade

Done!

Notes on MySQL 5.6:

In MySQL 5.6 root has a default password assigned and its located at /root/.mysql_secret file.

WARNING: Found existing config file /usr/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used –defaults-file)
and when you later start the server.
The new default config file was created as /usr/my-new.cnf,
please compare it with your file and take the changes you need.

Comments

Popular posts from this blog

Check MySQL query history from command line