Export and import of data from mysql. Import mysql databases

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 dump the MySQL database, 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.

We will also talk about how to draw data from MySQL databases 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 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 the maximum benefit for the cause and get what you were looking for.

This concludes the introductory part and we move on to an overview of the console commands for creating a dump of the MySQL database, which I decided to sort by the amount of 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 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 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 the usual output text file, which will contain the field names 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 displayed correctly 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 with complete list parameters and options for calling this command you can find 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 to work with streams, which is sed on 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. AT 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

This file opened 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 then I smoothly approached the second method MySQL export data in csv and xls, which is to use the MySQL command line.

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

SELECT * FROM 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 along the path you specified when calling, 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 launch options MySQL servers
• 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 media 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 PHP

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

Note. The MySQL database must not have a CREATE DATABASE line. 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 multiple 8 MB chunks.

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

Importing SQL files into MySQL databases with phpMyAdmin

After that, 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 get an 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 restart the process.

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

I advise you to choose fast way export, as well as specify in the format SQL. After that you can press ok. You will see a window asking you to save the file.

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

We select the overview of your computer, and indicate the path to our file. And press ok. As you can see, you have an error. Do not be alarmed, the thing is that we did not import the database itself, but only all of its tables. Therefore, first create a database, go into it and click the import button, having done all of the above. By clicking the OK button, you will succeed, and if you did everything correctly, no errors should appear.

As you can see, our table reappeared in its place, and all the data in it was saved. Now you understand what a wonderful opportunity database export and import in PHPMyAdmin. After all, if you lose in one day all your achievements over the years, thanks to given file You can return everything. With that, I say goodbye to you soon.

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, 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.

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

Database import is used when it is required to "upload" the database to the current hosting, or when switching to another hosting. This is also often done in practice by webmasters.

By themselves database export and database import- easy tasks. It is worth doing 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 section with databases - MySQL databases.

Select the database you want to export (go to phpMyAdmin). In this example, the database is named "cl209038".

1) In the first case, select - "Quick", leave the format SQL and click the "OK" button. As a result, a copy of the database will be loaded onto our computer.

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

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

To import a database, you also need to go to the hosting admin panel, 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 transfer the site to another hosting. If we have the same hosting and the database has already been created, then you can simply select this database and “reload” the data into it. You can, of course, delete it, then create a new (empty) one and upload it to it.

1) The base is already there. We select the database to import our copy of the database into. The Import to Database menu will appear.

Select the copy file of the database that you want to import. If necessary, you can change the encoding and format of the database. Next, click the "OK" button. After importing, the system will inform us whether everything went correctly or if there were any errors in 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 new base data. We go to the hosting admin panel, select the section with MySQL databases. We get into MySQL database management.

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

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