In connection with the move to new hosting, I had to learn the basics of transferring the database MySQL data. 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
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, has 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 binary data of the file 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.
Using the DirectAdmin panel; Using the phpMyAdmin web interface; Using the Sypex Dumper script; Using the server command line
Method #1: Export using the DirectAdmin panel
The DirectAdmin panel has a section that controls databases, the MySQL Management section. This section displays a list of user databases and basic controls:
creating and deleting a database;
database editing (phpMyAdmin interface);
recovering a damaged database;
checking database tables;
optimization of database tables;
database import;
database user management;
database export;
AT this moment we are interested in last paragraph. Near each database there is a link "Download"
In this case, the browser will prompt you to save the file to your local computer.<имя базы>.gz, in this example this is user_database.gz file. The file is provided compressed as a gz archive.
Method #2: Export using the phpMyAdmin web interface.
phpMyAdmin is an open source web application. source code written in PHP language and representing a web interface for administration DBMS MySQL. phpMyAdmin allows you to administer the MySQL server through a browser, execute SQL queries and view the contents of databases and tables. The application is very popular with web developers, as it allows you to manage the MySQL database using a friendly interface, without the need for complex SQL queries to perform simple tasks.
To go to the phpMyAdmin web interface, select the appropriate item in the Direct Admin panel:
To access phpMyAdmin, you need to enter the login and password of the database user that you specified when creating the database. The first thing we need to do after entering the phpMyAdmin interface is to select the database we are interested in from the list:
Let's consider each item in more detail.
1. Export. This item allows us to highlight the tables that need to be included in the database backup. By default, all tables will be included in the backup.
2. Format backup. There are several backup formats to choose from, in this case we choose the SQL type. The remaining methods are used when performing specific tasks and are used very rarely.
3. Very important point- "Save as file" checkbox. If this checkbox is checked, you will be prompted to download the backup copy. Otherwise, the text version of the backup will be displayed in the form of MySQL queries.
4. File name template. By default, the file name will look like this:<имя базы>.<формат файла>, in our example it is user_database.sql.
5. Compression. This item allows you to select the file compression method:
without compression, i.e. a file in sql format, the size of the file will correspond to the size of the database;
zip, i.e. a file in .zip archive format, the file size will be reduced by archiving the backup;
gzip, i.e. file in .gz format, the file size will be reduced by archiving the backup;
After selecting all the necessary parameters, just click the "OK" button and wait for the backup to be prepared. If the database is large, it may take some time to prepare the backup. As a result, you will be prompted to save the database backup file to the local computer.
Method #3: Export using the Sypex Dumper script.
Sypex Dumper is software(PHP script), with which you can easily and quickly create a backup copy (backup, dump) of the MySQL database, and also restore the database from the backup if necessary. Official website of the script: http://sypex.net/. On the site you can download the Sypex Dumper script for free, and read the documentation for working with this product. Sypex Dumper allows you to work with databases of almost any size. We will consider the script using the Sypex Dumper Lite 1.0.8 version as an example, this version of the script is the easiest to use.
Download the script itself, unpack the archive and upload the dumper.php file to your server, to the public_html directory. For the script to work correctly, you will need to create a directory for storing backup copies (dumps) of databases. To create a directory, go to the File Manager of the Direct Admin panel, change to the public_html directory and create a new backup directory.
Let's proceed directly to working with the Sypex Dumper script. The script is called from address bar browser: http://example.com/dumper.php, where example.com is your domain. The script will prompt you to enter your username and password. You will need to enter the database user login and database user password that you specified when creating the database.
After that, the script will prompt you to select an action on your databases: "Backup / Create a backup copy of the database" and "Restore / Restore the database from a backup copy". We are interested in the first point.
The "DB" item allows you to select the required database from the list of your databases. The table filter allows you to specify the tables to be included in the backup. You can find more detailed information about filters on the website of the Sypex Dumper script developer. In the “Compression Method” item, you can specify whether Gzip compression will be used when creating a backup (packed file with the .gz extension), or whether the backup will be saved in .sql format. The Compression level item is used only if the Gzip compression method is selected. The larger the value of this parameter, the smaller the file size will be.
After selecting all the options for creating a backup, click the Apply button. The backup will start, the process itself will be displayed on the monitor screen:
After the process is completed, you will be provided with a backup creation log, as well as an offer to download the backup file. If you wish to continue working with the Sypex Dumper script, click the Return button. It is worth noting the fact that the backup created by this script will be stored in the backup directory that we created previously, i.e. it is not necessary to download a backup copy, it can be stored on the server, in the backup directory.
Method #4: Export using the Sypex Dumper script.
This method is only available to those users who have access to SSH (Secure SHell, remote control operating system). To export a database backup, you need to connect via SSH to the server (for example, using the Putty ssh client if you have a Windows OS, or using a terminal if you have a Linux OS).
After connecting to command line just run the following command:
mysqldump -uUSER -pPASS DBNAME > /path/to/dump.sql
Where "-u" is the parameter that specifies the database user name to connect to (USER), "-p" is the parameter that specifies the password of the database user to connect to (PASS), DBNAME is the name of the database, "/path/to /dump.sql" is the full path to the database backup file. Let's take a look at our example command. Suppose the backup file is located in the domains/example.com/public_html/backup directory (where exapmle.com is your domain name), the database username is “user_user”, the password is “Ide2eib6?, the database name is “user_database”, the name backup - dump.sql, the backup file is located at domains/example.com/public_html/backup/.
In this case, the command to import the database will look like this:
mysqldump -uuser_user -pIde2eib6 user_database > domains/example.com/public_html/backup/dump.sql
If after executing this command operating system does not report export errors, the backup was exported successfully. The size of the database backup does not matter.
The considered methods are universal and allow solving any problem of database export.
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 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 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++ 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.
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 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. 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
FROM 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. And also turn off automatic creation key values for columns with zero value(depends on the table structure 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. 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 consider only 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 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.