loading...

MariaDB: Backing Up/Restoring Databases On The Command Line

nabbisen profile image Heddi Nabbisen 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

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

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';

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

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 🌲

Posted on by:

nabbisen profile

Heddi Nabbisen

@nabbisen

An ICT designer/developer and a security monk. "With a cool brain and a warm heart", I am challenging unsolved problems in our society. I use OpenBSD/Rust/etc.

Discussion

pic
Editor guide