MySQL DBA 筆記

變更 root 密碼

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

變更一般用戶密碼

方法一)
#>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>;

檢查資料庫名稱

mysql> show databases;

連結資料庫

mysql> use <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 variables;

在指令模式執行SQL statement

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

指令求助

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