MySQL复制，约束条件，查询与安全控制

MySQL之复制

复制表

我有一个表
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+mysql> select * from student;
+------+-------+--------+------+----------+-------+
| id   | name  | sec    | age  | address  | phone |
+------+-------+--------+------+----------+-------+
|    2 | jack  | male   |   20 | shanghai |   110 |
|    3 | alice | female |   20 | nanjing  |   110 |
+------+-------+--------+------+----------+-------+复制这个表的id，name，phone。
mysql> create table ml(select id,name,phone from student);mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| ml               |查看表的内容及结构：
mysql> select * from ml;
+------+-------+-------+
| id   | name  | phone |
+------+-------+-------+
|    2 | jack  |   110 |
|    3 | alice |   110 |
+------+-------+-------+
2 rows in set (0.00 sec)mysql> desc ml;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| phone | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

语法： create table 新表名（select   *  from  要复制的表名）

*可以是要复制表里面的字段

复制表的结构

mysql> create table zx(select * from ml where 4=5);
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| ml               |
|      
| zx               |
+------------------+mysql> select * from zx;
Empty set (0.00 sec) --------由于执行语句where 4=5，不成立，因此只复制表结构 不复制表数据mysql> desc zx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| phone | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table bs(select * from ml where 2=2);
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| bs               |
| ml               |
| student          |
| zx               |
+------------------+mysql> select * from bs;
+------+-------+-------+
| id   | name  | phone |
+------+-------+-------+
|    2 | jack  |   110 |
|    3 | alice |   110 |
+------+-------+-------+
2 rows in set (0.00 sec)mysql> desc bs;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| phone | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

语法：create table 新表名（select  *   from  复制的表名   where  n=m）

如果n等于m成立，那么复制表的结构与数据

如果n等于m不成立，那么只复制表的结构

MySQL的约束条件

约束条件	说明
null	标识是否允许为空，默认为NULL。
not null	标识该字段不能为空，可以修改。
UNIQUE KEY  (UK)	标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
default	为该字段设置默认值
UNSIGNED	无符号，正数
primary key   （PK）	标识该字段为该表的主键，可以唯一的标识记录，不可以为空
auto_increment	标识该字段的值自动增长（整数类型，而且为主键）
FOREIGN KEY (FK)	标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联
enum	二选一
set	多选

实验

mysql> use zyq;   #进入zyq库mysql>   create table idol(id int primary key auto_increment not null,name varchar(40) not null comment 'idol姓名',gender enum('男','女') not null default '男',hobby set('唱歌','跳舞','打麻将') not null);
Query OK, 0 rows affected (0.01 sec)mysql> desc idol;
+--------+------------------------------------+------+-----+---------+----------------+
| Field  | Type                               | Null | Key | Default | Extra          |
+--------+------------------------------------+------+-----+---------+----------------+
| id     | int(11)                            | NO   | PRI | NULL    | auto_increment |
| name   | varchar(40)                        | NO   |     | NULL    |                |
| gender | enum('男','女')                      NO   |     | 男      |                |
| hobby  | set('唱歌','跳舞','打麻将')          | NO   |     | NULL    |                |
+--------+------------------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

primary key 是标识该字段为该表的主键，可以唯一的标识记录，不可以为空

auto_increment  标识该字段的值自动增长（整数类型，而且为主键），每张表只能一个字段为自增

not null  字段不可以为空，可以修改

comment 指定注释

default 默认

set为多选

enum为单选

对于set多选，最后添加数据时候， insert into 表名（字段） values（'  ,  '）在一个引号里写即可，中间用逗号隔开。

为表idol添加数据

mysql> insert into idol(id,name,gender,hobby) values(1,'tom','男','唱歌');
Query OK, 1 row affected (0.00 sec)mysql> select * from idol;
+----+------+--------+--------+
| id | name | gender | hobby  |
+----+------+--------+--------+
|  1 | tom  | 男     | 唱歌   |
+----+------+--------+--------+mysql> insert into idol(id,name,gender,hobby) values(2,'jack','女','唱歌,跳舞');
Query OK, 1 row affected (0.00 sec)mysql> select * from idol;
+----+------+--------+---------------+
| id | name | gender | hobby         |
+----+------+--------+---------------+
|  1 | tom  | 男     | 唱歌          |
|  2 | jack | 女     | 唱歌,跳舞     |
+----+------+--------+---------------+
2 rows in set (0.00 sec)

我们之前创建表的时候 设置约束条件 id可以为自增，性别gender默认为男，那么代表我们添加数据的时候 不定义字段id，gender 那么出来的表数据也会有id，gender

mysql> insert into idol(name,hobby) values('cxk','唱歌');
Query OK, 1 row affected (0.00 sec)mysql> select * from idol;
+----+------+--------+---------------+
| id | name | gender | hobby         |
+----+------+--------+---------------+
|  1 | tom  | 男     | 唱歌          |
|  2 | jack | 女     | 唱歌,跳舞     |
|  3 | cxk  | 男     | 唱歌          |
+----+------+--------+---------------+
3 rows in set (0.00 sec)

MySQL之单表查询

准备测试company 测试表employee5

mysql> CREATE TABLE company.employee5(id int primary key AUTO_INCREMENT not null,name varchar(30) not null,sex enum('male','female') default 'male' not null,hire_date date not null,post varchar(50) not null,job_description varchar(100),salary double(15,2) not null,office int,dep_id int);mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','male','20180202','instructor','teach',5000,501,100),('tom','male','20180203','instructor','teach',5500,501,100),('robin','male','20180202','instructor','teach',8000,501,100),('alice','female','20180202','instructor','teach',7200,501,100),('tianyun','male','20180202','hr','hrcc',600,502,101),('harry','male','20180202','hr',NULL,6000,502,101),('emma','female','20180206','sale','salecc',20000,503,102),('christine','female','20180205','sale','salecc',2200,503,102),('zhuzhu','male','20180205','sale',NULL,2200,503,102),('gougou','male','20180205','sale','',2200,503,102);

结果

mysql> select * from employee5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
|  1 | jack      | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
|  2 | tom       | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
|  3 | robin     | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
|  4 | alice     | female | 2019-03-05 | instructor | teach           |  7200.00 |    501 |    100 |
|  5 | tianyun   | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
|  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
|  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
| 10 | gougou    | male   | 2017-02-02 | sale       |                 |  2200.00 |    503 |    102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)

查询：

简单查询

查询表中一共有多少条数据
mysql> select count(*) from employee5;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

条件查询

mysql> select * from employee5;
mysql> select id ,name,sex from employee5;

避免重复，去重查询： distinct

语法： select distinct 字段 from 表名

查询去重之后的性别
mysql> select distinct sex from employee5;
+--------+
| sex    |
+--------+
| male   |
| female |
+--------+
2 rows in set (0.00 sec)这是不去重的查询性别mysql> select sex from  employee5;
+--------+
| sex    |
+--------+
| male   |
| male   |
| male   |
| female |
| male   |
| male   |
| female |
| female |
| male   |
| male   |
+--------+
10 rows in set (0.00 sec)

通过计算查询

查询该表中每个人的工资
mysql> select name,salary from employee5;
+-----------+----------+
| name      | salary   |
+-----------+----------+
| jack      |  5000.00 |
| tom       |  5500.00 |
| robin     |  8000.00 |
| alice     |  7200.00 |
| tianyun   |   600.00 |
| harry     |  6000.00 |
| emma      | 20000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
+-----------+----------+
10 rows in set (0.00 sec)假设表中工资是月薪，一年假设14薪。查询该表中每个人一年的工资
mysql> select name,salary,salary*14 from employee5;
+-----------+----------+-----------+
| name      | salary   | salary*14 |
+-----------+----------+-----------+
| jack      |  5000.00 |  70000.00 |
| tom       |  5500.00 |  77000.00 |
| robin     |  8000.00 | 112000.00 |
| alice     |  7200.00 | 100800.00 |
| tianyun   |   600.00 |   8400.00 |
| harry     |  6000.00 |  84000.00 |
| emma      | 20000.00 | 280000.00 |
| christine |  2200.00 |  30800.00 |
| zhuzhu    |  2200.00 |  30800.00 |
| gougou    |  2200.00 |  30800.00 |
+-----------+----------+-----------+
10 rows in set (0.00 sec)

支持四则运算查询

支持起别名 语法： select  字段名，字段名  as 新字段名 from 表名

也可以起别名，比如查询一年的工资，起名为yearsalary 呈现出来
mysql> select name,salary*14 as yearsalary from employee5;
+-----------+------------+
| name      | yearsalary |
+-----------+------------+
| jack      |   70000.00 |
| tom       |   77000.00 |
| robin     |  112000.00 |
| alice     |  100800.00 |
| tianyun   |    8400.00 |
| harry     |   84000.00 |
| emma      |  280000.00 |
| christine |   30800.00 |
| zhuzhu    |   30800.00 |
| gougou    |   30800.00 |
+-----------+------------+
10 rows in set (0.00 sec)

显示格式自定义

 CONCAT() 函数用于连接字符串

将几个数据拼接在一起，显示格式，可以自定义，

想查询每个人及对应的工资  拼接起来
mysql> select concat(name,'@',salary) from employee5;
+-------------------------+
| concat(name,'@',salary) |
+-------------------------+
| jack@5000.00            |
| tom@5500.00             |
| robin@8000.00           |
| alice@7200.00           |
| tianyun@600.00          |
| harry@6000.00           |
| emma@20000.00           |
| christine@2200.00       |
| zhuzhu@2200.00          |
| gougou@2200.00          |
+-------------------------+
10 rows in set (0.01 sec)mysql> select concat(name,'#',salary) from employee5;
+-------------------------+
| concat(name,'#',salary) |
+-------------------------+
| jack#5000.00            |
| tom#5500.00             |
| robin#8000.00           |
| alice#7200.00           |
| tianyun#600.00          |
| harry#6000.00           |
| emma#20000.00           |
| christine#2200.00       |
| zhuzhu#2200.00          |
| gougou#2200.00          |
+-------------------------+
10 rows in set (0.00 sec)

单条件查询

查询工资为5000的人
mysql> select name from employee5 where salary=5000;
+------+
| name |
+------+
| jack |
+------+
1 row in set (0.00 sec)查询性别为女的人
mysql> select  name from employee5 where sex='male';
+---------+
| name    |
+---------+
| jack    |
| tom     |
| robin   |
| tianyun |
| harry   |
| zhuzhu  |
| gougou  |
+---------+
7 rows in set (0.00 sec)

多条件查询

查询工资在3000到5000之间的员工
mysql> select name,salary from employee5 where salary >=3000 and salary <= 5000;
+------+---------+
| name | salary  |
+------+---------+
| jack | 5000.00 |
+------+---------+
1 row in set (0.00 sec)

也可以使用关键词  between  and

查询工资在2000到6000之间的员工
mysql> select name,salary from employee5 where salary between 2000 and 3000;
+-----------+---------+
| name      | salary  |
+-----------+---------+
| christine | 2200.00 |
| zhuzhu    | 2200.00 |
| gougou    | 2200.00 |
+-----------+---------+
3 rows in set (0.00 sec)

关键字IS NULL  

查询职位描述为null的员工
mysql> select name , job_description  from employee5 where job_description  is null;
+--------+-----------------+
| name   | job_description |
+--------+-----------------+
| harry  | NULL            |
| zhuzhu | NULL            |
+--------+-----------------+
2 rows in set (0.01 sec)

查询职位描述为 not null的员工
mysql> select name , job_description  from employee5 where job_description  is not  null;
+-----------+-----------------+
| name      | job_description |
+-----------+-----------------+
| jack      | teach           |
| tom       | teach           |
| robin     | teach           |
| alice     | teach           |
| tianyun   | hrcc            |
| emma      | salecc          |
| christine | salecc          |
| gougou    |                 |
+-----------+-----------------+
8 rows in set (0.00 sec)

查询职位描述为'' 的员工
mysql> select name , job_description  from employee5 where job_description = '';
+--------+-----------------+
| name   | job_description |
+--------+-----------------+
| gougou |                 |
+--------+-----------------+
1 row in set (0.00 sec)

NULL说明：
        1、等价于没有任何值、是未知数。
        2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
        3、对空值做加、减、乘、除等运算操作，结果仍为空。
        4、比较时使用关键字用“is null”和“is not null”。
        5、排序时比其他数据都小，所以NULL值总是排在最前。

关键词in集合查询

查询工资是2000或者3000或者5000或者5500的员工
mysql> select name , salary  from employee5 where salary =2000 or salary =3000 or salary =5000 or salary =5500;
+------+---------+
| name | salary  |
+------+---------+
| jack | 5000.00 |
| tom  | 5500.00 |
+------+---------+
2 rows in set (0.00 sec)也可以将他们放在一个集合里面查询
mysql> select name , salary from employee5  where salary in(2000,5000,5500);
+------+---------+
| name | salary  |
+------+---------+
| jack | 5000.00 |
| tom  | 5500.00 |
+------+---------+
2 rows in set (0.00 sec)

查询工资不是5500，5000的员工
mysql> select name , salary  from employee5 where salary  not in (5000,5500);
+-----------+----------+
| name      | salary   |
+-----------+----------+
| robin     |  8000.00 |
| alice     |  7200.00 |
| tianyun   |   600.00 |
| harry     |  6000.00 |
| emma      | 20000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
+-----------+----------+
8 rows in set (0.00 sec)

排序查询

升序查询
升序查询名字首字母排列
mysql> select name from employee5 order by name;
+-----------+
| name      |
+-----------+
| alice     |
| christine |
| emma      |
| gougou    |
| harry     |
| jack      |
| robin     |
| tianyun   |
| tom       |
| zhuzhu    |
+-----------+
10 rows in set (0.00 sec)

降序查询
mysql> select name from employee5 order by name desc;
+-----------+
| name      |
+-----------+
| zhuzhu    |
| tom       |
| tianyun   |
| robin     |
| jack      |
| harry     |
| gougou    |
| emma      |
| christine |
| alice     |
+-----------+
10 rows in set (0.00 sec)

语法： select  字段 from  表名 order by  字段      默认升序查询

  select  字段 from 表名 order  by  字段  desc      倒叙查询

模糊查询：

查询工资中有数字20的员工
mysql> select * from employee5 where salary like '%20%';
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
|  4 | alice     | female | 2019-03-05 | instructor | teach           |  7200.00 |    501 |    100 |
|  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
|  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
| 10 | gougou    | male   | 2017-02-02 | sale       |                 |  2200.00 |    503 |    102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
5 rows in set (0.00 sec)

正则查询

1.查询有特定字符串或者字符的记录

查询 name 字段含有“a”的记录，SQL 语句和执行过程如下。
mysql> select * from employee5 where name regexp 'a';
+----+---------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name    | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+---------+--------+------------+------------+-----------------+----------+--------+--------+
|  1 | jack    | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
|  4 | alice   | female | 2019-03-05 | instructor | teach           |  7200.00 |    501 |    100 |
|  5 | tianyun | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
|  6 | harry   | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  7 | emma    | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
+----+---------+--------+------------+------------+-----------------+----------+--------+--------+
5 rows in set (0.00 sec)

2.查询以特定字符或字符串开头的记录

mysql> select * from employee5 where name regexp '^a';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2019-03-05 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

3.查询以特定字符或字符串结尾的记录

mysql> select * from employee5 where name regexp 'y$';
+----+-------+------+------------+------+-----------------+---------+--------+--------+
| id | name  | sex  | hire_date  | post | job_description | salary  | office | dep_id |
+----+-------+------+------------+------+-----------------+---------+--------+--------+
|  6 | harry | male | 2018-02-02 | hr   | NULL            | 6000.00 |    502 |    101 |
+----+-------+------+------------+------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

4.使用{n,}或者{n,m}来指定字符串连续出现的次数

字符串{n,}表示字符串连续出现 n 次；字符串{n,m}表示字符串连续出现至少 n 次，最多 m 次。

例如，a{2,} 表示字母 a 连续出现至少 2 次，也可以大于 2 次；a{2,4} 表示字母 a 连续出现最少 2 次，最多不能超过 4 次。

mysql> select * from employee5 where name regexp 'r{1}';
+----+-----------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+---------+--------+--------+
|  3 | robin     | male   | 2018-02-02 | instructor | teach           | 8000.00 |    501 |    100 |
|  6 | harry     | male   | 2018-02-02 | hr         | NULL            | 6000.00 |    502 |    101 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          | 2200.00 |    503 |    102 |
+----+-----------+--------+------------+------------+-----------------+---------+--------+--------+
3 rows in set (0.00 sec)

5.‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a

mysql> select * from employee5 where salary regexp '72+';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2019-03-05 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

函数

函数  
count()  统计数量  
max()     
min()    
avg()    
database()  
user()    
now()    
sum()    
password()
​​   SELECT COUNT(*) FROM employee5;   SELECT COUNT(*) FROM employee5 WHERE dep_id=101;  SELECT MAX(salary) FROM employee5;    SELECT MIN(salary) FROM employee5;   SELECT AVG(salary) FROM employee5;  SELECT SUM(salary) FROM employee5;  SELECT SUM(salary) FROM employee5 WHERE dep_id=101;     

MySQL之安全控制

数据库不要暴露在公网；授权普通用户，注意远程连接地址。ip肯定不能弄成%。权限修改数据库端口。3306进程，使用mysql普通用户启动。启动binlog日志删除空口令账号，没有密码就可以登录test（5.6）数据库删除禁止root用户远程访问记录慢日志调优： 配置最大连接数    max_connections=300        

1.确保MySQL运行用户为一般用户

# groupadd mysql
# useradd -M -s /sbin/nologin -g mysql mysql# vim /etc/my.cnf
user = mysql#注意点:改变拥有者和所属组对于mysql的安装目录

2.建议修改默认端口3306,改为其他的一些端口

# vim /etc/my.cnf
port = 3306 false
port = 10086 true

3.开启mysql二进制日志,在误删除数据的情况下,可以通过二进制日志恢复到某个时间点 ；；； 为了数据安全 ，为了好恢复

# vim /etc/my.cnf
log_bin = othername

4..删除空口令账号

#禁用匿名账号
# vim /etc/my.cnfskip-grant-tables = 1. --改成 "#skip-grant-tables = 1"#删除空口令用户
mysql> SELECT User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)mysql> create user 'newrain'@'localhost';	#(这是在做实验)创建空口令账户
Query OK, 0 rows affected (0.00 sec)mysql> SELECT User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| newrain      | localhost |                                           |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)mysql> drop user 'newrain'@'localhost';	#这是删除空口令账户
Query OK, 0 rows affected (0.01 sec)mysql> SELECT User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

a.禁止root账户远程访问(允许普通用户远程访问，某个网段即可)

mysql> SELECT User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)mysql> create user 'root'@'10.0.11.%' identified by "123";
Query OK, 0 rows affected (0.00 sec)mysql> SELECT User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | 10.0.11.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)mysql> drop user 'root'@'10.0.11.%';
Query OK, 0 rows affected (0.00 sec)

b.使用mysql的时候,经常会遇到MySQL: ERROR 1040: Too many connections这样的问题,一种是访问量确实很高, MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力,另外一种情况是MySQL配置文件中max_connections值过小, 这时就需要调整当前最大连接数

##设置最大连接数02
修改mysql配置文件my.cnf,在[mysqld]段中添加或修改max_connections值:
max_connections=256