MySQL数据库入门(四)
昨日回顾:
授权
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
今日作业:
写简单的备份数据的脚本
实现如下功能:
每周日凌晨 2点 备份使用 mysqldump 工具对如下数据库进行备份:
- db1
- db2
每天的凌晨 2点 备份逻辑库 mysql 的所有表
每个月的 25 日使用 xtrabackup 工具对数据库进行全量备份
以上的备份文件,要求在当天把备份好的文件传输到远程备份服务器:
192.168.122.100 上的 /backups/ 目录下。并按照当天的日期
分别存放(应该在备份服务器上创建名称为当天日期的目录)。ssh root@192.168.122.100 “mkdir /backups/
date +%F
”