Shell preliminary: mysql regularly dumps local data every day to replace remote database data _ MySQL

Shell preliminary: mysql regularly dumps local data every day to replace remote database data bitsCN.com

Shell preliminary: mysql regularly dumps local data every day to replace remote database data

01

Mysql regularly dumps local data every day to replace remote database data.

02

You need to import the tb_a table in the ser_a database on 192.168.3.5 to the ser_ B database on 192.168.3.6 at every day. The table name is also called tb_a.

03

Server script/usr/shell_clubs/auto_ SQL/server. sh

04

#! /Bin/sh

05

# Code by scpman

06

/Usr/bin/find/usr/shell_clubs/auto_ SQL/-name "*. SQL"-mtime + 3 | xargs rm # del old SQL

07

# Get new SQL

08

Time = 'date + % Y % m % d'

09

Mysqldump-uroot-p123456 ser_a tb_a>/usr/shell_clubs/auto_ SQL/'date + % Y % m % d' _ tb_a. SQL

10

Sleep 2

11

Ssh-p10022 192.168.3.6 <

12

/Usr/shell_clubs/auto_ SQL/client. sh

13

EEE

14

Note: The server script exports the specified table and uses the public key to ssh to the target machine to call the client script.

15

Client script/usr/shell_clubs/auto_ SQL/client. sh

16

#! /Bin/sh

17

# Code by scpman

18

# Client. sh

19

Time = 'date + % Y % m % d'

20

/Usr/bin/find/usr/shell_clubs/auto_ SQL/-name "*. SQL"-mtime + 3 | xargs rm # del old SQL

21

/Usr/local/bin/rsync-vzrtopgl -- progress root@192.168.3.5 :: all/usr/shell_clubs/auto_ SQL/'date + % Y % m % d' _ tb_a. SQL/usr/shell_clubs/auto_ SQL /;

22

Counts = 'ls-l/usr/shell_clubs/auto_ SQL/| grep "$ time" | awk '{print $ NF}' | wc-L'

23

Echo $ counts

24

If ["$ counts"-eq 1]

25

Then

26

Echo SQL: $ counts

27

Mysql-uroot-ptest123 <

28

Use ser_ B;

29

Drop table tb_a;

30

FFF

31

Mysql-uroot-ptest123 ser_ B </usr/shell_clubs/auto_ SQL/'date + % Y % m % d' _ tb_a. SQL -- default-character-set = utf8

32

Echo -------------- done

33

Else

34

Echo SQL wrong

35

Fi

36

Note: When a client script is called, the old SQL record in the current path will be deleted first, and then the table exported from the server will be synchronized through rsync, and check whether it is synchronized, if the synchronization succeeds, the existing tb_a will be cleared and the new tb_a will be imported into the database. if the synchronization fails, the script will exit (here you can send an email notification before exiting)

37

Then the process passes

38

Now, add a regular IP address on 192.168.3.5 (server side)

39

# Send SQL to 192.168.3.6

40

0 5 *** su-root-c "/usr/shell_clubs/auto_ SQL/server. sh">/usr/shell_clubs/auto_ SQL/res. log

BitsCN.com