How to Migrate Database from MySQL to MariaDB

Posted on

This article is solely written to achieve the purpose specified in the title of this article. There may be similar occurrence happens with those which is going to be written in this article. The case is specifically how to migrate database of an application powered by Moodle 3.2.1. As we may already know, Moodle is a framework for a web-based application which is claimed and stated in its official website in this link, it is a Learning Platform or course management system (CMS) – a free Open Source software package designed to help educators create effective online courses based on sound pedagogical principles. 

So, furthermore the actual migration process starts with the following step :

1. Backup the Moodle database which exist in MySQL Database Server. It is shown as follows :

[root@hostname www]# mysqldump -uroot -p moodle > /home/user/moodle-dump-file.sql
Enter password: 
[root@hostname www]#

2. Uninstall MySQL Database Server.

[root@hostname ~]# yum erase mysql*
Loaded plugins: fastestmirror, replace
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.18-1.el7 will be erased
---> Package mysql-community-common.x86_64 0:5.7.18-1.el7 will be erased
---> Package mysql-community-libs.x86_64 0:5.7.18-1.el7 will be erased
---> Package mysql-community-server.x86_64 0:5.7.18-1.el7 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================================
 Package                                             Arch                                Version                                   Repository                              Size
================================================================================================================================================================================
Removing:
 mysql-community-client                              x86_64                              5.7.18-1.el7                              installed                              106 M
 mysql-community-common                              x86_64                              5.7.18-1.el7                              installed                              2.5 M
 mysql-community-libs                                x86_64                              5.7.18-1.el7                              installed                              9.4 M
 mysql-community-server                              x86_64                              5.7.18-1.el7                              installed                              730 M

Transaction Summary
================================================================================================================================================================================
Remove  4 Packages

Installed size: 848 M
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Erasing    : mysql-community-server-5.7.18-1.el7.x86_64                                                                                                                   1/4 
  Erasing    : mysql-community-client-5.7.18-1.el7.x86_64                                                                                                                   2/4 
  Erasing    : mysql-community-libs-5.7.18-1.el7.x86_64                                                                                                                     3/4 
  Erasing    : mysql-community-common-5.7.18-1.el7.x86_64                                                                                                                   4/4 
  Verifying  : mysql-community-server-5.7.18-1.el7.x86_64                                                                                                                   1/4 
  Verifying  : mysql-community-common-5.7.18-1.el7.x86_64                                                                                                                   2/4 
  Verifying  : mysql-community-libs-5.7.18-1.el7.x86_64                                                                                                                     3/4 
  Verifying  : mysql-community-client-5.7.18-1.el7.x86_64                                                                                                                   4/4 

Removed:
  mysql-community-client.x86_64 0:5.7.18-1.el7               mysql-community-common.x86_64 0:5.7.18-1.el7               mysql-community-libs.x86_64 0:5.7.18-1.el7              
  mysql-community-server.x86_64 0:5.7.18-1.el7              

Complete!
[root@hostname ~]#

3. Install MariaDB.

[root@hostname ~]# yum -y install mariadb
Loaded plugins: fastestmirror, replace
Loading mirror speeds from cached hostfile
 * base: mirror.unej.ac.id
 * epel: mirror.smartmedia.net.id
 * extras: mirror.unej.ac.id
 * updates: mirror.vodien.com
Resolving Dependencies
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.56-2.el7 will be installed
--> Processing Dependency: mariadb-libs(x86-64) = 1:5.5.56-2.el7 for package: 1:mariadb-5.5.56-2.el7.x86_64
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.56-2.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================================
 Package                                      Arch                                   Version                                         Repository                            Size
================================================================================================================================================================================
Installing:
 mariadb                                      x86_64                                 1:5.5.56-2.el7                                  base                                 8.7 M
Installing for dependencies:
 mariadb-libs                                 x86_64                                 1:5.5.56-2.el7                                  base                                 757 k

Transaction Summary
================================================================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 9.5 M
Installed size: 53 M
Downloading packages:
(1/2): mariadb-libs-5.5.56-2.el7.x86_64.rpm                                                                                                              | 757 kB  00:00:09     
(2/2): mariadb-5.5.56-2.el7.x86_64.rpm                                                                                                                   | 8.7 MB  00:01:20     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                           121 kB/s | 9.5 MB  00:01:20     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : 1:mariadb-libs-5.5.56-2.el7.x86_64                                                                                                                           1/2 
  Installing : 1:mariadb-5.5.56-2.el7.x86_64                                                                                                                                2/2 
  Verifying  : 1:mariadb-libs-5.5.56-2.el7.x86_64                                                                                                                           1/2 
  Verifying  : 1:mariadb-5.5.56-2.el7.x86_64                                                                                                                                2/2 

Installed:
  mariadb.x86_64 1:5.5.56-2.el7                                                                                                                                                 

Dependency Installed:
  mariadb-libs.x86_64 1:5.5.56-2.el7                                                                                                                                            

Complete!
[root@hostname ~]# yum -y install mariadb-server
Loaded plugins: fastestmirror, replace
Loading mirror speeds from cached hostfile
 * base: mirror.xxx.xxx.xxx
 * epel: mirror.xxx.xxx.xxx
 * extras: mirror.xxx.xxx.xxx
 * updates: mirror.xxx.xxx.xxx
Resolving Dependencies
--> Running transaction check
---> Package mariadb-server.x86_64 1:5.5.56-2.el7 will be installed
--> Processing Dependency: perl-DBI for package: 1:mariadb-server-5.5.56-2.el7.x86_64
--> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.56-2.el7.x86_64
--> Processing Dependency: perl(DBI) for package: 1:mariadb-server-5.5.56-2.el7.x86_64
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed
---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed
--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64
--> Running transaction check
---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed
--> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Running transaction check
---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed
--> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
--> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed
--> Running transaction check
---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed
---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================================
 Package                                              Arch                                Version                                       Repository                         Size
================================================================================================================================================================================
Installing:
 mariadb-server                                       x86_64                              1:5.5.56-2.el7                                base                               11 M
Installing for dependencies:
 perl-Compress-Raw-Bzip2                              x86_64                              2.061-3.el7                                   base                               32 k
 perl-Compress-Raw-Zlib                               x86_64                              1:2.061-4.el7                                 base                               57 k
 perl-DBD-MySQL                                       x86_64                              4.023-5.el7                                   base                              140 k
 perl-DBI                                             x86_64                              1.627-4.el7                                   base                              802 k
 perl-IO-Compress                                     noarch                              2.061-2.el7                                   base                              260 k
 perl-Net-Daemon                                      noarch                              0.48-5.el7                                    base                               51 k
 perl-PlRPC                                           noarch                              0.2020-14.el7                                 base                               36 k

Transaction Summary
================================================================================================================================================================================
Install  1 Package (+7 Dependent packages)

Total download size: 13 M
Installed size: 62 M
Downloading packages:
(1/8): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm                                                                                                    |  32 kB  00:00:00     
(2/8): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm                                                                                                     |  57 kB  00:00:00     
(3/8): perl-DBD-MySQL-4.023-5.el7.x86_64.rpm                                                                                                             | 140 kB  00:00:01     
(4/8): perl-DBI-1.627-4.el7.x86_64.rpm                                                                                                                   | 802 kB  00:00:13     
(5/8): perl-IO-Compress-2.061-2.el7.noarch.rpm                                                                                                           | 260 kB  00:00:02     
(6/8): perl-Net-Daemon-0.48-5.el7.noarch.rpm                                                                                                             |  51 kB  00:00:01     
(7/8): perl-PlRPC-0.2020-14.el7.noarch.rpm                                                                                                               |  36 kB  00:00:00     
(8/8): mariadb-server-5.5.56-2.el7.x86_64.rpm                                                                                                            |  11 MB  00:01:49     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                           118 kB/s |  13 MB  00:01:49     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                                                                                                                   1/8 
  Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                                                                                                                  2/8 
  Installing : perl-IO-Compress-2.061-2.el7.noarch                                                                                                                          3/8 
  Installing : perl-Net-Daemon-0.48-5.el7.noarch                                                                                                                            4/8 
  Installing : perl-PlRPC-0.2020-14.el7.noarch                                                                                                                              5/8 
  Installing : perl-DBI-1.627-4.el7.x86_64                                                                                                                                  6/8 
  Installing : perl-DBD-MySQL-4.023-5.el7.x86_64                                                                                                                            7/8 
  Installing : 1:mariadb-server-5.5.56-2.el7.x86_64                                                                                                                         8/8 
  Verifying  : perl-DBI-1.627-4.el7.x86_64                                                                                                                                  1/8 
  Verifying  : perl-Net-Daemon-0.48-5.el7.noarch                                                                                                                            2/8 
  Verifying  : perl-IO-Compress-2.061-2.el7.noarch                                                                                                                          3/8 
  Verifying  : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                                                                                                                  4/8 
  Verifying  : perl-PlRPC-0.2020-14.el7.noarch                                                                                                                              5/8 
  Verifying  : 1:mariadb-server-5.5.56-2.el7.x86_64                                                                                                                         6/8 
  Verifying  : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                                                                                                                   7/8 
  Verifying  : perl-DBD-MySQL-4.023-5.el7.x86_64                                                                                                                            8/8 

Installed:
  mariadb-server.x86_64 1:5.5.56-2.el7                                                                                                                                          

Dependency Installed:
  perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7      perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7      perl-DBD-MySQL.x86_64 0:4.023-5.el7      perl-DBI.x86_64 0:1.627-4.el7     
  perl-IO-Compress.noarch 0:2.061-2.el7             perl-Net-Daemon.noarch 0:0.48-5.el7              perl-PlRPC.noarch 0:0.2020-14.el7       

Complete!
[root@hostname ~]#

4. Enable MariaDB service.

[root@hostname ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@hostname ~]#

5. Remove the content of the old directory of MySQL Database Server.

This is an important step, because in the previous step, uninstalling MySQL Database Server sometime cannot remove the old MySQL content’s directory. So, whenever the old or the previous content is not erased. Just delete all of it manually. Because if it doesn’t properly erased, it will generate the following error :

[root@hostname ~]# systemctl start mariadb
Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
[root@hostname ~]#

To check it further, the error which is caused when the service of MariaDB cannot be started properly, just check the status of the service as shown below :

[root@hostname ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Sab 2018-04-28 00:25:06 WIB; 7s ago
  Process: 16805 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=1/FAILURE)
  Process: 16804 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
  Process: 16773 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 16804 (code=exited, status=0/SUCCESS)

Apr 28 00:25:05 hostname systemd[1]: Starting MariaDB database server...
Apr 28 00:25:05 hostname mariadb-prepare-db-dir[16773]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
Apr 28 00:25:05 hostname mari5. adb-prepare-db-dir[16773]: If this is not the case, make sure the /var/lib/mysql is empty before running mariadb-prepare-db-dir.
Apr 28 00:25:05 hostname mysqld_safe[16804]: 180428 00:25:05 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Apr 28 00:25:05 hostname mysqld_safe[16804]: 180428 00:25:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Apr 28 00:25:06 hostname systemd[1]: mariadb.service: control process exited, code=exited status=1
Apr 28 00:25:06 hostname systemd[1]: Failed to start MariaDB database server.
Apr 28 00:25:06 hostname systemd[1]: Unit mariadb.service entered failed state.
Apr 28 00:25:06 hostname systemd[1]: mariadb.service failed.
[root@hostname ~]#

So, just remove the old content with the following command execution. The removal process itself is not actually to remove it permanently, but just in case something wrong happened, it can be restored back. It is being moved to another location which in the following context, it is placed in the /root/backup-mysql as shown below :

[root@hostname lib]# cd /root/
[root@hostname ~]# mkdir backup-mysql
[root@hostname ~]# cd /var/lib/mysql/
[root@hostname mysql]# ls
aria_log.00000001  auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  mysql               private_key.pem  server-cert.pem  sys
aria_log_control   ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  moodle       performance_schema  public_key.pem   server-key.pem
[root@hostname mysql]# mv -vf * /root/backup-mysql/
‘aria_log.00000001’ -> ‘/root/backup-mysql/aria_log.00000001’
removed ‘aria_log.00000001’
‘aria_log_control’ -> ‘/root/backup-mysql/aria_log_control’
removed ‘aria_log_control’
‘auto.cnf’ -> ‘/root/backup-mysql/auto.cnf’
removed ‘auto.cnf’
‘ca-key.pem’ -> ‘/root/backup-mysql/ca-key.pem’
removed ‘ca-key.pem’
‘ca.pem’ -> ‘/root/backup-mysql/ca.pem’
removed ‘ca.pem’
‘client-cert.pem’ -> ‘/root/backup-mysql/client-cert.pem’
removed ‘client-cert.pem’
‘client-key.pem’ -> ‘/root/backup-mysql/client-key.pem’
removed ‘client-key.pem’
‘ib_buffer_pool’ -> ‘/root/backup-mysql/ib_buffer_pool’
...
...
...
removed ‘sys/x@0024host_summary_by_stages.frm’
removed ‘sys/wait_classes_global_by_avg_latency.frm’
removed ‘sys/x@0024wait_classes_global_by_avg_latency.frm’
removed ‘sys/wait_classes_global_by_latency.frm’
removed ‘sys/x@0024wait_classes_global_by_latency.frm’
removed ‘sys/x@0024waits_by_user_by_latency.frm’
removed ‘sys/x@0024waits_by_host_by_latency.frm’
removed ‘sys/x@0024waits_global_by_latency.frm’
removed ‘sys/session_ssl_status.frm’
removed directory: ‘sys’
[root@hostname mysql]#

6. Restart the service of MariaDB.

After removing all the content located in /var/lib/mysql, try to restart MariaDB content. Below is the actual output shown in this step :

[root@hostname mysql]# ls
[root@hostname mysql]#

As shown in the above output generated by ‘ls’ command, the directory is empty. After that, try to start the service of MariaDB :

[root@hostname mysql]# systemctl start mariadb
[root@hostname mysql]# ls
aria_log.00000001  aria_log_control  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  performance_schema  test
[root@hostname mysql]# mysql -uroot 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> create database moodle;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> quit

As we can see above, there are another files generated after the service of MariaDB has successfully started.

7. Restore the dump file retrieved from MySQL Database Server into MariaDB.

The next process is restoring the dump file which is in the process, it will lead to the error message specified in the article titled ‘How to Solve MariaDB Error Message ERROR 1071 (42000) : Specified key was too long; max key length is 767 bytes’ in this link. After solving the error message, it will also lead to the error message specified in the article titled ‘How to Solve MariaDB Error Message ERROR 1709 (HY000) at line 1283: Index column size too large. The maximum column size is 767 bytes.’ in this link. Basically, the error will be solved after the MariaDB configuration is edited and it is added with the following lines of configuration in [mysqld] section. The complete line of configuration added is specified as follows :

[mysqld] 
innodb_large_prefix=TRUE 
innodb_file_per_table=TRUE 
innodb_large_prefix=on 
innodb_file_format=Barracuda

Leave a Reply