一、原理性概念
1、事物隔离级别
【MySQL】事务与隔离级别(99%的人存在误区)_哔哩哔哩_bilibili
(1)事物执行过程会遇到的几个问题
---脏读:A事物还未提交,B事物就读到了数据;
---不可重复读:A事物在本次事物中,对自己未操作过的数据,进行了多次读取,结果出现了不一致或记录不存在的情况。(破坏了一致性 update delete)
---幻读:A事物在本次事物中,对自己未操作过的数据,进行了多次读取,第一次读取时记录不存在;第二次读取时记录出现了(破坏了一致性 insert)
(2)定义标准--四种隔离级别
RU---Read uncommitted(未授权读取、读未提交)
RC---Read committed(授权读取、读提交)
RR---Repeatable read(可重复读取)
SE---Serializable(序列化)
(3)各厂商实现并不是完全按照这个标准来做,举例InnoDB的实现方案
RU:事物读取的时候,不加锁
RC:事物读取的时候加行级共享锁(读到才加锁),一旦读完、立即释放(并不是事物结束)
RR:事物读取的时候加行级共享锁,事物结束时才释放;
SE:事物读取的时候加表记共享锁,事物结束才释放;
二、mysql的日常使用
1、mysql备份还原
(1)备份导出数据
备份某一个库 mysqldump -u root -p --database testdb > /tmp/testdb.sql
备份所有库 mysqldump -u root -p --all-databases >/tmp/alldb.sql
备份某一张表 mysqldump -u root -p testdb table1 >/tmp/table1.sql
(2)还原导入数据
外部导入 mysql -u root -p </tmp/testdb.dump
进入数据库下导入 source /tmp/testdb.dump
mysql官方yum配置库下载 https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm 192
2、库表操作
(1)展示库、进入库,操作表;
show datebases; drop datebase t1; use t1; show tabels;
(2)对表的操作变更
alter table test add(sex varchar(2));
alter table test drop sex;
alter table test modify age int(3);
alter table test change age old int(3);
alter table test rename testtable;
(3)对表数据的操作变更
create datebase db1 default character set utf8;
create table test(id int(3),name varchar(20),age varchar(3));
insert into test values(1,'longyakun','22');
delete from test where id=1;
select * from test; update test set name='marry' where name='jack';
(4)删除表数据、删除表结构
truncate table testtable; 删除表数据 drop table testtable; 删除表结构
设置别名 select sal as 'gongzi',name as 'xingming' from qa where sal>'2500';
3、设置外键
create table userinfo( id int auto_increment primary key, name varchar(32) not null default '', depart_id int not null default 1, CONSTRAINT fk_user_dep FOREIGN KEY (depart_id) REFERENCES dep(id));
4、创建用户及权限设置
(1)创建用户
create user 'qfuser'@'localhost' identified by 'Qf/123com' ;
grant all on *.* to 'admin'@'localhost' identified by 'qF/12lcoQ';
重置用户密码
①set password for zk@'%'=password('zookeeper');
②update mysql.user set authentication_string=password('zabbix') where user='zabbixt' and host='%';
(2)给用户授权
grant select,update,insert on db.table to 'qfuser'@'localhost';
(3)回收用户权限
查看用户权限信息 show grants for 'qfuser'@'localhost';
回收存在的权限 revoke insert on db.table from 'qfuser'@'localhost';
5、密码策略
查看当前密码策略配置 show variables like 'validate%';
修改配置 set global validate_password_length=9;
刷新配置信息 flush privileges;
6、日志管理
(1)二进制日志
查看二进制日志配置 show variables like '%log_bin%';
查看二进制日志文件 show binary logs;
查看当前使用的二进制日志文件 show master status;
查看二进制日志事件 show binlog events in 'mysql-bin.000001';
①、开启二进制日志
选择合理空间足够的目录作为binlog存储位置,修改mysql配置( selinux是对deamon进程有操作权限的限制的,提前将selinux设置为disabled【vi /etc/selinux/config 修改SELINUX=disabled 】,重启机器生效)
#创建binlog存储目录
mkdir -p /data/3306/binlog
#将目录权限赋予给mysql用户
chown -R mysql:mysql /data/
#修改mysql配置 vi /etc/my.cnf
server_id=1
log-bin=/data/3306/binlog/mysql-bin
#重启mysqld服务
②、通过binlong恢复数据
#查看事件对应pos
show binlog events in 'mysql-bin.000001';
#CLI模式下截取日志
mysqlbinlog --start-position=pos1 --stop-position=pos2 mysql-bin.000001 > recover.sql
#进入mysql下
mysql>source recover.sql
(2)慢日志
a、开启慢日志
#查看慢日志是否开启
show variables like '%slow_query%';
#慢日志开启配置 vim /etc/my.cnf
slow_query_log=1 slow_query_log_file=/data/3306/slow.log long_query_time=1 log_queries_not_using_indexes=1
#重启mysql服务
systemctl restart mysqld
7、传统主从部署(Mysql复制最好确保master和slave服务器上的Mysql版本相同;如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本))
(1)主库配置
#主数据库创建远程登录slave用户,并授权
mysql>create user 'slave'@'192.168.%.%' identified by 'Qzmp@2020';
mysql>grant replication slave on *.* to 'slave'@'192.168.%.%';
#获取主库的日志信息,file表示当前日志文件名称,position表示当前日志的配置
mysql>show master status;
(2)同步主库数据到从库
#对主库上的所有表加锁,停止修改,即在从库复制的过程中主库不能执行
update\delete\insert语句;
mysql>flush tables with read lock;
#备份master库数据,同步给slave数据库
主库 mysqldump -uroot -p --all-databases > /tmp/alldb.sql
备份文件复制到从库
从库执行还原 mysql -uroot -p </tmp/alldb.sql
#主库解锁
mysql>unlock tables;
(3)从数据库配置
#添加配置信息 vi my.cnf
default-storage-engine=INNODB
symbolic-links=0
#注意server_id不同于主库
server_id=2
#开启二进制日志
log_bin=/database/mysql/binlog/mysql-bin
#指定relay-log日志目录(千万要指定从slave的relay log日志),新指定目录提前创建并授权给mysql用户;
relay_log=/database/mysql/relay-log/mysql-relay
①、从库建立复制关系,即从库指定主库的日志信息和连接信息
mysql > change MASTER TO MASTER_HOST='192.168.4.6',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='Q88hh#2020', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1353;
②、从库启动复制进程
mysql > START SLAVE;
#查看slave状态是否正常
mysql >show slave status\G
(4)注意几点:
1--master将操作语句记录到binlog日志中,然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能)。
2--slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和master数据保持一致了。
3--Mysql复制至少需要两个Mysql的服务,当然Mysql服务可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。
4--Mysql复制最好确保master和slave服务器上的Mysql版本相同(如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本)常见错误码-Error_code: 2003
5--master和slave两节点间时间需同步
Last_IO_Errno错误代码说明(此部分引用转载):
1005:创建表失败
1006:创建数据库失败
1007:数据库已存在,创建数据库失败
1008:数据库不存在,删除数据库失败
1009:不能删除数据库文件导致删除数据库失败
1010:不能删除数据目录导致删除数据库失败
1011:删除数据库文件失败
1012:不能读取系统表中的记录
1020:记录已被其他用户修改
1021:硬盘剩余空间不足,请加大硬盘可用空间
1022:关键字重复,更改记录失败
1023:关闭时发生错误
1024:读文件错误
1025:更改名字时发生错误
1026:写文件错误
1032:记录不存在
1036:数据表是只读的,不能对它进行修改
1037:系统内存不足,请重启数据库或重启服务器
1038:用于排序的内存不足,请增大排序缓冲区
1040:已到达数据库的最大连接数,请加大数据库可用连接数
1041:系统内存不足
1042:无效的主机名
1043:无效连接
1044:当前用户没有访问数据库的权限
1045:不能连接数据库,用户名或密码错误
1048:字段不能为空
1049:数据库不存在
1050:数据表已存在
1051:数据表不存在
1054:字段不存在
1065:无效的SQL语句,SQL语句为空
1081:不能建立Socket连接
1114:数据表已满,不能容纳任何记录
1116:打开的数据表太多
1129:数据库出现异常,请重启数据库
1130:连接数据库失败,没有连接数据库的权限
1133:数据库用户不存在
1141:当前用户无权访问数据库
1142:当前用户无权访问数据表
1143:当前用户无权访问数据表中的字段
1146:数据表不存在
1147:未定义用户对数据表的访问权限
1149:SQL语句语法错误
1158:网络错误,出现读错误,请检查网络连接状况
1159:网络错误,读超时,请检查网络连接状况
1160:网络错误,出现写错误,请检查网络连接状况
1161:网络错误,写超时,请检查网络连接状况
1062:字段值重复,入库失败
1169:字段值重复,更新记录失败
1177:打开数据表失败
1180:提交事务失败
1181:回滚事务失败
1203:当前用户和数据库建立的连接已到达数据库的最大连接数,请增大可用的数据库连接数或重启数据库
1205:加锁超时
1211:当前用户没有创建用户的权限
1216:外键约束检查失败,更新子表记录失败
1217:外键约束检查失败,删除或修改主表记录失败
1226:当前用户使用的资源已超过所允许的资源,请重启数据库或重启服务器
1227:权限不足,您无权进行此操作
1235:MySQL版本过低,不具有本功能