Database - MySQL

(最後更新: 2016-04-14)

Install

ubuntu :

sudo apt-get install mysql-server-5.6 mysql-client-5.6 mysql-client-core-5.6

mac :

brew install mysql
mysql.server restart

// client only
brew install mysql --client-only

mac : 開機自動啟動

mkdir -p ~/Library/LaunchAgents
ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents

登入不用密碼

如果你連 mysql 都進不去，從步驟[1]開始，如果你可以進到 mysql console 從步驟[3]開始

[1] 停止 mysql deamon

sudo /etc/init.d/mysql stop

[2] 加上 --skip-grant-tables option 啟動 mysql 指令

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

[3] edit password

mysql -u root
FLUSH PRIVILEGES;

修改密碼

SET PASSWORD FOR root@'localhost' = PASSWORD('');
或
UPDATE mysql.user SET Password=PASSWORD('') WHERE User='root';

[4] Flush privileges:

FLUSH PRIVILEGES;

[5] Restart mysql

sudo service mysql restart

設定/修改密碼

設定 root 密碼 (安裝時沒有設定過密碼)

mysqladmin -u root password

修改 root 密碼

mysqladmin -u root -p password

Enter password:
New password:
Confirm new password:

解除只有本機 IP 能連到 MySQL 的限制

[1] 預設 MySQL 的 3306 port 只有監聽本機的連結

root@test:/etc/apache2# netstat -an | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

[2] 修改 /etc/mysql/my.cnf, 註解 bind-address = 127.0.0.1

# bind-address      = 127.0.0.1

[3] 重啟

/etc/init.d/mysql restart

如果有使用 phpmyadmin, 要修改設定

權限→登錄資料→主機改成任意主機(%)

[4] 現在port就已經開啟讓外部ip可以連入了

root@test:/etc/mysql# netstat -an | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

[5] Done!

[error] 如果phpmyadmin沒辦法登入, 直接command登入也出現錯誤:

ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

可能原因是host改成 % (任意主機)，但批對不到localhost造成本機沒辦法登入

解決 : 再去phpmyadmin將主機改成localhost, 這時候host資料就有兩筆了%及localhost, 就可以登入了

`Access denied for user 'worker'@'10.0.21.77' (using password: YES)`

有可能是你正在轉移 DB, 但轉移時並不會幫你把 account 的密碼也轉移過去, 所以必須到修改 account 的密碼把密碼填上

`Host '172.18.0.4' is not allowed to connect to this MySQL server`

修改 root 的 host

USE mysql;
mysql> SELECT user,host FROM user WHERE User='root';
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+
mysql> UPDATE user SET host='%' WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> SELECT user,host FROM user WHERE User='root';
+------+------+
| user | host |
+------+------+
| root | %    |
+------+------+

Slow query

目的 : 將拖慢速度的 query 找出來, 進一步改善效能

開啟 slow query

1) `/etc/mysql/mysql.conf.d/mysqld.cnf` :

# 將原本被註解的 slow query 參數打開
slow-query-log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log     # slow query記錄檔的路儲存路徑
long_query_time = 1                                     # query超過1秒時，則會記錄
log-queries-not-using-indexes                           # 會記錄沒有索引的記錄

注意!! The --log-slow-queries option was removed in MySQL 5.6.1

2) 重啟動後就會在 `/var/log/mysql/` 下看到這個檔案了 : `mysql-slow.log`

分析 Slow query

安裝 mysql-server 就會有 mysqldumpslow 這個工具了

mysqldumpslow /var/log/mysql/mysql-slow.log

找出花最多時間的 5 個 queries

mysqldumpslow -a -s r -t 5 /var/log/mysql/mysql-slow.log

依最常執行的 query 做排序

mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log

參數 :

-t NUM       just show the top n queries
-a           don't abstract all numbers to N and strings to 'S'
-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time

其他分析工具 : pt-query-digest, mysqlsla

尋找 slow query

找出排名前 5 筆花最久時間的 query

SELECT * FROM slow_log ORDER BY query_time DESC LIMIT 5;

找出某天前 5 筆花最久時間的 query

SELECT * FROM slow_log WHERE start_time BETWEEN '2018-06-28 00:00:00' AND '2018-06-29 00:00:00' ORDER BY query_time DESC LIMIT 5;

Do not use `select *`

Especially you have a field with long string, it might cause you a slow query even you have narrowed down the scope in WHERE.

Let’s look at this query it took me for 1 min for only 60k records.

SELECT * FROM event WHERE expired_at <= 1566226900 AND type != 'system';

The root cause is one of fields whose type is json and in some records, they might be big but I don’t think they are more than 1k, but it is enough to make the query slow. It doesn’t matter how well I narrow down the scope in WHERE condition. To sum up, you should not use * in case you don’t notice that you include a big field.

Binlog

目的 : 將執行過的 query 紀錄下來, 當有一天操作失誤或數據遺失還可以復原回來

開啟 binlog

1) `/etc/mysql/mysql.conf.d/mysqld.cnf` :

# 將原本被註解的 log_bin 打開
log_bin = /var/log/mysql/mysql-bin.log

其他 :

# 只紀錄指定的 databases
binlog-do-db=db_name1
binlog-do-db=db_name2

# 忽略指定的 database
binlog-ignore-db=db_name1

# 限制 binlog 上限 size
max_binlog_size=100M

# 10 天後自動清除 binlog
expire_logs_days=10

2) 重啟動後就會在 `/var/log/mysql/` 下看到這兩個檔案了 : `mysql-bin.index` `mysql-bin.000001`

3) 測試

隨意 Inster 兩筆資料

使用 mysqlbinlog 指令將 binglog 輸出成 SQL

mysqlbinlog --start-datetime="2015-05-25 00:00:00"  --stop-datetime="2015-05-26 9:00:00" /var/log/mysql/mysql-bin.000001 > test_binlog_is_working.sql

執行還原

mysql> SOURCE test_binlog_is_working.sql;

其他指令

查看狀態

SHOW MASTER STATUS

重置

RESET MASTER

刪除指定日期以前的 binlog

PURGE BINARY LOGS BEFORE '2015-05-24 07:00:00';

刪除某個流水號以前的 binlog

PURGE BINARY LOGS TO ‘mysql-bin.000113′;

將超過 n 天的 binlog

FLUSH LOGS

輸出 mysql-bin 為檔案

mysqlbinlog /var/log/mysql/mysql-bin.000001 > qq.txt

恢復數據

1) 確定 binlog 是否有開啟, 如果有找出 binlog 位置

SHOW VARIABLES LIKE 'log_bin'

如果回應為 ON, 表示有開啟, 到 /etc/mysql/mysql.conf.d/mysqld.cnf 尋找 log-bin 的值
回應為 OFF 則表示沒有開啟, 不用執行以下步驟, 因為無法恢復了

假設 my.cnf 記錄著 :

log_bin = /var/log/mysql/mysql-bin.log  # 檔名前綴字串

/var/log/mysql :

(..略..)
-rw-rw----  1 mysql mysql  12967 May 24 01:19 mysql-bin.000001
-rw-rw----  1 mysql mysql     32 May 24 01:01 mysql-bin.index

2) 恢復最近一次備份的數據 (Optional)

如果 Server 之前有輩份, 先還原

mysql> SOURCE my_db.bak.sql

3) 用 mysqlbinlog 輸出 binlog

[1] 輸出整個 database

mysqlbinlog 無法直接輸出 table, 只能是一整個 database, 所以先取得 database 再到裡面找 table

指令需要指定 --database, --start-date, --stop-date, 在這期間內可能跨了很多 binlog

mysqlbinlog --database=my_db --start-datetime="2015-05-25 00:00:00"  --stop-datetime="2015-05-26 9:00:00" /var/log/mysql/mysql-bin.000001 > my_db.sql

//如果跨越了幾個 binlog, 採用追加的方式加到 bbs.sql
mysqlbinlog --database=my_db --start-datetime="2015-05-25 00:00:00"  --stop-datetime="2015-05-26 9:00:00" /var/log/mysql/mysql-bin.000001 >> my_db.sql

也可以用 /var/log/mysql/mysql-bin.00012* 模糊比對的方式指定多個 binlog 文件

[2] 取得指定的 table

cat my_db.sql | grep 'comment' > comment.sql

4) 執行要恢復的 table 的 binlog

[1] 找出 comment.sql 誤操作的語法並刪除, 如果只是要恢復數據可以不用做動作

[2] 執行 mysql> SOURCE comment.sql

ref : 參考恢復數據

MariaDB

MariaDB 是 mysql 的分支，效能比 MySQL 好，吃的資源也較少，用法與 MySQL 一模一樣，可以直接拿它來替代 MySQL

Install

sudo apt-get install mariadb-server mariadb-client

test :

mysql -u root

啟動

sudo service mysql start

欄位優化

IP 用 int unsigned 存

> echo ip2long('10.0.115.80');
> 167801680

對大 table 做 CRUD 的效能參考

860 萬的 table

SELECT

SELECT count(*) FROM `call_cv_history` WHERE event_at <= '2018-03-19 23:59:59';

event_at 範圍是1天的話是 1 row in set (0.50 sec)
event_at 範圍是10天的話是 1 row in set (5.14 sec)

event_at 有設 idnex, type 為 datetime

DELETE

DELETE FROM `call_cv_history` WHERE event_at <= '2018-03-20 23:59:59' LIMIT 50000;

LIMIT 是 10,000 的話是 Query OK, 10000 rows affected (0.91 sec)
LIMIT 是 50,000 的話是 Query OK, 50000 rows affected (13.26 sec)

最後採取每次刪 10,000 每 2~3 秒刪一次, 避免 cpu 飆高

INSERT 非常快無需擔心

COUNT(*) & COUNT(1) 初淺效能比較

先給結論: 無差別

測試環境: AWS RDS MySQL db.t2.small

Table 筆數: 近 570 萬

執行 query 所花時間差不多都是 0.75 秒

Troubleshootings

某些中文字無法存入問題

unicode 編碼一般常用字都是 3bytes, 但因為它是 4 byte 而無法寫入, 例如 : 𡟛參考 wiki

Mysql2::Error: Incorrect string value: '\xF0\xA1\x9F\x9B'

(未試過) 解法方法 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)

如果 MySQL Ram 吃到 45%+

在一台乾淨的 EC2 t1.micro 1G Ram，居然被吃到 45%，而執行 Rails app 後 Memory 就快被吃快了，網頁也變很頓，簡單的登入登出就會掛掉，

接著我試著增加 Swap，解決此問題，指令請到這篇增加 Swap 那段照著執行，

再執行一次 mysql start，一樣 Memory 是衝到爆，但它會自動分配一些到 swap，之後 MySQL 的 Memory 降到 26% -> 19% -> 16% ..

欄位 Type

Datetime 後面可以接長度，多出來的長度是小數點，可以細到毫秒以上 e.g. datetime(3) = 2017-06-28 03:14:35.280

SELECT `*` 及 SELECT {特定欄位} 的差別

當取出來的資料只有 1 筆可能還沒什麼差別, 但當資料有 1000, Query 的時間會微變慢,

但更慢的是當從 MySQL 取出來的資料傳輸到程式裡所花的時間, 而時間取決於 Query 出來的資料量大小, 很容易有 2s+ 的差距, 需要注意!

區分 : 全形半形大小寫

在某些情況下, 需要全形半形 db 的欄位 collation 就不要用 utf8_unicode_ci

要改用 utf8_general_ci

如果要區分大小寫就改用 utf8_bin

QPS & TPS

QPS：Queries Per Second 查詢量/秒, 是一台服務器每秒能夠相應的查詢次數, 是對一個特定的查詢服務器在規定時間內所處理查詢量多少的衡量標準
TPS : Transactions Per Second 是事務數/秒, 是一台數據庫服務器在單位時間內處理的事務的個數

查詢方法是在 MySQL console 輸入 SHOW STATUS;, 並指到以下對應的欄位

QPS = Questions
TPS = Com_commit/s + Com_rollback/s

Install

登入不用密碼

設定/修改密碼

解除只有本機 IP 能連到 MySQL 的限制

Access denied for user 'worker'@'10.0.21.77' (using password: YES)

Host '172.18.0.4' is not allowed to connect to this MySQL server

Slow query

開啟 slow query

1) /etc/mysql/mysql.conf.d/mysqld.cnf :

2) 重啟動後就會在 /var/log/mysql/ 下看到這個檔案了 : mysql-slow.log

分析 Slow query

尋找 slow query

Do not use select *

Binlog

開啟 binlog

1) /etc/mysql/mysql.conf.d/mysqld.cnf :

2) 重啟動後就會在 /var/log/mysql/ 下看到這兩個檔案了 : mysql-bin.index mysql-bin.000001

3) 測試

其他指令

恢復數據

1) 確定 binlog 是否有開啟, 如果有找出 binlog 位置

2) 恢復最近一次備份的數據 (Optional)

3) 用 mysqlbinlog 輸出 binlog

4) 執行要恢復的 table 的 binlog

MariaDB

Install

啟動

欄位優化

IP 用 int unsigned 存

對大 table 做 CRUD 的效能參考

860 萬的 table

COUNT(*) & COUNT(1) 初淺效能比較

Troubleshootings

某些中文字無法存入問題

如果 MySQL Ram 吃到 45%+

欄位 Type

SELECT * 及 SELECT {特定欄位} 的差別

區分 : 全形 半形 大小寫

QPS & TPS

`Access denied for user 'worker'@'10.0.21.77' (using password: YES)`

`Host '172.18.0.4' is not allowed to connect to this MySQL server`

1) `/etc/mysql/mysql.conf.d/mysqld.cnf` :

2) 重啟動後就會在 `/var/log/mysql/` 下看到這個檔案了 : `mysql-slow.log`

Do not use `select *`

1) `/etc/mysql/mysql.conf.d/mysqld.cnf` :

2) 重啟動後就會在 `/var/log/mysql/` 下看到這兩個檔案了 : `mysql-bin.index` `mysql-bin.000001`

SELECT `*` 及 SELECT {特定欄位} 的差別

區分 : 全形半形大小寫