MySQL DBA 筆記

DB效能監視

- 圖表

• MySQL Activity Report
  線上展示 http://gert.sos.be/demo/mysqlar/

- 文字

• mysqlreport
• MySQL Tuner
  下載 http://tools.assembla.com/svn/mysqlt.../mysqltuner.pl

變更 root 密碼

shell> mysqladmin -u root -p password 'newpass'
需輸入舊密碼

新增帳號

方法一)
#>mysql -u root -p mysql
mysql> CREATE USER 'myname'@'localhost' IDENTIFIED BY 'mypass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'myname'@'localhost' WITH GRANT OPTION;

方法二)
#>mysql -u roop -p mysql
mysql> INSERT INTO user
    ->     VALUES('localhost','myname',PASSWORD('mypass'),
    ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

變更一般用戶密碼

方法一)
#>mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('newpassword');
mysql> flush privileges;

調整 SQL 最佳輸出顯示

mysql> pager less -n -i -S;
mysql> show table status;
+---------------------+--------+---------+------------+------+----------------+-------------+-------------------+--
| Name                | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | I
+---------------------+--------+---------+------------+------+----------------+-------------+-------------------+--
| Backup              | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
| admin               | MyISAM |      10 | Dynamic    |    2 |             32 |          64 |   281474976710655 |
| ampusers            | MyISAM |      10 | Dynamic    |    1 |             44 |          44 |   281474976710655 |
| announcement        | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
| callback            | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
| cidlookup           | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
| cidlookup_incoming  | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
| cronmanager         | MyISAM |      10 | Dynamic    |    1 |            100 |         100 |   281474976710655 |
| custom_destinations | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
| custom_extensions   | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
| devices             | MyISAM |      10 | Dynamic    |    2 |             38 |          76 |   281474976710655 |
| disa                | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |
| extensions          | MyISAM |      10 | Dynamic    |    3 |             65 |         196 |   281474976710655 |
| featurecodes        | MyISAM |      10 | Dynamic    |   39 |             54 |        2136 |   281474976710655 |
| findmefollow        | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |   281474976710655 |

資料表操作

//檢查 Table 的 storage engine type
mysql> use <db_name>;
mysql> show table status;
mysql> show table status like "<table_name>";
mysql> show table status from <db_name>;

//檢查所有資料表 Table
mysql> use <db_name>;
mysql> show tables;
mysql> show tables from <db_name>;

//顯示既有資料表的 Create 的 SQL statement
mysql> use <db_name>;
mysql> show create table <table_name>;

//顯示資料表結構
mysql> show columns from <table_name>;

//新增一筆資料
mysql> INSERT INTO <table_name> (col1,col2) VALUES(val1,val2);

資料庫操作

//檢查資料庫名稱
mysql> show databases;

//連結資料庫
mysql> use <db_name>;

//顯示資料庫的所有參數
mysql> show variables;

//新增資料庫
mysql> CREATE DATABASE <db_name>;

//刪除資料庫
mysql> DROP DATABASE <db_name>;

在指令模式執行SQL statement

$>mysql -u db_user -ppassword -e "show tables" database
-p 與密碼之間不要有空格

指令求助

mysql> \h
mysql> help
mysql> help <command>;