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 the 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.