Linux recovers MySQL data sheet via SCP copy backup

Recently, the customer has a demand, the younger brother for the operation of the thing is also dabbler, so determined to explore the next. It was a night of torture and finally it was done. Because is two different server, originally wanted to use FTP, helpless younger brother is not very familiar with, so use SCP, but SCP has a problem is to enter the password to copy, this next little brother nasty, then I this shell script do,

My requirement is that I have to export three tables on a daily basis and then replicate them to another server and then recover the data from the three tables.

As the saying goes, the family looking for Niang, the younger brother on the degree Niang's wardrobe, finally found a fitting clothes.

The article link is this http://blog.csdn.net/xifeijian/article/details/19998129,

In order to avoid the link can not be used, I ventured to copy,

In the remote copy, you need to configure the SCP's no password copy files and directories . It is very useful when writing shell scripts, and you do not want the shell script to report permission errors during execution because of the ability to verify that the password is interrupted.

The whole process is as follows:

Local server: A
Remote server: B

1. Run ssh-keygen-t RSA on a to generate Id_rsa and id_rsa.pub two files under/home/openpf/.ssh, then copy the backup under/home/openpf/.ssh a copy id_rsa.pub named Id_rs A.PUB.A for copying to Server B:

2. Also run ssh-keygen-t RSA on B

3. Create the Authorized_keys file under B's/home/openpf/.ssh and upload the Id_rsa.pub.a file in A to the B server/home/openpf/.ssh,
Use the cat command to write the ID_RSA.PUB.A to the Authorized_keys file with the following command:

SCP./ID_RSA.PUB.A B's Ip:/home/openpf/.ssh
Cat ID_RSA.PUB.A >> Authorized_keys

4. Configuration Complete

Test:

SCP./ID_RSA.PUB.A 10.16.133.200:/home/openpf/.ssh No need to enter password to execute successfully

Command:

To copy a file:
1. Copy the local file to the remote
SCP filename User name @ computer IP: remote path
2. Copy files back to local from remote
SCP User name @ Computer IP: File name local path

To copy a directory:
1. Copy the local directory to the remote
Scp-r Directory name User name @ computer IP: remote path
2. Copy the directory back to local from the remote
SCP-R User name @ computer IP: Directory name local Path

The above catalogue can be built on its own

Mine is in/root/.ssh.

So the first step of the SCP is complete. Put down my shell script code, my little brother caught dead.

#!/bin/bash
hostname= "xxxx" #数据库ip
port= "3306" #端口
username= "root" #用户名
password= "xxxxx" #密码
Dbname= "RCBC" #数据库名称
tablecususer= "Cus_user" #数据库中表的名称
tablecusrank= "Cus_rank" #数据库中表的名称
Tablecususerbody= "Cus_user_body"
backupdir=/root/backup/mysql/question/#保存路径
#3. Set file backup filename
Filecususer =cus_user
Filecususerbody=cus_user_body
Filecusrank=cus_rank
dumpfilecususer= $backUpDir $filecususer ' Date-d now +%y%m%d '. SQL
dumpfilecususerbody= $backUpDir $filecususerbody ' date-d now +%y%m%d '. SQL
dumpfilecusrank= $backUpDir $filecusrank ' date-d now +%y%m%d '. SQL
#备份数据表
/usr/bin/mysqldump-u$username-p$port -p$password-h$hostname $DBNAME $TABLECUSUSER > $dumpfilecususer
/usr/bin/mysqldump-u$username-p$port-p$ Password-h$hostname $DBNAME $TABLECUSUSERBODY > $dumpfilecususerbody
/usr/bin/mysqldump-u$username-p$port-p$ Password-h$hostname $DBNAME $TABLECUSRANK > $dumpfilecusrank

#删除昨天的数据
Rm-f $backUpDir $filecususer ' date-d -1day +%y%m%d '. sql
Rm-f $backUpDir $filecususerbody ' date-d -1day +%y%m%d '. sql
Rm-f $backUpDir $filecusrank ' date-d -1day +%y%m%d '. sql

I'll just back up and then delete the SQL script that I backed up yesterday

And then the data is recovered.

The script is as follows

#!/bin/bash
Hostname=xxxx "#数据库信息
Port= "3306"
Username= "Root"
password= "XXXX"
Dbname= "RCBC" #数据库名称
Tablecususer= "Cus_user" #cus_user表
tablecusrank= ' Cus_rank ' #cus_rank表
tablecusbody= ' Cus_user_body ' #cus_user_body

#删除数据
Delete_user_sql= "Delete from ${tablecususer}"
Delete_rank_sql= "Delete from ${tablecusrank}"
Delete_body_sql= "Delete from ${tablecusbody}"
Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${delete_user_sql}"
Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${delete_rank_sql}"
Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${delete_body_sql}"
Date= ' Date +%y%m%d '

#复制数据
scp-r [email protected] Remote server IP:/ROOT/BACKUP/MYSQL/QUESTION/ROOT/BACKUP/MYSQL/SCP

#执行恢复命令
/usr/bin/mysql-uroot [email protected] RCBC <<eof
Source/root/backup/mysql/scp/question/cus_user${date}.sql;
Source/root/backup/mysql/scp/question/cus_rank${date}.sql;
Source/root/backup/mysql/scp/question/cus_user_body${date}.sql;
Eof
Exit

The above is the whole contents of the younger brother, caught dead. Look at

Linux recovers MySQL data sheet via SCP copy backup