备份mysql_备份MySQL

备份mysql

There are several ways to backup MySQL data, however, automation certainly makes the process easier. Insuring your backed up data is stored remotely further supports your business continuity in the event of a need to restore data onto new systems based on hardware failure, etc.

有多种备份MySQL数据的方法，但是，自动化无疑使该过程更容易。 如果需要根据硬件故障等将数据还原到新系统上，确保备份的数据可以远程存储进一步支持您的业务连续性。

Having spent some time murking through my network (not needing to reinvent the wheel to research an efficient backup procedure), I stumbled onto some backup scripts from PJ Doland.

花了一些时间在我的网络中苦思冥想(不需要重新发明轮子来研究有效的备份过程)，我偶然发现了一些来自PJ Doland的备份脚本。

PJ (Patrick) is a web designer in the Washington DC area with former ties to the CATO Institute web site and was willing to share two scripts he is using. I liked these scripts as they are simple, and include the functionality to automate dropping the backups on a remote server for business continuity.

PJ(Patrick)是华盛顿特区的一名网页设计师，与CATO Institute网站有渊源，并愿意分享他正在使用的两个脚本。 我喜欢这些脚本，因为它们很简单，并且包含自动删除远程服务器上的备份以实现业务连续性的功能。

The first is for use as a standard MySQL backup and is found below:

第一个用作标准MySQL备份，位于以下位置：

#!/bin/bash

#!/bin/bash

##################################### ### MySQL Configuration Variables ### #####################################

###################################### MySQL配置变量### #### ################################

# MySQL Hostname DBHOST='localhost'

＃MySQL主机名DBHOST ='localhost'

# MySQL Username DBUSER='root'

＃MySQL用户名DBUSER ='root'

# MySQL Password DBPASSWD='password'

＃MySQL密码DBPASSWD ='密码'

##################################### ### FTP Configuration Variables ##### #####################################

###################################### FTP配置变量##### ## ##################################

# FTP Hostname FTPHOST='www.example.com'

＃FTP主机名FTPHOST ='www.example.com'

# FTP Username FTPUSER='username'

＃FTP用户名FTPUSER ='用户名'

# FTP Password FTPPASSWD='password'

＃FTP密码FTPPASSWD ='密码'

# Local Directory for Dump Files LOCALDIR=/path/to/local/directory/

＃转储文件的本地目录LOCALDIR = / path / to / local / directory /

# Remote Directory for Offsite Backup REMOTEDIR=/path/to/remote/directory/

＃异地备份的远程目录REMOTEDIR = / path / to / remote / directory /

# Prefix for offsite .tar file backup TARPREFIX=db1

＃异地.tar文件备份的前缀TARPREFIX = db1

##################################### ### Edit Below If Necessary ######### #####################################

######################################如有必要，请在下面编辑###### ### ####################################

cd $LOCALDIR SUFFIX=`eval date +%y%m%d`

cd $ LOCALDIR SUFFIX =`评估日期+％y％m％d`

DBS=`mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"`

DBS =`mysql -u $ DBUSER -p $ DBPASSWD -h $ DBHOST -e“显示数据库”

for DATABASE in $DBS do if [ $DATABASE != "Database" ]; then FILENAME=$SUFFIX-$DATABASE.gz mysqldump -u$DBUSER -p$DBPASSWD -h$DBHOST $DATABASE | gzip --best > $LOCALDIR$FILENAME fi done

对于$ DBS中的DATABASE，如果[$ DATABASE！=“ Database”]; 然后FILENAME = $ SUFFIX- $ DATABASE.gz mysqldump -u $ DBUSER -p $ DBPASSWD -h $ DBHOST $ DATABASE | gzip --best> $ LOCALDIR $ FILENAME文件已完成

chmod 400 $LOCALDIR*.gz

chmod 400 $ LOCALDIR * .gz

tar -cf $TARPREFIX-$SUFFIX.tar $SUFFIX-*.gz

tar -cf $ TARPREFIX- $ SUFFIX.tar $ SUFFIX-*。gz

ftp -n $FTPHOST

ftp -n $ FTPHOST

It is a straightforward script, uses mysqldump and tar/gzips the files prior to using ftp to move the data to a remote server. This script and the one below can easily be modified to use scp if you prefer running the transfer over ssh.

它是一个简单的脚本，在使用ftp将数据移至远程服务器之前，先使用mysqldump和tar / gzips文件。 如果您更喜欢通过ssh运行传输，则可以轻松地将此脚本以及下面的脚本修改为使用scp。

The second script is updated to use mysqlhotcopy and is specifically for use with ISAM/MYISAM tables only. This script below will NOT work with InnoDB tables.

第二个脚本已更新为使用mysqlhotcopy，并且专门用于ISAM / MYISAM表。 下面的脚本不适用于InnoDB表。

#!/bin/bash

#!/bin/bash

### Configuration Variables

###配置变量

DBHOST='localhost' DBUSER='root' DBPASSWD='password' FTPHOST='ftp.example.com' FTPUSER='username' FTPPASSWD='password' LOCALDIR=/path/to/local/ REMOTEDIR=/path/to/local/ TARPREFIX=db1

DBHOST ='本地主机'DBUSER ='root'DBPASSWD ='密码'FTPHOST ='ftp.example.com'FTPUSER ='用户名'FTPPASSWD ='密码'LOCALDIR = /路径/到/本地/ REMOTEDIR = /路径/到/本地/ TARPREFIX = db1

### Do not edit anything below this line

###不要在此行下方编辑任何内容

cd $LOCALDIR SUFFIX=`eval date +%y%m%d`

cd $ LOCALDIR SUFFIX =`评估日期+％y％m％d`

DBS=`mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"`

DBS =`mysql -u $ DBUSER -p $ DBPASSWD -h $ DBHOST -e“显示数据库”

for DATABASE in $DBS do if [ $DATABASE != "Database" ]; then FILENAME=$SUFFIX-$DATABASE.tar.gz mysqlhotcopy -u $DBUSER -p $DBPASSWD $DATABASE $LOCALDIR tar -czf $LOCALDIR$FILENAME $LOCALDIR$DATABASE rm -rf $LOCALDIR$DATABASE rm -rf $LOCALDIR$DATABASE-replicate fi done

对于$ DBS中的DATABASE，如果[$ DATABASE！=“ Database”]; 然后FILENAME = $ SUFFIX- $ DATABASE.tar.gz mysqlhotcopy -u $ DBUSER -p $ DBPASSWD $ DATABASE $ LOCALDIR tar -czf $ LOCALDIR $ FILENAME $ LOCALDIR $ DATABASE rm -rf $ LOCALDIR $ DATABASE rm -rf $ LOCALDIR $ DATABASE -复制完成

chmod 400 $LOCALDIR*.tar.gz

chmod 400 $ LOCALDIR * .tar.gz

tar -cf $TARPREFIX-$SUFFIX.tar $SUFFIX-*.tar.gz

tar -cf $ TARPREFIX- $ SUFFIX.tar $ SUFFIX-*。tar.gz

ftp -n $FTPHOST

ftp -n $ FTPHOST

It is recommended to use 700 permissions on these shell scripts to limit them to root user as they do contain substantial information on user name, password, and path to multiple servers once they are updated with your information.

建议对这些shell脚本使用700个权限，以将它们限制为root用户，因为它们确实包含有关用户名，密码以及在更新了您的信息后到多个服务器的路径的大量信息。

They can then be added to cron to run on a schedule of your preference.

然后可以将它们添加到cron中，以按照您的首选时间表运行。

翻译自: https://www.sitepoint.com/backing-up-mysql/

备份mysql