一、主從同步：（A--->B）
10多年的靈山網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。全網(wǎng)整合營銷推廣的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整靈山建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)公司從事“靈山網(wǎng)站設(shè)計(jì)”,“靈山網(wǎng)站推廣”以來，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。
master:192.168.71.128
slave:192.168.71.138
1、Master配置:
vi /etc/my.cnf
server-id = 1
log-bin=MySQL-bin 啟用二進(jìn)制日志
binlog-ignore-db = mysql 不同步mysql庫
授權(quán)從數(shù)據(jù)庫192.168.71.138使用賬號(hào)rsync密碼bobo365同步主數(shù)據(jù)庫所有數(shù)據(jù)
mysql> grant replication slave on *.* to rsync@'192.168.71.138' identified by 'bobo365';
Query OK, 0 rows affected (0.01 sec)
刷新權(quán)限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
設(shè)置表為只讀狀態(tài)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
記錄file和position值，slave端要用到該數(shù)值。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 | 541 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
解鎖數(shù)據(jù)庫只讀。
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
2、Slave配置：
vim /etc/my.cnf
server-id = 2
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.71.128',master_user='rsync',master_
password='bobo365',master_log_file='mysql-bin.000025',master_log_pos=541;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.71.128
Master_User: rsync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000025
Read_Master_Log_Pos: 886
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 598
Relay_Master_Log_File: mysql-bin.000025
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 886
Relay_Log_Space: 758
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
3、測試：
master：
mysql> create database m2s;
Query OK, 1 row affected (0.00 sec)
mysql> use m2s;
Database changed
mysql> create table m2s_tb(id int(2),name char(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into m2s_tb values(001,'todu');
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bb |
| db_nagiosql_v3 |
| m2s |
| mysql |
| performance_schema |
| testcopy |
+--------------------+
7 rows in set (0.00 sec)
Slave：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cactidb |
| collabtive |
| db_nagiosql_v3 |
| m2s |
| my_wiki |
| mysql |
| performance_schema |
| test |
| testcopy |
+--------------------+
10 rows in set (0.00 sec)
mysql> use m2s;
Database changed
mysql> select * from m2s_tb;
+------+------+
| id | name |
+------+------+
| 1 | todu |
+------+------+
1 row in set (0.00 sec)
二、雙向同步：（A<--->B）
使用主主前提：
A、表的主鍵自增
B、程序?qū)憥熘付↖D
雙主互為主從：
解決主鍵自增長變量沖突：
Master1：
auto_increment_increment = 2 #自增ID的間隔，如 1 3 5間隔為2.
auto_increment_offset = 1
Master2：
auto_increment_increment = 2 #自增ID的間隔，如 2 4 6間隔為2.
auto_increment_offset = 2
存在ID不連續(xù)問題：
----> 1 3 5 6 8 10 11 13 15
互為主從參數(shù)：
log-bin = /data/3307/mysql-bin
log-slave-updates #開啟從庫binlog日志
三、二次學(xué)習(xí)內(nèi)容補(bǔ)充（對比學(xué)習(xí)）
環(huán)境：
3306 主
3307 從
主庫操作：
開啟binlog：log-bin = /data/3306/mysql-bin
更改server id：server-id = 1
添加復(fù)制用戶：
grant replication slave on *.* to rep@'192.168.1.%' identified by 'bobo365';
加讀鎖：
flush table with read lock;
show master status;
mysql-bin.000011 | 43275
show master logs;
窗口不關(guān)，重新開窗口dump數(shù)據(jù)：
mysqldump -S /data/3306/mysql.sock -uroot -pbobo365 -A -B -E --master-data=2 > /home/rep.sql
或者直接鎖表：
mysqldump -S /data/3306/mysql.sock -uroot -pbobo365 -A -B -E -X --master-data=2 > /home/rep.sql
或者--master-data=1
則從庫不用如下參數(shù)：
MASTER_LOG_FILE='mysql-bin.000011',
MASTER_LOG_POS=43275;
解鎖：
unlock tables;
從庫：
#log-bin = /data/3307/mysql-bin
server-id = 3
把主庫備份文件rep.sql灌入主庫：
source /home/rep.sql
登錄到從庫執(zhí)行：
CHANGE MASTER TO
MASTER_HOST='192.168.1.61',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='bobo365',
MASTER_LOG_FILE='mysql-bin.000011',
MASTER_LOG_POS=43275;
查看mster info文件：
[root@zhong-61 data]# more master.info
23
mysql-bin.000011
43275
192.168.1.61
rep
bobo365
3306
開啟開關(guān)：
start slave;
檢查：
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
附錄：
對比第一次，第二次增加遺漏關(guān)鍵步驟：
在主庫做全備，在從庫做全備導(dǎo)入。
重慶分公司
重慶分公司
