Here are 5 steps to migrate MySQL database to MariaDB database on Debian Linux server. The following steps have been tested in a real production environment. After completing the following 5 steps, no other changes are required, and production continues as usual.
1. Back up the existing database
Log in to Debian Server and enter the following command in the terminal to enter the MySQL shell.
mysql -u root -p
Enter the MySQL root password and we are in the MySQL shell. Enter the following command to see how many databases are in MySQL, don't forget to enter a semicolon.
show databases;
Here are the databases in my MySQL. You can see that there are 4 databases in total.
Enter the following command to exit the MySQL shell
exit;
Enter the following command in the terminal to back up all databases.
mysqldump --all-databases --user = root --password --master-data> backupdb.sql
The following error message appears:
mysqldump: Error: Binlogging on server not active
To fix this error, we need to make a few modifications to the my.cnf file. Edit the my.cnf file:
sudo vi /etc/mysql/my.cnf
Add the following line to the [mysqld] section
log-bin = mysql-bin
as follows:
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = / usr
datadir = / var / lib / mysql
tmpdir = / tmp
lc-messages-dir = / usr / share / mysql
skip-external-locking
log-bin = mysql-bin
#
Save the file and exit the text editor, then restart the mysql server.
sudo service mysql restart or sudo systemctl restart mysql
Run the mysqldump command again to back up all databases:
mysqldump --all-databases --user = root --password --master-data> backupdb.sql
This command will back up all databases and save them in the backupdb.sql file in the current working directory. If the following warning appears, you can ignore it.
-Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
2. Stop MySQL and remove all mysql packages
Although the configuration file my.cnf will not be deleted when MySQL is uninstalled, we will back up the configuration file my.cnf to a safe directory just in case. When MariaDB is installed, you will be asked if you want to use the original my.cnf file or the my.cnf configuration file provided by MariaDB.
sudo cp /etc/mysql/my.cnf my.cnf.bak
After the backup is completed, enter the following command to stop the mysql service.
sudo service mysql stop or sudo systemctl stop mysql
Then, remove all mysql packages.
sudo apt-get remove mysql-server mysql-client
3. Install MariaDB
First refer to Debian to install the MariaDB10.1 database server. The following address is the address of MariaDB 10.3.
Enter the following commands in order to add the official MariaDB software source.
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://nyc2.mirrors.digitalocean.com/mariadb/repo/10.3/debian/ jessie main'
After importing the public key and adding the official software source, enter the following command to install MariaDB:
sudo apt-get update
sudo apt-get install mariadb-server
During MariaDB installation, the installer will ask you whether you want to use the original MySQL my.cnf configuration file or the my.cnf file provided by MariaDB. We can use any of them.
4. Copy the contents of the my.cnf.bak backup file into the my.cnf configuration file
Enter the following command:
sudo cp my.cnf.bak /etc/mysql/my.cnf
5. Import the original database
Enter the following command to import the database backed up in the first step into MariaDB.
mysql -u root -p <backupdb.sql
After entering the MariaDB root password, we successfully imported the database. We can enter MariaDB shell to see if the import is successful. Enter the following command to enter the MariaDB shell:
mysql -u root -p
Enter the show database; command to list all databases:
MariaDB [(none)]> show databases;
You can see that all the original databases were imported successfully.
Alright, goodbye to MySQL. And say hello to MariaDB, there is no need to make any other changes now, production is still going on.
