Migrating from MySQL 5.1 (RHEL6) to MariaDB 5.5 (RHEL7)
RHEL6 contains MySQL 5.1 as a default MySQL implementation. RHEL7 contains MariaDB 5.5 as a default MySQL implementation. MariaDB is a community-developed a drop-in replacement for MySQL. For more information about MariaDB, see Content from mariadb.org is not included.MariaDB Upstream Web.
In order to migrate from MySQL 5.1 to MariaDB you need to pay attention to configuration changes, naming changes and data migration.
General changes between MySQL 5.1 and MariaDB 5.5 packages
MariaDB upstream uses the same file names as original MySQL. That means MariaDB shell is called mysql, MariaDB daemon is called mysqld_safe and client library is called libmysqlclient.
In order to keep MariaDB properly distinguished from original MySQL, RHEL7 contains MariaDB names where not necessary to follow upstream. It means the following layout is used in RHEL7:
- the packages names are called mariadb, mariadb-libs, mariadb-server, etc.
- only packages mariadb and mariadb-libs provide also RPM symbols mysql and mysql-libs; the rest of packages don't provide alternative mysql names
- the systemd unit file is called mariadb.service
- the log file is called mariadb.log and is stored in /var/log/mariadb directory
- the logrotate script is called mariadb
Starting with MariaDB 5.5, the InnoDB storage engine (formerly known as InnoDB Plugin) is the default storing engine.
InnoDB and some other plug-ins (for example, archive, blackhole and federated) were installable plug-ins in MySQL 5.1. Starting with MariaDB 5.5, these plug-ins became compiled-in storage engines, that is, they do not have to be installed nor uninstalled.
Notable Differences Between MySQL 5.1 and MariaDB 5.5 configuration
The following is a list of the most important changes between MySQL 5.1 and MariaDB 5.5 configuration, but all apply also as differences between MySQL 5.1 and MySQL 5.5, these are not specific for MariaDB but for differences between versions 5.1 and 5.5.
If you used InnoDB Plugin and it was loaded using the plugin-load=innodb=ha_innodb_plugin.so configuration option, you need to remove this configuration option as it does not work in MariaDB 5.5.
In MySQL 5.1, InnoDB Plugin included a configuration variable innodb_file_io_threads. However, this variable does not exist in MariaDB 5.5; new variables, innodb_read_io_threads and innodb_write_io_threads, are used instead. To ensure proper functionality, either remove the former variable from the configuration file or replace it with the current variables.
MySQL 5.1 used the language variable for specifying the directory which included the error message file. This option is now deprecated and has been replaced by the lc_messages_dir and lc_messages options. This also applies for configuration options. Also, error messages no longer contain mixed set of character sets and error messages are returned in the set following the character_set_results system variable instead. That is, some error messages can be different in MariaDB 5.5.
When upgrading from MySQL 5.1 to MariaDB 5.5 using the in-place upgrading method, the mysql.proxies_priv table will not exist. To create the missing table, the mysql_upgrade utility has to be run as soon as the new daemon is started.
MariaDB 5.5 uses latin1 for the stopword file if the character_set_server variable is ucs2, utf16 or utf32. Thus, if the table uses FULLTEXT indexes in these cases, users should repair the table using the REPAIR TABLE table_name QUICK.
For more information about MySQL 5.1 and MariaDB 5.5, refer to the release notes available at Content from dev.mysql.com is not included.Content from dev.mysql.com is not included.http://dev.mysql.com/doc/relnotes/mysql/5.1/en/ and Content from dev.mysql.com is not included.Content from dev.mysql.com is not included.http://dev.mysql.com/doc/relnotes/mysql/5.5/en/.
Upgrading from MySQL 5.1 to MariaDB 5.5 (or MySQL 5.5)
Before migrating from MySQL 5.1 to MariaDB 5.5, back up all your data, including any MySQL databases.
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 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 kept in the directory and MySQL 5.1 packages are upgraded to MariaDB 5.5 packages. You can also copy the data files in case the upgrade is not performed on the same system. The daemons should not be running at the time of copying. Set the appropriate permissions and SELinux context for copied files.
After upgrading, start the MariaDB server and run the mysql_upgrade command. Running mysql_upgrade is necessary to check and repair internal tables. All scripts that work with a server form Software Collection, especially the mysql_upgrade script, should be run inside the scl enable environment.
The in-place upgrade method is usually faster, however, there are certain risks and known problems. For more information, refer to the MySQL 5.5 Release Notes.
In addition, once the upgrade is complete, consider changing the appropriate settings in the my.cnf file to reflect the environment.
Example: Dump and Restore Upgrade
```
RHEL-6 ~]# service mysqld start
Starting mysqld: [ OK ]
RHEL-6 ~]# mysqldump --all-databases --routines --events > dump.sql
RHEL-6 ~]# service mysqld stop
Stopping mysqld: [ OK ]
RHEL-7 ~]# systemctl start mariadb
RHEL-7 ~]# mysql < dump.sql
RHEL-7 ~]# mysql_upgrade
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: In-place Upgrade
```
RHEL-6 ~]# service mysqld stop
Stopping mysqld: [ OK ]
RHEL-7 ~]# systemctl stop mariadb
RHEL-7 ~]# rm -rf /var/lib/mysql/
RHEL-7 ~]# cp -r /backup/var/lib/mysql/ /var/lib/mysql/
RHEL-7 ~]# chown -R mysql:mysql /var/lib/mysql/
RHEL-7 ~]# restorecon -R /var/lib/mysql/
RHEL-7 ~]# systemctl start mariadb
RHEL-7 ~]# mysql_upgrade
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 more information about the upgrading process, refer to Content from dev.mysql.com is not included.MySQL 5.5 Reference Manual.