What methods are there?
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 contents of your site third party programs, moreover, from unknown 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 correctly transfer MySQL databases, without losing or damaging data.
Creating a database on a new hosting
If you want to transfer 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 hosting provider, but comes down to one general algorithm:
1 - We are looking for the “MySQL”, “Databases” or something similar section.
2 - Click “Create” in it.
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 still empty.
Exporting the database from the old hosting
Now we will use what is called a database dump. Those. Let's save the current database from the server to your computer. To do this, we need the phpMyAdmin interface, which you need to find in personal account hosting provider who hosts your current website. Again, there is no single algorithm, so here is a general scheme:
2 - In the left corner, select your database (the one that you are going to export to your computer and then transfer to another hosting).
4 - You may be asked to select the export method “Normal” (many settings) or “Quick” (few settings). It doesn’t matter which one you 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 extension .sql.
Importing a database to the server of a new hoster
1 - In the same way we look for phpMyAdmin on the 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 your computer.
5 - Check that the encoding matches the one in which you saved the database.
6 - Don’t 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 look for these queries. If you find them, simply delete them and try importing the database again.
2 - When importing, the database located at the new hoster must be empty; if any tables already exist in it, select and delete them.
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
In the DirectAdmin panel there is a section in which control over databases is performed, the MySQL Management section. This section displays a list of user databases and main controls:
creating and deleting a database;
editing the database (phpMyAdmin interface);
restoring a damaged database;
checking database tables;
optimization of database tables;
database import;
database user management;
database export;
IN this moment It is the last point that interests us. Near each database there is a “Download” link.
In this case, the browser will prompt you to save the file to your local computer<имя базы>.gz, in this example it is the file user_database.gz. The file is provided in compressed form as a gz archive.
Method No. 2: Export using the phpMyAdmin web interface.
phpMyAdmin is a web application with open source code, written in PHP and is a web interface for administering the MySQL DBMS. 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 among web developers as it allows you to manage the MySQL DBMS using a user-friendly interface, without the need to use 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 database user login and password that you specified when creating the database. The first thing we need to do after logging into the phpMyAdmin interface is to select the database we are interested in from the list:
Let's look at each point in more detail.
1. Export. This item allows us to select the tables that need to be included in the database backup. By default, all tables will be included in the backup.
2. Backup format. There are several backup formats to choose from, in this case we choose the SQL type. The remaining methods are used to perform specific tasks and are used very rarely.
3. A very important point is the “Save as file” checkbox. If this checkbox is checked, you will be prompted to download the backup copy. Otherwise, a text version of the backup copy will be displayed on the screen in the form of MySQL queries.
4. File name template. By default, the file name will be as follows:<имя базы>.<формат файла>, in our example this is user_database.sql.
5. Compression. This item allows you to select the file compression method:
without compression, i.e. file in sql format, the file size will correspond to the size of the database;
zip, i.e. file in archive format.zip, the file size will be reduced by archiving the backup copy;
gzip, i.e. file in .gz format, the file size will be reduced by archiving the backup copy;
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. Finally, you will be prompted to save the database backup file to your local computer.
Method No. 3: Export using the Sypex Dumper script.
Sypex Dumper is a software product (PHP script) with which you can quickly and easily create a backup copy (backup, dump) of a MySQL database, and also restore the database from a backup copy if necessary. Official website of the script: http://sypex.net/. On the website 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 look at how the script works using the example of Sypex Dumper Lite 1.0.8; 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, in the public_html directory. For correct operation script, you will need to create a directory for storing database backups (dumps). To create a directory, go to the File Manager of the Direct Admin panel, go to the public_html directory and create a new backup directory.
Let's move on directly to working with the Sypex Dumper script. The script is called from the address bar of the 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 this, the script will prompt you to select an action on your databases: “Backup / Creating a database backup copy” and “Restore / Restoring a 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 that will be included in the backup. More detailed information You can find out about filters on the website of the developer of the Sypex Dumper script. In the “Compression method” item, you can specify whether Gzip compression will be used when creating a backup copy (packed file with the .gz extension), or whether it will be saved backup copy in .sql format. The Compression level item is used only if the Gzip compression method is selected. The higher the value of this parameter, the smaller the file size will be.
After selecting all the backup options, click Apply. The creation of a backup copy will begin, 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 want 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 the backup copy; it can be stored on the server in the backup directory.
Method No. 4: Export using the Sypex Dumper script.
This method is available only 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 (USER), "-p" is the parameter that specifies the database user password to connect (PASS), DBNAME is the database name, "/path/to /dump.sql" - full path to the database backup file. Let's look at the command using our example. Let's say the backup file is located in the directory domains/example.com/public_html/backup (where exapmle.com is the name of your domain), the database user name is “user_user”, the password is “Ide2eib6?, the database name is “user_database”, the name backup copy - dump.sql, the backup file is located along the path 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, which means the backup was successfully exported. The size of the database backup does not matter.
The considered methods are universal and allow you to solve any problem of database export.
Last updated: 10/31/2015
Saving files to the database
Let's look at how we can save files, particularly image files, into a database. To do this, add a new table Images to the database with four columns: Id (the primary key and identifier, is of type int), FileName (will store the file name and is of type nvarchar), Title (will store the file title and is also of type nvarchar) and ImageData (will contain binary file data and is of type varbimary(MAX)).
Let's define the code in which 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 the file to download string filename = @" C:\Users\Eugene\Pictures\cats.jpg"; // file title string title = "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 a database
In the previous 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 simplify working 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 we define the following method:
Private static void ReadFileFromDatabase() ( string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; List
In this method, using SqlDataReader, we obtain values from the database and use them to create an Image object, 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 the local computer. And after saving, the file downloaded from the database will appear in our program folder.
Hello, friends! The article concerns the blogosphere and in it we will look at saving the site’s database on TimeWeb hosting.
TimeWeb hosting, database website
Every person who has created his own website (blog) knows that he must periodically save the database of his website. There are reasons for this.
Can be technical problems on hosting, attackers can guess the password to the admin panel of your site and other problems. People have different opinions about the frequency of saving a 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 had 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 I was also unable to optimize the database.
I looked at articles on the Internet, but it says in general. After practicing through trial and error, everything worked out. Therefore, I decided to write a short article on how to make a copy of a website database on HDD, perhaps my experience will be useful.
The site is hosted by TimeWeb (WordPress engine). I save the site’s database once a week, some do it more often, some less often - it’s everyone’s business. There are two main site structures that need to be preserved: the file system and the site database. IN file system includes: WordPress engine, installed themes, plugins and images.
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, or directly from the hosting, I do the second option.
So, go to the hosting 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 save the archive of the public_html file/directory in your home directory (Fig. 2).
After a few seconds, archiving is completed. Next, 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 lasts several minutes (it all depends on the amount of data on the site). After that, click “On home page"(at the bottom of the table) and on home page click " File manager" We select the two files we need by archiving date and save them one by one.
To do this, double-click on the selected files one by one (Fig. 3), ask where to save, save. We hide the saved files in safe folders; it would not be superfluous to save them on DVD disc. Be sure to save your data.
And further. After writing this article, I had problems with the data on my website (blog), the problems were successfully resolved. Therefore, based on the results of my work, articles were published on creating a database backup (article), as well as on saving and optimizing the database (article
Due to the move to new hosting, I had to learn the basics of migrating a MySQL database. By the way, I use MaxSiteCMS as the CMS for my blog. I purchased new hosting, or rather a virtual dedicated server, from the company Adman.
I've been using my own script for a long time to create backups of my website and MySQL database. Which I wrote about in the article. But we have not yet had the opportunity to take advantage of the results of the work done. 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 need to do 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 instructions were not useful to me. And to restore you need to use the command:
Mysql -u username -p newdatabasename< databasebackup.sql
