Mysql的管理及使用
第1章 Mysql的管理
1.1 连接管理mysql
mysql[options] #Linux或UNIX shell提示符(终端窗口) mysql --help #查看帮助信息 mysql --version/-V #查看客户端程序的版本
01.常见的选项语法格式：
长格式(–<option>)
简易格式(-<option>)
02.通过指定的主机进行连接：
本地连接到在同一主机上运行的服务器
远程连接到在其他主机上运行的服务器
03.常见特定于客户机的连接选项
-u<user_name>或--host=<user_name>
-p<password>
-h<host_name>或--host=<host_name>
--protocol=<protocol_name> #协议
-P<port_number>或--port=<port_number>
-S<socket_name>或--socket=<socket_name>
1.2 数据库启动流程介绍
1 [root@db02 ~]# file /application/mysql/bin/mysqld 2 3 /application/mysql/bin/mysqld: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped
提示：单进程mysqld 其属性LSB即二进制的可执行文件，mysql.server便于我们通过SYS-V启动关闭的脚本
说明：support-files/mysql.server及mysqld_safe都是脚本文件，最后都调用mysqld二进制文件启动
1.3 mysql数据库的关闭与启动
01.数据库关闭命令
1 /etc/init.d/mysqld stop
2 mysqladmin -uroot -poldboy123 shutdown
注意：生产环境下禁止使用下述方式关闭mysql软件
kill -9 #利用系统进程管理命令关闭MySQL kill pid # pid即数据库服务对应的进程号 l killall mysqld #mysqld即数据库服务对应的进程名 l pkill mysqld #mysqld即数据库服务对应的进程名
02.数据库启动命令
1 /etc/init.d/mysqld start
2 application/mysql/bin/mysqld_safe [–user=mysql] &
1.3.1 perror查看错误详细内容
在mysql使用过程中，由于操作系统引起的文件目录不存在或sql语句错误引起的报错，这些error会有相应的代码如errorN、ErrcodeN，这里”N”代表具体的错误号
1 [root@db02 ~]# perror 126 127 135 2 OS error code 126: Required key not available 3 MySQL error code 126: Index file is crashed 4 OS error code 127: Key has expired 5 MySQL error code 127: Record file is crashed 6 MySQL error code 135: No more room in record file
1.4 Mysql初始化配置文件
即在数据库启动之前通过/etc/my.cnf告诉启动程序一系列预设置的选项
配置文件功能：影响服务器进程的启动；影响到客户端程序
实例01
1 ./mysqld_safe –basedir=/application/mysql –datadir=/application/mysql/data –socket=/tmp/mysql.sock –user=mysql #指定mysql启动参数
注意：由于指定了socket导致无法找到该文件，此时mysql无法进行正常连接，故使用mysql -uroot -poldboy123 -S /tmp/mysql.sock即可
1.4.1 配置my.cnf的方法
使用不同的”标签/模块”明确指定影响哪部分功能，代表一类程序
实例02
1 vim /etc/my.cnf 2 3 [mysqld] 4 basedir=/application/mysql 5 datadir=/application/mysql/data 6 socket=/tmp/mysql.sock 7 log-error=/var/log/mysql.log 8 port=3307 #其中，用户无需指定 9 10 [mysql] 11 socket=/tmp/mysql.sock 12 user=root 13 password=oldboy123 #此处通过socket登录，无需配置端口 14 /etc/init.d/mysqld restart #重启生效
说明：此时检查进程信息，可以看到与我们手动配置的my.cnf一致
1 [root@db02 ~]# ps -ef |grep [my]sql 2 3 root 3411 1918 0 15:52 pts/1 00:00:00 /bin/sh /application/mysql/bin/mysqld_safe 4 5 mysql 3548 3411 0 15:52 pts/1 00:00:00 /application/mysql/bin/mysqld --basedir=/application/mysql --datadir=/application/mysql/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/var/log/mysql.log --pid-file=/application/mysql/data/db02.pid --socket=/tmp/mysql.sock --port=3307
1.4.2 配置文件读取顺序
/etc/my.cnf ↓ ↓ ↓ /etc/mysql/my.cnf $MYSQL_HOME/my.cnf defaults-extra-file ~/.my.cnf
说明：假设4个配置文件都存在， 同时使用 –defaults-extra-file指定了参数文件，这时如果有一个”参数变量”在5个配置文件中都出现了，那么后面的配置文件中的参数变量值会覆盖前面配置文件中的参数变量值，就是说会使用 ~/. my.cnf中设置的值
注意：若使用 ./bin/mysqld_safe守护进程启动mysql数据库时，使用了 –defaults-file=<配置文件的绝对路径>参数，这时只会使用这个参数指定的配置文件
1.5 Mysql多实例配置
多个mysqld+多套my.cnf+多套数据=>生产环境无用
思路：
01.初始化两套数据到不同目录mysql_install_db /data/3306 /data/3308 02.两套配置文件 /data/3306/my.cnf /data/3308/my.cnf 03.两个socket socket=/data/3306/mysql.sock socket=/data/3308/mysql.sock 04.两套实例 /application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf /application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf 05.设计启动脚本
1.5.1 实例配置文件及程序脚本
注意：需首先关闭当前mysql服务，防止造成端口冲突/etc/init.d/mysqld stop
1 [root@db02 ~]# vim /data/3306/my.cnf 2 [client] 3 port = 3306 4 socket = /data/3306/mysql.sock 5 6 [mysqld] 7 user = mysql 8 port = 3306 9 socket = /data/3306/mysql.sock 10 basedir = /application/mysql 11 datadir = /data/3306/data 12 log-bin = /data/3306/mysql-bin 13 server-id = 6 14 15 [mysqld_safe] 16 log-error=/data/3306/oldboy_3306.err 17 pid-file=/data/3306/mysqld.pid
my.cnf
[root@db02 ~]# vim /data/3306/mysql #!/bin/sh port=3306 mysql_user="root" CmdPath="/application/mysql/bin" mysql_sock="/data/${port}/mysql.sock" mysqld_pid_file_path=/data/3306/3306.pid start(){ if [ ! -e "$mysql_sock" ];then printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null & sleep 3 else printf "MySQL is running...\n" exit 1 fi } stop(){ if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit 1 else printf "Stoping MySQL...\n" mysqld_pid=`cat "$mysqld_pid_file_path"` if (kill -0 $mysqld_pid 2>/dev/null) then kill $mysqld_pid sleep 2 fi fi } restart(){ printf "Restarting MySQL...\n" stop sleep 2 start } case "$1" in start) start ;; stop) stop ;; restart) restart ;; *) printf "Usage: /data/${port}/mysql {start|stop|restart}\n" esac
mysql
1.5.2 创建3308端口配置文件
1 \cp /data/3306/my.cnf /data/3308/ 2 \cp /data/3306/mysql /data/3308/ 3 sed -i 's/3306/3308/g' /data/3308/my.cnf 4 sed -i 's/server-id = 6/server-id = 8/g' /data/3308/my.cnf 5 sed -i 's/3306/3308/g' /data/3308/mysql
1.5.3 创建数据目录授权
1 mkdir -p /data/{3306,3308}/data 2 chown -R mysql.mysql /data/ 3 chmod 700 /data/{3306,3308} #增加其安全性
1.5.4 初始化数据并创建错误日志文件
1 cd /application/mysql/scripts 2 ./mysql_install_db --defaults-file=/data/3306/my.cnf --basedir=/application/mysql --datadir=/data/3306/data --user=mysql 3 ./mysql_install_db --defaults-file=/data/3308/my.cnf --basedir=/application/mysql --datadir=/data/3308/data --user=mysql 4 5 6 touch /data/3306/oldboy_3306.err 7 touch /data/3308/oldboy_3308.err
说明：5.6.36特殊性即不会自动创建错误日志文件
1.5.5 启动数据库并进行测试
1 /data/3306/mysql start
2 /data/3308/mysql start
3
4 sleep 5
注意：由于mysql服务的默认端口为3306，故在启动之前需先将mysql服务停止运行并关闭开机自启动
1 [root@db02 scripts]# netstat -lntup|grep 330 2 tcp 0 0 :::3306 :::* LISTEN 1793/mysqld 3 tcp 0 0 :::3308 :::* LISTEN 2001/mysqld 4 5 [root@db02 ~]# ps -ef |grep [m]ysql 6 root 1217 1 0 22:40 ? 00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf --pid-file=/data/3306/3306.pid 7 mysql 1403 1217 0 22:40 ? 00:00:01 /application/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf --basedir=/application/mysql --datadir=/data/3306/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/data/3306/oldboy_3306.err --pid-file=/data/3306/3306.pid --socket=/data/3306/mysql.sock --port=3306 8 9 root 1426 1 0 22:40 ? 00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --defaults-file=/data/3308/my.cnf --pid-file=/data/3308/3308.pid 10 mysql 1611 1426 0 22:40 ? 00:00:01 /application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf --basedir=/application/mysql --datadir=/data/3308/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/data/3308/oldboy_3308.err --pid-file=/data/3308/3308.pid --socket=/data/3308/mysql.sock --port=3308
1.5.5.1 加入开机自启
1 vim /etc/rc.local
2
3 /data/3306/mysql start
4 /data/3308/mysql start
1.5.6 mysql多实例登录
1 mysql -S /data/3306/mysql.sock
2 mysql -S /data/3308/mysql.sock #或mysql -h 10.0.0.52 -P 3306
第2章 Mysql用户管理
2.1 用户的介绍
01.定义：用户名+主机域(host指定要登陆本机的用户主机，而非本机)
02.功能作用：连接数据库、管理数据库对象及数据
03.连接数据库：
A.定义用户：用户名+主机域、密码
B.定义权限：对不同对象进行权限(角色)定义
grant 权限 on 权限范围(对象) to 用户 identified by ‘密码’;
04.权限(角色)：
对数据库的读、写等操作，如create、select、insert、update等
数据库定义好的一组权限的定义，如all privileges、replication slave
05.权限范围：
全库级别：*.* (所有数据库对象) 单库级别：banana.* (oldboy单库下所有对象) 单表级别：banana.test (单表级别)
注意：当在多个权限范围上设置了同一个用户的权限时，以综合最大权限为准。故在一般情况下不要在多个权限范围内设置权限，如库级别oldboy.*、表级别oldboy.test
06.用户：
repl@localhost repl@'10.0.0.53' repl@'10.0.0.%'(%为通配符，匹配所有)或10.0.0.0/255.0.0.0 repl@'10.0.0.5%'
#网段带匹配符%则必须加’ ‘，用户可加可不加
2.2 用户权限实例
查看当前所有用户(选择)
select user,password,host from mysql.user;
创建用户
语法：CREATE USER '用户'@'主机' IDENTIFIED BY '密码'; 实例：create user 'banana'@'10.0.0.%' identified by '123';
授权用户
语法：GRANT ALL ON *.* TO '用户'@'主机'; 实例：grant all on banana.* to banana@'172.16.1.%';
其他实例
grant all on *.* to banana@'172.16.1.%' identified by '123'; #创建用户，同时进行授权 grant all on *.* to banana@'localhost' identified by '123' with grant option; #授权单表root一样的权限 grant select,create,insert,update on banana.* to banana@'10.0.0.%' identified by '123'; #只对banana数据库下的对象进行增insert create、改update、查select
收回用户权限
语法：REVOKE INSERT ON *.* FROM '用户'@'主机'; 实例：revoke drop on banana.* from 'banana'@'10.0.0.%';
查看用户对应权限
语法：SHOW GRANTS FOR '用户'@'主机'; 实例：show grants for banana@'10.0.0.%';
注意：mysql内自带的命令可以大写，而自身定义的用户管理需区分大小写；尽量不要修改授权表
说明：使用数据库自带的命令无需修改授权表如drop、grant，而使用如delete、update等命令则需修改授权表，如下
删除用户
语法：drop user 'user'@'主机域; 实例：drop user banana@'10.0.0.52'; delete from mysql.user where user='banana' and host='localhost'; #强制删除用户(危险) flush privileges;
总结说明：可以授权的用户权限create, select, insert, update, delete, 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
2.3 Mysql忘记密码的解决方法
01.停止mysql服务
1 /etc/init.d/mysqld stop
02.添加参数启动服务
1 /application/mysql/bin/mysqld_safe –skip-grant-tables –skip-networking –user=mysql &
说明：在此模式下启动情况为：无密码登陆、网络用户无法登陆(只能本地登录)、与授权有关的命令均无法执行，如grant,revoke,drop user,create user
03.修改密码
1 mysql 2 3 mysql> use mysql 4 mysql> update mysql.user set password=PASSWORD('123') where user='root' and host='localhost'; #只针对5.6 5 mysql> flush privileges;
04.重启mysql服务
1 /etc/init.d/mysqld restart
05.登录验证
1 mysql -uroot -p123
至此，密码修改成功
第3章 MySQL客户端
3.1 Mysql客户端命令
SQL是用户用来管理及控制数据库的专用语言
mysql：
用于数据库连接管理；将用户SQL语句发送到服务器
mysqladmin：
命令行管理工具
mysqldump：
备份数据库和表的内容
3.1.1 ①mysql客户端接口自带功能
\h 或 help 或 ? #获取帮助信息，如help grant \G #格式化输出(行转列)，如select * from mysql.user\G \T 或 tee #记录操作日志，如tee /tmp/mysql.log 正误均记录 \c 或 CTRL+c #退出，如CTRL+d,exit,\q,quit等 \s 或 status #查看数据库负载、版本及连接等状态信息 \. 或 source #调用SQL脚本，如source world.sql; \u 或use #进入/切换数据库，如use world
快捷键
上下翻页 tab 补全表名 ctrl + C 退出 ctrl + L 清屏
3.1.2 ②mysqladmin命令
命令帮助：mysqladmin --help 语法：mysqladmin -u<name> -p<password> commands
实例：
mysqladmin version
mysqladmin processlist
mysqladmin status
mysqladmin ping
mysqladmin shutdown
mysqladmin variables
3.1.3 ③mysqldump命令
命令帮助：mysqldump --help 基本语法： Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS]
3.2 SQL语句
3.2.1 DDL 数据定义语言
定义对象：库(名字、特性)、表(表名字、列信息)
对于库定义：创建、删除、修改(本身和库中的对象->表,视图,存储过程,函数,触发器)
表定义：创建(定义表结构)、删除(对于表本身)、修改(只针对表结构非表内容)
3.2.1.1 库定义
查看库
show databases; show databases like '%ban%'; #模糊匹配
创建库定义
语法：CREATE DATABASE db_name CHARACTER SET charset_name COLLATE collation_name; 实例：create database banana; create database banana_tong character set gbk collate gbk_chinese_ci; #指定字符集建库
说明：在一些SQL语句的结尾处要使用”;”分号或者使用”\g”或”\G”来结束语句的运行
修改库字符编码
语法：ALTER DATABASE [db_name] CHARACTER SET charset_name COLLATE collation_name;
实例：alter database banana charset gbk;
删除库
drop database banana;
切库
use baanana;
3.2.1.2 表定义
表属性：字段、数据类型、索引 ==>默认：字符集、引擎
表定义(列->必须定义，至少一列)：
表名、列名、列属性(数据类型、列约束)
列定义的范围说明：
1)数据类型->字符/数字
2)约束条件->子健、外键、唯一
3)列属性->比较特殊的定义
定义表之前需先进入数据库
use world;
创建表
语法：CREATE TABLE 表名 (列名 列定义) 实例：create table test(id int); create table student(idcard int ,name char(30),sex char(4)); #创建多列
查看表结构
desc student;
查看建表语句
show create table student;
修改表定义(表名)
rename table student to test1;
alter table test1 rename to people;
添加表特性
alter table people add addr char(40) NOT NULL; alter table people add age int(4) after name; alter table people add tel varchar(15) first; #同时添加多个列定义 alter table people add id int(10) first,add sex char(4) after name;
删除表结构(列字段)
alter table people drop sex;
修改表定义/数据类型
alter table people modify name char(20); alter table people change name people_name char(30);
3.2.2 DCL 数据库控制语言
用户授权：grant
语法：GRANT ALL ON *.* TO 'user'@'localhost'; 实例：grant select,create,insert,update on banana.* to 'banana'@'10.0.0.%' identified by '123'; #创建用户的同时进行授权
回收权限：revoke
语法：REVOKE INSERT ON *.* FROM sys@localhost; 实例：revoke drop,delete on banana.* from sys@localhost;
3.2.3 DML 数据操作语言
插入数据
语法：insert into <表名> [(<字段名1>[,..<字段名n > ])] values (值1 )[,(值n )] 实例：insert into banana values(1,'boy'); #插入一行数据 insert into banana values(1,'boy'), (2,'girl'); #插入多行数据 #只针对某一列插入数据 insert into test(id,name) values(1,'boy');
更新/修改表(切记要有where条件)
update test2 set name='haha' where name='girl';
删除表内容(切记要有where条件)
delete from test where id=1; # 逻辑删除，一行一行删。 truncate table test; # 物理删除，pages(block) ，效率高 insert into `test` values(1,'apple'),(2,'pear'),(3,'banana'), (4,'lemon'), (5,'orange')
3.2.3.1 [企业案例]生产环境中d伪删除
01.为表添加一个state列
TINYINT说明：即字段类型，若设置为UNSIGNED类型，只能存储从0到255的整数，不能用来储存负数
1 mysql> alter table test2 add state tinyint(2) not null default 1; 2 mysql> desc test2; 3 4 +-------+----------+------+-----+---------+-------+ 5 | Field | Type | Null | Key | Default | Extra | 6 +-------+------------+------+-----+---------+-------+ 7 | id | int(11) | YES| | NULL | | 8 | name | varchar(20)| YES| | NULL | | 9 | state | tinyint(2) | NO | | 1 | | 10 +-------+-------------+------+-----+------+---+
02.查看当前的state状态
1 mysql> select * from test2; 2 3 +----+---------+-------+ 4 | id | name | state | 5 +----+---------+-------+ 6 | 1 | boy | 1 | 7 | 2 | girl | 1 | 8 | 3 | inca | 1 | 9 | 4 | zuma | 1 |
03.更新并修改state数据为0
1 mysql> update test2 set state=0 where name='boy'; 2 mysql> select * from test2; 3 4 +----+---------+-------+ 5 | id | name | state | 6 +----+---------+-------+ 7 | 1 | boy | 0 | 8 | 2 | girl | 1 | 9 | 3 | inca | 1 | 10 | 4 | zuma | 1 |
04.当查询时使用where条件，此时只会显示state=1的记录，其效果与删除类似
1 mysql> select * from test2 where state=1; 2 3 +----+---------+-------+ 4 | id | name | state | 5 +----+---------+-------+ 6 | 2 | girl | 1 | 7 | 3 | inca | 1 | 8 | 4 | zuma | 1 |
3.2.3.2 mysql安全模式(防止不加条件误删)
1 [root@db02 ~]# mysql -uroot -p123 -U 2 3 mysql> update test2 set name='oldgirl'; 4 5 ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
说明：mysql命令加上选项-U后，当发出没有where或limit关键字的update或delete时，mysql程序拒绝执行
3.2.4 DQL数据查询语言
语法：SELECT <字段1,字段2,...> FROM <表名> WHERE <表达式>
其中，select、from、where是不能随便改的，是关键字，支持大小写
查看用户的连接信息
select user,password,host from mysql.user; #模糊查询数据 select user,password,host from mysql.user where user like 'sy%';
查看test2表中的信息
select * from sys.test2; #查看所有信息 select id,name from sys.test2;
特殊信息查询
select id,name from test2 where id=2; select id,name from test2 where id>2 and id<4; #查看id大于2且小于4的记录 select id,name from test2 where name='sys'; select id,name from test2 order by id asc; #提取记录排序 select id,name from test2 order by limit 1,3; #显示跳过第一行后的三行(需要与排序配合使用)
注意：select必须同from搭配使用