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

How to import a database?

Importing a MySQL Database Using SSH

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

mysql -uUSERNAME -pUSERPASSWORD DBNAME< DUMPFILENAME.sql

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

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

USERPASSWORD - database user password, for example Rjkweuik12;

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

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

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 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 from 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- this is simply extracting information from tables in text form for further work with text or graphic editors.

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

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

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

Mysqldump -u username -p database_name > path_and_dump_file_name

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

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

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

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

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

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

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

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

Dumping a MySQL table and exporting data

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

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

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

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

The above example will display the following error:

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

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

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

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

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

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

This command will allow us to execute a 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 a regular text editor, no matter what resolution you give it when creating it.

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

Creating backups and outputting data from a MySQL database using queries

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

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

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

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

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

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

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

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

You can collect any statistics you want :)

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

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

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

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

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

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

For the sake of experiment I used this option in order 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 under Windows, while csv files 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 amount 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 built-in console command for working with threads, which is 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

Opened this file 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, in which they 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 it by separating the information special characters, perceived by editors.

And then I gradually approached the second method MySQL export data in csv and xls, which involves using 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 to run this command only needed 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 included in the WAMP OpenServer kit, which, in turn, launches the MySQL server in this way.

There are two ways to solve the problem:

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

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

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

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

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

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

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

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

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

SET variable_name = value;

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

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

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

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

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

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

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

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

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

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

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

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

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

Important point! If you work with MySQL 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 development sites. Work with PHP

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

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

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

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

Importing SQL files into MySQL databases using phpMyAdmin

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

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

This lesson covers important issues such as database export And database import MySQL. You may ask, why export databases at all? Typically this is done for a reason Reserve copy database so that it is always at hand, as they say, just in case of emergency. After all, no one is immune from force majeure circumstances, such as hosting accidents, which can lead to data loss. Another example could be an attack by hackers who want to harm your site. In fact, there can be many such examples.

Database import is used when you need to “re-upload” a database to your current hosting, or when moving to another hosting. This is also often done in practice by webmasters.

On our own database export And database import- the tasks are simple. It is worth performing these actions once in order to remember the algorithm for their implementation for the rest of your life. Let's look at how all this is done with examples.

To export a database, you need to go to the hosting admin panel, and then go to the database section - MySQL Databases.

Select the database that we want to export (go to phpMyAdmin). IN in this example the database is called "cl209038".

1) In the first case, select “Fast”, leave the format as SQL and press the “OK” button. As a result, a copy of the database will be downloaded to our computer.

2) In the second case, select “Normal”. A page with database export settings will appear. Select all the tables, select the encoding and compression level, as shown below. The rest, as a rule, does not need to be changed. Click the “OK” button and the database will be exported to our computer.

That's all, as you can see, database export MySQL is not a difficult task.

To import a database, you also need to go to the hosting admin panel and go to the section with MySQL databases. There are two options here - either you need to create a new database, or export to an existing one.

Creating a database will be required, for example, if we are moving the site to another hosting. If we still have the same hosting and the database has already been created, then you can simply select this database and “re-upload” the data into it. You can, of course, delete it, then create a new (empty) one and upload to it.

1) The database already exists. We select a database to import our copy of the database into. The database import menu will appear.

Select the database copy file that you want to import. If necessary, you can change the encoding and format of the database. Next, click the “OK” button. After import, the system will inform us whether everything went correctly or whether any errors occurred during the process. If errors occur, you can try to delete the database on the hosting, then create an empty database and import the database again.

2) Creation of a new database. Go to the hosting admin panel and select the section with MySQL databases. We get into MySQL database management.

Specify the desired database name and password for it. Click the “Create” button. As a result, a new (empty) database should be created - into it we will need to import a previously saved copy of our database.

That's all I wanted to tell you about exporting and importing MySQL databases. If you have any difficulties regarding these questions, write comments on this lesson.

When transferring a site from a local server to hosting, one of the mandatory procedures is export/import of the 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 run local server, in this case it is 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. IN top menu there is a point "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 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