MySQL 資料庫一般性管理

1. MySQL資料庫一般性管理

2. ▊Outlines • MySQL資料表類型 • 增加MySQL資料庫使用者 • 取消權限或移除MySQL使用者 • 備份和回復資料庫

3. ▊MySQL資料表類型 • MySQL支援許多種資料表的類型，它們都有自已的特色與優缺點：

4. MyISAM： • 在Web、資料倉儲和其他應用環境下最常使用的表格類型之一 • MySQL的預設表格類型 • 支援全文檢索 • 在預設資料庫mysql中，記錄關於使用者權限的資料表格，是以MyISAM表格類型儲存 • InnoDB： • 支援交易處理與外來鍵 • BDB： • 可替代InnoDB的表格類型 • Memory： • 將所有資料保存在RAM中，在需要快速搜尋引用和其他類似的環境下，可提供極快的訪問 • Merge： • 允許MySQL DBA或開發人員將一系列等同的MyISAM資料表以邏輯方式組合在一起，並作為1個對象引用它們 • Archive： • 為大量很少引用的歷史、歸檔、或安全審計訊息的儲存和檢索

5. MyISAM v.s. InnoDB • 若要追求空間、效率與速度，不需要從事表格與表格間的關聯時(即：外來鍵)，可使用MyISAM • 若著重交易工作、安全性、多人同時修改資料與需採用外來鍵的情況，則應使用InnoDB • 在MySQL資料庫系統中，不同類型的表格，是由不同類型的儲存引擎 (Storage Engine) 所產生與控制。

6. ▊增加MySQL資料庫使用者 • 在mysql中我們使用grant敘述，來設定新使用者並給予其相應之權限。 grant privileges on what to user identified by “密碼” • 說明： • Privleges: 設定權限等級給新使用者。可分成 “使用者層級權限” 與 “管理者層級權限” • What: 設定新使用者所能使用到的資料庫名稱及該資料庫中相關之資料表格 • *.*: 第一個 * 可指定某一個資料庫名稱，第二個 * 可指定該資料庫之下的某一個資料表名稱。若都是用 *，則表示可使用所有資料庫的所有資料表。 • User:設定新的使用者，包括使用者名稱與主機名稱。如：jacy@localhost • @符號之前是新使用者名稱，@之後是所在的主機 • 密碼: 指定使用者所用的密碼。如果新增的使用者沒有指定(identified)密碼，這代表該使用者沒有密碼。

7. 使用者層級權限

8. 管理者層級權限

9. 我們使用grant usage on *.* to jacy@localhost identified by “123456”來新增MySQL資料庫的使用者jacy。 • localhost指的是本地端的網址，如果使用者有指定網址，則可以使用所指定的網域名稱，如jacy@nuu.edu.tw是指jacy使用者在nuu.edu.tw網址。年與思 • 當我們要登錄MySQL資料庫時，只需輸入mysql –u jacy –p再輸入密碼即可。 • -u 的引數是指定使用者，在這我們輸入新增的使用者jacy • -p 的引數是密碼，我們輸入在前面設定的密碼

11. 當新使用者登入後，他可以修改自已的密碼。修改方式有二 (可任選其一)： • set password = password(“新密碼”); • set password for jacy@localhost = password(“新密碼”);

12. 使用grant來建立一個新的使用者lulu, 並授權他對phpmyadmin資料庫中的所有資料表格作delete、insert、select、update的操作，其辨識密碼是1234567890。 grant delete,insert,select,update on phpmyadmin.* to lulu@localhost identified by “1234567890” (各位自已try一下吧)

13. ▊取消使用者權限與移除MySQL使用者 • 要取消某個使用者的權限，可用revoke敘述。 revoke priviledges on what from user • 例：使用revoke來取消lulu在資料庫主機localhost的所有MySQL存取權限。 revoke all on *.* from lulu@localhost • revoke敘述只移除權限而非移除使用者，該使用者的項目仍存於user資料表中，所以使用者依舊可以連到伺服器上。 • 要完整的移除使用者，必須使用delete敘述明確的由user資料表上，將使用者紀錄刪除。

14. 使用delete敘述將在MySQL資料庫的使用者lulu在localhost網站給移除。使用delete敘述將在MySQL資料庫的使用者lulu在localhost網站給移除。 • 先選用多個系統預設資料庫中的 “mysql” 資料庫。打入下列指令 use mysql; • 打入下列指令以移除使用者lulu delete from user where user=“lulu” and host=“localhost”; • 移除過後需使用flush privileges來更新MySQL資料庫的權限。即上述delete指令執行完後，尚需執行下列指令 flush privileges; • flush privileges：當利用手動的方式修改grant時使用，用來確認這些權限的改變已經在系統內運作。

15. ▊備份和回復資料庫 • 備份資料庫是很重要的例常性工作以免資料庫當機或各種事件發生時資料遺失，而且對於銀行或財經單位，資料的備份是很重要的。

16. 直接拷貝資料 • 備份是很簡單的，尤其是在Windows系統下。就像我們將檔案複製到其它地方去放，要用時再將檔案複製到原來的目錄即可。 • 利用Windows版的MySQL資料庫系統所建構出來的所有資料庫，皆被存放於MySQL/data檔案路徑之下，以檔案夾的形式呈現。 • 最簡單的備份作法，是當MySQL資料庫系統停用時，從檔案總管中，找出我們要備份的資料庫，以複製檔案夾的方式將之複製一份即可。 • 當我們想要還原資料庫或資料表時，只要將備份的資料庫或資料表拷貝回去即可。

17. mysqldump備份指令 • 利用mysqldump指令來拷背資料沒有直接拷背資料來得快，但是也是一個簡單的方法。 • 首先，先將系統預設的空資料庫 “test”中，存入一個測試用的表格 “db”。 • 由預設資料庫 “mysql” 拷貝一份。 • 記得，MyISAM每一個表格有三個不同副檔名的檔案，全部都要copy到test檔案夾 (即：test資料庫) 中。

18. 備份特定資料庫中特定表格的指令： mysqldump -u root -p 資料庫名表格名>備份檔名.sql • 例： 備份“test”資料庫中的表格“db”，備份的檔名為 “testdb_bak” mysqldump -u root -p test db>testdb_bak.sql • 此備份檔所存放的位置與公用程式 “mysqldump” 相同 (即：MySQL\bin目錄內) • 若想要將所備份的檔案放於特定的目錄(如：testdb) 下，則： • 首先先建立該目錄 • 若此目錄是在MySQL\bin目錄之內，則可用相對路徑即可。例如： mysqldump -u root -p test db>testdb\testdb_bak.sql • 若此目錄是在MySQL\bin目錄之外，則需用絕對路徑。例如： mysqldump -u root -p test db>c:\AppServ\Mysql\bin\testdb\testdb_bak.sql

19. 備份特定資料庫的指令： mysqldump -u root -p --databases 資料庫名>備份檔名.sql • 例： 備份“test”資料庫，備份的檔名為 “test_bak” mysqldump -u root -p --databases test>test_bak.sql • 此備份檔所存放的位置與公用程式 “mysqldump” 相同 (即：MySQL\bin目錄內) • 若想要將所備份的檔案放於特定的目錄 (如：testdb) 下，則： • 首先先建立該目錄 • 若此目錄是在MySQL\bin目錄之內，則可用相對路徑即可。例如： mysqldump -u root -p --databases test>testdb\test_bak.sql • 若此目錄是在MySQL\bin目錄之外，則需用絕對路徑。例如： mysqldump -u root -p --databases test>c:\AppServ\Mysql\bin\testdb\test_bak.sql

20. 備份所有資料庫的指令： mysqldump -u root -p --all-databases>備份檔名.sql • 例： 備份所有資料庫，備份的檔名為 “alldb” mysqldump -u root -p --all-databases>alldb.sql • 此備份檔所存放的位置與公用程式 “mysqldump” 相同 (即：MySQL\bin目錄內) • 若想要將所備份的檔案放於特定的目錄 (如：testdb) 下，則做法同先前所述。

21. 資料庫(表格)回復指令 • 回復所備份之特定表格的指令： mysql -u root -p 資料庫名<備份檔名.sql • 例： 回復“test”資料庫中的表格“db”，備份的檔名為 “testdb_bak” mysql -u root -p test<testdb_bak.sql • 先前備份時，若有路徑設定上的不同，則可在符號 “<” 右方填寫備份檔名的部份，將先前的路徑填上即可。 • 回復所備份之資料庫的指令： mysql -u root -p<備份檔名.sql • 例： 回復“test”資料庫，備份的檔名為 “test_bak” mysql -u root -p<test_bak.sql • 先前備份時，若有路徑設定上的不同，則可在符號 “<” 右方填寫備份檔名的部份，將先前的路徑填上即可。

22. ■其它MySQL管理工具指令 • 請參考MySQL 5.1參考手冊第8章 (http://www.phptw.idv.tw/doc/mysql51cht/)