When you just start creating a website, you usually do it on a local server. When it is ready, it will need to be moved to remote server. Copying files is not difficult, but here's how import database to a remote server? Just about how to import a database into PHPMyAdmin, I will explain to you in this article.

There are many ways database import However, I’ll tell you what I think is the simplest one, and the one I use myself.

Step 1

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

Step 2

The second and final step is to do SQL query, which you copied, to PHPMyAdmin, which is located on the server where you need import database. To do this, follow these steps:

As a result, all your tables with all records will be created on the new server.

As you can see, the process exporting and importing a database in PHPMyAdmin simplified to a minimum, so there will be no problems with this.

Finally, I would like to give you one more piece of advice. The fact is that very often there is a situation when you need do not import the entire database, but, for example, only one table. Then the principle is absolutely the same, only when exporting you need to select not only the database, but also the table to export. And then again in top menu click on " Export". Then everything is the same database import.

Good afternoon friends, today we will learn how to make... Why is this needed, you may ask. First of all database export must be done periodically in order to emergency situations You have not lost any information that is important to you. The export will be a small file in which all information about the database will be stored. For database export you need to go to PHPMyAdmin and click on the database you are interested in. After this, 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 quick way export, and also indicate in the format SQL. After that you can click ok. A window will appear in front of you in which you will be asked to save the file.

You save the file in the location you need, the main thing is to remember where you saved it, because it is very important to us.
As for the normal export method. You can also use it if you need it, there are many additional 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 this setting, You can watch it. But we will not go deeper into this setting.
After you save the file on your computer, I will ask you to delete the database. I won’t explain to you how to do this, because we have already gone through this. Don’t be afraid to delete, we will return everything to its place with you.
It's time to get busy database import. Go to the import menu.

We select an overview of your computer and indicate the path to our file. And click ok. As you can see, an error has appeared in front of you. Don’t be alarmed, the whole point is that we did not import the database itself, but only all its tables. Therefore, first create a database, go into it and click the import button, having done all of the above. By pressing the ok button, everything will work out, and if you did everything correctly, no errors should appear.

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

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.

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, then go to the database section - Databases MySQL data.

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.

Databases (or abbreviated as DB) on most sites have a size (weight) of 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 appearance of a new record.

The records may include information about user registration, comments, personal messages, products, news and other data stored in the site database.

I had the opportunity to work with several sites whose database size exceeded 500 MB (magabytes), 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 limitations), I will share with you several working methods that will help you solve such problems (migration of large databases).

Export (download) large MySQL databases via Sypex Dumper

So, let’s begin our consideration of solving the problems at hand 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 advantages of Sypex Dumper include:

1. Multilingual (supports many languages).
2. Eat free version script (this is quite enough for our tasks).
3. High speed of task completion.
4. Working with large databases.
5. Convenient and intuitive interface.
6. And many other interesting “chips”.

This is what we will use to download (transfer) large databases.

To export (receive, download) the database of a specific site of yours for 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 the address (Where your_site.ru To come in».

4. On the page that opens (if authorization is successful), click once 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. You can leave the rest of the settings unchanged and click on the “ Execute».

I want to note, that you can export not the entire database, but only some of its tables as needed.

5. After the process of saving the database has finished (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 website along the way /sxd/backup/. If saving the database fails, check that the folder backup The write permissions are 777.

This completes the export (download) of the database.

Import (download) of large MySQL databases via Sypex Dumper

Above, we explained to you how to obtain the necessary database; now you need to transfer (import) it to another project, and to do this we do the following.

1. free from our website.

2. Unpack the archive and upload the folder to the website sxd in a way convenient for you.

3. To folder /sxd/backup/ load the previously received (downloaded) database.

4. Next go to the address http://your_site.ru/sxd/index.php(Where your_site.ru– the domain of your website), after which a data entry form will open in front of you. In it you indicate the data of the user who has rights to manage the database you need and click “ To come in».

5. On the page that opens (if authorization is successful), click once 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. You can leave the remaining settings unchanged and click on the “ Execute».

I want to note that you can import not the entire database, but only some of its tables as needed.

6. After the process of importing (loading) the database is completed (you can understand this by the progress bar), the task can be considered completed.

Export (download) large MySQL databases via SSH terminal

SSH - network protocol, which allows you to remotely (via special commands) control a system or server. In Windows, there are many programs for working with this protocol, the most popular of which is “PuTTY”.

On some hosting sites, such as, for example, there is a built-in Terminal right in the control panel. Let's not go far and consider the problem described in the title using his example. It is worth noting that the operations described below can be done in a separate SSH client.

1. Launch the Terminal. It looks like this:

2. If you connect to the server via third party program– log in to it by entering the appropriate data (you can get it in your 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 we want to export.

backup.sql– the name of the file in which the database will be saved and the path relative to . With this design, the database will be saved to the root of the hosting.

4. in the third stepEnter" on keyboard. note

Once 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 via file manager hosting.

The export (and import) process is not broadcast in SSH, and if your database is quite large, be patient, as you may receive a response from the server after more than 20 minutes.

Import (download) of large MySQL databases via SSH terminal

We have already found out what SSH is above, and now let’s start looking at how to import a previously downloaded database into another project.

1. To 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 root of the hosting, and second- This:

So we get full list files and directories of the current directory. Let's make sure that our previously downloaded database is among them.

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

backup.sql– the name of the file that will be downloaded 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 for the user you specified in the fourth step. Enter your password and click " Enter" on keyboard. note that entering a password in the SSH terminal is not shown, that is, whether you enter the password or paste it, there will always be an empty space in its place.

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