Upgrading MySQL 5.1 to MariaDB 10.0 on CentOS 6

Today we’re going to cover how to upgrade MySQL 5.1 to MariaDB 10 on Centos 6 in place. This tutorial is a general outline, and the steps were performed on an out-of-the-box install of MySQL 5.1. Do be careful to check your configuration file(s) when completed.

Information on variables and other changes:
http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html
http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
https://mariadb.com/kb/en/mariadb/mariadb-documentation/getting-started/…
https://mariadb.com/kb/en/mariadb/mariadb-documentation/optimization-and…

For this tutorial, we will be following the standard best practice of upgrading to 5.5 first (MariaDB 5.5 in this case). Upgrading directly to MariaDB 10 will fail.

1. Backup

Backup your database(s) with your preferred method. For this tutorial, we’ll just use mysqldump. Also be sure to make a copy of your configuration file(s).

[root@upgradeserver /]# mysqldump --all-databases > /tmp/backup.sql
[root@upgradeserver /]# cp /etc/my.cnf /etc/my.cnf.bak

Stop MySQL.

[root@upgradeserver yum.repos.d]# service mysql stop

Make a copy of the data directory just in case.

[root@upgradeserver yum.repos.d]# cp -R /var/lib/mysql /tmp/mysql_backup

2. Configure the MariaDB 5.5 Repository

Use the MariaDB repository configurator to setup a MariaDB 5.5 repo.

https://downloads.mariadb.org/mariadb/repositories/

Copy the output to a new file in /etc/yum.repos.d. I suggest naming it MariaDB55.repo.

[root@upgradeserver yum.repos.d]# cat MariaDB55.repo
# MariaDB 5.5 CentOS repository list - created 2014-04-02 18:03 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Clean the repository cache information.

[root@upgradeserver yum.repos.d]# yum clean all
Loaded plugins: fastestmirror
Cleaning repos: base extras mariadb updates
Cleaning up Everything
Cleaning up list of fastest mirrors

3. Remove MySQL 5.1

Use yum to remove MySQL 5.1.

[root@upgradeserver yum.repos.d]# yum remove mysql-server

4. Install MariaDB 5.5

[root@upgradeserver yum.repos.d]# yum install mysql-server
Loaded plugins: fastestmirror
Determining fastest mirrors
 * base: mirror.thelinuxfix.com
 * extras: mirror.raystedman.net
 * updates: mirror.thelinuxfix.com
base                                                                            | 3.7 kB     00:00
base/primary_db                                                                 | 4.4 MB     00:01
extras                                                                          | 3.4 kB     00:00
extras/primary_db                                                               |  19 kB     00:00
mariadb                                                                         | 1.9 kB     00:00
mariadb/primary_db                                                              |  16 kB     00:00
updates                                                                         | 3.4 kB     00:00
updates/primary_db                                                              | 3.7 MB     00:01
Setting up Install Process
Package mysql-server is obsoleted by MariaDB-server, trying to install MariaDB-server-5.5.38-1.el6.x86_64 instead
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-server.x86_64 0:5.5.38-1.el6 will be installed
--> Processing Dependency: MariaDB-client for package: MariaDB-server-5.5.38-1.el6.x86_64
--> Running transaction check
---> Package MariaDB-client.x86_64 0:5.5.38-1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================
 Package                     Arch                Version                    Repository            Size
=======================================================================================================
Installing:
 MariaDB-server              x86_64              5.5.38-1.el6               mariadb               43 M
Installing for dependencies:
 MariaDB-client              x86_64              5.5.38-1.el6               mariadb               10 M

Transaction Summary
=======================================================================================================
Install       2 Package(s)

Total download size: 53 M
Installed size: 217 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): MariaDB-5.5.38-centos6-x86_64-client.rpm                                 |  10 MB     00:15
(2/2): MariaDB-5.5.38-centos6-x86_64-server.rpm                                 |  43 MB     00:25
-------------------------------------------------------------------------------------------------------
Total                                                                  1.3 MB/s |  53 MB     00:40
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : MariaDB-client-5.5.38-1.el6.x86_64                                                  1/2
  Installing : MariaDB-server-5.5.38-1.el6.x86_64                                                  2/2
  Verifying  : MariaDB-server-5.5.38-1.el6.x86_64                                                  1/2
  Verifying  : MariaDB-client-5.5.38-1.el6.x86_64                                                  2/2

Installed:
  MariaDB-server.x86_64 0:5.5.38-1.el6

Dependency Installed:
  MariaDB-client.x86_64 0:5.5.38-1.el6

Complete!

5. Start MySQL

[root@upgradeserver yum.repos.d]# service mysql start
Starting MySQL.. SUCCESS!

6. Run mysql_upgrade

[root@upgradeserver yum.repos.d]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
information_schema
mysql
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.servers                                      OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
test
upgrade_test
Phase 3/3: Running 'mysql_fix_privilege_tables'...
OK

7. Check Upgrade and Verify Databases

[root@upgradeserver yum.repos.d]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 2
Server version: 5.5.38-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| upgrade_test       |
+--------------------+
4 rows in set (0.00 sec)

8. Remove MariaDB 5.5

[root@upgradeserver yum.repos.d]# yum remove mysql-server mysql-client
Loaded plugins: fastestmirror
Setting up Remove Process
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:5.5.38-1.el6 will be erased
---> Package MariaDB-server.x86_64 0:5.5.38-1.el6 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================
 Package                     Arch                Version                   Repository             Size
=======================================================================================================
Removing:
 MariaDB-client              x86_64              5.5.38-1.el6              @mariadb               43 M
 MariaDB-server              x86_64              5.5.38-1.el6              @mariadb              174 M

Transaction Summary
=======================================================================================================
Remove        2 Package(s)

Installed size: 217 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Erasing    : MariaDB-server-5.5.38-1.el6.x86_64                                                  1/2
  Erasing    : MariaDB-client-5.5.38-1.el6.x86_64                                                  2/2
  Verifying  : MariaDB-server-5.5.38-1.el6.x86_64                                                  1/2
  Verifying  : MariaDB-client-5.5.38-1.el6.x86_64                                                  2/2

Removed:
  MariaDB-client.x86_64 0:5.5.38-1.el6               MariaDB-server.x86_64 0:5.5.38-1.el6

Complete!

9. Disable MariaDB 5.5 Repo

[root@upgradeserver yum.repos.d]# mv MariaDB55.repo MariaDB55.repo.disabled

10. Configure the MariaDB 10 Repository

As we did with the MariaDB 5.5 repository, use the MariaDB repository configurator to setup a MariaDB 10 repo. https://downloads.mariadb.org/mariadb/repositories/ Copy the output to a new file in /etc/yum.repos.d. I suggest naming it MariaDB10.repo.

[root@upgradeserver /]# cd /etc/yum.repos.d
[root@upgradeserver yum.repos.d]# cat MariaDB10.repo
# MariaDB 10.0 CentOS repository list - created 2014-07-20 22:19 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Clean the repository cache information.

[root@upgradeserver yum.repos.d]# yum clean all
Loaded plugins: fastestmirror
Cleaning repos: base extras mariadb updates
Cleaning up Everything
Cleaning up list of fastest mirrors

11. Install MariaDB 10

[root@upgradeserver yum.repos.d]# yum install mysql-server mysql-client
Loaded plugins: fastestmirror
Determining fastest mirrors
 * base: ftp.linux.ncsu.edu
 * extras: mirror.raystedman.net
 * updates: centos.hostingxtreme.com
base                                                                            | 3.7 kB     00:00
base/primary_db                                                                 | 4.4 MB     00:02
extras                                                                          | 3.4 kB     00:00
extras/primary_db                                                               |  19 kB     00:00
mariadb                                                                         | 1.9 kB     00:00
mariadb/primary_db                                                              |  18 kB     00:00
updates                                                                         | 3.4 kB     00:00
updates/primary_db                                                              | 3.7 MB     00:03
Setting up Install Process
Package mysql-server is obsoleted by MariaDB-server, trying to install MariaDB-server-10.0.12-1.el6.x86_64 instead
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.0.12-1.el6 will be installed
---> Package MariaDB-server.x86_64 0:10.0.12-1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================
 Package                     Arch                Version                    Repository            Size
=======================================================================================================
Installing:
 MariaDB-client              x86_64              10.0.12-1.el6              mariadb               11 M
 MariaDB-server              x86_64              10.0.12-1.el6              mariadb               52 M

Transaction Summary
=======================================================================================================
Install       2 Package(s)

Total download size: 63 M
Installed size: 264 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): MariaDB-10.0.12-centos6-x86_64-client.rpm                                |  11 MB     00:07
(2/2): MariaDB-10.0.12-centos6-x86_64-server.rpm                                |  52 MB     00:29
-------------------------------------------------------------------------------------------------------
Total                                                                  1.7 MB/s |  63 MB     00:37
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : MariaDB-client-10.0.12-1.el6.x86_64                                                 1/2
  Installing : MariaDB-server-10.0.12-1.el6.x86_64                                                 2/2
  Verifying  : MariaDB-server-10.0.12-1.el6.x86_64                                                 1/2
  Verifying  : MariaDB-client-10.0.12-1.el6.x86_64                                                 2/2

Installed:
  MariaDB-client.x86_64 0:10.0.12-1.el6              MariaDB-server.x86_64 0:10.0.12-1.el6

Complete!

12. Start MariaDB 10

[root@upgradeserver yum.repos.d]# service mysql start
Starting MySQL. SUCCESS!

13. Run mysql_upgrade

[root@upgradeserver ~]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
ben
information_schema
mysql
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
performance_schema
test
tracker
tracker.accounts                                   OK
tracker.fragment_lookup                            OK
upgrade_test
Phase 3/3: Running 'mysql_fix_privilege_tables'...
OK

14. Check Upgrade and Verify Databases

[root@upgradeserver yum.repos.d]# mysql --version
mysql  Ver 15.1 Distrib 10.0.12-MariaDB, for Linux (x86_64) using readline 5.1

[root@upgradeserver yum.repos.d]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 3
Server version: 10.0.12-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| upgrade_test       |
+--------------------+
5 rows in set (0.01 sec)

15. Configuration File(s)

You’ll notice MariaDB breaks the configuration files up.

[root@upgradeserver my.cnf.d]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[root@upgradeserver my.cnf.d]# ls /etc/my.cnf.d
mysql-clients.cnf  server.cnf  tokudb.cnf

Copy your configurations from the backed-up my.cnf file to the appropriate new .cnf files. Pay special attention to variables which have changed between versions.

Or better yet, use this opportunity to go through the variables and tune your configuration. https://mariadb.com/kb/en/mariadb/mariadb-documentation/optimization-and…