MySQL to Maria DB Migration

With Oracle’s fairly recent acquisition of MySQL, a lot of people are looking to move away from MySQL in fear of Oracle changing the licensing, which could force you to change database backends.

# service stop apache2

# service stop nginx

# service stop mysql

Next, make a simple mysqldump of your current databases. These commands dump every SQL database you have to a single file. Make sure you do this on a partition big enough to hold your data. Doing this in /tmp is a very bad idea.

# apt-get remove mysql-server-core-5.5 mysql-server-5.5 mysql-server mysql-common mysql-client-5.5 libmysqlclient18
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following package was automatically installed and is no longer required:
libaio1
Use ‘apt-get autoremove’ to remove it.
The following packages will be REMOVED:
libdbd-mysql-perl libmysqlclient18 mysql-client-5.5 mysql-common
mysql-server mysql-server-5.5 mysql-server-core-5.5 php5-mysql
0 upgraded, 0 newly installed, 8 to remove and 0 not upgraded.
After this operation, 94.8 MB disk space will be freed.
Do you want to continue [Y/n]? y

# cd /backups
# mysqldump -u root -p –all-databases > mysqlbackup.sql

Next, add the MariaDB repositories, and install it. Instructions for your distribution can be found at:

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

# apt-get install mariadb-server
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following extra packages will be installed:
libdbd-mysql-perl libmariadbclient18 libmysqlclient18 mariadb-client-5.5
mariadb-client-core-5.5 mariadb-common mariadb-server-5.5
mariadb-server-core-5.5 mysql-common
Suggested packages:
tinyca mariadb-test
The following NEW packages will be installed:
libdbd-mysql-perl libmariadbclient18 libmysqlclient18 mariadb-client-5.5
mariadb-client-core-5.5 mariadb-common mariadb-server mariadb-server-5.5
mariadb-server-core-5.5 mysql-common
0 upgraded, 10 newly installed, 0 to remove and 0 not upgraded.
Need to get 8,794 B/31.1 MB of archives.
After this operation, 108 MB of additional disk space will be used.
Do you want to continue [Y/n]? y

Some package systems start what they install automatically, but some don’t. If not, start up MariaDB now, and verify its sanity.

# mysql -u root -p -Be ‘show databases’
Enter password:
Database
information_schema
drupal
mysql
performance_schema
test

This next part won’t be pretty, but it shouldn’t be tricky. The configuration has changed considerably between MySQL and MariaDB, however it is extremely easy to work through.

You should be safe in simply copying over the performance tuning options you set in MySQL’s my.cnf, and reconfiguring the rest by hand. For small databases with less intricate setups, it is likely that you won’t need to do any thing more than copy the performance options that you’ve changed.

bind-address = 127.0.0.1

max_connections = 10
connect_timeout = 30
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 256
sort_buffer_size = 16M
bulk_insert_buffer_size = 16M
tmp_table_size = 64M
max_heap_table_size = 64M

Start up MariaDB again and verify sanity once again. Once you’ve done that and stopped MariaDB, start it up once more, as you normally would through your init system. Note that MariaDB shares the same name for compatibility.

# service restart mysql
Stopping MariaDB database server: mysqld.
Starting MariaDB database server: mysqld . . .
Checking for corrupt, not cleanly closed and upgrade needing tables..
# mysql -u root -p -Be ‘show databases’
Enter password:
Database
information_schema
drupal
mysql
performance_schema
test

Lastly, start up the applications that use a SQL database.

# service apache2 start
# service tomcat start

At this point you’re done! There is no conversion of your databases when switching to MariaDB, so if for whatever reason you don’t like it, you can freely switch back to MySQL.

# service mysql stop
# apt-get remove mariadb-server-5.5 mariadb-common mariadb-client-5.5 libmariadbclient18
# apt-get install mysql-server

You must be logged in to post a comment.