Although there are different methods for backing up MySQL and MariaDB databases, the most common and effective one is to use a native tool that both MySQL and MariaDB make available for this purpose: the mysqldump command. As its name suggests, this is a command-line executable program that allows you to perform a complete export (dump) of all the contents of a database or even all the databases in a running MySQL or MariaDB instance. Of course it also allows partial backups, i.e. only some specific tables, or even only only a subset of all the records in a table.
The mysqldump command offers a multitude of different parameters that make it very powerful and flexible. Since having so many options can be confusing, in this post I am going to collect several of the most frequent usage examples with the most common parameters and that are most useful in the day to day life of the system administrator.
Being an executable program, mysqldump requires command line access to the MySQL server or network communication typically via port 3306 allowed. However, for security reasons, in many cases you don’t have either one or the other and it is not possible to use this command. Discover how to backup MySQL in an alternative way when you cannot use the mysqldump command:
Backup MySQL database using PHP1.- Backup completo de una base de datos
Let’s imagine that you have a MySQL or MariaDB database server with multiple databases and you want to backup the whole content of one of them. In this sense mysqldump offers the ability to export the structure, the data, the triggers and the procedures or routines separately, or all of them as a whole. Let’s see first the star example, that is, how to perform a complete export of all the information in a database which includes schema structure, data, events, procedures, triggers and views:
$ mysqldump -v --opt --events --routines --triggers --default-character-set=utf8 -u your_username -p your_db_name > db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql
If you also want to directly generate a compressed file with gzip or bzip2 without the need to first write to disk the SQL file resulting from the mysqldump command (much larger) and then compress it in a second step, you can run the command by adding | gzip -c after the name of the database to be exported.
This is very important especially if you want to automate your backups, since not doing so could cause the file system to become full before the backup is completed, which would result in failure and also you wouldn’t be allowed to perform more backups until new disk space is freed. Thus, the same command of the previous example but adapted to an unattended automatic backup environment would look like this:
$ mysqldump --opt --events --routines --triggers --default-character-set=utf8 -u your_username --password=your_password your_db_name | gzip -c > db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql.gz
If instead of specifying the specific database you want backup of all those present in a MySQL/MariaDB instance or server, then you will use the –all-databases parameter instead of the database name, your_db_name in our example.
Error: Your Requested widget " ai_widget-6" is not in the widget list.
- [do_widget_area above-nav-left]
- [do_widget_area above-nav-right]
- [do_widget_area footer-1]
- [do_widget id="wpp-4"]
- [do_widget_area footer-2]
- [do_widget id="recent-posts-4"]
- [do_widget_area footer-3]
- [do_widget id="recent-comments-3"]
- [do_widget_area footer-4]
- [do_widget id="archives-4"]
- [do_widget_area logo-bar]
- [do_widget id="oxywidgetwpml-3"]
- [do_widget_area menu-bar]
- [do_widget id="search-3"]
- [do_widget_area sidebar]
- [do_widget id="search-4"]
- [do_widget id="ai_widget-2"]
- [do_widget id="categories-5"]
- [do_widget id="ai_widget-3"]
- [do_widget id="ai_widget-4"]
- [do_widget id="ai_widget-5"]
- [do_widget_area sub-footer-1]
- [do_widget id="text-4"]
- [do_widget_area sub-footer-2]
- [do_widget_area sub-footer-3]
- [do_widget_area sub-footer-4]
- [do_widget_area upper-footer-1]
- [do_widget id="search-2"]
- [do_widget id="recent-posts-2"]
- [do_widget id="recent-comments-2"]
- [do_widget id="archives-2"]
- [do_widget id="categories-2"]
- [do_widget id="meta-2"]
- [do_widget_area upper-footer-2]
- [do_widget_area upper-footer-3]
- [do_widget_area upper-footer-4]
- [do_widget_area widgets_for_shortcodes]
- [do_widget id="search-5"]
- [do_widget id="ai_widget-6"]
- [do_widget_area wp_inactive_widgets]
- [do_widget id="wpp-2"]
- [do_widget id="text-1"]
- [do_widget id="recent-posts-3"]
- [do_widget id="categories-3"]
- [do_widget id="archives-3"]
- [do_widget id="icl_lang_sel_widget-3"]
1.1. Exporting only database structure
In this case you will use the –no-data parameter to generate a SQL file containing only CREATE TABLE statements that allow you to reproduce the structure of your database, but creating it empty of content:
$ mysqldump -v --opt --no-data --default-character-set=utf8 -u your_username -p your_db_name > db_structure_your_db_name_`date +%Y%m%d_%H%M%S`.sql
1.2. Exporting only table content
This would be the opposite of the previous example, i.e. you only want to export your database tables content by generating a SQL file that basically includes INSERT statements. For this you will use the –no-create-info and –skip-triggers parameters:
$ mysqldump -v --opt --no-create-info --skip-triggers --default-character-set=utf8 -u your_username -p your_db_name > db_data_your_db_name_`date +%Y%m%d_%H%M%S`.sql
2.- Partial backup of a database
If you are only interested in backing up a subset of tables you have two options: specify one by one the name of the different tables you want to back up, or use the –ignore-table parameter to export all the tables except those that we expressly indicate to be ignored:
2.1. Include only a subset of tables
$ mysqldump -v --opt --default-character-set=utf8 -u your_username -p your_db_name table1 table2 table3 > db_tables_your_db_name_`date +%Y%m%d_%H%M%S`.sql
2.2. Exclude a subset of tables
$ mysqldump -v --opt --default-character-set=utf8 -u your_username -p --ignore-table=table1 --ignore-table=table2 --ignore-table=table3 your_db_name > db_tables_your_db_name_`date +%Y%m%d_%H%M%S`.sql
3.- Remote backups
If you need to back up a database to a remote server, here are a couple of different ways to accomplish this:
Error: Your Requested widget " ai_widget-6" is not in the widget list.
- [do_widget_area above-nav-left]
- [do_widget_area above-nav-right]
- [do_widget_area footer-1]
- [do_widget id="wpp-4"]
- [do_widget_area footer-2]
- [do_widget id="recent-posts-4"]
- [do_widget_area footer-3]
- [do_widget id="recent-comments-3"]
- [do_widget_area footer-4]
- [do_widget id="archives-4"]
- [do_widget_area logo-bar]
- [do_widget id="oxywidgetwpml-3"]
- [do_widget_area menu-bar]
- [do_widget id="search-3"]
- [do_widget_area sidebar]
- [do_widget id="search-4"]
- [do_widget id="ai_widget-2"]
- [do_widget id="categories-5"]
- [do_widget id="ai_widget-3"]
- [do_widget id="ai_widget-4"]
- [do_widget id="ai_widget-5"]
- [do_widget_area sub-footer-1]
- [do_widget id="text-4"]
- [do_widget_area sub-footer-2]
- [do_widget_area sub-footer-3]
- [do_widget_area sub-footer-4]
- [do_widget_area upper-footer-1]
- [do_widget id="search-2"]
- [do_widget id="recent-posts-2"]
- [do_widget id="recent-comments-2"]
- [do_widget id="archives-2"]
- [do_widget id="categories-2"]
- [do_widget id="meta-2"]
- [do_widget_area upper-footer-2]
- [do_widget_area upper-footer-3]
- [do_widget_area upper-footer-4]
- [do_widget_area widgets_for_shortcodes]
- [do_widget id="search-5"]
- [do_widget id="ai_widget-6"]
- [do_widget_area wp_inactive_widgets]
- [do_widget id="wpp-2"]
- [do_widget id="text-1"]
- [do_widget id="recent-posts-3"]
- [do_widget id="categories-3"]
- [do_widget id="archives-3"]
- [do_widget id="icl_lang_sel_widget-3"]
3.1. Accessing the MySQL server via TCP/IP
If you can access your MySQL server through TCP/IP via its port 3306, you can perform backups in the same way as in the previous examples, but specifying the hostname and the port where the MySQL server listens by using the -h and -P parameters. It is also advisable to indicate the –compress parameter to compress network traffic between the client and server. As a result, you will gain in speed and bandwidth:
$ mysqldump -v --opt -h mysql.example.com -P 3306 --compress --events --routines --triggers --default-character-set=utf8 -u your_username -p your_db_name > db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql
3.2. Accessing via SSH
If on the other hand your MySQL server is only accessible via socket from localhost or some firewall prevents you from TCP/IP access, you can use SSH to perform the backup from one host to another like this:
$ ssh ssh_username@server "mysqldump -v --opt --events --routines --triggers --default-character-set=utf8 -u your_username --password=your_password your_db_name | gzip -c" > db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql.gz
4.- Database restoration
To restore a backup taken with any of the previous examples you will simply redirect the content of one of the .sql files to the mysql command and all the contents will be imported:
$ mysql -u your_username --password=your_password your_db_name < db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql
In case the SQL file contains information from more than one database (e.g. generated with the –all-databases parameter seen above) it is not necessary to specify any database name in the command line.
You can restore a compressed backup without having to uncompress it first like this:
$ gunzip -c db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql.gz | mysql -u your_username --password=your_password your_db_name
5.- Synchronize database between two servers
Finally, you can go a little further with the use of pipes and redirects and get to synchronize the whole content of a database to another without creating any intermediate file, whether they are on the same server or on remote servers. Here is a very useful one-line command that will allow you to do this quickly:
$ ssh ssh_username@server "mysqldump -v --opt --events --routines --triggers --default-character-set=utf8 -u your_username --password=your_password your_db_name | gzip -c" | gunzip | mysql --password=your_password -u your_username your_db_name
In this example a full backup of a database located on a remote server (source) is taken from the destination server to which you want to copy the database. Both servers don’t have to share the same usernames, database and passwords and it will be enough to properly set those on one side and those on the other.
By means of the same mechanism it could also be done the other way around, that is, to run a command from the source server that copies a database to a remote destination server. I leave it to you to put this into practice as an exercise, although if you have any difficulties don’t hesitate to contact me and I will be happy to help you!
Leave a Reply