shell yum 自动mysql数据库_目的： 192.168.100.20服务器对192.168.100.10服务器上的MySQL数据库进行备份。1、配置MySQL服务器1）100.10MySQL...

目的：

192.168.100.20服务器对192.168.100.10服务器上的MySQL数据库进行备份。

1、配置MySQL服务器

1)100.10MySQL服务器登录MySQL创建两个数据库

[root@centos01 ~]# mysql -uroot -ppwd@123

mysql> create database liyanxin;

mysql> create database wangzhaojun;

2)liyanxin数据库创建表，写入数据

mysql> create table liyanxin.liyanxin (姓名 char(6),性别 char(5),年龄 tinyint,电话 char(11));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into liyanxin.liyanxin values ('bob','男','18','11111111111');

Query OK, 1 row affected (0.00 sec)

mysql> insert into liyanxin.liyanxin values ('tom','女','20','22222222222');

Query OK, 1 row affected (0.00 sec)

mysql> select * from liyanxin.liyanxin;

+--------+--------+--------+-------------+

| 姓名 | 性别 | 年龄 | 电话 |

+--------+--------+--------+-------------+

| bob | 男 | 18 | 11111111111 |

| tom | 女 | 20 | 22222222222 |

+--------+--------+--------+-------------+

2 rows in set (0.00 sec)

3)wangzhaojun数据库创建表，写入数据

mysql> create table wangzhaojun.wangzhaojun (姓名 char(6),性别 char(5),年龄 tinyint,电话 char(11));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into wangzhaojun.wangzhaojun values ('bob','男','18','11111111111');

Query OK, 1 row affected (0.00 sec)

mysql> insert into wangzhaojun.wangzhaojun values ('tom','女','20','22222222222');

Query OK, 1 row affected (0.00 sec)

mysql> select * from wangzhaojun.wangzhaojun;

+--------+--------+--------+-------------+

| 姓名 | 性别 | 年龄 | 电话 |

+--------+--------+--------+-------------+

| bob | 男 | 18 | 11111111111 |

| tom | 女 | 20 | 22222222222 |

+--------+--------+--------+-------------+

2 rows in set (0.00 sec)

4)在100.10服务器上授权专门用来备份数据库的账号，并赋予相应的权限

mysql> grant select,lock tables on *.* to 'bob'@'192.168.100.%' identified by 'pwd@123';

Query OK, 0 rows affected (0.00 sec)

2、配置备份服务器

1)安装MySQL客户端，手动备份测试是否成功

[root@centos02 ~]# yum -y install mysql

[root@centos02 ~]# mysqldump -u bob -ppwd@123 -h 192.168.100.10 --databases liyanxin > liyanxin.sql

[root@centos02 ~]# ls

liyanxin.sql

2)编写备份脚本

[root@centos02 ~]# mkdir -p /opt/backup

[root@centos02 ~]# vim mysql.sh

#!/bin/bash

user="bob"

pass="pwd@123"

host="192.168.100.10"

conn="-u $user -p$pass -h $host"

data1="liyanxin"

data2="wangzhaojun"

bak="/opt/backup"

cmd="/usr/bin/mysqldump"

time=`date +%Y-%m-%d-%H-%M`

name_1="$data1-$time"

name_2="$data2-$time"

cd $bak

$cmd $conn --databases $data1 > $name_1.sql

$cmd $conn --databases $data2 > $name_2.sql

/bin/tar zcf $name_1.tar.gz $name_1.sql --remove > /dev/null

/bin/tar zcf $name_2.tar.gz $name_2.sql --remove > /dev/null

3)测试脚本备份是否成功

[root@centos02 ~]# ./mysql.sh

[root@centos02 ~]# cd /opt/backup/

[root@centos02 backup]# ls

liyanxin-2020-05-20-02-10.tar.gz wangzhaojun-2020-05-20-02-10.tar.gz

4)设置计划任务实现自动备份

[root@centos02 ~]# mv mysql.sh /opt/backup/

[root@centos02 ~]# crontab -e

*/5 * * * * /opt/backup/mysql.sh

[root@centos02 ~]# systemctl status crond

● crond.service - Command Scheduler

Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)

Active: active (running) since 三 2020-05-20 01:20:56 CST; 37min ago

Main PID: 829 (crond)

CGroup: /system.slice/crond.service

└─829 /usr/sbin/crond -n

————————————本文到此结束，感谢观看——————————————