Transferring a MySQL database to another server. Migrate a database to an older version of MS SQL Server

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:

1. Export data using the phpMyAdmin interface through the provider's control panel.
2. 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 implementation. general algorithm creating a database on hosting, which involves:

1. Activation personal account on rented hosting.
2. Search the menu for the section “MySQL”, “Database” or a menu with a similar name.
3. 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 MySQL 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

1. In the same way as in the previous section, go to administrative panel hosting.
2. We select the database we need (the one we created at the very beginning).
3. Click the “Import” tab.
4. Click “Browse” and indicate the location of the storage file previously saved on your computer.
5. We check that the encoding is similar to the one that was applied when saving the file (point 7 of the previous section).
6. 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 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

Our site files are safely uploaded to the hosting. Let's not waste time and transfer the existing database (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.

We see it has appeared new user, but there is no database attached to it.

Now let's create the database itself.

Click on the "Base" tab MySQL data".

"Add MySQL Database" button.

Enter the name in the pop-up window. For convenience, it is better to enter the same data as in the username.

A database appeared, attached to the user we created before.

Write this information down somewhere, i.e. username and database, and password, if you changed it. All this will be needed in the next lesson.

Click on "phpMyAdmin".

On the left, click on the default database.

We find our newly created one and open it in the same way.

Find the “Import” tab at the top – click.

The algorithm is as follows:

1. Choose the required database data.
2. Click on the “Export” menu item in top menu.
3. 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.
4. Check that all WordPress database tables are selected for transfer.
5. If the “Normal” option is specified, then you can determine the compression during export.
6. 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 you don’t 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:

1. Go to PhpMyAdmin and select a new database.
2. In the top menu, click on the “Import” item.
3. After clicking on the “Choose File” button, select the SQL file to import on your computer.
4. 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 I expect more of you in PRO cool features. 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 paragraph just one “Export/Import” button, but, in fact, you have everything you need for a full migration of the WordPress database and site. 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 an excellent job, 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.

Many people have wondered how to transfer scripts with a MySQL database to another hosting. So, I wrote in this article how to transfer your database using SSH/telnet and PHPMyAdmin.

If you have access via telnet or SSH to both servers, then the sequence of your actions will be as follows:
Log in via telnet/SSH to the source server. Export the contents of your database with the following command:

mysqldump -uYour login -pYour password _mysql YourDatabase > baza.sql

After executing this command, all the contents of your database will be saved in the baza.sql file.

Then you need to upload the resulting file with your database to the recipient server. This can be done in the same telnet/SSH session with the ftp command, or using any client you prefer (first download the baza.sql file to your computer, and then upload it to the recipient server). After your database file is on the recipient server, log into this server via telnet/SSH. You can upload your database to the recipient server by running the following command:

mysql -uYour login -pYour password _mysql YourDatabase< baza.sql

(passwords and logins and database names, of course, you must indicate that are valid for the recipient server). As a result, you will transfer your database from one server to another, without any losses.

The technique described above can be used in cases where you have access to both servers via telnet or SSH and in cases where your database is quite large (several tens of thousands of records). In cases where you do not have access to the servers (or one of them) via telnet or SSH or you do not know how to work in team environment Unix and use telnet or SSH client, then you can use the following method:
On the source server, go to the script for working with MySQL databases (As a rule, this is PHPMyAdmin). Select the database intended for transfer and in its properties indicate “View database dump (schema)” (It should be noted that specific names menu items may differ from those mentioned here due to the fact that different servers can be used different versions program, and therefore it is very advisable to familiarize yourself with the relevant documentation). Check the boxes for the items you need: “Structure only”, “Structure and data”, “Data only” and check the “Send” box. When you click the “Go” button after some time you will be asked to upload a file - this will be the content of your database. When the file is downloaded to your computer.

From the author: Relatives recently came to visit. So, in a couple of days, they first depleted the entire food supply, then they “composted” the entire nervous supply, and in the end they burned down the music supply ( music center). In general, out of harm’s way, I decided to quickly transfer the MySQL database. If you also find yourself in this situation, then be sure to read this article.

Fast export method

Let's start with an overview of phpMyAdmin. To transfer a database, you first need to create a duplicate of it. The program has special functionality for this. Let's take a closer look this process:

You can first select the desired database in the list on the left, and then go to the “Export” menu tab. If you use this option, then a table-by-table MySQL migration will be performed. To do this, set the “Export Method” to “Normal”, and select the export elements in “Tables”.

If you need to export the entire database (with all tables), then immediately go to “Export”. Only here we no longer operate with tables, but with databases. In the “Export method” we also set “Normal”. Then select the desired database, and in the “Output” section, select the “Save output to file” option.

The next step is to set the format in which the database copy will be saved. Select the value “SQL” from the corresponding list. This will ensure that the copy can be used on most platforms. But if you are going to transfer the database to concrete basis, then you can select the appropriate format here: PHP array, CSV, PDF and others.

Below in the “Format Options” and “Data Saving Options” sections you can configure more “ ” parameters for transferring the MySQL database. But we will not dwell in detail on their review. If you are not familiar with any of them, then it is better not to change (unnecessarily) set values default. Here you can configure maximum compatibility with older versions of the DBMS and how the tables will be saved. You can only export data or structures. We will copy the tables completely (the “structure and data” option).

After setting all the parameters to create a copy of the database, click “Ok” at the bottom. As a result, we get a duplicate database, which can be easily transferred to another server. By default, the created file is saved in your browser's downloads folder.

We import

WITH using phpMyAdmin You can not only create copies of the entire server, databases and individual tables. The program allows you to easily transfer MySQL data to another DBMS instance. Executing this process is in many ways similar to exporting a database. phpMyAdmin can connect both individual tables to the database and several databases to the server at once. To attach tables on the left in the list, select the desired database, and then go to the “Import” tab.

To attach a database (or several databases) to the server, immediately go to the specified tab. Then, in the “Imported file” section, check the “Browse your computer” option, and use Explorer to indicate the location of the database file.

Here you need to specify the encoding in which the data in the imported source is presented. You should be more careful with this parameter, otherwise you will get real “hieroglyphs” instead of rows in the tables, and you will have to hire a native Japanese or Chinese to decipher them. And there is a real shortage of them in our area.

The most commonly used encoding is UTF-8, which is set by default. Therefore, even if you don’t know exactly which one is used in the portable MySQL database, then you should try this encoding. In any case, you can always delete the imported database and then “re-upload” it with a different encoding.

I also hasten to disappoint the “zealous” fans of phpMyAdmin. This utility Suitable only for export-import of small databases (up to 2 “meters”). This value is quite enough for a partial (stage-by-stage) server transfer, which may not be entirely convenient and may delay the entire process for a long time.

In the “Format” section set the value to “SQL”. If necessary, turn on compatibility mode. And also turn off automatic creation key values ​​for nullable columns (depending on the table structure of the imported source). And to finish the import, click “Ok”.

If you are going to transfer a MySQL database from a backup, then do not forget to delete the “original” source from the server before starting the import. Otherwise, you will receive an error message because the database already exists.

If the process was successful, the program system will display a corresponding message.

Alternative software

I promised along the way learning MySQL introduce you to various software for DBMS administration. This way you can expand your “professional” horizons and choose the program that best suits your needs and type of activity.

Today we will test the capabilities of MySQL migration using a powerful, feature-rich application developed by the creators of the DBMS. You can download MySQL Workbench from the company's official resource. Several third-party distributions (and links to them) that will be required to administer the DBMS using this platform are also described in detail.

I repeat once again: the tool in question has powerful functionality, so we will consider only the one that is designed for importing and exporting individual databases in SQL format. To do this, launch the program, click on the icon of the desired connection (if there are several of them).

In the new window that opens on the left in the “Navigator” panel, select the desired tab(for export or import). I'm importing a duplicate database created using phpMyAdmin.

To transfer MySQL data, go through the “Data Import” item. In the tab of the same name in the “Import Options” section, select the second option (indicated in the picture).

Since we don’t have any schemes, we click on “Start Import” at the bottom. The next tab “Import Progress” displays the status of the transfer process specified file. This option may be useful when importing large volumes data.

After the MySQL transfer is completed, we will have db1 in the list of databases, a duplicate of which we created using phpMyAdmin.

Well, while I was “hiding” my MySQL database, all my relatives had left. Since I was busy, and there was no one to replenish the food supply of the refrigerator. This is how my favorite DBMS saved me from a “related” misfortune. Why does she thanks a lot.