How to save a database from phpmyadmin to your computer. Transferring a MySQL database to another server

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.

There may be technical problems with the 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 was written there 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 site’s database to a hard drive, perhaps my experience will come in handy.

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. The file system includes: the 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 “To the start page” (at the bottom of the table) and on the main 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 wouldn’t hurt to save them to a DVD. Be sure to save your data.

And one more thing. 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

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 content of your website to third-party programs, especially those 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 competently 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 the personal account of the 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.

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

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 transfer the MySQL database. 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 at 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, a table-by-table MySQL transfer 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 a specific base, 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 “ ” 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, it is best not to change (unnecessarily) the default values. 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

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 encoding is used in the portable MySQL database, it’s worth trying 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 is only suitable for exporting and importing 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. We also disable the automatic creation of a key value for columns with a null value (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

As you learn MySQL, I promised to introduce you to various DBMS administration software. 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 of MySQL migration using a powerful, feature-rich 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 adjacent “Import Progress” tab displays the status of the transfer process of the specified file. This option can be useful when importing large amounts of 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 had 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. For which I thank her very much.

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;

At the moment we are interested in the last point. 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 the file is 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 an open source web application 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 the script to work correctly, 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. You can find out more detailed information 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 the backup copy will be saved 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 #4: Export using the Sypex Dumper script.

This method is available only to those users who have access to SSH (Secure SHell, remote control of the 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).

Once connected, just run the following command on the command line:

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 the operating system does not report any export errors, then the backup copy 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.