How to migrate MySQL to MariaDB on Linux?
- PHPzforward
- 2023-08-31 09:13:02969browse
This article will help you migrate your database from MySQL to MariaDB because MySQL to MariaDB binary compatibility is very simple during the migration process.
After Oracle acquired MySQL, the community drove the results of such a movement and developed a new database called MariaDB. MariaDB is open source and compatible with MySQL. Most Linux distributions (RH, CentOS, Fedora) have started using MariaDB support as a drop-in replacement for MySQL.
If we want to migrate the database from MySQL to MariaDB, then this article will be helpful.
Preparing the MySQL database and tables
We create a test MySQL database and overall tables for demonstration purposes. If you have a database and tables in your environment, you can skip this step.
Log in to MySQL from the terminal using the MySQL root user and root user password.
# mysql -uroot -p
Create a database and tables
mysql> Create database test1; mysql> use test1; mysql> create table tab1(name varchar(30), owner varchar(30), species varchar(20), sex char(1)); Query OK, 0 rows affected (0.02 sec)
In addition, we will add some records to test the data
mysql> insert into tab1 values('Chandra','Prakash','Kadarla','m'),('Srinvas','Kadarla','Chary','m'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Then quit from the MySQL.
Backup of MySQL database
We need to use the command – MySQL dump to back up the MySQL database
# mysqldump --all-databases --user=root --password --master-data > backupDB.sql
For security reasons, we will obtain some configuration files of MySQL elsewhere in the system
# cp /etc/mysql/my.cnf /opt/my.cnf.bak
Uninstall the MySQL package
us MySQL will be uninstalled using the following command –
# service mysqld stop # yum remove mysql-server mysql-client mysql-common
Install MariaDB package
MariaDB has been replaced by MySQL on all major Linux distribution official repositories. You can add the official repository even if you are using an older version of Linux.
Create the file in the yum repos directory
# sudo vi /etc/yum.repos.d/MariaDB.repo
Add the following content to the file and save it
[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/5.5/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
To install the Maria-DB server and client, run the following commands .
# yum install MariaDB-server MariaDB-client
Loaded plugins: fastestmirror, langpacks
base | 3.6 kB 00:00
extras | 3.4 kB 00:00
mariadb | 2.9 kB 00:00
updates | 3.4 kB 00:00
--> Restarting Dependency Resolution with new changes.
--> Running transaction check
---> Package MariaDB-shared.x86_64 0:5.5.49-1.el7.centos will be obsoleting
---> Package mariadb-libs.x86_64 1:5.5.35-3.el7 will be obsoleted
--> Finished Dependency Resolution
Dependencies Resolved
========================================================================================
Package Arch Version Repository Size
========================================================================================
Installing:
MariaDB-client x86_64 5.5.49-1.el7.centos mariadb 8.6 M
MariaDB-server x86_64 5.5.49-1.el7.centos mariadb 40 M
MariaDB-shared x86_64 5.5.49-1.el7.centos mariadb 1.0 M
replacing mariadb-libs.x86_64 1:5.5.35-3.el7
Installing for dependencies:
MariaDB-common x86_64 5.5.49-1.el7.centos mariadb 23 k
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-DBI x86_64 1.627-4.el7 base 802 k
perl-Data-Dumper x86_64 2.145-3.el7 base 47 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 3 Packages (+8 Dependent packages)
Total download size: 51 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7/mariadb/packages/MariaDB-5.5.49-centos7-x86_64-common.rpm: Header V4 DSA/SHA1 Signature, key ID 1bb943db: NOKEY
Public key for MariaDB-5.5.49-centos7-x86_64-common.rpm is not installed
(1/11): MariaDB-5.5.49-centos7-x86_64-common.rpm | 23 kB 00:00:01
(2/11): MariaDB-5.5.49-centos7-x86_64-client.rpm | 8.6 MB 00:00:12
warning: /var/cache/yum/x86_64/7/base/packages/perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm is not installed
(3/11): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm | 32 kB 00:00:00
(4/11): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm | 57 kB 00:00:00
(5/11): perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00:00
(6/11): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:00:00
(7/11): perl-Net-Daemon-0.48-5.el7.noarch.rpm | 51 kB 00:00:00
(8/11): perl-DBI-1.627-4.el7.x86_64.rpm | 802 kB 00:00:00
(9/11): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00:00
(10/11): MariaDB-5.5.49-centos7-x86_64-shared.rpm | 1.0 MB 00:00:01
Total download size: 51 M
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : MariaDB-common-5.5.49-1.el7.centos.x86_64 1/12
Installing : perl-Data-Dumper-2.145-3.el7.x86_64 2/12
Installing : MariaDB-client-5.5.49-1.el7.centos.x86_64 3/12
Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 4/12
Installing : perl-Net-Daemon-0.48-5.el7.noarch 5/12
Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 6/12
Installing : perl-IO-Compress-2.061-2.el7.noarch 7/12
Installing : perl-PlRPC-0.2020-14.el7.noarch 8/12
Installing : perl-DBI-1.627-4.el7.x86_64 9/12
Installed:
MariaDB-client.x86_64 0:5.5.49-1.el7.centos MariaDB-server.x86_64 0:5.5.49-1.el7.centos
MariaDB-shared.x86_64 0:5.5.49-1.el7.centos
Dependency Installed:
MariaDB-common.x86_64 0:5.5.49-1.el7.centos perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7
perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBI.x86_64 0:1.627-4.el7
perl-Data-Dumper.x86_64 0:2.145-3.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
Replaced:
mariadb-libs.x86_64 1:5.5.35-3.el7
Complete!After installing all the required packages, we need to set a password for the root user account. Root password recovery from MySQL backup configuration files.
# cp /opt/my.cnf /etc/mysql/
Now, we need to run the following command to restart the MariaDB service.
# service mariadb start
Import MySQL database
We must import the database we backed up from MySQL into MariaDB
# mysql -u root -p < backupDB.sql
Enter the password of the root user, and then the database will be imported to MariaDB
We need to check whether the database is restored correctly.
Please follow the following command –
# mysql -u root -p MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test1 | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> use test1; MariaDB [test01]> select * from tab1; select * from tab1; +---------+---------+---------+------+ | name | owner | species | sex | +---------+---------+---------+------+ | Chandra | Prakash | Kadarla | m | | Srinvas | Kadarla | Chary | m | +---------+---------+---------+------+ 2 rows in set (0.00 sec)
Now the migration from MySQL to MariaDB has been successfully completed.
After we run the command and follow the tutorial, we can migrate the database from MySQL to MariaDB in simple steps, where MariaDB has a lot of new features compared to MySQL. In the tutorial above I used a simple scenario to test the migration configuration.
The above is the detailed content of How to migrate MySQL to MariaDB on Linux?. For more information, please follow other related articles on the PHP Chinese website!