Mysql資料庫同步實現

標籤：

1、 設定Master伺服器

server-id=1 
log-bin=/var/lib/mysql/mysql-bin 

//為了使用事務的InnoDB在複製中最大的持久性和一致性，你應該指定innodb_flush_log_at_trx_commit=1,sync_binlog=1選項 
innodb_flush_log_at_trx_commit=1 
sync_binlog=1 

 

2、 設定Slave伺服器

Binlog_format=mixed

Max_allowed_packet=12M

Lower_case_table_names=1

Server-id = 8

 

Report-host=xxx.xxx.xxx.xxx

Report-user=replslave

Report-password=XXX

Log-bin=slave-bin.log

 

3、 使用者資訊同步

由於使用者在建立視圖等時候，會記錄下建立視圖的使用者名稱密碼。所以需要建立相同的使用者名稱及主機地址

 

1. A.      如果是可以鎖定Master伺服器的資料

a)        鎖定Master伺服器

flush tables with read lock; 

b)        查詢Binlog狀態

show master status;

 

 

c)        匯出Master資料

mysqldump --default-character-set=utf8mb4 --opt -u admin --password=ILoveQihuohui!-h Master地址 備份資料庫 > bak.sql

 

重新開啟主機Master寫操作功能

unlock tables; 

d)        匯入Master資料到Slave伺服器

mysql -uroot -p --default-character-set=utf8mb4 dbname <bak.sql

 

e)        設定Slave伺服器

stop  slave; 

mysql>change master to master_host=‘192.168.1.2‘, 
->master_user=‘repl_user‘, 
->master_password=‘123456‘, 
->master_log_file=‘mysql-bin.000013‘, 
->master_log_pos=7863951; 

start slave;

 

1. B.      如果不可以鎖定Master伺服器的資料

a)        查詢Binlog狀態

show master status;

 

b)        匯出Master資料

mysqldump --default-character-set=utf8mb4 --opt -u admin --password=ILoveQihuohui!-h Master地址 備份資料庫 > bak.sql

 

c)        匯入Master資料到Slave伺服器

mysql -uroot -p --default-character-set=utf8mb4 dbname <bak.sql

 

d)        確認Binlog具體位置

需要找到附近位置，並定位到具體的sql語句

 Show Binlog Events In ‘mysql-bin.000013‘ from 7863951

 

e)        設定Slave伺服器

stop  slave; 

mysql>change master to master_host=‘192.168.1.2‘, 
->master_user=‘repl_user‘, 
->master_password=‘123456‘, 
->master_log_file=‘mysql-bin.000013‘, 
->master_log_pos=7863951; 

start slave;

 

自動備份指令碼：

需要建立一個自讀使用者，該使用者需要有備份的資料庫以下許可權：

 select、Lock Tables、Show View

指令碼如下：

@echo off

set h=%time:~0,2%

set h=%h: =0%

set"Ymd=%date:~,4%%date:~5,2%%date:~8,2%%h%"

md g:\db_backups\%Ymd%

"D:\xampp\mysql\bin\mysqldump"  --default-character-set=utf8mb4 --opt -u dev--password=winbei888 -h 10.100.1.76 winbei_production >g:\db_backups\%Ymd%\winbeidb.sql

"D:\xampp\mysql\bin\mysqldump"  --default-character-set=utf8mb4 --opt -u dev--password=winbei888 -h 10.100.1.76 openfire >g:\db_backups\%Ymd%\openfiredb.sql

 

g:\db_backups\7za.exe a -rg:\db_backups\backdata\%Ymd%.zip g:\db_backups\%Ymd%\*

rd g:\db_backups\%Ymd%\ /S/Q

 

 

@echoon

 

 

 

 

Mysql資料庫同步實現