mysql慢查询日志增量备份_mysqlhotcopy + binlog 实现mysql增量备份-CSDN博客

本文链接：https://blog.csdn.net/weixin_29275257/article/details/113116399

mysqlhotcopy只是简单的缓存写入和文件复制的过程，占用资源和备份速度比mysqldump快很多很多。特别适合大的数据库，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，只能运行在数据库目录所在的机器上。

[warning]注意：mysqlhotcopy只支持MyISAM 引擎。[/warning]

1. 安装依赖包

mysqlhotcopy是perl语言写的，因此需要安装perl的连接mysql的驱动:

# yum install perl-DBI.x86_64

# yum install perl-DBD-MySQL.x86_64

# yum install perl-DBI.x86_64

# yum install perl-DBD-MySQL.x86_64

2 mysqlhotcopy常用参数:

-–allowold 如果目标存在不退出(加上一个_old后缀重新命名它)

-–addtodest 增量备份，新的备份自动覆盖掉原來的数据，相对于allowold

-–checkpoint=db_name.tbl_name 在指定的数据库，表中插入检查点条目。

-–debug 启用调试输出。

-–dryrun，-n 报告动作而不执行它们。

-–flushlog 所有表锁定后刷新日志。

-–keepold 完成后不删除以前(重新命名的)的目标。

-–method=command 复制方法(cp或scp)。

-–noindices 备份中不包括全部索引文件。这样使备份更小、更快。可以在以后用myisamchk -rq重新构建索引。

-–user=user_name，-u user_name 当连接服务器时使用的MySQL用户名。

-–password=password，-p password 当连接服务器时使用的密码。请注意该选项的密码值是不可选的，不象其它MySQL程序。

-–port=port_num，-P port_num 当连接本地服务器时使用的TCP/IP端口号。

-–quiet，-q 除了出现错误时保持沉默。

-–regexp=expr 复制所有数据库名匹配给出的正则表达式的数据库。

-–socket=path，-S path 用于连接的Unix套接字文件。

-–suffix=str 所复制的数据库名的后缀。

-–tmpdir=path 临时目录(代替/tmp)。

--resetmaster 所有表锁定后reset二进制日志

--resetslave 所有表锁定后reset master.info

--record_log_pos=db.table 指定记录slave和master信息的表

3. 创建记录slave和master信息的表

CREATE TABLE `mysqlhotcopy_log_pos` (

`host` varchar(60) NOT NULL,

`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`log_file` varchar(32) DEFAULT NULL,

`log_pos` int(11) DEFAULT NULL,

`master_host` varchar(60) DEFAULT NULL,

`master_log_file` varchar(32) DEFAULT NULL,

`master_log_pos` int(11) DEFAULT NULL,

`relay_log_file` varchar(32) DEFAULT NULL,

`relog_log_pos` int(11) DEFAULT NULL,

PRIMARY KEY (`host`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATETABLE`mysqlhotcopy_log_pos`(

`host`varchar(60)NOTNULL,

`time_stamp`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,

`log_file`varchar(32)DEFAULTNULL,

`log_pos`int(11)DEFAULTNULL,

`master_host`varchar(60)DEFAULTNULL,

`master_log_file`varchar(32)DEFAULTNULL,

`master_log_pos`int(11)DEFAULTNULL,

`relay_log_file`varchar(32)DEFAULTNULL,

`relog_log_pos`int(11)DEFAULTNULL,

PRIMARYKEY(`host`)

)ENGINE=MyISAMDEFAULTCHARSET=utf8

[warning]注意：mysqlhotcopy脚本没有记录relay_log_file和relog_log_pos值，同时，也不会把这些信息写入到文件。后面记录relay log和pos值信息和写入到文件是因我改了mysqlhotcopy脚本内容。[/warning]

4. 专用用户权限

grant select, reload, lock tables on *.* to 'mysqlbackup'@'localhost' identified by 'www.ttlsa.com';

grant select, delete, update, insert on mysql.mysqlhotcopy_log_pos to 'mysqlbackup'@'localhost' identified by 'www.ttlsa.com';

grantselect,reload,locktableson *.*to'mysqlbackup'@'localhost'identifiedby'www.ttlsa.com';

grantselect,delete,update,insertonmysql.mysqlhotcopy_log_posto'mysqlbackup'@'localhost'identifiedby'www.ttlsa.com';

5. 正则使用

如果只想热备其中的一部分数据就有可能用到正则了.

5.1 数据库名匹配，比如：备份以ttlsa开头的库，可以使用：

# mysqlhotcopy --flushlog -u='mysqlbackup' -p='www.ttlsa.com' --regexp= ^ttlsa /backup/mysqlback

备份以[a-f]开头的库，可以使用：

# mysqlhotcopy --flushlog -u='mysqlbackup' -p='www.ttlsa.com' --regexp=^[a-f] /backup/mysqlback

5.2 备份某个数据库中的某些表：

备份ttlsa_com库以user开头的表：

# mysqlhotcopy --flushlog -u='mysqlbackup' -p='www.ttlsa.com' ttlsa_com./^user/ /backup/mysqlback

备份ttlsa_com库除user_log开头的表：

# mysqlhotcopy --flushlog -u='mysqlbackup' -p='www.ttlsa.com' ttlsa_com./~^user_log/ /backup/mysqlback

备份ttlsa_com库以user_0,user_1,user_2......,user_9开头的表：

# mysqlhotcopy --flushlog -u='mysqlbackup' -p='www.ttlsa.com' ttlsa_com./^$user_[0-9]$/ /backup/mysqlback

6. 记录slave和master信息

# perl ./mysqlhotcopy -u mysqlbackup -p www.ttlsa.com -S /tmp/mysql.sock --record_log_pos=mysql.mysqlhotcopy_log_pos --keepold --record_log_pos2file --flushlog --regexp="[a-zA-Z0-9_-]" /backup/mysqlback/mysqlhotcopy_20131114_041307

注意：--record_log_pos2file参数是我修改mysqlhotcopy加的。

记录的信息如下所示：

7. 增量备份实现

根据6上面的master、slave信息记录，来实现增量备份。

# mysqlbinlog --start-position=POS BIN_LOG_FILE

8. 简单备份脚本

#!/bin/bash

mysqlhotcopy="/usr/local/mysql/bin/mysqlhotcopy2"

user="mysqlbackup"

password="www.ttlsa.com"

socket="/tmp/mysql.sock"

backupdir="/backup/mysqlback"

datadir="mysqlhotcopy_`date +%Y%m%d_%I%M%S`"

echo $datadir

target="$backupdir/$datadir"

retention_days_local="5"

status=($(mysql -u$user -p${password} -S $socket -e "show slave status\G" --skip-column-names | egrep "Slave_IO_Running|Slave_SQL_Running" | awk '{print $2}'))

if [ "${status[0]}" == "Yes" ] && [ "${status[1]}" == "Yes" ]; then

mkdir -p $target

$mysqlhotcopy -u $user -p $password -S $socket --record_log_pos=mysql.mysqlhotcopy_log_pos --keepold --record_log_pos2file --flushlog --regexp="[a-zA-Z0-9_-]" $target

find $backupdir -name "^mysqlhotcopy_*_*" -type d -mtime +${retention_days_local} | xargs rm -rf

else

echo "slave error"

#!/bin/bash

mysqlhotcopy="/usr/local/mysql/bin/mysqlhotcopy2"

user="mysqlbackup"

password="www.ttlsa.com"

socket="/tmp/mysql.sock"

backupdir="/backup/mysqlback"

datadir="mysqlhotcopy_`date +%Y%m%d_%I%M%S`"

echo$datadir

target="$backupdir/$datadir"

retention_days_local="5"

status=($(mysql-u$user-p${password}-S$socket-e"show slave status\G"--skip-column-names|egrep"Slave_IO_Running|Slave_SQL_Running"|awk'{print $2}'))

if["${status[0]}"=="Yes"]&&["${status[1]}"=="Yes"];then

mkdir-p$target

$mysqlhotcopy-u$user-p$password-S$socket--record_log_pos=mysql.mysqlhotcopy_log_pos--keepold--record_log_pos2file--flushlog--regexp="[a-zA-Z0-9_-]"$target

find$backupdir-name"^mysqlhotcopy_*_*"-typed-mtime+${retention_days_local}|xargsrm-rf

else

echo"slave error"

转载请注明来自运维生存时间: http://www.ttlsa.com/html/3689.html