How to perform backup of a database in MairaDB/MySQL by using mysqldump

mysqldump is used to take logical backup of database/s in MairaDB and MySQL. It works great if the database size is small. If your database size is huge, consider taking hot backups.

Let's follow below steps to take the backup of TechBrothers Database.

1) Create the directory for backups

First of all create the directory in which you would like to save MairaDB Database/s backups. I am going to create mysqlbackup direct under root directory.

sudo mkdir /mysqlbackup

2) Provide Full Permission to mysql on backup directory

chown-R mysql:mysql /mysqlbackup

3) Create backupuser in MairaDB or Mysql

Create the user backupuser that we will use to perform backups of MariaDB databases. You can have any name you like for your backup user and provide strong password.

MariaDB [(none)]>CREATE USER 'backupuser'@'localhost' identified BY 'Test123';

4) Provide Permissions to backups user

You have to provide permissions to backup user so it can perform backups for databases. I used below statement to provide required permissions to backup user in MariaDB. You can further refine the permissions for backup user..

MariaDB [(none)]GRANT reload, lock tables, REPLICATION client, 
CREATE TABLE, 
             process, 
             super, 
             CREATE, 
INSERT, 
SELECT 
ON *.* TO 'backupuser'@'localhost';

5) Run mysqldum to perform backup of a database in MariaDB

Let's say I have database called TechBrothers in MairaDB and I would like to perform the backup. I can use below command. Change the below command as per your Database Name, backup user name  , password and backup directory.

mysqldump --user=backupuser --password=Test123 TechBrothers > /mysqlbackup/TechBrothers.sql 

Video Demo : How to perform Backup of a database in MariaDB or Mysql by using mysqldump