Transferring a database to another SQL server. How to correctly transfer a MySQL database from an old hosting to a new one

May 16

Recently the question arose of transferring a website from one hosting to another. With sites using only html and css without databases, there should be no difficulties. Dancing with a tambourine begins when you arrive happy owner website with a database. Nowadays there are an overwhelming majority of such sites on the Internet.

We can transfer the site files themselves using regular copying from hosting to hosting

In order to transfer a database from one server to another we need:
1. Download the database dump to your computer (Export operation).

2. Upload the database dump to the hosting server (Import operation).

This can be done several times in various ways. Some CMS even allow you to perform these operations internally. You can also use a database dumper MySQL data. But the most familiar and common method for migrating databases is transfer using database control panel tools, which I would like to consider in this article.

1. Export the database from the server to your computer.

We need to go to the server on which the database that we need to transfer is currently located, and on the left side of the menu in the list of databases, select the one that needs to be transferred.

You need to go to the " Export" and select as " Export method» — Normal — display all possible settings.

After this, you need to pay attention to the fields:
1. In the Tables tab, all tables that need to be imported must be selected. There may be a situation when some tables will not be needed during import to transfer databases. In this case, you can deselect those tables that are not needed by selecting the necessary table names with the Ctrl key on the keyboard. (IMPORTANT! If you are not experienced user databases - it is better to select all tables. )
2. You need to select the “Save output to file” option.
3. Remember the encoding that is set in the field - File encoding.
4. You don’t need to touch the remaining points if you don’t know why they are needed.
5. Click OK, then save the file to your computer.

2. Import the database from your computer to the server.

1. We check the extension of the saved melon database on our computer. It should be '.sql'. If, when saving the database, the option was specified - archive it (zip, gzip, bzip) - you must first extract the database from the archive.
2. Our database dump should not contain queries like « CREATE DATABASE, /*!40101 SET @OLD» . You can verify the absence or presence of such a request by opening the database dump with Notepad or another text editor. If such a request is present, you should delete this line and resave the file. As a rule, it is found in the first 15 lines of the database dump.
3. You need to make sure that no tables are created in the future database. To do this, go to and select your database in the menu on the left in the list of databases. On the left side of the menu you can see the message “No tables found in the database.” If tables are present, you need to delete them.

After completing all these steps, you can safely go to the “Import” tab, in which, by clicking on the “Browse” button, select the saved database dump from your computer. In the “File encoding:” field, select the encoding in which this database was created.

There is no need to make any further settings. Click OK and wait for the database import to finish.

The database was successfully transferred and after that our site will work on the new hosting! (provided that all other operations to transfer the site have already been done).

Hello, dear and respected readers of my blog! Today I would like to tell you about how to transfer a mysql database to hosting.

Let's do without eloquent and lengthy introductions today, let's get straight to the point.

What methods of copying a database to hosting exist?

I can say with confidence that there are definitely many ways to copy. But I don’t want and won’t describe irrational and unrealistically complex procedures, since it doesn’t make any sense (it’s unlikely that there are people lurking among the readers who want to complicate their lives).

Therefore, we will start from simple and accessible options for the masses, including:

Export data using the phpMyAdmin interface through the provider's control panel.
Copying the database using third-party programs.

Which way is better?

I recommend the first one, which is used by professional webmasters. The only thing worth remembering is that the installation algorithm may differ, since each hosting has its own individual control panel.

The second option, although it does not cause difficulties, I do not recommend using it! Trust information about your web resource third party programs really bad idea.

Step-by-step instructions for creating a database on a new hosting

Let's start with the fact that I will not describe the procedure for creating the mysql database itself, since most likely, having decided to import it to hosting, you worried about creating it earlier. This means we are ready to begin implementing the general algorithm for creating a database on hosting, which involves:

Activation of your personal account on rented hosting.
Search the menu for the section “MySQL”, “Database” or a menu with a similar name.
Direct creation of a database on hosting.
To do this, click the “Create” button. We write a new name for the database, bind a user to it (often the hosting provider automatically creates a user, but sometimes this operation must be carried out yourself. Therefore, if this happens, do not forget to activate highest level access rights for this account).

Transferring the MuSQL database from the old hosting to your computer

Now I propose to move on to the step that experienced users call a database dump. More specifically, we will save the current database from the old hosting to our computer.

Note! Dump- This function file, the purpose of which is to simplify the procedure for transferring information from the database.

Procedure:

Importing MySQL data to new hosting

In the same way as in the previous section, go to the administrative panel of the hosting.
We select the database we need (the one we created at the very beginning).
Click the “Import” tab.
Click “Browse” and indicate the location of the storage file previously saved on your computer.
We check that the encoding is similar to the one that was applied when saving the file (point 7 of the previous section).
We leave all other settings unchanged and complete the process of copying data to the new hosting server.

Solving possible problems

Make sure that before you start importing materials to the new hosting, there are no query phrases “CREATE DATABASE” in the storage file. To do this, open it any text program(you can use Notepad or Word Pad) and use Ctrl+F (using a text search) to find these queries, and if found, simply delete them.

I hope everything is clear?! By following the instructions step by step, you should not have any difficulties.

Share useful articles from my blog on social networks with friends, subscribe to updates and see you soon!

Sincerely! Abdullin Ruslan

The algorithm is as follows:

Choose the necessary base data.
Click on the “Export” menu item in top menu.
Determine the export method. Considering that all default settings are saved, you can use the “Quick” option. I choose “Normal” only if I need to compress the file.
Check that all WordPress database tables are selected for transfer.
If the “Normal” option is specified, then you can determine the compression during export.
At the very bottom of the page, click “Ok”.

As a result, the application will create a database dump and offer to save it on your computer. All settings, as you can see, are set initially, and in 99% of cases there is no need to change anything.

The import process is even simpler. Let's say you already have an empty website database created in cPanel, where you want to transfer all the information from the previous one. Procedure:

Go to PhpMyAdmin and select a new database.
In the top menu, click on the “Import” item.
After clicking on the “Choose File” button, select the SQL file to import on your computer.
Click “Ok” at the bottom of the page.

After successful completion of the procedure, you will see a corresponding message, and a list of created tables will be displayed on the right under the name of the new database. That's it for migration wordpress database completed. Then return to the remaining stages, if you have been doing them.

WordPress Database Migration Plugins

In the article about I mentioned a couple of modules that help change domain name(and other information) in the site database. Making changes manually in PhpMyAdmin would be extremely troublesome, and not always safe. I will not review the plugins in detail, but will only present their main advantages.

Important! All three plugins can work with serialized data and make a correct replacement of information in the database (taking into account the length of the string), for example:

s:11:"hello world" will become s:9:"new world"

There may be errors when executing SQL and editing directly through PhpMyAdmin.

Initially, it only allowed making changes to the database, but latest versions the developers have significantly expanded its functions. Now you can also download a dump and restore the database, change the domain, prefix. New interface made it absolutely great. In this article I’ll tell you about the module in a little more detail, although everything here is extremely simple. Downloads - 100k+, rating - 4.4.

This tool can be used not only when migrating a WordPress database, but also the entire site. allows you to transfer media files, plugins, themes. You can also start the process of searching and editing data in the database. Distinctive feature is fast work(so as not to burden the hosting provider), and also there is no need to install additional PHP extensions. The solution even works with PHP v5.2, while the previous one requires a minimum of PHP v5.4. More than 300 thousand downloads, rating - 4.8.

Performs the basic tasks of transferring a database to WordPress: exporting a dump, searching and replacing information, saving SQL files on the computer. More than 200 thousand downloads, rating - 4.7. Free version, in principle, that’s enough, although of course you can expect cooler features in PRO. You will have to import the database through PhpMyAdmin as I described in the first section.

Using the Search and Replace plugin

I came across this module most often, so I’ll look at it in more detail as an example. I think the process of transferring a WordPress database in other options is similar; in extreme cases, watch the video instructions in their descriptions on wordpress.org.

So. after Search and Replace and activation, all its functions are located in the menu item of the same name in the “Tools” section. There are 4 main directions:

Backup Database - creating a backup.
Search & Replace - search and replace information.
Replace Domain URL - change the domain.
SQL Import - import.

In the first and last paragraphs there is only one button “Export/Import”, but, in fact, you have everything you need for a full migration of the WordPress database and website. The only thing you need to remember is that the data is imported into the current database connected to the project.

Go to the Replace Domain URL section. In fact, it provides the ability to replace the old domain with a new one.

This feature will not save you from the remaining steps in the algorithm, but at least it will simplify the task of transferring the database to WordPress. In addition, you can change the table prefix, which is also useful.

The Search & Replace tab helps you replace information in the database.

First of all, you determine the old and new values, then mark the tables where the search and replacement should be performed.
By default, the “Dry Run” option is enabled - this means that the actions will be performed in “test mode”.
If you uncheck the box, you will have 2 options to choose from - import SQL query but to make changes to the database or directly perform this action.

That is, in essence, you can do: 1) a test run of the task, 2) a real data replacement, 3) receive an SQL query, and then implement it through PhpMyAdmin or the 4th tab of the module. Great flexibility!

Total. I hope the information on transferring a database to WordPress is enough for you to carry out this procedure yourself. In principle, there is nothing complicated, but when working with a database you always need to be extremely careful - at a minimum, create backups before starting work. All three plugins do their job well, but I personally like Search and Replace the most - simple, flexible, without unnecessary details.

If you have anything to add about the transfer and migration of a WordPress database or sites, write in the comments.

Sometimes there is a need to transfer a SQL database from one SQL server to another. Typically, the data migration process involves creating a database backup and restoring it on another SQL server. However, it would seem that in such a simple operation various kinds of difficulties can arise. In this article we will try to deal with some of them.

1. If a database with the same name already exists

If, when migrating a database, it is discovered that a database with the same name already exists, or during recovery an error occurs that a database file with the same name already exists, you must manually specify a new database name and/or the folder in which the physical files will be located DB. This can be done in SQL Server Managment Studio by specifying a new database name on the General tab when restoring

and the folder in which the database files will be located (Files tab)

2. Transfer of the Alta-GTD database along with additional ED databases

If you need to transfer the Alta-GTD database along with additional ED databases, then you must:

1. Create a backup copy of the database along with additional databases using the Alta-GTD program. To do this, you need to run Service - SQL Administrator - Backup SQL database data, and then answer affirmatively to the question about the need backup additional ED databases. After the creation operation backup copies additional databases will be completed, the program will display information window with a description of all created backup files. These files, as well as the backup file of the working database, must be transferred to another SQL server.

2. Restore databases from backups. If the server already has a database with the same name, then it must be restored with a different name (see section 1).

3. If the working database was renamed during recovery, then you need to run the script for all additional databases:

UPDATE [Additional_Database_Name].. SET = "_gtd_ed@Main_Database_Name"

4. If one or more additional databases were renamed during recovery, then a script must be executed for each of them

UPDATE [Main_Database_Name].. SET = "New_Additional_Database_Name" WHERE = "Old_Additional_Database_Name"

Our site files are safely uploaded to the hosting. Let's not waste time and reschedule existing base data (DB) from local to server.

To do this, go to PhpMyAdmin. For those who have forgotten where it is, take a look.

We see our database (mebelip) and click on the name.

A window opened in front of us with a lot of data that means absolutely nothing to a beginner. But we don’t need that now.

We simply click on the "Export" tab.

Let me remind you that by default OpenServer opens in the Chrome browser. Therefore, in the lower left corner we see the downloaded file. Remember where you saved it, we will need it soon.

We click on it.

Just for fun, you can see what “secret” information is in it. Opens with a simple Notepad.

That's it, with local server finished. Go to the admin panel of your hosting account.

Here we are interested in the "MySQL" tab.

Click the "Add" button MySQL user". By default, it has already been created. But I recommend creating a new one, specifically for this site.

In the future, when you have more than one site here, this will help avoid confusion.

A pop-up window appears, enter the username in it. It consists of two parts, the first is host1234567 (the numbers will be yours), the unchangeable part, but in the second we write our information.

For further convenience, this will be “meb”. This way I won’t get confused, and I’ll know for sure that this applies to a furniture site.

You can set your own password.