MySQL Backup: Safeguarding Your MySQL Dump

In this article, we will consider ways of backing up MySQL DBMS and give examples of the most useful keys and parameters. You can use Hostman cloud databases as a test platform.

Syntax

You can create a database dump through the command line using the mysqldump utility. It is installed by default with the MySQL package. Its functionality is the same on Linux and Windows, and it can be launched on both local and remote DBMS servers. General syntax:

mysqldump [options] > <dump_file_name>

Example:

mysqldump -v -h127.0.0.0.1 -uroot mydb -p > /tmp/dump.sql

Here, we have made a backup archive of the database called mydb and placed it in the /tmp directory. We connect to the local database (IP 127.0.0.1) under the root account. It is the simplest way of creating a MySQL database backup.

These are the basic parameters of the mysqldump utility:

-h is the host address you must connect to to create the dump.
-u is the account that performs the backup (make sure that you have all the necessary permissions).
-p is the account password. When using a script, it should be entered in the format p12345; in other cases, it is better to leave only the -p symbol, as it is safer.

We have listed only the most popular parameters. Check the official website to view the full list for a more in-depth study of the issue.

Creating MySQL dumps

Let's look at several other cases.

Backing up with subsequent archiving

Command:

DBNAME=mydb
mysqldump -uroot -p ${DBNAME} | gzip -c > /tmp/${DBNAME}.sql.gz

We made a DBNAME variable and placed the name of the database we need to archive. After that, we ran the mysqldump utility to pass the execution queue to the GZIP archiver upon completion.

As a result, the dump is placed in the /tmp/<base name>.sql.gz directory. If you want to display the process on the screen, use an additional -v option:

mysqldump -v -uroot -p ${DBNAME} | gzip -c > /tmp/${DBNAME}.sql.gz

Simultaneous archiving of two or more databases

The task is simple, just list the database names with a space and use the -B option. This is how the command will look like:

mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiplе_bases.sql

Backing up all available bases

You can also run MySQL database backups using the --all-databases parameter instead of the database name. Example command:

mysqldump -v -uroot -p --all-databases > /tmp/dump_all_bases.sql

Backing up only the database structure

This method will save the tables without any data to the archive. The mode is selected using the --no-data parameter:

mysqldump -v -uroot -p --no-data base1 > /tmp/dump_base1_nodata.sql

Backing up a table

In this case, you should add the table name to the database name. For example, as in the command:

mysqldump -v -uroot -p base1 table1 table2 > /tmp/dump_base1_tables.sql

Transferring DBMS access rights

The system can unload the current accounts along with the current passwords. This function is required to export the DBMS to another host without the risk of losing access to the database. The command will look like this:

mysqldump -v -uroot -p mysql user > /tmp/mysql_user.sql

Execute the flush privileges command (via SQL Shell) to ensure operability after migration.

Ignoring a specific table during archiving

Use the command with the --ignore-table key:

mysqldump -v -uroot -p base --ignore-table=base.logs > /tmp/base.sql

Backing up under any account

In the examples above we connect to the database as the root user. In practice, the procedure can be performed under other accounts as well. The main thing is that the selected user should have the right to access the database we need to dump. You can create a separate account with limited rights to perform archiving. Let's do it with the SQL command:

> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES, PROCESS, EXECUTE ON *.* TO backup@localhost IDENTIFIED BY 'backup123';

Here, we have created the backup account and set its password as backup123. The user will have access to connect to the database on the local server. In order not to manually enter the same parameters at each startup, we can write a configuration file:

vi ~/.my.cnf

[mysqldump]
host = 127.0.0.1
user=backup
password="backup123"

Now, the command will take a "light" form:

mysqldump base > /tmp/dump.sql

Backing up the database via phpMyAdmin

If it is inconvenient to work in the console, you can use phpMyAdmin.

Go to the "Export" item of the top menu, and in the "Export Method" section, put the switch to "Normal". After that, several additional options will open. Among them, we are interested in the compression method, for example, ZIP. Click OK, and the dump will be downloaded, backed up, and saved on the local computer.

Troubleshooting

Sometimes, you may encounter the error Incorrect key file for table, which looks like this:

mysqldump: Error 1034: Incorrect key file for table '<table name>'; try to repair it when dumping table `<table name>` at row: xxxxxx

There may be several reasons for this. For example, a logical error of a table in the database has been detected, or the storage space where the system saves temporary data has run out. Accordingly, different approaches are needed to solve the problem. So, the first thing to do is to check the amount of space on the disk. The configuration file located at /etc/my.cnf contains the tmpdir option, which defines the folder where the system saves temporary tables.

If it does not exist, the /tmp path will be used by default. You can check if there is enough space to create a dump with the command:

df -h

The situation is different if a table is corrupted. It must be restored, but the solution depends on the type of the problematic part of the database.

MyISAM. In the SQL command line, enter the command:

> REPAIR TABLE <table name> USE_FRM;

After that, repeat the attempt to create a backup copy.

INNODB. Open the DBMS configuration file:

vi /etc/my.cnf

Find the [mysqld] section and add the line:

innodb_force_recovery = 1

Restart the database server:

systemctl restart mysql

If the error occurs again, change the value of innodb_force_recovery to 2. If the problem persists, change it to 3, and so on up to 6, until the backup succeeds. Keep in mind that the applied parameter sometimes leads to data loss. If you have to raise its value above 2, you should manually check if valuable information is still safe after the backup is completed.

Conclusion

The mysqldump command has a wide functionality that allows you to backup databases in various combinations. If necessary, it can be easily replaced by phpMyAdmin, a convenient tool with a graphical interface. To experiment with these instructions, use a test database, with no valuable information. For example, you can rent a cloud database from Hostman.