Xtrabackup+rsync backing up the database and synchronizing to the remote backup machine

Xtrabackup+rsync backing up the database and synchronizing to the remote backup machine

Simple architecture:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/54/wKiom1V5N5yzcxrsAAErWN2NyIE784.jpg "title=" FA ( R87X$_VX) {%ckh7klhd.png "alt=" Wkiom1v5n5yzcxrsaaerwn2nyie784.jpg "/>

Simple description:

To back up the intermediate three servers above the database data to the backup machine 140; The three servers are game servers, and Web services and DB are all on a single machine

The most front-end is the Passport server, providing the player's account information and recharge information, such as Web services and DB are on one machine, you need to back up the database data to the backup machine 140

Server 129 to the Passport server database did the main master synchronization of MySQL, here first ignore

Requirements:

Database data for game servers and passport servers, locally backed up, backed up to remote backup machine 140, reserved locally for three days, and 7 days on the backup machine

Backup of the Passport server's binary log files to the remote backup machine 140 (local no backup)

Scheduled tasks, executed 1 o'clock in the morning daily

Reference:

http://732233048.blog.51cto.com/9323668/1660146

Steps:

1, take one of the servers for example, the others are the same

To operate on 192.168.186.130:

all of the following scripts and required packages can be downloaded to http://down.51cto.com/data/2058997

vi install_packages.sh #!/bin/bash# installation Xtrabackup rsync and dependent package # variable values may need to be modified, carefully confirmed under RSYNCPASSWORD=SCJ              #rsync的密码rsync = ' rpm -qa  Rsync ' if [ -z ${rsync} ];then    yum -y install  rsyncfiecho  $rsyncpassword  > /etc/rsyncd.passwordchmod 600 /etc/ rsyncd.passwordlibaio= ' Rpm -qa libaio ' if [ -z ${libaio} ];then     yum -y install libaiofiperl_time_hires= ' Rpm -qa perl-time-hires ' if [  -z ${perl_time_hires} ];then    yum -y install  Perl-time-hiresfiperl_dbd_mysql= ' Rpm -qa perl-dbd-mysql ' If [ -z ${perl_dbd_mysql}  ];then    yum -y install perl-dbd-mysqlfiperl_io_socket_ssl= ' rpm  -qa perl-io-socket-ssl ' If [ -z ${perl_io_sockEt_ssl} ];then    yum -y install perl-io-socket-sslfipercona_ xtrabackup= ' rpm -qa percona-xtrabackup ' system_weishu= ' uname -i ' mysqlversion= ' Mysql -V  | awk -f ', '   ' {print $1} '  | awk  ' {print $5} '  |awk -f '. '   ' {print $2} ' if [ -z ${percona_xtrabackup} ];then    if  [ ${system_weishu} ==  ' i386 '  ];then    #等号前后要有空格          if [  $mysqlversion  -ge 5 ];then        #版本大于等于5.5            rpm -ivh   packages/percona-xtrabackup-2.2.9-5067.el6.i686.rpm         elif [  $mysqlversion  -lt 5 ];then     #版本小于5 .5             rpm -ivh  packages/percona-xtrabackup-20-2.0.8-587.rhel6.i686.rpm         fi    elif [ ${system_weishu} ==  ' x86_64 '  ];then        if [  $mysqlversion  -ge  5 ];then       #版本大于等于5 .5             rpm -ivh packages/percona-xtrabackup-2.2.9-5067.el6.x86_64.rpm         elif [  $mysqlversion  -lt 5 ];then      #版本小于5.5            rpm -ivh   packages/percona-xtrabackup-20-2.0.8-587.rhel6.x86_64.rpm         fi    fifi

vi mysql_full_backup.sh#!/bin/bash# This script is used for full backup of the database, combined with the scheduled task completion # Note: The following variable values may have to be modified, carefully confirm the next mysqluser=rootrsyncuser= scjrsync_hostip= ' 192.168.186.140 ' ip= ' 192.168.186.130 '                  #本机ipmysqlpassword =123456mysqlsocket= '/tmp/mysqld.sock ' mysql_defaults_file = '/usr/local/mysql/my.cnf ' backuptodir= '/data/mysql_backup/mysql_full_backup_lszc/'     # Backup to Directory binlogdir= '/opt/mysql/binlog/'       #二进制日志目录binlogname = ' mysql-binlog.0* '          #二进制日志文件名rsyncmodule =mysqlbackup              #rsync的模块if  [ ! -d  $backuptodir  ];then     mkdir -p  $backuptodirfi # for full backup innobackupex --user= $mysqluser  --password=$ mysqlpassword --socket= $mysqlsocket  --defaults-file= $mysql _defaults_file  $backuptodir  2 > /dev/null | |  exit 1# Delete the full backup file a day ago, that is, keep two backup files find  $backuptodir   -type f -mtime +1 -exec  rm -f {} \; #删除一天前的二进制日志文件, that is, two-day binary log files are retained cd  $binlogdironedayago _binlog= ' find .  -type f -mtime +1 -name  "$binlogname"  | sort | tail - N1 | awk -f '/'   ' {print $2} ' mysql -u  $mysqluser  -p$mysqlpassword  -e  "purge master logs to  ' $onedayago _binlog '"  2> /dev/null# find the last full backup file, Compress cd  $backuptodirlastfull _backup= ' ls -l | grep  ' ^d '  | awk  ' {print  $9} '  | sort | tail -n1 ' Tar -zcf mysql_full_${ip}_${lastfull_backup}. Tar.gz ${lastfull_backup} #将压缩文件rsync同步到备份机rsync  -az --password-file=/etc/rsyncd.password   ${backuptodir}mysql_full_${ip}_${lastfull_backup}.tar.gz [email protected]${rsync_hostip}::$ rsyncmodule | |   exit 1# Delete Compressed Files rm -f ${backuptodir}mysql_full_${ip}_${lastfull_backup}.tar.gz 

To create a scheduled task:

Crontab-e

#对数据库进行全备份0 1 * * * CD/DATA/SCRIPTS/MYSQL;. /mysql_full_backup.sh

This script only executes the VI mysqlbinlog_backup.sh#!/bin/bash# on the Passport server This script is used to back up the binary log files and is completed with the scheduled task # Note: This script only applies to binary log files in a single directory, If the binary log file is not alone under a directory, please go to modify the configuration file # This script: The binary log files are not backed up locally, only backup to the far end of the backup machine # variable values may need to be modified, carefully confirmed under binlogdir=/opt/mysql/binlog/# Binlog Data Catalog rsyncuser=scjrsync_hostip= ' 192.168.186.140 ' rsync_binlog_monule=mysqlbinlogbackup #binlog的rsync模块 # Synchronizing data to a backup machine Rsync-az--password-file=/etc/rsyncd.password ${binlogdir}* [email protected] $rsync _hostip:: $rsync _binlog_ Monule | | Exit 1

Create a scheduled task on a Passport server

Crontab-e

#将二进制文件备份到备份机 */1 * * * * CD/DATA/SCRIPTS/MYSQL;. /mysqlbinlog_backup.sh

2. Install rsync on the backup machine 140:

Yum-y Install Rsync

Vi/etc/xinetd.d/rsync #修改文件, let xinetd to manage rsync

Change Disable = yes to disable = no

Yum-y Install xinetd

Vi/etc/rsyncd.conf #创建配置文件, default is not present

uid = rootgid = rootuser chroot =  Nomax connections = 200timeout = 600pid file = /var/run/rsyncd.pidlock  file = /var/run/rsyncd.locklog file = /var/run/rsyncd.log[mysqlbackup]path  = /data/mysql/mysql_full_backupignore errorsread only = nolist =  Nohosts allow = 192.168.186.0/255.255.255.0auth users = scjsecrets file  = /etc/rsyncd.password[mysqlbinlogbackup]path = /data/mysql/mysqlbinlog_backupignore  errorsread only = nolist = nohosts allow = 192.168.186.0/ 255.255.255.0auth users = scjsecrets file = /etc/rsyncd.password 

Note: Auth users = SCJ This line must have, otherwise any account without password can access the Rsync server, so that only SCJ this account can access the

Vi/etc/rsyncd.password #创建密码文件, user name: password

Scj:scj

chmod 600/etc/rsyncd.password #密码文件的权限必须是600, or there's going to be a problem.

Mkdir-p/data/mysql/{mysqlbinlog_backup,mysql_full_backup} #创建共享目录

Chkconfig xinetd on

/etc/init.d/xinetd Start #启动rsync

Netstat-tlnpa | grep 873 #rsync监听873端口

TCP 0 0::: 873:::* LISTEN 13613/xinetd

vi/data/scripts/mysql/delmysql_backup.sh#!/bin/bash# This script is used to periodically clean up MySQL full backup and binary files on the backup machine #mysql full backup and binary files all reserved for 7 days # The value of the variable may need to be modified to carefully confirm the mysql_full_backupdir=/data/mysql/mysql_full_backup #mysql的全备份目录mysqlbinlog_backupdir =/data/mysql /mysqlbinlog_backup #二进制文件备份目录 # Delete the full backup file seven days ago, that is, keep eight backup files find $mysql _full_backupdir-type f-mtime +7-exec rm-f {} \; #删除 Seven days ago binary log file, that is, the binary log file that is reserved for eight days find $mysqlbinlog _backupdir-type f-mtime +7-exec rm-f {} \;

Create a scheduled task

Crontab-e

#定时清理mysql的全备份和二进制文件0 3 * * * CD/DATA/SCRIPTS/MYSQL;. /delmysql_backup.sh

Post-Data Recovery steps:

#先使用全备份恢复到备份时的数据: #1,/etc/init.d/mysqld stop                   #在数据恢复时一定要记得把mysql服务停掉, mv /opt/mysql/data/* /tmp/linshi/         #把数据库数据目录下的所有数据临时mv到一个临时目录 # #, find the last full backup locally: (for example: 2015-06-11_05-13-26) # 4, innobackupex --apply-log 2015-06-11_05-13-26       #准备一个全备份 # #, innobackupex --copy-back --defaults-file=/usr/local/mysql/my.cnf 2015-06-11_05-13-26               #恢复数据, Chown -r mysql.mysql  /opt/mysql/data/          #修改数据权限 (the default xtrabackup after recovering data, All data permissions are root) #7,/etc/init.d/mysqld start                        #启动mysql服务 # #如果本地备份数据全部丢失: # To the backup machine to find the last full backup file: (For example: mysql_full_192.168.186.136_2015-06-11_05-13-26.tar.gz) #rsync  -avz [email protected] Backup machine ip:/data/mysql/mysql_full_backup/ mysql_full_192.168.186.136_2015-06-11_05-13-26.tar.gz /data/mysql_backup/mysql_full_backup_lszc/         #这里的root是备份机的root用户, you need to use the root password #cd /data/mysql_backup/mysql_full_ backup_lszc/#tar  -zxf mysql_full_192.168.186.136_2015-06-11_05-13-26.tar.gz#innobackupex -- apply-log 2015-06-11_05-13-26       #准备一个全备份 #innobackupex --copy-back  --defaults-file=/usr/local/mysql/my.cnf 2015-06-11_05-13-26               #恢复数据 #chown -r mysql.mysql /opt/mysql/data/           #修改数据权限 (default xtrabackup restore data, all data permissions are root) #/etc/init.d/mysqld  start                        #启动MySQL Service # #再使用二进制日志文件恢复备份之后的数据: #cd  2015-06-11_05-13-26                      #进入到最后一个备份目录 #cat xtrabackup_ binlog_info                # Get the binary log file that is being used when making a full backup and the POS value at this point, data from the binary log file and the POS value start after recovery # Use the Mysqlbinlog command

This article is from the "See" blog, please be sure to keep this source http://732233048.blog.51cto.com/9323668/1660910

Xtrabackup+rsync backing up the database and synchronizing to the remote backup machine