学习预览:
(一)mysql 运维基础篇(Linux云计算从入门到精通)
(三)mysql 触发器、存储过程和函数(数据库运维基础补充)
(五)mysql数据备份—物理备份(完备+lvm快照+xtrabackup)+逻辑备份(mysqldump+导入导出)
(六)mysql复制技术—M-S主从配置(传统+GTID)+M-M-S-S主从配置(GTID)
(七)mysql中间件mycat配置和部署(基于M-M-S-S)
(八) 数据库集群技术—Galera Cluster安装与配置
(九)数据库集群技术Galera+mycat(数据库运维学习终章)
CONTEND
一、mysql安全机制
1.1 MySQL权限表
我们主要负责以下四种权限表的设置:
mysql.user Global level
用户字段
权限字段
安全字段
资源控制字段
mysql.db Database level
用户字段
权限字段
mysql.tables_priv Table level
mysql.columns_priv Column level
1.2 MySQL用户管理
(1)1.登录和退出MySQL
示例:
mysql -h192.168.5.240 -P 3306 -u root -p123 mysql -e 'select user,host from user'
-h 指定主机名 [默认为localhost]
-P MySQL服务器端口 [默认3306]
-u指定用户名 [默认root]
-p指定登录密码 [默认为空密码]
此处mysql为指定登录的数据库
-e接SQL语句
(2)创建用户
以前有的版本好像可以直接用grant同时创建用户并授权的,我这个版本并不支持,不清楚的话用help看看语法。
CREATE USER语句创建,接着授权,后面在介绍授权的细节。对了,由于MySQL8.0之后的加密规则为caching_sha2_password.而在此之前的加密规则为mysql_native_password。要想其他客户端连接的话,需要加上mysql_native_password哦,如下图:
create user 'user1'@'localhost' identified with mysql_native_password by 'Root@123';--创建用户
grant all on *.* to 'user1'@'localhost' ; --授权然后自己登陆试试
select * from user\G --可以在user表中查看自己创建的用户信息
(3)删除用户
drop user 'user1'@'localhost';
(4)修改用户密码(我发现不同版本的mysql的设置语法不太一样,不清楚用help)
#root修改自己的密码,直接在终端修改
[root@Centos7-host1 ~]# mysqladmin -uroot -p'Root@123' password 'Root@321'
#或者在mysql里修改,以下两种都可以
mysql> alter user root@localhost identified by 'Root@123';
mysql> set password for root@localhost='Root@123';
#root修改其他用户密码
mysql> create user 'root1'@'localhost' identified with mysql_native_password by 'Root@123';--创建用户
mysql> grant all on *.* to 'root1'@'localhost' ; --授权然后自己登陆试试
mysql> set password for root1@192.168.119.156='Root@321' --修改密码再重新登录
#用户修改自己的密码
mysql> set password='Root@123';
1.3 MySQL权限管理
权限应用的顺序:user ==> db ==> table_priv ==> columns_priv
示例:
create user 'admin1'@'localhost' identified with mysql_native_password by 'Root@123';
create user 'admin2'@'192.168.119.%' identified with mysql_native_password by 'Root@123';
create user 'admin3'@'192.168.119.156' identified with mysql_native_password by 'Root@123';
create user 'admin4'@'192.168.119.158' identified with mysql_native_password by 'Root@123';
grant all on *.* to admin1@localhost with grant option; --赋予admin所有权限,这个一般不建议
grant all on bbs.* to admin2@'192.168.119.%' ; --赋予admin2对bbs数据库的管理权限
grant all on bbs.user to admin3@'192.168.119.156' ;--只赋予admin3对bbs数据库中user表的管理权限
grant select(id),insert(name,age) on bbs.user to admin4@'192.168.119.158';--针对字段了,不过这个太变态了,很少用
删除用户很简单: drop user admin1@'localhost';
二、MySQL日志管理
2.1 错误日志
配置方式:在配置文件中(my.cnf)中添加log_error=path 即可。错误日志默认就是开启的,默认存放在/var/log/mysqld.log下面,具体位置可通过查看/etc/my.conf文件得知。在运行mysql时候出现问题可通过查看该日志助于排错。
log-error=/var/log/mysqld.log 后面试错误日志的存储路径#
2.2 二进制日志(binlog)
二进制日志作用是增量备份,数据恢复。他记录了数据库所有变化的操作,如DDL、DCL、DML,其实就是SQL语句
(1)配置介绍
server-id=6 #主从复制需要用到
log_bin=/var/log/binlog/mysql-bin #指定存放位置mysql-bin名称前缀
binlog_format=row #日志格式
#这个文件夹需要自己创的,并且授权给mysql用户,重启生效
[root@Centos7-host1 ~]# mkdir /var/log/binlog/
[root@Centos7-host1 ~]# chown mysql.mysql /var/log/binlog/
[root@Centos7-host1 ~]# systemctl restart mysqld
(2) 二进制日志格式
- DDL和DCL,以statement(语句)方式直接记录SQL
- DML(insert、update、delete),记录的是已经提交的事物
- SBR :statement,记录具体语句(5.6以下版本常用)
- RBR :Row ,记录数据行的变化(常用)
- MBR :mixed,混合模式(一般不用)
(3) 日志内容
以事件(event) 作为记录的最小单元
以下(截取)为一个事件,以一个at开始,到下一个at结束
[root@Centos7-host1 ~]# mysqlbinlog /var/log/binlog/mysql-bin.000001
# at 4
#200410 18:05:57 server id 6 end_log_pos 124 CRC32 0x7eccf0b6 Start: binlog v 4, server v 8.0.19 created 200410 18:05:57 at startup
# at 124
(4)我们可以在mysql客户端查看二进制的基本情况(ctrl+z==>fg在shell和mysql客户端之间切换)
#查看有哪些二进制日志
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 178 | No |
| mysql-bin.000002 | 155 | No |
+------------------+-----------+-----------+
#刷新
mysql> flush logs;
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 178 | No |
| mysql-bin.000002 | 202 | No |
| mysql-bin.000003 | 155 | No |
+------------------+-----------+-----------+
#查看正在使用的二进制日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 155 | | | |
+------------------+----------+--------------+------------------+-------------------+
#查看某二进制日志的事件信息
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 6 | 124 | Server ver: 8.0.19, Binlog ver: 4 |
| mysql-bin.000001 | 124 | Previous_gtids | 6 | 155 | |
| mysql-bin.000001 | 155 | Stop | 6 | 178 | |
+------------------+-----+----------------+-----------+-------------+------------------
(5)二进制日志的截取
注意:重启mysqld和flush logs会截断,reset master会删除所有的binlog,这个要谨慎,否则以后有问题就没法恢复了。
关于截取binlog有以下几种方法,一般选择position截取,然后进行恢复,关于数据恢复我会在在后面备份中学习总结下哦。
截取全部
# mysqlbinlog mysql-bin.000001
按datetime截取
# mysqlbinlog mysql-bin.000003 --start-datetime="2004-10-01 18:29:59"
# mysqlbinlog mysql-bin.000003 --stop-datetime="2004-10-02 18:29:59"
# mysqlbinlog mysql-bin.000003 --start-datetime="2004-10-01 18:29:59" --stop-datetime="2004-10-02 18:29:59"
按position截取
# mysqlbinlog mysql-bin.000003 --start-position=260
# mysqlbinlog mysql-bin.000003 --stop-position=260
# mysqlbinlog mysql-bin.000003 --start-position=260 --stop-position=360
2.3 slow log(慢日志)
作用:记录MYSQL运行期间执行较慢的语句,这样可以告诉开发者哪里有问题。
(1)配置情况
slow_query_log=1 #打开慢日志
slow_query_log_file=/var/log/mysql_slow/slow.log #默认路径可手动指定
long_query_time=5 #超过多长时间被定义为慢语句
mkdir /var/log/mysql_slow
chown mysql.mysql /var/log/mysql_slow/
systemctl restart mysqld
(2)查看慢日志是否打开,slow_query_log 状态为ON 表示打开。
mysql> show variables like '%slow%';
+---------------------------+------------------------------+
| Variable_name | Value |
+---------------------------+------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql_slow/slow.log |
+---------------------------+------------------------------+
(3) 查看慢日志记录时间
当查询语句大于等于long_query_time这个值得时间(单位S)就会被定义为查询慢的语句,就会被慢日志记录起来
mysql> show variables like '%long%';
+----------------------------------------------------------+----------+
| Variable_name | Value |
+----------------------------------------------------------+----------+
| long_query_time | 5.000000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+----------------------------------------------------------+----------+
(4)慢日志测试
mysql> select benchmark(500000000,2*3); #就是一个函数,让2*3计算这么多次
+--------------------------+
| benchmark(500000000,2*3) |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (7.88 sec)
#查看慢日志是否有记录
tail /var/log/mysql_slow/slow.log
#或者用这个查询
mysqldumpslow -s c -t 3 slow.log