Export data from mysql. Importing mysql databases

Useful tips

Greetings, friends! 🙂

Today I decided to continue the conversation about working with MySQL in the console and pay attention to the procedure for exporting a MySQL database.

In this article I will talk about how to dump a MySQL database, as well as upload data from MySQL to an Excel file and csv format.

We will look at various options for retrieving information from: creating a dump of one or several databases, exporting data from individual tables and arbitrary results SELECT requests.

We’ll also talk about how to output data from a MySQL database in the server console and the MySQL command line.

In this article, I will not talk about how to export data using phpMyAdmin and other visual tools.

Firstly, because there is already enough material on this topic on the Internet. Moreover, it’s high-quality material, which I’m not eager to copy-paste.

And, secondly, I myself briefly reviewed the process of outputting information from a MySQL database to an SQL file in one of my articles, where I talked about .

So, if you are not a professional developer or system administrator who might benefit from information about working with the console, and you only came for instructions on how to export the database to phpMyAdmin, you can limit yourself to reading the information at the link above.

I want you to understand me correctly: I do not want to offend you in any way, but I just want you to spend your time with maximum benefit for your business and get what you were looking for.

This concludes the introductory part and we move on to a review of console commands for creating a MySQL database dump, which I decided to sort by the amount of data being saved: starting from exporting the entire database, ending with individual tables and the results of arbitrary queries.

Creating a MySQL database dump via the console

I would like to make a small clarification at the very beginning.

Database dump is a file with a set of SQL commands, which, when launched, allows you to create databases and tables, as well as fill them with information. The dump is needed for those who want to download a MySQL database in order to copy it to another server or within an existing one.

Also, if anyone is not aware, a backup of a MySQL database is, essentially, a dump of it made at a certain period of time, which allows you to restore the structure and data of the database if necessary.

Export data- this is simply extracting information from tables in text form for further work with text or graphic editors.

Therefore, the commands for these actions will be slightly different.

To create a database dump, MySQL has a built-in utility called mysqldump, which must be used outside of the MySQL command line in the server console or other shell.

So, for the simplest and most common option - exporting data from a specific database in the MySQL console to transfer it to another server or internal copying, you need to run the following command:

Mysqldump -u username -p database_name > path_and_dump_file_name

This utility can create MySQL database dumps only in the form of files with SQL commands, so no matter what extension you choose for your file, its contents will be the same in any case. And don’t forget to check the write permissions of the directory in which it will be located before exporting information from MySQL so that the file can be created.

If suddenly you need to make a dump of all the databases on the server, then use the following command option:

Mysqldump -u username -p --all-databases > path_and_dump_file_name

To dump only a few specific databases, you will need to call mysqldump with the following parameters:

Mysqldump -u username -p --databases database_name1, database_name2, ... > path_and_dumpfile_name

As a result, in each case you will receive a MySQL database dump containing commands for creating the structure of the contained tables (fields, their types, indexes, keys, etc.), as well as operations for filling them with data.

This option is only suitable for restoring and copying entire databases.

We will talk further about how to make backups of certain MySQL tables and obtain their data in readable form.

Dumping a MySQL table and exporting data

To create a dump of certain MySQL database tables, we will need the same utility mysqldump, called with the following parameters:

Mysqldump -u username -p database_name table_name1, table_name2, ... > path_and_dumpfile_name

When calling mysqldump, you can specify the required tables as the parameter value --tables, when used the parameter --databases will be ignored:

Mysqldump -u username -p --databases database_name1, database_name2 --tables table_name1, table_name2, ... > path_and_dumpfile_name

The above example will display the following error:

Mysqldump: Got error: 1049: Unknown database "database_name1," when selecting the database

As you can see, only the latest database from the list will be used. In principle, this behavior is quite logical, because The specified tables may not appear in all databases.

Okay, we have received a dump of MySQL database tables. It can be used to restore them or copy them along with the structure.

But what if you just need to get the information stored in them and, preferably, in a readable form so that you can send it to the manager and view it in a regular text or spreadsheet editor? MySQL has tools for this too.

The option of calling the utility will help us achieve our plans mysql from the console with certain parameters:

Mysql -u username -p database_name -e "SELECT * FROM table_name"

This command will allow us to execute a query to the required database and output the result to the console without going to the MySQL command line.

Well, in order not to output data to the console, but to write it to a file, you need to supplement the command as follows:

Mysql -u username -p -e "SELECT * FROM tablename" > path_and_filename

Thanks to these constructions, we can not only obtain data stored in all fields of the table, but also in specific ones. To do this, just replace the wildcards (*) symbol with the required ones, separated by commas.

As a result, the output will be a regular text file that will contain the names of the fields in the form of a header and information on them for all records. It can be opened in a regular text editor, no matter what resolution you give it when creating it.

If you want to export data from a MySQL database in xls or csv format so that the resulting file is displayed correctly in spreadsheet editors, then we will tell you how to do this a little later :)

Creating backups and outputting data from a MySQL database using queries

We talked about how to dump a MySQL database - one or several, as well as individual tables. But sometimes in practice there are cases when you need to export a data set that is not limited to one table. Or you need to select only some data from the table.

Developers of corporate projects especially often encounter this when managers ask them to provide all sorts of statistical data. Or when you need to backup a certain part of the table to quickly restore it.

For backup we will need the same utility mysqldump, which will need to be called like this:

Mysqldump -u user_name -p database_name table_name --where "lookup" > path_and_dump_file_name

As a result, we will receive a file with SQL commands to create a table with its entire structure, which, after creation, will be filled with information selected using a lookup query.

If we just need to get the data stored in one or more tables, then we will need to modify the command used in the previous case when retrieving all the data in the table, only with some clarifications:

Mysql -u user_name -p -e "SELECT * FROM table_name WHERE lookup" > path_and_file_name

As you understand, in addition to the various clarifications specified in the request using the directive WHERE, you can use other SQL constructs: JOIN, UNION etc.

You can collect any statistics you want :)

The same action can also be performed when working on the MySQL command line using the following command:

SELECT * FROM database_table WHERE lookup INTO OUTFILE "path_and_file_name";

This command is specifically designed to create files with sampling results. Moreover, the results can not only be exported to files, but also written to variables, and the output data can be formatted in various ways.

If the above is your case, then you can find a complete list of parameters and options for calling this command here - https://dev.mysql.com/doc/refman/5.7/en/select-into.html

To conclude my brief excursion into mysqldump, I would like to give a variant of calling a command with a list of parameters to create an optimized dump of the MySQL database and tables, restoring the database and individual tables from which will take less time than with a regular call:

mysqldump -u user_name -h host_or_IP_MySQL_server -p --no-autocommit --opt database_name > path_and_dump_file_name;

For the sake of experiment, I used this option to dump a MySQL database of 143 MB in size. The subsequent restore took 59 seconds compared to 1 minute and 3 seconds when the database was restored from a dump made by calling mysqldump without special parameters.

I agree that this is a small thing. But this is only in the case of a given amount of data. If you use this technique when creating a dump larger than 1GB, the difference will be more significant.

If you encounter such a situation, do not forget to first archive the MySQL database dump. The best is tar.gz. Then recovery will take even less time.

Export data from MySQL to Excel and csv files

It was not for nothing that I combined information about outputting information from MySQL into these two formats in one block, because... they are very similar, they are used in approximately the same way (to structure information in the form of tables) and the same commands for export will be called.

As you know, the only significant difference between these formats is that the xls and xlsx extensions have files created in Microsoft Office Excel, which only works under Windows, while csv files are more universal and operations with them are possible in many editors.

This does not mean that xls will not open anywhere except Microsoft Office Excel. The same OpenOffice confirms the opposite.

But for this to be possible, this support must be present in the software product. csv files are readable even in an ordinary text editor like Notepad, but this form will not be entirely readable.

Let me start with the fact that you can only export the results of SQL queries to xls or csv, which we learned to work with earlier, because it will be impossible to output the entire database into one file in one operation.

Firstly, this is not optimal, because... such a file is unlikely to open if there is a large volume of information stored in the database. And, secondly, it is not clear how to split the information inside the file into tables and fields.

No, it is, of course, possible to do this, but it is unlikely to be done with one command and in general it is unlikely that anyone will do this in the console. I think that for these purposes you will need special software or at least a script.

If you suddenly know how you can export information from the entire MySQL database into one or several xls files in the console at once, then write about it in the comments. I think reading about this will be useful for many.

So, if we are talking about how to export data from MySQL to xls and csv, then this can be done directly in the server console through the utility mysql or in, the work with which I introduced you in my previous article.

Let's start in order.

You can export data from a MySQL database to csv and xls formats directly in the server console using the following commands.

On Linux systems:

Mysql -u username -d database_name -p -e "SELECT * FROM database_table;" | sed "s/"/\"/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > path_and_file_name. csv

In principle, if absolutely necessary, you can use this command to export MySQL data to an Excel file. But, to be honest, I haven’t dealt with this in practice and I have no idea what will come out in the end, because... I'm currently working on Windows. If you use this command under Linux, please write in the comments about the results of your work. I think the information will be of interest to everyone.

On Windows:

Unfortunately, exporting data from MySQL tables to csv using the above command will not work in this case, because Windows, unlike Linux, does not have a built-in console command for working with threads, like sed in Linux.

Of course, you can install it, but it’s too much hassle. Alternatively, you can use CygWin— Linux console emulator for Windows systems.

It's good if you already have it installed. Otherwise, exporting data from the MySQL database using the chosen method will bring us too much trouble.

But extracting information into an xls file is as easy as 5 kopecks :) It’s very easy to launch it in the following way, which I tried personally:

Mysql -u username -d database_name -p -e "SELECT * FROM database_table;" > path_and_file_name.xls

This file opened in Microsoft Office Excel without any problems at all. The only thing is that when opening it, a message was displayed on the screen warning that the actual format of the file being opened differs from its specified extension.

But when confirming the action, the document opened without difficulty - all the information was divided into cells in the form in which it was stored in the table itself.

I don’t know, maybe if you perform any specific actions in Microsoft Office Excel, problems will arise in the future; I haven’t dug that deep. When I looked through the data normally, at least, I didn’t see anything unusual.

If you encounter any problems while using the xls file exported from MySQL, either in this program or in others, please let me know in the comments.

Using the method described above, you can export the contents of a MySQL database to a csv file, in principle. But then the data from different fields of the table will be written en masse, without delimiters, which may not be displayed well in various programs for working with tables, which usually work with csv files.

OpenOffice, by the way, doesn’t care :) It automatically delimited the information obtained in the way we exported the contents of the MySQL database to xls. I don’t know how he does it, but I recommend using it :)

Well, the same Microsoft Office Excel displayed all the information corresponding to one record in the table, writing it in one cell without any delimiters. I think other table editors will do the same.

Therefore, when exporting a MySQL database to csv files, you need to do this by separating the information with special characters that are understood by editors.

And then I gradually approached the second method of exporting MySQL data to csv and xls, which is to use the MySQL command line.

So, in order to export MySQL data to a csv file in this way, we need the following command:

SELECT * FROM database_table INTO OUTFILE "path_and_file_name.csv" FIELDS TERMINATED BY "," ENCLOSED BY """ LINES TERMINATED BY "\n";

As a result of its execution, you will receive a csv file in the path you specified when calling, which will open correctly in most modern spreadsheet editors. Just in case, I remind you that you need to run this command only after connecting to the MySQL database.

This command is also great for exporting MySQL data to an xls file for correct display in Microsoft Office Excel. Only in this case we don’t need separators, because they will interfere with dividing information into cells:

SELECT * FROM database_table INTO OUTFILE "path_and_file_name.xls";

However, in practice, not everything is as simple as I described. While running the command, you may encounter the following error in the console that prevents the export from completing:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

It is caused by the fact that your MySQL server was started with the option --secure-file-priv. Personally, I encountered this problem due to the fact that to work in the console I use the MySQL distribution kit included in the WAMP OpenServer kit, which, in turn, launches the MySQL server in this way.

There are two ways to solve the problem:

Change MySQL server startup parameters
Change the path to the final MySQL export file

The first method seemed too complicated to me, because... I would have to delve into the OpenServer configuration, which was not written by me with all the ensuing circumstances 🙂 Therefore, I decided to take the second path. If you encounter a similar problem, repeat after me.

First you need to go to the MySQL command line and run one of the following commands:

SHOW VARIABLES LIKE "secure_file_priv"; SELECT @@GLOBAL.secure_file_priv;

The result of executing both will be the value of the MySQL global variable secure_file_priv, which contains the path to the directory through which MySQL data export and import operations can be performed (in the future, a link to an article on data import).

Those. when using commands LOAD DATA And SELECT... INTO OUTFILE exported and imported files can only be located within this directory.

In my case, this variable was generally set to NULL, because I, as I already said, use MySQL utilities from the distribution included in OpenServer to work in the console. This value indicated that the MySQL data export and import operations using the specified commands were completely closed.

As it turned out later, this is a common situation when using boxed WAMP and MAMP servers.

Unfortunately, in my case it was not possible to use the usual methods of changing the values of MySQL global variables:

SET variable_name = value;

As a result, I only saw the following error in the console:

ERROR 1238 (HY000) at line 1: Variable "secure_file_priv" is a read only variable.

As a result, to change the value of a variable secure_file_priv and open the export and import operations, I needed to go into the MySQL configuration file mysql.ini, which is located in the root directory of the MySQL distribution, or can be accessed in another way if MySQL is included with your WAMP/LAMP/MAMP server build.

By the way, if you want to change the path to the file exchange spool directory, you will need to do the same.

In my case, this variable already existed in the config, only in commented form:

Secure-file-priv = "%dprogdir%\\userdata\\temp"

If you don’t have it, then write it from scratch in the section (at least for me it was located there).

I uncommented it and decided to use it in the form in which it was written. Those. when exporting data from MySQL and importing it back, my files will now be stored in a directory c:\openserver\userdata\temp\.

After changing the config (any one, by the way), do not forget to restart your server or a separate service whose settings you edited, if possible, for the changes to take effect!

To be sure, after restarting the MySQL server, display the variable again secure_file_priv and copy its value to the clipboard.

And now we need to call the command as at the beginning, only before the name of the file into which information from the MySQL database will be saved, write the path stored in the variable we are changing in the following form:

SELECT * FROM database_table INTO OUTFILE "secure_file_priv_value\file_name.csv";

After this, exporting data from MySQL worked in my case.

Important point! If you work with MySQL under Windows, then do not forget to change “\” to “/” when specifying the path to the file, otherwise the error will be --secure-file-priv will still continue to appear.

This concludes the article on how to dump a MySQL database and its tables, as well as output data from MySQL tables into various formats. Write your reviews in the comments and share with everyone your script options that you use most often in practice.

If you liked the article, you can thank the author by reposting the article on social networks or financially using the form below so that you can pay for basic hosting.

Good luck to everyone and 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

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.

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,

This procedure involves transferring data from one database (A) to another (B). As a rule, database B is located on the hosting (or in Denver), and database A is located on the user’s computer and is a file with the sql extension. Database A has another name - Dump.

How to import a database?

Importing a MySQL Database Using SSH

This method is used quite rarely, but we will describe it. First, fill in the database from which you will import it to the hosting provider’s server, where your website files are stored. Next, to import the database, use the command:

mysql -uUSERNAME -pUSERPASSWORD DBNAME< DUMPFILENAME.sql

mysql --user=USERNAME --password=USERPASSWORD DBNAME< DUMPFILENAME.sql

Instead of words written in capital letters we substitute:
USERNAME - database user name, for example uhosting_databaseuser;

USERPASSWORD - database user password, for example Rjkweuik12;

DBNAME - name of the database into which the import will be made, for example uhosting_databasename

DUMPFILENAME - name of the dump file from which data will be imported. Here we also need to specify the path to the database that we uploaded to the hosting provider’s server.