当前位置：Gxlcms > 数据库问题 > MySQL

MySQL

时间：2021-07-01 10:21:17 帮助过：5人阅读

第1章数据库

2.3 数据库安装

MySQL家谱：

1、正宗后代

3.26 MySQL-5.0.xx到MySQL-5.1.xx的产品线 5.2 yum自带

正式中很少用了。

yum info mysql

yum grouplist

2*****、MySQL-5.4.xx-到MySQL-5.7.xx产品线 主流。

主流5.5 5.6 5.7

讲此产品线,5.6.34

3、MySQL-Cluster-6.0.xx-到MySQL-Cluster-7.5.xx产品线

类似 ORACLE RAC

2.4 数据库常见安装方法：

mysql安装方法:

1、yum/rpm 安装适合并发不大，企业内部的一些应用场景不能定制

2、二进制安装比较简单方便适合5.0-5.1和5.5-5.6系列，

解压即可

3、编译安装 5.5-5.6系列采用cmake编译方式*******常用

./cmake ，make， makeinstall

4、5.0产品线 configure ，make make install

5、利用源码编译安装，做成rpm包，然后在yum仓库 yum安装

建议：MySQL5.5及以上版本，安装方式机器数量少的话，推荐cmake编译安装数量多就用二进制免安装，如果数量特别大，可以选择定制rpm包的安装方式

讲5.6.34，cmake安装。

第3章单实例安装：

3.1 安装过程

1、 安装MySQL所需要的依赖包和编译软件（cmake）

[root@db02 ~]# yum install -y ncurses-devel libaio-devel

[root@db02 ~]# yum install -y cmake

[root@db02 ~]# rpm -qa ncurses-devel libaio-devel cmake

ncurses-devel-5.7-4.20090207.el6.x86_64

cmake-2.8.12.2-4.el6.x86_64

libaio-devel-0.3.107-10.el6.x86_64

2、 创建用户

[root@db02 ~]# useradd mysql -s /sbin/nologin -M

[root@db02 ~]# id mysql

uid=501(mysql) gid=501(mysql) groups=501(mysql)

3、上传源码安装包并解压

[root@db02 ~]# cd /home/oldboy/tools/

rz传入

[root@db02 tools]# tar xf mysql-5.6.34.tar.gz

4、编译、

[root@db02 tools]# cd mysql-5.5.32

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.34 \

-DMYSQL_DATADIR=/application/mysql-5.6.34/data \

-DMYSQL_UNIX_ADDR=/application/mysql-5.6.34/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_EXTRA_CHARSETS=all \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \

-DWITH_ZLIB=bundled \

-DWITH_SSL=bundled \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_EMBEDDED_SERVER=1 \

-DENABLE_DOWNLOADS=1 \

-DWITH_DEBUG=0

3.2 cmake编译参数

5、编译安装

make &&make install

提示：

#错误情况：磁盘满了，内存扩大会提高速度

#1)请提前加大VM虚拟机硬件的内存，这样编译时候会更快，最好设置2GB以上的内存。

#3)重视每个操作过程的输出，有错误要解决掉再继续，不能忽略掉操作中的错误（error）。

6、做软链接

ln -s /application/mysql-5.6.34/ /application/mysql

7、配置MySQL配置文件

cp support-files/my*.cnf /etc/my.cnf

8、初始化

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql

9、授权mysql用户

chown -R mysql.mysql /application/mysql/

10、复制mysql启动文件

cp support-files/mysql.server /etc/init.d/mysqld

11、授权mysql启动文件

chmod 700 /etc/init.d/mysqld

12、加入开机自启动

chkconfig mysqld on

chkconfig --list mysqld

13、启动mysql

/etc/init.d/mysqld start

netstat -lntup|grep 330

14、设置mysql全局环境变量

echo ‘PATH=/application/mysql/bin/:$PATH‘ >>/etc/profile

tail -1 /etc/profile

source /etc/profile

echo $PATH

mysql

重新初始化

##############################################################

#重新初始化

#rm -fr /application/mysql/data/*

#授权mysql用户

chmod 700 /etc/init.d/mysqld

chown -R mysql.mysql /application/mysql/

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql

[root@db02 mysql-5.6.34]# pwd

/home/oldboy/tools/mysql-5.6.34

cp support-files/my-default.cnf /etc/my.cnf

/etc/init.d/mysqld start

##############################################################

15、设置密码:

mysqladmin -u root password ‘oldboy123‘

3.2.1 清理用户及无用数据库(基本优化)

select user,host from mysql.user;

drop user ‘‘@‘db02‘;

drop user ‘‘@‘localhost‘;

drop user ‘root‘@‘db02‘;

drop user ‘root‘@‘::1‘;

select user,host from mysql.user;

drop database test;

show databases;

##################

3.3 排错

tail -100 /application/mysql/data/db02.err

常见错误

3.3.1 启动问题四：

常见问题1、

[root@mysql02 ~]# /etc/init.d/mysqld start

Starting MySQL.Logging to ‘/application/mysql-5.6.36/data/mysql02.err‘.

170618 18:59:33 mysqld_safe Directory ‘/application/mysql-5.6.36/tmp‘ for UNIX socket file don‘t exists.

ERROR! The server quit without updating PID file (/application/mysql-5.6.36/data/mysql02.pid).

解决方法：

[root@mysql02 ~]# mkdir /application/mysql/tmp

[root@mysql02 ~]# chown -R mysql.mysql /application/mysql/

[root@mysql02 ~]# /etc/init.d/mysqld start

Starting MySQL.Logging to ‘/application/mysql-5.6.36/data/mysql02.err‘.

. SUCCESS!

常见问题2、

故障:ERROR! The server quit without updating PID file

1.权限.chown -R mysql.mysql

2.killall mysqld

3.重新初始化.

4.运行1年了,出问题(非法关机或者关数据库).

第4章多实例介绍

4.1 什么是MySQL多实例？

在一台服务器上安装一套MySQL程序，起多个不同的端口，通过不同的端口提供服务，多实例配置用多个配置文件配置 server_id的区别端口的区别数据文件路径的区别

4.2 MySQL多实例的作用，

l ·有效利用服务器资源

当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务，且可以实现资源的逻辑隔离。

l 节约服务器资源

当公司资金紧张，但是数据库又需要各自尽量独立地提供服务，而且，需要主从复制等技术时，多实例就再好不过了。

MySQL多实例有它的好处，但也有其弊端，比如，会存在资源互相抢占的问题。

当某个数据库实例并发很高或有SQL慢查询时，整个实例会消耗大量的系统CPU、磁盘I/O等资源，导致服务器上的其他数据库实例提供服务的质量一起下降。这就相当于大家住在一个房子的不同卧室一样，早晨起来上班，都要刷牙、洗脸等，这样卫生间就会长期占用，其他人要等待一样。不同实例获取的资源是相对独立的，无法像虚拟化一样完全隔离。

4.3 MySQL多实例的生产应用场景

4.3.1 资金紧张型公司的选择

若公司资金紧张，公司业务访问量不太大，但又希望不同业务的数据库服务各自尽量独立地提供服务而互相不受影响，同时，还需要主从复制等技术提供备份或读写分离服务，那么，多实例就再好不过了。例如：可以通过3台服务器部署9~15个实例，交叉做主从复制、数据备份及读写分离，这样就可达到9~15台服务器每个只装一个数据库才有的效果。这里要强调的是，所谓的尽量独立是相对的。

4.3.2 并发访问不是特别大的业务

当公司业务访问量不太大的时候，服务器的资源基本上都浪费了，这时就很适合多实例的应用，如果对SQL语句的优化做得比较好，MySQL多实例会是一个很值得使用的技术，即使并发很大，合理分配好系统资源，搭配好服务，也不会有太大问题。

4.3.3 门户网站应用MySQL多实例场景

门户网站通常都会使用多实例，因为配置硬件好的服务器，可节省IDC机柜空间，同时，跑多实例也会减少硬件资源跑不满的浪费。比如，百度公司的很多数据库都是多实例，不过，一般是从库多实例，例如某部门中使用的IBM服务器为48核CPU，内存96GB，一台服务器跑3~4个实例；此外，新浪网使用的也是多实例，内存48GB左右。

4.4 多实例安装：

#####################################

多实例安装多实例与单实例只能用一个

1、安装MySQL所需要的依赖包和编译软件（cmake）

[root@db02 ~]# yum install -y ncurses-devel libaio-devel

[root@db02 ~]# yum install -y cmake

[root@db02 ~]# rpm -qa ncurses-devel libaio-devel cmake

ncurses-devel-5.7-4.20090207.el6.x86_64

cmake-2.8.12.2-4.el6.x86_64

libaio-devel-0.3.107-10.el6.x86_64

2、创建用户

[root@db02 ~]# useradd mysql -s /sbin/nologin -M

[root@db02 ~]# id mysql

uid=501(mysql) gid=501(mysql) groups=501(mysql)

3、上传源码安装包并解压

[root@db02 ~]# cd /home/oldboy/tools/

rz传入

wget -q http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.34.tar.gz

[root@db02 tools]# tar xf mysql-5.6.34.tar.gz

4、编译、

[root@db02 tools]# cd mysql-5.5.32

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.34 \

-DMYSQL_DATADIR=/application/mysql-5.6.34/data \

-DMYSQL_UNIX_ADDR=/application/mysql-5.6.34/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_EXTRA_CHARSETS=all \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \

-DWITH_ZLIB=bundled \

-DWITH_SSL=bundled \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_EMBEDDED_SERVER=1 \

-DENABLE_DOWNLOADS=1 \

-DWITH_DEBUG=0

5、编译安装

make &&make install

提示：

#错误情况：磁盘满了，内存扩大会提高速度

#1)请提前加大VM虚拟机硬件的内存，这样编译时候会更快，最好设置2GB以上的内存。

#3)重视每个操作过程的输出，有错误要解决掉再继续，不能忽略掉操作中的错误（error）。

6、做软链接

ln -s /application/mysql-5.6.34/ /application/mysql

若安装了mysql单实例关闭单实例

[root@db02 mysql-5.6.34]# /etc/init.d/mysqld stop 关闭单实例

Shutting down MySQL.. SUCCESS!

[root@db02 mysql-5.6.34]# chkconfig mysqld off 关闭单实例

7、把data.zip放入/

unzip data.zip 解压配置文件包

[root@db02 /]# tree /data/

/data/

├── 3306

│?? ├── my.cnf

│?? └── mysql

└── 3307

├── my.cnf

└── mysql

8、创建多实例目录

mkdir /data//{3306,,3307}/data -p

9、授权多实例目录

chown -R mysql.mysql /data/

10、授权启动文件执行权限

find /data/ -name mysql|xargs chmod 700

find /data/ -name mysql|xargs ls -l

11、初始化

cd /application/mysql/scripts

./mysql_install_db --defaults-file=/data/3306/my.cnf --basedir=/application/mysql/ --datadir=/data/3306/data --user=mysql

./mysql_install_db --defaults-file=/data/3307/my.cnf --basedir=/application/mysql/ --datadir=/data/3307/data --user=mysql

12、设置全局环境变量

echo ‘PATH=/application/mysql/bin/:$PATH‘ >>/etc/profile

tail -1 /etc/profile

source /etc/profile

13、启动多实例MySQL

[root@db02 tools]# /data/3306/mysql start

Starting MySQL...

[root@db02 tools]# /data/3307/mysql start

Starting MySQL...

14、登录多实例数据库

[root@db02 tools]# mysql -S /data/3306/mysql.sock

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.34 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

15、给mysql设置密码

[root@db02 scripts]# mysqladmin password oldboy123 -S /data/3306/mysql.sock

[root@db02 scripts]# mysqladmin password oldboy123 -S /data/3307/mysql.sock

[root@db02 scripts]# mysqladmin password oldboy123 -S /data/3308/mysql.sock

16、登录有密码的实例

[root@db02 scripts]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock

17、#有密码改密码

[root@db02 scripts]# mysqladmin -poldboy456 password oldboy123 -S /data/3307/mysql.sock

4.5 现场作业：请你增加一个3308实例。

mkdir -p /data/3308/data

\cp /data/3306/my.cnf /data/3308/

\cp /data/3306/mysql /data/3308/

sed -i ‘s/3306/3308/g‘ /data/3308/my.cnf

sed -i ‘s/server-id = 6/server-id = 8/g‘ /data/3308/my.cnf

sed -i ‘s/3306/3308/g‘ /data/3308/mysql

chown -R mysql:mysql /data/3308

chmod 700 /data/3308/mysql

cd /application/mysql/scripts

./mysql_install_db --defaults-file=/data/3308/my.cnf --datadir=/data/3308/data --basedir=/application/mysql --user=mysql

chown -R mysql:mysql /data/3308

egrep "server-id|log-bin" /data/3308/my.cnf

/data/3308/mysql start

sleep 5

netstat -lnt|grep 3308

第5章 MySQL安全优化

不要给数据库外网IP

第6章 mysql启动基本原理

6.1 MySQL单实例启动基本原理说明

/etc/init.d/mysqld是一个shell启动脚本，启动后最终会调用mysqld_safe脚本，最后调用mysqld主程序启动mysql，如下，/etc/init.d/mysqld脚本中调用mysqld_safe的程序。

/etc/init.d/mysqld启动：

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &

/etc/init.d/mysqld 停止：

if (kill -0 $mysqld_pid 2>/dev/null)

then

echo $echo_n "Shutting down MySQL"

kill $mysqld_pid

6.2 MySQL单实例服务启动小结

1）使用/etc/init.d/mysqld start 命令启动数据库的本质就相当于执行mysqld_safe --user=mysql &命令

2）后文老男孩自己开发shell脚本启动多实例数据库时就是调用的mysqld_safe程序。

3）在找回MySQL root密码时，也会使用musqld_safe程序并带忽略授权表的参数启动来找回root密码

请描述MySQL启动与关闭的原理?
mysql 使用start启动,调用mysqld_safe指向配置文件来管理mysqld的进程。
mysql 使用stop关闭原理，是利用KIll的方式杀掉mysqld_pid进程号。

6.3 关闭MySQL数据库的方法选择顺序如下：

第一种、最先使用MySQL自带的管理脚本，实例如下：

/etc/init.d/mysqld stop

第二种：为mysqladmin管理方法：

mysqladmin -uroot -poldboy123 shutdown #===这个命令最大的障碍就是必须事先知道密码。

第三种：为利用系统进程管理命令关闭MySQL。

kill pid ##==这里的pid为数据库服务对应的进程号、

killall mysqld ##==这里的mysqld是数据库服务对应的进程名字。

pkill mysqld ##==这里的mysqld是数据库服务对应的进程名字

可通过如下地址查看生产高并发环境野蛮粗鲁杀死数据库导致故障企业案例：

http://oldboy.blog.51cto.com/2561410/1431161

http://oldboy.blog.51cto.com/2561410/1431172

6.4 作业:自己写一个单实例mysql启动脚本,并且能够加入chkconfig管理.

6.5 多实例MySQL 启动与关闭方法示例

下面来看一下多实例MySQL启动与关闭方法的原理

启动3306实例命令服务的命令为/data/3306/mysql start 实际上就是mysql_safe加上不同的实例配置文件参数启动实例如下：

mysqld_safe --defaults-file=/data/3306/my.cnf >/dev/null 2>&1 &

停止3306实例命令为/data/3306/mysql stop 实际上就是使用的mysqladmin命令的方法

mysqladmin -u root -podlboy123 -S /data/3306/mysql.sock shutdown

也可以用kill的方式

第7章 MySQL登录、密码设置、查询帮助

7.1 MySQL登录优化

登录安全优化：/etc/my.cnf

[client]

user=root

password=oldboy123

设置:600

chmod 600 /etc/my.cnf

[root@db02 ~]# mysql 单实例

[root@db02 ~]# mysql -S /data/3306/mysql.sock 多实例

[root@db02 ~]# HISTCONTROL=ignorespace 忽略开头空格的记录可以加入环境变量

[root@db02 ~]# mysql -uroot -poldboy -S /data/3306/mysql.sock 安全登录加空格开头

7.2 查看MYSQL命令帮助

man linux命令及配置文件\函数等.

help linux内置命令

mysql帮助:

help或?

到底.

#设置MySQL的提示符

mysql> prompt \u@oldboy \r:\m:\s-->

PROMPT set to ‘\u@oldboy \r:\m:\s-->‘

root@oldboy 11:41:12-->

7.3 设置及修改MySQL root用户密码

方法1：

单实例修改：mysqladmin -uroot -poldboy123 password oldboy

多实例修改：mysqladmin -uroot -poldboy -S /data/3306/mysql.sock password oldboy123

方法二：

mysql登录后修改

mysql> update mysql.user set password=password(‘1234‘) where user=‘root‘ and host=‘localhost‘;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

7.4 单实例找回MySQL root用户密码（面试题）

单实例：

忽略授权表启动：

单实例：mysqld_safe --skip-grant-table --user=mysql &

多实例：mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-table &

直接mysql 命令登录

修改密码：update mysql.user set password=PASSWORD("oldboy1234") where user=‘root‘ and host=‘localhost‘;

#刷新flush privileges;

#成功登录mysql -uroot -poldboy1234

#重启MySQL

pkill mysql 杀掉MySQL

/etc/init.d/mysqld start 正常启动

mysql -uroot -poldboy1234 登录成功

###############################################

7.5 多实例找回MySQL-root密码丢失############

###############################################

#跳过授权表不验证启动

[root@db02 ~]# mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-table &

[root@db02 ~]# mysql -uroot -S /data/3306/mysql.sock 登录

#修改密码

mysql> update mysql.user set password=PASSWORD("oldboy1234") where user=‘root‘ and host=‘localhost‘;

Query OK, 1 row affected (0.12 sec)

Rows matched: 1 Changed: 1 Warnings: 0

#刷新

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

#成功登录

[root@db02 ~]# mysql -uroot -poldboy1234 -S /data/3306/mysql.sock

#重启MySQL

[root@db02 ~]# mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock shutdown

[root@db02 ~]# mysqld_safe --defaults-file=/data/3306/my.cnf &

[root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock 登录

7.6 小结

第8章 SQL语句DDL DCL DML

DDL（Data Definition Language）——数据定义语言（create,alter,drop），管理基础数据，例如：库，表 #←运维要熟练、开发也要熟练

DCL（Data Control Language）——数据控制语言（grant，revoke，commit，rollback），用户授权，权限回收，数据提交回滚等 #←运维要熟练

DML（Data Manipulation Language）——数据操作语言（select，insert，delete，update），针对数据库里的表的数据进行操作，记录 #←开发要熟练，运维要了解

SQL分类:

DDL 数据定义语言管理库和表 create,drop,alter等. *****熟练

DCL 数据控制语言用户管理授权 grant,revoke,commit;rollback. *****熟练

DMC 数据操作语言针对表里的数据 insert,delete,update,select *****熟悉\开发熟练.

8.1 SQL语句基本操作

8.1.1 查看数据库:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| oldboy |

| performance_schema |

+--------------------+

4 rows in set (0.00 sec)

8.1.2 创建数据库

mysql> create database linjie;

Query OK, 1 row affected (0.01 sec)

8.1.3 指定字符集建库

CREATE DATABASE db_name CHARACTER SET charset_name COLLATE collation_name

CREATE DATABASE oldgirl CHARACTER SET gbk COLLATE gbk_chinese_ci;

show character set;找字符集.

mysql> create database linjie character set gbk COLLATE gbk_chinese_ci;

Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

8.1.4 改库的字符集

mysql> alter database linjie character set utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

mysql> show create database linjie\G

*************************** 1. row ***************************

Database: linjie

Create Database: CREATE DATABASE `linjie` /*!40100 DEFAULT CHARACTER SET utf8 */

1 row in set (0.00 sec)

8.1.5 查看建库语句

mysql> show create database mysql\G

*************************** 1. row ***************************

Database: mysql

Create Database: CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */

1 row in set (0.00 sec)

8.1.6 删库

mysql> drop database linjie;

Query OK, 0 rows affected (0.00 sec)

8.1.7 切库

mysql> use oldboy;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

8.1.8 查看当前所在库

mysql> select database();

+------------+

| database() |

+------------+

| oldboy |

+------------+

1 row in set (0.00 sec)

8.1.9 查看库里的表

mysql> show tables;

+------------------+

| Tables_in_oldboy |

+------------------+

| student |

| test |

+------------------+

2 rows in set (0.00 sec)

8.2 DDL&&DCL语句之管理用户

8.2.1 查看当前数据库的用户列表

mysql> select user,host from mysql.user;

+--------+--------------------------+

| user | host |

+--------+--------------------------+

| oldboy | % |

| root | 127.0.0.1 |###数据核心保留管理员用户

| oldboy | 172.16.1.% |

| oldboy | 172.16.1.0/255.255.255.0 |

| oldboy | localhost |

| root | localhost | ###数据库核心保留管理员用户

+--------+--------------------------+

6 rows in set (0.00 sec)

8.2.2 创建用户

CREATE	USER	‘用户‘@‘主机‘	IDENTIFIED BY ‘密码‘
动作（创建）	对象（用户）	用户和从哪个主机登录	设定密码

现在创建一个oldboy用户，只允许本地主机登录，密码是oldboy123.

CREATE USER ‘用户‘@‘主机‘ IDENTIFIED BY ‘密码‘;

create user ‘oldboy‘@‘locahost‘ identified by ‘oldboy123‘; #只能连接

mysql> create user linjie@‘localhost‘ identified by ‘123456‘;

Query OK, 0 rows affected (0.00 sec)

企业里创建用户一般是授权一个内网网段登录，最常见的网段写法有两种。

方法1：172.16.1.%（%为通配符，匹配所有内容）。

方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24，是个小遗憾。

8.2.3 查看用户对应的权限

mysql> show grants for linjie@‘localhost‘;

+---------------------------------------------------------------------------------------------------------------+

| Grants for linjie@localhost |

+---------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO ‘linjie‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ |

+---------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show grants for linjie@‘localhost‘\G

*************************** 1. row ***************************

Grants for linjie@localhost: GRANT USAGE ON *.* TO ‘linjie‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘

1 row in set (0.00 sec)

8.2.4 删除用户

drop user ‘user‘@‘主机域‘

注意引号，可以是单或双引号，规范是最好加单引号。

drop	user	‘user‘@‘主机域‘
动作（删除）	对象（用户）	具体用户

删除上述中的oldboy用户：

mysql> select user,host from mysql.user where user=oldboy‘; ###删除前检查

+------+-----------+

| user | host |

+------+-----------+

| oldboy | localhost |

+------+-----------+

1 row in set (0.01 sec)

mysql> drop user oldboy@localhost;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user where user=‘oldboy‘;

Empty set (0.00 sec)

8.2.5 特殊的删除方法：

mysql> delete from mysql.user where user=‘bbs‘ and host=‘172.16.1.%‘;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

8.2.6 给用户授权

8.2.6.1 grant命令语法

1、通过在mysql中输入“help grant”得到如下帮助信息。

……

CREATE USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘mypass‘;

GRANT ALL ON db1.* TO ‘jeffrey‘@‘localhost‘;

GRANT SELECT ON db2.invoice TO ‘jeffrey‘@‘localhost‘;

GRANT USAGE ON *.* TO ‘jeffrey‘@‘localhost‘ WITH MAX_QUERIES_PER_HOUR 90;

……

通过查看grant的命令帮助，可以很容易的找到创建用户并授权的例子。

2、运维人员比较常用的创建用户的方法是，使用grant命令在创建用户的同时进行权限授权。具体授权例子为：

grant all privileges on dbname.* to username@localhost identified by ‘密码‘;

##前面的引号可以没有，但是密码一定要引起来

grant	all	on dbname.*	to username@localhost	identified by ‘passwd‘
授权命令	对应权限	目标：库和表	用户名和客户端主机	用户密码

mysql> grant all on kkk.* to www@localhost identified by ‘123456‘;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user where user=‘www‘;

+------+-----------+

| user | host |

+------+-----------+

| www | localhost |

+------+-----------+

1 row in set (0.01 sec)

3、操作案例1：创建test用户，对oldboy库具备所有权限，允许从localhost主机登录管理数据库，密码为123456.

实现上述操作的具体命令为：

grant all privileges on oldboy.* to ‘test‘@‘localhost‘ identified by ‘123456‘;

操作过程如下：

查看当前数据库用户情况，然后执行对应命令授权如下：

mysql> grant all privileges on oldboy.* to ‘test‘@‘localhost‘ identified by ‘123456‘;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user where user=‘test‘;

+------+-----------+

| user | host |

+------+-----------+

| test | localhost |

+------+-----------+

1 row in set (0.00 sec)

操作案例2：创建一个权限和root一样大的system用户（with grant option是下面命令的重点）。

mysql> show grants for root@localhost; ###先查看root用户的权限

+----------------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@localhost |

+----------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515‘ WITH GRANT OPTION |

| GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION |

+----------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> grant all on *.* to ‘system‘@‘localhost‘ identified by ‘123456‘ with grant option; ##创建

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for system@localhost;

+------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for system@localhost |

+------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO ‘system‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ WITH GRANT OPTION |

+------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

8.2.7 创建用户同时授权

grant all on *.* to oldgirl@‘172.16.1.%‘ identified by ‘oldgirl123‘;

grant all on *.* to oldgirl@‘172.16.1.0/255.255.255.0‘ identified by ‘oldgirl123‘;

flush privileges;

mysql> show grants for oldgirl@‘172.16.1.0/255.255.255.0‘\G

*************************** 1. row ***************************

Grants for oldgirl@172.16.1.0/255.255.255.0: GRANT ALL PRIVILEGES ON *.* TO ‘oldgirl‘@‘172.16.1.0/255.255.255.0‘ IDENTIFIED BY PASSWORD ‘*2CADADD54086D5EB4C9F10E0430084D7F179885C‘

1 row in set (0.00 sec)

8.2.8 回收权限

mysql> revoke insert on *.* from oldboy@‘localhost‘;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for oldboy@‘localhost‘\G

*************************** 1. row ***************************

Grants for oldboy@localhost: GRANT SELECT, 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 ON *.* TO ‘oldboy‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘

*************************** 2. row ***************************

Grants for oldboy@localhost: GRANT ALL PRIVILEGES ON `oldboy`.* TO ‘oldboy‘@‘localhost‘

2 rows in set (0.00 sec)

8.2.9 博客、CMS、bbs等产品的数据库授权：

对于Web连接数据库的用户授权尽量采用最小化原则，但是很多开源软件都是Web界面安装，因此，在安装期间除了select,insert,update,delete4个权限外，有可能还需要create，drop等比较危险的权限，因此需要建库、建表，因此授权例子如下。

mysql> grant select,insert,update,delete,create,drop on blog.* to blog@‘172.16.1.%‘ identified by ‘123456‘;

Query OK, 0 rows affected (0.00 sec)

生成数据库、表后，可以使用revoke命令收回create、drop授权：

mysql> revoke create,drop on blog.* from blog@‘172.16.1.%‘;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for blog@‘172.16.1.%‘;

+--------------------------------------------------------------------------------------------------------------+

| Grants for blog@172.16.1.% |

+--------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO ‘blog‘@‘172.16.1.%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO ‘blog‘@‘172.16.1.%‘ |

+--------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

8.2.10 生产场景具体授权命令为：

a.主库授权的命令：

grant select,insert,update,delete on blog.* to ‘blog‘@‘172.16.1.%‘ identified by ‘123456‘;

b.从库授权用户的命令：

grant select on blog.* to ‘blog‘@‘172.16.1.%‘ identified by ‘123456‘;

当然从库除了做select的授权外，还可以加read-only等只读参数，严格控制web用户写从库。

8.2.11 授权不规范导致的生产血案

运维人员授权用户all权限了，导致开发通过该用户自行改了表结构（字段），最后造成服务处问题，最后黑锅甩在了运维人员身上。

运维人员排查了半天没结果，最后对比表结构（把生产数据和备份的数据比对），发现了问题，最后告诉开发，把字段改回去，服务就好了。

启发：生产场景尽量不要给开发select以为的权限，用于网站的连接账号不要授权select,insert,delete,update以外的权限，对别人的“仁慈”，就是对自己的岗位和公司最大的背叛。

8.3 DML语句之管理表中的数据

8.3.1 往表中插入数据语法

drop table test;

CREATE TABLE test (

id int(4) NOT NULL AUTO_INCREMENT,

name char(20) NOT NULL,

PRIMARY KEY (id)

) ;

（1）命令语法：

insert into <表名> [(字段名1[,..<字段名n>])] values(值1)[,(值n)]

（2）新建一个简单的测试表test，语句如下：

mysql> use oldboy

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> CREATE TABLE test (

-> id int(4) NOT NULL AUTO_INCREMENT,

-> name char(20) NOT NULL,

-> PRIMARY KEY (id)

-> ) ;

Query OK, 0 rows affected (0.32 sec)

mysql> desc test;

+-------+----------+------+-----+---------+----------------+

+-------+----------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

+-------+----------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

8.3.2 往表中插入数据的不同的语法例子：

8.3.2.1 按规矩指定所有列名，并且每列都插入值

mysql> insert into test(id,name) values(1,‘oldboy‘);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+--------+

| id | name |

+----+--------+

| 1 | oldboy |

+----+--------+

1 row in set (0.00 sec)

8.3.2.2 由于id列为自增的，所以，可以只在name列插入值

mysql> insert into test(name) values(‘oldgirl‘);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

+----+---------+

2 rows in set (0.00 sec)

在建表的时候就确认了

#查看表结构

mysql> desc student;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

+-------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

8.3.2.3 如果不指定列，就要按规矩为每列都插入恰当的值。

mysql> insert into test values(3,‘oldboy‘);

Query OK, 1 row affected (0.05 sec)

mysql> select * from test;

+----+--------+

| id | name |

+----+--------+

| 1 | linjie |

| 2 | oldboy |

| 3 | oldboy |

+----+--------+

3 rows in set (0.00 sec)

8.3.2.4 批量插入数据的方法：

mysql> insert into test values(4,‘linjie‘),(5,‘lanyezi‘);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test;

+----+---------+

| id | name |

+----+---------+

| 1 | linjie |

| 2 | oldboy |

| 3 | oldboy |

| 4 | linjie |

| 5 | lanyezi |

+----+---------+

5 rows in set (0.00 sec)

8.3.2.5 插入全部数据

mysql> delete from test;

Query OK, 5 rows affected (0.07 sec)

mysql> select * from test;

Empty set (0.00 sec)

mysql> insert into test values(1,‘oldboy‘),(2,‘oldgirl‘),(3,‘inca‘),(4,‘zuma‘),(5,‘kaka‘);

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from test;

+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

+----+---------+

5 rows in set (0.00 sec)

8.3.2.6 测试完毕，退出数据库，然后备份上述数据，留着备用。

[root@db02 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy >/opt/oldboy.sql

Warning: Using a password on the command line interface can be insecure.

[root@db02 ~]# ll /opt/oldboy.sql

-rw-r--r--. 1 root root 2051 2017-06-21 14:58 /opt/oldboy.sql

[root@db02 ~]# egrep -v ‘#|\/|^$|--‘ /opt/oldboy.sql

USE `oldboy`;

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

LOCK TABLES `test` WRITE;

INSERT INTO `test` VALUES (1,‘oldboy‘),(2,‘oldgirl‘),(3,‘inca‘),(4,‘zuma‘),(5,‘kaka‘);

UNLOCK TABLES;

mysql> alter table student change id id int primary key auto_increment; 添加主键

unique 唯一的独一无二的。

8.3.3 修改表中的数据

8.3.3.1 修改表中的数据

update test set id=6 where name=‘kaka‘;

mysql> update test set name=‘bingbing‘ where id=3;

Query OK, 1 row affected (0.12 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;

+----+----------+

| id | name |

+----+----------+

| 1 | linjie |

| 2 | oldboy |

| 3 | bingbing |

| 4 | linjie |

| 5 | lanyezi |

+----+----------+

5 rows in set (0.00 sec)

8.3.3.2 不指定条件误操作

mysql> update test set name=‘linjie‘;

Query OK, 5 rows affected (0.15 sec)

Rows matched: 5 Changed: 5 Warnings: 0

mysql> select * from test;

+----+--------+

| id | name |

+----+--------+

| 1 | linjie |

| 2 | linjie |

| 3 | linjie |

| 4 | linjie |

| 6 | linjie |

+----+--------+

5 rows in set (0.01 sec)

-U禁止update操作

防止不加条件误删

[root@db02 ~]# mysql -U

mysql> update test set name=‘xiaoting‘;

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>

至于防止误操作导致上述数据库故障案例的方法之一，请读者到老男孩的博客http://oldboy.blog.51cto.com/2561410/1321061索引列的创建及生效条件

8.3.4 查询表中的数据

8.3.4.1 命令语法：

select<字段1，字段2，...> from <表名> where <表达式>

其中，select、from、where是不能随便改的，是关键字，支持大小写。

mysql> select user,host from mysql.user;

+------+-----------+

| user | host |

+------+-----------+

| root | 127.0.0.1 |

| root | ::1 |

| | db02 |

| root | db02 |

| | localhost |

| root | localhost |

+------+-----------+

6 rows in set (0.00 sec)

8.3.4.2 查看表test中所有数据

mysql> select * from test;

+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

+----+---------+

5 rows in set (0.00 sec)

8.3.4.3 倒叙查询

mysql> select * from test where id>1 order by id desc;

+----+---------+

| id | name |

+----+---------+

| 5 | kaka |

| 4 | zuma |

| 3 | inca |

| 2 | oldgirl |

+----+---------+

4 rows in set (0.12 sec)

8.3.4.4 升序查询

mysql> select * from test where id>1 order by id asc;

+----+---------+

| id | name |

+----+---------+

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

+----+---------+

4 rows in set (0.00 sec)

8.3.5 删除表中的数据

8.3.5.1 命令语法：

delete from 表名 where 表达式

8.3.5.2 删除表test中编号为1 的记录。

mysql> delete from test where id=1;

Query OK,

MySQL

第1章 数据库

2.3 数据库安装

2.4 数据库常见安装方法：

第3章 单实例安装：

3.1 安装过程

3.2 cmake编译参数

3.2.1 清理用户及无用数据库(基本优化)

3.3 排错

3.3.1 启动问题四：

第4章 多实例介绍

4.1 什么是MySQL多实例？

4.2 MySQL多实例的作用，

4.3 MySQL多实例的生产应用场景

4.3.1 资金紧张型公司的选择

4.3.2 并发访问不是特别大的业务

4.3.3 门户网站应用MySQL多实例场景

4.4 多实例安装：

4.5 现场作业：请你增加一个3308实例。

第5章 MySQL安全优化

第6章 mysql启动基本原理

6.1 MySQL单实例启动基本原理说明

6.2 MySQL单实例服务启动小结

6.3 关闭MySQL数据库的方法选择顺序如下：

6.4 作业:自己写一个单实例mysql启动脚本,并且能够加入chkconfig管理.

6.5 多实例MySQL 启动与关闭方法示例

第7章 MySQL登录、密码设置、查询帮助

7.1 MySQL登录优化

7.2 查看MYSQL命令帮助

7.3 设置及修改MySQL root用户密码

7.4 单实例找回MySQL root用户密码（面试题）

7.5 多实例找回MySQL-root密码丢失############

7.6 小结

第8章 SQL语句DDL DCL DML

8.1 SQL语句基本操作

8.1.1 查看数据库:

8.1.2 创建数据库

8.1.3 指定字符集建库

8.1.4 改库的字符集

8.1.5 查看建库语句

8.1.6 删库

8.1.7 切库

8.1.8 查看当前所在库

8.1.9 查看库里的表

8.2 DDL&&DCL语句之管理用户

8.2.1 查看当前数据库的用户列表

8.2.2 创建用户

8.2.3 查看用户对应的权限

8.2.4 删除用户

8.2.5 特殊的删除方法：

8.2.6 给用户授权

8.2.6.1 grant命令语法

8.2.7 创建用户同时授权

8.2.8 回收权限

8.2.9 博客、CMS、bbs等产品的数据库授权：

8.2.10 生产场景具体授权命令为：

8.2.11 授权不规范导致的生产血案

8.3 DML语句之管理表中的数据

8.3.1 往表中插入数据语法

8.3.2 往表中插入数据的不同的语法例子：

8.3.2.1 按规矩指定所有列名，并且每列都插入值

8.3.2.2 由于id列为自增的，所以，可以只在name列插入值

8.3.2.3 如果不指定列，就要按规矩为每列都插入恰当的值。

8.3.2.4 批量插入数据的方法：

8.3.2.5 插入全部数据

8.3.2.6 测试完毕，退出数据库，然后备份上述数据，留着备用。

8.3.3 修改表中的数据

8.3.3.1 修改表中的数据

8.3.3.2 不指定条件误操作

8.3.4 查询表中的数据

8.3.4.1 命令语法：

8.3.4.2 查看表test中所有数据

8.3.4.3 倒叙查询

8.3.4.4 升序查询

8.3.5 删除表中的数据

8.3.5.1 命令语法：

8.3.5.2 删除表test中编号为1 的记录。

人气教程排行

第1章数据库

第3章单实例安装：

第4章多实例介绍