How to Dump Remote Database in MySQL

1. What is MySQL Dump
2. Different Syntax of MySQL Dump
3. MySQL Dump From the Remote Server to the Local Machine

This article introduces how to dump MySQL remote database.

What is MySQL Dump

MySQL dump is the process of backing up the SQL databases and the MySQL database package to another server. The process generally happens to keep a backup of the records from one place to its replicas.

Often this process is scheduled and iterates on time. MySQL provides users with the mysqldump utility to recreate the databases with privileges.

The utility creates a flat file in the system with all the SQL statements to regenerate the schema.

The server where the dump is there must not necessarily be the MySQL server. The DumpFile is the file that keeps track of all the databases and schema.

With the help of the backup file, the database schema can get recreated at another instance or the server. The file contains the set of SQL statements like creating and insert, which repopulates the similar schema hierarchy (database->table->views->functions->records) at another place.

The process of dumping generally takes 30 mins or more based on the size of the database.

The Prerequisites for MySQL dump are:

1. There must be all access privileges to the database, functions, views, triggers for using the utility.
2. The dumped file should have to create, alter, insert privileges as it will recreate new schemas and would need to alter and insert operations on them.

Different Syntax of MySQL Dump

The syntax of MySQL dump for all databases:

mysqldump [options] --databases database_name ... > backupFileName.sql

The syntax of MySQL dump for tables:

mysqldump [options] database_name [table_name ...] > backupFileName.sql

The syntax of the MySQL dump for the MySQL workbench:

mysqldump [options] --all-databases > backupFileName.sql

The above syntax forms dump of the database, table, and all the databases. The explanation of its arguments is below.

1. The keyword mysqldump must always be at the start. It denotes that the MySQL statement is to create a dump. It indicates that a SQL dump needs to get formed.
2. Followed by the keyword, the options are non-mandatory attributes that denote that the user can add/remove options on a need basis. Examples of options are:
   2.1 The command -u root -p has options as hyphen u and hyphen p to indicate the username and password attributes in the options list. The root is the username set in MySQL, and the password is the user’s choice.
3. The --databases keyword allows the user to specify the name of the database to take backup followed by the Database name.
4. For dump of tables, specify database name followed by table name with the same syntax given above.
5. For creating the backup of all the databases inside the workbench, directly pass --all-databases without any specific database name in the file.
6. The > symbol specifies the output of the first section must pass to the file provided after the operator.
7. The backupfileName.sql is the file name in which all the dump should be kept. The file name is a relative path and not the file’s absolute path, which creates the backup file in the bin folder of the MySQL developer workbench.

MySQL Dump From the Remote Server to the Local Machine

The command for MySQL dump from the remote server to the local machine is below.

The query works when no MYSQLdump is installed on the remote server. In that scenario, a tunnel needs to get created from the local machine to the remote server and then dump the data in the local machine.

The LINUX environment is the prerequisite to the ssh secured tunnel. The ssh command will work on the same.

The command to create an ssh tunnel is as below.

ssh -f -L3310:localhost:3306 user@remote.server -N

The ssh keyword allows the system to create a secured encrypted connection with the remote server. Using this command, a user can make a secured connection with any remote machine knowing the server details.

There are various options available with the ssh command.

1. The -f option enables the process to go to the background before the process’s actual execution.

2. The -L option specifies that the local or client machine port will be forwarded to the remote host and server machine port.

   In the above query, port 3310 is the remote host port where the process will start. Port 3306 is the localhost server port.

3. The -N option gets used for securely forwarding the ports. The command is to create a tunnel between local and remote servers.

mysqldump -P 3310 -h 127.0.0.1 -u mysql_user -p database_name table_name

The mysqldump keyword states the host machine to create a database dump. The dump forms once a secured connection is established.

1. The -P option specifies the port where the remote machine establishes the connection. In the above case port is 3310.
2. The -h option states the remote server’s hostname. Here the connection is made using the process of tunneling.
3. The -u option states that the argument next is the username that helps establish a secured connection to the remote server.
4. The -p option states the password to use while connecting to the remote server.

The database name and table name provided in the above statement states the table name to get dumped in the local machine from the remote server.