How to export data to mysql. Export Database (PHPMyAdmin)

When transferring a site from a local server to a hosting, one of the mandatory procedures is the export / import of a database (database). Therefore, in this article I will try to describe in as much detail as possible the process of exporting and importing a database from a local server, that is, from a web interface. PHPMyAdmin version 3.2.3 for hosting.

The first thing to do is start a local server, in this case Denwer. After Launch Denwer you need to open a browser and write in the browser line: "http://localhost/tools/phpmyadmin", after which a window will open in front of you(Fig.1)with previously created databases.

Fig.1

Next, you need to select the database that we will export, in my case it is a database called Mybd. The database can be selected by clicking on it in the left side of the browser window, where it says "Please select a database" (Fig.2).

Fig.2

After selecting the required database, a window with the database structure will open. The top menu has an item Export with which we will export the database to a computer, in order to then import it to the hosting. And so, go to the item "Export" (Fig.3).

Fig.3

In the window that opens, you need to make some measurements (Fig.4), namely: in the "Export" block, you need to select all the tables of the database by clicking on the item "Select All" and check the box next to SQL, this item is responsible for the type of file that will be exported. Also, check the box next to Save as file, which will ensure that the database is saved in a file. The rest of the settings do not need to be changed, just press the button Go.

Fig.4

Now, if you did everything right, the database file should download to your computer. With the help of this file we will import the database to the hosting.

Also, you need to change the file encoding to UTF-8 without BOM, I use a text editor to change the encoding Notepad++(Download ) . With this program, open the database file and in the menu "Encoding" choose "Convert to UTF-8 without BOM" (Fig.5), then save and close.

Fig.5

Greetings, friends! 🙂

Today I decided to continue talking about working with MySQL in the console and pay attention to the database export procedure. MySQL data.

In the article I will talk about how to make a dump MySQL databases, as well as upload data from MySQL to excel file and csv format.

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

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

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 the network on this topic. Moreover, high-quality material, which I do not burn with the desire to copy-paste.

And, secondly, I myself briefly considered 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 may need information about working with the console, and you came only for instructions on exporting the database to phpMyAdmin, then you can limit yourself to reading the information from 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 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 data stored: from exporting the entire database to individual tables and results of arbitrary queries.

Creating a dump of a MySQL database via the console

I want to make a small clarification at the very beginning.

Base dump is a file with SQL set commands, which, when launched, 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 an 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 database structure and data if necessary.

Data export- it's just 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, 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 from a specific database in the MySQL console to transfer it to another server or internal copy, 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 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 creation of the file is possible.

If suddenly you need to make a dump with 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 need to call mysqldump with the following options:

mysqldump -u username -p --databases database_name1, database_name2, ... > path_and_dump_file_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.

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

Dumping a MySQL table 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 username -p database_name table_name1, table_name2, ... > path_and_dump_file_name

Even when calling mysqldump, you can specify the required tables as a parameter value --tables, when using which the parameter --databases will be ignored:

mysqldump -u username -p --databases database_name1, database_name2 --tables table_name1, table_name2, ... > path_and_dump_file_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 of those specified will be used. In principle, this behavior is quite logical, because. all databases may not contain the specified tables.

Okay, we've got a dump of the 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 tools for that too.

The option to call the utility will help us achieve our goals. 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 them 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 get the data stored in all fields of the table, but also in specific ones. To do this, instead of the wildcards (*) character, it is enough to register the required ones separated by a comma.

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 normal text editor, no matter what resolution you give it when creating it.

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 extracting 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 need to select only some data from the table.

Developers of corporate projects are especially often faced with this when managers ask them to provide all sorts 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 should be called like this:

Mysqldump -u username -p dbname table_name --where "lookup" > path_and_dump_file_name

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 a 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 to select all the data in a table, with only a few clarifications:

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

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

Any statistics can be collected 🙂

The same action can also be performed from the MySQL command line with the following command:

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

This command is just designed to create files with the results of the selection. 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 the full 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 brief digression for mysqldump, I want to give an option to call 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 normal call:

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

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 encounter such a situation, then do not forget to first pack the MySQL database dump into an archive. tar.gz is best. Then the recovery will take even less time.

Export data from MySQL to Excel and csv files

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

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

It doesn't mean that xls is nowhere but Microsoft office Excel won't open. The same OpenOffice confirms the opposite.

But for this possibility this support must 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 the results can be exported to xls or csv SQL queries, which we have learned to work with earlier, because the entire database into one file will not be possible to display in one operation.

Firstly, this 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 break information into tables and fields inside the file.

No, it is, of course, possible to do this, but it is unlikely that one command will do this, 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 to one or more xls files in the console at once, then write about it in the comments. I think reading about it will be useful to many.

So, if we are talking about how to export data from MySQL to xls and csv, then you can do it 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

In principle, if absolutely necessary, you can use this command to export MySQL data to an Excel file. But, to be honest, I didn’t deal with data in practice, and what will come out in the end - no idea, because. I work now under 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 the Windows:

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

Install it, of course, you can, but too much trouble. Alternatively, you can also use CygWin is a Linux console emulator for Windows systems.

Well, if you already have it installed. V otherwise exporting data from the MySQL database in the chosen way will bring us too much trouble.

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

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

opened given file in Microsoft Office Excel without any problems at all. The only thing is that when opening a message was displayed warning that the actual format of the file being opened differs from the 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. In the usual view of the data, at least, I did not come across anything unusual.

If you encounter any problems in the process of using the xls file exported from MySQL, 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 the data from different fields of the table will be written in bulk, without separators, which can be poorly displayed in various programs to work with tables, in which they usually work with csv files.

OpenOffice, by the way, doesn't care 🙂 It automatically demarcated 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 it 🙂

Well, the same Microsoft Office Excel displayed all the information corresponding to one record in the table, writing it into one cell without any separators. I think that other spreadsheet 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 accepted by editors.

And here I smoothly approached the second way to export 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 at the path you specified when calling it, which will open correctly in most modern spreadsheet editors. Just in case, I remind you to run given command only needed after connecting to a MySQL database.

This command is also great for exporting MySQL data to xls file for correct display in Microsoft Office Excel. Only in this case, we do not need separators, because they will interfere with the breakdown of 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. During the execution of 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 because 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 package, which, in turn, starts the MySQL server in this way.

There are two ways to solve the problem here:

• Change MySQL server startup options
• 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 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 the article on data import).

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

In my case, this variable had a value set at all NULL, because I, as I already said, use the 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 closed altogether.

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

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

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.

Finally, 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 buffer directory, 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 do not have it, then write it from scratch in the section (at least, I have it 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 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 corrected, if possible, 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, only before the name of the file in 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 "value_secure_file_priv\file_name.csv";

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

Important point! If you are working 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 show up anyway.

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

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 the basic hosting.

Good luck to everyone and see you soon! 🙂

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

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

Good day, colleagues 🙂

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

I have already written articles on how to perform basic actions with MySQL data through the console and make a backup of the MySQL database, as well as exporting 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 with the tool of all the hard developers - through the console.

If you need instructions for importing a database through 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 the ways and tools, a few words about what is MySQL database import, what is it like and what is the best way to do it?

Importing a MySQL database: what and why?

Importing a MySQL database is an operation that populates 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:

1. the structure of the database, its tables and the data stored in them (colloquially 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 MySQL dump database with its structure and all stored information, as already mentioned, you need a database dump file, which is a text file with any extension (it can first be packed into an archive to reduce the size), containing SQL commands to create 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 normal data recovery, such complexities 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 entry attributes.

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

These formats are even preferable, because. when they are created, 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

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

I will list them, starting from the lowest level, ending with the highest level (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 you to write 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 the instruments will not cause any questions, because. Programming language tools usually work on the basis of MySQL console commands, and programs are based on scripts or work directly with MySQL command line.

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 kinds of restrictions set by the settings of programming languages ​​on web server and the programs themselves (which, by the way, are not always possible to change).

Due to this, filling the MySQL database through the console can not only be faster, but also make this operation possible in principle, because scripts and programs tend to abort 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 dump to a large MySQL database using 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.

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

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

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

How to restore a MySQL database from a dump using the 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 via , we first need to run 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 their description, take it from there, because. I don't want to duplicate them in the second round.

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

Source path_and_dump_filename;

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 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 on the database during import, while in the second one, it is not.

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

On Linux, this can be done like this:

Gunzip > [archive_filename.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 via the console is a very simple operation that can be performed with a single command. So you don't have to be a developer to perform this procedure.

If suddenly you do not know how to start the server console, then 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 an entire database. In this case, the dump you upload must contain the operations of creating it and filling it 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 to a 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 again in order.

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

LOAD DATA INFILE "path_and_dump_file_name" 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 builds), 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, we need the utility mysqlimport, which is part of the MySQL distribution, and the following call to it:

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

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 be present in the name of the imported file.

Those. if you want to import from excel spreadsheet in MySQL table users, then your file should be named users.xls.

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

With mysqlimport it is also possible to upload multiple xls or csv files to MySQL at once. In order for the data to get to its destination, the names of the files and tables of the database, 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 username -p database_name --columns column1, column2, ... import_file_name and_path

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

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

Features of loading data into 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, a link to which I posted earlier in the text.

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

The only thing is 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, because ordinary editors can simply die from 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 the imported one, and if one is found, it is deleted and recreated.

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

So disabling the existence check foreign keys and others is also a great 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 MySQL database from text files you may also need to disable foreign keys.

Moreover, unlike the previous situation, in this case it will not work to write directives to the file, because 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, I did not mention there that the MySQL system variable FOREIGN_KEY_CHECKS has two meanings: global and session (for the current session).

The global value of MySQL variables is in effect for any action taken on the MySQL server, up to and including restarting the server. 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, at which it is assigned a unique connection id, and ends when disconnected from the server, which can occur at any time (for example, by 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 entering the MySQL Shell, I found that disabling foreign key checking in the previously described way 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 turned off the check for the existence of foreign keys within the session, and not globally, which, in addition to specified method, can also be done 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 was loading the csv file into a MySQL table through the server console, without a direct connection to MySQL server, then the session was not created, within the framework of which my session value of the variable would work.

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

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, to check that the changes have taken effect, it is not superfluous to review the values ​​of the variable. To display the session and global values ​​at the same time, 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 experience in the comments. I think that many will be interested in your experience.

See you soon! 🙂

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

Over 5 years of experience professional website development. Work with the PHP, opencart,

When you first 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 easy, here's how import database to a remote server? Just about that how to import database in phpmyadmin I will explain to you in this article.

There are many ways database import, however, I will tell you the most, in my opinion, simple, and which I use myself.

Step 1

First of all, you need export database from the current location (in particular, the local server). Our goal is to get SQL query our database. To do this, do the following:

Step 2

The second and final step is to execute SQL query that you have 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 import not the entire database, and, 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 for export. And then again in the top menu click on " Export". Then everything is the same database import.

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

If your dump does not exceed the allowable limit by much, you can split it into several parts and import it in several passes. This is justified if, for example, the limit is 2Mb, and your database is 5-10Mb in size. It is clear that "cutting" a 100MB database into 50 parts is a rather time-consuming and costly process in terms of time resources.

There are several options for solving this problem.

Editing the web server config

There are no special problems with this on VDS / VPS, you just need to correct the config. Namely, in php.ini increase the allowable maximum values ​​for files uploaded to the server, maximum size for files transferred by 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 allowable 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 worth paying attention to is Sypex Dumper.

Having used it for the first time many years ago and having appreciated 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 must first place a dump of your database in the backup directory. After the script is initialized, you will see an authorization panel for connecting to the database. Enter your login and password. Host and port are optional only if they are specific.

After authorization, you can go directly to the import of 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 uploaded earlier to the Backup directory.

For most cases, none additional settings are no longer needed and you can safely start the import by clicking on the "Run" button. The import, depending on the size of the database and your Internet connection speed, may take some time. During the import, you can see exactly which tables are being imported into this moment to the base. Upon completion of the script, you will see the execution log. It looks something like this:

That, in fact, is all - the database is imported!

Console

Import through the console will not be considered. I think people who use the console without me know how to import any database. A ordinary users without special training it is better not to meddle there. Since the execution of some commands can lead to serious consequences, up to a complete server crash.

Finally

I do not presume to say that Sydex Dumper is the only and correct solution. There are other more elegant ways 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 that are not on the site.