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？打工不能發家，小小生意卻可以，最近比較有體會。明白這個道理，現在在工作不免有一絲悲觀，但我還是選擇工作唄，因為目前還是多認識些人，接觸多點資源吧。

posted @ 2017-12-04 23:08 前程明亮閱讀(1319) 評論(0) 收藏舉報

刷新頁面返回頂部

前程明亮