1. 总结关系型数据库相关概念,关系,行,列,主键,唯一键,域。
- 关系Relational :关系就是二维表,其中:表中的行、列次序并不重要
- 行row:表中的每一行,又称为一条记录record
- 列column:表中的每一列,称为属性,字段,域field
- 主键Primary key:PK ,一个或多个字段的组合, 用于惟一确定一个记录的字段,一张表只有一个主键, 主键字段不能为空NULL
- 唯一键Unique key: 一个或多个字段的组合,用于惟一确定一个记录的字段,一张表可以有多个UK,而且UK字段可以为NULL
- 域domain:属性的取值范围,如,性别只能是'男'和'女'两个值,人类的年龄只能0-150
2. 总结关联类型,1对1,1对多,多对多关系。可以自行设计表进行解释。
- 一对一联系(1:1): 在表A或表B中创建一个字段﹐存储另一个表的主键值 如: 一个人只有一个身份证
- 一对多联系(1:n):外键, 如: 部门和员工
- 多对多联系(m:n):增加第三张表, 如: 学生和课程
学生表student:
id name
1001 张三
1002 张四
学生卡表cardid name1001 card11002 card2#添加外键学生卡表cardid name student_id(设置为唯一键)1001 card1 10011002 card2 1002
表A:学生表student(子表)
id name class_id(外键非空:班级id)
1001 张三 111
1002 张四 222
1003 王五 111
1004 赵六 111表B:班级表class(父表)
id name
111 class1
222 class2
一个班级对应多个学生,一个学生只能对应一个班级
学生表student:id name
1001 张三
1002 张四
1003 王五
1004 赵六
课程表course:id name
111 java
222 mysql#额外创建一个关系表学生课程关系表 student_course_relationstudent_id course_id
1001 111
1001 222
1002 111
1002 222
3. 总结mysql设计范式
第一范式:1NF
无重复的列,每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有 多个值或者不能有重复的属性,确保每一列的原子性。除去同类型的字段,就是无重复的列
下面这个例子就不符合第一范式:
ID | 课程名称 |
1 | 语文,数学,英语 |
说明:第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库
第二范式:2NF
第二范式必须先满足第一范式,属性完全依赖于主键,要求表中的每个行必须可以被唯一地区分,通常为表加上每行的唯一标识主键PK,非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键
订单号 | 商品编号 | 商品名 | 数量 | 单价 |
1001 | 001 | 西瓜 | 5 | 6.0 |
1002 | 002 | 草莓 | 10 | 8.0 |
订单号和商品编号组合作为主键,但商品名只依赖于商品编号而不是订单号和商品编号,因此不符合第二范式。
第三范式:3NF
满足第三范式必须先满足第二范式属性,非主键属性不依赖于其它非主键属性。第三范式要求一个数据表中不包含已在其它表中已包含的非主关键字信息,非PK的字段间不能有从属关系
学号 | 姓名 | 班级 | 班主任 | 性别 |
---|---|---|---|---|
001 | 张三 | 一班 | 李老师 | 男 |
002 | 李四 | 二班 | 王老师 | 男 |
003 | 王五 | 一班 | 李老师 | 男 |
班级和班主任都依赖于班级,因此存在传递依赖关系。
4. 总结Mysql多种安装方式,及安全加固,并总结mysql配置文件, 完成将server和client端的mysql配置默认字符集为utf8mb4;
4.1 Mysql安装
- 程序包管理器管理的程序包
- 源代码编译安装
- 二进制格式的程序包:展开至特定路径,并经过简单配置后即可使用
- 项目官方:https://downloads.mariadb.org/mariadb/repositories/
- 国内镜像:https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/
- https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/
- mysql-server:8.0
- mariadb-server : 10.3
- mariadb-server:5.5 服务器包
- mariadb 客户端工具包
- mysql-server:5.1 服务器包
- mysql 客户端工具包
[root@centos7 ~] #tee /etc/yum.repos.d/mysql.repo <<EOF[mysql]name = mysql5.7baseurl = https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/gpgcheck = 0EOF[root@centos7 ~] #yum -y install mysql-community-server[root@centos7 ~] #systemctl enable --now mysqld[root@centos7 ~] #ss -ntlState Recv-Q Send-Q Local Address:Port Peer Address:PortLISTEN 0 128 *:22 *:*LISTEN 0 100 127 .0.0.1:25 *:*LISTEN 0 128 [::]:22 [::]:*LISTEN 0 100 [::1]:25 [::]:*LISTEN 0 80 [::]:3306 [::]:*[root@centos7 ~] #mysqlERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password:NO)[root@centos7 ~] #grep password /var/log/mysqld.log2021 -01-27T00 :45:09.953242Z 1 [Note] A temporary password is generated forroot@localhost: pe%b #S8ah)j-2021 -01-27T00 :46:09.491494Z 2 [Note] Access denied for user 'root' @ 'localhost'(using password: NO)# 修改初始密码方法 1[root@centos7 ~] #mysql -uroot -p'pe%b#S8ah)j-'mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5 .7.33Copyright (c) 2000 , 2021 , Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.# 使用初始密码登录无法执行操作 , 需要修改密码后才可以mysql> statusERROR 1820 (HY000): You must reset your password using ALTER USER statementbefore executing this statement.# 修改简单密码不符合密码策略mysql> alter user root@ 'localhost' identified by 'Magedu2021' ;ERROR 1819 (HY000): Your password does not satisfy the current policyrequirements# 修改为复杂密码mysql> alter user root@ 'localhost' identified by 'Magedu0!' ;Query OK, 0 rows affected (0.00 sec)mysql> status--------------mysql Ver 14 .14 Distrib 5 .7.33, for Linux (x86_64) using EditLine wrapperConnection id: 4Current database:Current user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5 .7.33Protocol version: 10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket: /var/lib/mysql/mysql.sockUptime: 3 min 38 secThreads: 1 Questions: 8 Slow queries: 0 Opens: 106 Flush tables: 1 Opentables: 99 Queries per second avg: 0 .036--------------mysql> exit# 修改初始密码方法 2[root@centos7 ~] #mysqladmin -uroot -p'pe%b#S8ah)j-' password 'Magedu0!'mysqladmin: [Warning] Using a password on the command line interface can beinsecure.Warning: Since password will be sent to server in plain text, use ssl connectionto ensure password safety
[root@ubuntu1804 ~] #apt install mysql-server[root@ubuntu1804 ~] #systemctl status mysql.service● mysql.service - MySQL Community ServerLoaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:enabled)Active: active (running) since Wed 2021 -01-27 15 :41:33 CST; 10min agoMain PID: 2115 (mysqld)Tasks: 28 (limit: 2290 )CGroup: /system.slice/mysql.service└─2115 /usr/sbin/mysqld --daemonize --pid-file = /run/mysqld/mysqld.pidJan 27 15 :41:33 ubuntu1804.magedu.org systemd[1]: Starting MySQL CommunityServer...Jan 27 15 :41:33 ubuntu1804.magedu.org systemd[1]: Started MySQL CommunityServer.[root@ubuntu1804 ~] #mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5 .7.32-0ubuntu0.18.04.1 (Ubuntu)Copyright (c) 2000 , 2020 , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> status--------------mysql Ver 14 .14 Distrib 5 .7.32, for Linux (x86_64) using EditLine wrapperConnection id: 3Current database:Current user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5 .7.32-0ubuntu0.18.04.1 (Ubuntu)Protocol version: 10 2.2.3 初始化脚本提高安全性运行脚本: mysql_secure_installation范例 : 针对 MySQL5.6 前版本进行安全加固Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket: /var/run/mysqld/mysqld.sockUptime: 10 min 43 secThreads: 1 Questions: 10 Slow queries: 0 Opens: 105 Flush tables: 1 Opentables: 98 Queries per second avg: 0 .015
例:脚本安装
#! /bin/bash
. /etc/init.d/functions
SRC_DIR=`pwd`
#MYSQL='mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz'
MYSQL='mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz'
COLOR='echo -e \E[01;31m'
END='\E[0m'
MYSQL_ROOT_PASSWORD=magedu
check (){
if [ $UID -ne 0 ]; then
action "当前用户不是root,安装失败" false
exit 1
fi
cd $SRC_DIR
if [ ! -e $MYSQL ];then
$COLOR"缺少${MYSQL}文件"$END
$COLOR"请将相关软件放在${SRC_DIR}目录下"$END
exit
elif [ -e /usr/local/mysql ];then
action "数据库已存在,安装失败" false
exit
else
return
fi
}
install_mysql(){
$COLOR"开始安装MySQL数据库..."$END
yum -y -q install libaio numactl-libs ncurses-compat-libs
cd $SRC_DIR
tar xf $MYSQL -C /usr/local/
MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
ln -s /usr/local/$MYSQL_DIR /usr/local/mysql
chown -R root.root /usr/local/mysql/
id mysql &> /dev/null || { useradd -s /sbin/nologin -r mysql ; action "创建mysql用户"; }
echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
ln -s /usr/local/mysql/bin/* /usr/bin/
cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
[ -d /data ] || mkdir /data
mysqld --initialize --user=mysql --datadir=/data/mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
service mysqld start
[ $? -ne 0 ] && { $COLOR"数据库启动失败,退出!"$END;exit; }
sleep 3
MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
mysqladmin -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
action "数据库安装完成"
}
check
install_mysql
4.2 安全加固
运行脚本:mysql_secure_installation
设置数据库管理员 root 口令禁止 root 远程登录删除 anonymous 用户帐号删除 test 数据库
范例: 针对MySQL5.6前版本进行安全加固
root@centos7 ~] #mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5 .6.51 MySQL Community Server (GPL)Copyright (c) 2000 , 2021 , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+ -------------------- +| Database |+ -------------------- +| information_schema || mysql || performance_schema |+ -------------------- +3 rows in set (0.00 sec)mysql> select user,host from mysql.user;+ ------ + -------------------------- +| user | host |+ ------ + -------------------------- +| root | 127 .0.0.1 || root | ::1 || | centos7.wangxiaochun.com || root | centos7.wangxiaochun.com || | localhost || root | localhost | + ------ + -------------------------- +6 rows in set (0.00 sec)mysql> exitBye[root@centos7 ~] #[root@centos7 ~] #mysql -uxxxWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5 .6.51 MySQL Community Server (GPL)Copyright (c) 2000 , 2021 , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> status--------------mysql Ver 14 .14 Distrib 5 .6.51, for Linux (x86_64) using EditLine wrapperConnection id: 3Current database:Current user: xxx@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5 .6.51 MySQL Community Server (GPL)Protocol version: 10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket: /var/lib/mysql/mysql.sockUptime: 5 min 2 secThreads: 1 Questions: 11 Slow queries: 0 Opens: 67 Flush tables: 1 Opentables: 60 Queries per second avg: 0 .036--------------mysql> exitBye[root@centos7 ~] #file `which mysql_secure_installation `/usr/bin/mysql_secure_installation: Perl script, ASCII text executable[root@centos7 ~] #mysql_secure_installationNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQLSERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MySQL to secure it, we 'll need the currentpassword for the root user. If you 've just installed MySQL, andyou haven 't set the root password yet, the password will be blank,so you should just press enter here. Enter current password for root (enter for none):OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MySQLroot user without the proper authorisation.Set root password? [Y/n] yNew password:Re-enter new password:Password updated successfully!Reloading privilege tables..... Success!By default, a MySQL installation has an anonymous user, allowing anyoneto log into MySQL without having to have a user account created forthem. This is intended only for testing, and to make the installationgo a bit smoother. You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y... Success!Normally, root should only be allowed to connect from 'localhost' . Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] y... Success!By default, MySQL comes with a database named 'test' that anyone canaccess. This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] y- Dropping test database...ERROR 1008 (HY000) at line 1 : Can 't drop database ' test '; database doesn' t exist... Failed! Not critical, keep moving...- Removing privileges on test database...... Success!Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [Y/n] y... Success!All done ! If you 've completed all of the above steps, your MySQLinstallation should now be secure.Thanks for using MySQL!Cleaning up...[root@centos7 ~] #
4.3 Mysql配置文件
/etc/my.cnf #Global 选项/etc/mysql/my.cnf #Global 选项~/.my.cnf #User-specific 选项
[mysqld][mysqld_safe][mysqld_multi][mysql][mysqladmin][mysqldump][server][client]
parameter = value
_ 和 - 相同1 , ON , TRUE 意义相同, 0 , OFF , FALSE 意义相同 , 无区分大小写
4.4 将server和client端的mysql配置默认字符集为utf8mb4
[root@Rocky8 ~]# vim /etc/my.cnf
#针对mysql客户端
[mysql]
default-character-set=utf8mb4#针对所有MySQL客户端
[client]
default-character-set=utf8mb4
5. 掌握如何获取SQL命令的帮助,基于帮助完成添加testdb库,字符集utf8, 排序集合utf8_bin.创建host表,字段(id,host,ip,cname等)
https://dev.mysql.com/doc/refman/8.0/en/sql-statements.htmlhttps: //dev.mysql.com/doc/refman/5.7/en/sql-statements.html
查看帮助:
#创建testdb数据库,设置字符集为utf8,排序规则utf8_bin。
mysql> create database testdb character set utf8 collate utf8_bin;
#创建host表,字段(id,host,ip.cname等)
mysql>create table host(id int auto_increment primary key, host varchar(255) not full, ip varchar(15)not full, cname varchar(255) )character set utf8 collate utf8_bin;
6. 根据表扩展出几个语句,完成总结DDL, DML的用法,并配上示例。
6.1 DDL语句
- 创建表 CREATE TABLE
- 查看表 SHOW TABLE
- 修改表和删除表 ALTER TABLE ; DROP TABLE
例:
创建表和字段名,并设置属性
19:02:10(root@localhost) [testdb]> CREATE TABLE student (
-> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(20) NOT NULL,
-> age tinyint UNSIGNED,
-> #height DECIMAL(5,2),
-> gender ENUM('M','F') default 'M'
-> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)
查询表结构desc student
删除表
DROP TABLE [ IF EXISTS ] 'tbl_name' ;
6.2 DML语句
INSERT 表插入数据
19:06:28(root@localhost) [testdb]> insert student (name,age) value("huang",26);
UPDATE 表更新数据
19:09:11(root@localhost) [testdb]> update student set age='18'where id='10';
DELETE 表删除数据
19:09:32(root@localhost) [testdb]> delete from student where id='10';
7. 总结mysql架构原理
- 长连接:使用MySQL客户端等于数据库后,直到使用exit退出数据库
- 短连接:使用mysql -e选项,客户端向服务端申请运行一个命令后立即退出
8. 总结myisam和Innodb存储引擎的区别。
- 不支持事务
- 表级锁定
- 读写相互阻塞,写入不能读,读时不能写
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 读取数据较快,占用资源较少
- 不支持MVCC(多版本并发控制机制)高并发
- 崩溃恢复性较差
- MySQL5.5.5 前默认的数据库引擎
- tbl_name.frm 表格式定义
- tbl_name.MYD 数据文件
- tbl_name.MYI 索引文件
- 行级锁
- 支持事务,适合处理大量短期事务
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库引擎
- 所有InnoDB表的数据和索引放置于同一个表空间中
数据文件:ibdata1, ibdata2,存放在datadir定义的目录下表格式定义:tb_name.frm,存放在datadir定义的每个数据库对应的目录下
- 每个表单独使用一个表空间存储表的数据和索引
两类文件放在对应每个数据库独立目录中数据文件(存储数据和索引):tb_name.ibd表格式定义:tb_name.frm
9. 总结mysql索引作用,同时总结哪些查询不会使用到索引。
- 索引可以降低服务需要扫描的数据量,减少了IO次数
- 索引可以帮助服务器避免排序和使用临时表
- 索引可以帮助将随机I/O转为顺序 I/O
- 占用额外空间,影响插入速度
- B+ TREE、HASH、R TREE、FULL TEXT
- 聚簇(集)索引、非聚簇索引:数据和索引是否存储在一起
- 主键索引、二级(辅助)索引
- 稠密索引、稀疏索引:是否索引了每一个数据项
- 简单索引、组合索引: 是否是多个字段的索引
- 左前缀索引:取前面的字符做索引
- 覆盖索引:从索引中即可取出要查询的数据,性能高
以下查询不会使用到索引:
- 全表扫描:当数据量大或者查询列未被索引时,可能会选择全表扫描
- 使用函数或者表达式:在查询条件中对列使用函数或者表达式可能导致索引失效
- 非最左前缀:对于复合索引,如果查询条件没有涉及最左边的列,那么索引可能不会被使用
- 数据类型不匹配:查询条件中的数据类型与索引列的数据类型不匹配可能导致索引失效
- OR条件:当使用OR条件时,如果每个条件涉及的列都未被同一个复合索引覆盖,则可能不会使用索引
- Like查询:当LIKE查询的模式以通配符开始时,索引可能不会被使用
- NULL值:对于包含NULL值的列,索引可能不会被使用。
10. 总结事务ACID事务特性
- A:atomicity 原子性;整个事务中的所有操作要么全部成功执行,要么全部失败后回滚
- C:consistency一致性;数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定律(N50周启皓语录)
- I:Isolation隔离性;一个事务所做出的操作在提交之前,是不能为其它事务所见;隔离有多种隔离级别,实现并发
- D:durability持久性;一旦事务提交,其所做的修改会永久保存于数据库中
11. 总结事务日志工作原理。
事务日志:transaction log
- redo log:记录某数据块被修改后的值,数据更新前先记录redo log( WALWrite Ahead Log ),可以用来恢复未写入data file的已成功事务更新的数据
- undo log:保存与执行的操作相反的操作,即记录某数据被修改前的值,可以用来在事务失败时进行rollback
命令:show variables like '%innodb_log%';innodb_log_file_size 50331648 # 每个日志文件大小innodb_log_files_in_group 2 # 日志组成员个数innodb_log_group_home_dir ./ # 事务文件路径
innodb_flush_log_at_trx_commit=0|1|2
1 此为默认值,日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能,但服务器崩溃可能丢失最后一秒的事务2 每次提交后都会写入OS的缓冲区,但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些,但操作系统或停电可能导致最后一秒的交易丢失
1.配置为2和配置为0,性能差异并不大,因为将数据从Log Buffer拷贝到OS cache,虽然跨越用户态与内核态,但毕竟只是内存的数据拷贝,速度很快2.配置为2和配置为0,安全性差异巨大,操作系统崩溃的概率相比MySQL应用程序崩溃的概率,小很多,设置为2,只要操作系统不奔溃,也绝对不会丢数据
12. 总结mysql日志类型,并说明如何启动日志。
mysql日志类型:
事务日志 transaction log:
包括redo log 和undo log。通过设置innodb_flush_log_at_trx_commit=0|1|2 决定事务日志何时被写入并刷新到磁盘中。默认启动
错误日志 log_error:
- mysqld启动和关闭过程中输出的事件信息
- mysqld运行中产生的错误信息
- event scheduler运行一个event时产生的日志信息
- 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
默认启用。
日志路径查询:SHOW GLOBAL VARIABLES LIKE 'log_error' ;
记录哪些警告信息至错误日志文件
#CentOS7 mariadb 5.5 默认值为1#CentOS8 mariadb 10.3 默认值为2log_warnings=0|1|2|3... #MySQL5.7之前log_error_verbosity=0|1|2|3... #MySQL8.0
通用日志 general_log:
记录对数据库的通用操作,包括:错误的SQL语句。
通用日志相关设置
启用:general_log=ON
日志路径:general_log_file=HOSTNAME.log
日志存储为哪种类型:log_output=TABLE|FILE|NONE
日志当前存放路径查询:SHOW GLOBAL VARIABLES LIKE 'log_output';
慢查询日志slow_query_log:
记录执行查询时长超出指定时长的操作
相关变量:
slow_query_log= ON |OFF # 开启或关闭慢查询,支持全局和会话,只有全局设置才会生成慢查询文件long_query_time=N # 慢查询的阀值,单位秒 , 默认为 10sslow_query_log_file=HOSTNAME- slow .log # 慢查询日志文件log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk# 上述查询类型且查询时长超过 long_query_time ,则记录日志log_queries_not_using_indexes= ON #不使用索引或使用全索引扫描,不论是否达到慢查询阀值的语句是否记录日志,默认OFF ,即不记录log_slow_rate_limit = 1 #多少次查询才记录, mariadb 特有log_slow_verbosity= Query_plan, explain #记录内容log_slow_queries = OFF # 同 slow_query_log , MariaDB 10.0/MySQL 5.6.1 版后已删除
二进制日志(备份):
- 记录导致数据改变或潜在导致数据改变的SQL语句
- 记录已提交的日志
- 不依赖于存储引擎类型
- 基于"语句"记录:statement,记录语句,默认模式( MariaDB 10.2.3 版本以下 ),日志量较少
- 基于"行"记录:row,记录数据,日志量较大,更加安全,建议使用的格式,MySQL8.0默认格式
- 混合模式:mixed, 让系统自行判定该基于哪种方式进行,默认模式( MariaDB 10.2.4及版本以上 )
show variables like 'binlog_format';
有两类文件1. 日志文件: mysql|mariadb-bin. 文件名后缀,二进制格式 , 如: on.000001,mariadb-bin.0000022. 索引文件: mysql|mariadb-bin.index ,文本格式 , 记录当前已有的二进制日志文件列表
sql_log_bin= ON |OFF: # 是否记录二进制日志,默认 ON ,支持动态修改,系统变量,而非服务器选项log_bin=/PATH/BIN_LOG_FILE: # 指定文件位置;默认 OFF ,表示不启用二进制日志功能,上述两项都开启才可以binlog_format=STATEMENT| ROW |MIXED: # 二进制日志记录的格式, mariadb5.5 默认 STATEMENTmax_binlog_size= 1073741824 : # 单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为 1G# 说明:文件达到上限时的大小未必为指定的精确值binlog_cache_size= 4 m # 此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接)max_binlog_cache_size= 512 m # 限制用于缓存多事务查询的字节大小。sync_binlog= 1 | 0 : # 设定是否启动二进制日志即时同步磁盘功能,默认 0 ,由操作系统负责同步日志到磁盘expire_logs_days=N: # 二进制日志可以自动删除的天数。 默认为 0 ,即不自动删除
二进制日志相关配置 :
查看 mariadb 自行管理使用中的二进制日志文件列表,及大小SHOW {BINARY | MASTER } LOGS查看使用中的二进制日志文件SHOW MASTER STATUS在线查看二进制文件中的指定内容SHOW BINLOG EVENTS [ IN 'log_name' ] [ FROM pos] [ LIMIT [ offset ,] row_count]范例:show binlog events in 'mysql-bin.000001' from 6516 limit 2 , 3
mysqlbinlog [OPTIONS] log_file…--start-position = # 指定开始位置--stop-position = #--start-datetime = #时间格式: YYYY-MM-DD hh:mm:ss--stop-datetime =--base64-output [ = name]-v -vvv
# at 328#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1exec_time=0 error_code=0use `mydb` /*!*/ ;SET TIMESTAMP = 1446712300 /*!*/ ;CREATE TABLE tb1 (id int , name char ( 30 ))/*!*/ ;事件发生的日期和时间: 151105 16 :31:40事件发生的服务器标识: server id 1事件的结束位置: end_log_pos 431事件的类型: Query事件发生时所在服务器执行此事件的线程的 ID : thread_id= 1语句的时间戳与将其写入二进制文件中的时间差: exec_time= 0错误代码: error_code= 0事件内容:GTID : Global Transaction ID , mysql5 .6 以 mariadb10 以上版本专属属性: GTID
# 从 10.0.0.8 远程主机实时同步从指定的二进制日志 binlog.000002 开始向后进行同步到当前目录[root@centos8 data] #mysqlbinlog -R --host=10.0.0.8 --user=test --password=123456--raw --stop-never binlog.000002
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
PURGE BINARY LOGS TO 'mariadb-bin.000003' ; # 删除 mariadb-bin.000003 之前的日志PURGE BINARY LOGS BEFORE '2017-01-23' ;PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30' ;
RESET MASTER [ TO #]; #删除所有二进制日志文件,并重新生成日志文件,文件名从 # 开始记数,默认从1开始,一般是 master 主机第一次启动时执行, MariaDB 10.1.6 开始支持 TO #
FLUSH LOGS ;
[root @centos8 ~] #mysqladmin flush-binary-log[root @centos8 ~] #mysqladmin flush-logs[root @centos8 ~] #mysqlMariaDB [hellodb]> flush logs ;
13. 总结二进制日志的不同格式的使用场景。
- 基于"语句"记录:statement,记录语句,默认模式( MariaDB 10.2.3 版本以下 ),日志量较少
- 基于"行"记录:row,记录数据,日志量较大,更加安全,建议使用的格式,MySQL8.0默认格式
- 混合模式:mixed, 让系统自行判定该基于哪种方式进行,默认模式( MariaDB 10.2.4及版本以上 )
14. 总结mysql备份类型,并基于mysqldump, xtrabackup完成数据库备份与恢复验证。
备份类型:
- 完全备份:整个数据集
- 部分备份:只备份数据子集,如部分库或表
增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较 快,还原复杂
增量和差异备份的基础和前提是完全备份
- 冷备:读、写操作均不可进行,数据库停止服务
- 温备:读操作可执行;但写操作不可执行
- 热备:读、写操作均可执行
- 物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
- 逻辑备份:从数据库中"导出"数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度
mysql> use hellodb;
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+[root@localhost ~]# mysqldump -uroot -A --single-transaction --source-data=2 --flush-privileges > /backup/all.sql
25 rows in set (0.00 sec)
mysql> update students set classid=6 where stuid>=20;mysql> select * from students;
.....省略
| 20 | Diao Chan | 19 | F | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 6 | NULL |
| 23 | Ma Chao | 23 | M | 6 | NULL |
| 24 | Xu Xian | 27 | M | 6 | NULL |
| 25 | Sun Dasheng | 100 | M | 6 | NULL |
+-------+---------------+-----+--------+---------+-----------+mysql> update teachers set age=18 where tid=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 18 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)mysql> source /backup/all.sql
mysql> select * from students;
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
例:xtrabackup数据备份和还原
1 安装xtrabackup包#先安装MySQL5.7和xtrabackup包[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm2 在原主机做完全备份到/backup[root@centos8 ~]#mkdir /backup[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base#目标主机无需创建/backup目录,直接复制目录本身[root@centos8 ~]#scp -r /backup/ 目标主机:/3 在目标主机上还原1)预准备:确保数据一致,提交完成的事务,回滚未完成的事务[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base2)复制到数据库目录注意:数据库目录必须为空,MySQL服务不能启动[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base3)还原属性[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql4)启动服务[root@centos8 ~]#service mysqld start
15. 编写crontab,每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。基于xtrabackup,每周1,周5进行完全备份,周2到周4进行增量备份
[root@rocky8 ~]# crontab -l
0 2 * * 1,5 backup_per_day.sql0 2 * * 2-4 backup_per_day_increment.sql
[root@rocky8 ~]# cat backup_per_day.sql
#! /usr/bin/bash
base_path=/mysql/db/
timestamp=`date +%Y%m%d`
mysql_output=${base_path}/test-table
database_name=test
for db in `mysql -uroot -e 'show databases' |grep -Ev '^(Datebase|information_schema|performance_schema|sys)$'`;doxtrabackup -uroot --backup --databases='$database_name.$db' --target-dir= $base_path/$timestamp/base/
done
[root@rocky8 ~]# cat backup_per_day_increment.sql
#! /usr/bin/bash
.source backup_per_day.sql
base_path=/mysql/db/
inc_timestamp=`date +%Y%m%d`
mysql_output=${base_path}/test-table
database_name=testfor db in `mysql -uroot -e 'show databases' |grep -Ev '^(Datebase|information_schema|performance_schema|sys)$'`;do
xtrabackup -uroot --backup --databases='$database_name.$db' --target-dir= $base_path/$inc_timestamp/$inc/ --incremental-basedir=$base_path/$timestamp/base/
done