Linux Commands for Backing up and Restoring MySQL Databases in the Terminal

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.

Related Posts