How to Dump Remote Database in MySQL
- What is MySQL Dump
- Different Syntax of MySQL Dump
- MySQL Dump From the Remote Server to the Local Machine
![How to Dump Remote Database in MySQL](/img/MySQL/feature-image---mysqldump-remote-database.webp)
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:
- There must be all access privileges to the database, functions, views, triggers for using the utility.
- 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.
- 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. - 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 hyphenu
and hyphenp
to indicate the username and password attributes in the options list. Theroot
is the username set in MySQL, and the password is the user’s choice. - The
--databases
keyword allows the user to specify the name of the database to take backup followed by the Database name. - For dump of tables, specify database name followed by table name with the same syntax given above.
- For creating the backup of all the databases inside the workbench, directly pass
--all-databases
without any specific database name in the file. - The
>
symbol specifies the output of the first section must pass to the file provided after the operator. - 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.
-
The
-f
option enables the process to go to the background before the process’s actual execution. -
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. Port3306
is the localhost server port. -
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.
- The
-P
option specifies the port where the remote machine establishes the connection. In the above case port is3310
. - The
-h
option states the remote server’s hostname. Here the connection is made using the process of tunneling. - The
-u
option states that the argument next is the username that helps establish a secured connection to the remote server. - 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.
Rashmi is a professional Software Developer with hands on over varied tech stack. She has been working on Java, Springboot, Microservices, Typescript, MySQL, Graphql and more. She loves to spread knowledge via her writings. She is keen taking up new things and adopt in her career.
LinkedIn