How to Change MySQL to MariaDB in Ubuntu
To switch from MySQL to MariaDB on Ubuntu, update your package list, install the MariaDB server, start the MariaDB service, and secure the installation. This process ensures a smooth transition while maintaining database integrity.
Steps to Migrate Data From MySQL to MariaDB
- Step 1: Create MySQL Database Backup
- Step 2: Uninstall MySQL Database Packages
- Step 3: Install MariaDB Database Packages
- Step 4: Load MySQL Backup File to MariaDB
Step 1: Create MySQL Database Backup
- Check for incompatibilities between MySQL and MariaDB.
- The existing Databases of MySQL need to be backed up to be transferred from MySQL to MariaDB.
- The command mysqldump can export all the existing databases in the form of files.
- Binary Logging is required for this command to work, and this can be enabled in the MySQL configurations by modifying my.cnf file and adding the following option under the [mysqld] section.
log-bin=mysql-bin
- Restart the MySQL by using the following command:
$ sudo service mysql restart
- After that enter the command to use mysqldump.
$ mysqldump --all-databases --user=root --password --master-data > backupdb.sql
- A backup of my.cnf can be created to be on the safer side, but this step is optional. To create the backup use the following command:
$ sudo cp /etc/mysql/my.cnf /opt/my.cnf.bak
Step 2: Uninstall MySQL Database Packages
- Stop the MySQL service by using the following command
$ sudo service mysql stop
or
$ sudo systemctl stop mysql
or
$ sudo /etc/init.d/mysql stop
- Once the services have stopped, remove MySQL packages and configurations. You may leverage any of the following commands based on your operating system:
- On RPM-based systems, (e.g., CentOS, Fedora, or RHEL):
$ sudo yum remove mysql* mysql-server mysql-devel mysql-libs
$ sudo rm -rf /var/lib/mysql
- On Debian based systems (e.g., Debian, Ubuntu, or Mint):
$ sudo apt-get remove mysql-server mysql-client mysql-common
$ sudo apt-get autoremove
$ sudo apt-get autoclean
$ sudo deluser mysql
$ sudo rm -rf /var/lib/mysql
Step 3: Install MariaDB Database Packages
The latest versions of CentOS and Ubuntu have the MariaDB package pre-installed in repositories. For older versions, you can visit the MariaDB website and download it based on the operating system version.
For a better understanding of this example, we will install MariaDB on Ubuntu and CentOS.
Ubuntu 14.04
$ sudo apt-get install software-properties-common
$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
$ sudo add-apt-repository 'deb http://mirror.mephi.ru/mariadb/repo/5.5/ubuntu trusty main'
$ sudo apt-get update
$ sudo apt-get install mariadb-server
CentOS 7
Create a custom yum repository file for MariaDB as follows.
$ sudo vi /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
$ sudo yum install MariaDB-server MariaDB-client
After all necessary packages have been installed, you may be asked to type a new password for the root user account. After configuring the root password, don’t forget to recover my.cnf backup file.
$ sudo cp /opt/my.cnf /etc/mysql/
Now, start the MariaDB service as follows:
$ sudo service mariadb start
or
$ sudo systemctl start mariadb
or
$ sudo /etc/init.d/mariadb start
Step 4: Load MySQL Backup File to MariaDB
Now import the MySQL backup file into MariaDB using the following command:
$ mysql -u root -p < backupdb.sql
Enter the password and the import process for MySQL to MariaDB migration will start. When the import of files from MySQL to MariaDB is complete the cursor will return to the command prompt.
To verify the success of import from this MySQL to MariaDB converter process, run the following commands:
$ mysql -u root -p
MariaDB [(none)]> show databases;
MariaDB [(none)]> use test01;
MariaDB [test01]> select * from pet;
How to Migrate MySQL to MariaDB in Ubuntu?
Migrating data from MySQL to MariaDB is a straightforward process, given the close compatibility between the two database systems. MariaDB is a popular choice for many organizations due to its enhanced features and improved performance over MySQL. Here’s a detailed guide on how to migrate your data from MySQL to MariaDB in four simple steps.