We dump the MySQL database and export data to the console. How to export (download) and import (upload) large MySQL databases? Importing a MySQL Database Using SSH

When migrating a site from local server for hosting, one of the mandatory procedures is the export/import of the 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 the web interface PHPMyAdmin version 3.2.3 for hosting.

The first thing to do is start a local server, in this case Denwer. After launching Denwer, you need to open your browser and enter 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 we need to select the database that we will export, in my case it is a database called Mybd. You can select a database by clicking on it on the left side of the browser window, where it says “Please select a database” (Fig. 2).

Fig.2

After selecting the desired database, a window with the database structure will open. There is an item in the top menu "Export" with the help of which we will export the database to a computer and then import it to the hosting. And so, go to the “Export” item (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 database tables by clicking on the item "Select All" and check the box next to the item SQL, this item is responsible for the type of file that will be exported. Also, you need to check the box next to "Save as file", which will ensure that the database is saved in a file. There is no need to change any other settings, just press the button "Go".

Fig.4

Now, if you did everything correctly, the database file should download to your computer. Using this file we will import the database to the hosting.

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

Fig.5

In this article we will consider two questions - how you can import databases and how to solve the problem with importing large databases by changing the settings in php.ini.

As you know, data backup is a guarantee that you will be able to restore your website if suddenly, for some reason, the operation of the site is disrupted. How to restore a website from a backup? There are several ways, we will look at how you can import a mysql database into phpMyAdmin. Before you begin the database import process, you need to delete all tables from the database into which you are going to import files.

To delete tables, log in to home page phpMyAdmin and do the following:

2 - click Mark all;
3 - select Delete from the drop-down menu and click OK,

Now you can start importing the mysql database. To do this, follow these steps:

1 - select the desired database;
2 - open the Import tab in the top menu;
3 - find through Browse backup copy with the database and click OK.

If you did everything correctly, a message indicating that the import was successful will appear in the phpMyAdmin window.

Importing large mysql databases

Webmasters often encounter the problem of importing large mysql databases. The problem is due to the fact that in PHPMyAdmin, downloading a database dump is limited by the size of the uploaded file, which by default should not exceed 2MB (2,048 KB). When trying to download a dump of a large database, an error message appears: “The download file size is probably too large...” etc.

Let's look at one way to import a dump of a large database. The import file size limit is determined by the PHP configuration. PHP configuration settings are specified in the php.ini file. This means that to import large databases, we will need to change the maximum size of uploaded files in the php.ini configuration file.

Where is the php.ini file located? You can calculate the path to the php.ini file as follows:

• Create an info.php file and place the following code in it;
• Load the pops this file to the root folder of the site (www, public_html);
• Enter the address http://your_site/info.php into the address bar of your browser;
• In the window that opens, find Loaded Configuration File, where the path to the file will be indicated;

To import a large mysql database, you need to change the php.ini directives in PHPMyAdmin.

The php.ini file has been found, now all that remains is to edit it, or rather, change several directives in the file as shown below:

Maximum time (sec) to fully load the script:
max_execution_time = 60
The maximum time (sec) specified for the script to receive all data:
max_input_time = 90
The maximum amount of memory that the script is allowed to use:
memory_limit = 128M
Maximum allowed size of uploaded files:
upload_max_filesize = 200M
The maximum size of transferred data that PHP will accept:
post_max_size = 180M

By changing the above directives in php.ini, we were able to import databases up to 180 M through PHPMyAdmin.

To import a database dump you can also use free version Sypex Dumper programs. The dumper is optimized for maximum operating speed, as well as working with databases of about 800 megabytes.

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

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

There are several options for solving this problem.

Editing the web server config

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

Post_max_size = 2000M upload_max_filesize = 2000M

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

Max_execution_time = 32000 max_input_time = 32000

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

Memory_limit = 512M

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

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

Sypex Dumper

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

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

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

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

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

Console

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

Finally

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

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

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

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 unless you are a professional developer or system administrator, who may find information about working with the console useful, and you only came for instructions on how to export the database to phpMyAdmin, then 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 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 request to the required database and display the result in the console without going into command line MySQL.

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 normal 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:

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 case given volume 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 program Office Excel, which only works on Windows, and csv file s are more universal and operations with them are possible in many editors.

This does not mean that xls is anywhere except Microsoft Office Excel won't open. The same OpenOffice confirms the opposite.

But for this to be possible, this support must be present in 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 only results can be exported to xls or csv SQL queries, 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 launch options MySQL servers
• 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 on 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 in social media 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

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

Second and last step is the execution 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.