MySQL Local Area Network Backup Using Centos7

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"
14. $ 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
4. # DATADIR =/var/lib/mysql
5. DATADIR =/application/data # obtain the data file path
6. BAKDIR =/backup # obtain the path of the target data file to be backed up
8. ### 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.
9. # HOSTNAME = 'uname-N'
10. Cd $ DATADIR # transfer to/application/data to query mysql-bin.index files
11. # FILELIST = 'cat $ HOSTNAME-bin.index'
12. FILELIST = 'catmysql-bin. Index'
14. # COUNTERnumber
15. COUNTER = 0
16. Forfilein $ FILELIST
17. Do
18. COUNTER = 'expr $ COUNTER + 1'
19. Done
21. NextNum = 0
22. Forfilein $ FILELIST
23. Do
24. Base = 'basename $ file'
25. NextNum = 'expr $ NextNum + 1'
26. If [$ NextNum-eq $ COUNTER]
27. Then
28. Echo "skiplastest"
29. Else
30. Dest = $ BAKDIR/$ base
31. If (test-e $ dest)
32. Then
33. Echo "skipexist $ base"
34. Else
35. Echo "copying $ base"
36. Cp $ base $ BAKDIR
37. Fi
38. Fi
39. Done
41. 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.