Bob's Blog

从前一天开始的数据：

SELECT * FROM `visitor` WHERE record_date > date_sub(curdate(), interval 1 day) order by id desc;

今天内从zhihu来的访问数据

SELECT * FROM `visitor` WHERE record_date > curdate() and (agent like '%zhihu%' or referer like '%zhihu%') order by id desc;

新建数据库和用户并指定只访问该数据库

create database my_site;

create user 'sitemanager'@'%' identified by 'MyPermission';

alter user 'sitemanager'@'%' identified WITH mysql_native_password by 'MyPermission';

GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `my_site`.* TO 'sitemanager'@'%';

如果提示密码过于简单(Your password does not satisfy the current policy requirements)，可以更改password policy后再次添加用户：

set global validate_password.policy=0;

修改用户密码

use mysql;
alter user 'sitemanager'@'%' IDENTIFIED BY 'NewPermission';

改mysql默认密码

先查看默认的临时密码：cat /var/log/mysqld.log | grep 'password'

修改密码：

mysql -uroot -p
mysql> ALTER USER USER() IDENTIFIED BY "Killer'Hunter@2020";