It is necessary to make a backup of the database before reinstalling the server or just in case something unexpected happens. We can actually do backups through Phpmyadmin by exporting all databases. But what if we haven’t installed Phpmyadmin on the Server?

How to Backup All Databases

1. Backup Schema and Data

mysqldump -u username -p --all-databases > backup.sql
or
mysqldump -u username -p -A > backup.sql

2. Backup only the Schemas

mysqldump -u username -p --all-databases --no-data > backup_schema.sql
or
mysqldump -u username -p -A -d > backup_schema.sql

3. Backup by Remote Server

mysqldump -u root -h 128.168.1.1 -p -A > backup.sql

4. Backup Routines

mysqldump -u username -p db_name --routines > backup_routinetrigger.sql
or
mysqldump -u username -p db_name -R > backup_routinetrigger.sql

5. Backup Routines and Triggers

mysqldump -u username -p db_name --routines --triggers > backup_routinetrigger.sql

6. Backup Specific Database

mysqldump -u username -p --database db_name > backup_db_name.sql
or
mysqldump -u username -p db_name > backup_db_name.sql

7. Backup Specific Table in Specific Database

mysqldump -u username -p --database db_name table_name > backup_db_name_table_name.sql
or
mysqldump -u username -p db_name table_name > backup_db_name_table_name.sql

How To Restore Database

8. Restore all Databases

mysql -u username -p < backup.sql

9. Restore one database

mysql -u username -p db_name < backup_db_name.sql

10. Manual command line for mysql

man mysqldump
man mysql

11. References

https://dev.mysql.com/doc/refman/8.0/en/mysql.html

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

Post Views: 1,124