飞天班第48节：数据库高级应用-1

1、下载rpm包
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

# 如果提示需要账号密码，就用这个方式下载
# wget --http-user=youremail@email.com --http-passwd=yourpassword https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

2、查看系统是否自带mariadb
rpm -qa | grep mariadb
[root@alibaba local]# rpm -qa | grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64

3、将查出的mariadb进行卸载
rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64

4、把刚刚下载的mysql tar解压
tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

5、在解压目录安装如下4个mysql核心包
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

6、安装到server时缺少依赖报错
[root@alibaba mysql5.7]# rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
        libaio.so.1()(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64
        libaio.so.1(LIBAIO_0.1)(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64
        libaio.so.1(LIBAIO_0.4)(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64

7、安装缺少的依赖
yum -y install libaio

8、再次安装server
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

9、启动mysql服务
[root@alibaba ~]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service

10、查看v5.7版本的默认登录密码,只能本地登录
[root@alibaba ~]# grep password /var/log/mysqld.log
2020-06-19T04:30:50.288394Z 1 [Note] A temporary password is generated for root@localhost: =GDs4dKhDTJT

11、登录到mysql命令行，修改默认密码
[root@alibaba ~]# mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';

12、会告诉你密码不符合规范，可以修改校验等级和长度后再次执行上面的语句
mysql> set global validate_password_policy=LOW;
mysql> set global validate_password_length=6; 

13、账户授权
mysql> use mysql;
mysql> select host,user from user;
# 所有ip都可以访问数据库
mysql> grant all privileges on *.* to gavin@'%' identified by '123456';
# 也可以设置只有内网网段ip才可访问，并授权账号可以授权其他人,%不限制访问的ip
# mysql> grant all privileges on *.* to gavin@'192.168.%' identified by '123456' with grant option;
mysql> flush privileges;

rpm -qa | grep -i mysql
# 1、将查出来的包名卸载
rpm -e –nodeps 包名
# 查看mysql是否开启启动
chkconfig --list | grep -i mysql
# 2、删除mysql开启启动服务
chkconfig --del mysql
# 3、然后找出OS中分散的mysql文件夹，并删除
[root@helloworld mysql]# find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
/etc/rc.d/init.d/mysql
/run/lock/subsys/mysql
/var/spool/mail/mysql
/usr/bin/mysql
/usr/local/mysql
/usr/local/mysql/bin/mysql
/usr/local/mysql/data/mysql
/usr/local/mysql/include/mysql
/usr/lib64/mysql
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql
/usr/share/mysql
/home/mysql
/www/aikomj.github.io/assets/images/2020/icoding/mysql
/www/aikomj.github.io/_posts/2020/mysql
/www/jekyll-blog/mysql
/www/jekyll-blog/assets/images/2020/icoding/mysql
/root/mysql
/root/mysql/mysql

# 声明使用查询缓存，但是不建议这样使用
select SQL_CACHE * from ad_user;
# 怎么保证数据一致性， 查询缓存失效是非常频繁的，查询缓存被清空：1.表结构变化，2.增加数据，3.修改数据 
# mysql底层会监控commit事件和ddl,判断查询缓存是否应该被清空。 
# 注意：
	1、MySQL 8.0 把查询缓存这个模块去掉了，这说明查询缓存的作用不大
	2、判断用户查询权限是在执行器阶段开始的
	3、查询缓存会随着结果定期更新
# 核心三步
- 分析
- 优化
- 执行 

# 看MySQL支持的数据引擎8个
mysql> show engines;

# 查询表的引擎,一般客户端工具也可以查询，如navicat
show table status like '%ad_user%' \G;
# 创建表的时候可以选择数据引擎

cd /var/lib/mysql
# 这个目录下存放的是数据库对应的各个数据库的数据文件,直接把数据库文件icoding_admin备份到别的服务器，实现数据库冷备份/数据库迁移

linux下Mysql启动时读取配置文件的顺序为
1./etc/my.cnf 
2./etc/mysql/my.cnf
3./usr/local/mysql/etc/my.cnf（自己安装mysql的目录）
4.~/my.cnf 

# 查找my.cnf文件
sudo find / -name my.cnf
#第二种
sudo mysql --help | grep my.cnf

mysql> show variables like '%log_error%';
# mysql配置文件my.cnf配置如下，修改log_error的文件路径，错误级别
log_error=/var/log/mysqld.log
log_warnings=2
# log_warnings= 0| 1| 2 
# 0 关闭
# 1 开启-default
# >1 大于1,失败的连接，拒绝访问的错误也会记录到log_error

mysql> show variables like '%general_log%';
# mysql配置文件/etc/my.cnf配置
log_output=FILE
FILE、TABLE、FILE,TABLE、NONE(不输出)

mysql> show variables like '%slow%';

# 1、mysql配置文件/etc/my.cnf配置慢查询相关参数
[root@alibaba mysql]# vi /etc/my.cnf
slow_query_log=ON  # 开启
slow_launch_time=3  
# slow_query_log_file=/usr/local/mysql/slow.log 慢sql语句日志文件，修改后要授权mysql用户
# 修改/usr/local目录的拥有者是mysql用户
[root@alibaba mysql]# chown -R mysql:mysql /usr/local/mysql/slow.log
# 2、重启mysql
[root@alibaba mysql]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
# 3、tail -f 命令刷新查看慢sql日志文件
# 4、慢查询sql语句
mysql> use icoding_admin;
mysql> select sleep(4),username from ad_user; # 每条记录睡4秒

[root@alibaba mysql]# tail -f -n100 /var/lib/mysql/alibaba-slow.log 
/usr/sbin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2020-06-19T08:38:17.726855Z
# User@Host: root[root] @ localhost []  Id:     6
# Query_time: 12.000825  Lock_time: 0.000176 Rows_sent: 3  Rows_examined: 3
use icoding_admin;
SET timestamp=1592555897;
select sleep(4),username from ad_user;

# 查看sql语句慢查询时间
mysql> show variables like 'long_query_time';

# 帮助手册
mysqldumpslow --help
# 根据时间降序
mysqldumpslow -s -t /var/lib/mysql/alibaba-slow.log 
# 根据记录数降序
mysqldumpslow -s -r /var/lib/mysql/alibaba-slow.log 
# 根据执行次数降序
mysqldumpslow -s -c /var/lib/mysql/alibaba-slow.log 

 # 默认关闭 
 mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

# 查询mysql当前使用的binlog模式
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

# 查询binlog单个文件的最大大小，默认是1g,文件达到1g，创建新的binlog文件
mysql> show variables like '%max_binlog%';
| max_binlog_size      | 1073741824 

# 查看当前正在记录操作的日志log_bin文件名称
mysql > show master status;

# my.cnf开启binlog和binlog文件大小，一般设置500m,根据公司要求数据库情况自己调整
# 开启binlog看下面的两种模式

# 新建文件夹binlog
[root@alibaba local]# mkdir binlog
# 给mysql用户授权
[root@alibaba local]# chown -R mysql:mysql /usr/local/binlog/
# 修改mysql配置文件my.cnf，开启statement模式
[root@alibaba local]# vi /etc/my.cnf 
[mysqld]
server-id=213   # mysql5.7版本一定要加server-id，5.6可不加
log_bin=/usr/local/binlog/mysql-bin   # MySQL会自动生成一个mysql-bin-00001.log
binlog_format=statement
# binlog日志切割的大小
max_binlog_size=500m
# binlog过期清理时间,有效期3天
expire_logs_days=3

# 重启mysql
[root@alibaba local]# service mysqld restart

[root@alibaba binlog]# ls
mysql-bin.000001  mysql-bin.index

# 查看
mysql> show binlog events in 'mysql-bin.000001';

# 修改my.cnf 将statement模式修改为row模式
[root@alibaba binlog]# vi /etc/my.cnf
binlog_format=row
# 重启mysql
[root@alibaba binlog]# service mysqld restart

# 重启后发现多了个mysql-bin.000002文件，因为模式不同
[root@alibaba binlog]# ls
mysql-bin.000001  mysql-bin.000002  mysql-bin.index

# row模式的日志查询, -v 显示更新语句 -vv 显示更新语句+显示字段
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002

delete from ad_user where id =3

[root@alibaba binlog]# mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002

[root@alibaba binlog]# mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002

[root@alibaba binlog]# mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002

mysqlbinlog --base64-output=decode-rows --start-datetime='2020-06-19 22:00' --stop-datetime='2020-06-19 22:05' -vv mysql-bin.000002

# 查看帮助命令
mysqlbinlog --help

mysql> set sql_log_bin=0; #临时关闭binlog

[root@alibaba binlog]# mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002

# 混合模式
statement：95%

# 查看帮助命令
mysqldump --help

# --master-data 0(不记录position) 1(记录position位置) 2(记录position位置并注释该条解析的语句)
# routines 存储过程
# triggers 触发器
# events 事件
# single-transaction 避免导出数据的过程中mysql卡顿 ，在开始备份前会对导出数据内容做镜像再导出，在导出的过程中，独立一个事务是隔离的，保证数据的一致性
# --ignore-table=icoding_admin.ad_user_role --ignore-table=icoding_admin.ad_user  忽略多表
# 基于innodb引擎 ，-p密码 -P端口
# --flush-logs 刷新，生成新的binlog日志
mysqldump -uroot -p123456 -h127.0.0.1 --master-data=2 --routines --triggers --events --single-transaction --databases icoding_admin --ignore-table=icoding_admin.ad_user_role > mydb.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| icoding_admin      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# 删除数据库
mysql> drop database icoding_admin;
Query OK, 3 rows affected (0.04 sec)

# 查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# 恢复数据库
mysql> source mydb.sql;

# 查看数据库,icoding_admin又回来啦
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| icoding_admin      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysqldump -uroot -p123456 -h127.0.0.1 --master-data=2 --routines --triggers --events --lock-tables --databases icoding_admin --ignore-table=icoding_admin.ad_user_role > mydb.sql

[root@alibaba binlog]# crontab --help
crontab [-u username]　　　　//省略用户表表示操作当前用户的crontab
    -e      (编辑工作表)
    -l      (列出工作表里的命令)
    -r      (删除工作表)

# 每1分钟执行一次myCommand
* * * * * myCommand

# 每小时的第3和第15分钟执行
3,15 * * * * myCommand

# 在上午8点到11点的第3和第15分钟执行
3,15 8-11 * * * myCommand

# 每隔两天的上午8点到11点的第3和第15分钟执行
3,15 8-11 */2  *  * myCommand

# 每周一上午8点到11点的第3和第15分钟执行
3,15 8-11 * * 1 myCommand

# 每晚的21:30重启smb
30 21 * * * /etc/init.d/smb restart

# 每月1、10、22日的4 : 45重启smb
45 4 1,10,22 * * /etc/init.d/smb restart

# 每周六、周日的1 : 10重启smb
10 1 * * 6,0 /etc/init.d/smb restart

# 每天18 : 00至23 : 00之间每隔30分钟重启smb
0,30 18-23 * * * /etc/init.d/smb restart

# 每星期六的晚上11 : 00 pm重启smb
0 23 * * 6 /etc/init.d/smb restart

# 每一小时重启smb
* */1 * * * /etc/init.d/smb restart

# 晚上11点到早上7点之间，每隔一小时重启smb
* 23-7/1 * * * /etc/init.d/smb restart

# 1、先做全量备份,可以定时处理
# 编写备份的脚本
[root@alibaba binlog]# vim icoding_admin_bak.sh
backupdir=/usr/local/binlog/bak
time=` date +%Y%m%d%H%M `
# --master-data=2 记录position位置
mysqldump -uroot -p123456 -h127.0.0.1 --master-data=2 --single-transaction --databases icoding_admin  > $backupdir/icoding_admin_bak_$time.sql
# 删除过期的备份，这里是超过6天
find $backupdir -name "icoding_admin_bak_*.sql" -type f -mtime +6 -exec rm {} \; > /dev/null 2>&1

# crontab添加定时执行脚本的任务
[root@alibaba binlog]# crontab -e
13 14 * * * sh /usr/local/binlog/icoding_admin_bak.sh

 # 添加一条数据
 insert into ad_user VALUES(4,'allen','1234567')

mysql> drop database icoding_admin;  
Query OK, 3 rows affected (0.03 sec)

[root@alibaba bak]# cat icoding_admin_bak_202006211413.sql 

mysql> show binlog events in 'mysql-bin.000002';

# 2、开启binlong，statement/row/mixed三种模式，默认使用row模式
# 全量恢复数据，也可考虑是否binlog
mysql> source icoding_admin_bak_202006211413.sql.sql
# 对binlog做数据导出
# 场景1:如果数据量小，比如关键的某一条数据，row模式下可以直接手工处理，row模式可以看被修改删除的数据，直接自己拿出来重新写回
mysqlbinlog --base64-output=decode-rows --start-position=6311 --stop-position=6542  -vv mysql-bin.000002

# 场景2:数据量特别大，需要导出进行处理
mysqlbinlog --start-position=6311 --stop-position=6542 mysql-bin.000002 > ist.sql

# 3、增量数据的恢复根据需要把当前会话binlog记录关掉，避免记录没必要的操作，提高恢复效率
mysql> set sql_log_bin=0;
mysql> source ist.sql

# 导出增量备份的数据
[root@alibaba binlog]# mysqlbinlog --start-position=6311 --stop-position=6542 mysql-bin.000002 > bak/ist.sql
[root@alibaba binlog]# cd bak
[root@alibaba bak]# ls
icoding_admin_bak_202006211413.sql  ist.sql
# 进入mysql命令行模式恢复数据
mysql> source icoding_admin_bak_202006211413.sql
# 全量备份恢复完毕查看数据 
mysql> use icoding_admin;
Database changed
mysql> select * from ad_user;
+----+------------+----------+
| id | username   | password |
+----+------------+----------+
|  1 | arry       | 1234567  |
|  2 | gavin      | 1234567  |
|  3 | coding_qin | 1234567  |
+----+------------+----------+
# 增量恢复，不记录binlog
mysql> set sql_log_bin=0;
mysql> source ist.sql
# 增量备份恢复完毕查看数据，allen老师也恢复了
mysql> select * from ad_user;
+----+------------+----------+
| id | username   | password |
+----+------------+----------+
|  1 | arry       | 1234567  |
|  2 | gavin      | 1234567  |
|  3 | coding_qin | 1234567  |
|  4 | allen      | 1234567  |
+----+------------+----------+
# 恢复完成
mysql> exit;
Bye

# --quick : 该选项在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
# --all-databases 备份所有数据库
# --databases 指定备份的数据库
# --flush-logs 生成新的二进制日志
# --delete-master-logs 
# --single-transaction 单事务，为了确保数据的一致性，为了确保使用--single-transaction命令时，保证dump文件的有效性。需没有下列语句ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE，因为一致性读不能隔离上述语句。所以如果在dump过程中，使用上述语句，可能会导致dump出来的文件数据不一致或者不可用。
mysqldump -uroot -p123456 --quick --events --all-databases --flush-logs --delete-master-logs --single-transaction > data.sql

# 初始化时，创建相应目录
# mkdir -p /home/mysql/backup/daily 

# 定时任务
# 每个星期日凌晨3:00执行全量备份脚本 
# 0 3 * * 0 /bin/bash -x /root/mysqlFullBack.sh >/dev/null 2>&1
# 周一到周六凌晨3:00执行增量备份脚本
# 0 3 * * 1-6 /bin/bash -x /root/mysqlPartBack.sh >/dev/null 2>&1

BakDir=/home/mysql/backup
LogFile=/home/mysql/backup/bak.log
Date=`date +%Y%m%d`
Begin=`date +"%Y年%m月%d日 %H:%M:%S"`
cd $BakDir
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tgz
mysqldump -uroot -p123456 --quick --events --all-databases --flush-logs --delete-master-logs --single-transaction > $DumpFile
/bin/tar -zvcf $GZDumpFile $DumpFile
/bin/rm $DumpFile
Last=`date +"%Y年%m月%d日 %H:%M:%S"`
echo [FullBack] 开始:$Begin 结束:$Last $GZDumpFile successful >> $LogFile
# scp重复全量备份文件到其他服务器
# scp $GZDumpFile root@xxxx:/usr/mysql/backup/$GZDumpFile
# 删除30天前的全量备份文件
find $BakDir -mtime +30 -type f -name "*.sql.tgz" | xargs rm -f
# 删除增量备份文件
cd $BakDir/daily
/bin/rm -f *

mysql > source /home/data.sql;

# 在/var/lib/mysql下或/var/lib/mysql/mysql-bin下可查看到增量备份日志
mysqladmin -uroot -p123456 flush-logs

#!/bin/bash
#在使用之前，请提前创建以下各个目录
backupDir=/home/mysql/backup/daily
#增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
mysqlDir=/var/lib/mysql
#mysql的数据目录
logFile=/home/mysql/backup/bak.log
BinFile=/var/lib/mysql/binlog.index
#mysql的index文件路径，放在数据目录下的

mysqladmin -uroot -p123456 flush-logs
#这个是用于产生新的mysql-bin.00000*文件
# wc -l 统计行数
# awk 简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do
    base=`basename $file`
    echo $base
    #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
    NextNum=`expr $NextNum + 1`
    if [ $NextNum -eq $Counter ]
    then
        echo $base skip! >> $logFile
    else
        dest=$backupDir/$base
        if(test -e $dest)
        #test -e用于检测目标文件是否存在，存在就写exist!到$logFile去
        then
            echo $base exist! >> $logFile
        else
            cp $mysqlDir/$base $backupDir
            echo $base copying >> $logFile
         fi
     fi
done
echo [PartBack] `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup successful! >> $logFile

mysqlbinlog /home/mysql/backup/daily/binlog.000008 | mysql -uroot -p123456;

0 3 * * 0 /bin/bash -x /root/mysqlFullBack.sh >/dev/null 2>&1

0 3 * * 1-6 /bin/bash -x /root/mysqlPartBack.sh >/dev/null 2>&1

[root@alibaba ~]# which mysqldump
/usr/bin/mysqldump