Databases (or DB for short) on most sites have a size (weight) equal to several tens of Mb (megabytes). The initial size of the database at the start of different projects depends on the structure of the site itself (CMS), but then their weight increases with each new entry.
Records can be user registration information, comments, personal messages, products, news and other data stored in the site database.
I had a chance to work with several sites, the size of the databases of which exceeded 500 MB (megabytes), and on some even reached 2 GB (gigabytes). Since databases of this size are difficult to transfer using standard methods (due to hosting and browser limits), I will share with you several working methods that will help you in solving such tasks (migration of large databases).
Exporting (downloading) large MySQL databases via Sypex Dumper
So, let's start with you the consideration of solving the tasks set with an easier option, namely, with a ready-made solution.
"Sypex Dumper" is a software product written in PHP that allows you to work with databases without using phpMyAdmin. The benefits of "Sypex Dumper" include:
- Multilingual (supports multiple languages).
- There is a free version of the script (this is quite enough for our tasks).
- High speed of task execution.
- Working with large databases.
- Convenient and clear interface.
- And many other interesting "chips".
We will use them to download (transfer) large databases.
To export (get, download) the database of your specific site for its further use, you need to do the following.
1. free from our website.
2. sxd in a way convenient for you.
3. Next, go to (Where your_site.com To come in».
4. On the page that opens (with successful authorization), click 1 time on the section " Export» in the top menu. If several databases are available to the user, select the one that we will export (download) from the drop-down list. The remaining settings can be left unchanged and click on the " Run».
I want to note that you can not export the entire database, but only some of its tables if necessary.
5. After the process of saving the database is over (you can understand this by the progress bar), you can download the database you need by clicking on the appropriate button.
In addition, all exported databases will be stored on your site along the way. /sxd/backup/. If saving the database fails, check that the folder backup write permissions are 777.
This completes the export (download) of the database.
Importing (loading) large MySQL databases via Sypex Dumper
Above, we discussed with you a way to obtain the necessary database, now you need to transfer (import) it to another project, and for this we do the following.
1. free from our website.
2. Unzip the archive and upload the folder to the site sxd in a way convenient for you.
3. To folder /sxd/backup/ load the previously obtained (downloaded) database.
4. Next go to http://your_site.ru/sxd/index.php(Where your_site.com– the domain of your site), after which a form for entering data will open in front of you. In it, you specify the data of the user who has the rights to manage the database you need and click " To come in».
5. On the page that opens (with successful authorization), click 1 time on the section " Import» in the top menu. If several databases are available to the user, select the one into which we will import (load) data from the drop-down list. The remaining settings can be left unchanged and click on the " Run».
I want to note that you can import not the entire database, but only some of its tables if necessary.
6. After the process of importing (loading) the database has ended (you can understand this by the progress bar), the task can be considered completed.
Exporting (downloading) large MySQL databases via SSH terminal
SSH is a network protocol that allows you to remotely (through special commands) manage a system or server. In Windows, there are many programs for working with this protocol, the most popular of which is PuTTY.
On some hostings, such as, for example, there is a built-in Terminal right in the control panel. We will not go far and consider the task described in the title using his example. It is worth noting that the operations described below can also be done in a separate SSH client.
1. We start the Terminal. On it looks like this:
2. If you connect to the server through a third-party program, log in to it by entering the appropriate data (you can get them in the hosting control panel or from your hosting provider).
second- This:
mysqldump -u USERNAME -p DATABASE > backup.sql
directly the export itself, where:
USERNAME– login of the user who has access to the database.
DATABASE– the name of the database that we want to export.
backup.sql– the name of the file where the database will be saved and the path relative to . With this design, the database will be saved to the hosting root.
4. in the third stepEnter" on keyboard. note
After the server is ready to accept commands via SSH again, this will mean that the database export is completed and you can download it via FTP or through the hosting file manager.
The process of exporting (and importing) to SSH is not broadcast, and if your database is quite large, be patient, as you can get a response from the server after more than 20 minutes.
Importing (uploading) large MySQL databases via SSH terminal
What SSH is, we have already found out above, and now we will begin to consider how to import a previously downloaded database into another project.
1. at the root of your hosting, download the previously downloaded database in a convenient way for you.
2. Connect to your hosting/server via SSH.
so we definitely go to the hosting root, and second- This:
Thus, we get a complete list of files and directories in the current directory. We look to include our previously loaded database.
4. If everything is fine and the base is in place, enter the final command:
mysql -u USERNAME -p DATABASE< backup.sql
USERNAME– login of the user who has access to the database.
DATABASE– the name of the database into which we will import the data.
backup.sql- the name of the file to be loaded, and the path relative to . With this design, the database will be imported from the hosting root.
5. After entering the command, you will be prompted to enter the password from the user you specified in the fourth step. Enter your password and click " Enter" on keyboard. note that the password entry in the SSH terminal is not shown, that is, you enter the password or paste it - there will always be an empty space in its place.
After that, when the server is again ready to accept commands via SSH, this will mean that the database import is completed and you can continue working on the project.
Good afternoon friends, today we will learn how to do. What is it for, you can ask a question. First of all 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 a quick export method, and also 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.
As for the usual way of exporting. You can also use it if you need it, there are many additional settings that you can set 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 developments for many years, thanks to this file you can return everything. With that, I say goodbye to you soon.
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:
- the structure of the database, its tables and the data stored in them (colloquially referred to as a database dump);
- just data stored in a table or collected using SELECT requests.
This article will cover both options.
To restore a database from a MySQL dump 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 for creating 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, a regular txt file is also suitable, the data in which will be separated, or files created in special spreadsheet editors (Microsoft Office Excel, OpenOffice, etc.) with a different extension: xls, csv, odt, etc.
These formats are even preferable, 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 tools for importing the MySQL database, 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):
- Server console and MySQL command line;
- Scripts written in programming languages that allow you to write data to MySQL using language tools;
- 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, as a rule, 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 the Web server and the programs themselves (which, by the way, cannot always be changed).
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:
- using a command on the MySQL command line;
- 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.
Strictly speaking, this is the disadvantage of this method compared to the previous one, tk. 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
In Windows, there is no standard 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:
mysqlimport -u username -p database_name name_and_path_to_import_fileThis 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 table to 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 the full list of 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 the special options of the mysqldump command, which I wrote about in my previous article on exporting a MySQL database, a link to which was placed in the text earlier.
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.
Therefore, disabling the check for the existence of foreign keys and others is also an excellent 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 a MySQL database from text files, it may also be necessary 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 the specified method, can also be performed as follows:
SET SESSION variable_name = variable_value; SET @@session.variable_name = variable_value; SET @@variable_name = variable_value;
In the above commands, the variable is explicitly marked as a session variable.
And, since I loaded the csv file into the MySQL table through the server console, without a direct connection to the MySQL server, the session was not created, within which my session value of the variable would work.
I ended up setting the global 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 PHP, opencart,
Greetings, friends! 🙂
Today I decided to continue talking 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 the MySQL database, as well as upload data from MySQL to an Excel file and csv format.
We will consider various options for extracting information from: creating a dump of one and several databases, exporting data from separate tables and the results of arbitrary SELECT requests.
And also let's talk about how to display data from the 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 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 might find useful information about working with the console, and you only came 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 a set of SQL commands that, 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 a regular 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 restoration.
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 my brief tour of 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:
mysqldump -u username -h MySQL_server_host_or_IP -p --no-autocommit --opt database_name > path_and_dump_file_name;For the sake of experiment, I used this option 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 Office Excel, which works only under Windows, while csv files are more universal and operations with them are possible in many editors.
This does not mean that xls will not open anywhere except Microsoft Office Excel. The same OpenOffice confirms the opposite.
But for this possibility, this support must be present in the 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, you can only export the results of SQL queries to xls or csv, which we 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 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 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. 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 for working 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 along the path you specified when calling it, 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 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 for working in the console I use the MySQL distribution kit included in the WAMP OpenServer kit, which, in turn, starts the MySQL server in this way.
There are two ways to solve the problem here:
- Change MySQL server startup options
- Change path to destination MySQL export file
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 on social networks or financially using the form below, so that you can pay for 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 website development. Work with PHP
