In connection with the move to new hosting, I had to learn the basics of transferring a MySQL database. By the way, I use MaxSiteCMS as my blog's CMS. And I purchased a new hosting, or rather a virtual dedicated server, from Adman.
I've been using my own script for a long time to back up my website and MySQL database. About which he wrote in the article. But to use the results of the work done has not yet been necessary. Which, of course, did not upset me, since the previous hosting worked properly and data recovery was not required.
How to save a MySQL database
Let me remind you that it cannot be copied simply by overwriting some file.
And to save the dump, you must execute the following in the console:
mysqldump -u username -p databasename > databasebackup.sql
How to restore a MySQL database
Since I already had a backup copy of the database, the previous instruction was not useful to me. And to restore, you need to use the command:
mysql -u username -p newdatabasename< databasebackup.sql
From the author: Recently relatives came to visit. So in a couple of days they first depleted the entire food base, then “composted” the entire nervous one, and in the end they burned down the music (music center). In general, away from sin, I decided to quickly transfer the MySQL database. If you are also in this situation, then be sure to read this article.
Quick way to export
Let's start with an overview of phpMyAdmin. To transfer the database, you first need to create a duplicate of it. For this, the program has a special functionality. Let's consider this process in detail:
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 transfer of MySQL will be performed. To do this, in the "Export Method" set "Normal", and in the "Tables" select the export elements.
If you want to export the entire database (with all tables), then immediately go to "Export". Only here we already operate not with tables, but with databases. In the "Export Method" 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 copy of the database will be saved. Select the "SQL" value in 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 a specific basis, then here you can select the appropriate format: PHP array, CSV, PDF, and others.
Below in the "Format Options" and "Data Saving Options" sections, you can configure more than " " options for transferring the MySQL database. But we will not dwell on their review in detail. If you're unfamiliar with any of them, it's best not to change (unnecessarily) the defaults set. 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 is easy to transfer to another server. By default, the generated file is saved in your browser's downloads folder.
We import
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. This process is very similar to exporting a database. phpMyAdmin "can" attach both separate tables to the database, and several databases at once to the server. 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 "Import file" section, check the "Overview of your computer" item, and through the explorer indicate the location of the database file.
Here you need to specify the encoding in which the data is presented in the imported source. You should be 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 with them in our area there is a real shortage.
The most commonly used encoding is UTF-8, which is the default. Therefore, even if you do not 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 upset the "zealous" fans of phpMyAdmin. This utility is suitable only for export-import of small databases (up to 2 "meters"). This value is quite enough for a partial (staged) transfer of the server, which may not be very convenient and drag out the whole process for a long time.
In the "Format" section, set the value to "SQL". If necessary, enable compatibility mode. We also disable the automatic creation of a key value for columns with a null value (depending on the structure of the tables of the imported source). And click "OK" to finish the import.
If you are going to transfer the MySQL database from a backup, then do not forget to delete the “original” source from the server before starting the import. Otherwise, you will get an error message because the given 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 database management software as you learn MySQL. So you can expand your "professional" horizons, and choose the program that best suits your needs and type of activity.
Today we will test MySQL portability with a powerful feature rich application developed by the creators of the database. You can download MySQL Workbench from the company's official resource. It also describes in detail several third-party distributions (and links to them) that will be required to administer the DBMS using this platform.
I repeat once again: the tool in question has powerful functionality, so we will only consider the one that is designed to import and export individual databases in SQL format. To do this, run the program, click on the icon of the desired connection (if there are several).
In the new window that opens on the left in the Navigator panel, select the desired tab (for export or import). I am importing a duplicate database created with phpMyAdmin.
To carry out the transfer of 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. In the adjacent tab "Import Progress" the status of the transfer process of the specified file is displayed. This option can be useful when importing large amounts of data.
After the end of the MySQL transfer, we will have db1 in the list of databases, a duplicate of which we created using phpMyAdmin.
Well, in the meantime, I "hid" my MySQL database, all the relatives left. Since I was busy, and there was no one to replenish the food base of the refrigerator. This is how my favorite DBMS saved me from a "related" misfortune. For which many thanks to her.
What are the ways?
1 - using the phpMyAdmin interface.
2 - using the hosting provider's control panel.
3 - using a third-party program.
Which way is better?
We recommend the first one, because its scheme is simple, and is used by most webmasters. We do not recommend using the third method! Never trust the content of your site to third-party programs, moreover, from incomprehensible developers. You can also use the second method, but its algorithm is very different, and depends on the hoster's control panel. Therefore, we will consider the first option in detail, and tell you how to competently transfer MySQL databases without losing or corrupting data.
Create a database on a new hosting
If you want to migrate a MySQL database, then you already have a database and you know how to create it. In any case, this process is different for each of the hosting providers, but comes down to one general algorithm:
1 - We are looking for the section “MySQL”, “Databases” or something similar.
2 - In it, click "Create".
3 - Enter the name of the database, attach a user to it (usually the database user has already been created, if not, then create it and set the highest rights) and enter the password for the database.
4 - The database has been created, but it is currently empty.
Exporting the database from the old hosting
Now we will use what is called a database dump. Those. save the current database from the server to your computer. To do this, we need the phpMyAdmin interface, which must be found in the personal account of the hosting provider that hosts your current site. Again, there is no single algorithm, so we present a general scheme:
2 - On the left in the corner, select your database (the one you are going to export to your computer, so that later you can transfer it to another hosting).
4 - You may be asked to select the export method “Normal” (many settings) or “Quick” (few settings). It does not matter which one to choose, the main thing is to change only those settings that we describe below.
5 - You need to select all tables by clicking on the “Select All” button.
7 - Just in case, remember the encoding, but do not touch it.
8 - Click "OK" and save the file from the database to your computer. Typically, the saved file has the .sql extension.
Importing the database to the server of the new host
1 - In the same way, we are looking for phpMyAdmin on a new hosting.
2 - In it, select the desired database (we created it at the beginning of the article, and it remained empty).
3 - Click on the "Import" tab.
4 - Click “Browse” and select the database saved on the computer.
5 - Check that the encoding matches the one in which you saved the database.
6 - Do not change anything else, click "OK" and your database will be imported to the new hosting.
What are the problems?
1 - When importing a database, it should not contain queries like " CREATE DATABASE, /*!40101 SET @OLD". To check their presence, open the database file on your computer with any text editor (Notepad++ is best) and use Ctrl+A to search for these queries. If you find it, just delete them and try importing the database again.
2 - When importing, the database located at the new host must be empty, if there are already any tables in it - select and delete them.
