Page last modified 07:59, 18 Apr 2020 by alang

MySQL DBA 筆記

內容表格

1. 延伸閱讀
2. Percona Server
3. 資料庫備份/匯入
4. Bash Samples
5. 設定 MySQL 可接受外部主機的存取
6. MySQL Tuning and Optimization
7. MySQL 系統參數
8. DB效能監視
9. 變更 root 密碼
10. 新增帳號
11. 查詢帳號
12. 移除帳號
13. 變更一般用戶密碼
14. 調整 SQL 最佳輸出顯示
15. 關於 Store Engine (InnoDB/MyISAM)
16. View 操作
17. Procedure 操作
18. Trigger 操作
19. Primary key 與 Unique Key
20. 資料表(Table)操作
21. 資料庫(Database)操作
22. 在指令模式執行SQL statement
23. 指令求助

版本為 19:36, 20 Nov 2024

返回到版本存檔.

查閱目前版本

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:

--single-transaction 避免線上資料交易影響備份工作

參考教學:
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)
NOTE: 資料庫啟動時，必須啟用 Binary Log (--bin-log) 功能，才能做增量備份。

$> 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

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

優化資料庫參考連結：

簡易優化法：

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/

- 文字

mysql> show processlist;
+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host            | db     | Command | Time | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  3 | root | localhost       | webapp | Query   |    0 | NULL  | show processlist | 
|  5 | root | localhost:61704 | webapp | Sleep   |  208 |       | NULL             | 
|  6 | root | localhost:61705 | webapp | Sleep   |  208 |       | NULL             | 
|  7 | root | localhost:61706 | webapp | Sleep   |  208 |       | NULL             | 
+----+------+-----------------+--------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     | 
| Connections              | 8     | 
| Max_used_connections     | 4     | 
| Ssl_client_connects      | 0     | 
| Ssl_connect_renegotiates | 0     | 
| Ssl_finished_connects    | 0     | 
| Threads_connected        | 4     |  << active connections
+--------------------------+-------+
7 rows in set (0.00 sec)

變更 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> DROP USER 'user1'@'localhost';

變更一般用戶密碼

方法一)

#>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。

Primary key 與 Unique Key

新增 Primary Key

mysql> ALTER TABLE ans
mysql>  ADD PRIMARY KEY (sn);

移除 Primary Key

mysql> ALTER TABLE ans DROP PRIMARY KEY;

移除 Unique Key

mysql> show create table ans;

----------------------------------+
| ans   | CREATE TABLE `ans` (
  `SN` int(11) NOT NULL,   
  `AN` varchar(64) NOT NULL,
  `MonthsFromStart` int(11) NOT NULL,
  UNIQUE KEY `AN` (`AN`),  
  KEY `ClusteredIndex-20160930-104525` (`SN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------

mysql> ALTER table ans drop key AN;

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

MySQL DBA 筆記

內容表格

延伸閱讀

Percona Server

資料庫備份/匯入

Bash Samples

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

MySQL Tuning and Optimization

MySQL 系統參數

DB效能監視

變更 root 密碼

新增帳號

查詢帳號

移除帳號

變更一般用戶密碼

調整 SQL 最佳輸出顯示

關於 Store Engine (InnoDB/MyISAM)

View 操作

Procedure 操作

Trigger 操作

Primary key 與 Unique Key

資料表(Table)操作

資料庫(Database)操作

在指令模式執行SQL statement

指令求助

內容表格