DEV Community

loading...

MariaDB: Backing Up/Restoring Databases On The Command Line

Heddi Nabbisen
An ICT designer/developer and a security monk with "a cool brain and a warm heart". OpenBSD/Rust etc. Interested in social issues and relationships. Founder of Scqr Inc.
Updated on ・2 min read

Summary

Backing up/restoring databases are good fellows for both development and management of services.
This post is about how to back up/restore MariaDB databases on the command line instantly, based on MariaDB Corporation's "Backup and Restore Overview".

Environment

  • MariaDB 10.0
✿ ✿ ✿

Backing Up

Just mysqldump ... > file path:

$ mysqldump -u %user% -p %from-database% > ./database-backup.sql
Enter fullscreen mode Exit fullscreen mode

You'll be asked the password.

If you want to use a one-liner, to exchange -p to -p%password% will be the way. (Please take notice of the command history which may include the password.)

References

Restoring

Just mysql ... < file path:

$ mysql -u %user% -p %to-database% < ./database-backup.sql
Enter fullscreen mode Exit fullscreen mode

You'll be asked the password.

If you want to use a one-liner, to exchange -p to -p%password% will be the way. (Please take notice of the command history which may include the password.)

Partial Restoring

It's possible to restore some tables or some records by creating a temporary user.


This is an example of restoring only table1 in db1 by using the temporary user named admin_restore_temp.
First, execute GRANT for admin_restore_temp to have all privileges on only table1:

GRANT SELECT
ON db1.* TO 'admin_restore_temp'@'localhost' 
IDENTIFIED BY 'its_pwd';

GRANT ALL ON db1.table1
TO 'admin_restore_temp'@'localhost';
Enter fullscreen mode Exit fullscreen mode

Then, restore with the backup file, /data/backup/db1.sql, and the --force option like this:

$ mysql -u admin_restore_temp -p --force < /data/backup/db1.sql
Enter fullscreen mode Exit fullscreen mode

After MariaDB generates errors, only table1 will be restored.


There are several different ways.
For example, we can restore partial records in some tables by using INSERT statements after creating a temporary database instead of using the mysql ... < file path command.

References

✿ ✿ ✿

Happy serving 🌲

Discussion (0)