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 |

Procedure 操作

// 查詢 procedure

mysql> show procedure status;

Trigger 操作

// 查詢 trigger

mysql> show triggers like 'name%';
mysql> show triggers;

// 新增 trigger 的 script

DELIMITER ;;
SET SESSION SQL_MODE="ALLOW_INVALID_DATES" ;;
CREATE DEFINER=`root`@`localhost` TRIGGER `cdr_insert` AFTER INSERT
 ON `cdr` FOR EACH ROW BEGIN
    DECLARE new_dst varchar(80);
    IF NEW.billsec > 0 THEN
      CALL fix_number(NEW.dst,@fixed_dst);
      CALL get_rate(@fixed_dst,NEW.dstchannel,NEW.billsec,@myname,@final_cost);
      INSERT INTO cdrcost (uniqueid,destination,cost) VALUES (NEW.uniqueid,@myname,@final_cost);
      INSERT INTO normalization (destination,fixed,dstchannel) VALUES (NEW.dst,@fixed_dst,NEW.dstchannel);
    END IF;
  END ;;

Tips:

若是非 root 用戶要新增 trigger，必須要有 super 權限，而且必須是 ＊.＊ 指令為：
mysql> grant super on *.* to '<user>'@'localhost'

※這是個不安全的使用方法，不建議使用。

// 移除 trigger

mysql> DROP TRIGGER <schema.name>.<trigger.name>

※ schema.name 就是 database name。

資料表(Table)操作

// 檢查 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> describe <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;

// 資料表的 INDEX
// 顯示 INDEX
mysql> SHOW INDEX FROM <db_name>.<table_name>;

// 新增 INDEX
mysql> ALTER TABLE <table_name> ADD INDEX (column1);

// 新增 UNIQUE INDEX
mysql> ALTER TABLE <table_name> ADD UNIQUE (column2);

資料庫(Database)操作

//檢查資料庫名稱
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];

// 匯出資料庫的 schema
# mysqldump --no-data --tables -uroot -p [db_name] > mydb-schema.sql

Notes: 其他參數用法

＊ --ignore-table=[db_name].[table_name] 忽略的資料表名稱，若為多個資料表，此參數需重複輸入。
＊ 以上指令不會對 Trigger 及 procedure 做匯出。

// 匯出完整資料庫
# mysqldump --add-drop-table -uroot -p [db_name] > mydb-dump.sql

Notes:

若資料庫有包含 procedure，必須再加上參數 --routines。

在指令模式執行SQL statement

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

指令求助

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