If you are running your own Linux-driven VPS, you are probably using MySQL for your databases. This article is a small reminder of the various commands for managing MySQL databases using mysqldump, mysql, and mysqlimport commands in Linux.
How to Back Up MySql Database
To back up a MySQL database or database, the database must exist on the database server and you must have access to it. The command format would be
mysqldump --user=root --password --single-transaction database > database.sql
How to Back Up All Databases
If you want to back up all databases, use the following command with the –all-databases option
mysqldump --user=root --password --single-transaction --all-databases > all_databases.sql
How To Backup Only MySql Database Structure
If you only want to back up the database structure without data, use the –no-data option with the command
mysqldump --user=root --password --single-transaction --no-data database > database_structure.sql
How To Backup Only MySql Database Data
To back up data without structure only, use the –no-create-info option with the command
mysqldump --user=root --password --single-transaction --no-create-db --no-create-info database > database
How To Restore MySql Database
We just saw how to back up entire databases, only structures, and only data, now we will see how to restore them using the following command
mysql --user=root --password databse < database.sql
If you want to restore a database that already exists, then you will need to use the mysqlimport command
mysqlimport --user=root --password databse < database.sql
You can restore tables, structure, and database data in the same way.