mysqldump service to make easier the process of creating a backup of database or system of databases in MySQL and MariaDB
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.
