Use the automysqlbackup tool in centos to automatically back up data in mysqllinux and use crontab to execute scheduled tasks.

I have seen that the old version of server administration can be automatically backed up, And I think workbench should be fine ...... I didn't expect it to work, and MySQL only considered adding this function to Community plugin. Sorrow ...... Fortunately, someone wrote an automysqlbackup file shared with SourceForge. Try it.

Introduction on SourceForge:

Description

Automysqlbackup with a basic configuration will create daily, weekly and monthly backups of one or more of your MySQL databases from one or more of your MySQL servers.

Other features include:
-Email notification of backups (with email notification)
-Backup compression and encryption (using compression and encryption)
-Resumable backup rotation (the time when the backup file is saved)
-Incremental Database backups (Incremental Backup)

 

1. Download automysqlbackup

Http://sourceforge.net/projects/automysqlbackup/

2. Extract

Copy the downloaded automysqlbackup-v3.0_rc6.tar.gz file to/usr/tmp.

Create an automysqlbackup folder under/usr/local and enter the folder

CD/usr/local

Mkdir automysqlbackup

CDAutomysqlbackup

Then extract terminal.

Tar-xzvf/usr/tmp/automysqlbackup-v3.0_rc6.tar.gz

3. Install

Install the following command:

./Install. Sh

There will be two places in the middle to ask for the installation directory of the configuration file, just press Enter.

After the installation is complete, a message is displayed, telling you what to do if this command is not used by the current user:

Let's first go to/etc/automysqlbackup to see what's going on.

There are four files under visual testing ......

4. modify the configuration file

The automysqlbackup. conf file is used:

The file contains some basic configuration information, such as the user name, password, and IP address used to connect to the MySQL server.

# Username to access the MySQL server e.g. dbuserconfig_mysql_dump_username='Root'# Password to access the MySQL server e.g. passwordconfig_mysql_dump_password='1234'# Host Name (or IP address) of MySQL Server e. g localhostconfig_mysql_dump_host='Localhost'
 

Continue. An important configuration isBackup storage locationSorry!

# Backup directory location e. g/Backupsconfig_backup_dir='/Var/backup/DB'
 

The name of the database you want to configure can also be accurate to the table name or the database name. Or simply leave it blank, but if it is left blank, all databases will be backed up by default ...... In this case, the disk may explode ......

 # Databases to backup # list of databases  For Daily/weekly backup e.g .( '  Db1  '   '  DB2  '   '  Db3 '  ...) # Set to (), I. e. empty,  If  You want to backup all databasesconfig_db_names = () # You can use # declare -A mdbnames = ( "  $ {Dbnames [@]}  "   '  Added entry1  '   '  Added entry2  ' ...) # Instead to copy the contents of $ dbnames and add further entries (Optional). # list of databases  For  Monthly backups. # Set to (), I. e. empty,  If  You want to backup all databasesconfig_db_month_names = () # List of dbnames to exlucde  If  Dbnames is empty, I. e. (). config_db_exclude = ( '  Information_schema  '   ' Wiqun  ' )

In addition, you can set the time interval for weekly, monthly, and daily.

 # Rotation settings # Which day  Do You want monthly backups? ( 01 To 31  ) # If the chosen day is greater than  Last Day of the month, it will be Done  # On  Last  Day of the month. # Set  0 To disable monthly backups. config_do_monthly = "  22  "  # Which day  Do You want weekly backups? ( 1 To 7 Where 1  Is Monday) # Set  0  To disable weekly backups. config_do_weekly = "  7 "  # Set rotation of daily backups. Value * 24 hours # If you want to keep only today  '  S backups, you cocould choose 1, I. e. Everything older than 24 hours will be removed. Config_rotation_daily = 7  # Set Rotation  For Weekly backups. Value * 24hoursconfig_rotation_weekly = 35  # Set Rotation  For Monthly backups. Value *24hoursconfig_rotation_monthly = 150 

The first two are both easy to understand, that is, when to perform automatic backup every month or every week. If you do not want to use weekly backup or monthly backup, set the corresponding value to 0. So what does rotation mean? It is actually the Log retention period.

For example, config_rotation_weekly = 35 indicates that backups stored on a weekly basis can be retained for up to 35 days.

You can configure the email sending configurations, such as the email address and attachment content.

# What wowould you like to be mailed to you? # -Log: Send only log File  # -Files: Send log File  And SQL files as attachments (see Docs )# -Stdout: will simply output the log to the screen If Run manually .# -Quiet: Only Send Logs If  An error occurs to the mailaddr. config_mailcontent = '  Files  '  # Set the maximum allowed email size  In K .( 4000 = Approx 5 MB email [see Docs]) config_mail_maxattsize = 4000  # Allow packing of files  Tar And splitting it In  Pieces of config_mail_maxattsize.config_mail_splitandtar = '  Yes  '  # Use uuencode instead of mutt. Warning: not all email clients work well with uuencoded attachments. # config_mail_use_uuencoded_attachments = '  No  '  # Email address to send mail ? (User@domain.com) config_mail_address = ' Elarwei@gmail.com  ' 

5. Run automysqlbackup

Before Running automysqlbackup, do not forget to create a new place where we store backup database files, that is, the"/Var/backup/DB", Now let's create this directory.

Mkdir/var/backup

Mkdir/var/backup/DB

Now we can run automysqlbackup. Command:

Automysqlbackup/etc/automyslqbackup/automysqlbackup. conf

Too many. How can I report an error?

Parsed configFile "/Etc/automysqlbackup. conf"# CheckingForPermissionsWriteTo folders: Base folder/Var/Backup... exists... OK. Backup folder/Var/backup/DB... exists... writable?Yes. Proceeding./Usr/local/bin/automysqlbackup: Line139: Mutt: Command not found
 

Okay, ask Google.

Someone has encountered this problem on SourceForge ......, The answer is:

 Please set config_mailcontent =  '  stdout   '  in  your config  file  and try again from your console. 
 the output shoshould directly be displayed. 
 it seems some error occured before the check  for  config variables cocould determine 
 whether there is a need  for  Checking  If  mutt (Mail Program) is installed and can be used. 
 therefore the error handler tried to mail the errors using mutt (your configuration choice),  which  isn  '  T there.  

I used an email to report the error in the configuration file, but the mutt is not installed on your machine.ProgramTherefore, an error occurs during configuration detection. The solution is to change the error to "stdout", but if it is changed to "stdout", will it lose our intention to send a report to an email ...... So, let's install the mutt program.

(PS: This step is not required if you do not use email sending.)

5.1 install mutt

Command is

Yum install mutt

After the installation is complete, run automysqlbackup again.

No error is reported this time. Let's take a look at the changes under/var/backup/DB.

CD/var/backup/DB

Ls

There are several folders.

6. Set cron job

Cron is a tool that allows the system to regularly run background scripts.

6.1 compile an executable script

In the/usr/local/bin directory, write a script to be periodically executed.

#! /Bin/Sh/Usr/local/bin/automysqlbackup/etc/automysqlbackup. conf
 

Save as backupscript. Sh

Then, change the mode to the root executable and read/write mode.

Chmod 744/usr/local/bin/backupscript. Sh

6.2 add executable scripts to scheduled tasks

First use the command

Crontab-l

Let's take a look at the timer programs in cron.

If"No crontab for root", It indicates that there are no scheduled tasks under the root user. You can create a new file to configure scheduled tasks for the root user.

VI root-Conf

Enter

0-59/10 */usr/local/bin/backupscript. Sh

It means to execute the backup script every ten minutes (exaggerated, in order to see more backup results in a short period of time, it is generally set to be executed at a time of a day) save and exit.

Next, use the command

Crontab-u Root-Conf

As the scheduled task list file under root. Then typeCrontab-lCommand, you can view the scheduled task we just set. In additionCrontab-eCommand to edit our scheduled tasks. For details about how to set the time, seeKaixuanThisArticleUse crontab in Linux to execute scheduled tasks.

 

7. View backup results

Let's go to the backup directory and check the changes.

CD /Var/backup/DB/daily

Ls

Two directories are displayed.

 

Go to MySQL

At, the backups in the daily directory were as follows:

Backups in the daily directory at are as follows:

You can also view the backup email sent in the email, as shown below:

However, Gmail automatically treats these emails as spam, So if you cannot see them in your mailbox, look for them ......

Reference

How to Do painless MySQL Server backups with automysqlbackup

 

Other backup tools include

xtrabackup