In a database management system, backup is an important part of dealing with databases.
In MySQL/ MariaDB database, the mysqldump command is used to take the backup of the database. It’s designed specifically for backup purposes. Mysqldump can be used to back up a single database, multiple databases, and all databases.
Backup Procedures
Backing up a single database
We can run the following command in the shell prompt to take the backup for a single database.
# mysqldump -u root -p database_name > database_name.sql
NOTE: Please replace the database_name with your real database name. Depending on the database size, the process can take some time.
Backing up all databases
We can run the following command in the shell prompt to take the backup for all databases.
For that, need to add --all-databases option with mysqldump.
# mysqldump -u root -p --all-databases > all-databases.sql
Restore Procedures
Restoring a MySQL backup
We can run the following command in the shell prompt to restore a MySQL backup.
For that, need to find the .sql backup file and run the following command.
# mysql -u root -p database_name < file.sql
NOTE: Please replace the database_name with your real database name and file with .sql backup file name.
Restoring all MySQL backup
We can run the following command in the shell prompt to restore all MySQL backups.
# mysql -u root -p < all-databases.sql
NOTE: Please replace the all-databases.sql file with your backup file name.
Restoring a single database from a dump of all the databases
For that, need to add --one-database option with MySQL query.
# mysql -u root -p --one-database database_name < all_databases.sql
NOTE: Please replace the database_name with your real database name and all_databases.sql with the MySQL backup file name.
Related LayerStack Product
Related Tutorials
