MySQL Automatic backup

preface : "MySQL automatic backup", in the installation of MySQL, the need is to automate the backup of MySQL, so as to avoid the loss of data during the project to cause irreversible consequences, then this article will teach you how to automate the backup, attention ah, wide eyes, Haha, this is a rare secret!

Often inadvertently found articles are deducted to a variety of sites above, very hateful!
Then life where not reptile, crawler please mark Http://blog.csdn.net/qing_gee
Emulate Yan, see not yin and internal introspection also!

Library file Backup

Library file backup, I recommend once a week (of course, my project size is general, and then as to how to do once a week, we'll talk about it later)

#!/bin/bashdate_now=`date +%Y_%m_%d_%H%M%S`mysqldump -uroot -plixiaoli --flush-logs --master-data=2 maweiqing>/home/backup/${date_now}.sql &&zip -r /home/backup/${date_now}.zip /home/backup/${date_now}-f /home/backup/${date_now}.sql

1. Gets the current date, as the file name of the library file backup
2. mysqldump command to backup database maweiqing to 20151030.sql
3. The zip command shrinks a file.
4. RM command to delete SQL files

Binary log backup

Binary file backup, I suggest that sooner or later.

#!/bin/bashdate_now= ' Date +%y_%m_%_d_%h%m%s ' filename=mysql-binfilesuffix_max=0Filesuffix_lastbutone=0Mysqlpath=/var/lib/mysql/backuppath=/home/backup/mysqlbinarylog/mysqladmin-uroot-plixiaoli Flush-logs && forFNameinch' ls$mysqlpath` Dofilesuffix=${fname##*.}isnum= ' expr match${filesuffix} ' [0-9][0-9]*$ '`if[$isnum] && [$isnum -ne 0] && [$filesuffix -GT $filesuffix _max] ThenFilesuffix_lastbutone=$filesuffix _maxfilesuffix_max=$filesuffixfi Done&&filefullname=${filename}.${filesuffix_lastbutone}Cp$mysqlpath$filefullname $backuppath&&zip${backuppath}${date_now}${filefullname}. zip$backuppath$filefullname&&Echo "Done"

1. Note that binary log backup requires MySQL to open binary logs.
2. Its file path is below the/var/lib/mysql/path.
3. The mysqladmin command makes a binary log backup.

Speaking of which, I think I'd like to remind you of something so that you don't have to come to me after you do it (if you want to come to me, I think it would be nice).

• The above two files list the path, you must check whether exists, if does not exist, just mkdir a bit.
• If the zip command is not installed on the server, I will tell you how to do it later.
• The MySQL log file thing that my other article will explain, see.
• As for how to edit files on Linux, I think you can ask the "vim" adult.
• What else have you forgotten to say? By the way, before you read this article, it's best to look at MySQL configuration

These two files are well done, what do we do next? "Yuan Fang, what do you think?" "Monsieur, do you think?" ”

Test it, please.

First of all, to get two files to run, it must be to give them the soul, otherwise, how to engage.

[root@iZ23gsv94suZ soft]# chmod 700 flushlog_and_copy.sh [root@iZ23gsv94suZ soft]# chmod 700 backuplixiaoli.sh 

Flushlog_and_copy.sh This is a binary file backup.
Then the other one is the backup library file.

Test it!

[[email protected] soft]# sh backuptiangong.sh onthe command line interface can be insecure.  adding: home/backup/201577%)[[email protected] backup]# ll28-rw-r--r-- 1 root root 23948 Oct 30 10:55 2015_10_30_105545.zip

See, file backup OK! Since there is a zip file, this means that the zip command also has, not bad!

Test binary files

[[email protected] Soft]# sh flushlog_and_copy.sh  mysqladmin: [Warning] Using a password on   the  command line interface can be insecure.expr:syntax  Error  Adding:home/backup/mysqlbinarylog/mysql-bin.000002  (deflated 36 %) done[[email protected] Soft]# cd/home/backup/ mysqlbinarylog/ [[email protected] Mysqlbinarylog]# ll  Total 8 -rw-r--r--1 root root 343 Oct 30 10:59 2015_10_30_ 105901mysql-bin.000002.zip -rw-r-----1 root root 167 Oct 10:59 mysql-bin.000002
       

Do not know whether this style of writing is in line with your reading habits, if you think you can, then continue! Take a look at Linux Automation services first!

Here, you may need to look at Linux: Time synchronization, learn about Linux Automation Services!

Let's see if Crontab has a list.

[root@iZ23gsv94suZ mysqlbinarylog]# crontab -l-bash:crontab:not found

What the? There is no installation crontab, but there is no big deal, the installation of ok!

[[email protected] mysqlbinarylog]# yum Install-y vixie-cronLoaded plugins:securitysetting up Install processresolving Dependenciesinstalled:Cronie. x86_640:1.4. 4-A .El6 Dependency Installed:cronie-anacron. x86_640:1.4. 4-A .El6 Crontabs. Noarch 0:1.10-.El6 Exim. x86_640:4.72-7.El6 complete!

But the above, I think there is no big deal, but I think it is very important to start the crontab service, and then set to boot, how do these do? Don't forget I already told you, check Linux: Time synchronization.

Add the following two automation tasks to crontab!

* * 1-5 sh /home/soft/flushlog_and_* * 6 sh /home/soft/backuplixiaoli.sh

Note that the path of the file AH.

A little explanation, it's all in my memory.

1. Monday to Friday 12:30, 17:30, 23:30 for binary file backup
2. Saturday 23:40 make a backup of the library files.

Then, let's crontab -l check to see if we've ordered to join the automation task!

At the end of the final, please be sure to watch, Backup has no automation to start, if you have trouble, please trouble me, haha!

Conclusion : I want to do the technical road sweeper, for you for me to clear the confusion I encountered! Have not found, I am very ideal, haha!

Copyright notice: This is the self-styled qing_gee of the kick-off workers, in writing a high-quality blog column. Let you have a happy reading time, is no longer a luxury dream!

MySQL Automatic backup