[Linux] How to backup and restore MySQL databases on Linux

How to backup and restore MySQL databases on Linux

A simple and easy method for creating MySQL backups is to use the mysqldump command. This command will create a simple .sql file of an existing database, which can then be restored to any other empty MySQL database. This article will work for any Linux distribution running MySQL.

1. Back up the database using the following command:

mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
Example:
mysqldump -u root data112021 > data112021.sql
mysqldump -u root --quick --max_allowed_packet=512M > data112021.sql

The parameters of the said command as follows:

[username] - A valid MySQL username.

[password] - A valid MySQL password for the user.

[database_name] - A valid Database name you want to take backup.

[dump_file.sql] - The name of backup dump file you want to generate.

2. Restore the backup to a local database server - the mysql command will let you take the contents of a .sql file backup, and restore it directly to a database. This is the syntax for the command:

mysql -u [username] –p[password] [database_name] < [dump_file.sql]
Example:
mysql -u root data112021 < data112021.sql
mysql -u root --quick --max_allowed_packet=512M data112021 < data112021.sql

3. Restore the backup to a remote database server - you can also use the mysql command to restore a .sql file backup to a remote MySQL server. If you have another server running MySQL, and you have the database credentials, you can define a hostname in the command by adding the -h flag to specify a hostname. This changes the syntax of the command to:

mysql –h [hostname] –u [username] –p[password] [database_name] < [dump_file.sql]
Example:
mysql –h -u root data112021 < data112021.sql
mysql –h -u root --quick --max_allowed_packet=512M data112021 < data112021.sql

As long as you have the correct credentials and the remote server is running, you will be able to restore the database remotely.