Migrating from MySQL 5.5 or MariaDB 5.5 to the MariaDB 10.0 Software Collection (RHEL6 and RHEL7)

Updated

Red Hat Enterprise Linux 6 (RHEL6) contains MySQL 5.1 as the default MySQL implementation. Red Hat Enterprise Linux 7 (RHEL7) contains MariaDB 5.5 as the default MySQL implementation. MariaDB is a community-developed drop-in replacement for MySQL. In addition to these basic versions, MariaDB 5.5 has been available for RHEL6 and RHEL7 as a Software Collection since Red Hat Software Collections (RHSCL) 1.0. Since RHSCL 2.0, the MariaDB 10.0 Software Collection has been available for RHEL6 and RHEL7. See what issues you can encounter during migration from MySQL 5.5 to MariaDB 10.0 or from MariaDB 5.5 to MariaDB 10.0.

Please note that it is possible to upgrade to MariaDB 10.0 only from MariaDB 5.5 or MySQL 5.5, not from earlier versions. Thus you need to upgrade to MariaDB 5.5 or MySQL 5.5 first. Instructions how to upgrade to MariaDB 5.5 are available in this article and in the RHSCL 1.2 Release Notes.

MariaDB 10.0 is built on the MariaDB 5.5 series with backported features from MySQL 5.6 and with entirely new features unavailable elsewhere. Read more about MariaDB 10.0 in upstream articles Content from mariadb.com is not included.What is MariaDB 10.0 and Content from mariadb.com is not included.Upgrading from MariaDB 5.5 to MariaDB 10.0.

In case of upgrade from MySQL, read what differences MariaDB includes in comparison to MySQL in articles about Content from mariadb.com is not included.compatibility, Content from mariadb.com is not included.features differences, and Content from mariadb.com is not included.upgrading.

Notable differences between the MariaDB 5.5 and MariaDB 10.0 Software Collections

Beside the differences in the above upstream documentation, there are the following notable changes:

  • the name of the service is rh-mariadb100-mariadb in RHEL6 and RHEL7
  • the test database is not created by default
  • configuration files for the MariaDB 10.0 Software Collection are the /etc/opt/rh/rh-mariadb100/my.cnf file and in the /etc/opt/rh/rh-mariadb100/my.cnf.d/ directory
  • variable files including the database files for the MariaDB 10.0 Software Collection are located in the /var/opt/rh/rh-mariadb100/lib/ directory
  • the log file for the MariaDB daemon is /var/opt/rh/rh-mariadb100/log/mariadb/mariadb.log
  • the pid file for the daemon is /var/run/rh-mariadb100-mariadb/mariadb.pid

Please note that the rh-mariadb100 Software Collection supports neither mounting over NFS nor dynamical registering using the scl register command.

How to upgrade

Prior to upgrading, back-up all your data, including any MySQL databases.

Because the rh-mariadb100 Software Collection does not conflict with the mysql or mariadb packages from the core systems, it is possible to install the rh-mariadb100 Software Collection together with the mysql or mariadb packages. It is also possible to run both versions at the same time, however, the port number and the socket in the my.cnf files need to be changed to prevent these specific resources from conflicting.

Upgrading can be performed either by using the mysqldump and mysqlimport utilities or using an in-place upgrade:

  • In the first scenario, the whole dump of all databases from one database is generated, mysql is run with the dump file as an input, using the mysqlimport or the LOAD DATA INFILE SQL command within the other database. At the same time, the appropriate daemons have to be running during both dumping and restoring. You can use the --all-databases option in the mysqldump call to include all databases in the dump. The --routines, --triggers, and --events options can also be used if needed.

  • During the in-place upgrade, the data files are copied from one database directory to another database directory. The daemons must not be running at the time of copying. Set appropriate permissions and SELinux context for the copied files.

After upgrading, start the server and run the mysql_upgrade command. Running mysql_upgrade is necessary to check and repair internal tables.

In case the root user has a non-empty password defined (it should have a password defined), it is necessary to call the mysql_upgrade utility with the -p option and specify the password.

Service names and paths bellow depend on which version you upgrade from.

Example of a Dump and Restore Upgrade

Creating a backup from MariaDB 5.5 in base RHEL7:

~]# service mariadb start
Starting mariadb:                                          [  OK  ]
~]# mysqldump --all-databases --routines --events > dump.sql
~]# service mariadb stop
Stopping mariadb:                                          [  OK  ]

Creating a backup from the MariaDB 5.5 Software Collection:

~]# service mariadb55-mysqld start
Starting mariadb55-mysqld:                                 [  OK  ]
~]# scl enable mariadb55 -- mysqldump --all-databases --routines --events > dump.sql
~]# service mariadb55-mysqld stop
Stopping mariadb55-mysqld:                                 [  OK  ]

For upgrading from the MariaDB 5.5 Software Collection in RHEL7, use mariadb55-mariadb as the service name.
For upgrading from the MySQL 5.5 Software Collection, use mysql55-mysqld as the service name.

Importing the dumped database into the MariaDB 10.0 Software Collection:

~]# service rh-mariadb100-mariadb start
Starting rh-mariadb100-mariadb:                            [  OK  ]
~]# scl enable rh-mariadb100 'mysql' < dump.sql
~]# scl enable rh-mariadb100 'mysql_upgrade -u root -p'
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
a.t1                                               OK
mysql.columns_priv                                 OK
<skipped tables list>
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
OK

Example of an In-place Upgrade from MariaDB 5.5 in Base RHEL7

~]# service mariadb stop
Stopping mariadb:                                          [  OK  ]
~]# service rh-mariadb100-mariadb stop
Stopping rh-mariadb100-mariadb:                            [  OK  ]
~]# rm -rf /var/opt/rh/rh-mariadb100/lib/mysql/
~]# cp -r /var/lib/mysql/ /var/opt/rh/rh-mariadb100/lib/
~]# chown -R mysql:mysql /var/opt/rh/rh-mariadb100/lib/mysql/
~]# restorecon -R /var/opt/rh/rh-mariadb100/lib/mysql/
~]# service rh-mariadb100-mariadb start
Starting rh-mariadb100-mariadb:                            [  OK  ]
~]# scl enable rh-mariadb100 'mysql_upgrade -u root -p'
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
a.t1                                               OK
mysql.columns_priv                                 OK
<skipped tables list>
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
OK

For upgrading from the MariaDB 5.5 Software Collection, use /opt/rh/mariadb55/root/var/lib/mysql/ as a source when copying the data.
For upgrading from the MySQL 5.5 Software Collection, use /opt/rh/mysql55/root/var/lib/mysql/ as a source when copying the data.

Product(s)
Category
Components
Tags
Article Type