mysql基础

1.mysql安装

5.6 安装步骤：

$> groupadd mysql
$> useradd -r -g mysql -s /bin/false mysql
$> cd /usr/local
$> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
$> ln -s full-path-to-mysql-VERSION-OS mysql
$> cd mysql
$> scripts/mysql_install_db --user=mysql
$> bin/mysqld_safe --user=mysql &

$> cp support-files/mysql.server /etc/init.d/mysql.

5.7 安装步骤：

$> groupadd mysql
$> useradd -r -g mysql -s /bin/false mysql
$> cd /usr/local
$> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
$> ln -s full-path-to-mysql-VERSION-OS mysql
$> cd mysql
$> mkdir mysql-files
$> chown mysql:mysql mysql-files
$> chmod 750 mysql-files
$> bin/mysqld --initialize --user=mysql
#客户端使用ssl方式连接mysql
$> bin/mysql_ssl_rsa_setup
$> bin/mysqld_safe --user=mysql &
$> cp support-files/mysql.server /etc/init.d/mysql.server

两者不同点：

1. 5.7默认安装完后，会有随机登录密码；5.6 默认安装登录是不需要密码的
2. mysql数据库初始化目录方式不同。

以下以 5.7版本 为例学习：

配置文件

文件名：my.cnf ( my.ini)

• 文本文件
• 可有多个配置文件
• 参数替换原则
  
  配置文件的优先级逐渐增高，后面的配置文件参数会替换前面的。
  一个最简单的配置文件：

#客户端的设置
#配置user,passwd后，客户端连接mysql时会自动读取这两个参数
[client]
user = root
password = 123456
[mysql]
#mysql连接成功后显示的提示符
prompt=(\\u@\\h) [\\d]>\\_ 
##prompt=(\\\u@\\\h) [\\\d]>\\\_
# 服务器端的参数
[mysqld]
port = 3306
user = mysql
datadir = /mdata/mysql_test_data
log_error = error.log
log_timestamps=SYSTEM

配置参数

• 从作用域上可分为global和session
• 从类型上又可分为可修改和只读参数
• 用户可在线修改非只读参数
• 只读参数只能通过配置文件修改并重启
• 所有参数的修改都不持久化
  
• 通过global修改参数不影响当前seesion参数! ! !

查看每一个会话级别的variables：
在 performance_schema数据库中有一个variables_by_thread表，里面对应每一个连接线程对应的variables值
例如：查看每一个会话的long_query_time;

select * from variables_by_thread where variable_name='long_query_time';

该数据库中还有一个threads表，存放每一个mysql连接线程对应的processlist_id;
通过利用上面的thead_id去查对应的processlist_id:
在通过show processlist; 去匹配连接的哪个会话；

processlist中的id就是 连接会话的id,也可以通过select connection_id() 查询。

用户权限

• 删除所有用户名为空的用户
• 不允许密码为空的用户存在
• 管理员用户可以有所有库权限
• 开发应用只需给相应库的权限
  

创建和删除用户

#创建test用户，密码是123456， % 表示所有ip都可访问。
create user 'test'@'%' identified by '123456';
#指定ip
create user 'test'@'192.168.1.%' identified by '123456';
drop user 'test'@'%';

权限管理

show grants; 查看当前用户的权限

show grants for ‘test’@’%’; 在root用户下查看test用户的权限。

USAGE：表示可以连接数据库

如果在test用户下查看root用户权限是不可以的，test用户没有对mysql库的读取权限

这时需要给test用户增加权限

# 赋予test用户对mysql库的增删改查权限
grant select,update,insert,delete on mysql.* to 'test'@'%';

注：不要用grant语句去创建用户，要先创建用户，在赋予权限。

如何删除权限：

#删除所有的权限
revoke all on mysql.* to 'test'@'%';
#删除指定的权限
revoke select,update,insert,delete on mysql.* to 'test'@'%';
# revoke不会删除用户，即使删除所有权限，用户还会有USAGE权限

修改用户密码

alter user 'test'@'%' identified by '123';

权限存在哪里

mysql数据库中有四张表，user，db, tables_priv, columns_priv;

• user表存放用户全局级别(所有数据库)的权限。
• db表存放用户数据库级别的权限。
• tables_priv表存放表级别的权限
• columns_priv表存放列级别的权限。
  例如：查看test用户的全局权限(这里的全局指的是 . )

select * from user where user='test'\G

user表中密码字段：5.7之前是password，5.7中是authentication_string.

为test用户增加一个 create 的全局权限

grant create on *.* to 'test'@'%';

这样test用户就能进行*.*级别的Create操作。

注意

不要通过修改这四张权限表去修改权限。还是要通过grant命令去操作权限。

用户资源管理

有下面四个选项，可以控制用户使用到的资源

• MAX_QUERIES_PER_HOUR count #每小时最大查询次数
• MAX_UPDATES_PER_HOUR count #每小时最大修改次数
• MAX_CONNECTIONS_PER_HOUR count #每小时最大连接数
• MAX_USER_CONNECTIONS count #同时连接的最大用户数
  例如：

#修改同时连接的最大用户数
alter user ‘test’@'%' with max_user_connections 10; 

MySQL连接

• 通过本地socket进行连接
  mysql -S /tmp/mysql.sock-u root -p
• 通过 TCP/IP 协议远程连接
  mysql -h 192.168.6.133 -P 3306 -u root -p
• 通过配置 my.cnf 免密码输入

[client]
user=root
password=123
#配置本地socket
socket=/tmp/mysql.sock

如果不知道是通过那种方式连接的，可以在登录mysql后，通过status或者\s 命令查看。

ssl连接

mysql 5.7 客户端默认使用ssl方式连接服务器。
运行 bin/mysql_ssl_rsa_setup后，在/mdata/mysql_test_data/数据目录下就会产生*.pem密钥文件。
连接mysql后，运行show variables like ‘%ssl%’;命令可知ssl 能够使用

然后用户通过TCP/IP方式连接，就可使用ssl方式连接

mysql -h 192.168.1.129 -u zhzs -p

通过status命令可看到

​