MySQL Database Dump on Ubuntu/Linux

2 min read1 day ago

Backing up your MySQL database is a vital part of maintaining and managing databases. Before we start, follow this step-by-step guide to learn how to dump your MySQL database in Ubuntu/Linux and enable compression over it.

Step 1: Make a Database Dump

If you are running within MySQL tablespaces, then this command can be utilized to make the resulting file truly independent from your current database. This command will take a dump of your MySQL database and run it in the background (note: by having & at the end).

mysqldump --single-transaction --set-gtid-purged=OFF -u<db_user> -p<db_password> -h<db_host> <db_name>.dump &

--single-transaction : It will maintain consistency in the dump, useful for InnoDB tables.
--set-gtid-purged=OFF : Ensures the GTID (Global Transaction Identifier) is excluded from the dump, important for replication setups.
-u<db_user> : Replace <db_user> with your database username.
-p<db_password> : Replace <db_password> with your database password. You will be prompted for the password if you do not include it here.
-h<db_host> : Substitute <db_host> with your database host. Use localhost if the database is on the same machine.
<db_name> : Replace <db_name> with the database name you want to dump.

The & at the end of the command will run it in the background, so you can continue using the terminal for other tasks.

Step 2: Compressing the Dump File

After you are done making a dump file, you need to compress it using the following command. To save disk space and for quicker transfer of the dump file, compress it.

gzip <db_name>.dump &

<db_name> : Use the name of your own database. The & at the end of the command line makes it run in the background, keeping your terminal free for other jobs.

Step 3: Relocating the Compressed Dump File

After the dump file has been compressed, you might want to move it to your storage or a more secure place. Move the compressed dump file using the mv command.

mv <db_name>.dump.gz <path_to_move_dump>/<db_name>.dump.gz

<db_name> : Replace with your database dump file name.
<path_to_move_dump> : The path to the directory where you want your compressed dump file moved.

Example

Here is an example of how you could run these commands:

Create the database dump:

mysqldump --single-transaction --set-gtid-purged=OFF -uadmin -psecretpassword -hlocalhost mydatabase > mydatabase.dump &

Compress the dump file:

gzip mydatabase.dump &

Move the compressed dump file:

mv mydatabase.dump.gz /backup/mydatabase.dump.gz

Conclusion

These are simple and easy steps to back up a MySQL database using mysqldump, compress it, and move the data dump file. This process is necessary to make regular database backups secure and prevent unauthorized access to your data. Make sure to keep an eye out for our subsequent posts where we provide advanced tips and tricks on how best to manage MySQL databases.

Stammp Technologies Pvt Ltd (https://stammp.com/)
https://www.linkedin.com/company/stammp
https://www.linkedin.com/in/jayprakashjangir/