MySQL 常用用户权限管理命令

一、用户

1.1 创建用户

-- 创建用户,并允许其在任何IP登陆
create user 'username'@'%' identified by 'password';

-- 创建用户,并允许其在任何主机登陆,不设置密码
create user 'username'@'%';

-- 创建用户,并仅允许使用jitwxs.cn域名的主机登陆
create user 'username'@'jitwxs.cn' identified by 'password';

-- 创建用户,并仅允许使用192.168.1.1的主机登陆
create user 'username'@'192.168.1.1' identified by 'password';

-- 创建用户,并仅允许使用192.168.1开头的主机登陆
create user 'username'@'192.168.1.%' identified by 'password';

详解 MySQL下的行表锁

阅读本文前,请务必查看先导文章:数据库基础理论

阅读本文后,你可以查看延申文章:[MySQL] 行级锁SELECT … LOCK IN SHARE MODE 和 SELECT … FOR UPDATE

一、MyISAM 引擎

MyISAM 是 MySQL 5.1 之前的默认搜索引擎,我们都知道,MyISAM 采用表锁,即操作一条记录也会锁住整张表。适合做主要查询、非事务的表的引擎。下面演示下 MyISAM 引擎下的读锁与写锁。

解决 MySQL 报错 Expression #1 of SELECT list is not in GROUP BY clause and contains nonag...

一、问题描述

运行 sql 后报错:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated 
column ‘day_offset’ which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by...

MySQL 5.7.5 及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL 模式(默认情况下),MySQL将拒绝选择列表,HAVING 条件或 ORDER BY 列表的查询引用在 GROUP BY 子句中既未命名的非集合列,也不在功能上依赖于它们。(5.7.5之前,MySQL没有检测到功能依赖关系,默认情况下不启用ONLY_FULL_GROUP_BY。)

解决 MySQL 报错The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents ....

问题描述

今天在使用SpringBoot 2.1 + MyBatis时,报了一个很奇怪的错误,如下所示:

2018-11-15 15:22:42.424 ERROR 14132 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Exception during pool initialization.

java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.13.jar:8.0.13]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.13.jar:8.0.13]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.13.jar:8.0.13]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.13.jar:8.0.13]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[mysql-connector-java-8.0.13.jar:8.0.13]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76) ~[mysql-connector-java-8.0.13.jar:8.0.13]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835) ~[mysql-connector-java-8.0.13.jar:8.0.13]
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455) ~[mysql-connector-java-8.0.13.jar:8.0.13]
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240) ~[mysql-connector-java-8.0.13.jar:8.0.13]
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207) ~[mysql-connector-java-8.0.13.jar:8.0.13]
	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136) ~[HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369) ~[HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198) ~[HikariCP-3.2.0.jar:na]
...

Linux MySQL 安装教程

一、Ubuntu 16.04

sudo apt-get install mysql-server

一旦安装完成,MySQL 服务器应该自动启动。您可以在终端提示符后运行以下命令来检查 MySQL 服务器是否正在运行:

sudo netstat -tap | grep mysql

如果要卸载:

sudo apt-get autoremove mysql-server

MySQL 5.7 绿色版安装教程

一、下载安装包

首先前往官网下载 MySQL,也可以直接下载 mysql-5.7.20-winx64.zip

二、配置 MySQL

(1)下载完毕后(我此时版本为5.7.20),将压缩包解压,将解压后的整个文件夹放在你要放的位置,这里我放在 D 盘根目录下,即路径为: