Import a large database into mysql. Importing SQL Files into MySQL Databases

Useful tips

Good day, colleagues :)

Today I will continue to introduce you to working with MySQL in the console and the MySQL command line.

I have already written articles on how to perform basic actions with MySQL data through the console and how to backup the MySQL database, as well as export the information stored in it.

The logical continuation of this story will be the restoration of the database and the information stored in it using MySQL database import operations. And, importantly, we will continue to do this using the tool of all true developers - through the console.

If you need instructions for importing a database via phpMyAdmin, then you can find it in the article about. In the current article, I am not eager to describe it again, especially since today’s material will be devoted exclusively to importing the MySQL database through the console.

But, before we start reviewing methods and tools, a few words about what importing a MySQL database is, what it is like and how best to do it?

Importing a MySQL database: what and why?

Importing a MySQL database is an operation that fills the database with information. In this case, the data source is a dump file - a snapshot of another database, automatically created during the export operation, or a specially prepared SQL script.

Import, as well as export of the MySQL database, there are two types of information stored in the database:

the structure of the database, its tables and the data stored in them (commonly called a database dump);
simply data stored in a table or collected using SELECT requests.

This article will discuss both options.

To restore a MySQL database with its structure and all stored information from a dump, as already mentioned, you need a database dump file, which is a text file with any extension (can be pre-packed into an archive to reduce the size), containing SQL commands for creating the database itself and tables, as well as filling them with information.

Therefore, in order to restore a MySQL database from a dump, you need to execute the commands contained in the file.

For regular data recovery, such complications are not necessary. It is enough to have a test file available, the information in which will be structured in the same way as in the database table: the number of columns with information corresponds to the number of table record attributes.

For these purposes, a regular txt file will be suitable, the data in which will be separated, or files created in special spreadsheet editors (Microsoft Office Excel, OpenOffice, etc.) with an excellent extension: xls, csv, odt, etc.

These formats are even preferable, because When creating them, data delimiters are added automatically by editors, and there is no need to enter them separately, as in the case of a regular text file.

Adding Data to MySQL: Tools

Regarding the tools for importing a MySQL database, I can say that today there are three of them.

I will list them, starting from the lowest level and ending with the highest level (from the point of view of using all kinds of shells and add-ons):

Server console and MySQL command line;
Scripts written in programming languages that allow you to record data in MySQL using language tools;
Ready-made programs that provide a visual interface for working with the database (the same phpMyAdmin, MySQL WorkBench, MySQL Manager, etc.).

I think that the order of the tools will not raise any questions for anyone, because... Programming language tools, as a rule, work on the basis of MySQL console commands, and programs are based on scripts or work directly with MySQL from the command line.

One way or another, the console is at the forefront of everything, and the remaining tools are, in fact, its emulators.

Therefore, using the console when importing data into MySQL allows you to bypass various kinds of restrictions set by the settings of programming languages on the Web server and the programs themselves (which, by the way, are not always possible to change).

Due to this, you can not only load a MySQL database through the console faster, but also make this operation possible in principle, because Scripts and programs tend to interrupt imports when the maximum script execution time is reached or not start at all due to the size of the downloaded file.

I think everyone who has ever tried to upload a large dump into a MySQL database via phpMyAdmin understands what I’m talking about.

Often these limits are the cause of errors when importing a MySQL database, which you will never see when using the console.

They, of course, are not constant, and they can be changed, but this is an additional headache, which, by the way, may not be solvable for ordinary users.

I hope that I have motivated you to import the MySQL database via the console (both its structure and individual data).

And on this positive note, we move on to the long-awaited practice and consider methods and commands for console transfer of data to the database.

How to restore a MySQL database from a dump via the console?

So, in order to deploy a MySQL dump from the console, there are two ways:

using a command on the MySQL command line;
in the server console itself.

Let's start in order.

So, in order to import a MySQL database dump into an existing storage via , we first need to launch it and select the desired database into which we will upload our dump.

The implementation of these actions is described in detail in the article linked above, so if you need a description of them, take them from there, because I don’t want to duplicate them for the second round.

After you have done the above, enter the following command into MySQL Shell:

Source path_and_dump_file_name;

All that remains for us is to study the messages in the console about the progress of the operations contained in the dump.

Without first switching to the desired database, after connecting to the MySQL server in the console, the dump can be imported with the following command:

Mysql -u username -p database_name< путь_и_имя_файла_дампа

That's all. The main thing is to wait for the import to finish if the file is very large. The completion of the dump can be judged by when the server console is available again.

As a matter of fact, this is the disadvantage of this method compared to the previous one, because in the first it is possible to monitor the operations performed on the database during import, but in the second it is not.

If the dump file is packed into an archive, then when downloading it will need to be unpacked at the same time.

On Linux this can be done like this:

Gunzip > [archive_file_name.sql.gz] | mysql -u -p

In Windows there is no standard utility for unpacking the archive in the console, so you will need to install it additionally.

As you can see, importing a MySQL dump via the console is a very simple operation, which is performed with one command. So you don't have to be a developer to perform this procedure.

If suddenly you don’t know how to launch the server console, you can find this information in the article about the MySQL command line, the link to which I already posted earlier.

By the way, using the described methods it is also possible to import a MySQL table, and not the entire database. In this case, the dump you upload must contain the operations of creating it and filling it with data.

Loading data into a MySQL database from a file in the console

We talked about restoring a MySQL database from a dump in the console. Now is the time to figure out how you can import data from files, including xls and csv, into a MySQL database in the same way.

For this task, we again have the same two tools as in the previous case: the MySQL command line and the server console.

Let's start the review in order again.

So, to import a file in the MySQL command line, we run it again and go to the database into which the data will be loaded.

LOAD DATA INFILE "path_and_name_of_dump_file" INTO TABLE `database_table` COLUMNS TERMINATED BY "," ENCLOSED BY "\"" LINES TERMINATED BY "\n";

Don't forget that if the MySQL server was started with the option --secure-file-priv(which often happens when using MySQL distributions included in WAMP/MAMP assemblies), then the file name must be specified taking into account the system variable secure_file_priv.

In order to import a MySQL database into the server console without going into MySQL Shell, we will need the utility mysqlimport, included in the MySQL distribution, and its following call:

mysqlimport –u user_name –p database_name name_and_path_to_import_file

This utility is an analogue of the SQL command LOAD DATA INFILE, for command line only. But, the question arises, why then, among the parameters of its call, is not the table into which the data from the file will be loaded?

The fact is that mysqlimport simply physically does not have this parameter. Instead, the name of the table into which the data will be loaded must appear in the name of the imported file.

Those. if you want to import an Excel table into a MySQL table users, then your file should be called users.xls.

The extension of the imported file, as already mentioned, can be anything.

With mysqlimport you can also load multiple xls or csv files into MySQL at once. In order for the data to reach its destination, the names of the files and database tables, as in the previous example, must also match.

If suddenly the columns in the imported file are not in the same sequence as the columns of the database table, then to clarify their order you need to use the —columns option in the following form:

Mysqlimport –u user_name –p database_name --columns column1, column2, ... name_and_path_to_import_file

Naturally, in my examples I did not consider the full list of mysqlimport parameters, because some of them are very specific and are used extremely rarely in practice.

If you want to familiarize yourself with them, their full list is available here - https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html

Features of loading data into a MySQL database from a dump

If you want the process of importing a large MySQL database to go faster, then you need to create a database dump using special options of the mysqldump command, which I wrote about in my previous article about exporting a MySQL database, the link to which was posted in the text earlier.

Unfortunately, the MySQL database import commands themselves do not have such options.

The only thing is that to increase the speed when loading a large database dump, you can use the following feature.

1. Open the dump file (preferably in file managers, since ordinary editors can simply get overwhelmed by large files).

2. Write the following lines at the beginning of the file:

SET foreign_key_checks = 0; SET UNIQUE_CHECKS = 0; SET AUTOCOMMIT = 0;

Note! They may already be there or commented out (many programs that make dumps can add them automatically)

3. At the end of the file we write the reverse actions:

SET foreign_key_checks = 1; SET UNIQUE_CHECKS = 1; SET AUTOCOMMIT = 1;

By the way, these commands will help not only speed up the import process, but also make it possible.

The fact is that if you have ever looked at the dump file for importing a MySQL database, you might have noticed that the operation of setting the structure of the loaded tables looks like this:

DROP TABLE IF EXISTS `clients`; CREATE TABLE `clients` (...);

Those. a search is performed in the database for a table with the same name as the one being imported, and if one is found, it is deleted and created anew.

And if suddenly an existing table is linked by foreign keys to others, then the entire loading will fail.

Therefore, disabling the check for the existence of foreign keys and others is also an excellent guarantee of the successful completion of the MySQL database import process.

Features of importing csv into MySQL database and other files

When loading data into a MySQL database from text files, you may also need to disable foreign keys.

Moreover, unlike the previous situation, in this case it will not be possible to write directives into the file, because SQL commands in it will not be accepted and executed.

In a previous article about exporting a MySQL database, I already mentioned how to do this using the following operation on the MySQL command line:

SET FOREIGN_KEY_CHECKS=0;

However, I didn't mention there that the MySQL system variable FOREIGN_KEY_CHECKS has two meanings: global and sessional (for the current session).

The global value of MySQL variables is valid for any action on the MySQL server until it is restarted. Then the values of the variables will be reset and they will be assigned default values.

The session value of the MySQL system variable is set only for the duration of the user's session with the MySQL server. A session or session begins when a client connects to the server, at which time it is assigned a unique connection id, and ends when disconnected from the server, which can happen at any time (for example, due to a timeout).

Why did I decide to remember this?

Because when executing commands to load a file into a MySQL database through the server console, without going into the MySQL Shell, I discovered that disabling foreign key checking using the previously described method does not work.

The console still showed an error message caused by the presence of foreign keys in the table.

And it arose for the reason that the above command disabled the check for the existence of foreign keys within the session, and not globally, which, in addition to the indicated method, can also be performed as follows:

SET SESSION variable_name = variable_value; SET @@session.variable_name = variable_value; SET @@variable_name = variable_value;

In the above commands, the variable is clearly marked as session.

And, since I was loading a csv file into a MySQL table through the server console, without a direct connection to the MySQL server, a session was not created within which my session variable value would work.

I ended up setting FOREIGN_KEY_CHECKS to global and the import was successful.

You can do this in one of the following ways:

SET GLOBAL variable_name = variable_value; SET @@global.variable_name = variable_value;

After changing the values, it's a good idea to review the variable's values to verify that the changes took effect. To display session and global values simultaneously, use the following command:

SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;

This concludes today’s article on importing a MySQL database. Share your impressions and your own developments in the comments. I think that many will be interested in your experience.

See you again! 🙂

P.S.: if you need a website or need to make changes to an existing one, but there is no time or desire for this, I can offer my services.
More than 5 years of experience professional website development. Work with PHP, OpenCart, WordPress, Laravel, Yii, MySQL, PostgreSQL, JavaScript, React, Angular and other web development technologies.
Experience in developing projects at various levels: landing pages, corporate websites, Online stores, CRM, portals. Including support and development HighLoad projects. Send your applications by email [email protected].

Databases (or abbreviated as DB) on most sites have a size (weight) of several tens of MB (megabytes). The initial size of the database at the start of different projects depends on the structure of the site itself (CMS), but then their weight increases with each appearance of a new record.

The records may include information about user registration, comments, personal messages, products, news and other data stored in the site database.

I had the opportunity to work with several sites whose database size exceeded 500 MB (magabytes), and on some even reached 2 GB (gigabytes). Since databases of this size are difficult to transfer using standard methods (due to hosting and browser limitations), I will share with you several working methods that will help you solve such problems (migration of large databases).

Export (download) large MySQL databases via Sypex Dumper

So, let’s begin our consideration of solving the problems at hand with an easier option, namely, with a ready-made solution.

"Sypex Dumper" is a software product written in PHP that allows you to work with databases without using phpMyAdmin. The advantages of Sypex Dumper include:

Multilingual (supports many languages).
There is a free version of the script (for our purposes this is quite enough).
High speed of task completion.
Working with large databases.
Convenient and intuitive interface.
And many other interesting “chips”.

This is what we will use to download (transfer) large databases.

To export (receive, download) the database of a specific site of yours for further use, you need to do the following.

1. free from our website.

2. sxd in a way convenient for you.

3. Next, go to the address (Where your_site.ru To come in».

4. On the page that opens (if authorization is successful), click once on the section “ Export» in the top menu. If several databases are available to the user, select the one that we will export (download) from the drop-down list. You can leave the rest of the settings unchanged and click on the “ Execute».

I want to note, that you can export not the entire database, but only some of its tables as needed.

5. After the process of saving the database has finished (you can understand this by the progress bar), you can download the database you need by clicking on the appropriate button.

In addition, all exported databases will be stored on your website along the way /sxd/backup/. If saving the database fails, check that the folder backup The write permissions are 777.

This completes the export (download) of the database.

Import (download) of large MySQL databases via Sypex Dumper

Above, we explained to you how to obtain the necessary database; now you need to transfer (import) it to another project, and to do this we do the following.

1. free from our website.

2. Unpack the archive and upload the folder to the website sxd in a way convenient for you.

3. To folder /sxd/backup/ load the previously received (downloaded) database.

4. Next go to the address http://your_site.ru/sxd/index.php(Where your_site.ru– the domain of your website), after which a data entry form will open in front of you. In it you indicate the data of the user who has rights to manage the database you need and click “ To come in».

5. On the page that opens (if authorization is successful), click once on the section “ Import» in the top menu. If several databases are available to the user, select the one into which we will import (load) data from the drop-down list. You can leave the remaining settings unchanged and click on the “ Execute».

I want to note that you can import not the entire database, but only some of its tables as needed.

6. After the process of importing (loading) the database is completed (you can understand this by the progress bar), the task can be considered completed.

Export (download) large MySQL databases via SSH terminal

SSH is a network protocol that allows you to remotely (via special commands) manage a system or server. In Windows, there are many programs for working with this protocol, the most popular of which is “PuTTY”.

On some hosting sites, such as, for example, there is a built-in Terminal right in the control panel. Let's not go far and consider the problem described in the title using his example. It is worth noting that the operations described below can be done in a separate SSH client.

1. Launch the Terminal. It looks like this:

2. If you connect to the server through a third-party program, log in to it by entering the appropriate data (you can get it in your hosting control panel or from your hosting provider).

second- This:

Mysqldump -u USERNAME -p DATABASE > backup.sql

directly the export itself, where:

USERNAME– login of the user who has access to the database.

DATABASE– the name of the database we want to export.

backup.sql– the name of the file in which the database will be saved and the path relative to . With this design, the database will be saved to the root of the hosting.

4. in the third stepEnter" on keyboard. note

Once the server is ready to accept commands via SSH again, this will mean that the database export is completed and you can download it via FTP or through the hosting file manager.

The export (and import) process is not broadcast in SSH, and if your database is quite large, be patient, as you may receive a response from the server after more than 20 minutes.

Import (download) of large MySQL databases via SSH terminal

We have already found out what SSH is above, and now let’s start looking at how to import a previously downloaded database into another project.

1. To the root of your hosting, download the previously downloaded database in a convenient way for you.

2. Connect to your hosting/server via SSH.

so we definitely go to the root of the hosting, and second- This:

Thus, we get a complete list of files and directories of the current directory. Let's make sure that our previously downloaded database is among them.

4. If everything is fine and the base is in place, enter the final command:

Mysql -u USERNAME -p DATABASE< backup.sql

USERNAME– login of the user who has access to the database.

DATABASE– the name of the database into which we will import data.

backup.sql– the name of the file that will be downloaded and the path relative to . With this design, the database will be imported from the hosting root.

5. After entering the command, you will be prompted to enter the password for the user you specified in the fourth step. Enter your password and click " Enter" on keyboard. note that entering a password in the SSH terminal is not shown, that is, whether you enter the password or paste it, there will always be an empty space in its place.

After this, when the server is again ready to accept commands via SSH, this will mean that the database import is completed and you can continue working on the project.

When you just start creating a website, you usually do it on a local server. When it is ready, it will need to be moved to a remote server. Copying files is not difficult, but here's how import database to a remote server? Just about how to import a database into PHPMyAdmin, I will explain to you in this article.

There are many ways database import However, I’ll tell you what I think is the simplest one, and the one I use myself.

Step 1

The first thing you need to do is export database from your current location (specifically a local server). Our goal is to get SQL query our database. To do this you need to do the following:

Step 2

The second and final step is to do SQL query, which you copied, to PHPMyAdmin, which is located on the server where you need import database. To do this, follow these steps:

As a result, all your tables with all records will be created on the new server.

As you can see, the process exporting and importing a database in PHPMyAdmin simplified to a minimum, so there will be no problems with this.

Finally, I would like to give you one more piece of advice. The fact is that very often there is a situation when you need do not import the entire database, but, for example, only one table. Then the principle is absolutely the same, only when exporting you need to select not only the database, but also the table to export. And then again in the top menu click on " Export". Then everything is the same database import.

If you have backed up or exported your database to an SQL file, you can import it into one of your hosting account's MySQL databases via phpMyAdmin.

Note. There should not be a CREATE DATABASE line in a MySQL database. Otherwise, the import may fail.

The phpMyAdmin interface allows you to import 8 MB of data at a time. If you need to import a larger file, split it into several 8 MB parts.

Attention! If you are importing a database for managed WordPress hosting to keep your website running smoothly.

Importing SQL files into MySQL databases using phpMyAdmin

After this, the SQL file will run and update the database you selected in the SQL file. Restoring the database may take several minutes.

Note. If you receive the error message Script timeout passed, if you want to finish import, please resubmit same file and import will resume, you can immediately select the same file and resume the process.

I am often asked how to import a large MySQL database onto a server. It is known that phpMyAdmin by default has restrictions on the size of the imported database.

If your dump is not much larger than the acceptable limit, you can split it into several parts and import it in several passes. This is justified if, for example, the limit is 2 MB, and your database is 5-10 MB in size. It is clear that “cutting” a 100MB database into 50 parts is a rather labor-intensive and time-consuming process.

There are several options for solving this problem.

Editing the web server config

On VDS/VPS there are no particular problems with this, you just need to correct the config. Namely, in php.ini, increase the allowed maximum values for files uploaded to the server, the maximum size for files transferred using the POST method:

Post_max_size = 2000M upload_max_filesize = 2000M

In addition, if your database is very large, you should increase the maximum allowed script execution time.

Max_execution_time = 32000 max_input_time = 32000

And just in case, you can increase the size of the allowed amount of RAM:

Memory_limit = 512M

After making changes, be sure to restart the web server.

It is clear that this method is not suitable for virtual hosting, because... it does not imply the ability to edit configs.

Sypex Dumper

You can use third-party software. And the first application that is worth paying attention to is Sypex Dumper.

Having used it for the first time many years ago and appreciating all its capabilities and advantages, I can safely label it a “Must Have”. Sypex Dumper is a PHP server application that does not require installation. It is enough to copy it, for example, to the root of your site in the sxd directory and call it in the browser: http://Your_Site/sxd/. It is worth noting that you should first place a dump of your database in the backup directory. After initializing the script, you will see the authorization panel for connecting to the database. Enter your username and password. Host and port are optional only if they are specific.

After authorization, you can go directly to importing the database. In field "Database" the database to which you are connected will be selected, and in the field "File" you will see the dump you previously uploaded to the Backup directory.

For most cases, no additional settings are needed anymore and you can safely start the import by clicking on the “Run” button. Import, depending on the size of the database and your Internet connection speed, may take some time. During import, you can see which tables are currently being imported into the database. When the script completes, you will see the execution log. It looks something like this:

That's all, actually - the database is imported!

Console

We will not consider importing through the console. I think people who use the console without me know how to import any database. But it is better for ordinary users without special training not to go there. Since executing some commands can lead to serious consequences, including the complete crash of the server.

Finally

I do not presume to say that Sydex Dumper is the only and correct solution. There are other more elegant methods that require the user to have certain knowledge and appropriate access to the server settings.

But in a shared hosting environment, Sydex Dumper will certainly be your indispensable assistant.

Subscribe to my telegram and be the first to receive new materials, including those not on the site.