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 🌲
Top comments (0)