How to export data to mysql. How to import large MySQL database bypassing phpMyAdmin restrictions

This procedure involves transferring data from one database (A) to another (B). As a rule, database B is hosted (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 do I import a database?

Importing MySQL Database Using SSH

This method is rarely used, but we will describe it. First, fill in the database from which the import will be made 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, substitute:
USERNAME is the name of the database user, for example uhosting_databaseuser;

USERPASSWORD - DB user password, for example Rjkweuik12;

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

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

If you did backup or exported the database to a SQL file, then you can import it into one of the databases MySQL data your hosting account via phpMyAdmin.

Note. The MySQL database must not have a CREATE DATABASE row. V otherwise import may fail.

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

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

Importing SQL files into MySQL databases using phpMyAdmin

After that, the SQL file will run and the update of the database you selected in the SQL file will be performed. Database recovery can take several minutes.

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

Greetings, friends! 🙂

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

In this article I will talk about how to make a dump MySQL databases, as well as to unload data from MySQL to Excel file and csv format.

We'll consider different options fetching information from: creating a dump of one or several databases, exporting data from individual tables and arbitrary results SELECT requests.

And also let's talk about how to output data from the MySQL database in the server console and command line MySQL.

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

Firstly, because there is already enough material on the web on this topic. Moreover, high-quality material, which I am not eager to copy and paste.

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

So if you are not a professional developer or system administrator, for whom the information on working with the console may be useful, and you came only for instructions on exporting the database to phpMyAdmin, you can restrict yourself to reading the information on the link above.

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

This concludes the introductory part and we move on to an overview of the console commands for creating a dump of the MySQL database, which I decided to sort by the amount of saved data: from exporting the entire database to 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 run, allows you to create databases and tables, as well as fill them with information. A dump is needed for those who want to download a MySQL database in order to copy it to another server or within the existing one.

Also, if someone is not in the know, a MySQL database backup is, in fact, its dump, made in a certain period of time, which allows you to restore the structure and data of the database if necessary.

Data export- this is simply the extraction of information from tables in text form for further work with a text or graphic editors.

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

To create a database dump, MySQL has a built-in utility called mysqldump to 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 of 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 user_name -p db_name> path_and_dumpfile_name

This utility can create dumps of the MySQL database only in the form of files with SQL commands, so whatever extension you choose for your file - in any case, its contents will be the same. And do not 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 with all databases on the server, then use the following version of the command:

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

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

Mysqldump -u username -p --databases dbname1, dbname2, ...> 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 suitable only for restoring and copying entire databases.

How to make backups of certain MySQL tables and get their data in a readable form will be discussed further.

Dumping MySQL tables and exporting data

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

Mysqldump -u user_name -p db_name table_name1, table_name2, ...> path_and_dumpfile_name

When calling mysqldump, you can also specify the required tables as the parameter value —Tables, when using which the parameter —Databases will be ignored:

Mysqldump -u username -p --databases dbname1, dbname2 --tables tablename1, tablename2, ...> dumpfile_path and name

The above example will display the following error:

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

As you can see, only the most recent database from the list of specified ones will be used. In principle, this behavior is quite logical, since the specified tables may not appear in all databases.

Ok, we got 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 the means to do this, too.

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

Mysql -u user_name -p db_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 into the MySQL command line.

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

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

Thanks to these constructions, we can not only get the data stored in all fields of the table, but also in specific ones. To do this, it is enough to write the required ones, separated by commas, instead of the wildcards (*) symbol.

As a result, we will get a regular text file at the output, which 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 the usual text editor, no matter what permission you give it when creating.

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

Creating backups and outputting data from MySQL database using queries

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

This is especially often encountered by developers of corporate projects when managers ask them to provide all kinds of statistical data. Or when you need to make a backup of a certain part of the table for its quick recovery.

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

Mysqldump -u username -p dbname table_name --where lookup> path_and_name of dumpfile

As a result, we will get a file with SQL commands for creating a table with its entire structure, which, after creation, will be filled with information selected using the lookup query.

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

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

As you can imagine, 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 🙂

The same action can also be performed while working in the MySQL command line using the following command:

SELECT * FROM db_table WHERE lookup_query INTO OUTFILE "path_and_file_name";

This command is just intended to create files with the selection 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

At the end of his a short excursion According to mysqldump, I want 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:

For the sake of experiment, I used this option in order to make a 143 MB dump of the MySQL database. The subsequent restore took 59 seconds versus 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 trifle. 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, then the difference will be more significant.

If you come across such a situation, do not forget to pre-pack the MySQL database dump into the archive. Tar.gz is best. Then the recovery will take even less time.

Export data from MySQL to Excel and csv files

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

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

This does not mean that xls is nowhere else but Microsoft Office Excel, will not open. The same OpenOffice confirms the opposite.

But for such an opportunity this support should be present in software product... csv files are readable even in an ordinary text editor such as "Notepad", only this form will not be entirely readable.

To begin with, only results can be exported to xls or csv SQL queries, with which we have learned to work with you earlier, tk. it will be impossible to dump the entire database into one file in one operation.

First, it is not optimal because such a file is unlikely to open with large amounts of information stored in the database. And, secondly, it is not clear how to split information into tables and fields within a file.

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 someone 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 at once to one or several xls files in the console, then write about it in the comments. I think that reading about this will be useful to 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 MySQL database to csv and xls formats directly in the server console using the following commands.

On the Linux systems:

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

Basically, 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 the data in practice, and I have no idea what will come out in the end. I work under Windows now. If you will use this command under Linux - write in the comments, please, about the results of your work. I think the information will be of interest to everyone.

On the Windows:

Unfortunately, in this case, the export of data from MySQL tables to csv using the above command will fail, because Windows, unlike Linux, does not have a built-in console command for working with streams, which is sed in Linux.

Installing it, of course, is possible, but too much trouble. 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 is very simple to launch it in the following way, which I personally tried:

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

This file was opened in Microsoft Office Excel without any problems at all. The only thing is, when you open the screen, a message was displayed with a 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, perhaps, when performing any specific actions in Microsoft Office Excel, problems will arise in the future, I didn’t dig so deeply. At the very least, I didn’t come across anything unusual during normal viewing of the data.

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

In the way described above, you can export the contents of the MySQL database to a csv file, in principle. But then data from different fields of the table will be written in bulk, without separators, which may be poorly displayed in different programs for working with tables, in which they usually work with csv files.

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

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

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

And then I smoothly approached the second method MySQL export data in 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 db_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 along the path you specified when calling, which will open correctly in most modern spreadsheet editors. Just in case, I remind you that to run this command only needed after connecting to 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 do not need separators, since they will interfere with breaking information into cells:

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

However, in practice, not everything is as simple as I described. While executing the command, you may encounter the following error in the console preventing the export from being performed:

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 faced 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, starts the MySQL server in this way.

There are two ways to solve the problem here:

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

The first method seemed too complicated to me, because I would have to dig into the OpenServer configuration, which was not written by me with all the ensuing circumstances 🙂 Therefore, I decided to go the second way. If you are faced with a similar problem - repeat after me.

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

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

Both will result in the value of the global MySQL variable secure_file_priv, which contains the path to the directory through which the MySQL data export and import operations can be carried out (in the future, a link to the 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 since As already mentioned, I use MySQL utilities from the distribution kit included in OpenServer to work in the console. This value indicated that MySQL data export and import operations using the specified commands were closed altogether.

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 saw only 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 the variable secure_file_priv and open the export and import operations, I needed to go to the MySQL configuration file mysql.ini, which is located in the root directory of the MySQL distribution, or it can be accessed in another way if MySQL is included in your WAMP / LAMP / MAMP server build package.

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

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

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

If you don't have it, then write it from scratch in the section (at least I had it located there).

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

After changing the config (any, by the way), do not forget to restart your server or a separate service, the settings of which you edited, if there is such an opportunity for the changes to take effect!

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

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

SELECT * FROM db_table INTO OUTFILE "value_secure_file_priv \ filename.csv";

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

An important point! If you work with MySQL under Windows, then do not forget to change "\" to "/" when specifying the path to the file, otherwise an error with —Secure-file-priv will continue to be displayed anyway.

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

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

Good luck to everyone and see you soon! 🙂

P.S.: if you need a site or need to make edits to an existing one, but there is no time and desire for this, I can offer my services.

More than 5 years of experience professional development sites. Work with the PHP

Good afternoon friends, today we will learn how to do it. What is it for? You can ask a question. Primarily database export must be done periodically so that in emergency situations you do not lose important information for you. The export will be a small file that will store all the information about the database. For database export you need to go to PHPMyAdmin and click on the database you are interested in. After that, you will see all the tables in it and, without going into them, click on the menu button called export. The following page will appear in front of you:

I advise you to choose quick way export, as well as specify the format SQL... Then you can press ok. You will see a window in which you will be offered to save the file.

You save the file in the place you need, the main thing is to remember where you saved it, because it is very important to us.
Concerning the usual way export. You can also use it if you need it, there are many additional settings that you can specify during export. For example, select the required tables from the database, specify the encoding, and much more. If you are interested in this setting, you can see it. But you and I will not delve into this setting.
After you save the file on your computer, I will ask you to delete the database. I will not explain to you how to do this, because we have already gone through this. Do not be afraid to delete, we will return everything to its place with you.
It's time to get busy database import... Go to the import menu.

We select the overview of your computer, and indicate the path to our file. And press ok. As you can see, an error has appeared in front of you. Do not be alarmed, the point is that you and I did not import the database itself, but only all of its tables. Therefore, first create a database, go into it and click the import button, having done all the above. By pressing the ok button, everything will work out for you, and if you did everything correctly, no errors should appear.

As you can see, our table reappeared in its place, and all the data in it was saved. Now you understand what a great opportunity export and import database in PHPMyAdmin... After all, if you lose in one day all your achievements for many years, thanks to this file You can get everything back. On this I say goodbye to you until soon.

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 operations with MySQL data through the console and make a backup of 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 the import operations of the MySQL database. And, importantly, we will continue to do this with the tool of all the hardcore developers - through the console.

If you need instructions on importing a database via phpMyAdmin, then you can find it in the article on. 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 the methods and tools, a few words about what a MySQL database import is, what is it like, and how best to do it?

MySQL database import: what and why?

Importing a MySQL database is an operation during which the database is filled 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 a MySQL database, can be of two types of information stored in the database:

1. the structure of the database, its tables and the data stored in them (in the common people referred to as a database dump);
2. just data stored in a table or collected using SELECT requests.

This article will cover both options.

To recover from dump MySQL a database with its structure and all stored information, as already mentioned, a database dump file is needed, which is a text file with any extension (it can be previously packed into an archive to reduce its size) containing SQL commands to create the database itself and tables, as well as fill them with information.

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

For normal 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 attributes of the table record.

For these purposes, the usual one is also suitable. txt file, the data in which will be divided, or files created in special spreadsheet editors (Microsoft Office Excel, OpenOffice, etc.) with a different extension: xls, csv, odt, etc.

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

Adding Data to MySQL: Tools

As for the tools for importing the MySQL database, I can say that there are as many as three of them today.

I will list them, starting with the lowest-level ones, ending with the high-level ones (in terms of using all kinds of shells and add-ons):

1. Server console and MySQL command line;
2. Scripts written in programming languages ​​that allow writing data to MySQL using language tools;
3. 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 arrangement of the instruments will not raise any questions from anyone. programming language tools, as a rule, work on the basis of MySQL console commands, and programs are based on scripts or work with the MySQL command line directly.

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

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

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

I think everyone who at least once tried to load a dump into a large MySQL database through phpMyAdmin understands what I'm talking about.

These limits are often the reason for errors when importing a MySQL database, which you will never see when using the console.

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

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

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

How to restore MySQL database from dump via console?

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

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

Let's start in order.

So, in order to import a MySQL database dump into an existing repository through, we first need to start 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 at the link above, so if you need a description of them, take them from there, because I don’t want to duplicate them in the second circle.

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

Source path_and_name_dumpfile;

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 dbname< путь_и_имя_файла_дампа

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

In fact, this is the disadvantage this method compared to the previous one, because in the first one it is possible to observe the operations performed with the database during import, while in the second one it is not.

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

On Linux, this can be done as follows:

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

V Windows standard There is no utility for unpacking the archive in the console, so it will need to be installed additionally.

As you can see, importing a MySQL dump through the console is a very simple operation that can be done with one command. So you don't need to be a developer to complete this procedure.

If you suddenly do not know how to start the server console, you can find this information in the article on the MySQL command line, the link to which I already had earlier.

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

Loading data into 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 in the same way, including from xls and csv into MySQL database.

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 the 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 `db_table` COLUMNS TERMINATED BY", "ENCLOSED BY" \ "" LINES TERMINATED BY "\ n";

Do not 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 the MySQL database in the server console without going into the MySQL Shell, the utility will come in handy mysqlimport, included in the MySQL distribution, and the following call to it:

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

The fact is that mysqlimport simply does not physically 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 from Excel tables to MySQL table users then your file should be named users.xls.

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

It is also possible to upload multiple xls or csv files to MySQL using mysqlimport. In order for the data to reach its destination, the names of 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 db_name --columns column1, column2, ... name_and_path_to_import_file

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

If you want to familiarize yourself with them yourself, a complete list of them 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 be faster, then you need to create a database dump using special options mysqldump commands, which I wrote about in my previous article on exporting a MySQL database, the link to which I posted in the text earlier.

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

The only thing, to increase the speed when loading a large dump of the database, you can use the following trick.

1. Open the dump file (preferably in file managers since regular editors can simply boggle with large files).

2. We write the following lines at the beginning of the file:

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

Note! Maybe they are already there or commented out (many programs that make dumps can add them automatically)

3. At the end of the file, 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 into 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 that of the imported one, and if one is found, then it is deleted and recreated.

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

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

Features of importing csv into MySQL database and other files

When loading data into MySQL database from text files it may also be necessary to disable foreign keys.

Moreover, in contrast to the previous situation, in this case, it will not work to write directives into the file, since SQL commands in it will not be accepted and executed.

In a previous article on 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, there I did not mention that the MySQL system variable FOREIGN_KEY_CHECKS has two meanings: global and session (for the current session).

The global value of MySQL variables remains in effect 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 the 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 the client connects to the server, in which it is assigned a unique connection id, and ends when disconnecting from the server, which can happen at any time (for example, by timeout).

Why did I decide to remember this?

Because when executing the commands for loading a file into the MySQL database through the server console, without entering the MySQL Shell, I found that disabling foreign key checking in the above way does not work.

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

And it arose for the reason that the given command disabled the check for the existence of foreign keys within the session, and not globally, which, in addition to the specified way, 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 explicitly marked as a session variable.

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

I ended up setting the global FOREIGN_KEY_CHECKS and the import succeeded.

This can be done in one of the following ways:

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

After changing the values, you can review the values ​​of the variable to verify that the changes have taken effect. To display session and global values, use the following command at the same time:

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.

Until next time! 🙂

P.S.: if you need a site or need to make edits to an existing one, but there is no time and desire for this, I can offer my services.

More than 5 years of experience professional website development. Work with the PHP, Opencart,