Linux regularly backs up MySQL data and synchronizes to other servers

Source: Internet
Author: User
Tags chmod inotify mysql backup rsync ssh port

(Backup restore operation) # # #导出数据库/usr/bin/mysqldump-u root-pwd Database > database20180808.sql## #导入数据库mysql-u root-p Database < D atabase20180808.sql   (Backup to compressed file import from compressed file) # # # Backup to compressed file/usr/bin/mysqldump-u root-pwd Database | Gzip > Extract database20180808.sql.gz### Compressed files from gzip < database20180808.sql.gz | Mysql-u root-p database   (crontab scheduled backup) Create backup directory mkdir-p/bak/mysqlbakcd/bak/mysqldata  write run script vi/usr/ sbin/ script code         #! /bin/bash        #        # This was a shellscript for a Uto DB Backup and Delete old backup        #        backupdir=/bak/mysql bak        time= ' date +%y%m%d%h '         mysql_bin_dir/mysqldump-u ROOT-PWD Database | gzip > $backupdir/database$time.sql.gz        #        find $ Backupdir-name "Name_*.sql.GZ "-type f-mtime +7-exec rm {}; >/dev/null 2>&1        #  "  (Script Description:
    • Backupdir MySQL Backup address
    • Root MySQL user name
    • PWD MySQL Password
    • Database name
    • Mysql_bin_dir the bin path of MySQL;
    • Time= ' Date +%y%m%d%h ' can also be written as Time= "$ (date +"%y%m%d$h ")" where the sign is the symbol above the TAB key, not the ' left ' sign of enter, and a space after date.
    • Type F means finding a normal type of file, and F represents a normal file.
    • Mtime +7 The file changes time to find the file, +5 indicates that the file change time is 7 days ago, or if-mmin +5 means that the file change time is now 5 minutes ago.
    • The exec rm {} \ Represents the execution of a shell command, followed by a command or script to execute, followed by a pair of {}, a space and one, and finally a semicolon.
    • /dev/null 2>&1 redirects the standard error to standard output and throws it under/dev/null. In layman's words, all standard output and standard errors are thrown into the trash, where the & means that the command executes in the background.
)3. Add Execute permissions for scripts# chmod +x/usr/sbin/bakmysql.sh4, set crontab timing executionVi/etc/crontab# added in the last line: 3 * * * root/usr/sbin/ to perform a backup daily 3:00
Note: The crontab configuration file format is as follows: Time- sharing weekly command
5. Restart Crontab/etc/rc.d/init.d/crond restartthat's it. Scheduled backups and cleanup of backup data for the first 7 daysSynchronizing to other serversthis uses the Linux Sync File tool rsync+inotify to synchronize the files.Rsyncrsync is a data mirroring Backup tool--remote Sync under Unix-like systems. A fast incremental backup tool remote sync that supports local replication or synchronizes with other SSH and rsync hostsusagersync src destThis is the simplest usage, which means synchronizing the Src,dest file. (i.e., after execution, the Dest file is the same as SRC, whichever is src)Common Options
  • -A: Equivalent to-rlptgod, archive
  • -R: Recursive
  • -L: Copy software link
  • -P: Retain permission information
  • -T: Synchronize the modified time of SRC to dest
  • -G: Sync Group info (group)
  • -O: Synchronization owner Information (OWN)
  • -D: Keep characters with block device files
  • -Z: Enable compressed transport
  • -–delete: If SRC does not have this file, then dest also cannot have, that is, in the dest delete the files that are not in SRC. (If you use this option, it must be paired with the-R option)
# # Synchronize the local/bak/mysqlbak/file to the remote server/bak/mysql/bak directory to exclude Mysqlbak/index directory via SSH Port rsync-vzacu/bak/mysqlbak/[email Protect Ed]:/bak/mysqlbak--exclude "Mysqlbak/index"-E "ssh-p 22" # Synchronize files under remote directory/bak/mysqlbak to the local/bak/mysqlbak/directory rsync- VZRTOPG--progress--delete [email Protected]:/bak/mysqlbak/bakenable Rsync server-side synchronization remote FilesRSYCN is the server's file receiving end, RSYCN client is the server's file push end. RSYCN Server/File Receive-side configurationServer needs to open RSYNCD serviceAdd configuration file rsyncd.confvi/etc/rsyncd.conf# The following is the global configuration log file =/var/log/rsyncd.logpid File =/var/run/rsyncd.pidlock File =/var/lock/rsyncd[ Mysqlbak] #模块名, specify this name on the source server comment = Sync rsync/home #描述信息path =/bak/mysqlbak #备份目录use chroot=no #不使用chroot without root permission read only = no #设置本地备份目录为读写权限uid =rootgid=rootmax connections=10 #客户端最大连接数auth users = root #指定数据同步用户secrets file =/etc/rsyncd. Pass #指定数据同步用户信息文件hosts allow= #允许连接的客户端ignore errors = yes #忽略出现I/O error timeout = 600Create an authentication filevi/etc/rsyncd.pass# #代码 root:root #格式是用户名: Password # is the main permission to read this file, otherwise it will report no permissions chmod 600/etc/rsyncd.passModify/etc/xinetd.d/rsync file, disable change to NoService rsync{disable = Nosocket_type = streamwait = Nouser = Rootserver =/usr/bin/rsyncserver_args =--daemonlog_on_fail Ure + = USERID}Start the service sideRsync--daemon--config=/etc/rsyncd.confRSYCN client/File send-side configurationSimple client configuration requires only a configuration password tovi/etc/rsync_client.pwd# #代码root #只需要填写rsync服务的密码 # is the main permission to read this file, otherwise it will report no permission chmod 600/etc/rsync_client.pwdClient Synchronization Test/USR/BIN/RSYNC-AUVRTZOPGP--progress--password-file=/etc/rsync_client.pwd/bak/mysqlbak/[email protected]:: Mysqlbak
Rsync is just a one-time sync, and if you need to sync it in real, you need to introduce another tool
INotifyInotify is a powerful, fine-grained, asynchronous file system event monitoring mechanism, the Linux kernel from 2.6.13, joined the Inotify support, through Inotify can monitor the file system to add, delete, modify, move and other subtle events, Using this kernel interface, third-party software can monitor the file system under the various changes, and inotify-tools is such a third-party software.
inotify only need to follow the deployment in sync with the client, when the monitored files have changed touch rsync script Sync
installationYum Install Inotify-toolsConfiguring the file path for monitoringvi/etc/inotify_exclude.lst# code/bak/mysqlbak #监控目录 @/bak/log #排除监控目录rsync exclusion monitoring Files directoryvi/etc/rsyncd.d/rsync_exclude.lst# Code src/*.html*src/js/src/2014/20140[1-9]/the client synchronizes to the remote script Auto Sync script with inotify#variablescurrent_date=$ (date +%y%m%d_%h%m%s) source_path=/bak/mysqlbak/log_file= /var/log/rsync_client.log#rsyncrsync_server= Module=mysqlbakinotify_exclude= ' (. */*\.log|. */*\.SWP) $|^/tmp/src/mail/(2014|20.*/.*che.*) ' rsync_exclude= '/bak/rsync_exclude.lst ' #rsync client pwd checkif [!-E ${RSYNC_PWD}];thenecho-e "rsync client Passwod file ${rsync_pwd} does not exist!" Exit 0fi#inotify_functioninotify_fun () {/usr/bin/inotifywait-mrq--timefmt '%y/%m/%d-%h:%m:%s '--format '%T%w%f ' \-- Exclude ${inotify_exclude}-e modify,delete,create,move,attrib ${source_path} \| While read FILEDO/USR/BIN/RSYNC-AUVRTZOPGP--exclude-from=${rsync_exclude}--progress--bwlimit=200--password-file= ${RSYNC_PWD} ${source_path} ${rsync_user}@${rsync_server}::${rsync_module}done} #inotify loginotify_fun >> ${ Log_file} 2>&1 &Execute permissions on the script, and then you can do it.chmod 777

Linux regularly backs up MySQL data and synchronizes to other servers

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.