MySQL学习笔记（Day003：升级/参数/连接/权限）
@(MySQL学习)
[TOC]
一. 数据库升级
###1. 环境说明：
一般说来，MySQL数据库的二进制数据文件，也就是my.cnf中的配置项datadir所在的位置，和我们MySQL应用程序安装的位置，是分开的，仅仅通过配置项告诉MySQL，数据库的数据存在datadir这个目录下。当程序和数据分离以后，方便我们对数据库应用程序做版本的升级或者回退。
###2. 环境举例：
MySQL安装目录：
- MySQL 5.6.27: /usr/local/mysql-5.6.27-linux-glibc2.5-x86_64
- MySQL 5.7.9 : /usr/local/mysql-5.7.9-linux-glibc2.5-x86_64
datadir目录：
- /data/mysq_data/
初始环境：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16shell> ll | grep mysql
lrwxrwxrwx 1 root root 34 Nov 16 13:40 mysql -> mysql-5.6.27-linux-glibc2.5-x86_64
drwxr-xr-x 13 root mysql 4096 Nov 16 13:37 mysql-5.6.27-linux-glibc2.5-x86_64
drwxr-xr-x 9 7161 wheel 4096 Oct 12 00:29 mysql-5.7.9-linux-glibc2.5-x86_64
shell> ll /data/mysql_data/
total 13540
-rw-rw---- 1 mysql mysql 65468 Nov 16 13:50 bin.000001
-rw-rw---- 1 mysql mysql 1176237 Nov 16 13:50 bin.000002
-rw-rw---- 1 mysql mysql 26 Nov 16 13:50 bin.index
-rw-rw---- 1 mysql mysql 6882 Nov 16 13:50 error.log
-rw-rw---- 1 mysql mysql 865 Nov 16 13:50 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Nov 16 13:50 ibdata1
drwx------ 2 mysql mysql 4096 Nov 16 13:50 mysql
drwx------ 2 mysql mysql 4096 Nov 16 13:50 performance_schema
drwx------ 2 mysql mysql 4096 Nov 16 13:49 test
3. 版本升级
1 | shell> /etc/init.d/mysqld stop #安全的停止数据库的运行 |
5.1.X、5.5.X、5.6.X是可以直接通过该方式升级到5.7.X。5.0.X未知，需要测试
注意：
如果原来数据二进制文件保存在/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data目录下,在升级之前，要么将该目录的数据拷贝到新的你指定的data目录（比如/usr/local/mysql-5.7.9-linux-glibc2.5-x86_64/data ），要么修改my.cnf，将datadir指向/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data，总之一定要确保my.cnf中的数据位置和你实际的数据位置是一致的，不管是默认的也好，还是你datadir指定的也好
4.关于降级问题的说明
通过覆盖mysql系统表的方式存在问题，会导致启动不起来。官方建议如下：
官方MySQL5.7降级建议
上述建议中使用的SQL语句可在mysql5.7的源码的srcipts/mysql_system_tables_fix_for_downgrade.sql中找到，或者直接运行这个sql脚本。
姜老师测试后发现，有bug; 可以启动，但是原来的用户表，无法访问。
当前记录时间是2015-11-17，等待下一步解决方案。
二. MySQL的连接登录
###1. 几种登录方式
方式一
mysql -p- 该方法默认使用root用户, 可使用
select user();查看当前用户
- 该方法默认使用root用户, 可使用
方式二
mysql -S /tmp/mysql.sock -u root -p密码A- 该方法适用于在安装MySQL主机上进行本地登录
方式三
mysql -h 127.0.0.1 -u root -p密码B- 使用
'root'@'127.0.0.1'这个用户登录
- 使用
方式四
mysql -h localhost -u root -p密码A- 该方式等价与【方式二】，且和【方式三】属于两个不同的“用户”
###2. 免密码登录
- 方式一
my.cnf增加[client]标签1
2
3[client]
user="root"
password="你的密码"
1
2
3
4
5
6
7
8
#单对定义不同的客户端
[mysql] # 这个是给/usr/loca/mysql/bin/mysql 使用的
user=root
password="你的密码"
[mysqladmin] # 这个是给/usr/local/mysql/bin/mysqladmin使用的
user=root
password="你的密码"
**每个不同的客户端需要定义不同的标签，使用`[client]`可以统一**
方式二
login-path1
2
3
4
5
6
7
8
9
10
11shell> mysql_config_editor set -G vm1 -S /tmp/mysql.sock -u root -p
Enter password [输入root的密码]
shell> mysql_config_editor print --all
[vm1]
user=root
password=*****
socket=/tmp/mysql.sock
#login
shell> mysql --login-path=vm1 # 这样登录就不需要密码，且文件二进制存储 ,位置是 ~/.mylogin.cnf该方式相对安全。如果server被黑了，该二进制文件还是会被破解
方式三
~/.my.cnf, 自己当前家目录1
2
3
4#Filename: ~/.my.cnf
[client]
user="root"
password="你的密码"
三. MySQL 参数介绍和设置
###1. 参数的分类
- 全局参数：GLOBAL
- 可修改参数
- 不可修改参数
- 会话参数：SESSION
- 可修改参数
- 不可修改参数
1: 用户可在线修改
非只读参数，只读参数只能预先在配置文件中进行设置，通过重启数据库实例,方可生效。
2: 所有的在线修改过的参数(GLOBAL/SESSION)，在重启后，都会丢失，不会写如
my.cnf，无法将修改进行持久化
3: 有些参数，即存在于
GLOBAL又存在于SESSION, 比如autocommit(PS：MySQL默认是提交的)
###2. 查看参数
1 | mysql> show variables; # 显示当前mysql的所有参数，且无隐藏参数 |
###3. 设置参数
设置全局(GLOBAL)参数
1
2
3
4
5mysql> set global slow_query_log = off; #不加global，会提示错误
#slow_query_log是全局参数
mysql> set slow_query_log = off; # 下面就报错了，默认是会话参数
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL设置会话(SESSION)参数
1
2
3mysql> set autocommit = 0; # 当前会话生效
# 或者
mysql> set session autocommit = 0; # 当前会话生效autocommit同样在GLOBAL中, 也有同样的参数1
mysql> set global autocommit = 1; #当前实例，全局生效
注意：如果这个时候/etc/init.d/mysqld restart, 则全局的autocommit的值会变成默认值，或者依赖于my.cnf的设置值。
执行的效果如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25mysql> show variables like "slow%"; # 原值为ON
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | slow.log |
+---------------------+----------+
3 rows in set (0.00 sec)
mysql> select @@session.autocommit; # 等价于 slect @@autocomit;
+----------------------+
| @@session.autocommit |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)
mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
四. 权限管理
###1. “用户 + IP”的概念
MySQL中同一个用户名，比如Bob,能否登录，以及用什么密码登录，可以访问什么库等等，都需要加上IP，才可以表示一个完整的用户标识
`bob@127.0.0.1
和bob@loalhost以及bob@192.168.1.100这三个其实是不同`的 用户标识
###2. 用户权限管理
系统表权限信息:
- a) 用户名和IP是否允许
- b) 查看mysql.user表
// 查看全局所有库的权限 - c) 查看mysql.db表
// 查看指定库的权限 - d) 查看mysql.table_priv表
// 查看指定表的权限 e) 查看mysql.column_priv表
// 查看指定列的权限*tips: mysql> desc [tablename]; 可以查看表的结构信息；*
常用权限：
- SQL语句：SELECT、INSERT、UPDATE、DELETE、INDEX
- 存储过程：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER
管理权限：SUPER、RELOAD、SHOW DATABASE、SHUTDOWN、
可选资源:
- MAX_QUERIES_PER_HOUR count
- MAX_UPDATES_PER_HOUR count
- MAX_CONNECTIONS_PER_HOUR count
MAX_USER_CONNECTIONS count
*tips:只能精确到小时，对于部分场景不适用，可以考虑中间件方式*
- 显示当前用户的权限
1
2
3
4#这三个是同一个意思
mysql> show grants;
mysql> show grants for current_user;
mysql> show grants for current_user();
###3. 基本操作1
2
3
4
5
6
7
8
9
10
11
12
13
14
15mysql> create user 'bob'@'127.0.0.1' identified by '123';
#创建一个认证用户为'bob'@'127.0.0.1',密码是123
mysql> grant all on NWDB.* to 'bob'@'127.0.0.1';
#授予他NWDB库下面所有表的所有访问权限; *.*表示所有库的所有表
mysql> grant all on NWDB.* to 'alice'@'127.0.0.1' identified by '123';
#这个grant语句会搜索用户，如果用户不存在，则自动创建用户，
#如果不带identified by, 则该用户名密码为空
mysql> grant all on *.* to 'tom'@'192.168.10.%' identified by '123' with grant option;
#表示这个用户'tom'@'127.0.0.1'可以访问所有库的所有表，
#同时，他还可以给其他用户授予权限(with grant option)，
#注意如果，*.*改成了某一个指定的非USER库，
#则tom没法去新建其他用户了，因为User库没有权限了
#192.168.10.% 表示属于192.168.10.0/24网段的用户可以访问
4. 撤销权限
revoke关键字，该关键字只删除用户权限，不删除用户revoke语法同grant一致, 从grant ... to变为revoke ... from
