Remote backup MySQL database under Linux

This environment is mainly a remote backup MySQL database of a common method, for other knowledge do not do too much to explain.

Environmental requirements:

two linux: one MySQL database server and the other as a client for backup.

Environmental objectives:

Implement remote backup of MySQL database

Example:

(A for server-side IP 192.168.1.1 B is client IP for 192.168.1.2)

1. First you have to set up a database on a (build database name I take Xianyu as an example)

Create Database Xianyu; ( Build Database Xianyu)

2. Create a database user on a to use for backup. Here to use SJC, and grant permissions

Grant all privileges the xianyu.* to ' SJC ' @ ' 192.168.1.2 ' identified by ' 123456 '; ( establish a SJC user and give him all permissions, of course, the permissions are not necessarily all to see the specific needs )

3. The backup command can be executed on B.

Mysqldump-u sjc-p123456-h 192.168.1.1--databases xianyu >/xianyu.sql ( use mysqldump to perform backup note I put it under the root just for the convenience of the experiment. That directory depends on the specific situation)

Manual backup is complete but this is not the best, manual backup is cumbersome we can use the above command to do a shell script combined with scheduled tasks to enable it to achieve automatic backup to improve productivity

Create a script

vi/mysql.sh

Script content:

#！ /bin/bash

M_user= "SJC"

M_pwd= "123456"

M_host= "192.168.1.1"

M_conn= "-u $m _user-p$m_pwd-h $m _host"

m_db= "Xianyu"

Local_dir=/opt/beifen

Local_cmd= "/usr/bin/mysqldump"

Time= ' Date +%y%m%d-%h%m '

M_name= "$m _db-$time"

CD $local _dir

$local _cmd $m _conn--databases $m _db > $m _name

Permissions to execute after saving

chmod +x/mysql.sh

the contents of the script do not explain the meaning of the information can be searched .

Next is the scheduled task.

Crontab-e

2 * * */mysql.sh ( 2:30 execution mysql.sh script per day )

Open service

Service Crond Start

This article is from the "12067941" blog, please be sure to keep this source http://12077941.blog.51cto.com/12067941/1853200

Remote backup MySQL database under Linux