MySQL and MariaDB allow the mysqldump service to make easier the process of creating a backup of database or system of databases.
Using mysqldump performs a proper backup. You can only apply this utility if your database process is accessible and working.
You can preferably form a physical backup if the database is inconvenient for any reason, and this can give a copy of the filesystem structure that holds your data.
The guidance in this article operates to both MySQL and MariaDB.
Before starting database backup, listen to the following points:
1) You must have a running MySQL or MariaDB installation and a database user to handle the backup.
2) You have root access to the system or a user account with sudo rights.
Back up a Database
The mysqldump command’s general syntax is:
mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sql
Before starting backup process, mysqldump prompts for a password.
Depending on the size of the database, it could take a while to finish.
In the directory, the database backup will be created where the command is run.
-$(date +%F) brings a timestamp to the filename.
If you want:
A backup of an entire Database Management System (DBMS):
mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p
Back up of a particular database. Replace db1 with the name of the database you need to back up:
mysqldump -u username -p db1 --single-transaction --quick --lock-tables=false > db1-backup-$(date +%F).sql
Back up of a single table from any database. table1 is exported from the database db1 in below example:
mysqldump -u username -p --single-transaction --quick --lock-tables=false db1 table1 > db1-table1-$(date +%F).sql
Breakdown of the mysqldump command options used above as follows:
--single-transaction: Allot a BEGIN SQL statement before dropping data from the server.
--quick: Drive dumping tables row by row. This gives added safety for systems with limited RAM and/or massive databases where saving tables in memory could enhance problems.
--lock-tables=false: Do not lock tables for the backup session.
How to schedule automatic backups with cron
For enabling regular backups of database , have to add entries to /etc/crontab
1) Generate a file to keep the login credentials of the MySQL root user which will be doing the backup.
Note that the system user whose home directory in which, this file is saved can be unrelated to any MySQL users.
File: /home/example_user/.mylogin.cnf
1 [client]
2 user = root
3 password = MySQL root user's password
2) Control permissions of the credentials file:
chmod 600 /home/example_user/.mylogin.cnf
3) Then generate a cron job file. Following is an example cron job to back up the whole database management system every day at 1am:
File: /etc/cron.daily/mysqldump
1 0 1 * * * /usr/bin/mysqldump --defaults-extra-file=/home/example_user/.my.cnf -u root --single-transaction --quick --lock-tables=false --all-databases > full-backup-$(date +\%F).sql
How to restore a Backup
The restoration command’s general syntax is:
mysql -u [username] -p [databaseName] < [filename].sql
Restore a whole DBMS backup. You will be advised for the MySQL root user’s password:
That can overwrite all prevailing data in the MySQL database system
mysql -u root -p < full-backup.sql
Rebuild a single database. For this, you have a blank or old destination to import the data into and, the MySQL user who is working the command must have write access to that database:
mysql -u [username] -p db1 < db1-backup.sql
For the restoration of a single table, must have a target database to collect the data: mysql -u dbadmin -p db1 < db1-table1.sql
