MySQLDump - MySQL database backup

Using the mysqldump tool, you can back up a local database and restore it to a remote database with the same characteristics, all using a simple command. Next, you’ll see practical examples of using the mysqldump tool to export and import MySQL databases.

Below is an export example:

1

$ mysqldump -u [USERNAME] -p[PASSWORD] [DATABASE-NAME] > dumpfilename.sql

And to import the database:

1

$ mysql -u [USERNAME] -p[PASSWORD] [DATABASE-NAME] < dumpfilename.sql

Based on the syntax shown above, let’s say there is a database called “exemplo”, and we want to export it. To do this, we would run the following command:

1

$ mysqldump -u root exemplo > exemplo.sql

This command would export all the content in the “exemplo” database to a file called “exemplo.sql”.

Once we have our database exported, in another database (for example the one in the production environment) into which we want to import the previously exported database, we would run the following command:

1

$ mysql -u root exemplo < exemplo.sql

We can also import a database by connecting to mysql and running the source command there to import the database, for this we use the following sequence of commands:

1

$ mysql -u root

1

$ mysql> use exemplo;

1

$ mysql> source /home/usuario/dump/dumpfilename.sql

1. Back up N databases simultaneously:

If you intend to back up N databases simultaneously, first you’ll need to identify the names of the databases you want to export. To do this, you can connect to the database with the following command:

1

$ mysql -u root

After connecting, you can list the database names with the following command:

1

$ mysql> show databases;

The command’s output is shown below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

$ mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| clientes |
| mysql |
| exemplo |
+--------------------+
4 rows in set (0.00 sec)

If you want to export the cliente database and the exemplo database, you could run the tool using the following syntax:

1

$ mysqldump -u root --databases <DATABASE-NAME-1 DATABASE-NAME-2 DATABASE-NAME-N> > dumpfilename.sql

So our command would look like this:

1

$ mysqldump -u root --databases clientes exemplo > clientes_exemplo.sql

2. Back up all databases:

The following command would back up all databases:

1

$ mysqldump -u root --all-databases > dumpfilename.sql

3. Back up a specific table

If you intend to back up a specific table within a database, you can use the following syntax:

1

$ mysqldump -u <DATABASE-NAME> <TABLE-NAME> > dumpfilename.sql

So if we wanted to export a table called “tabela1″ in a database called “exemplo”, we would use the following command:

1

$ mysqldump -u root exemplo tabela1 > exemplo_tabela1.sql

Well, that’s all folks.

Questions? Leave a comment!