Complete guide to Install,Backup & Restore,Set password in MariaDB 10.

share on:
backup and restore mariadb database from command line

MariaDB is a truly Open Source Database management system created by MySQL’s original developers in 2009 after MySQL was acquired by ORACLE.

MariaDB is maintained and supported by MariaDB Foundation and community. It is currently in use by many organizations  for example Mozilla Corporation, Wikipedia, OpenSuse, Fedora, The Chakra Project to name a few.

For more info go to List of companies/organizations using MariaDB.

MariaDB is easy to install and configure is currently the default database Management system in Centos 7. MariaDB is recommended for its official support.

Current MariaDB version which can be used with CentOS 7 is 10.1.17 but the one provided in default CentOS repositories is 5.5.x.

Here’s a brief summary of what you will find in this article.


Install the Latest MariaDB.

In order to use the latest version of MariaDB we need to create a repo file in  /etc/yum.repos.d  directory and enable that repo. for MariaDB.

For CentOS 7 64-bit.

shell> vi /etc/yum.repos.d/MariaDB.repo
name = MariaDB
baseurl =


For  CentOS 6 64-bit.

shell> vi /etc/yum.repos.d/MariaDB.repo
name = MariaDB
baseurl =


As we have created a repo. for MariaDB , we can easily install Latest MariaDB.

Update the system and then install the MariaDB client and Server.

shell> yum update
shell> yum -y install MariaDB-server MariaDB-client


Enable the MariaDB to start at boot and then start the MariaDB service.

shell> systemctl enable mysql.service
shell> systemctl start mysql.service


Starting MaraiDB and Double-checking its status.

shell> systemctl status mysql.service



Securing the Database Server.

We need to secure the Database server by running this script  ( mysql_secure_installation )  in the shell.

shell> mysql_secure_installation
Enter current password for root (enter for none):
Set root password? [Y/n]   y

New password:
Re-enter new password:

Remove anonymous users? [Y/n]   y

**** If you don't want to access your database remotely then enter 'y' otherwise enter 'n' ****

Disallow root login remotely? [Y/n]   y

Remove test database and access to it? [Y/n]   y

Reload privilege tables now? [Y/n]   y


Stopping MariaDB .

To stop MariaDB service use,

shell> systemctl stop mysql.service



Backup and Restore MariaDB database.


Creating Backup of MariaDB Database.

It is very important in server administration to know how to take Database Backups and Restoring them. We can use mysqldump command to create dumps of databases managed by MySQL.

Dumps are nothing but a file containing all the necessary commands to recreate the database.

Also creating and restoring dumps is really fast and easy. You can either create a dump of all the databases one by one or all at once.


Create Backup of a single database.

Let’s say, For example, we have multiple databases from database_01 to database_30 and we want to take backup of database_11 only, then we need to apply this command.

To back up a single database, use the following command at the shell prompt:

shell> mysqldump -u root -p database_11 > database_11_bak.sql



Create Backup of multiple databases in MariaDB.

We will use –databases option to mysqldump for creating multiple databases backup.

shell> mysqldump -u root -p --databases database_01 database_02 database_03 ...  > multiple_database_backup.sql



Backing Up All Databases.

We can take backup of all our databases by using  –all–databases option to mysqldump.

shell> mysqldump -u root -p --all-databases > all_databases.sql


If you’re using InnoDB tables then you should use,

shell> mysqldump --all-databases --single-transaction --user=root --password > all_databases.sql


Options that we have used here:

--all-databases - this dumps all of the tables in all of the databases
--user - The MySQL user name you want to use for the backup
--password - The password for this user.
--single-transaction - for InnoDB tables


If you are using  Global Transaction Identifier’s (GTID’s) with InnoDB (GTID’s are not available with MyISAM), you should use the –set-gtid-purged=OFF option.

shell> mysqldump --all-databases --single-transaction --set-gtid-purged=OFF --user=root --password > all_databases.sql


Note :  mysqldump does not dump the INFORMATION_SCHEMA database by default. To dump INFORMATION_SCHEMA, name it explicitly on the command line and also use the –skip-lock-tables option.
mysqldump never dumps the performance_schema database.


Back Up MariaDB Database with Compression.

We can easily compress the .sql file by piping the output of mysqldump to gzip. For creating a compressed database backup of  database_01 we can use,

shell> mysqldump -u root -p database_01 | gzip > database_01.sql.gz



Restoring MySQL Databases.

Now that we know how to create the Backup of Databases, Let’s restore them.

Restore A Single Database.

To restore a MariaDB database backup, we need to create an empty database first.

shell> mysql -u root -p

create database database_01;



In addition to that, we can also use a one-liner like.

shell> mysql -uroot -p'root_password' -e "create database database_01"

( not secure but useful in scripts )


Then restore the backup to your MariaDB database

shell> mysql -u root -p database_01 < database_01.sql


sometimes permissions on various folders can cause problems in restoring.

To prevent that try copying  .sql  files to  /tmp  directory first and then apply restore like this.

shell> cp database_01.sql /tmp
shell> mysql -u root -p database_01 < /tmp/database_01.sql


Matching names ( database_01 ) is not compulsory  but advised.


Restore All Databases.

This simple command will restore all of your MariaDB databases with ease and your existing databases in MariaDB will be preserved.

shell> mysql -u root -p < all_databases.sql



Setting up MariaDB/MySQL Password.

Setting up Password in MySQL is very easy.


Setting up Password for the first time.

Setting up a password is very Important for the protection of your Databases.

You can set up a root password using mysqladmin command at the shell prompt as follows:

shell> mysqladmin -u root password your_password



Updating MySQL/MariaDB password.

Update your old password with a new password in MariaDB using this command.

shell> mysqladmin -u root -p'old_password' password new_password



Recovering MySQL/MariaDB password.

To learn more about how to recover password in MySQL 5.7, Read this article.

If you have lost your MySQL/MariaDB password then you can restore it easily. All you need to have is root access to your server. So let’s start recovering MySQL/MariaDB password.

First of all, Log in as root and stop the mysql process.

shell> sudo /etc/init.d/mysql stop



shell> systemctl stop mysqld.service


Now, let’s start  the mysql daemon with skip the grant tables & option.

backup and restore mariadb database from command line


Log in to the database using our new password.

shell> mysql -uroot -p


Enter the new password.


Updating the MariaDB from MariaDB 5.x to  MariaDB 10.x.

Steps included in the up gradation process are as follows:

  • Taking the full backup of all databases.
  • Take the backup of MySql conf ( my.cnf ) file and Directory (my.cnf.d)
  • Take a backup of MariaDB data directory.
  • Remove old MariaDB 5.x  packages via yum command.
  • clean Yum cache.
  • Install latest MariaDB 10.

Now Take a full backup of all databases before proceeding further.

Taking the backup of  my.cnf  and  my.cnf.d.

shell> cp -p  /etc/my.cnf /etc/my.cnf.$(date +%Y-%m-%d).orig


Taking backup of  /etc/my.cnf.d/  in  /root directory.

[root@debyum ~]# cd /etc/my.cnf.d/ 
[root@debyum my.cnf.d]# cd /etc/my.cnf.d/
[root@debyum my.cnf.d]# tar -czvf /root/my.cnf.d.$(date +%Y-%m-%d).tar.gz *
[root@debyum my.cnf.d]# ls -l /root/
-rw-r--r--. 1 root   root     7961036 Sep  7 14:59 latest.tar.gz
-rw-r--r--. 1 root   root        1385 Oct 19 07:48 my.cnf.d.2016-10-19.tar.gz


Now we can remove packages.


Remove old MariaDB 5.x  packages via yum command.

Now we will remove old MariaDB 5.x packages. For that, we need to find out the list of MariaDB packages already installed on our server.

[root@debyum ~]# yum list installed | grep -i mariadb
mariadb-client.x86_64                  5.x-x.el7.centos                            @mariadb        
mariadb-common.x86_64                  5.x-x.el7.centos                            @mariadb        
mariadb-server.x86_64                  5.x-x.el7.centos                            @mariadb        
mariadb-shared.x86_64                  5.x-x.el7.centos                            @mariadb


Before removing anything , first stop the mariadb.service.

shell> systemctl stop mariadb.service


Now remove these packages via yum command.

shell> yum remove mariadb mariadb-server mariadb-libs -y


Now run the following command to remove yum cached data. (Important)

shell> yum clean all


Now we can  install latest MariaDB 10 , or you can take another look at this post from start.

After installing latest MariaDB 10, Reinstall PHPs MySQL lib

shell> yum install php-mysql


Start MariaDB and update data files.

shell> service mysql start  
sehll> mysql_upgrade -u root -p


To Check the version of MySq use.

shell> mysql -V



To sum up, if you are looking for a modern and secure database then MariaDB  can be a good choice.

Again, I have tried to cover all the concepts with their examples. Still, if I have missed anything please update us through comment box. I will keep updating the same based on feedback’s received. Good day.



share on:


Hello there, My name is Rishi Guleria and I work as a Linux system administrator. I have created this blog to share what I have learned so far and to learn new things. Don't forget to leave the feedback. Have a great day. :)

Leave a Response

share on: