Exporting and importing data to MySQL is usually required when transferring information from one MySQL database to another and for backup purposes.

Data backup is purely technological in nature. This means that in case of any software or hardware hardware failure, we will be able to restore the current customer data. We guarantee the safety of the data itself, not their backups.

However, if there is a backup copy for the required date, we are ready to meet the client halfway and restore the data lost due to negligence. To do this, you need to apply for a free theme from the control panel.

The only way to be sure of 100% data recovery is to perform regular backup of information yourself. To do this, you need to use the mysqldump utility, which is available through the unix shell.

Example of commands for exporting and importing a database:

mysqldump -u user_name -p -h db server_name database name > dump.sql

The data will be saved in the dump.sql file. Then (if necessary) we restore the data from the backup copy (dump) as follows:

mysql -u user_name -p -h db server_name database name < dump.sql

MySQL utilities launched in this way will request a password to the database. You need to enter the password of the corresponding DB user. If the password has been lost, you can change it yourself in the control panel, in the section Service tree (the drop-down menu is located in the topmost line of the control panel on the right).

By default, the system assumes that the data in the database is stored in UTF-8 encoding. If your data is stored in a different encoding, then after creating a backup copy (dump) for subsequent correct data recovery, you need to open the created file with a text editor and correct the line in it:

/*!40101 SET NAMES utf8 */;

For example, for Windows-1251 encoding, you need to specify:

/*!40101 SET NAMES cp1251 */;

Additional mysqldump utility options:

--add-drop-table — option that adds the DROP TABLE command before creating tables. Before restoring tables from the dump, tables with the same name in the working database will be deleted and recreated from the backup. It is recommended to use it to prevent possible errors after recovery;

--add-locks — option that adds the LOCK TABLES commands before executing and UNLOCK TABLE after executing each table dump. It is used to speed up access to MySQL;

--quote-names — option that tells the utility to put quotation marks for table and column names. Recommended for use for MySQL versions, younger 4.1.1. In older versions, it is activated by default.

The --quick and --opt options are recommended if the MySQL database is too large to fit entirely in memory.

At the same time, the mysqldump utility throws an error:

mysqldump: Out of memory (Needed XXXXX bytes)
mysqldump: Got error: 2008: MySQL client
run out of memory when retrieving data from server 

As a result, the line for creating a copy of the database is as follows:

mysqldump --opt -u user_name -p -h db server_name --add-drop-table database name > dump.sql

either such:

mysqldump --quick -u user_name -p -h db server_name --add-drop-table database name > dump.sql

For compatibility of a dump made on a third-party server, we recommend creating a database dump with the key:

--set-variable max_allowed_packet=2M

or

-O max_allowed_packet=2M

If during import you get an error like

mysqldump: Error 2020:
Got packet bigger than 'max_allowed_packet'
bytes when dumping table `some_table_name ` at row: 2

this means that there is data in your database that cannot be divided into separate parts, each no more than two megabytes.

As a rule, this happens if the MySQL database is used to store the contents of various files, for example, pictures or music, without placing them directly in the file system. In this case, if the size of a file exceeds a certain value, the database becomes unusable on our servers and you will either need to delete such data from your database, or consider using a database server on a VPS server, where you can set the desired max_allowed_packet limit to the required value, or not use it at all.

If the version MySQL 4.0.x, is installed on your server, then for the correct transfer of data from third-party servers, it is necessary to use the key when exporting the database:

--compatible=mysql40