預先準備的步驟,以下說明皆為MySQL Enterpise 所提供工具
1.先將Master 的my.cnf,修改如下所示,必須重啟MySQL才能生效。
1.1以下範例是將binlog 寫在同一檔案內,也可以針對不同database 設置各別binlog
,例如binlog-do-db = dbname1
1.2如有相關資料庫權限的異動,不需要同步到Slave,可加上binlog-ignore-db=mysql
[mysqld]
server-id=1
log-bin = /SQL/mysql55/mysql-bin
binlog_format = mixed
binlog-ignore-db=mysql
2.備份Master 的資料,透過MySQL Enterprise Backup (MEB)工具,撰寫mysqlbackup.sh script 可透過系統排程設定時間執行。
#!/bin/bash
datetime=`date +%Y-%m-%d_%H`
file1="mysqlbackup_$datetime.log"
/var/lib/mysql/bin/mysqlbackup \
--user="root" --password="you-password" \
--backup-dir=/NFS/ \
--with-timestamp \
backup-and-apply-log > /var/lib/mysql/$file1 2>&1
3. 檢視備份log檔案,是否有無錯誤訊息,如有必須重新執行。
4.或是檢視資料庫,exit_state 欄位顯示 SUCCESS
SELECT *
FROM `backup_history`
ORDER BY `backup_id` DESC
5. 在新建Slave 資料庫(作為Replication用途)內,先確認下列事項:
5.1 Slave database 停止服務。
5.2 data 目錄必須清空。
5.3 my.cnf 參數必須與 Master 相同。
innodb_data_file_path
innodb_log_files_in_group
innodb_log_file_size
5.4 my.cnf 參數(Slave)。
server-id=2
relay-log=mysqld-relay-bin
6.將Master匯入到Slave 資料庫內。透過mysqlrestore.sh script 語法。
綠色字體擇按照實際產生的目錄名稱作修改。
#!/bin/bash
/var/lib/mysql/bin/mysqlbackup \
--defaults-file=/NFS/2015-12-23_03-30-01/backup-my.cnf \
--datadir=/var/lib/mysql/data \
--backup-dir=/NFS/2015-12-23_03-30-01/ \
copy-back
6.匯入完成時間點,會依照實際資料量,而有所不同。MEB執行完畢後,啟動MySQL,
在/var/lib/mysql/data 目錄下,查看 hostname.err ,結果如下所示:
InnoDB: The log file was created by ibbackup --apply-log at
InnoDB: ibbackup 151223 5:12:21
InnoDB: NOTE: the following crash recovery is part of a normal restore.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
151223 18:14:44 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 1 row operations to undo
InnoDB: Trx id counter is 3E9837C00
InnoDB: Last MySQL binlog file position 0 184731019, file name /SQL/mysql55/mysql-bin.003220
7.進入Master 的資料庫,執行下列語法,讓Slave(Replication)透過
此帳號存取Master 。
CREATE USER 'repl'@'10.1.1.20' IDENTIFIED BY 'new-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.20';
8.進入Slave 的資料庫,執行下列語法,讓Slave(Replication)資料源指定Master
CHANGE MASTER TO
MASTER_HOST='10.1.1.201',
MASTER_USER='repl',
MASTER_PASSWORD='new-password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.003220',
MASTER_LOG_POS=184731019,
MASTER_CONNECT_RETRY=10;
8.進入Slave 的資料庫,執行下列語法:
START SLAVE;
9.查看 Replication 運作狀況,結果都是Yes,表示運作正常。
mysql> SHOW SLAVE STATUS\G;
Slave_IO_Running | Yes |
Slave_SQL_Running | Yes |
留言列表