How to quickly migrate a MySQL database using applications. Transferring a MySQL database to another server Extracting files from a database
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 musical one ( music Center). In general, away from sin, I decided to quickly transfer the base MySQL data. 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. Consider in detail this process:
You can first select the desired database in the list on the left, and then go to the "Export" menu tab. If use this option, then a table-by-table MySQL migration 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 suitable only for export-import of small bases (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 software for DBMS administration. So you can expand your "professional" horizons, and choose the program that best suits your needs and occupation.
Today we will test MySQL portability with a powerful feature rich application developed by the creators of the database. Download MySQL Workbench You can from the official resource of the company. 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. The adjacent tab "Import Progress" displays the status of the migration process specified file. 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 website third party programs, besides 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 personal account the hosting provider that hosts your current website. 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 you can later 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++ works best) and use Ctrl+A to look 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.
Last update: 31.10.2015
Saving to a file database
Let's look at how we can save files, in particular image files, to a database. To do this, add a new table Images to the database with four columns: Id ( primary key and identifier, is of type int), FileName (will store the file name and is of type nvarchar), Title (will store the title of the file and is also of type nvarchar), and ImageData (will contain the file's binary data and is of type varbimary(MAX)).
Let's define the code in which the data will be loaded into the table:
Private static void SaveFileToDatabase() ( string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; using (SqlConnection connection = new SqlConnection(connectionString)) ( connection.Open(); SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = @"INSERT INTO Images VALUES (@FileName, @Title, @ImageData)"; command.Parameters.Add("@FileName", SqlDbType.NVarChar, 50 ); command.Parameters.Add("@Title", SqlDbType.NVarChar, 50); command.Parameters.Add("@ImageData", SqlDbType.Image, 1000000); // path to file to load string filename = @" C:\Users\Eugene\Pictures\cats.jpg"; // file title string title = "(!LANG:Cats"; // получаем короткое имя файла для сохранения в бд string shortFileName = filename.Substring(filename.LastIndexOf("\\")+1); // cats.jpg // массив для хранения бинарных данных файла byte imageData; using (System.IO.FileStream fs = new System.IO.FileStream(filename, FileMode.Open)) { imageData = new byte; fs.Read(imageData, 0, imageData.Length); } // передаем данные в команду через параметры command.Parameters["@FileName"].Value = shortFileName; command.Parameters["@Title"].Value = title; command.Parameters["@ImageData"].Value = imageData; command.ExecuteNonQuery(); } } !}
After executing this program, the corresponding entry will appear in the database:
Retrieving files from the database
In the last topic, we looked at how to add a file to a database. Now let's perform the reverse operation - get the file from the database. First, let's define a file class that will make it easier to work with data:
Public class Image ( public Image(int id, string filename, string title, byte data) ( Id = id; FileName = filename; Title = title; Data = data; ) public int Id ( get; private set; ) public string FileName ( get; private set; ) public string Title ( get; private set; ) public byte Data ( get; private set; ) )
Then, in the program code, define the following method:
Private static void ReadFileFromDatabase() ( string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; List
In this method, using the SqlDataReader, we get the values from the database and create an Image object from them, which is then added to the list. And at the end we look, if there are elements in the list, then we take the first element and save it to local computer. And after saving, the file loaded from the database will appear in the folder of our program.
Hello, friends! The article concerns the blogosphere and in it we will consider saving the site database on TimeWeb hosting.
TimeWeb hosting, website database
Every person who has created his own site (blog) knows that periodically it is necessary to save the database of his site. There are reasons for this.
There may be technical problems on the hosting, attackers can pick up the password to the admin panel of your site and other problems. People have different attitudes to the frequency of saving the database, but they must be saved unambiguously. In the initial period, after creating my own blog (I wrote in the article), I knew that I needed to make a copy of it. My first attempt was unsuccessful, although there is nothing complicated there (I learned this later). It is clear that he also could not optimize the database.
I looked at articles on the Internet, but it says, in general. After some trial and error, everything worked out. Therefore, I decided to write a short article on how to make a copy of the site database on HDD maybe my experience will be useful.
The site is hosted on TimeWeb hosting (WordPress engine). I save the site database once a week, someone does it more often, someone less often - this is everyone's business. It is necessary to save two main structures of the site, this is the file system and the site database. AT file system includes: WordPress engine, installed themes, plugins and pictures.
The database includes site content (pages, articles, comments). TimeWeb hosting constantly makes backups and stores them for the last three days. You can save data from the admin panel, you can directly from the hosting, I do it according to the second option.
So, go to the hosting in the control panel (Fig. 1).
Click the "Manage BACKUP" button. In the window that opens, click on the arrow in the public_html line. Confirm that you really want to archive the public_html file/directory in your home directory (Figure 2).
After a few seconds, archiving is completed. Further, without leaving this window, in the line where your domain is registered, click on the arrow and confirm that you really want to save the file/directory archive.
Archiving takes several minutes (it all depends on the amount of data on the site). After that, click on "On start page» (at the bottom of the table) and on home page press " File manager". We select the two files we need by the date of archiving and save them one by one.
To do this, one by one, double-click on the selected files (Fig. 3), it is asked where to save, save. We hide the saved files in safe folders, it will not be superfluous to save them to DVD disc. Be sure to save your data.
And further. After writing this article, I had problems with the data of my site (blog), the problems were successfully resolved. Therefore, based on the results of my work, articles were published on the creation backup database (article ), as well as maintaining and optimizing the database (articles
