首先建立信息数据库client，数据表user_info,表结构
创建数据及表，录入数据：
[root@localhost ~]# mysql -uroot -p123456
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.11 sec)
mysql> exit
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
symbolic-links=0
default-storage-engine = INNODB
character-set-server = utf8
collation-server = utf8_general_ci
[client]
default-character-set = utf8
[mysqld_safe]
log-error=/usr/local/mysql/data/mysql.log
pid-file=/usr/local/mysql/data/mysql.pid
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# mysql -uroot -p123456
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
mysql> exit
mysql> create database client;
Query OK, 1 row affected (0.00 sec)
mysql> use client;
Database changed
mysql> create table user_info(身份证 int(20),姓名 char(20),性别 char(2),用户ID号 int(110),资费 int(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into user_info values('000000001','孙空武','男','011','100');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user_info values('000000002','蓝凌','女','012','98');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user_info values('000000003','姜纹','女','013','12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user_info values('000000004','关园','男','014','38');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user_info values('000000004','罗中昆','男','015','39');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user_info;
+-----------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+-----------+--------+-------------+--------+
| 1 | 孙空武 | 男 | 11 | 100 |
| 2 | 蓝凌 | 女 | 12 | 98 |
| 3 | 姜纹 | 女 | 13 | 12 |
| 4 | 关园 | 男 | 14 | 38 |
| 4 | 罗中昆 | 男 | 15 | 39 |
+-----------+-----------+--------+-------------+--------+
5 rows in set (0.17 sec)
完整备份client.user_info表：
[root@localhost ~]#mkdir backup
[root@localhost ~]# mysqldump -uroot -p123456 client user_info > backup/client.user_info-$(date +%Y%m%d).sql
模拟数据丢失恢复数据：
[root@localhost ~]# mysql -uroot -p123456 -e 'drop table client.user_info;'
[root@localhost ~]# mysql -uroot -p123456 -e 'use client; show tables;'
[root@localhost ~]# mysql -uroot -p123456 client < backup/client.user_info-20181214.sql
[root@localhost ~]# mysql -uroot -p123456 -e 'select * from client.user_info;'
+-----------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+-----------+--------+-------------+--------+
| 1 | 孙空武 | 男 | 11 | 100 |
| 2 | 蓝凌 | 女 | 12 | 98 |
| 3 | 姜纹 | 女 | 13 | 12 |
| 4 | 关园 | 男 | 14 | 38 |
| 4 | 罗中昆 | 男 | 15 | 39 |
+-----------+-----------+--------+-------------+--------+
定期备份数据：
[root@localhost ~]# which mysqldump
/usr/local/mysql/bin/mysqldump
[root@localhost ~]# vim /opt/bak_client.sh
#!/bin/bash
# 备份client.user_info表 脚本
/usr/local/mysql/bin/mysqldump -uroot -p123456 client user_info >backup/client.user_info-$(date +%Y%m%d).sql
[root@localhost ~]# chmod +x /opt/bak_client.sh
[root@localhost ~]# crontab -e
0 0 * * * /opt/bak_client.sh
MySQL数据库备份脚本
实验环境：
mysql-server：192.168.200.101 服务端
mysql-client：192.168.200.102 服务器
实验要求：对mysql-server的class库和client库实现异地备份，每天凌晨2:00进行备份，撰写一个数据恢复脚本。
MySQL服务端授权，给予select和lock tables权限，以备份
[root@localhost ~]# mysql -uroot -p123456
mysql> grant select,lock tables on class.* to 'root'@'192.168.200.11' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select,lock tables on client.* to 'root'@'192.168.200.11' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
客户端安装客户端软件
[root@client ~]# yum -y install mysql
连接测试
[root@client ~]# mysql -uadmin -p -h192.168.200.10
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| class |
| client |
撰写客户端备份脚本
[root@client ~]# vim /opt/bakmysql.sh
#!/bin/bash
# MySQL数据库备份脚本
# 设置登录变量
MY_USER="root"
MY_PASS="123456"
MY_HOST="192.168.200.10"
MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"
# 设置备份的数据库
MY_DB1="class"
MY_DB2="client"
# 定义备份路径、工具、时间、文件名
BF_DIR="backup"
BF_CMD="/usr/bin/mysqldump"
BF_TIME=$(date +%Y%m%d-%H%M)
NAME_1="$MY_DB1-$BF_TIME"
NAME_2="$MY_DB2-$BF_TIME"
# 备份为.sql脚本，然后打包压缩(打包后删除原文件)
[ -d $BF_DIR ] || mkdir -p $BF_DIR
cd $BF_DIR
$BF_CMD $MY_CONN --databases $MY_DB1 > $NAME_1.sql
$BF_CMD $MY_CONN --databases $MY_DB2 > $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
[root@client ~]# chmod +x /opt/bakmysql.sh
[root@client ~]# /opt/bakmysql.sh
[root@client ~]# ls backup/
class-20191012-1805.tar.gz client-20191012-1805.tar.gz
[root@client ~]# crontab -e
0 2 * * * /opt/bakmysql.sh
[root@client ~]#systemctl restart crond
[root@client ~]# /opt/bakmysql.sh
[root@client ~]# ls backup/
class-20191012-1805.tar.gz client-20191012-1805.tar.gz
撰写数据恢复脚本
[root@client ~]# vim /opt/restore_mysql.sh
#!/bin/bash
# 恢复MySQL数据库数据脚本
# 设置变量
MY_USER="root"
MY_PASS="123456"
MY_HOST="192.168.200.10"
BF_DIR="backup"
mkdir .aaa
ls $BF_DIR |column -t > .aaa/db_list
awk -F'-' '{print $2}' .aaa/db_list > .aaa/dt.txt
read -p "请指定要恢复数据库的日期(YYYYMMDD):" dt
if [ $dt -ge 20191001 ] && [ $dt -le 20191031 ];then
grep "$dt" .aaa/dt.txt &>/dev/null
if [ $? -ne 0 ];then
echo "很抱歉,您恢复数据库的备份日期不再备份日期范围内"
else
echo "搜索到的可恢复数据库如下:"
awk -F'-' /$dt/'{print NR,$1}' .aaa/db_list
read -p "请选择您要恢复数据库的编号: " nb
nm=$(awk -F'-' /$dt/'{print NR,$1}' .aaa/db_list |awk /$nb/'{print $2}')
echo "现在开始恢复数据库:$nm到$dt"
cd $BF_DIR
onm=$(ls |grep "$nm-$dt")
mkdir .bbb
tar xf $onm -C .bbb
mysql -u$MY_USER -p$MY_PASS -h$MY_HOST $nm < .bbb/*
echo "$nm已经恢复到$dt"
rm -rf .bbb
cd - &>/dev/null
rm -rf .aaa
fi
else
echo "很抱歉,您恢复数据库的备份日期不再备份日期范围内"
fi
[root@client ~]# chmod +x /opt/restore_mysql.sh
如在客户端恢复数据，需要开放权限
mysql> grant all on class.* to 'root'@'192.168.200.11';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on client.* to 'root'@'192.168.200.11';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
恢复测试：
[root@localhost ~]# /opt/restore_mysql.sh
mkdir: 无法创建目录".aaa": 文件已存在
请指定要恢复数据库的日期(YYYYMMDD):20191012
搜索到的可恢复数据库如下:
1 class
2 client
请选择您要恢复数据库的编号: 2
现在开始恢复数据库:client到20191012
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000) at line 22: Access denied for user 'root'@'192.168.200.11' to database 'client'
client已经恢复到20191012
[root@localhost ~]# /opt/restore_mysql.sh
请指定要恢复数据库的日期(YYYYMMDD):20191014
很抱歉,您恢复数据库的备份日期不再备份日期范围内