零、mysql5.7.17 二进制安装单实例(centos 7 )
bash <(curl -s -S -L https://raw.githubusercontent.com/zhutongcloud/Linux/master/Mysql/mysql5.7.17_install.sh)
一、权限管理
1.创建库test
create database test;
2.创建用户user01对库test有所有权限
grant all on test.* to 'user01'@'%' identified by 'password';
3.慎用:创建用户并授与root相同授权权限。[grant 权限 on 数据库对象 to 用户]
grant all on *.* to 'user01'@'%' identified by '123456' with grant option;
flush privileges;
4.只创建一个用户:
CREATE USER 'test'@'%' IDENTIFIED BY '1234';
5.添加用户并授权:
grant all privileges on *.* to 'root'@'%' identified by 'password' ;
6.叠加权限,给user01用户所有库所有表增删改查的权限,只是select就是只有查询权限
grant select,insert,update,delete on *.* to 'user01'@'%' identified by 'password';
7.查看user01的权限
show grants for user01@%;
8.修改数据库密码:
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
update mysql.user set authentication_string=password(“123”) where User=”test” and Host=”localhost”;
9.撤销权限
首先查看该用户的权限,然后复制想要撤销的权限,将grant 改成revoke,将to 改为from 。
show grants for user01@%;
二、关于备份
如果mysqldump 和 MySQL版本不一致,则在备份时会报错。可以在其他装同一版本的MySQL服务器上拷贝mysqldump文件即可(亲测)
备份某个库的多个表
mysqldump -uroot -p 库名 table1 table2 table3 > /tmp/backup.sql
备份全库
mysqldump -uroot -p --all-databases > BackupName.sql
备份多个库
mysqldump -uroot -p 库名1 库名2 > BackupName.sql
三、创库创表 插入数据
create database 库名
show databases;
create table 表名(字段 字段类型)
show tables;
复制表:
create table table2 select * from table1;
查看数据:select * from
查看当前用户:select user();
查看授权用户:select user,host from mysql.user;
查看所有用户授权详情:select * from mysql.user;
查看密码:select user,password from mysql.user;
查看某一个用户的授权信息:show grants for 'user'@'localhost'\G
查看当前数据版本:select version();
查看当前使用的数据库: select database();
更新表:update [库名].[表名] set 字段1=字段值1,字段2=字段值2.... where 条件;
删除数据库:drop database [库名];
删除数据表:drop table [表名];
删除用户:drop user 用户名@客户端;
删除表记录:delete from [表名] where [条件];
删除数据:delete from [表名];
添加主键:alter table 表名 and primary key(字段名);
添加普通索引:alter table 表名 and index 索引名(字段名);
添加普通索引:alter table 表名 and index 索引名(字段名(前几个字符写数字));
添加联合索引如:alter table 表名 and index 索引名(id(7),name(8));
删除索引:alter table 表名 drop index 索引名;
添加字段(列):alter table 表名 and 字段名 字段类型;默认在最后面插入。
添加字段(列)如:alter table test add age int(4) after id; 在id后插入
删除字段(列):alter table 表名 drop 字段名;
修改字段名:alter table 表名 change 字段名1 字段名2 数据类型;
四、关于优化
1.关于数据库缓存:
show variables like "%cache%";查看:
query_cache_size 0
query_cache_type OFF
设置开启这两个值:set global query_cache_size =1024000000;set session query_cache_type=on;
2.关于慢查询
(1)漫查询日志是否开启
show variables like '%slow%';查看Log_slow_query是否开启,slow慢query查询.
临时生效:set global log_slow_queries=on;
永久生效:log-slow-queries=/var/lib/mysql/slowquery.log
log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
(2)设置慢查询时间
show variables like '%long%';查看慢查询日志时间;默认为10s,应该改成0.5-1秒;set long_query_time=0.5;
永久生效:long_query_time=2 (记录超过的时间,默认为10s)
(3)show full processlist 发现慢语句,利用desc或explain加语句查看是否应用了索引。
ows行-扫描的行数。type类型,key(索引)排查;
3. 关于索引
普通索引:alter table test add index(t_name);
主键索引:创建表的时候就已经创建。主键索引唯一且不能为空ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
添加唯一索引:唯一索引,不允许有重复的。ALTER TABLE `table_name` ADD UNIQUE ( `column` )
添加多列索引:ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
五、企业级mysql监控参数
1.查询吞吐量
Com_select:select查询语句个数/每秒
Com_insert:insert操作语句个数/每秒
Com-update:update操作语句个数/每秒
Com-delete:delete操作语句个数/每秒
Questions:查询语句总量
show global status like 'Com_select';
| Com_select | 18484 |
show global status like 'Com_update';
| Com_update | 3000 |
show global status like 'Com_insert';
| Com_insert | 1054 |
show global status like 'Com_delete';
| Com_delete | 198 |
show global status like 'Questions';
| Questions | 723868 |
MySQL [(none)]> show global status like 'uptime';
| Uptime | 12441 |
注:
1. Writes = Com_insert + Com_update + Com_delete,如果在监控mysql吞吐量写操作的时候可以将insert
update、delete数值进行合并。
2. Questions是反映当前查询数量,设置告警非常必要,查询量的骤降,可能就预示着某个严重的问题。
qps = Questions/uptime
2.查询执行性能
Slow_queries:慢查询数量
show global status like 'Slow_queries';
| Slow_queries | 0 |
3.连接情况
Threads_connected:当前打开的数据库连接数
max_connections:数据库当前的最大连接数
show global status like '%connect%';
| Variable_name | Value |
+-----------------------------------------------+---------------------+
| Aborted_connects | 1 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 253 |
| Locked_connects | 0 |
| Max_used_connections | 71 |
| Max_used_connections_time | 2019-07-31 14:57:59 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 41
检查并设置连接限制
监控客户端连接情况相当重要,因为一旦可用连接耗尽,新的客户端连接就会遭到拒绝。MySQL 默认的连接数限制为 151。
查询最大连接数
方法一:
MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
方法二:
mysql -uread -p123456 -h47.98.97.124 -e "show variables like 'max_connections'"|grep max_connections
max_connections 151
修改连接数
MariaDB [(none)]> set global max_connections = 200;
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 200 |
+-----------------+-------+
1 row in set (0.00 sec)
4.缓冲池使用情况
缓存命中率
缓冲池是一块内存区域,在对数据库进行读取操作时,首先将数据从磁盘中读取到缓冲池中,那缓冲池命中率则是客户端请求的数据在直接在缓冲池获取的比例。想象下如果缓冲池命中率很低,大部分数据都要从磁盘读取,可想而知一个数据库的性能。作为一个mysql OLTP系统,缓冲命中率最好在百分99以上。
mysql> show engine innodb status\G;
QPS(增删改查);MySQL请求流量带宽;响应流量带宽,主从状态,TPS(每秒事务的处理能力),监控缓冲池使用情况,缓存碎片,慢查询数量,当前连接数,吞吐量,端口服务状态,mysql正常运行的时间
吞吐量: questios/uptime 查询总量/服务运行时间
max_connections 默认为151 监控可用连接的数量 设置报警 在my.conf修改适当的连接数 在连接数接近151报警,调整最大连接数,提高数据库性能
六、Mysql主从复制
文章链接:
https://www.jianshu.com/p/4eb2982daa94
七、Atlas实现MySQL读写分离
文章链接:
https://www.jianshu.com/p/44854ba04a6e