MySQL DBA 筆記

MySQL Tuning and Optimization

• MySQL 5.4 Scaling on Nehalem with Sysbench
• Tuning MySQL on Linux
• Maximizing Sysbench OLTP performance for MySQL

清除 binary log

5.2 MySQL Server Logs :: 5.2.3 The Binary Log
12.5.1 SQL Statements for Controlling Master Servers :: 12.5.1.1 PURGE BINARY LOGS Syntax

系統目錄 /var/lib/mysql 底下有很多 mysql-bin.xxxxxxx 的檔案，這些是 MySQL 的 binary log，假設系統並沒有作 replication，而且確定不需要保留這些 transaction log，下列指示可以清除它們：

// 檢查 log 功能開啟

mysql> show variables like '%log%';
名稱為 log_bin

// 檢查 log 檔

mysql> show binary logs;
編號愈大的表示 log 愈新

// 刪除編號 mysql-bin.000011 以前但不包含 mysql-bin.000011 的所有 binary log files

mysql> purge binary logs to 'mysql-bin.000011';
指令結束後，記得在執行一次 show binary logs 檢查看看。

MySQL 系統參數

// 查詢語法

mysql> show variables like '%keyword%';
使用 % 是模糊查詢。

// MySQL 版本

mysql> show variables like 'version';

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>select host,user from mysql.user;

// 列出帳號 user1 的權限

mysql> SHOW GRANTS FOR user1@localhost;

// 列出目前帳號的權限

mysql> SHOW GTANTS;

變更一般用戶密碼

方法一)
#>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> DELETE FROM <table_name> WHERE <column_name>='value';

//刪除所有資料
mysql> DELETE FROM <table_name>;

//更新一筆資料
//只更新 id=3這筆資料
mysql> update <table> set <field>=replace(<field>,'<str_search>','<str_replace>') where id=3;

//新增一個欄位
mysql> ALTER TABLE <table_name> ADD uniqueid VARCHAR(32);

//增加欄位的字串長度
mysql> ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(16) NOT NULL;

資料庫操作

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

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

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

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

新增權限
mysql> GRANT ALL PRIVILEGES ON <db_name>.* TO <db_user>@localhost;
mysql> GRANT ALL PRIVILEGES ON <db_name>.* TO <db_user>@localhost WITH GRANT OPTION;

移除權限
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION ON <db_name>.* FROM <db_user>@localhost;

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

在指令模式執行SQL statement

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

指令求助

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