用户管理
权限表
MySql通过权限表来控制用户对数据库的访问
- user表:记录允许连接到服务器的用户信息,里边的权限是全局级的;有42个字段,可分4类:用户列、权限列、安全列、资源控制列;
- 用户列:主要包括
Host、User
, user和host为user表的联合主键;5.7.20
已没有password
字段,采用authentication_string
存储密码 - 权限列:描述了全局范围内允许对数据和数据库进行的操作;普通权限用于操作数据库,高级权限用于数据库管理;
- 安全列:ssl、x509以及授权插件相关;
- 资源控制列:用来限制用户使用的资源,包括:
- max_questions:用户每小时允许执行的查询操作次数
- max_updates:用户允许每小时执行的更新操作的次数;
- max_connections:用户每小时允许执行的连接操作次数
- max_user_connections:用户允许同时建立连接的次数;
- 用户列:主要包括
- db表:存储了用户对某个数据库的操作权限
- host表:存储了某个主机对数据库的操作权限,不常用;【我本地mysql5.7.20中并没有host表】
- table_priv表:用来对表设置操作权限
- columns_priv:用来对表的某一列设置权限
- procs_priv表:用于对存储过程和存储函数设置操作权限;
账户管理
- 登录和退出mysql服务器:
mysql [-hpuPe]
,-h
主机名,-u
用户名,-P
端口号,可在命令最后指定数据库名
,-e sql
执行sql语句并退出; create
新建普通用户:create user username[@host] [identified by [password] 'password' | identified with auth_plugin [as 'auth_string']]
- 如果只指定用户名部分,则对所有主机开放;如果省略
identified by
部分,则不用密码即可登录;如果使用密文设置密码则可使用identified by password '密文'
- create创建的用户没有任何权限
- 如果只指定用户名部分,则对所有主机开放;如果省略
grant
也可新建用户:grant privileges on db.table to user@host [identified by 'password'][, user@host [identified by 'password']] [with grant option]
drop user <username>
:删除普通用户- root用户修改自己的密码
mysqladmin -u uname -h localhost -p password 'newpwd'
- 修改mysql中的user表
set password=password('newpassword')
root用户修改普通用户的密码
- set password for ‘user@host’ =password(‘newpwd’)
- update mysql.user表
grant usage on *.* to 'user@host' identified by 'password
root密码丢失解决办法
- 使用
--skip-grant-tables
选项启动mysql服务,mysql-safe --skip-grant-tables user=mysql
,/etc/init.d/mysql start-mysqld --skip-grant-tables
- 使用root用户登录,修改密码
- 加载权限表,
flush privileges
- 使用
权限管理
- 授权:
grant priv_type [(column)] on [table | function | procedure] db_name.tab_name to user [identified by 'password'] [with grant option]
- 全局层级:
grant all on *.*
- 数据库层级:
grant all on db_name.*
- 表层级:
grant all on db_name.tab_name
- 列层级以及子程序层级
- 全局层级:
- 收回权限:
revoke all privileges, grant option from 'user@host'
- 查看权限:
show grants for 'user'
- 访问控制
- 连接核实阶段:通过提供的信息与user表进行匹配,决定是否接收连接
- 请求核实阶段:执行操作时 自顶向下 层级检查用户的权限;
数据备份与恢复
数据备份
数据库在备份之前执行
flush tables with read lock
确保数据已刷至磁盘,在备份的时候不会有新数据写入
- 备份指定数据库:
mysqldump -u root -p <db_name> > /path/db_name.sql
- 备份多个数据库:
mysqldump -u root -p --databases <db_name>[,db_name1, db_name2] > /path/filename.sql
- 常用可选项:
--add-drop-tables --add-drop-database
:在每个create
语句前增加drop
;--add-locking
使用lock tables
与unlock tables
语句引用每个表转储,重载转储文件时使插入更快;--comments=[0|1]
如果为0,禁止输出转储文件中的其他信息,如程序版本服务器版本等--compact
精简输出,禁用注释,并启用--skip-add-drop-tables --no-set-names --skip-disabled-keys --skip-add-locking
--compatible=name
产生与其他数据库系统或者其他版本兼容的输出,值ansi MySQL323 MySQL40 oracle mssql db2 maxdb no_key_options no_table_options
--complete-insert
或-c
包括列名的完整insert语句--delete
或-D
导入文本文件前清空表--default-character-set=name
设置字符集,默认utf8--extended-insert
或者-e
多行insert语法,使得转储与重载更快,文件更小--lock-tables
或者-l
开始转储前锁定所有表,适用MyISAM引擎--single-transaction
同--lock-tables
,适用于事务表(InnoDB引擎)--lock-all-tables
或-x
对所有数据库中的所有表加锁,会自动关闭--single-transaction
和--lock-tables
-opt
速记选项,默认开启,包含:--add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables-quick --set-charset
--no-create-db
或-n
禁止输出create database
语句--no-create-info
或-t
只导出数据,不添加create table
语句--no-data
或-d
不写表的任何信息,只转储表结构--port=port
或-P port
指定服务端端口--silent
静默模式,有错误时才提示;--flush-logs
或者-F
开始转储前刷新服务器日志文件,需要reload
权限;
数据恢复
mysql -u root -p <db_name> < /path/db_name.sql
source /path/db_name.sql
数据库迁移
- 同版本迁移:MyISAM表 可直接复制数据文件,InnoDB需使用
mysqldump
导出导入 - 不同版本迁移:字符集设置、兼容性设置
- 不同数据库库迁移:将mysql数据转储为其他类型的输出(不完全一致,需要修改部分语句)
表的导出
具有
file
权限
select columnlist from table where condition into outfile 'filename' [options]
导出文件,options
选项:
fields terminated by 'val'
设置字段之前的分割符,默认为\t
,单个或多个字符fields enclosed by 'val'
设置字段的包围字符,只能是单个字符fields escaped by 'val'
设置转义字符,默认为\
lines starting by 'val'
设置每行数据开头的字符,可为单个或多个字符,默认不设置lines terminated by 'val'
设置每行结束的字符,默认是\n
mysqldump -T /path/dir -u root -p db_name
会在/path/dir
下边生成两个文件,一个为sql包含建表,一个为txt文本包含数据;mysql -u root -p [--vertical] --execute="select * from table;" <db_name> > /path/db_name.txt
导出数据到文本mysql -u root -p --html --execute="select * from table;" <db_name> > /path/db_name.html
导出数据到htmlmysql -u root -p --xml --execute="select * from table;" <db_name> > /path/db_name.xml
导出数据到xml
表的导入
load data infile 'filename.txt' into table <tbl_name> [options] [ignore numberlines]
导入,options
同导出,ignore为忽略行数mysqlimport -u root -p <db_name> filename.txt [options]
导入
日志
- mysql中的日志主要分为4类:
- 错误日志:启停服务、运行之间出现的问题
- 查询日志:记录建立客户端连接和执行的语句
- 二进制日志:记录所有更改数据的语句,可拥有数据复制
- 慢日志:记录所有执行时间超过
long_query_time
所有的查询或不使用索引的查询
- 刷新日志:
flush logs
或mysqladmin flush-logs
或mysqladmin refresh
将刷新日志
二进制日志
主要记录MySQL的数据库变化,包含了所有更新了数据或者已潜在更新了数据的语句,通过二进制日志可实现数据增量备份;
- 启用二进制日志
[mysqld]
server-id=1
log-bin [=/path/[filename]] # 日志存放的位置,可以指定目录或者文件名
expire_logs_days=10 # 日志过期清除的时间
max_binlog_size=100M #单个文件大小的限制,不能超过1G或者小于4096B
查看二进制日志
show binary logs
查看二进制日志文件个数和文件名mysqlbinlog /usr/local/var/mysql/alleyzdeMacBook-Pro-bin.000001
查看二进制文件内容
删除二进制日志
reset master
所有二进制日志将被删除,mysql将重新创建二进制日志purge master logs TO 'filename'
删除文件名编号比指定文件名编号小的所有日志文件purge master logs before 'date'
删除指定日期之前的所有日志文件
使用二进制日志恢复数据库:
mysqlbinlog [option] filename | mysql -uuser -ppasswd
,options
常用参数:--start-date --stop-date
指定恢复数据库的起始时间点和结束时间点--start-position --stop-position
指定恢复数据的开始位置和结束位置
暂停二进制日志功能:
set sql_bin_log=[0|1]
0
时则暂停记录;
错误日志
- 配置错误日志
[mysqld]
log-error=[/path/[filename]]
查看错误日志位置
show variables like 'log_error'
删除错误日志:
flush logs
或者mysqladmin -u root -p flush-logs
注意直接删除后不会重新生成日志
通用查询日志
- 启用
[mysqld]
log[=/path/[filename]]
- 删除
mysqladmin flush-logs
慢查询日志
慢查询日志是记录查询时长超过指定时间的日志,记录执行时间较长的SQL语句
- 启动和设置慢查询日志
[mysqld]
# 这个方式我在mysql 5.7.20测试会报错
# log-slow-queries[=path/[filename]]
slow-query-log
slow_query_log_file=/varl/lib/mysql/slow
long_query_time=10 # 时间值 单位为秒,默认10秒
- 慢查询日志分析工具:mysqldump slow ; mysql sla; mysql log filter;
性能优化
- 查询数据库性能常用命令以及参数:
show status like 'val'
,val的常见参数:
Connections
连接mysql的次数Uptime
mysql服务上线时间Slow_queries
慢查询次数Com_select
查询操作的次数Com_insert
插入操作的次数Com_update
更新操作的次数Com_delete
删除操作的次数
优化查询
- 分析查询语句 :
explan | desc [extended] select * from tab
- 使用索引查询:
- 使用like查询时
%
不能位于第一个位置(btree) - 使用多列索引的查询语句,查询条件中使用了第一个字段时才会生效(最左前缀)
- 使用
OR
时,需要前后两个列都有索引 - 使用连接查询代替子查询(子查询会产生临时表,而join查询不会)
- 使用like查询时
数据库结构优化
- 将字段很多的表分解成多个表
- 增加中间表,减少联合查询
- 增加冗余字段
- 优化插入记录的速度
- [MyISAM]插入大量数据到非空表时,可先禁用索引
alter table tb_name disabled keys
,插入完成后启用索引alter table tb_name enable keys
- [MyISAM]使用批量插入,使用一条insert插入多值记录;
- 使用
load data infile
导入比insert
快 - 禁用唯一性检查
set unique_checks=0
- [InnoDB]禁用外键检查
set foreign_key_checks=0
- [InnoDB] 禁止自动提交
set autocommit=0
- [MyISAM]插入大量数据到非空表时,可先禁用索引
- 分析表
analyze local table tb_name
- 检查表
check table tb_name [option]
,options只对MyISAM
引擎的表有效
quick
不扫描行不检查错误的连接fast
只检查没有被正确关闭的表changed
只检查上次检查后被更改的表和没有被正确关闭的表medium
扫描行以验证被删除的链接是有效的extended
对每行的所有关键字j进行一个全面的关键字查找,确保表示100%一致
- 优化表
optimize local table tb_name
,可以消除删除和更新造成的文件碎片;
优化mysql服务器
优化硬件
- 较大内存
- 高速磁盘系统
- 合理分布I/O
- 多处理器
优化查询参数
key_buffer_size
索引缓冲区大小,索引缓冲区所有线程共享,取决于内存大小table_cahce
同时打开表的个数,值越大打开表的个数越多,但过多会影响性能query_cache_size query_cache_type
查询缓冲区大小; 只适合修改操作少且经常执行相同查询操作的情况
- 当
query_cache_type=0
时所有查询不使用缓冲区 - 当
query_cache_type=1
时所有查询默认使用缓存区,可使用select sql_no_cahce * from tb
禁止使用 - 当
query_cache_type=2
时除非sql指定select sql_cache from tb
才会使用缓冲区
- 当
sort_buffer_size
排序缓冲区大小,值越大排序速度越快,默认2Mread_buffer_size
每个线程连续扫描时为扫描的每个表分配的缓冲区大小,连续读取会用到read_rnd_buffer_size
表示为每个线程保留出的缓冲区大小,按特定顺序取出来会用到innodb_buffer_pool_size
表示innodb表和索引的最大缓存,值越大查询操作越快,但过大会影响系统性能max_connections
数据库最大连接数innodb_flush_log_at_trx_commit
何时将缓冲区的数据写入文件,0
每隔一秒,1
每次提交事务时2
提交事务时写入文件,每隔一秒写入磁盘; 默认为1back_log
对到来的tcp连接侦听的队列大小interactive_timeout
关闭连接前等待操作的秒数thrad_cache_size
可复用的线程数量wait_timeout
,关闭一个连接时等待的秒数
参考资料《MySQL5.7从入门到精通》