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