MySQL database backup and recovery under Linux all tips

Many users have experienced the loss of valuable data, and as a large amount of data is deposited into the MySQL database, coupled with the erroneous use of the drop Database command, system crashes or edits to the table structure can lead to catastrophic losses. Therefore, it is necessary to make a backup of the MySQL database in case of an unexpected time recovery.

a simple local backup using MySQL-related commands

1 mysqlldump Command

Mysqldump is a SQL-level backup mechanism that guides data tables into SQL script files and is relatively appropriate for upgrading between different MySQL versions, which is also the most commonly used backup method.

Backing up with mysqldump is simple, if you want to back up the database "Db_backup", use the command:
#mysqldump –u-p phpbb_db_backup >/usr/backups/mysql/db_backup2008-1-6.sql
You can also compress backup files by using the gzip command:
#Mysqldump Db_backup | gzip >/usr/backups/mysql/db_backup2008-1-6.sql.gz (SQL generated after backup does not contain the built-in database statements!) )
Back up only a few database tables that are frequently updated:
# # Mysqldump sample_db Articles comments Links >/usr/backups/mysql/sample_db.art_comm_lin.2008-1-6.sql
The above command backs up the articles, comments, and links three tables.

To recover data using commands:
#mysql –u-p db_backup </usr/backups/mysql/db_backup2008-1-6.sql
Note You must ensure that the database is running when you use this command.

2 Using the SOURCE syntax

In fact, this is not the standard SQL syntax, but the functionality provided by the MySQL client, such as:
# source/tmp/db_name.sql;
The absolute path to the file needs to be specified, and it must be a file that the Mysqld run user (for example, nobody) has permission to read.

3 mysqlhotcopy Backup

mysqlhotcopy can only be used for backup MyISAM, and can only be run on Linux and UNIX and NetWare systems. Mysqlhotcopy supports one-time copying of multiple databases while also supporting regular expressions. Here are a few examples:
#mysqlhotcopy-h=localhost-u=goodcjh-p=goodcjh db_name/tmp
(Copy the database directory db_name to/tmp)
Note that to use mysqlhotcopy, you must have SELECT, RELOAD (to perform FLUSH TABLES) permissions, and you must have permission to read the Datadir/db_name directory.

To Restore a database method:

Mysqlhotcopy back up is the entire database directory, the use can be copied directly to the mysqld specified directory (in this case,/usr/local/mysql/data/) directory, but also to pay attention to the issue of permissions, In addition, you should first delete the old copy of the database as follows:

#/bin/rm-rf/mysql-backup/**//*old
three steps to turn off MySQL server, copy files, and query start MySQL server:
#/etc/init.d/mysqld Stop
stopping MySQL: [OK]
# cp-af/mysql-backup/**//*/var/lib/mysql/
#/etc/init.d/mysqld Start
starting MySQL: [OK]
#chown-R nobody:nobody/usr/local/mysql/data/(change the owner of the Db_name directory to mysqld running user)
Second, using network backup

It is not safe to put MySQL data on a single computer, so you should back up your data to other Linux computers on your local area network. Assume that the MySQL server IP address is: 192.168.1.3. The remote computer IP address of the local area network using Linux is 192.168.1.4; Similar to the Windows network share, UNIX (Linux) systems also have their own network shares, that is, NFS (Network File System), on the Linux client Hook (Mount) Before NFS disk sharing, you must first configure the NFS server side. The Linux system NFS server configuration method is as follows:
　　　
(1) Modify/etc/exports, add shared directory
/export/home/sunky 192.168.1.4 (rw)
/export/home/sunky1 * (rw)
/export/home/sunky2 linux-client (rw)
Note: sunky, Sunky1, sunky2 in the/export/home/directory are the directories that are ready to be shared, 10.140.133.23, *, and linux-client are the IP addresses or host names that are allowed to hook up this shared Linux client. If you want to use hostname linux-client You must add the Linux-client host IP definition in the server host/etc/hosts file. The format is as follows:
192.168.1.4 linux-client
If you modify the/etc/export file to add new shares, you should stop the NFS service before starting the NFS service to make the newly added share work. The same effect can be achieved using the command EXPORTFS-RV. Linux Client mount (Mount) NFS sharing for other Linux systems or UNIX systems. Here we assume that 192.168.1.4 is the host IP address of the NFS server, and of course the hostname can be used, but the server-side IP definition must be added to the native/etc/hosts file. /export/home/sunky the directory that is shared with the server. This allows the Linux client to access files that are shared on NFS by other Linux systems or UNIX systems via/MNT/NFS.

backing up MySQL data to a remote computer using Linux requires the NFS protocol (Network file System) installed on both ends, and the remote NFS computer after installing the NFS protocol to modify the configuration file:/etc/exports, add a line:
/usr/backups/mysql/192.168.1.4 (rw, No_root_squash)
represents the/usr/backups/mysql/directory share. This directory has read and write access to the remote root user. Save the NFS configuration file, and then use the command:
#exportfs-a–r
then restart the NFS service:
#service nfsd Start
after setting up the remote computer, create a backup_share directory in the MySQL server/mnt directory:
#mkdir/mnt/backup_share
mount the/usr/backups/mysql/directory of the remote Linux computer to the/mnt/backup_share directory of the MySQL server:
# mount-t NFS 192.168.1.4:/usr/backups/mysql/mnt/backup_share
when the directory is mounted, simply enter the/mnt/backup_share directory, which is equal to the IP address: 192.168.1.4 The NFS computer's/usr/backups/mysql directory. Use mysqldump to back up "phpbb_db_backup" to a remote computer:
# mysqldump db_backup >/mnt/backup_share/db_backup2008-1-6.sql
How to Automate network backups:

The program on the Linux server updates the MySQL database every day, so it reminds me to write a shell script, combined with crontab, to backup the database regularly. Create a shell script: sample_db_backup.sh
# at the very end the $ (date +%f) automatically add backup date
mysqldump-u <username>-P <password>-H

#un-mount the filesystem
Umount/mnt/backup_share
# mount \u2013o Soft 192.168.1.4:/archive/mnt/backup_share
Description: An important parameter for the Mount NFS server: Hard mount or soft (soft) mount.

Hard Mount: The NFS client continually tries to connect to the Server for NFS (in the background, usually does not give any hint information) until it is mounted.
Soft Mount: The connection to the NFS server is attempted in the foreground, and is the default connection method. When an error message is received, the mount attempt is terminated and the relevant information is given.

The question of whether to use a hard mount or a soft mount depends largely on what information you are accessing. For example, when you want to look at the video files of an NFS server, you never want the system to output a large amount of error messages due to unexpected situations (such as a sudden change in network speed), and if you use a hard mount at this point, the system waits until it can reconnect to the NFS server. In addition, if it is non-critical data, you can also use soft mount mode, such as FTP some data, so that the remote machine temporarily connected or closed when you do not suspend your session process.

The following script file permissions are established: chmod +x./sample_db_backup.sh

then use the Add this script to the/etc/crontab Timer task:
5 * * 0 mysql/home/mysql/sample_db_backup.sh
Well, every Sunday 5:01 the system will automatically run the sample_db_backup.sh file to back up the MySQL database over the network.

Three, real-time recovery M y S Q L data method

mysqldump is a very useful tool when backing up MySQL data and table structures. However, typically, backups are usually only once a day, or at a specific interval. If data is lost within a period of time when the backup is complete, it is likely that the data will not be recoverable. Is there any way to protect the data in real time? In fact, there are several ways to achieve the real-time protection of MySQL database. One of these is described here, even with binary logs for data recovery.

1 Setting the binary log method

To recover data from a binary log, you need to know the path and file name of the current binary log file. You can typically find the path from the options file (that is, my.cnf or My.ini, depending on your system). If it is not included in the options file, it can be given as an option on the command line when the server is started. The option to enable binary logging is--log-bin. To determine the file name of the current binary log file, enter the following MySQL statement:

# SHOW BINLOG EVENTS \g
2 The simplest data recovery

backing up and running the binary log every day is really a good way to recover data in a MySQL server. For example, you can use mysqldump to back up data at night, and if for some reason data is lost for a certain period of time after the data backup is complete, you can use the following method to recover it. First, stop the MySQL server, and then use the following command to restart the MySQL server. This command will guarantee that the only person who can access the database server is:
#/etc/init.d/mysqld Stop
stopping MySQL: [OK]
# mysqld--socket=/tmp/mysql_restore.sock--skip-networking
here, a socket option will name a different socket file for the U n i x system. Once the server is under exclusive control, you can safely operate on the database without worrying about the user attempting to access the database during data recovery, causing more trouble. The first step in recovery is to recover the dump files that were backed up at night:
#mysql-u root-pmypwd--socket=/tmp/mysql_restore.sock </var/backup/20080120.sql
This command restores the contents of the database to the content of the backup that was just completed at night. To restore the database transaction after the dump file was created, you can use the Mysqlbinlog tool. If the log is flush when you perform a backup operation every night, you can use the following command-line tool to recover the entire binary log file:
mysqlbinlog/var/log/mysql/bin.123456 \
| mysql-u root-pmypwd--socket=/tmp/mysql_restore.sock
3 Recovery for a certain point in the question

for MySQL 4.1.4, you can specify the start and end time of the DateTime format in the Mysqlbinlog statement through the--start-date and--stop-date options. Assuming that the SQL statement executed by the user on 2008-1-22 10 o'clock in the morning deletes a large data table, it can be recovered using the following command: To restore the table and data, you can restore the previous night backup and enter:

#mysqlbinlog--stop-date= "2008-1-22 9:59:59"
/var/log/mysql/bin.123456 |
mysql-u root-pmypwd \
--socket=/tmp/mysql_restore.sock
#mysql-U root-pmypwd
The statement restores data that precedes any given stop-date date. If an error operation is not found after several hours of execution of an SQL statement, some data that is entered after the recovery may be required. At this point, you can also complete this function via Mysqlbinlog:
#mysqlbinlog--start-date= "2008-1-22 10:01:00" \
/var/log/mysql/bin.123456 \
| mysql-u root-pmypwd \
--socket=/tmp/mysql_restore.sock
#mysql-U root-pmypwd
in that row, the SQL statement that is logged in from 10:01 runs. Combined execution of the previous night's dump file and two lines of Mysqlbinlog can restore all data to one second before 10:00. You should check the logs to make sure the time is correct.

4 using position for recovery

You can also specify a log location without specifying a date and time, and using the Mysqlbinlog options--start-position and--stop-position. They function the same as the start and End Date option, and the difference is that the location number from the log is given. Using the log location is a more accurate method of recovery, especially when many transactions occur simultaneously due to destructive SQL statements. To determine the location number, you can run Mysqlbinlog to find the time range for the transaction that you did not expect, but you should re-point the result to a text file for review. The action commands are:
mysqlbinlog--start-date= "2005-04-20 9:55:00"--stop-date= "2005-04-20 10:05:00"
/var/log/mysql/bin.123456 >/tmp/mysql_restore.sql
the command will create a small text file in the/tmp directory, which will display the SQL statement when the wrong SQL statement was executed. You can use VI or gedit text editor to open the file and look for statements you don't want to repeat. If the location number in the binary log is used to stop and resume the recovery operation, comments should be made. Use Log_pos plus a number to mark the position. After recovering the previous backup file using the location number, you should enter the following from the command line:
mysqlbinlog--stop-position= "368312"/var/log/mysql/bin.123456
| mysql-u root-pmypwd
mysqlbinlog--start-position= "368315"/var/log/mysql/bin.123456
| mysql-u root-pmypwd
the 1th row above reverts to all transactions until the stop location. The next line restores all transactions from the given starting position until the end of the binary log. Because the output of Mysqlbinlog includes a set TIMESTAMP statement before each SQL statement is recorded, the recovered data and the associated MySQL log will react to the original time of the transaction execution.

5 Other methods

for a standard installation of MySQL, full recovery of lost data at any moment via binary log is a very simple and fast thing. Of course, there are other ways to protect your data if you can't tolerate the requirements of using this method, such as locking other users when you're doing a recovery operation, and so on:

using MySQL replication technology
http://dev.mysql.com/doc/mysql/en/replication.html
using MySQL Clustering technology
http://dev.mysql.com/doc/mysql/en/ndbcluster.html

Reference Documents:
backing up your MySQL data (author: Mayank Sharma)
http://www.linux.com/articles/41313
point-in-time Data Recovery (Russell Dyer)
http://dev.mysql.com/tech-resources/articles/point_in_time_recovery.html

MySQL database backup and recovery under Linux all tips