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 Latest MariaDB 10.
- Secure the Database Server.
- Take MariaDB backups.
- Restoring MariaDB Backups.
- Setting up a Password for MariaDB.
- Updating MariaDB Password.
- Recovering MariaDB/MySQL Password.
- Updating the MariaDB from MariaDB 5.x to MariaDB 10.x.
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.
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.
