MySQL备份还原——AutoMySQLBackup介绍

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL备份还原——AutoMySQLBackup介绍，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11247字，纯文字阅读大概需要17分钟。

内容图文

automysqlbackup

automysqlbackup.conf

CHANGELOG

install.sh

README

LICENSE

automysqlbackup: AutoMySQLBackup的应用程序脚本

automysqlbackup.conf ： AutoMySQLBackup的配置文件范本

CHANGELOG： AutoMySQLBackup功能添加、Bug修复历史记录

install.sh： AutoMySQLBackup的安装脚本

README： AutoMySQLBackup的声明、安装、使用说明文档

建议在安装前看一下README文件，里面有介绍如何安装、配置automysqlbackup。AutoMySQLBackup提供了自动和手动安装配置两种功能（如下所示）

自动安装配置

1：运行脚本install.sh安装配置AutoMySQLBackup

[root@DB-Server tmp]# cd automysqlbackup

[root@DB-Server automysqlbackup]# ./install.sh 

### Checking archive files for existence, readability and integrity.

automysqlbackup ... exists and is readable ... md5sum okay :)

automysqlbackup.conf ... exists and is readable ... md5sum okay :)

README ... exists and is readable ... md5sum okay :)

LICENSE ... exists and is readable ... md5sum okay :)

Select the global configuration directory [/etc/automysqlbackup]: 

Select directory for the executable [/usr/local/bin]: /usr/bin

### Creating global configuration directory /etc/automysqlbackup:

success

### Copying files.

if you are running automysqlbackup under the same user as you run this install script,

you should be able to access it by running ‘automysqlbackup‘ from the command line.

if not, you have to check if ‘echo $PATH‘ has /usr/bin in it

Setup Complete!

[root@DB-Server automysqlbackup]# 

如下所示，你会发现install.sh脚本将automysqlbackup.conf、 LICENSE、 README三个文件拷贝到/etc/automysqlbackup/目录下，并复制了

automysqlbackup.conf文件生成了myserver.conf

[root@DB-Server ~]# cd /etc/automysqlbackup/

[root@DB-Server automysqlbackup]# ls

automysqlbackup.conf LICENSE myserver.conf README

[root@DB-Server automysqlbackup]#

2：配置自己的AutoMySQLBackup的配置文件myserver.conf

默认情况下，这些AutoMySQLBackup的参数都是注释的，你需要取消注释，给予相关参数配置信息，例如使用那个账号做备份、账号密码、

# Username to access the MySQL server e.g. dbuser

CONFIG_mysql_dump_username=‘root‘    --备份账号

# Password to access the MySQL server e.g. password

CONFIG_mysql_dump_password=‘123456‘

# Host name (or IP address) of MySQL server e.g localhost

CONFIG_mysql_dump_host=‘localhost‘

# "Friendly" host name of MySQL server to be used in email log

# if unset or empty (default) will use CONFIG_mysql_dump_host instead

#CONFIG_mysql_dump_host_friendly=‘‘

# Backup directory location e.g /backups

CONFIG_backup_dir=‘/u03/mysqlbackup/‘

....................................................

下面列出了部分主要的参数设置，具体可以参考automysqlbackup.conf 或 myserver.conf里面的参数的描述

README里面关于配置参数的一个参考样本，一般参数设置应该根据具体情况也业务需求来合理设定。

Default configuration

CONFIG_configfile="/etc/automysqlbackup/automysqlbackup.conf"

CONFIG_backup_dir=‘/var/backup/db‘

CONFIG_do_monthly="01"

CONFIG_do_weekly="5"

CONFIG_rotation_daily=6

CONFIG_rotation_weekly=35

CONFIG_rotation_monthly=150

CONFIG_mysql_dump_usessl=‘yes‘

CONFIG_mysql_dump_username=‘root‘

CONFIG_mysql_dump_password=‘‘

CONFIG_mysql_dump_host=‘localhost‘

CONFIG_mysql_dump_socket=‘‘

CONFIG_mysql_dump_create_database=‘no‘

CONFIG_mysql_dump_use_separate_dirs=‘yes‘

CONFIG_mysql_dump_compression=‘gzip‘

CONFIG_mysql_dump_commcomp=‘no‘

CONFIG_mysql_dump_latest=‘no‘

CONFIG_mysql_dump_max_allowed_packet=‘‘

CONFIG_db_names=()

CONFIG_db_month_names=()

CONFIG_db_exclude=( ‘information_schema‘ )

CONFIG_mailcontent=‘log‘

CONFIG_mail_maxattsize=4000

CONFIG_mail_address=‘root‘

CONFIG_encrypt=‘no‘

CONFIG_encrypt_password=‘password0123‘

3：运行备份脚本并检查备份

[root@DB-Server ~]# /usr/bin/automysqlbackup /etc/automysqlbackup/myserver.conf

Parsed config file "/etc/automysqlbackup/automysqlbackup.conf"

# Checking for permissions to write to folders:

base folder /u03 ... exists ... ok.

backup folder /u03/mysqlbackup/ ... exists ... writable? yes. Proceeding.

checking directory "/u03/mysqlbackup//daily" ... exists.

checking directory "/u03/mysqlbackup//weekly" ... exists.

checking directory "/u03/mysqlbackup//monthly" ... exists.

checking directory "/u03/mysqlbackup//latest" ... exists.

checking directory "/u03/mysqlbackup//tmp" ... exists.

checking directory "/u03/mysqlbackup//fullschema" ... exists.

checking directory "/u03/mysqlbackup//status" ... exists.

# Testing for installed programs

mysql ... found.

mysqldump ... found.

gzip ... found.

# Parsing databases ... done.

======================================================================

AutoMySQLBackup version 3.0

http://sourceforge.net/projects/automysqlbackup/

Backup of Database Server - localhost

Databases - mysql,performance_schema,test

Databases (monthly) - mysql,performance_schema,test

======================================================================

======================================================================

Dump full schema.

Rotating 4 month backups for 

======================================================================

======================================================================

Dump status.

Rotating 4 month backups for 

======================================================================

Backup Start Time Sat Jul 11 22:16:32 CST 2015

======================================================================

Daily Backup ...

Daily Backup of Database ( mysql )

Rotating 6 day backups for mysql

----------------------------------------------------------------------

Daily Backup of Database ( performance_schema )

Rotating 6 day backups for performance_schema

----------------------------------------------------------------------

Daily Backup of Database ( test )

Rotating 6 day backups for test

----------------------------------------------------------------------

Backup End Time Sat Jul 11 22:16:33 CST 2015

======================================================================

Total disk space used for backup storage...

Size - Location

750k /u03/mysqlbackup/

======================================================================

###### WARNING ######

Errors reported during AutoMySQLBackup execution.. Backup failed

Error log below..

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

检查备份目录，你会发现生成了daily、fullschema、 latest、 monthly、 status、 tmp、 weekly等目录，刚刚备份的文件位于daily目录下，如下所示

手动安装配置

AutoMySQLBackup也可以手动配置，在文档README里面有介绍如何手工配置，也非常的简单

1. Create the /etc/automysqlbackup directory.

2. Copy in the automysqlbackup.conf file.

3. Copy the automysqlbackup file to /usr/local/bin and make executable.

4. cp /etc/automysqlbackup/automysqlbackup.conf /etc/automysqlbackup/myserver.conf

5. Edit the /etc/automysqlbackup/myserver.conf file to customise your settings.

6. See usage section.

AutoMySQLBackup的使用

AutoMySQLBackup使用mutt发送邮件，它是linux下的一个email程序，由于我们一般都使用sendmail发送邮件，所以一般在配置文件里面不启用改参数。所以使用AutoMySQLBackup时，一般自己写shell脚本，调用AutoMySQLBackup来备份数据，如下所示automysqlbackup.sh

#REM backup script

#REM ----------------------------------------

#REM  Backup script for EEL Mysql

#REM  Tommy Wang -  08-14-2012

#REM  Kerry Kong -  07-12-2015 Modify this Script

#REM ----------------------------------------

#REM - USER DEFINED VARIABLES -

export DATESTAMP=`date ‘+%F‘`

#REM - Logfiles -

export BACKUP_FULL_LOG=/mysqldata/db_backup/logs/auto_backup_$DATESTAMP.log

/usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf >>$BACKUP_FULL_LOG

chown mysql.mysql /mysqldata/db_backup -R >>$BACKUP_FULL_LOG

find  /mysqldata/db_backup -type f -exec chmod 400 {} \; >>$BACKUP_FULL_LOG

find  /mysqldata/db_backup -type d -exec chmod 700 {} \; >>$BACKUP_FULL_LOG

EMAILTMP=/mysqldata/db_backup/logs/auto_backup_db_$DATESTAMP.TMP

##################################### config email parameters ####################################

echo ‘Content-Type: text/html‘ > $EMAILTMP

echo ‘To: dba@xxxx.com‘ >> $EMAILTMP 

echo ‘Subject: Backup Status - The server xxxx  MYSQL (dump backup)‘ >> $EMAILTMP

echo ‘<pre style="font-family: courier; font-size: 9pt">‘ >> $EMAILTMP

#################################################################################################

echo "==============================================================================" >> $EMAILTMP

echo "======================== Daily Backup for Mysql Database =====================" >> $EMAILTMP

echo "==============================================================================" >> $EMAILTMP

echo " " >> $EMAILTMP 

cat $BACKUP_FULL_LOG >> $EMAILTMP 2>&1

echo " " >> $EMAILTMP 

echo "================================== End of Backup =============================" >> $EMAILTMP

echo "==============================================================================" >> $EMAILTMP

/usr/sbin/sendmail -t -f "BackupAdmin" < $EMAILTMP

rm $EMAILTMP

chown mysql.mysql $BACKUP_FULL_LOG

使用AutoMySQLBackup发送邮件时，会有下面告警提示，这个是因为我们将连接数据库的账号密码配置在配置文件中，这样非常不安全。如何去掉这个告警提示呢？ AutoMysqlBackup: Warning: Using a password on the command line interface can be insecure. 这篇博客里面介绍了一种方法

找到automysqlbackup文件，找到removeIO，然后在其下面加上下面这段脚本后，你就不会收到告警信息。

[root@getlnx20 ~]# cd /usr/local/bin

[root@getlnx20 bin]# vi automysqlbackup

removeIO

Add this after removeIO:

# Remove annoying warning message since MySQL 5.6

if [[ -s "$log_errfile" ]]; then

sedtmpfile="/tmp/$(basename $0).$$.tmp"

grep -v "Warning: Using a password on the command line interface can be insecure." "$log_errfile" > $sedtmpfile

mv $sedtmpfile $log_errfile

fi

关于AutoMySQLBackup的介绍到这里，其实AutoMySQLBackup还有很多细节地方值得我们去学习、研究。限于篇幅，不在此一一赘述。

MySQL备份还原——AutoMySQLBackup介绍

标签：

本文系统来源：http://www.cnblogs.com/kerrycode/p/4648974.html

内容总结

以上是互联网集市为您收集整理的MySQL备份还原——AutoMySQLBackup介绍全部内容，希望文章能够帮你解决MySQL备份还原——AutoMySQLBackup介绍所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端