Reserve copying of bases MySQL

You work above the web - project in which it is used SUBD MySQL. Certainly, each serious project assumes, that the backup copy of all data used in job will be periodically created. It de fakto the standard approach. Moreover, without it it is impossible to guarantee to visitors of a site uninterrupted operation of job of your resource. In case the site renders commercial services, presence of reserve copying is simply vital necessity.

In general, almost all hostings - providers make reserve copying all files of users. However, it is not necessary to forget that providers do{make} backup, basically, for itself, on a case of failure at itself. For this reason users in conditions of a hosting can to count, certainly, on restoration in case of removal{distance} of any data on fault of the user, but at all the fact, that the provider will make restoration of MySQL-base at once on reception of search. It is better to do{make} for itself a copy and in case of that her  to use. It is possible even to copy periodically this backup on another, not provajderskuju the machine - so it is more reliable, just in case.

To make a copy of all static HTML-and other documents simply. As simply periodically " to postpone in storonku " and copies of scripts. Much more complex  represents a problem  of creation of a copy (further backup) such dynamical structure, as database MySQL. The basic difficulties which arise before the manager of the site placed on a hosting, usually are such:

1. Absence of physical access to files of a database. As a rule, providers of a hosting give an opportunity of job with a database only through scripts or the special mysql-client, but do not give the rights to access directly to files which contain the data from MySQL-base.

2. Absence at the manager of knowledge of how in general it is necessary to do{make} backup. usually such problem  arises only when " the fried cock " has pecked. That is, in case of failure, intrusion of hackers or in other non-staff situations. Web designers simply are not ready to immediate backup and start to study convulsively the documentation on MySQL, and time goes...

3. In case the web designer does not own in a sufficient measure skills of job with the specialized utilities from package MySQL, there can be the difficulties connected to restrictions, imposed by the hosting - provider on the user accounts. For example, if the base very big and its{her} size exceeds a limit for memory accessible to the user (RAM), backup will make difficultly. It is necessary to use thin adjustments of utilities of reserve copying, that sometimes too causes difficulties in practice.

So, given clause{article} is intended to facilitate job on creation of backup copies of MySQL-bases. If you are a web designer, and work with MySQL, for certain, the information contained in given clause{article}, will be useful to you.

How to make a copy of base MySQL

There is a program mysqldump, allowing quickly and simply to make operations on creation of backup copies of bases MySQL. Also mysqldump enables to do{make} very thin adjustments for management of process of creation of backup copies of databases or separate tables. It is possible to say, that mysqldump is the basic tool which to you should be used in the event that you will do{make} backup MySQL.

At once we shall take a simple problem  which we shall solve with the help mysqldump, and we shall understand, that to what. There is a hosting, there is database DBNAME which was allocated to you with the hosting - provider. There is host HOST on which MySQL server is placed, login LOGIN to it , port PORT on which the server works, and also password PASS. Having all these data, it is possible to make dump (a dump, a copy) bases DBNAME so (it is carried out in unix shell):

> mysqldump-uLOGIN-PPORT-hHOST-pPASS DBNAME> dump.txt

After performance of the given command in a file dump.txt we will have copy of MySQL-base DBNAME. It will take place only in that case, certainly, if you will set all parameters correctly, according to adjustments of the hosting. At once it is necessary to say, that the program mysqldump makes a conclusion of results directly you on STDIN, that is, on the screen. It is necessary to redirect a conclusion to any file. For example, as in this case - "> dump.txt ". If to not make it, and the base big, you receive all those megabytes of the information which contain in her on the screen.

Let's a little tell about that does{makes} mysqldump. This program creates the script of restoration of your data. That is, the conclusion mysqldump is not any abstract and not readable binary data, and the intelligent text of the script. For example, if in your base there was a table test in which there was a field test2 with type of the data integer and the unique recording "1111" mysqldump will create approximately such script:

* MySQL dump 8.14

*

* Host: HOST Database: DBNAME

* --------------------------------------------------------

* Server version 3.23.39-log

*

* Table structure for table 'test'

*

CREATE TABLE test (

test2 int (11) default NULL

) TYPE=MyISAM;

*

* Dumping data for table 'test2'

*

INSERT INTO test2 VALUES ('1111');

Thus, mysqldump "will describe" all your tables and will create INSERT-commands for restoration of the data in tables. So, we redirect a conclusion mysqldump to a text file which we shall use then for restoration. We shall consider also this process - a reconstruction of base from a backup copy.

For restoration we shall use the standard program mysql which are included into the complete set of delivery MySQL alongside with mysqldump. We admit{allow}, at us is present backup in a file dump.txt. We need to restore it  in working base. For example, we have casually removed our database, and now we try to correct this ill luck. We do{make} so:

> mysql-uLOGIN-PPORT-hHOST-pPASS DBNAME <dump.txt

That is, we force to incorporate the mysql-client to the server and to execute the script which at us is present. After performance of this command in your base tables and the data from a backup copy will appear. Take into account that the data will be simply restored under the script from dump.txt. That is, if tables which are mentioned in a dump of base, already exist and have other structure, here obviously there will be a mistake. Simply see at the script and on working base and present, that you manually carry out commands from the script. If are sure, that all will be good - safely restore.

Let's consider more thin adjustments mysqldump:

- databases allows to make so, that mysqldump will switch on in the script of restoration of CREATE DATABASE command / *! 33333 IF NOT EXISTS*/DBNAME and USE DBNAME. It will allow to create working bases " from zero ". That is, without use - databases it is meant, that the user restores one database and obviously specifies, where it is necessary to place the restored data. If backup it is created with the purpose to make completely a working copy of the data, for example, on the other MySQL-server it is necessary to use this key;

- all-databases allows to make copies of all databases which exist on the given MySQL-server. If it is necessary to make copies only some bases, it is necessary to specify simply them through a blank by a call mysqldump from the command line (see above);

Key - help. The program mysqldump has set of versions. To see, what opportunities are supported particularly by your version, it is possible with the help of this key;

- add-drop-table - a key which will force mysqldump to add in the final script drop table command before creation of tables. It will allow to avoid some mistakes at restoration of base from a backup copy. Certainly, it is necessary to take into account that the tables which are taking place in a working copy (if tables with the same name exist in backup), before restoration from a backup copy will be removed from a basis and are recreated from backup;

- no-data. With the help of this key it is possible to make quickly a copy of structure of tables / bases without the data. For example, you have created the complex  table and would like to save its{her} structure, and the data which are in this table on the future, for you in a backup copy are not necessary;

- result-file =... - this key can be used for perenapravlenija a conclusion in a file. It is possible to use usual unix-perenapravlenie the command ">", and it is possible - this key. To whom that is pleasant;

Except for the listed keys mysqldump has and more a quantity of very useful opportunities which you can apply on circumstances. The full documentation on mysqldump is accessible on page http://www.mysql.com/doc/m/y/mysqldump.html. <http: // www.internet-technologies.ru/? url=http%3A%2F%2Fwww.mysql.com%2Fdoc%2Fm%2Fy%2Fmysqldump.html.>

One more very useful advice{council} on use mysqldump in khostingovoj to environment{Wednesday}. As a rule, at use of a hosting on the user some restrictions are imposed. For example, it is impossible to borrow{occupy} more quantity of physical memory (RAM, the RAM). mysqldump by default places all data received from the MySQL-server in memory, and then writes down all this on a disk. Accordingly, if the provider gives you to borrow{occupy}, for example, 30Mb memories, and the base which copy you do{make} with the help mysqldump, borrows{occupies} 50Mb, certainly, here there will be a mistake - mysqldump cannot fulfil correctly and will come to the end under abnormal condition about what to you informs. "To force" mysqldump to write the data at once on a disk, instead of to store{keep} them, let even temporarily, in memory, use a key - quick. It will solve a problem.

Automation of reserve copying

Now we shall think, as though to us to automate process of creation of backup copies of a database. So, there is a program - cron. She allows to start processes during time specified by the user or with the certain periodicity. At once we shall make a reservation - cron generally exists only under Unix so if you use for a hosting of OS Windows, consult with the hosting - provider how it is better to start processes during necessary time. And in general, perhaps, this item{point} will be interesting only to unix-users.

In unix shell it is started crontab-e and we create such rule of start of process of creation of copies of base:

00 * * * mysqldump-uLOGIN-PPORT-hHOST-pPASS DBNAME | gzip-c> ` date " + %Y-% m-% d " ` .gz

This command, being started from cron at midnight (00:00) each day, does{makes} a dump of your base DBNAME and arkhiviruet by his  archiver gzip in file - archive with a name corresponding to the current date. For example, if we do{make} dump on January, 3, 2002, the name of a file with archive will be 2002-01-03.gz. To receive files on which names it is possible to learn{find out} conveniently date of their creation, we use date command which is standard for all unix-systems. This command allows to set an any format of a conclusion of date, that we and used - date " + %Y-% m-% d ". We have placed this command in return unary inverted commas (backticks), that in unix shell forces to insert into the command (exaggerating) result of performance of other command.

We save a rule for cron and we wait for results. So, every day we shall have on a disk zaarkhivirovannuju a copy of our database. It is possible to find quickly the necessary archive under his  name and to restore that has deteriorated, for example. By the way, if you want to automate removal{distance} of old archives, try to use cron and find command which usually is in unix. Starting periodically find ~/katalog-JA-arkhivami-name "*.gz"-mtime +7, you will delete archives which "are more senior" than seven days. Read the documentation on find - she is accessible on man find command in unix shell.

If you have the machine constantly connected to the Internet, it is possible as on cron to copy created by you backup on it{her}. Certainly, provajderskaja the hosting - machine is very reliable piece. However, as they say, " protected the God protects ". Old as the world the true in the certain conditions can to help you also. Use for copying on other machine of ftp command and scp. Add their start in cron. If your machine supports connection under the report ssh, use secure copy the client for copying files - scp. Read the documentation on this command in man-page man scp. Provisional start: scp 2002-01-03.gz login@your.host.ru: - zakachivaem a file 2002-01-03.gz on the machine your.host.ru having authorized there under a login login.

The script mysqlhotcopy, written on language Perl, will help you to simplify creation of copies of databases and separate tables. Use of this script allows to make reserve copying is even more floppy.