MySQLdump command (backup local or remote databases)

MySQLdump – backup a local database

MySQL installation comes with a backup utility for backing up the databases. Here is how you can use it:

mysqldump -uUserName  Database_name > backup_file_name.sql

Enter the password when prompted.

Backup File name with date and time; modify the command as below:

mysqldump -uUserName  Database_name > backup_file_name_$(date +%F.%H%M%S).sql

To format the date further, please refer to this page: bash date formatting examples.

To enable gzip compression to the backup file, here is the modified command:

mysqldump -uUserName  Database_name | gzip > backup_file_name_$(date +%F.%H%M%S).sql

MySQLdump remote database backup (command line)

Use the following command to create a backup of the remote database. Replace the placeholders with actual values:

mysqldump -h remote_host -u remote_user -p remote_database > backup_file.sql

remote_host: Replace this with the hostname or IP address of the remote MySQL server.
remote_user: Replace this with your username to connect to the remote MySQL server.
remote_database: Replace this with the database name you want to back up.
backup_file.sql: Replace this with the desired name for your backup file. It will be saved in the current directory on your local machine.

After running this command, you’ll be prompted to enter the password for the remote MySQL user.

The mysqldump command generates a SQL script containing the database’s structure and data. It’s a helpful way to create backups that can be quickly restored later.

Remember that if the remote MySQL server is not publicly accessible, you might need to ensure that you have the necessary network connectivity and permissions to connect to it from your local machine. Also, keep your backup files secure, as they contain sensitive information from your database.