A database is an elementary apart in the management of information within a company and is a vital component in information systems. Therefore, it is necessary to carry out operations that guarantee its optimal functioning. However, many times problems can occur that affect these data, so it is important to make constant backups of the database. If you use MySQL you will be able to do it without problems with mysqldump.
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfers to another SQL server.
Basic use of Mysqldump
The tool is a simple tool in its use but powerful for the possibilities and flexibility it offers you. Making a backup with mysqldump is really easy. I will show you:
- Back up a single database:
This is the most basic form of use that mysqldump has. Thanks to this, I will backup a single database. Open a terminal and Run:
:~# mysqldump -u root -p database > output.sql
Please replace the name of the database with yours and output.sql with the name you want.
- Make a backup of all databases:
But in some cases, I need to perform a Backup of all databases. To do this, run:
:~# mysqldump -u root -p --all-databases > output.sql
Don’t forget to replace output with the name you want for your backup.
- Back up multiple databases
And at other times, I need to perform backups of certain databases.
:~# mysqldump -u root -p --databases database1 database2 > output.sql
Again, I remind you to replace the data.
Common mysqldump options
There are many options to modify the execution of mysqldump, however, you can access them through the terminal
:~# mysqldump --help
Here, I’ll show them to you with a brief description:
–compatible=nameChange the dump to be compatible with a given mode. By default tables are dumped in a format optimized for MySQL
| mysqldump options | |
|---|---|
| Option | Description |
| -A, –all-databases | Dump all the databases |
| -Y, –all-tablespaces | Dump all the tablespaces |
| -y, –no-tablespaces | Do not dump any tablespace information |
| –add-drop-database | Add a DROP DATABASE before each create. |
| –add-drop-table | Add a DROP TABLE before each create. |
| -c, –complete-insert | Use complete insert statements |
| -C, –compress | Use compression in server/client protocol |
| -B, –databases | Dump several databases |
| -E, –events | Dump events |
| -F, –flush-logs | Flush logs file in server before starting dump |
| –flush-privileges | Emit a FLUSH PRIVILEGES statement after dumping the mysql database |
| -f, –force | Continue even if we get an SQL error |
| -h, –host=name | Connect to host |
| –ignore-table-name | Do not dump the specified table |
| -p, –password[=name] | Password to use when connecting to server |
| -P, –port=# | Port number to use for connection |
| –ssl | Enable SSL for connection |
| -u, –user=name | User for login if not current user |
| -v, –verbose | Print info about the various stages. |
Of course, there are other options, but their use is less frequent.
As I said, mysqldump is a wonder and also allows you to compress the backup using gzip.
:~# mysqldump -u root -p --all-databases | gzip > backup.sql.gz
Or, connect to a host:
:~# mysqldump -h IP -u root -p --routines --all-databases > output.sql
Restore a backup
If the backup contains only one database and does not contain ‘drop database’ and ‘create database’ statements, then the database where the restoration should be performed can be indicated:
:~# mysql -u root -p database < backup.sql
Or, if the backup is total, you can restore it as follows:
:~# mysql -u root -p < backup.sql
As you can see making a backup with mysqldump is really simple but at the same time is very important so I recommend you to further examine the options for you to get the most out of this tool.
We want to know your opinion, have you used mysqldump? What is your favorite option?
Please spread this article through your social networks
