Mysql数据库常用操作、企业级监控参数、AB复制、Atlas实现读写分离

叁歲伎倆 2022-01-20 21:27 213阅读 0赞

**零、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.查询吞吐量

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