How to do Backup and recovery in MySQL server?

[Lalana]

The backup and recovery data are, as always, key activities to ensure the security data from a DBMS. In particular, we provide through the backup saves the contents of the database at a given moment, while the recovery is the process by which we restore the contents of the database following a corruption. But I am having less knowledge about doing the backup and recovery. So thought to get some notes from your side. Please tell me how to do Backup and recovery in MySQL server? It would be much helpful if you provide some appropriate examples. Waiting for your replies. Thanks in Advance.

[GracieSingh]

To be able to make full recovery in case of problems, you must run the MySQL server with logs generated (option - log-bin server), otherwise, everything will be possible, if there are problems, it will restore Data last backup, thus losing its subsequent amendments. They are still quite rare cases where it is necessary to use backups to recover data: in general it is hardware failure or power supply system, or crash the operating system or file system. In these cases, however, it is possible that MySQL is able to restart correctly.

[MaggieK]

The first security measure to be taken to our data, therefore, is to make regular backups. The best way to do this is to use the mysqldump program, which allows us to save our data very simply.

mysqldump - single-transaction - all-databases> filename

With this command we get, in file filename, a list of INSERT statements that allow us to restore the entire contents of the database. The file is in text format, so we can open it and check the contents. The - single-transaction is useful if your database contains InnoDB tables, as it allows a consistent view of those tables, that is not affected by any updates made during the backup operation. As for non-transactional tables (MyISAM) is not possible to have this guarantee, then you must make sure that the tables are not changed during the backup.

[Savannah87]

If you want to backup a single database instead of the entire server can do it by omitting the - all-databases and presented in its place the database name that interests us. The moment we want to reload the server backup content made, just to read the backup file to the mysql client:

mysql <file_name 
mysql db_name <file_name

The second syntax is used when the backup contains the data of a single database, in this case because there are no references to back up the database used. Imagine we take to ensure the possibility, in the event of a crash, a complete recovery of data up to the time of failure. First we have to worry about then enable the log files. To do this we must add to the configuration file in the mysqld section, the log-bin option:
log-bin = path / basename
The server will then create log files in the specified directory as the file name and use the basename adding, as an extension, a 6-digit sequential number that increments with each new log file. You can also omit the indication of the path and basename, simply use the log-bin, in which case the log will be created in the MySQL data directory, and name of the file will be used 'computer_name-bin'. Ideally, however, is that the logs are on a drive other than the data, so that any hardware failure will not endanger their availability.

[Aashirya]

Each time you restart the MySQL server creates a new log file, incrementing the sequence, it is also possible to force this through education FLUSH LOGS. For better management of backup in this case we use a wider form of the command to execute mysqldump when we make the full backup of the data:

mysqldump - single-transaction - flush-logs - master-data = 2 - all-databases - delete-master-logs> filename

As you can see we've added two options: with a flush-logs MySQL creates a new log file, which will be the first to be used when restoring data from this backup, with master-data = 2 on the backup file is written ( comment in the form of) the name of the log file you just created, then the delete-master-logs option deletes the previous log, no longer needed.

[Kalanidhi]

In a disaster, then, we first restore the primary backup as above, then we'll log files through the program mysqlbinlog:

mysqlbinlog nome_file_log nome_file_log | mysql 
mysqlbinlog nome_file_log nome_file_log> filename

Use this command to give input in two log files in the program: in the first case we denote the direct output of the mysql client to rerun the instructions stored in the logs (remember to add the data for the connection!), And then rebuild the situation of the database. It is important, if the log files to be processed is more than one, re-execute them all with a single instruction. In the second case we write the output to a text file so you can then view and review. When reading binary logs we can define, in two ways, instructions to be taken into account: we can use a start and end timestamp, or positions on the file.

mysqlbinlog - start-date = "11/16/2010 9:55:00" - stop-date = "16/11/2010 10:00:00" file_name 
mysqlbinlog - start-position = "2345" - stop-position = "4567" file_name

In the first case to say mysqlbinlog only read the instructions included between 9:55 and 10:00 on the date indicated, the second one showing their positions in the log file to reference. In both cases we can also use one of two options (beginning or end). To find out which positions refer, we can examine the program's output to the text file, which shows for each instruction stores the start position and end.

[Quattro]

You are now responsible for maintenance of MyISAM tables, for which we have the program myisamchk. However, this program should be used in server not active, because it can cause problems when trying to access the tables in conjunction with the MySQL server: our advice is to use in its place, the SQL statements that perform the same functions:

• CHECK TABLE table [option]
• REPAIR TABLE table [option]
• OPTIMIZE TABLE table

CHECK TABLE is used to check the status of a table and see if there are problems. Possible option values are: QUICK, FAST, MEDIUM, EXTENDED, CHANGED, these options are in ascending order of complexity, and provide a control more and more accurate but also slower. The output of CHECK TABLE would normally indicate that the table is OK, otherwise you need to repair it with REPAIR TABLE. The advice is to start with the first and test later only in the event of failure. Again we will have an output that tells us the outcome of the operation. The OPTIMIZE TABLE statement is, finally, to optimize the space requirements of a table: it is good to run especially when many were made or canceled many updates on a table. Another alternative to the SQL statements for the maintenance of the tables is the client program mysqlcheck, unlike that myisamchk can be performed safely even at server start.