在CentOS 7 架設互為備援的MariaDB設定

Azure上新增的VM選CentOS 7安裝完畢以後~

安裝MariaDB...就參閱之前的文章步驟不變。

設定DB
vim /etc/my.cnf.d/server.cnf

[mysqld]
# Replication Setting
server-id=1  <- 為數字，每台不重複即可
log-bin=mysql-bin

新增同步帳號
CREATE USER 'backup'@'Remote_IP_Addr' IDENTIFIED BY 'key in password';
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'Remote_IP_Addr';
FLUSH PRIVILEGES;

接著做一次 MariaDB 異地備援不同步時的處理，只需要DB SYNC一次即可，任選一邊當MASTER。

兩邊均設定主從
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='Remote_IP_Addr', MASTER_PORT=3306, MASTER_USER='backup', MASTER_PASSWORD='key in password';
START SLAVE;

檢查雙邊機制是否正常運作，若沒有ERROR就是了。
show slave status \G;

MariaDB : Failed Registration of InnoDB as a Storage Engine

昨晚DB顯示GG了，早上追了一下貌似自建Function沒設Read Only造成Log塞爆導致Slave DB LOG同步異常，然後自我修復重啟失敗所致。

做個MEMO囉...不經一事不長一智。

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

參考官方文件中的描述，在建立FUNCTION時，要增加READS SQL DATA否則在Recovery或Replicatioin時會出現unsafe的error message，這message多了就會塞爆LOG...

CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN i;
END;

無法啟動的LOG類似以下這個...只需要把/var/lib/mysql/下的ib_logfileXX跟ibdataX搬移開來(要移除也行~不過個人習慣是下MV指另，RM下好離手就回不來了。

101003 18:37:27 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Error: log file .\ib_logfile0 is of different size 0 40894464 bytes
InnoDB: than specified in the .cnf file 0 536870912 bytes!
101003 18:37:27 [ERROR] Plugin 'InnoDB' init function returned error.
101003 18:37:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
101003 18:37:27 [ERROR] Unknown/unsupported table type: InnoDB
101003 18:37:27 [ERROR] Aborting

接者下service mysql start 或 systemctl start mariadb.service 啟動即可。

• Slave DB 出現 Could not initialize master info structure 時，下reset slave; 清一下LOG。
• 出現 Error : Tablespace for table '`database`.`table-temp`' exists. Please DISCARD the tablespace before IMPORT.
  這個卡麻煩~畢竟DB不是都自己在處理的，其他人刪除Table沒下Drop指令而是使用...時，就會造成此錯誤，請將該table-temp.idb搬移出目錄即可。
•  

MariaDB 異地備援不同步時的處理

1.於Slave DB : stop slave;
   --停止Slave的同步

2.於Master DB: mysqldump --user=root -p --master-data --all-databases > /tmp/mysql_full.sql
   --做全DB備份，注意 --master-data 一定要加

3.於Master DB : RESET MASTER;
--清除Master的Log

4.於Master DB : scp -pr /tmp/mysql_full.sql tibtrade@10.1.0.5:/tmp/
   --將Master備份的資料傳到Slave機器上

5. 於Slave DB : mysql -u root -p --default-character-set=utf8 < /tmp/mysql_full.sql
   --將全DB備份還原至Slave DB

6. 於Slave DB : RESET SLAVE;
--清除Slave Log

7. 於Slave DB : start slave;
   --啟動同步機制

8. 於Slave DB : show slave status \G; 
   --檢查是否有ERROR

抓MariaDB slow query log 的設定備忘

昨晚DB無法啟動，先REBOOT後恢復早上又掛了，使用檢查錯誤LOG指令
#mysqld --help --verbose | grep 'log-error' | tail -1

[Note] Plugin 'FEEDBACK' is disabled.

[ERROR] mysqld: unknown option '--log-long-format'

[ERROR] Aborting

看來是之前埋入的儲存Long Query的設定出問題，不過會觸發到又發生失敗還是因為MySQL 5.6 -> MariaDB 10.1之後，又適巧某事主開發時使用到慢吞吞的SQL Query等連鎖反應造成的。

my.cnf的設定為

[mysqld]

feedback=ON       <-  少加這行所以回 Plugin 'FEEDBACK' is disabled.

slow_query_log

slow_query_log_file = /var/log/mysql-slow.log

long_query_time = 10

#log-long-format  <-多了這行所以出現unknown option 'log-long-format' 

FEEDBACK用處請參考本篇 

將正在運行的 MySQL 5.1 Server 換成 MariaDB 10.1

這陣子觀察全文檢索的需求看來越來越必要，MySQL 5.1貌似只有MyISAM支援但因為交易使用InnoDB較穩定所以Database得換換，裝完後第一時間感受是變快許多...反正交由測試人員去感受等回覆囉...若一切無異常下個大工程就是把正式環境的DB通通替換成MariaDB 10.1。

CentOS卡熟所以先拿來開刀，Ubuntu環境就先等等。

1. 設定 mariadb yum

在 MariaDB 下載頁面中有 LINK 列出了各種 Linux 套件的安裝法，選擇"Download"需要的版本後再點選 Repository configuration tool 進入環境選擇，我的版本是CentOS 6.7 64位元所以選了10.1版本(10.2是beta)，底下會秀出 MariaDB 的 yum repository 的資料，整段都複製起來吧。

然後在 /etc/yum.repos.d 這個文件夾下建立一個 mariadb.repo，然後把剛剛複製下來整段內容，通通寫到 mariadb.repo 這個文件並存檔就行了，這樣以後就可以用 yum 取得官方的 MariaDB 相關套件。

2. 備份 mysql data

不論如何，一定要把 mysql server 先停掉的，保險點可以先用 mysqldump 備份完整資料先...啟動備份用的backup.sh儲存當下完整備份跟單DB備份檔。

接者 service mysqld stop 後再下 mv /var/lib/mysql /var/lib/mysql.bak 

3. 移除舊的 MySQL 套件

直接下指令 yum remove mysql-server mysql-libs 這指令主要移除 MySQL 的 server 和動態連結套件，所以可能會列出一些相關套件會一併被移除，我的截圖畫面如下，需記得甚麼東西被移除就是到時候得再裝回去，否則若干服務會異常

4. 安裝 MariaDB Server

直接下指令 yum install MariaDB-server MariaDB-client 完成後先不要啟動MariaDB Service

5. 還原舊資料

在步驟 2 的地方，原本舊的 MySQL Server 資料被修改路徑，所以現在舊資料是在 /var/lib/mysql.bak，此時會發現到 MariaDB Server 安裝完畢後，/var/lib/mysql 也被建立了，所以 mv /var/lib/mysql.bak /var/lib/mysql 置換回來。

6. 升級資料表

由於 MySQL Server 內各表格的內容多少會和 MariaDB 不同，所以還是得升級一下，剛剛也還沒有啟動 MariaDB Server，所以現在就啟動並且升級資料庫。
service mysql start
chkconfig mysql on
mysql_upgrade -p

7. 把被移除的相關套件還原

同步驟3有那些被移掉的就再裝囉，以此案例只需要 yum install php56w-mysql 即可。

PS. 以上內容引用自https://www.pigo.idv.tw/archives/2120 ，感謝大大的分享...

MySQL備忘 ... CREATE TEMPORARY TABLE

■    Temporary Table語法 

CREATE TEMPORARY TABLE tmp_name (         id int(10) NOT NULL ,         name VARCHAR(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建立Temppry Table：

        也可使用ENGINE = MEMBORY來做儲存.

■    使用說明 

◎    建立Temporary Table僅有該連線可使用，其它連線無法使用(table不存在，table內資料也查詢不到)

◎    不同的連線，可建立相同名稱的temporary Table!

◎    Show Tables看不到Temporary table出現

◎    可使用index / PK / Unique

■    應用 

◎    Session如果是以資料庫儲存的，可用temporary table來作儲存，登出後此table就drop。

◎    購物車的暫存機置，如無需保存到下次登入，可用Temporary table。

◎    大量資料的子集處理

在大的資料量下讓資料庫不斷的排序搜尋，非常影響資料庫效能的，這時可以建立小的temporary table

用複合語法來建立的資 料，例如將：

insert into tmp_data (data_id , data_name) select data_id , data_name from data where data_id >1000 ;

◎    跨Table的合併資料查詢處理

以往對於跨table的索引方式，例如有CUSTOMER_A和CUSTOMER_B兩個table都有NAME和TIME欄位，如果要查詢NAME並用TIME排序也可用! 

RSYNC 同步資料備忘...

雙邊伺服器將SSL認證互相設定後，就不用進行輸入密碼驗證之程序。

指令 rsync -avz --rsh='ssh -p 1022' /來源目錄 Account@Host:/目的路徑
將"來源目錄"複製到"目的路徑"下

指令 rsync -avz --rsh='ssh -p 1022' /來源目錄/ Account@Host:/目的路徑
將"來源目錄/*.*"複製到"目的路徑"下

選項與參數：
-v ：觀察模式，可以列出更多的資訊，包括鏡像時的檔案檔名等；
-q ：與 -v  相反，安靜模式，略過正常資訊，僅顯示錯誤訊息；
-r ：遞迴複製！可以針對『目錄』來處理！很重要！
-u ：僅更新 (update)，若目標檔案較新，則保留新檔案不會覆蓋；
-l ：複製連結檔的屬性，而非連結的目標原始檔案內容；
-p ：複製時，連同屬性 (permission) 也保存不變！
-g ：保存原始檔案的擁有群組；
-o ：保存原始檔案的擁有人；
-D ：保存原始檔案的裝置屬性 (device)
-t ：保存原始檔案的時間參數；
-I ：忽略更新時間 (mtime) 的屬性，檔案比對上會比較快速；
-z ：在資料傳輸時，加上壓縮的參數！
-e ：使用的通道協定，例如使用 ssh 通道，則 -e ssh
-a ：相當於 -rlptgoD ，所以這個 -a 是最常用的參數了！