首先建立信息数据库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
[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
&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; mysql -u$MY_USER -p$MY_PASS -h$MY_HOST $nm <.bbb/*
&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; echo "$nm已经恢复到$dt"
&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; rm -rf .bbb
&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; cd - &>/dev/null
&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; rm -rf .aaa
&#160;&#160;&#160;&#160;&#160;&#160;&#160; fi
else
echo "很抱歉,您恢复数据库的备份日期不再备份日期范围内"
fi
&#160;
[root@client ~]# chmod +x /opt/restore_mysql.sh
如在客户端恢复数据,需要开放权限
&#160;
mysql> grant all on class.* to &#39;root&#39;@&#39;192.168.200.11&#39;;
Query OK, 0 rows affected (0.00 sec)
&#160;
mysql> grant all on client.* to &#39;root&#39;@&#39;192.168.200.11&#39;;
Query OK, 0 rows affected (0.00 sec)
&#160;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
&#160;
恢复测试:
[root@localhost ~]# /opt/restore_mysql.sh
mkdir: 无法创建目录".aaa": 文件已存在
请指定要恢复数据库的日期(YYYYMMDD):20191012&#160;
搜索到的可恢复数据库如下:
1 class
2 client
请选择您要恢复数据库的编号: 2&#160;&#160;&#160;&#160;
现在开始恢复数据库: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 &#39;root&#39;@&#39;192.168.200.11&#39; to database &#39;client&#39;
client已经恢复到20191012
&#160;
[root@localhost ~]# /opt/restore_mysql.sh
请指定要恢复数据库的日期(YYYYMMDD):20191014
很抱歉,您恢复数据库的备份日期不再备份日期范围内