Jex’s Note

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@jexpoyi:/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@jexpoyi:/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

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 : 參考恢復數據

欄位 Type

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

區分 : 全形 半形 大小寫

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

要改用 utf8_general_ci

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

QPS & TPS

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

mysql syntax show status;

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

Q&A

某些中文字無法存入問題

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% ..

指定 SELECT 的欄位

SELECT * 及單一欄位的差別

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

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

修改大 TABLE (Innno DB)

新增欄位

目前測試的量沒有很多, 但可以給一些建議

環境: AWS RDS Aurora MySQL 5.6.10a (db.t2.small)

MySQL 5.6 以前不支援 ALTER table without locking table, 如果是 5.6 又是 InnoDB 的話,可以放心 alter,TABLE 不會被 lock

130 萬筆資料庫的檔案大約需要 67s,cpu 會吃 35%,一直 insert 也很順, 不會影響到服務

不建議建立另一個沒 data 的 TABLE, 先 ALTER 後再 copy 舊 TABLE 到新的, 因為 cpu 會吃到 80% 以上, 操作也略為複雜

修改欄位屬性 (ALTER TABLE)

這個動作會 lock table, INSERT / UPDATE / SELECT 都會, 它會先執行 copy to tmp table, 所以會整個會卡死

300 萬筆就足夠 lock 30 分以上

加欄位跟index

不會 lock table

新增 index

這個動作不會 lock table

效能

LIMIT & OFFSET

當資料筆數很大, 且 OFFSET 很大的話, 效能會很不好, 解決方式是不要用 OFFSET, 改用 PK id < 每次撈取的最後一個 id

KILL PROCESS

如果對 MySQL 下的指令造成 LOCK TABLE 或其他效能上的影響, 想取消的話, 可以刪除這個 process

先找出 process id

SHOW PROCESSLIST;

| 284349 | root      | 10.0.1.224:52018  | my-log     | Query   | 2568 | copy to tmp table               | ALTER TABLE `user` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE |

kill process

mysql> kill 284349;
Query OK, 0 rows affected (0.00 sec)

MariaDB

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

Install

sudo apt-get install mariadb-server mariadb-client

test :

mysql -u root

啟動

sudo service mysql start

Comments