Database

12 月 14 2020

Percona XtraBackup for MySQL 8.0.22

這篇也算是作紀錄。

Percona 在 2020-10-23 貼出這文： MySQL New Releases and Percona XtraBackup Incompatibilities；裡面這段看起來感覺像是又被搞了…

特別強調 “又” 字是因為已經有過一次，而且放在 Percona XtraBackup – Documentation，但這次只張貼 blog post，沒寫在文件…

恰巧某台 11 月初新裝的 Linux 踩到坑，便持續關注 Percona XtraBackup 等新版釋出。

幾天前發現 Percona XtraBackup 的 github 有這個 pull request：PXB-2373 Release Notes for 8.0.22-15.0 (8.0)，今天就在下載頁看到了（而且換了版本編號邏輯… :-P ）
雖然 Percona XtraBackup 8.0 Release Notes 尚未更新，但剛測試的確沒看到 unknown error …

另外，MySQL 8.0.22 還有這變革…

By • 0 • Tags: ,

12 月 7 2020

測試 Raspberry Pi 4 的 MySQL server

之前測過 Raspberry Pi 4 可用的 storage devices ；讓我選擇的話，不在 Raspberry Pi 4 透過 USB 3 裝 SSD，大概就是 MicroSD card 。

fio 只能測試 file I/O，而 sysbench 可以測試 CPU 跟 memory，甚至還能測試 DBMS 效能。

測試基準作業系統都是 Ubuntu 20.10，安裝 MySQL 8，使用的工具是 sysbench ，用 oltp_read_write 作測試。（Percona 尚未提供 groovy 的 repository，就先不用 sysbench-tpcc 作測試了…）

裝機之後簡單執行這幾行指令：

# mysql -e "CREATE DATABASE sbtest;"
# sysbench oltp_read_write --mysql-user=root prepare
# sysbench oltp_read_write --mysql-user=root run

Intel SSD 760p 搭配 NVMe-to-USB3 轉接器，fio 測得結果是 iops 約 11k，傳輸約 42MB/s 。

SanDisk Extreme Pro MicroSD card，fio 測得結果是 iops 約 390，傳輸約 1560KB/s 。

對照組，這是在 DigitalOcean 開 US$ 10/mo 的 VPS （1 vCPU & 2GB RAM）作測試。
（看 /proc/cpuinfo 是 Intel(R) Xeon(R) CPU E5-2630L 0 @ 2.30GHz ，bogomips 約 4k）

有想要放網站的話，選用 VPS 還是會比較好… 至少，被攻擊的時候不會塞滿自己的頻寬。
有興趣玩 VPS 的可以參考這裡呀… XD

By • , 0 • Tags: , , , , ,

7 月 31 2020

MySQL 8.0.20 升級後記（踩 & 拆雷記）

續前一篇，這陣子除了表訂工作之外，就是觀察 MySQL 升級後有哪些地雷被引爆，接著開始救傷。

一顆跟 Java/JDBC 有關，錯誤訊息如下圖；原因是 MySQL 8 沒有 query_cache_size 等等的參數，而解決方式是更新 MySQL Connector/J。

另外一顆的成因/故事就比較長了…

MySQL 8 之前的版本，我們習慣把 character_set_server 設為 utf8 ，而 collation_server 設為 utf8_general_ci ；升版前看到這兩個變數預設值都換掉了，就很開心的拿掉… 於是部份（年紀比較大）的 PHP web server 就爆了，錯誤訊息如下圖。

初步研判以為是 PHP 認不得 utf8mb4 ，仔細追查後發現真因是「認不得 utf8mb4_0900_ai_ci」，解決方式是把 collation_server 設為 utf8mb4_general_ci 。

附帶一提， PHP 5.3 的 ext/mysqlnd 沒這問題，僅 5.4 ~ 5.6 受影響，而 PHP 7 之後都支援 UCA Ver.9 的 collations …

By • , , 0 • Tags: , , , , , , ,

5 月 31 2020

MySQL 8.0.20 升級筆記

這陣子在處理 MySQL 5.6 -> 5.7 -> 8.0 升級的事，踩到一些地雷；紀錄一下。

目前新版的 MySQL 5.7 & 8.0 在全新安裝時會隨機產生 root 的密碼，並把密碼寫在 log 裡面。
這行為在自動化佈署時會遇上點麻煩，而避免隨機產生 root 密碼的方式是在 script 作這樣的事：

...
mysqld --initialize-insecure
systemctl start mysqld
echo "ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '##########';" | mysql
...

（5.7 -> 8.0）升級方面，第一個變化是 mysql_upgrade 不需要執行了；server 在啟動時會檢查，並自行升版。

原本在 5.7 版仍能使用的幾個 Server System Variables 在 8.0 版必須移除：

  • query_cache_type = 0
  • query_cache_size = 0
  • innodb_large_prefix = 1
  • innodb_file_format = Barracuda

expire_logs_days 也應移除，改用 binlog_expire_logs_seconds。
我另外在 8.0 版補上這兩個設定：

  • mysqlx = 0
  • default_authentication_plugin = mysql_native_password

5.7 -> 8.0 的預設語系/字元集有變化：

character_set_servercollation_server
MySQL 5.7latin1latin1_swedish_ci
MySQL 8.0utf8mb4utf8mb4_0900_ai_ci

特別提 8.0.20 是因為使用 Percona XtraBackup 時踩到地雷（Ref. percona-xtrabackup-80 does not work with mysql 8.0.20）；原因是 Redo log 格式被更改了，Changes in MySQL 8.0.20 (2020-04-27, General Availability) 裡面有這段：

InnoDB: Redo log records for modifications to undo tablespaces increased in size in MySQL 8.0 due to a change in undo tablespace ID values, which required additional bytes. The change in redo log record size caused a performance regression in workloads with heavy write I/O. To address this issue, the redo log format was modified to reduce redo log record size for modifications to undo tablespaces. (Bug #29536710)

（有興趣深究的可以看看這個 commit）

Percona XtraBackup – Documentation 裡面也有這段說明：

Due to changes in MySQL 8.0.20 released by Oracle at the end of April 2020, Percona XtraBackup 8.0, up to version 8.0.11, is not compatible with MySQL version 8.0.20 or higher, or Percona products that are based on it: Percona Server for MySQL and Percona XtraDB Cluster.

另外一顆地雷是 Drupal 7 在連線初始化時會設定 SQL mode，但 NO_AUTO_CREATE_USER 在 8.0 被拿掉了；目前先用 這個 comment 裡面夾帶的 patch 作修正。

By • 1 • Tags: , ,

11 月 30 2014

[MySQL] shrink ibdata file

最近幫忙處理某台 MySQL 5.0 server，做了個小實驗，寫個文章把處理過程紀錄下來。

因為該 server 沒有 innodb_file_per_table 這個設定，所有的 InnoDB 資料都放在 ibdata 這個檔案裡，而我們希望能讓該 server 持續運作，盡可能在不停機的狀況下，把 InnoDB 的資料切開。

實驗後，我們透過這些步驟完成切割/轉移：

  1. 確認舊的 server 有開啟 binlog，準備跑 replication。
  2. 用 Percona XtraBackup 備份舊的 MySQL 資料庫。
  3. 準備新機器，把備份檔丟到新的 server。
  4. 在新機器的 my.cnf 加入 innodb_file_per_table 設定，把 MySQL daemon 跑起來。
  5. 在新機器用以下指令把 InnoDB table 挑出來：
    SELECT `TABLE_SCHEMA`, `TABLE_NAME` FROM `TABLES` WHERE `ENGINE`='InnoDB' ORDER BY `TABLE_SCHEMA`,`TABLE_NAME`;
  6. 在新機器用 mysqldump 匯出 table schema & data ：
    mysqldump -u root -pMY_PASSWORD --max_allowed_packet=512M -Cx --opt TABLE_SCHEMA TABLE_NAME > TMP_DIR/TABLE_SCHEMA.TABLE_NAME.sql
  7. 在新機器 drop 掉已匯出的 table：
    DROP TABLE `TABLE_SCHEMA`.`TABLE_NAME`;
  8. 停掉新機器的 MySQL daemon，把 datadir 的 ib* 搬走，再啟動 MySQL daemon。
  9. 重新匯入 table：
    mysql -u root -pMY_PASSWORD TABLE_SCHEMA < TMP_DIR/TABLE_SCHEMA.TABLE_NAME.sql
  10. 用 CHANGE MASTER TO … 指令開始讓新機器的 MySQL 成為舊 server 的 SLAVE。
  11. 同步完成後，找時間讓舊的 server 退下，讓新的 server 佔用舊機器的 IP。
  12. 在新機器的 MySQL 執行 RESET SLAVE，停掉 replication。

By • • Tags: , , , , ,

3 月 13 2012

在 FreeBSD 用 ports 安裝 MySQL 5.5

我在 FreeBSD 用 ports 安裝 databases/mysql41-server 、 databases/mysql50-server 時，會放這些 configuration parameters：

  • WITH_CHARSET=big5
  • WITH_XCHARSET=all
  • BUILD_OPTIMIZED

（databases/mysql51-server 會多放個 WITH_FAST_MUTEXES）

之前要安裝 databases/mysql55-server 時發現…
databases/mysql55-server 只剩下 WITHOUT_OPENSSL 與 WITH_FASTMTX 這兩個 configuration parameters 可以用，而預設的 WITH_EXTRA_CHARSETS（文章前頭提到的 WITH_XCHARSET）就是 all；以往使用的 WITH_CHARSET=big5 必須從 my.cnf 作設定（這也的確是比較好的作法）；BUILD_OPTIMIZED 則是不需要了（也不支援？）。

另外，databases/mysql55-server 預設只有這些 storage engines：

mysql> SHOW ENGINES;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                      | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

ARCHIVE、BLACKHOLE、EXAMPLE 跟 FEDERATED 這四個 storage engines 可以透過 INSTALL PLUGIN 的方式進行安裝，例：

mysql> INSTALL PLUGIN archive SONAME 'ha_archive.so';
mysql> INSTALL PLUGIN blackhole SONAME 'ha_blackhole.so';
mysql> INSTALL PLUGIN example SONAME 'ha_example.so';
mysql> INSTALL PLUGIN federated SONAME 'ha_federated.so';

By • , • Tags: , ,

3 月 2 2012

MySQL 的 max key length

我曾試著在 MYISAM 格式的 table 裡，把兩個 VARCHAR(255)，UTF8 編碼的欄位設定成一組 UNIQUE KEY。
當時遇到這個 Error：

#1071 – Specified key was too long; max key length is 1000 bytes

VARCHAR(255) 代表該欄位可以紀錄 255 個字元，每個字元以 UTF8 encoded 儲存，一個欄位可以佔用 765 bytes，所以兩個欄位總和超過 1000 bytes，無法設為一組 UNIQUE KEY。

前陣子，我把 MySQL 從 5.1 系列升版到 5.5 系列。
在 5.1（含）之前，MySQL 使用 3 個 bytes 紀錄 UTF8 encoded data。
開發團隊在 MySQL 5.5.3 加入了 utf8mb4 編碼，用 4 個 bytes 紀錄 UTF8 encoded data，支援更多的文字。

utf8mb4 讓我聯想到 max key length 的問題，所以進行了一些小測試，並追了一下 source code。
從目前的最新版本的 source code 看來：

By • • Tags:

8 月 25 2010

PHP 的 mysqlnd（MySQL Native Driver）

PHP 自 5.3.0 開始，引入 mysqlnd 這個 extension。
在 5.3.0 的 Full ChangeLog 中可以看到這行：

Added mysqlnd extension as replacement for libmysql for ext/mysql, mysqli and PDO_mysql. (Andrey, Johannes, Ulf)

依照個人的過往經驗，native driver 的效能都會比較好，而 mysqlnd 也不例外（有興趣的可以看看這篇文章）。

比較可惜的是，mysqlnd 在 Windows 平台的 PHP 是預設套件；在 *NIX 平台，使用前必須在編譯前就先做好設定（configure；參數可參考 PHP 官方的mysqlnd 安裝文件）。

剛才，很高興看到 FreeBSD 的 PHP5 ports 加入了 option，讓我們可以輕鬆搞定。 8-)

By • , , • Tags: , , ,

8 月 8 2010

[PHP] 試玩 MongoDB 的資料關聯

發現 MongoDB 是好一陣子前的事，而我也在某台機器上把它裝起來。
之前玩的時候，發現 MongoDB 不須帳號與密碼就可以連接，而且也無法配置使用者權限，所以把它丟在旁邊。

前幾天，我在網路上看到網友詢問 MongoDB 有沒有辦法作 JOIN，而他得到的答案是否定的。
趁著今天在家閒閒沒事作，書也看完幾個段落，就透過 PHP 來試試這種 ODBMS 如何實作資料關聯。

MongoDB 與常見的資料庫（例如：MySQL）有些微的不同：

資料庫資料表
MongoDB 稱之為Database（DB）Collection
MySQL 稱之為Database（DB）Table

先把這個觀念講清楚，底下的範例程式碼才不會看得霧煞煞。 8-)

首先，先建立 DB 與 Collection：

// 連接 MongoDB
$m = new Mongo();

// 連接資料庫，名稱就是 test
$testDB = $m->selectDB( 'test' );

// 建立 Collection，名稱分別是 user 與 sex
$testDB->createCollection( 'user' );
$testDB->createCollection( 'sex' );
  • MongoDB 沒有 createDB 這種指令。只要選擇資料庫，建立 Collection 之後，系統就會自動產生 DB。
  • 指令列模式下，選擇資料庫的指令跟 MySQL 相同（USE DB_NAME）。

再來，存入性別資料：

// 連接 MongoDB
$m = new Mongo();

// 連接資料庫，名稱就是 test
$testDB = $m->selectDB( 'test' );

// 在 sex 這個 Collection 裡面放入資料，好讓程式分辨男性與女性
$testDB->sex->insert( array('sex_name' => 'Female') );
$testDB->sex->insert( array('sex_name' => 'Male') );

// 把 sex 這個 Collection 的資料倒出來看
$cursor = $testDB->sex->find();
$array = iterator_to_array($cursor);
var_dump($array);
  • 以往我們儲存在 Table 的 data row 會有個 id 值，方便我們建立關聯。MongoDB 這種資料庫則是在 Collection 裡面放物件，可以不需要 id 值。

開始存入使用者名稱，並紀錄他（她）們的性別：

// 連接 MongoDB
$m = new Mongo();

// 連接資料庫，名稱就是 test
$testDB = $m->selectDB( 'test' );

// 找出男性資料，並取得關聯值
$male = $testDB->sex->findOne( array('sex_name' => 'Male') );
$refMale = $testDB->sex->createDBRef( $male );

// 找出女性資料，並取得關聯值
$female = $testDB->sex->findOne( array('sex_name' => 'Female') );
$refFemale = $testDB->sex->createDBRef( $female );

// 存入使用者資料
$testDB->user->insert( array('name' => 'BoyName', 'sex' => $refMale) );
$testDB->user->insert( array('name' => 'GirlName', 'sex' => $refFemale) );

// 取出並顯示使用者資料
$cursor = $testDB->user->find();
$array = iterator_to_array($cursor);
foreach ( $array as $user ) {
        // 找出性別的關聯物件
        $sexRef = $testDB->user->getDBRef($user['sex']);
        echo "Name: {$user['name']}\tSex: {$sexRef['sex_name']}\n";
}

以上，簡單的試玩心得。 (羞)

By • , • Tags: , , , ,

4 月 1 2010

[MySQL] 處理 weekday 欄位

最近在 MySQL 處理包含 weekday 型態的資料，發覺… 在 MySQL 處理 weekday 真的是「輕鬆寫意」… 8-)

根據 DAYOFWEEK() 與 WEEKDAY() 的定義，回傳的資料都是數值。
而大多數 DBMS 都有取得 weekday 的方法。

我會說「 MySQL 處理 weekday 輕鬆寫意」的原因是… ENUM ！
我把將欄位型態定義為 enum(‘Sun’, ‘Mon’, ‘Tue’, ‘Wed’, ‘Thu’, ‘Fri’, ‘Sat’)。
今天（2010/04/01，愚人節）是星期四，若是要抓出該欄位為 Thu 的資料，只要用這種語法：

SELECT ... WHERE `col`= DAYOFWEEK( NOW() )

用這類方法要注意的是，DAYOFWEEK() 與 WEEKDAY() 對於每個 weekday，回傳的數值都不一樣。

By • • Tags: , , ,

1 2