How To preform MySql database backup on CentOS with MariaDB

We are going to use mysqldump to create a backup dump of the database, to backup all the databases, create backup folder :

mkdir /var/lib/mysql/backup

Now let check the databases size :

mysql -u root -p
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema ;

In case of a large database this can take a long time, you might want to install pv progress bar to get some information during, the data will be piped through the progress bar, for example = | pv -W

yum install pv

Now Lets create some Database dumps, the basic is to dump all the database :

mysqldump -u root -p --all-databases | pv -W > /var/lib/mysql/backup/all-database.sql

Note that the --all-databases option is applicable to backup only. The backup file itself will contain all the relevant CREATE DATABASE quux; commands for the restore.

Make sure the dump is created

ls -1 -s /var/lib/mysql/backup/

To create zipped backup use :

mysqldump --single-transaction -u root -p 'Database name' | gzip | pv -W > /var/lib/mysql/backup/'Database Name'.sql.gz

To restore all the databases :

mysql -u root -p --all-databases < /var/lib/mysql/backup/all-database.sql

To restore specific database

mysql -u root -p 'Database name' < /var/lib/mysql/backup/'Database name'.sql

To restore from zipped backup/dump

zcat /var/lib/mysql/backup/'Database Name'.sql.gz | mysql -u root -p 'Database Name'