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
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

 

For  CentOS 6 64-bit.

shell> vi /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

 

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;

exit;

 

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

 

OR

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 *
client.cnf
enable_encryption.preset
mysql-clients.cnf
server.cnf
tokudb.cnf
[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:
engy

engy

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. Good Day. :)

Leave a Response

share on: