1026 Simple ideas for backup and restore

--Go from blog http://blog.csdn.net/abaowu/article/details/611386

--the whole thinking forward and reverse

--Backup ideas

--Full backup mysqldump > file path
--incremental backup take advantage of Linux copy mysqlbin-log files, copy those new files, then use to perform the restore operation

--Recovery ideas

Similarly, you can use full and incremental backups for recovery
Mysql-u Root-p < C:\backup.sql--full-scale backup

--Pipeline command passed to MySQL inside
Mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql-bin.000053" | mysql-uroot-p111111 back_db;--Incremental Backup
Mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql-bin.000054" | mysql-uroot-p111111 back_db;--Incremental Backup
Mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql-bin.000055" | mysql-uroot-p111111 back_db;--Incremental Backup

Suitable for objects

This article is tested under Linux under MySQL version 4.1.14 and may be appropriate for MySQL 4.0,5.0 and other versions.
　　
This article is suitable for MySQL that does not have replication enabled, and if replication is initiated, it may not be necessary to take such a backup strategy or to modify the relevant parameters.
　　
Each person's backup strategy may be different, so please revise according to the actual situation, do extrapolate, do not copy copy, may cause unnecessary loss.
　　
I hope you understand what this script is going to do!
　　
　　
Script Description

All data is backed up every 7 days, backed up binlog every day, i.e. incremental backups.
　　
(If the data is small, backup the full data once a day, it may not be necessary to do an incremental backup)
　　
The author is not familiar with shell scripts, so many places are very stupid:)
　　

Open Bin Log

In MySQL version 4.1, the default is only the error log, no other logs. You can open the bin log by modifying the configuration. There are many methods, one of which is the mysqld part in/ETC/MY.CNF:
　　
[Mysqld]
Log-bin
　　
The primary role of this log is incremental backup or replication (there may be other uses).
　　
If you want an incremental backup, you must open this log.
　　
For MySQL with frequent database operations, this log can become large and may be multiple.
　　
After flush-logs in the database, or using Mysqladmin,mysqldump to invoke Flush-logs and use the parameter delete-master-logs, these log files disappear and generate a new log file (initially empty).
　　
So if you never back up, it may not be necessary to turn on the log.
　　
A full backup can be called Flush-logs, and the flush-logs is preceded by an incremental backup to back up the latest data.
　　
　　
Full backup Script
　　
If the database data is more, we usually a few days or a week to back up the data, so as not to affect the application to run, if the amount of data is small, then one day backup does not matter.
　　
Download assuming we have a large amount of data, the backup script is as follows: (refer to a MySQL backup script on the network, thank:))

#!/bin/sh
　　
# MySQL Data backup script
# by Scud Http://www.jscud.com
# 2005-10-30
#
# Use mysqldump--help,get more detail.
#
　　
Bakdir=/backup/mysql
Logfile=/backup/mysql/mysqlbak.log
　　
Date= ' Date +%y%m%d '
　　
echo "" >> $LogFile
echo "" >> $LogFile
echo "-------------------------------------------" >> $LogFile
echo $ (date + "%y-%m-%d%h:%m:%s") >> $LogFile
echo "--------------------------" >> $LogFile
　　
　　
CD $BakDir
　　
dumpfile= $DATE. sql
gzdumpfile= $DATE. sql.tgz
　　
Mysqldump--quick--all-databases--flush-logs
--delete-master-logs--lock-all-tables
> $DumpFile
　　
echo "Dump done" >> $LogFile
　　
Tar czvf $GZDumpFile $DumpFile >> $LogFile 2>&1
　　
echo "[$GZDumpFile]backup success!" >> $LogFile
　　
Rm-f $DumpFile
　　
#delete previous daily backup files: A file with an incremental backup that deletes an incremental backup after a full backup.
CD $BakDir/daily
　　
Rm-f *
　　
CD $BakDir

echo "Backup done!"
　　
echo "Please Check $BakDir directory!"
　　
echo "Copy it to your local disk or FTP to somewhere!!!"
　　
Ls-al $BakDir

The above script backs up MySQL to the local/backup/mysql directory, and the incremental backup files are placed in the/backup/mysql/daily directory.
　　
Note: The script above does not transfer the backed-up files to other remote computers, nor does it delete the backup files a few days ago: The user is required to add the relevant script, or manual operation.
　　

Incremental backup
　　
Incremental backups have a smaller amount of data, but to operate on a full backup, users can balance time and cost by choosing the best way to benefit themselves.
　　
Incremental backups Use bin log, which is the following script:

#!/bin/sh
　　
#
# MySQL Binlog backup script
#
　　
/usr/bin/mysqladmin Flush-logs
　　
Datadir=/var/lib/mysql
Bakdir=/backup/mysql/daily
　　
# # #如果你做了特殊设置, modify here or modify the row where this variable is applied: default to Machine name, MySQL default is to take machine name
Hostname= ' Uname-n '
　　
CD $DATADIR
　　
Filelist= ' Cat $HOSTNAME-bin.index '

# #计算行数, which is the number of files
Counter=0
For file in $FILELIST
Do
Counter= ' expr $COUNTER + 1 '
Done
　　
Nextnum=0
For file in $FILELIST
Do
Base= ' basename $file '
nextnum= ' expr $NextNum + 1 '
If [$NextNum-eq $COUNTER]
Then
echo "Skip Lastest"
Else
dest= $BAKDIR/$base
if (test-e $dest)
Then
echo "Skip exist $base"
Else
echo "Copying $base"
CP $base $BAKDIR
Fi
Fi
Done
　　
echo "Backup MySQL binlog OK"

The incremental backup script is the pre-backup flush-logs,mysql will automatically put the in-memory log into the file, and then generate a new log file, so we only need to back up the previous few, that is, do not back up the last one.
Since the last backup to this backup may also have more than one log file generation, so to detect the file, if it has been backed up, there is no backup.
　　
　　Note : Similarly, users also need to send their own remote, but do not need to delete, the full backup after the program is automatically generated.
　　
Access settings

The script is finished, in order to allow the script to run, also need to set the corresponding user name and password, mysqladmin and mysqldump are required user name and password, of course, can be written in the script, but it is not easy to modify, suppose we use the root user of the system to run this script, Then we need to create a. my.cnf file in the/root (that is, the root user's home directory), which reads as follows

[Mysqladmin]
Password =password
user= Root
[Mysqldump]
User=root
Password=password

Note : Setting this file is only readable by root. (chmod my.cnf)
　　
This file indicates that the program uses the root user of MySQL to back up the data, and the password is the corresponding setting. This will not require the user name and password to be written in the script.
　　

Run automatically
　　
In order for the backup program to run automatically, we need to add it to crontab.
　　
There are 2 ways, one is to put the script according to their own choice into the/etc/cron.daily,/etc/cron.weekly directory.
One is to use CRONTAB-E to put into the root user's scheduled tasks, such as full backups run every Sunday 3 o'clock in the morning, daily backups run every Monday-Saturday 3 o'clock in the morning.
　　
For specific use, please refer to Crontab's help.

1026 Simple ideas for backup and restore