I have got a task to migrate a system from a CentOS server and another CentOS server. When I imported the MySQL database to the server, I have got the following error:

(SQL Error: 1294 SQL State: HY000) - Invalid ON UPDATE clause for 'column_name' field

Oh…. DATETIME can use CURRENT_TIMESTAMP on update starting from MySQL 5.6 whereas my server is MySQL 5.5. I have two choices – use other workarounds or upgrade MySQL. Finally, I decide to upgrade the MySQL.

Before upgrade, you MUST MUST MUST BACKUP BACKUP BACKUP BACKUP and BACKUP current databases. To have a complete backups, I recommend you to do both of the followings.

  • backup all databases in SQL files (use mysqldump or phpMyAdmin)
  • backup the whole database folder (default path: /var/lib/mysql/)
  • Prepare a list of database username and passwords and also the granted permissions

Here is my shell script to backup all databases into single file for each database.

#!/bin/bash
# MySQL Super Admin User
USER='root'
# MySQL Super Admin Password
PASSWORD='root_password'
# Backup Directory - NO TAILING SLASH!
# Before running the script, ensure that the directory exists
OUTPUT="/home/user/database_dir"
 
##### And 
TIMESTAMP=`date +%Y%m%d_%H%M%S`;
mkdir $OUTPUT/$TIMESTAMP;
cd $OUTPUT/$TIMESTAMP;
echo "Starting MySQL Backup";
echo `date`;
databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > dbbackup-$TIMESTAMP-$db.sql
    fi
done
echo "Finished MySQL Backup";
echo `date`;

find /mnt/backup -mtime +10 -type f -delete

You have to create users again and restore the databases manually one by one.

START UPGRADE MySQL NOW

  1. Make sure you do the backup as mentioned above
  2. Stop mysql
    # service mysqld stop
  3. Get the yum repository for MySQL 5.7
    # wget http://dev.mysql.com/get/mysql57-community-release-el6.rpm
  4. Install the yum repository
     # yum localinstall mysql57-community-release-el6.rpm
  5. Use yum to upgrade mysql
     # yum install mysql-community-server
  6. Check current mysql version (should display 5.7)
     # mysql --version

Now, if you cannot use original username and password to login, and you cannot restart mysql, no worry! Let’s continue for migration.

MIGRATION

A temporary password may be reset after installation of MySQL Server. You can find this password in the log file by the following command

# grep "temporary password" /var/log/mysqld.log

If you still get the “Access denied message”, you may try the following steps

  1. Rename the current mysql directory
    # mv /var/lib/mysql /var/lib/mysql-today
  2. Change root password in safe mode (run as root user)
    # mysqld_safe --skip-grant-tables &
    # mysql -uroot
        use mysql;
        update user set authentication_string=PASSWORD("new_password") where User='root';
        flush privileges;
        quit;
  3. Start MySQL
     # service mysqld start
  4. You should be able to login as the root with the new reset password now
    # mysql -u root -p

 

Once the root password reset, you can do the database migration manually

  1. Create the users, databases and privileges
  2. Restore the databases from sql files one by one

ADDITIONAL NOTE: Error occurs in phpMyAdmin

You may get an error when you access to phpMyAdmin. You can simply upgrade phpMyAdmin to the latest version to resolve the error. Get the package from phpMyAdmin download page and replace the package in your server. For sure you have to backup specific configurations for your preferences.

 

I want to find a solution to prevent restoring users and databases but seems to be impossible. At least the column password is changed to authentication_string in the newer MySQL. Welcome to share more information if you know.