Import and export MySQL data via unix Shell

Exporting and importing data into MySQL is usually required when moving information from one MySQL database to another and for performing backups.

Data backup is purely technological in nature. This means that in the event of any software or hardware failure, we will be able to restore the client's current data. We guarantee the safety of the data itself, and not its backup copies.

However, if we have a backup copy for the required date, we are ready to meet the client halfway and restore 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 regularly back up your information yourself. To do this, you need to use the mysqldump utility, which is available through the unix shell.

Example commands for exporting and importing a database:

mysqldump -u username -p -h dbname> dump.sql

The data will be saved in the dump.sql file. Next (if necessary) we restore the data from the backup copy (dump) like this:

mysql -u username -p -h dbname < dump.sql

Utilities for working with MySQL launched in this way will ask for a password to the database. You must enter the password of the corresponding database user. If the password has been lost, you can change it yourself in the control panel, in the Service Tree section (the drop-down menu is located in the topmost line of the control panel on the right).

By default, the system considers 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), in order to subsequently restore the data correctly, 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 options for the mysqldump utility:

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

--add-locks — option that adds LOCK TABLES commands before execution and UNLOCK TABLE after execution of each table dump. Used to speed up access to MySQL;

--quote-names — an option that tells the utility about the need to put quotes for table and column names. Recommended for use for MySQL versions younger than 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.

In this case, the mysqldump utility generates 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 to create a copy of the database looks like this:

mysqldump --opt -u username-p -h dbname --add-drop-table dbname  > dump.sql

or this one:

mysqldump --quick -u username-p -h dbname --add-drop-table dbname > 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 this one:

-O max_allowed_packet=2M

If during import you receive 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 your database contains data that cannot be divided into separate parts, each no more than two megabytes.

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

If MySQL 4.0.x is installed on your server, then to correctly transfer data from third-party servers, you must use the key when exporting the database:

--compatible=mysql40