How to correctly transfer a MySQL database from an old hosting to a new one. MySQL Database Migration

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, 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 the question about the need backup additional ED-bases. After the operation to create backup copies of additional databases is 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"

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 move the base MySQL data. 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 null-valued 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 to 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 MySQL migration using a powerful multifunctional 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 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.

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 there is one date type defined in dialect 1 version 6.x 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 is clear that creating a database backup with the option 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 configures the 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. Copying 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 user 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.

You have a MS SQL Server database that 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 the database from more new version to the old one - this 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. Backup created on a newer server cannot be restored on 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 Server version" 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 old base to a new one through a request 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.