Restore

---

Create Backup of a single database.

Let’s say, For example, we have multiple databases from database_01 to database_30 and we want to take backup of database_11 only, then we need to apply this command.

To back up a single database, use the following command at the shell prompt:

shell> mysqldump -u root -p database_11 > database_11_bak.sql

Create Backup of multiple databases in MariaDB.

We will use –databases option to mysqldump for creating multiple databases backup.

shell> mysqldump -u root -p --databases database_01 database_02 database_03 ...  > multiple_database_backup.sql

Backing Up All Databases.

We can take backup of all our databases by using  –all–databases option to mysqldump.

shell> mysqldump -u root -p --all-databases > all_databases.sql

If you’re using InnoDB tables then you should use,

shell> mysqldump --all-databases --single-transaction --user=root --password > all_databases.sql

Back Up MariaDB Database with Compression.

We can easily compress the .sql file by piping the output of mysqldump to gzip. For creating a compressed database backup of  database_01 we can use,

shell> mysqldump -u root -p database_01 | gzip > database_01.sql.gz

Restoring MySQL Databases.

Now that we know how to create the Backup of Databases, Let’s restore them.

Restore A Single Database.

To restore a MariaDB database backup, we need to create an empty database first.

shell> mysql -u root -p  create database database_01;  exit;

In addition to that, we can also use a one-liner like.

shell> mysql -uroot -p'root_password' -e "create database database_01"

( not secure but useful in scripts )

Then restore the backup to your MariaDB database

shell> mysql -u root -p database_01 < database_01.sql

sometimes permissions on various folders can cause problems in restoring.

To prevent that try copying  .sql  files to  /tmp  directory first and then apply restore like this.

shell> cp database_01.sql /tmp shell> mysql -u root -p database_01 < /tmp/database_01.sql

Matching names ( database_01 ) is not compulsory  but advised.

Restore All Databases.

This simple command will restore all of your MariaDB databases with ease and your existing databases in MariaDB will be preserved.

shell> mysql -u root -p < all_databases.sql

--