用架構圖說明今天要做的事情是再方便不過的事情了
目前的 WP 站台是在 Google Cloud Platform Marketplace 快速啟動的測試環境
也就是為人詬病的 Staging 不小心就 Production 的陋習
為了避免 https://blog.wnlin.org 不小心消失在 Internet 上
所以就開始拆架構，主要先從 DB 將資料拆出來，
未來 Slave 處理任何作業，也不擔心對主站台產生什麼不良影響
首先要先掌握 Bitnami WordPress (以下稱為 Master) 主要使用 MySQL 版本: MySQL Community Server 5.6.27
用的還是使用 Source 回來自主打包，沒從 APT 上處理，對本機升級來說，保留一些方便性
root@wnlin-wordpress:/opt/bitnami/mysql/bin# pwd /opt/bitnami/mysql/bin root@wnlin-wordpress:/opt/bitnami/mysql/bin# mysqld --version /opt/bitnami/mysql/bin/mysqld.bin Ver 5.6.27 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL))
目前 MySQL Hot Backup 主流不得不推薦 Percona，所以在 Master 上安裝 percona-xtrabackup-24 直接取用
root@wnlin-wordpress:~# dpkg -l | grep percona-xtrabackup ii percona-xtrabackup-24 2.4.12-1.wheezy amd64 Open source backup tool for InnoDB and XtraDB
接下來是處理預設的 my.cnf 設定調整，畢竟要用上 Replication, 還是有很多細節需要處理的
這邊觀察到 Bitnami 細心的行為，已經幫不同規格機器調整好設定參數，雖然不多，但有興趣研究的還是可以看看
root@wnlin-wordpress:/opt/bitnami/mysql/bitnami# ls -al total 24 drwxr-xr-x 2 bitnami root 4096 Sep 9 19:36 . drwxr-xr-x 11 root root 4096 Sep 9 19:35 .. lrwxrwxrwx 1 root root 39 Oct 8 2017 my.cnf -> /opt/bitnami/mysql/bitnami/my-small.cnf -rw-r--r-- 1 bitnami bitnami 473 Nov 16 2015 my-large.cnf -rw-r--r-- 1 bitnami bitnami 472 Nov 16 2015 my-medium.cnf -rw-r--r-- 1 bitnami bitnami 469 Nov 16 2015 my-micro.cnf -rw-r--r-- 1 bitnami bitnami 472 Nov 16 2015 my-small.cnf
先把本機專用的 Listen Port 放到本機以外都能用
root@wnlin-wordpress:/opt/bitnami/mysql# cat my.cnf | grep bind-address #bind-address=127.0.0.1 bind-address=0.0.0.0
再來是把 Binary Log 打開
root@wnlin-wordpress:/opt/bitnami/mysql# cat my.cnf | grep "log-bin\|log-slave-updates" log-bin log-slave-updates
設定一下 server-id ; 雖然有 default value, 但還是這訂一下比較好
因為只要 WordPress DB, 所以 binlog_do_db 也只處理單一 Database
root@wnlin-wordpress:/opt/bitnami/mysql# cat my.cnf | grep "server-id\|binlog_do_db" server-id=1 binlog_do_db=bitnami_wordpress
MySQL 5.6.27 已支援 GTID, 所以也一起開一開，直接設定 GTID Replication No Problem.
root@wnlin-wordpress:/opt/bitnami/mysql# cat my.cnf | grep "gtid" gtid-mode = on enforce-gtid-consistency = on
相關設定都不是 Dynamic Parameters ; 所以要記得 Restart Master MySQL Service
root@wnlin-wordpress:~# /opt/bitnami/ctlscript.sh restart mysql /opt/bitnami/mysql/scripts/ctl.sh : mysql stopped 180902 00:50:59 mysqld_safe Logging to '/opt/bitnami/mysql/data/mysqld.log'. 180902 00:50:59 mysqld_safe Starting mysqld.bin daemon with databases from /opt/bitnami/mysql/data /opt/bitnami/mysql/scripts/ctl.sh : mysql started at port 3306
Create Replica User for replication after MySQL restarted.
Ref: Creating a User for Replication
CREATE USER 'repl-user'@'%' IDENTIFIED BY 'repl-Pass'; GRANT REPLICATION SLAVE ON bitnami_wordpress.* TO 'replicacloud'@'%' WITH GRANT OPTION;
Excute full backup by Innobackupex & compress.
# innobackupex --defaults-file=/opt/bitnami/mysql/my.cnf -u "root-user" -p'root-Pass' /backup/db/ # innobackupex --defaults-file=/opt/bitnami/mysql/my.cnf -u "root-user" -p'root-Pass' --apply-log /backup/db/2018-09-xx_xx-xx-xx/ # tar -czvf wp-db-backup.tar.gz /backup/db/2018-09-xx_xx-xx-xx
Prepare Slave MySQL Database replication environment
Install Percona MySQL Server
Config Slave my.cnf
Option lower_case_table_names is interesting,
It’s can avoids the Replication Fail causing table uppercase caused by the Master.
bind-address = 0.0.0.0 server-id = 2 # for slave identity log_bin lower_case_table_names=1 gtid-mode = on enforce-gtid-consistency = on
Copy db backup file to slave datadir & Chown for mysqld.
# rsync -avP /home/user/db/2018-09-xx_xx-xx-xx/* /var/lib/mysql # chown -R mysql:mysql /var/lib/mysql
Start Slave mysqld & prepare for migrate to MySQL 5.7
root@wp-replica:~# /etc/init.d/mysql restart Stopping mysql (via systemctl): mysql.service. Starting mysql (via systemctl): mysql.service. root@wp-replica:/var/lib/mysql# mysql_upgrade -u root -p Enter password: Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Checking system database. bitnami_wordpress.wp_yoast_seo_meta OK sys.sys_config OK Upgrade process completed successfully. Checking if update is needed.
Restart mysqld for apply MySQL 5.7 Schema
root@wp-replica:/var/lib/mysql# /etc/init.d/mysql restart Restarting mysql (via systemctl): mysql.service.
Get detail xtrabackup_binlog_info
root@wp-replica:/home/heavenruler/db/2018-09-xx_xx-xx-xx # cat xtrabackup_binlog_info mysqld-bin.000004 35020393 7afb7f52-8c5c-11e5-a0cc-42010af00009:1-39203
Loging to Slave mysql server & change master for GTID Replication.
RESET SLAVE AALL; SET GLOBAL gtid_purged="7afb7f52-8c5c-11e5-a0cc-42010af00009:1-39203"; CHANGE MASTER TO MASTER_HOST="MASTER_IP", MASTER_USER="repl-user", MASTER_PASSWORD="repl-Pass", MASTER_AUTO_POSITION = 1; START SLAVE;
Go check Slave done without replication delay.
SHOW SLAVE STATUS; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: MASTER_IP Master_User: repl-user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysqld-bin.000004 Read_Master_Log_Pos: 428715748 Relay_Log_File: wp-replica-relay-bin.000005 Relay_Log_Pos: 79120341 Relay_Master_Log_File: mysqld-bin.000004 Slave_IO_Running: Yes skip Seconds_Behind_Master: 0 skip Master_UUID: 7afb7f52-8c5c-11e5-a0cc-42010af00009 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates skip Retrieved_Gtid_Set: 7afb7f52-8c5c-11e5-a0cc-42010af00009:39204-464625 Executed_Gtid_Set: 431e75fb-ae9c-11e8-b76a-42010af00002:1-3, 7afb7f52-8c5c-11e5-a0cc-42010af00009:1-464625 Auto_Position: 1
Done.