Mysql第一周

前言：好久不见，我又来写博客拉。上个月只写了几篇django-rest-framework的，而且还是根据官网的英文写的。干货不多，内心还是有点羞耻的……

简单说下我11月去干嘛了。11月初美图给我发offer了，当时面试我也说了11月底可以去实习。感觉美图也是蛮有名气的嘛，就拿了这个offer了，现在我也是在厦门的美图公司实习，刚入职几天，正在疯狂打杂中。拿了offer后，内心那种不安全感，源于一个问题，“你行不行？”。面试时我当然自信地表现出I CAN DO IT咯，但有些东西得入职后才知道……所以我就去学django-rest-framework，官网还有一些没看完，真是垃圾……之后觉得我在MYSQL数据库这方面实在是没有什么底气，就去看MYSQL的官网，看了两天，看着看着，发现两个问题：

• 深不见底，很多东西我有听过的，但没用过。
• 我记忆力不行，命令啥的看了就忘

为了解决这两个问题，并且能更好地与面试官撕逼，我决定报一个班。这是我第一次报培训班，当时没啥钱，还是凑出来的，感谢投资我的人儿，爱你们。

20号左右我就回家了，回家干嘛呢？拔智齿!! 我拔了一颗，还有三颗没拔，在家休养了一个星期。我流了很多血，很伤身体，在此和各位大哥女神们提个醒，以后有小孩了，10几岁就得隔几个月定期带小孩去看牙医。28号，我从揭阳坐高铁到厦门，不争气地说，这是我第一次出省。扯蛋的话稍后再说，这篇博客主要写MYSQL的基础知识，毕竟老子花了钱的，明天又要打杂了，现在周日晚上不写，怕是又没机会咯。

 

一、术语

1、数据库管理系统dbms----database management system

2、关系型数据管理系统rdbms----relational dbms

3、实例instance
数据库管理软件安装之后在服务器上启动起来就称为启动了一个实例，通常一个服务器上只启动一个数据库实例，但也有启动多个实例的，一个实例可以包含多个数据库。

4、数据库对象database object
数据库中创建的用来存储，操作数据的对象，比如表，字段，索引，存储过程等

备份时要把所有数据库对象备份出来，而不是简单地备份数据库表。

5、存储过程sp----stored procedure
在数据库内部创建的具有一系列数据处理逻辑的方法

6、触发器trigger
当数据库表发生修改操作时自动触发某些数据处理的方法。
如：触发修改用户最近一次登陆时间

7、主键primary key
数据表中定义的用来唯一确定表中各行数据的一个或几个字段

8、唯一键unique（可创建多个）
Primary key 和unique index都可以作为约束表中这一列或几列的数据不重复

9、外键foreign key
当数据表中的某一个或几个字段的值来源于某个父表时，则可以创建两个表之间的数据映射关系。

10、性能performance 调优tuning

11、备份backup
将数据库中所有数据和对象或者指定的数据和对象导出成文件形式存储

12、恢复restore
利用之前的备份文件将数据库中的指定对象和库还原到备份文件中的状态

13、授权grant
授予某个数据库用户某个权限的动作

14、回收revoke
将某个数据库用户的某个权限回收的动作

15、权限privilege

16、数据库架构database architecture
指数据库设计工作中对数据库一个或多个实例的部署，之间的数据协同，高可用性，并发性等特性所做的统一规划。

 

二、安装MYSQL

安装数据库是很重要的，我之前10月30号写了一篇Linux上安装二进制文件MySQL详解。

如果你的系统是windows，可以参考MySQL5.7.20安装，生成随机密码登陆请看http://blog.csdn.net/wxyfennie/article/details/50656457

安装这块不多说了，看我之前的博客，建议还是尝试用二进制安装下mysql，这样会显得自己更牛逼。

[root@iz2zeezp920l8adg80sf0pz mysql-files]# ls
auto.cnf        ib_logfile0  iz2zeezp920l8adg80sf0pz.err  sys
chengliang      ib_logfile1  iz2zeezp920l8adg80sf0pz.pid  test
ib_buffer_pool  ib_logfile2  mysql
ibdata1         ibtmp1       performance_schema
[root@iz2zeezp920l8adg80sf0pz mysql-files]# 

Mysql-files目录为初始化的数据文件存放路径，为每一个数据库创建了一个文件夹

Lbdata1和ib_logfile0/1为三个专为innodb存放数据和日志的共享文件

basedir=/usr/local/mysql	代表Mysql安装路径
datadir=/usr/local/mysql/mysql-files	代表Mysql的数据文件路径
log-error=/usr/local/mysql/mysql-files/M00006.err	记录Mysql启动日志和运行错误日志
character-set-server	指定Mysql的字符集，默认是latin1
collation-server	指定Mysql的排序规则(是否容许A=a)，默认是latin1_swedish_ci
default-storage-engine	指定Mysql的存储引擎，默认是innoDB
default-time-zone	指定时区，如果没指定则和系统默认时区一致
open-files-limit(默认5000)	指定Mysqld运行过程中可以打开的文件数，避免出现"Too many open files"报错
pid-file=/usr/local/mysql/data/M00006.pid	指定Mysqld进程对应的程序ID文件，默认在数据文件目录里
skip-grant-tables	指定避开Mysql内部的权限表启动服务
tmpdir	指定临时表文件的存放路径

 

 

三、远程连接阿里云数据库

因为我的服务器在阿里云，我想通过CRT或者xshell啥的，直接在我本地PC连接上数据库就好。这需求很简单吧，也不过分呀。

看上面的sql语名，我创建了一个chengliang用户，但是通过CRT或者mysql客户端就是登不上数据库，用root用户可以。这我就懵比了。于是上网搜些资料，按以下进行排查：

1、已经给数据库添加权限了，远程主机按理是可以连接上的呀

mysql> select host,user from user;                 
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | chengliang    |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

mysql> FLUSH PRIVILEGES; 
Query OK, 0 rows affected (0.00 sec)

mysql>

2、难道是端口没开？

[root@iz2zeezp920l8adg80sf0pz mysql]# netstat -ntlp | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      27909/mysqld        
[root@iz2zeezp920l8adg80sf0pz mysql]# 

3306端口明明有开呀!!

 

3、用telnet试下

[root@iz2zeezp920l8adg80sf0pz mysql]# telnet 101.200.48.238 22
Trying 101.200.48.238...
Connected to 101.200.48.238.
Escape character is '^]'.
SSH-2.0-OpenSSH_6.6.1

Protocol mismatch.
Connection closed by foreign host.
[root@iz2zeezp920l8adg80sf0pz mysql]# telnet 101.200.48.238 3306
Trying 101.200.48.238…

[root@iz2zeezp920l8adg80sf0pz mysql]# 

天呐，3306端口telnet不通!! 这是为啥？明明通过netstat -ntlp查看已经开通了3306了呀!!莫非是阿里云限制了端口的访问？

上网查下资料先：解决阿里云服务器3306端口无法访问的问题（windows server 2008r2）

 

4、根据别人博客写的，我下载了一个端口扫描工具，扫下我的服务器。感觉我的阿里云服务器可以只开通22号端口？

果然，3306没开通呀！此时我心里已经默默长草了。

5、在阿里云的云服务器管理控制台添加安全组规则（端口3306）

顺便吐槽下阿里云，上面这个页面我找了超久，用户体验太差了吧

[root@iz2zeezp920l8adg80sf0pz mysql]# telnet 101.200.48.238 3306
Trying 101.200.48.238...
Connected to 101.200.48.238.
Escape character is '^]'.
J
5.7.20'pK,_TG=!tb0:!KQc{7ymysql_native_password

!
#08S01Got packets out of orderConnection closed by foreign host.
[root@iz2zeezp920l8adg80sf0pz mysql]# 

连接成功！Mysql客户端也可以连接上了。

 

四、权限系统介绍

 

1、查看mysql实例默认root用户的权限（来自localhost）

mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+-------------------------------------------
--------------------------+
2 rows in set (0.00 sec)

2、root用户已经有全局权限，故不用在db里面再写入权限了。

mysql> select * from db where user='root' and host='localhost';     # 数据库权限
Empty set (0.00 sec)

mysql> 
mysql> select * from tables_priv where user='root' and host='localhost';　#表权限
Empty set (0.00 sec)

mysql> select * from procs_priv where user='root' and host='localhost';      #存储过程权限
Empty set (0.00 sec)

 

 

 

mysql> create user zcl_test@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> create user zcl_test@localhost;
ERROR 1396 (HY000): Operation CREATE USER failed for 'zcl_test'@'localhost'
mysql> 
mysql> create user Zcl_test@localhost; 
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create user Zcl_test@Localhost; 
ERROR 1396 (HY000): Operation CREATE USER failed for 'Zcl_test'@'localhost'
mysql> 

 

五、用户授权

 

1、创建新用户

mysql> create user cdq@localhost identified by "mysql"; 
Query OK, 0 rows affected (0.00 sec)

mysql>

2、cdq用户可以登陆

[root@iz2zeezp920l8adg80sf0pz ~]# mysql -u cdq -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use mysql;　# 没有权限
ERROR 1044 (42000): Access denied for user 'cdq'@'localhost' to database 'mysql'
mysql> 
mysql> show grants for cdq@localhost;
+-----------------------------------------+
| Grants for cdq@localhost                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'cdq'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

Usage权限是创建一个用户之后的默认权限，其本身代表连接登录权限。

 

3、给cdq用户添加权限：

mysql> grant select on test.* to cdq@localhost;
Query OK, 0 rows affected (0.00 sec)


mysql> show grants for cdq@localhost;
+-----------------------------------------------+
| Grants for cdq@localhost                      |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'cdq'@'localhost'       |
| GRANT SELECT ON `test`.* TO 'cdq'@'localhost' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| animals        |
| person         |
| shirt          |
| shop           |
| t1             |
+----------------+
5 rows in set (0.00 sec)

mysql> select * from animals;　# 有select权限
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.01 sec)

 没有insert权限，同理，需要添加insert权限：

mysql> insert into animals values(7,'bird');
ERROR 1142 (42000): INSERT command denied to user 'cdq'@'localhost' for table 'animals'
mysql> 

4、也可以用更改权限表的方式，来添加权限：（但一般不用）

mysql> update mysql.db set Insert_priv='Y' where user='cdq' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  select * from mysql.db where user='cdq' and host='localhost';            
+-----------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db   | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost | test | cdq  | Y           | Y           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
+-----------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 

 

六、用户连接

除了平时的交互式登陆连接外，还有以下方法(下面的xxx为登陆密码)：

[root@iz2zeezp920l8adg80sf0pz ~]# mysql -u root -pxxx mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 343

免交互登陆，可以用于写shell脚本

 

七、修改密码

方法一：修改本身连接用户的密码(只能修改当前连接用户)：

mysql> set password=password("password");
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@iz2zeezp920l8adg80sf0pz ~]# mysql -u cdq2 -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'cdq2'@'localhost' (using password: YES)
#密码已经被修改
[root@iz2zeezp920l8adg80sf0pz ~]# mysql -u cdq2 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 630

方法二：

mysql> alter user cdq2@localhost identified by 'mysql';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@iz2zeezp920l8adg80sf0pz ~]# mysql -u cdq2 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 631

 

八、MySQL用户资源限制

最大连接数为2：

mysql> alter user cdq2@localhost with max_user_connections 2;
Query OK, 0 rows affected (0.00 sec)


[root@iz2zeezp920l8adg80sf0pz ~]# mysql -u cdq2 -p
Enter password: 
ERROR 1226 (42000): User 'cdq2' has exceeded the 'max_user_connections' resource (current value: 2)
[root@iz2zeezp920l8adg80sf0pz ~]# 

mysql> select * from mysql.user where user='cdq2';

 查看数据库连接状态：

mysql> show full processlist;
+-----+------+-----------+------+---------+------+----------+-----------------------+
| Id  | User | Host      | db   | Command | Time | State    | Info                  |
+-----+------+-----------+------+---------+------+----------+-----------------------+
| 624 | cdq2 | localhost | NULL | Sleep   |    3 |          | NULL                  |
| 628 | root | localhost | NULL | Query   |    0 | starting | show full processlist |
+-----+------+-----------+------+---------+------+----------+-----------------------+
2 rows in set (0.00 sec)

mysql> 

 

九、用户密码过期策略

1、root用户设置cdq2用户密码过期：

mysql> alter user cdq2@localhost password expire;
Query OK, 0 rows affected (0.00 sec)

mysql> 

2、cdq2密码过期，必须先重置：

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.　　# 必须先重置密码
mysql> set password=password("mysql");
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

 

十、锁定用户lock

1、root用户把cdq2锁住：

mysql> alter user cdq2@localhost account lock;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select * from mysql.user where user='cdq2';

2、cdq2无法登陆

[root@iz2zeezp920l8adg80sf0pz ~]# mysql -u cdq2 -p
Enter password: 
ERROR 3118 (HY000): Access denied for user 'cdq2'@'localhost'. Account is locked.
[root@iz2zeezp920l8adg80sf0pz ~]# 

 

 

第一周大概就这样了，以后争取每周写一篇mysql，有点难，因为在新公司实习有好多要学的呀。不过我会尽量的哈哈

之前一个人来厦门感觉很寂寞，周围好多同学都去留在广州或者深圳了，而我老爸老妈也有意让我留广东的。加上自己已经到了发春的年龄，心理还真是不好受，作为一个男的，真是太幼稚了。算了，太感性的话不说……

现在感觉打工并不能赚什么钱，就算一个月税前2/3w，so what？打工不能发家，小小生意却可以，最近比较有体会。明白这个道理，现在在工作不免有一丝悲观，但我还是选择工作呗，因为目前还是多认识些人，接触多点资源吧。