How to quickly migrate a MySQL database using applications. Database transfer to hosting

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

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

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.

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 are the proud owner of a 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 with your own internal means. You can also use MySQL database dumper. 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 required names tables with clamped 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 similar 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 completed).

Do you have MS database SQL Server, which needs to be transferred to another physical computer. You have already made a backup and are happily starting to restore. But then it turns out that on the computer where you need to transfer the database, more old version MS SQL Server. Stack Overflow assures you that everything is bad. But is this really so?

Of course, transferring a database from a newer version to an older one is not a classic and not the most correct work scenario. But often databases are created in such a way that they support all newer versions of SQL, starting with some, for example, 2008 R2, because MS SQL's direct compatibility is more than excellent. And, for example, your client has already installed MS SQL 2016, and you have MS SQL 2014 on your test server for development. And you want to deploy the client’s database to figure out where he is confused with the data.

Microsoft disowned the problem - they say they don’t have it backward compatibility, and that's it. A backup created on a newer server cannot be restored to an older server. Yes, they have tools like DTS, database copying, export-import, etc. But they are so inconvenient and cumbersome that it is not particularly convenient to do a normal transfer of a large database with many tables using them. In any case, it didn’t work out for me personally.

Yes, you can generate SQL scripts for the entire database, including the data. But imagine, you have a bunch of blob fields with big data in your database, and in general the size of the entire database is 500+ GB. Can you imagine how long such a script will take, how long it will take to generate and execute.

The number one limitation is that you need access via MS SQL Management Studio to both servers - old and new. If this is not possible, then it should be possible on the machine from which you want to transfer the database to install the version of SQL to which you want to transfer the database, in order to transfer the database first to this version locally, and then drag it through a backup or directly through *df database files (via Detach/Attach) on new car (SQL version Server" and in this case will already match).

Another limitation is that you will need a script for the database schema (all objects, including tables, indexes, constraints, stored procedures, triggers, etc.) without data, and the instructions for creating Foreign Key Constraints must go in this script in at the very end, separately from the script for creating the tables themselves.

I will briefly describe the data transfer algorithm itself. All actions are performed in a Management Studio session connected to the server, to which you need to move the database.

1) On the new server, create an empty database with the same files and file groups as the transferred database.

2) Using the database schema script, we create all database objects (tables, indexes, views, triggers, stored procedures and functions), but without creating Foreign Key Constraints. You cannot create an FK at this stage, because they will interfere with data insertion.

3) We connect the database from which we will transfer data as a Linked Server so that we can use calls to the old database in requests to the new database.

EXEC sp_addlinkedserver @server=N"LinkedServerAlias", @srvproduct=N"", @provider=N"SQLNCLI", @datasrc=N"LinkedServerHost\LinkedServerName"; EXEC sp_addlinkedsrvlogin "LinkedServerUser", "false", null, "RealUser", "RealUserPassword";
4) Because database structures are the same, we will use the built-in stored procedure sp_msforeachtable, which allows you to run a query on each database table to generate a script for transferring data from the old database to the new one through a query like

INSERT INTO ? SELECT * FROM ?
sp_msforeachtable replaces the question mark with the name of each table and runs the query multiple times (once per table).

Here I came across the most large number rake.

A) Problem number one is that for tables with IDENTITY fields you need to call:

SET IDENTITY_INSERT ON; --INSERT INTO ... (the insertion itself); SET IDENTITY_INSERT OFF;
b) Problem number two is that on tables that do not have IDENTITY fields, do this call is impossible, so it is necessary to dynamically determine whether the table has an IDENITY column or not.

This can be done with this request:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME="SomeTable") AND (COLUMNPROPERTY(object_id("dbo.SomeTable"), COLUMN_NAME, "IsIdentity") = 1)
c) Problem number three is that, as it turned out, in IDENITY_INSERT ON mode you cannot do

INSERT INTO ... SELECT * FROM ...
, but you need to list specific fields.

You can list table fields in a row using the following query:

SELECT SUBSTRING((SELECT ", " + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "SomeTable" ORDER BY ORDINAL_POSITION FOR XML path("")), 3, 200000);
4) Generate an insert script for all tables:

Script generation procedure

EXEC sp_msforeachtable N" DECLARE @command varchar(MAX); DECLARE @name varchar(200); SET @name=""?"; SET @name = SUBSTRING(@name, 8, LEN(@name)-8); SET @command = """"; SELECT @command= SUBSTRING((SELECT "", "" + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = """" + @name + """" ORDER BY ORDINAL_POSITION FOR XML path("""")), 3, 200000); SET @command = ""INSERT INTO ""+ @name +"" (""+ @command + "") SELECT "" + @command + "" FROM "" + ""LinkedServerAlias.SourceDatabase."" + ""?""; SET @command= ""IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME=""""" + @Name + "" """") AND (COLUMNPROPERTY(object_id(""""dbo.""+@Name+"""""), COLUMN_NAME, """"IsIdentity"""") = 1)) SET IDENTITY_INSERT "" + @name + "" ON; "" +@command; SET @command=@command+"";"" + ""IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME=""""" + @Name + """""") AND (COLUMNPROPERTY(object_id(""""dbo.""+@Name+"""""), COLUMN_NAME, """"IsIdentity"""") = 1)) SET IDENTITY_INSERT "" + @name + "" OFF;""; PRINT (@command); --EXEC(@command); // If you uncomment, the script will be executed immediately, and not just displayed on the screen "

6) Execute the script to create all Foreign Key Constraints (now possible).

7) Done! You transferred the database from the new one SQL server to the old one, even though it was considered impossible. Moreover, the transfer is carried out only one and a half times slower than the data transfer speed over the network, i.e. pretty quickly.

8) Cleaning up after ourselves (disabling Linked Server):

EXEC sp_droplinkedsrvlogin "LinkedServerUser", null; sp_dropserver "LinkedServerAlias";
Limitations of the method.

1) Similar method It will not be possible to transfer tables that have columns of the XML type.
There are probably many other restrictions, because... the database that I transferred in this way did not use many of the features of the SQL server. You can write about the limitations in the comments, and I will supplement the article with them.

Thank you for your attention! Hope it helps someone.

By database transfer we mean the procedure of changing the server InterBase as in the direction of increase serial number, and in the direction of its reduction, as well as the transition to another operating system or hardware platform. In some sources, the database transfer procedure is called migration.

Versions currently in use InterBase from 4.x to 6.x, and in the sixth version the database can be created in dialect 1 or in dialect 3. In general case transition from the younger version InterBase to the older one does not require any special actions, and the databases work fine, but the user cannot use additional services, which are provided by the older version. If you complete the database transfer procedure, you will be able to use additional services. As for the 6.x dialects, they interpret some data types differently. For example, in earlier versions InterBase and in version 6.x dialect 1 one date type is defined Date whose value first contains the date and then the time. There are three types defined in Dialect 3 version 6.x - timestamp, which fully matches the type Date defined in earlier versions; type Date which contains only date values, and type Time, which contains time values.

When performing a database migration procedure automatic replacement date type is only executed in definitions domains. The date type in the metadata is replaced manually.

Every operating system in my own way interprets each data type. When installing the server InterBase Any version is configured for the appropriate operating system and hardware environment.

Thus, each database is “linked” to the server version InterBase, to the operating system and hardware environment.

This explains the need to perform the database migration procedure.

From the above it becomes clear that the creation backup copy database with parameter enabled Transportable causes version information to be included in the backup file InterBase operating system and hardware environment in which the database was created and operated.

When transferring a database to another personal computer, the server InterBase reads the backup file and correctly performs data type conversion if necessary and makes settings on new version InterBase operating systems and hardware environment.

Please note that you can only upgrade to the next sequential version. InterBase both in increasing and decreasing directions.

When migrating a database to two or three versions higher (or lower), you must perform the migration procedure for each intermediate versions IMegVase.

To change the dialect (for example, from the first to the third), you must either recreate the database or use the utility y/ix.

Database transfer procedure algorithm

A. Create a database backup file. The file is created using one of the methods discussed above. It is advisable to check whether the backup file was created correctly. To do this, on the same personal computer, deploy the database in another directory and check its functionality.

b. Create a copy file of registered users on the server InterBase. Please remember that user information is stored in a file isc4.gdb on the server InterBase and in the database itself. To copy a file iscA.gdb you can use the same utility gbak.

Example 12.7. Copy the database registered users file.

gbak -b -user SYSDBA -password masterkey C:IBServeisc4.gdb C:isc4.gdk

V. Reinstall server InterBase or switch to another personal computer. After reinstalling the server on a personal computer (or moving to another personal computer), you need a file iscA.gdb restore using the same utility gbak.

It is important to remember that when upgrading to a higher version InterBase all clients registered in the next lower version InterBase, will work fine (but without additional features), and in older ones - unstable.

For such clients, it is advisable to reinstall the client part InterBase on personal computers x clients.

Example 12.8. Transferring the database registered users file.

gbak -с -user SYSDBA -password masterkey C:isc4.gdk C:isc4.gdb

In examples 12.7 and 12.8 it was meant that the version was being replaced InterBase on one computer.

d. Restore (transfer) the database using one of the methods described above.

The algorithm proposed above works reliably when upgrading the version InterBase. If you need to downgrade the version number InterBase, then to perform this operation you must have two personal computers: the first - with a working database on an older version InterBase, second - with installed server InterBase lower version. We start the procedure for creating a backup copy of the database (step “a” of the algorithm) from the second computer. This will create a backup file in a lower version. But possible the following options:

• in older version InterBase When creating and operating the database, no mechanisms were used that are missing in the younger version InterBase, then a full-fledged working copy of the database will be created in the lower version InterBase",
• the database used the original mechanisms of the older version InterBase, then a copy of the database and a log of detected errors will be received. And errors in barely restoring the database in a lower version InterBase will have to fix it manually.

For reliable operation of the database, it is necessary to ensure the same version and dialect of the server InterBase and client side InterBase for every client.

Clients of all versions InterBase, unlike clients running Dialect 3 version 6.x, do not have access to:

To the keywords:

CURRENTDATE CURRENTTIME CURRENT_ TIMESTAMP COLUMN

TIMESTAMP

To identifiers enclosed in quotes.