主從同步

master端配置

1. 修改my.cnf文件

1
2
3
4
5
6
7
8
9
10

vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = mixed
expire-logs-days = 7
binlog-do-db = luxtarget
binlog-ignore-db = mysql
replicate-do-db = luxtarget
replicate-ignore-db = mysql

1. 新建授權(quán)復(fù)制用戶

   1 2	grant replication slave on *.* to 'slave'@'xxx.xxx.xxx.xxx' identified by 'slave'; flush privileges;

2. 重啟mysql

   1	service mysqld restart

3. 鎖定數(shù)據(jù)庫，將數(shù)據(jù)打包并拷貝至slave

   1	flush tables with read lock;

4. 查看master上二進(jìn)制日志情況，記錄File與Position

   1	show master status;

5. 當(dāng)數(shù)據(jù)打包拷貝完畢，解除鎖定；

   1	ublock tables;

   當(dāng)數(shù)據(jù)較大時(shí)，將數(shù)據(jù)打包備份比使用mysqldump導(dǎo)入時(shí)效率更高

slave端配置

1. 修改my.cnf文件

   1 2 3 4 5 6 7 8 9 10

   vim /etc/my.cnf [mysqld] server-id = 2 log-bin = mysql-bin binlog_format = mixed expire-logs-days = 7 binlog-do-db = luxtarget binlog-ignore-db = mysql replicate-do-db = luxtarget replicate-ignore-db = mysql

2. 重啟mysql

   1	service mysqld restart

3. 將從master拷貝來的數(shù)據(jù)導(dǎo)入slave

4. 連接master開始同步

   1 2 3 4 5

   change master to master_host='xxx.xxx.xxx.xxx',master_user='slave',master_password='slave',master_log_file='master-bin.xxxxxx,master_log_pos=xxx; start slave; show slave status\G;

   當(dāng)Slave_IO_Running與Slave_SQL_Running的狀態(tài)都為Running時(shí)即可。

半同步配置

master端配置

1. 安裝mysql半同步插件

   1 2 3

   INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; SET GLOBAL rpl_semi_sync_master_enabled = 1; SET GLOBAL rpl_semi_sync_master_timeout = 1000;

2. 修改my.cnf文件

   1 2 3 4

   vim /etc/my.cnf [mysqld] rpl_semi_sync_master_enabled=1 #啟用半同步 rpl_semi_sync_master_timeout=1000 #超時(shí)時(shí)間為1s

3. 重啟mysql

   1	service mysqld restart

slave端配置

1. 安裝mysql半同步插件

   1 2 3 4

   INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SET GLOBAL rpl_semi_sync_slave_enabled = 1; STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

2. 修改my.cnf文件

   1	rpl_semi_sync_slave_enabled=1 #啟用半同步復(fù)制

3. 重啟mysql

   1	service mysqld restart

檢查配置

1. master

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

   SHOW GLOBAL STATUS LIKE 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)

2. slave

   1 2 3 4 5 6 7

   mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+ 1 row in set (0.01 sec)

好久不折騰mysql主從了，發(fā)現(xiàn)5.5交之前的版本，配置上有些變化，趁著夜深人靜，重溫一下，順便簡單記錄一下配置過程。

主從同步

master端配置

1. 修改my.cnf文件

1
2
3
4
5
6
7
8
9
10

vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = mixed
expire-logs-days = 7
binlog-do-db = luxtarget
binlog-ignore-db = mysql
replicate-do-db = luxtarget
replicate-ignore-db = mysql

本文出自：http://mayiwei.com/, 原文地址：http://mayiwei.com/2014/02/22/mysql55-master-slave/, 感謝原作者分享。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com