MySQL Local Area Network Backup Using Centos7

Source: Internet
Author: User

MySQL Local Area Network Backup Using Centos7

Database Backup is an important step. For online systems, databases without backups are insecure. This blog post provides a full backup, Incremental backup, and local area network (LAN) Scheduled backup for the MySQL database using shell script files by checking online data.

Server Introduction: centos 7.0

[Local full backup]

1. single database backup script file

# Vi mysql-backup.sh

[SQL] view plaincopy
  1. Db_user = "root" # username and password of the current server
  2. Db_passwd = "root"
  3. Db_host = "192.168.64.133" # local server address
  4. Db_name = "whp" # Name of the backup database
  5. # Thedirectoryforstoryyourbackupfile.
  6. Backup_dir = "/backup" # path of the file to be backed up
  7. # Dateformatforbackupfile (dd-mm-yyyy)
  8. Time = "$ (date +" % Y-% m-% d-% H-% M-% S ")"
  9. # Mysql, mysqldumpandsomeotherbin 'spath
  10. MYSQL = "/application/mysql/bin/mysql"
  11. MYSQLDUMP = "/application/mysql/bin/mysqldump"
  12. GZIP = "/bin/gzip"
  13. $ MYSQLDUMP-u $ db_user-h $ db_host-p $ db_passwd $ db_name | $ GZIP-9> "$ backup_dir/$ db_name" _ "prop"

2. retain full backup of 7-day historical data

[SQL] view plaincopy
  1. Db_user = "root"
  2. Db_passwd = "root"
  3. Db_host = "localhost"
  4. # Thedirectoryforstoryyourbackupfile.
  5. Backup_dir = "/application/backup /"
  6. # Dateformatforbackupfile (dd-mm-yyyy)
  7. Time = "$ (date +" % H-% M-% S-% m-% d-% y ")"
  8. # Mysql, mysqldumpandsomeotherbin 'spath
  9. MYSQL = "/application/mysql/bin/mysql"
  10. MYSQLDUMP = "/application/mysql/bin/mysqldump"
  11. MKDIR = "/bin/mkdir"
  12. RM = "/bin/rm"
  13. MV = "/bin/mv"
  14. GZIP = "/bin/gzip"
  15. # Checkthedirectoryforstorebackupiswriteable
  16. Test! -W $ backup_dir & echo "Error: $ backup_dirisun-writeable." & exit
  17. # Thedirectoryforstorythenewestbackup
  18. Test! -D "$ backup_dir/backup.0/" & $ MKDIR "$ backup_dir/backup.0 /"
  19. Echo "StarttoBackup ...";
  20. # Getalldatabases
  21. # Don 'tbackupinformation _ schema and performance_schema
  22. All_db = "$ ($ MYSQL-u $ db_user-h $ db_host-p $ db_passwd-Bse 'showdatabases ')"
  23. All_db =$ {all_db // information_schema /};
  24. All_db =$ {all_db // performance_schema /};
  25. Fordbin $ all_db
  26. Do
  27. $ MYSQLDUMP-u $ db_user-h $ db_host-p $ db_passwd $ db | $ GZIP-9> "$ backup_dir/backup.0/snapshot"
  28. Done
  29. # Deletetheoldestbackup
  30. Test-d "$ backup_dir/backup.7/" & $ RM-rf "$ backup_dir/backup.7"
  31. # Rotatebackupdirectory
  32. Forintin6543210
  33. Do
  34. If (test-d "$ backup_dir"/backup. "$ int ")
  35. Then
  36. Next_int = 'expr $ int + 1'
  37. $ MV "$ backup_dir"/backup. "$ int" "$ backup_dir"/backup. "$ next_int"
  38. Fi
  39. Done
  40. Echo "BackUpSuccess! "
  41. Exit0;

3. Modify shell script permissions and execute

# Chmod 700 mysql-backup.sh // only allow the Administrator to run this script

#./Mysql-backup.sh // execute the script, test once

[Incremental log file backup]

1. Create a script file:

# Vi incre-backup.sh

[SQL] view plaincopy
  1. # Execute mysqladmin to refresh the log file
  2. /Application/mysql/bin/mysqladmin-uroot-prootflush-logs
  3. # DATADIR =/var/lib/mysql
  4. DATADIR =/application/data # obtain the data file path
  5. BAKDIR =/backup # obtain the path of the target data file to be backed up
  6. ### If you have made special settings for mysqlbinlog, modify this field or modify the row that applies this variable: The machine name is used by default, and the machine name is used by mysql by default.
  7. # HOSTNAME = 'uname-N'
  8. Cd $ DATADIR # transfer to/application/data to query mysql-bin.index files
  9. # FILELIST = 'cat $ HOSTNAME-bin.index'
  10. FILELIST = 'catmysql-bin. Index'
  11. # COUNTERnumber
  12. COUNTER = 0
  13. Forfilein $ FILELIST
  14. Do
  15. COUNTER = 'expr $ COUNTER + 1'
  16. Done
  17. NextNum = 0
  18. Forfilein $ FILELIST
  19. Do
  20. Base = 'basename $ file'
  21. NextNum = 'expr $ NextNum + 1'
  22. If [$ NextNum-eq $ COUNTER]
  23. Then
  24. Echo "skiplastest"
  25. Else
  26. Dest = $ BAKDIR/$ base
  27. If (test-e $ dest)
  28. Then
  29. Echo "skipexist $ base"
  30. Else
  31. Echo "copying $ base"
  32. Cp $ base $ BAKDIR
  33. Fi
  34. Fi
  35. Done
  36. Echo "backupmysqlbinlogok"

2. Modify the File Permission and execute

# Chmod 700 incre-backup.sh // only allow the Administrator to run this script

#./Mysql-backup.sh // execute the script, test once

Automatic Backup]

Automatic Backup is based on the above script. the linux crontab command is used to automatically execute shell files at regular intervals:

# Crontab-e

Add:

00 01 ***/mysql-backup.sh // run at every day

Remote LAN backup]

Local Area Network Backup uses the NFS server to achieve data sharing over the local area network. It is achieved by mounting the NFS server, just as we usually share files and configure network connections, share your files. The implementation method is as follows:

1. NFS Server Installation

A) software packages required to install the NFS server:

# Yuminstall-y nfs-utils

B) edit the exports file

# Vim/etc/exports

#/Home/nfs/192.168.248.0/24 (rw, sync)

Hosts With the same network number as 192.168.248.0/24 can mount the/home/NFS/directory on the nfs server to their own file systems.

Rw indicates read/write; sync indicates synchronous write.

C) Start the nfs service.

# Systemctlstart rpcbind. service

# Systemctlstart nfs-server.service

D) Confirm that the NFS server is successfully started:

# Rpcinfo-p

E) If the instance is not successfully started, restart the rpcbind and nfs-server services:

# Systemctl restart rpcbind. service

# Systemctl restart nfs-server.service

Client installation

A) install nfs and start the rpcbind service.

# Yuminstall-y nfs-utils

B) Start rpcbind first

# Systemctlenable rpcbind. service

C) then start the rpcbind service:

Systemctlstart rpcbind. service

D) check whether the NFS server has a shared directory:

# Showmount-e 192.168.64.138

E) mount the server directory on the slave machine to a directory on the client (here, mount the local backup file path above to the server ):

# Mount-tnfs-o nolock, nfsvers = 1, vers = 3 192.168.64.138:/home/nfs/backup

Problem: mount. nfs: Stale NFS file handle

Solution: remove the mounting document from the client and remount the file:

# Umount-

[Backup recovery]

A. Restore the gzip database to Mysq

# Gunzip <dbname.gz | mysql-u Username-p dbname

B. Restore the database using binary files:

#/Mysql/bin/mysqlbinlog -- database = fox -- start-date = "5:00:00" -- stop-date = "9:00:00"/mysql/data/mysql-bin.000001 |/mysql/bin /mysql-u root-p123456-f

Database: Specifies a specific database.

Start-date: start Time

Stop-date: End Time

/Mysql/data/mysql-bin.000001: specify a binary file

[Summary]

The basic backup method has been completed, but we need to adopt different backup policies according to the specific application scenarios, so as to ensure security without consuming too much memory.

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: info-contact@alibabacloud.com 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.