MySQL DBA 筆記

Percona Server

Percona Server 是 MySQL 的一個分支版本，使用同樣的 MySQL 原始程式，不過整個資料庫系統都作了額外的功能強化與性能優化工作，除了在效能與性能有明顯改善以外，相關的 HA/Cluster 配套方案也都一應具全。

Percona Server 還免費提供許多只有 MySQL 付費企業版才有的 features，所以更適合讓企業用在營運的重要系統上。

企業若想使用 MySQL 這類的資料庫，但對於現有的 MySQL CE 版，無論在性能與功能上都不甚滿意時，不妨考慮 Percona Server，不僅是 OpenSource 可免費使用，有許多 feature 在營運系統上都很有用。

• 中文網站說明：http://blog.wu-boy.com/category/%E9%...tradb-cluster/
• Personal Server v.s MySQL CE 版本比較
• 教學：
  • 基本安裝：http://www.percona.com/doc/percona-s...tallation.html
  • Percona XtraDB Cluster 搭配 HAProxy

資料庫備份/匯入

// 匯出/備份資料庫的 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。

// 匯出指定 Table

$> mysqldump --add-drop-table -uroot -p [db_name] Table-1 Table-2 > mydb-tables.sql

// 匯出 Table 部分紀錄

$> mysqldump --databases asteriskcdrdb --tables cdr --where="calldate > 2015-07-05 order by calldate desc" -uyouruser -p'yourpass'  > cdr.sql

// 備份資料庫 (for InnoDB)

$> mysqldump --single-transaction --add-drop-table -uroot -p [db_name] > my_innodb.sql 

TIPs:

參考:
http://dev.mysql.com/doc/refman/5.0/...db-backup.html
http://www.neo.com.tw/archives/1122
http://www.softwareprojects.com/reso...base-1684.html

// 備份大資料庫及交易資料 (for InnoDB)

$> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases > backup_sunday_1_PM.sql 

回復資料庫

先回覆完整備份
$> mysql < backup_sunday_1_PM.sql

再回復 binary logs
$>mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

TIPs：

參考：
https://dev.mysql.com/doc/refman/5.0...up-policy.html

MySQL交易紀錄稱為 binary log。

// 匯入資料庫

方法一：
# mysql -u<db-user> -p <db-name> < mydb-dump.sql

方法二：
# mysql -u<db-user> -p
mysql> SET GLOBAL connect_timeout=60;
mysql> use <db-name>;
mysql> \. /path/to/mydb-dump.sql
mysql> quit
 

Import .csv to MySQL

• http://chensh.loxa.edu.tw/php/X_21.php
• http://dev.mysql.com/doc/refman/5.0/...sqlimport.html
• http://forums.mysql.com/read.php?108,212918,212918
• http://www.linuxquestions.org/questi...script-781812/
• http://ericlondon.com/bash-shell-scr...sv-files-mysql
• Script to import Master.csv in the MySQL database
• http://codingstandards.iteye.com/blog/604541

Bash Samples

#!/bin/sh
...
#Update Database
RESULT=`/usr/bin/mysql -uroot -p$MYSQLROOTPASSWOOD <<SQL

use asteriskcdrdb

CREATE TABLE cdr_new (
acctid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
src varchar(80) NOT NULL,
dst varchar(80) NOT NULL,
...
...
uniqueid varchar(32) NOT NULL,
userfield varchar(80) NOT NULL
);

RENAME TABLE cdr TO cdr_backup;

RENAME TABLE cdr_new TO cdr;
ALTER TABLE cdr ADD INDEX ( calldate );
ALTER TABLE cdr ADD INDEX ( dst );
ALTER TABLE cdr ADD INDEX ( accountcode );

quit
SQL`
...

設定 MySQL 可接受外部主機的存取

編輯 /etc/mysql/my.cnf
註解這行 bind-address

#bind-address            = 127.0.0.1

DB用戶的權限必須允許外部主機存取，請參閱帳號管理

MySQL Tuning and Optimization

優化資料庫參考連結：

• MySQL 5.4 Scaling on Nehalem with Sysbench
• Tuning MySQL on Linux
• Maximizing Sysbench OLTP performance for MySQL
• MySQL 5 Optimization and Tuning Guide
• mysql 5.0 my.cnf設定檔解析
• [MySQL] Chapter 8. Optimization
• mysql5.x數據庫的MyISAM和InnoDB引擎配置優化

簡易優化法：

locate my-large.cnf
locate my-huge.cnf 

mysqladmin variables -uroot -p
mysq> SHOW VARIABLES;
mysql> SHOW STATUS;

MySQL 系統參數

// 查詢語法

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

shell> mysqladmin variables -uroot -p

// MySQL 版本

mysql> show variables like 'version';

// 系統狀態

mysql> /s

mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1


Connection id:          29
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.77 Source distribution
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 4261 days 14 hours 34 min 50 sec

DB效能監視

- 圖表

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

- 文字

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

// 顯示目前的連線
#mysqladmin -u root -p processlist 

變更 root 密碼

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

新增帳號

方法一)

#>mysql -u root -p mysql
mysql> CREATE USER 'db_user'@'from_host' IDENTIFIED BY 'db_pass';
mysql> GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'from_host' WITH GRANT OPTION;

方法二)

#>mysql -u roop -p mysql
mysql> INSERT INTO user
    ->     VALUES('from_host','db_user',PASSWORD('db_pass'),
    ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

TIP:

from_host 連線用戶端的 IP 或 hostname，或者如下
- localhost 限制僅local 存取
- % 不限制來源

查詢帳號

// 列出所有帳號

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;

方法二) 

#>mysql -u root -p
mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD("密碼") WHERE User='目標使用者' AND Host = 'localhost';
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 |

加上 /G 以直列方式顯示結果
NOTE: 不適用 rows 太多的查詢

mysql> show table status like 'data_source_stats_hourly_cache' \G
*************************** 1. row ***************************
           Name: data_source_stats_hourly_cache
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 124417
 Avg_row_length: 73
    Data_length: 10244096
Max_data_length: 9278300
   Index_length: 6654260
      Data_free: 191479
 Auto_increment: NULL
    Create_time: 2014-12-24 20:34:07
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

關於 Store Engine (InnoDB/MyISAM)

// 檢查 Table 的 storage engine type

mysql> use <db_name>;
mysql> pager less -n -i -S;
mysql> show table status;
mysql> show table status like "<table_name>";
mysql> show table status from <db_name>;

在 Linux console 可以改用

顯示所有 Tables
mysqlshow -uroot -p'password' --status <db_name>

顯示指定的 Table
mysqlshow -uroot -p'password' --status <db_name> <table_name> 

// 檢查預設的 Store Engine Type

mysql> show engines;
mysql> show variables; 

// 變更 Table 的 Store Engine Type

mysql> ALTER TABLE <table_name> ENGINE=InnoDB;

View 操作

// 顯示資料庫的 VIEW

mysql> show full tables in <table_name> where table_type like 'VIEW';

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

mysql> use <db_name>;
mysql> show tables;
mysql> show tables from <db_name>;

顯示資料表的筆數、類型、建立時間等等資訊

mysql> show table status;
mysql> show table status like 'table-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> RENAME TABLE old_name TO new_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 <column_name> VARCHAR(32);
※遇到有 dash 的 column name，需要加特殊符號 `，如以下指令
mysql> ALTER TABLE <table_name> ADD `column-name` 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);

// 修復損壞的資料表
當操作資料表時，出現錯誤訊息： Table 'cc_system_log' is marked as crashed and should be repaired
執行修復指令：

mysql> repair TABLE <tab_name>;

// 清除某個日期以前的資料
檢視最後的 30 筆

mysql>SELECT calldate FROM cdr WHERE calldate < '2011/1/1' ORDER BY calldate DESC LIMIT 30;
mysql>SELECT count(*) FROM cdr WHERE calldate < '2011/1/1';
mysql>DELETE FROM cdr WHERE calldate < '2011/1/1';

資料庫(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];

在指令模式執行SQL statement

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

指令求助

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