Backup and restore mariadb with mysqldump

Backup and restore mariadb using mysqldump

 

1/ Backup​​ single Database

 

shell>​​ mysqldump db_name > backup-file.sql

2/ Restoring or loading the database

 

shell> mysql db_name < backup-file.sql

3/​​ Backup all database

 

mysqldump --all-databases > /opt/alldb.sql

4/ Restore all Database

mysql -u root -p

source /opt/alldb.sql;

Quá trình diễn ra:

.

.

.

Query OK, 0 rows affected (0.056 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 1 row affected (0.000 sec)

 

Database changed

Query OK, 1 row affected (0.000 sec)

 

Database changed

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

Query OK, 0 rows affected (0.001 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.001 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Database changed

Database changed

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

Database changed

Database changed

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

 

Query OK, 0 rows affected (0.000 sec)

# kiểm tra lại

 

MariaDB [test005]> show databases;

+--------------------+

| Database  ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ |

+--------------------+

| classicmodels  ​​ ​​ ​​ ​​​​ |

| employees  ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ |

| information_schema |

| mysql  ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ |

| performance_schema |

| test  ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ |

| test001  ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ |

| test002  ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ |

| test003  ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ |

| test005  ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ |

+--------------------+

10 rows in set (0.000 sec)

Lưu ý:​​ ngoài cách backup trên bạn cũng có thể sử dụng​​ MariaDB​​ Replicate để backup.​​ Nếu note chính bị lỗi thì vẫn còn node slave dự phòng. Sử dụng MariaDB​​ 

 

 

Link tham khảo:

https://mariadb.com/kb/en/library/backup-and-restore-overview/

 

https://mariadb.com/kb/en/replication-as-a-backup-solution/

 

 

chúc các bạn thành công.

SaKuRai

Xin chào, Mình là Sakurai. Blog này là nơi để note lại và chia sẻ những kiến thức, kinh nghiệm mà mình và anh em trong Team. Cảm ơn các bạn đã quan tâm theo dõi!

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *