MariaDB: Backing Up/Restoring Databases On The Command Line

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

• Backup Tools - mysqldump

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

• Restoring Data from Dump Files

✿ ✿ ✿

Happy serving 🌲