MySQL数据库入门（四）---授权、查询、分组、逻辑备份mysqldump，全表备份XtraBackup

MySQL数据库入门（四）

文章目录

• MySQL数据库入门（四）
• • @[toc]
  • • 昨日回顾：
    • • 授权
      • • 客户端来源:
      • 多表查询
    • 单表查询
    • • 基础查询
      • 多表查询
      • • a、条件
        • b、通配符
        • c、限制
        • d、排序
        • *e、分组
        • f、嵌套查询
    • MySQL 逻辑备份 mysqldump
    • • 特点
      • 用法
      • /*查看帮助*/
      • 常见参数
      • 备份
      • 日常用法
      • • 备份所有库
        • 备份指定的多个库
      • 全备
      • • • 下面为完成后的输出结果
    • 今日作业：
    • • 写简单的备份数据的脚本

昨日回顾：

授权

5.7:
grant 权限 on 逻辑库.表
to 用户名@‘客户端来源’
identified by ‘密码’;
权限
all
select
update,delete

db_name.*
db_name.t
*.*

客户端来源:

• 192.168.1.100
• 192.168.1.1_
• 192.168.1.%
• www.sharkyun.com # 需要此域名（主机名）被 MySQL Server 解析
• w%.sharkyun.com

*mysql -u用户名  -p'密码' -h MySQL 服务器 iP(主机名) -P 端口号

多表查询

外键：
设置外键属性的限制条件：参考的主表字段，必须是具备有主键属性

存储值的限制条件： 外键的值必须是在主表有的值

单表查询

基础查询

  select * from 表
  select * from 表 where id > 2
  select id,name,age as gg from 表 where id > 2

多表查询

a、条件

select * from 表 where id > 1 and name != '王麻子' and age = 18;
select * from 表 where id between 5 and 16;
select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select id from 表)

b、通配符

    select * from 表 where name like 'sha%'  - sha开头的所有（多个字符串）
    select * from 表 where name like 'shar_'  - sha开头的所有（一个字符）

c、限制

    select * from 表 limit 5;            - 获取前 5 行
    select * from 表 limit 0,2;          - 从第 1 行开始, 取出 2 行, 包含第 1 行

d、排序

    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

*e、分组

    select age from 表 group by age
    select age,id from 表 group by age,id
    select age,id from 表  where id > 10 group by age,id order by id desc
    select age,id,count(*),sum(age),max(age),min(age) from 表 group by age,id - 分组统计
    select age from 表 group by age having max(id) > 10
    特别的：group by 必须在where之后，order by之前

f、嵌套查询

    select * from  (select name from t1 where age>18 and age < 25 order by id desc limit 2 ) as tt  order by id;

MySQL 逻辑备份 mysqldump

特点

自动记录position位置。
show master status\G;
可用性，一致性
锁表机制

用法

mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql

/查看帮助/

mysqldump --help

常见参数

–single-transaction 备份前启用一个事务，保证数据一致性。
仅对 InnoDB 存储引擎有效。还有需要保证没有其他的连接正在使用以下语句： ALTER TABLE， CREATE TABLE，DROP TABLE， RENAME TABLE， TRUNCATE TABLE-l, --lock tables 对于不支持事务的存储引擎的表备份使用此选项，比如 MySAM, 可以保证备份期间的数据一致性。会依次对正在备份的每个数据库中的所有表进行锁表操作，此时只可以读。和 --single-transaction 互斥。

-x, --lock-all-tables 锁定所有数据库中的所有表。这是通过在整个转储期间采用全局读锁来实现。

有几个选项控制mysqldump如何 处理存储的程序（存储过程和函数，触发器和事件）：

–events：备份事件计划程序事件

–routines：备份存储过程和函数

–triggers：备份表的触发器

–triggers默认情况下启用 该选项，以便在转储表时，它们伴随着它们具有的任何触发器。

以上的选项都是默认开启的，如果不想备份以上的内容，需要明确设置这些选项：
–skip-events， --skip-routines, --skip-triggers。

备份

[root@localhost ~]# mysqldump -u root -p'QianFeng@123' --all-databases > all-dump.sql
[root@localhost ~]# ls
all-dump.sql
[root@localhost ~]# du all-dump.sql -h
4.0K    all-dump.sql

日常用法

备份所有库

[root@localhost ~]# mysqldump -uroot -p'QFedu123@'  -h  172.16.153.10 --all-databases > `date +%FT%H_%M_%S`dump_all.sql

不包含 INFORMATION_SCHEMA,performance_schema,sys

备份指定的多个库

// 为了考虑篇幅，请自行添加指定用户名密码参数和指定服务器的参数
[root@localhost ~]# mysqldump --databases db1 db2 db3 > `date +%FT%H_%M_%S`dump_all.sql
备份指定库的指定几个表
[root@localhost ~]# mysqldump db1 t1 t3 t7 > dump.sql

全备

确保安装 EPEL 源

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

安装 libev
为了成功安装Percona XtraBackup libev包需要先安装。

yum  install -y libev

安装Percona存储库

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

安装软件

shell> yum install percona-xtrabackup-24

mkdir /backups
xtrabackup --backup --user=root --password='QianFeng@123'  --target-dir=/backups/full

恢复
停止数据库的服务
清理环境
修改权限
启动数据库

systemctl stop mysqld.service

rm -rf /var/lib/mysql/ *

xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backups/full

下面为完成后的输出结果

180818 10:59:25 [01]        ...done
180818 10:59:25 completed OK!
shell> chown  mysql.mysql -R /var/lib/mysql

或者使用 rsync 命令

shell> rsync -avrP /backup/ /var/lib/mysql/
shell> chown  mysql.mysql -R /var/lib/mysql

启动数据库

shell> systemctl start mysqld.service
innobackuper 命令实现

shell> innobackupex --defaults-file=/etc/my.cnf --host=192.168.1.146 --user=root --password=123123 /backup

shell> innobackupex --apply-log --use-memory=4G /backups/2018-08-17_15-53-11

shell> systemctl stop mysqld.service

shell> rm -rf /var/lib/mysql/ *

shell> innobackupex --datadir=/var/lib/mysql --copy-back 2018-08-17_15-53-11

shell> chown  mysql.mysql -R /var/lib/mysql

shell> systemctl start mysqld.service

今日作业：

写简单的备份数据的脚本

实现如下功能：

1. 每周日凌晨 2点 备份使用 mysqldump 工具对如下数据库进行备份：

   • db1
   • db2

2. 每天的凌晨 2点 备份逻辑库 mysql 的所有表

3. 每个月的 25 日使用 xtrabackup 工具对数据库进行全量备份

4. 以上的备份文件，要求在当天把备份好的文件传输到远程备份服务器：
   192.168.122.100 上的 /backups/ 目录下。并按照当天的日期
   分别存放（应该在备份服务器上创建名称为当天日期的目录）。

ssh root@192.168.122.100 “mkdir /backups/date +%F”