Use scripts to automate remote backup of MySQL Databases

Generally, mysql requires backup. There are many backup methods. The following is an automatic backup of a remote database that I used in concert with a scheduled task.

Generally, mysql requires backup. There are many backup methods. The following is an automatic backup of a remote database that I used in concert with a scheduled task.

Generally, mysql requires backup. There are many backup methods. The following is an automatic backup of a remote database that I used in concert with a scheduled task.

I. Confirm the backup solution:

Backup Machine: ip192.168.8.51

Database Server: ip192.168.8.46

Backup content: mysql's studydb and coursedb databases are remotely backed up and executed at every day. Each database is backed up as an independent SQL file. The backup date and time are embedded in the file in the tar.gz format.

1. Create a database:

[Root @ ns ~] # Service mysqld status

Mysqld (pid 3554) is running...

[Root @ ns ~] # Mysqladmin-uroot-p password 123123 # change the password

Enter password: # Enter the original password

[Root @ ns ~] # Mysql-uroot-p123123

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 8

Server version: 5.0.77-log Source distribution

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql>

Mysql> create database studydb; # create a database

Query OK, 1 row affected (0.05 sec)

Mysql> create database couredb;

Query OK, 1 row affected (0.00 sec)

2. Grant database permissions:

Mysql> grant select, lock tables on studydb. * to 'operator' @ '192. 168.8.51 'identified by 'pwd123'; # using the logical backup tool mysqldump requires the select and lock tables permissions on the database

Query OK, 0 rows affected (0.00 sec)

Mysql> grant select, lock tables on coursedb. * to 'operator' @ '192. 168.8.51 'identified by 'pwd123 ';

Query OK, 0 rows affected (0.00 sec)

3. Test the backup at 192.168.8.51:

[Root @ localhost ~] # Cd/tmp

[Root @ localhost tmp] # mysqldump-u operator-ppwd123-h 192.168.8.46 -- databases studydb> test. SQL

[Root @ localhost tmp] # ls

Bin games test. SQL

2. Compile the Mysql backup script at 192.168.8.51:

[Root @ localhost ~] # Mkdir/root/bin

[Root @ localhost ~] # Cd/root/bin

[Root @ localhost bin] # vi dbbak. sh

#! /Bin/bash

# This is a simple mysql database logical backup script

#1. Define database connection and target database information

MY_USER = "operator"

MY_PASS = "pwd123"

MY_HOST = "192.168.8.46"

MY_CONN = "-u $ MY_USER-p $ MY_PASS-h $ MY_HOST"

MY_DB1 = "studydb"

MY_DB2 = "coursedb"

#2. Define the Backup Directory, tool, time, and Object Name subject

BF_DIR = "/opt/dbbak /"

BF_CMD = "/usr/local/bin/mysqldump"

BF_TIME = 'date + % Y % m % d-% H % m'

NAME_1 = "$ MY_DB1-$ BF_TIME"

NAME_2 = "$ MY_DB2-$ BF_TIME"

#3. Export it as a. SQL script before compression (package and delete the original file)

Cd $ BF_DIR/

/Bin/tar zcf restart $ NAME_1. SQL -- remove &>/dev/null

/Bin/tar zcf restart $ NAME_2. SQL -- remove &>/dev/null

[Root @ localhost bin] # mkdir/opt/dbbak ### create a backup directory

[Root @ localhost bin] # chmod a + x dbbak. sh ### grant the script execution permission

[Root @ localhost bin] # dbbak. sh # Run the script

[Root @ localhost bin] # ls/opt/dbbak/### verify the effect

Coursedb-20130505-0311.tar.gz studydb-20130505-0311.tar.gz

3. Set a scheduled task on 192.168.8.51:

[Root @ localhost bin] # crontab-e

30 2 ***/root/bin/dbbak. sh

[Root @ localhost bin] # chkconfig crond on

[Root @ localhost bin] # service crond status

Crond (pid 3263) is running...

All right, you have to do is back up the database.