目录
MySQL介绍
数据库基本概念
数据库(database):数据库是一个以某种有组织的方式存储的数据集合,是保存有组织的数据的容器。适合存储到数据库的数据:
- 重要性比较高的数据
- 关系较为复杂的数据
数据库管理系统(DBMS):数据库管理系统是管理数据库的软件,数据库通过DBMS创建和操纵容器。DBMS在行业中分为两个派系:
- RDBMS(Relational Database Management System):关系型数据库管理系统,比较适合于安全级别要求高的数据以及关系较复杂的数据,包括MySQL、Oracle、MsSQL(SQL Server)等
- NoSQL(Not only SQL):非关系型数据库管理系统,适合高性能存储数据,可配合RDBMS进行使用;也可针对大数据处理分析和分布式架构,包括:
键值(Key-Value):Redis、memcached
文档(document):mongodb
MySQL简介
MySQL是一个开源的关系型数据库管理系统(DBMS),企业版本选择(MySQL分支版本):
- Oracle:MySQL官方版(InnoDB存储引擎、MGR、InnoDB Cluster)
- Redhat:MariaDB(Xtradb、Zabbix(TokuDB引擎、MyRocks引擎)、Xtradb cluster)
- Percona:PerconaDB,建议使用它提供的工具percona toolkit,XBK(xtrabackup),PXC
MySQL分为Alpha、Beta、Gamma、Generally Available(GA)等版本:
- Alpha:开发中版本
- Beta:测试版
- Gamma:比Beta更高级版本
- Generally Available(GA):稳定版
MySQ行业主流版本, 版本选择建议要上新环境,一般选择官方主流版本5.6、5.7–GA(稳定发布版),6-12月的产品版本:
- MySQL 5.6:5.6.20、5.6.34、5.6.36、5.6.38、5.6.40
- MySQL 5.7:5.7.18、5.7.20、5.7.22
- MySQL 8.x
本次使用MySQL 5.7.20版本(Sep 13,2017)进行学习。
MySQL安装
MySQL主要提供三种格式的MySQL包的安装:
- 源码格式:source code,源代码
- 二进制格式:binary code,由源码经过编译得到,机器跑的码
- RPM格式:分为binary rpm和source rpm
安装方式 | 包名举例 | 包名含义 | 介绍 |
---|---|---|---|
二进制包 | mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz | 软件名<版本>-<CPU架构>.tar.gz | 二进制包包含了已经过编译,可以马上运行的程序 |
源码包 | mysql-5.7.20.tar.gz | 软件名<版本>.tar.gz | 源代码包里面包括了程序原始的程序代码,需要在计算机上进行编译以后才可以产生可以运行程序,所以从源代码安装的时间会比较长。 |
RPM包 | MySQL-server-5.7.20-1.el6.x86_64.rpm | 软件件名<版本><CPU架构>.rpm | RPM是redhat系列发行版本所用的格式软件包,可使用rpm -ivh安装。yum安装其实就是自动判断rpm包的依赖,一次性把所有需要安装的rpm统一下载安装,其原理和一个个的安装rpm没有本质区别。 |
注意:无论采用哪种安装方式,一定要记住MySQL的安装目录。
MySQL源码安装
-
官网下载source code源码包
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.20.tar.gz
-
解压并重命名目录
tar xf mysql-boost-5.7.20.tar.gz mv mysql-boost-5.7.20 mysql
-
安装依赖包
yum -y install gcc gcc-c++ make cmake ncurses ncurses-devel perl boost boost-doc boost-devel
-
创建关键用户及目录
mkdir -p /usr/local/mysql mkdir -p /usr/local/mysql/data useradd -M -s /sbin/nologin mysql chown -R mysql.mysql /usr/local/mysql
-
cmake进行预编译
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/\ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql \ -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_MEMORY_STORAGE_ENGINE=1 \ -DWITH_SYSTEMD=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=./boost
-
编译安装
make && make install
MySQL二进制安装
-
官网下载MySQL 5.7.20 二进制包
[root@localhost app]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
-
解压并重命名目录
[root@localhost app]# tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz [root@localhost app]# mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql [root@localhost app]# ls mysql [root@localhost app]# ll mysql/ total 36 drwxr-xr-x. 2 root root 4096 Sep 23 11:43 bin -rw-r--r--. 1 7161 31415 17987 Sep 13 2017 COPYING drwxr-xr-x. 2 root root 55 Sep 23 11:43 docs drwxr-xr-x. 3 root root 4096 Sep 23 11:43 include drwxr-xr-x. 5 root root 229 Sep 23 11:43 lib drwxr-xr-x. 4 root root 30 Sep 23 11:43 man -rw-r--r--. 1 7161 31415 2478 Sep 13 2017 README drwxr-xr-x. 28 root root 4096 Sep 23 11:43 share drwxr-xr-x. 2 root root 90 Sep 23 11:43 support-files
-
将bin目录添加到环境变量中(bin目录是MySQL安装目录下的一个特别重要的目录)
[root@localhost app]# vim /etc/profile export PATH=/app/mysql/bin:$PATH [root@localhost app]# source /etc/profile
-
创建mysql用户和组,并修改mysql目录权限
[root@localhost app]# useradd -M -s /sbin/nologin mysql [root@localhost app]# chown -R mysql.mysql /app/mysql [root@localhost app]# mkdir -p /data/mysql [root@localhost app]# chown -R mysql.mysql /data/mysql
-
初始化数据(建库)
一套完整的数据库管理系统的基本结构- 软件部分:/app/mysql
- 数据部分:系统相关数据(维持mysql数据库软件程序,正常运行的系统数据)、用户数据(与用户应用程序相关的数据)
方法一:初始化数据,初始化管理员的临时密码 [root@localhost app]# mysqld --initialize --user=mysql --basedir=/app/mysql --datadir=/data/mysql ... 2021-09-23T04:05:57.310710Z 1 [Note] A temporary password is generated for root@localhost: 1:hl_c2z1bS< 方法二:初始化数据,初始化管理员的密码为空 [root@localhost app]# mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/mysql --initialize:初始化数据 --user:数据库管理员 --basedir:数据库软件放置位置 --datadir:数据库数据放置位置 [root@localhost app]# cd /data/mysql/ [root@localhost mysql]# ls auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema sys
扩展:5.6初始化区别
/app/mysql/scripts/mysql_install_db --user=mysql --datadir=/app/mysql/data --basedir=/app/mysql
重要说明:5.7开始,MySQL加入全新的密码安全机制(加入insecure后关闭该机制)
- 初始化完成后,会生成临时密码(显示到屏幕,日志也有记录)
- 密码复杂度要求:长度超过12位,字符混合
- 密码过期时间180天
-
编辑MySQL配置文件/etc/my.cnf(应该默认为空,若有内容表示maraidb没有卸载干净)
[root@localhost ~]# vim /etc/my.cnf [mysqld] user=mysql basedir=/app/mysql datadir=/data/mysql server_id=6 port=3306 socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock prompt=3306 [\\d]>
-
配置启动脚本
[root@localhost mysql]# cd /app/mysql/support-files [root@localhost support-files]# ./mysql.server start Starting MySQL.Logging to '/data/mysql/localhost.localdomain.err'. . SUCCESS! 将启动脚本复制到/etc/init.d/下 [root@localhost support-files]# cp mysql.server /etc/init.d/mysqld 而后就可以通过service进行管理 [root@localhost ~]# service mysqld stop Shutting down MySQL.. SUCCESS!
-
使用systemd管理mysql
[root@localhost ~]# vim /etc/systemd/system/mysqld.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/app/mysql/bin/mysqld --defaults-file=/etc/my.cnf LimitNOFILE = 5000
注意:若mysqld处于启动状态先将其关闭,然后再使用systemd管理。
[root@localhost ~]# service mysqld stop Shutting down MySQL.. SUCCESS! [root@localhost ~]# systemctl start mysqld
-
安装后的简单管理
# 修改mysql root密码 [root@localhost ~]# mysqladmin -uroot -p password 123456 Enter password: mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. # MySQL登录测试 [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.
查看数据库中的用户密码等信息
5.6版本
select user,password,host from mysql.user;5.7版本
select user,authentication_string,host from mysql.user;3306 [(none)]>select user,authentication_string,host from mysql.user; +---------------+-------------------------------------------+-----------+ | user | authentication_string | host | +---------------+-------------------------------------------+-----------+ | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | +---------------+-------------------------------------------+-----------+ 3 rows in set (0.00 sec)
重要的bin目录
在MySQL的安装目录下有一个特别重要的bin目录,这个目录放置了很多可执行文件。这些执行文件可以使用相对路径/绝对路径执行,或者将bin目录对应的绝对路径加入到环境变量PATH中让系统自动查找该路径下的可执行文件。
[root@localhost ~]# cd /app/mysql/bin/
[root@localhost bin]# ls
innochecksum mysql_config_editor mysqlshow
lz4_decompress mysqld mysqlslap
myisamchk mysqld-debug mysql_ssl_rsa_setup
myisam_ftdump mysqld_multi mysqltest_embedded
myisamlog mysqld_safe mysql_tzinfo_to_sql
myisampack mysqldump mysql_upgrade
my_print_defaults mysqldumpslow mysqlxtest
mysql mysql_embedded perror
mysqladmin mysqlimport replace
mysqlbinlog mysql_install_db resolveip
mysqlcheck mysql_plugin resolve_stack_dump
mysql_client_test_embedded mysqlpump zlib_decompress
mysql_config mysql_secure_installation
在类Unix系统中用来启动MySQL服务器程序的可执行文件有很多,大部分都位于bin目录下:
- mysqld:mysqld可执行文件就表示MySQL服务器程序,运行这个可执行文件就可以直接启动一个MySQL服务器进程。
- mysqd_safe:mysql_safe是一个启动脚本,它会间接调用mysqld并持续监控服务器的运行状态。当服务器进程出现错误时,它还可以帮助重启服务器程序。且mysql_safe启动MySQL服务器程序时,它会将服务器程序的出错信息和其他诊断信息输出到错误日志,方便以后查找错误原因。
- mysqld_multi:mysql_multi可执行文件可以启动或停止多个服务器进程,也能报告它们的运行状态。
bin目录下还有很多客户端程序,如mysql、mysqldump、mysqladmin、mysqlcheck等。重点关注可执行文件mysql,通过该可执行文件与数据库服务器程序交互。
[root@localhost bin]# cd ../support-files/
[root@localhost support-files]# ll
total 24
-rw-r--r--. 1 mysql mysql 773 Sep 13 2017 magic
-rwxr-xr-x. 1 mysql mysql 1061 Sep 14 2017 mysqld_multi.server
-rwxr-xr-x. 1 mysql mysql 894 Sep 14 2017 mysql-log-rotate
-rwxr-xr-x. 1 mysql mysql 10576 Sep 14 2017 mysql.server
support-files目录下的mysql.server也是一个启动脚本
- mysql.server:mysql_server脚本会间接调用mysqld_safe。在执行mysql.server时,在后面添加start就可以启动服务器进程了。
mysql.server start
MySQL基础管理
连接管理
MySQL要求在能执行命令之前登录到DBMS,不同的用户登录到数据库拥有不同的权限。MySQL在内部保护自己的用户列表,并把每个用户与各种权限关联起来(show grants for 用户; 可查用户权限)。为了连接到MySQL,需要以下信息:
- 主机名(如果连接到本地MySQL数据库,为localhost)
- 端口(默认端口3306)
- 合法用户名
- 用户口令
可使用mysql命令行实用程序连接数据库,mysql常用参数如下:
mysql
-u 用户(类Unix系统省略-u参数,会将登录操作系统的用户名当作MySQL用户名处理)
-p 密码(-p与密码之间不要有空格,建议不要显式输入密码)
-h IP(本地为localhost或127.0.0.1,且本地-h参数可省略)
-P 端口(必须与-h同时使用)
-S socket文件(套接字通信)
-e 免交互执行命令(命令行查看一些信息)
< 导入SQL脚本(备份恢复很有用)
# TCP/IP连接登录
[root@localhost mysql]# mysql -uroot -p -h192.168.1.4 -P3306
Enter password:
mysql [(none)]>select @@socket;
+-----------------+
| @@socket |
+-----------------+
| /tmp/mysql.sock |
+-----------------+
1 row in set (0.00 sec)
# Socket登录
[root@localhost mysql]# mysql -uroot -p -S /tmp/mysql.sock
Enter password:
# 免交互执行sql语句
[root@localhost mysql]# mysql -uroot -p123456 -e "select user,host from mysql.user;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| app | 10.0.0.% |
| root | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
# sql脚本执行
[root@localhost mysql]# mysql -uroot -p < test.sql
Enter password:
启动方式
以上多种方式,都可以单独启动MySQL服务:
- mysqld_safe和mysqld一般是在临时维护时使用
- mysql.server是CentOS 6和7通用的启动方式,可将mysql.server拷贝到/etc/init.d/通过service服务启动
从Centos 7系统开始,支持systemd直接调用mysqld的方式进行启动数据库,编辑文件/etc/systemd/system/mysqld.service。
初始配置
-
初始配置作用
- 控制MySQL的启动;
- 影响客户端的连接。
-
初始配置方法
- 预编译(源码包)
- 配置文件(适用于所有启动方式)
- 命令行参数(仅限于mysqld_safe、mysqld启动方式)
-
启动选项
对于在程序启动时指定的设置称之为启动选项,这些选项控制着程序启动后的行为。启动选项可以在命令行中指定(仅对当次启动生效),也可以在配置文件中指定(在命令行中指定需要加上- - 前缀,在配置文件中则不需要)。启动服务器程序时在命令行中设置启动选项的通用格式:
–启动选项1[=值1] --启动选项2[=值2]… --启动选项n[=值n]
例如:
mysqld --skip-networking 禁止客户端使用TCP/IP网络进行通信 mysqld --default-storage-engine=MyISAM 设置表的默认引擎为MyISAM
使用mysqld --verbose --help可以查看mysqld程序支持的启动选项:
[root@localhost ~]# mysqld --verbose --help | more mysqld Ver 5.7.20-log for linux-glibc2.12 on x86_64 (MySQL Community Server (GPL)) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Starts the MySQL database server. Usage: mysqld [OPTIONS] Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf The following groups are read: mysqld server mysqld-5.7 The following options may be given as the first argument: --print-defaults Print the program argument list and exit. --no-defaults Don't read default options from any option file, except for login file. --defaults-file=# Only read default options from the given file #. --defaults-extra-file=# Read this file after the global files are read. --defaults-group-suffix=# Also read groups with concat(group, suffix)
-
配置文件
每次启动服务器程序时,都去加载配置文件中的内容。因此想要启动选项永久生效,可以将其写入配置文件中。-
配置文件的默认读取路径
[root@localhost mysql]# mysqld --help --verbose |grep my.cnf /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default
默认情况下,MySQL启动时,会依次读取以上配置文件,如果有重复选项,会以最后一个文件设置的为准。
但是,如果启动时加入了–defaults-file=xxxx时,以上的所有文件都不会读取. -
配置文件的编辑格式
配置文件中的启动选项被划分为若干个组,每个组有一个组名,使用括号括起来。配置文件中不同的选项组是给不同的程序使用的。配置文件中的选项不允许加- - 前缀,并且每行只能指定一个选项。服务器端选项组 --控制MySQL启动
[mysqld] → mysqld服务器程序
(具体的启动选项…)
[mysqld_safe] → mysqld_safe服务器程序
(具体的启动选项…)
[server] → 作用于所有的服务器程序
(具体的启动选项…)客户端选项组 --连接MySQL
[mysql] → mysql客户端程序
(具体的启动选项…)
[mysqldump] → mysqldump客户端程序
(具体的启动选项…)
[client] → 作用于所有客户端程序
(具体的启动选项…)
优先级
-
多个配置文件中的相同启动选项,以最后一个配置文件的启动选项为准;
-
同个配置文件中的不同的组,以最后一个出现的组中出现的启动选项为准;
-
配置文件和命令行都出现的启动选项,以命令行中的启动选项为准。
[root@localhost ~]# cat /etc/my.cnf [mysqld] user=mysql --mysql用户 basedir=/app/mysql --程序放置路径 datadir=/data/mysql --数据放置路径 socket=/tmp/mysql.sock --socket文件(数据库启动才有该文件) server_id=6 --用于主从复制 port=3306 --端口号 log_error=/data/mysql/mysql.log [mysql] socket=/tmp/mysql.sock --socket文件(保证和服务端相同) prompt=Master [\\d]> --连接到数据库的名称显示
-
基础管理
选择数据库
最初连接到数据库没有任何数据库打开供给使用,可使用USE关键字选择一个数据库,而后执行数据库操作。例如,使用mysql数据库:
mysql [(none)]>use mysql;
Database changed
USE语句并不返回任何结果,这里显示的Database changed是mysql命令行实用程序在数据库选择成功后显示的。
查看数据库和表信息
数据库、表、列、用户、权限等信息都被存储在数据库和表中,可使用MySQL的SHOW来显示这些信息。
查看可用数据库信息:
mysql [mysql]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
查看数据库内表信息:
mysql [mysql]>show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
...
显示表内的列信息:
mysql [mysql]>show columns from user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
...
DESCRIBE user可作为SHOW COLUMNS FROM user;的快捷方式。
总结:
- show databases;显示可用数据库信息
- show tables:显示数据库内可用表信息
- show columns from <table>;显示表内列信息
- show status;显示广泛的服务器状态信息;
- show grants for <user>;显示授予用户(所有用户或特定用户)的安全权限;;
- show errors;/show warnings;显示服务器错误或警告信息
查看系统变量
MySQL服务器程序在运行过程中会用到许多影响程序行为的变量,它们被称为系统变量。
查看MySQL服务器程序支持的系统变量以及它们的当前值可以使用如下命令:
SHOW VARIABLES [LIKE 匹配的模式];
mysql [(none)]>show variables like 'default_storage%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.18 sec)
设置系统变量可以在服务器程序启动时通过启动选项(命令行/配置文件)设置,或者在服务器程序运行过程中使用SET语句设置:
-
启动选项(服务器程序启动时)
# 命令行 mysqld --default-storage-engine=MyISAM # 配置文件 vim my.cnf default-storage-engine=MyISAM
-
SET语句(服务器程序运行时)
SET [GLOBAL|SESSION] 系统变量=值;
GLOBAL:全局范围,影响服务器的整个操作
SESSION:会话范围,影响某个客户端连接的操作# 设置当前会话的default-storage-engine=MyISAM SET SESSION default-storage-engine=MyISAM; SET default-storage-engine=MyISAM; # 设置全局的default-storage-engine=MyISAM SET GLOBAL default-storage-engine=MyISAM;
注意:某个客户端改变了某个系统变量在GLOBAL范围的值,只会影响后续连入的客户端作用范围为SESSION的值,而不会影响之前就已经连入的客户端的SESSION的值。
查看状态变量
MySQL服务器程序维护了很多关于程序运行状态的变量,称之为状态变量。状态变量的值只能服务器自己设置,而不能人为设置。
查看状态变量的语句如下所示:
SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配模式];
mysql [(none)]>show status like 'thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 2 |
| Threads_created | 2 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.39 sec)
用户管理
-
作用:登录、管理数据库逻辑对象
-
定义
用户名@'白名单':白名单支持哪些IP登录数据库 mysql@'%' --所有地址 mysql@'10.0.0.%' --10.0.0.0/24网段的地址 mysql@'10.0.0.0/255.255.254.0' --10.0.0.0/23网段的地址 mysql@'10.0.0.200' --10.0.0.200 mysql@'10.0.0.5%' --10.0.0.50-10.0.0.59 mysql@'localhost' --本地主机
-
管理操作
# 登入数据库 [root@localhost mysql]# mysql -uroot -p123456 # 切换数据库 mysql [(none)]>use mysql; # 增 mysql [mysql]>create user letty@'10.0.0.%' identified by '123456'; Query OK, 0 rows affected (0.26 sec) # 查 mysql [mysql]>desc mysql.user; # 查看列信息 mysql [mysql]>select user,host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | letty | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +---------------+-----------+-------------------------------------------+ 4 rows in set (0.00 sec) # 改 mysql [mysql]>alter user letty@'10.0.0.%' identified by 'mysql'; Query OK, 0 rows affected (0.00 sec) # 删 mysql [mysql]>drop user letty@'10.0.0.%'; Query OK, 0 rows affected (0.00 sec)
权限管理
权限介绍
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
- ALL:以上所有权限,普通管理员拥有
- with grant option:超级管理员拥有,给其他用户授权的权限
- SELECT, INSERT, UPDATE, DELETE:应用用户拥有
授予权限
grant 权限 on 对象 to 用户 identified by ‘密码’;
grant all on wordpress.* to wordpress@'10.0.0.%' identified by '123456';
- grant:授权命令
- all:权限
- on:命令关键字,在什么上设置权限
- wordpress.*:权限作用范围
*.*:chmod 755 -R /,所有库 → 管理员用户
wordpress.*:chmod 755 -R /wordpress,仅对wordpress → 开发和应用用户
wordpress.t1:chmod 755 -R /wordpress/t1,仅对wordpress下的t1表 - to:命令关键字,对哪个用户进行设置
- wordpress@‘10.0.0.%’:用户
例1:windows的管理员用户通过navicat登录到linux中的MySQL
grant all on *.* to root@'10.0.0.%' identified by '123456';
例2:创建一个应用用户app用户,能从windows上登录mysql,并能操作app库
grant select,update,insert,delete on app.* to app@'10.0.0.%' identified by '123456';
对开发人员用户授权流程:
- 权限(root权限不能给任何人,即使给其他权限也应当走规范的流程)
- 对谁操作(确定需要操作的库)
- 你从哪里来(确定要连接数据库的IP)
- 密码要求(密码复杂度要求到位)
注意:8.0在grant命令添加新特性
- 建立用户与授权分开;
- grant不再支持自动创建用户,不支持更改密码;
- 授权之前,必须提前创建用户
查看权限
show grants for 用户;
mysql [mysql]>show grants for app@'10.0.0.%';
+---------------------------------------------------------------------+
| Grants for app@10.0.0.% |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app'@'10.0.0.%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app`.* TO 'app'@'10.0.0.%' |
+---------------------------------------------------------------------+
2 rows in set (0.10 sec)
mysql [mysql]>show grants for root@'10.0.0.%';
+--------------------------------------------------+
| Grants for root@10.0.0.% |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%' |
+--------------------------------------------------+
1 row in set (0.09 sec)
回收权限
revoke 权限 on 对象 from 用户;
mysql [mysql]>revoke delete on app.* from app@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql [mysql]>show grants for app@'10.0.0.%';
+-------------------------------------------------------------+
| Grants for app@10.0.0.% |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app'@'10.0.0.%' |
| GRANT SELECT, INSERT, UPDATE ON `app`.* TO 'app'@'10.0.0.%' |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)
本地管理员用户忘记密码
skip-grant-tables参数:在连接层不做验证,即不将授权功能加入内存(在重启数据库时加入该参数)
skip-networking参数:关闭连接层TCP/IP连接方式
[root@localhost mysql]# mysqld_safe --skip-grant-tables --skip-networking & --mysqld_safe方式启动mysql
[root@localhost mysql]# mysql --不做验证直接登入数据库
mysql> flush privileges; --先将磁盘的授权表刷到内存后再更改密码
mysql> alter user root@'localhost' identified by '123456';
[root@db01 ~]# pkill mysqld
[root@db01 ~]# systemctl start mysqld
多实例应用
-
准备多个目录
[root@localhost ~]# mkdir -p /data/330{7,8,9}/data
-
准备配置文件
[root@localhost ~]# cat > /data/3307/my.cnf <<EOF [mysqld] basedir=/app/mysql datadir=/data/3307/data socket=/data/3307/mysql.sock log_error=/data/3307/mysql.log port=3307 server_id=7 log_bin=/data/3307/mysql-bin EOF [root@localhost ~]# cat > /data/3308/my.cnf <<EOF [mysqld] basedir=/app/mysql datadir=/data/3308/data socket=/data/3308/mysql.sock log_error=/data/3308/mysql.log port=3308 server_id=8 log_bin=/data/3308/mysql-bin EOF [root@localhost ~]# cat > /data/3309/my.cnf <<EOF [mysqld] basedir=/app/mysql datadir=/data/3309/data socket=/data/3309/mysql.sock log_error=/data/3309/mysql.log port=3309 server_id=9 log_bin=/data/3309/mysql-bin EOF
-
初始化数据
[root@localhost ~]# mv /etc/my.cnf /etc/my.cnf.bak [root@localhost ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/mysql [root@localhost ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/mysql [root@localhost ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/mysql
-
systemd管理多实例
[root@localhost ~]# cd /etc/systemd/system [root@localhost system]# cp mysqld.service mysqld3307.service [root@localhost system]# vim mysqld3307.service ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf [root@localhost system]# cp mysqld.service mysqld3308.service [root@localhost system]# vim mysqld3308.service ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf [root@localhost system]# cp mysqld.service mysqld3309.service [root@localhost system]# vim mysqld3309.service ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
-
授权相关目录
[root@localhost system]# chown -R mysql.mysql /data/*
-
启动多实例
[root@localhost system]# systemctl start mysqld3307.service [root@localhost system]# systemctl start mysqld3308.service [root@localhost system]# systemctl start mysqld3309.service
-
验证多实例
[root@localhost system]# netstat -lnp|grep 330 [root@localhost system]# mysql -S /data/3307/mysql.sock -e "select @@server_id" [root@localhost system]# mysql -S /data/3308/mysql.sock -e "select @@server_id" [root@localhost system]# mysql -S /data/3309/mysql.sock -e "select @@server_id"