mysql 數(shù)據(jù)庫主從同步配置(同庫名&不同庫名)

最近工作中遇到需要同步數(shù)據(jù)庫的需求，結(jié)合著一些人的經(jīng)驗整理了下mysql 主從同步的配置步驟，同庫名的情況比較常見也容易解決，不同庫名的情況是這次遇到的一個問題點，做下記錄。

1）環(huán)境描述

mysql 的安裝可以參考：http://www.cnblogs.com/kevingrace/p/6109679.html

Centos 7 版本

master: 172.20.223.130

slave: 172.20.223.128
注意下面幾點：

1）要保證同步服務(wù)期間之間的網(wǎng)絡(luò)聯(lián)通。即能相互ping通，能使用對方授權(quán)信息連接到對方數(shù)據(jù)庫（防火墻開放3306端口）。

2）同步前，雙方數(shù)據(jù)庫中需要同步的數(shù)據(jù)要保持一致。這樣，同步環(huán)境實現(xiàn)后，再次更新的數(shù)據(jù)就會如期同步了。

2）主從復(fù)制實現(xiàn)過程記錄

為了測試效果，先在master機器上創(chuàng)建測試庫

mysql> CREATE DATABASE zxkang_sync CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use zxkang_sync;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into zxkang_sync.haha values(1,"terry"),(2,"tony");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from zxkang_sync.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | terry |
|  2 | tony |
+----+-----------+
2 rows in set (0.00 sec)

溫馨提示：

修改庫或表的字符集
mysql> alter database zxkang_sync default character set utf8;    //修改zxkang_sync庫的字符集
mysql> alter table zxkang_sync.haha default character set utf8;  //修改zxkang_sync.haha表的字符集

添加主鍵
mysql> Alter table zxkang_sync.haha add primary key(id);     //將zxkang_sync.haha表的id添加主鍵
mysql> Alter table zxkang_sync.haha change id id int(10) not null auto_increment;  //自增長屬性

刪除主鍵時要先刪除自增長，再刪除主鍵
mysql> Alter table zxkang_sync.haha change id id int(10);    //刪除自增長
mysql> Alter table zxkang_sync.haha drop primary key;     //刪除主建
下面是master數(shù)據(jù)庫上的操作：

1）設(shè)置master數(shù)據(jù)庫的my.cnf文件（在[mysqld]配置區(qū)域添加下面內(nèi)容）

[root@master ~]# vim /etc/my.cnf
.......
server-id=1        #數(shù)據(jù)庫唯一ID，主從的標(biāo)識號絕對不能重復(fù)。
log-bin=mysql-bin    #開啟bin-log，并指定文件目錄和文件名前綴
binlog-do-db=zxkang_sync  #需要同步的數(shù)據(jù)庫。如果是多個同步庫，就以此格式另寫幾行即可。如果不指明對某個具體庫同步，就去掉此行，表示同步所有庫（除了ignore忽略的庫）。
binlog-ignore-db=mysql  #不同步mysql系統(tǒng)數(shù)據(jù)庫。如果是多個不同步庫，就以此格式另寫幾行；也可以在一行，中間逗號隔開。
sync_binlog = 1      #確保binlog日志寫入后與硬盤同步
binlog_checksum = none  #跳過現(xiàn)有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed   #bin-log日志文件格式，設(shè)置為MIXED可以防止主鍵重復(fù)。

溫馨提示：

在主服務(wù)器上最重要的二進制日志設(shè)置是sync_binlog，這使得mysql在每次提交事務(wù)的時候把二進制日志的內(nèi)容同步到磁盤上，即使服務(wù)器崩潰也會把事件寫入日志中。

sync_binlog這個參數(shù)是對于MySQL系統(tǒng)來說是至關(guān)重要的，他不僅影響到Binlog對MySQL所帶來的性能損耗，而且還影響到MySQL中數(shù)據(jù)的完整性。對于"sync_binlog"參數(shù)的各種設(shè)置的說明如下：
sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什么時候來做同步，或者cache滿了之后才同步到磁盤。
sync_binlog=n，當(dāng)每進行n次事務(wù)提交之后，MySQL將進行一次fsync之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強制寫入磁盤。

在MySQL中系統(tǒng)默認(rèn)的設(shè)置是sync_binlog=0，也就是不做任何強制性的磁盤刷新指令，這時候的性能是最好的，但是風(fēng)險也是最大的。因為一旦系統(tǒng)Crash，在binlog_cache中的所有binlog信息都會被丟失。而當(dāng)設(shè)置為“1”的時候，是最安全但是性能損耗最大的設(shè)置。因為當(dāng)設(shè)置為1的時候，即使系統(tǒng)Crash，也最多丟失binlog_cache中未完成的一個事務(wù)，對實際數(shù)據(jù)沒有任何實質(zhì)性影響。

從以往經(jīng)驗和相關(guān)測試來看，對于高并發(fā)事務(wù)的系統(tǒng)來說，“sync_binlog”設(shè)置為0和設(shè)置為1的系統(tǒng)寫入性能差距可能高達5倍甚至更多。

2）導(dǎo)出master數(shù)據(jù)庫多于slave數(shù)據(jù)庫中的數(shù)據(jù)，然后導(dǎo)入到slave數(shù)據(jù)庫中。保證雙方在同步環(huán)境實現(xiàn)前的數(shù)據(jù)一致。

導(dǎo)出數(shù)據(jù)庫之前先鎖定數(shù)據(jù)庫
mysql> flush tables with read lock;    #數(shù)據(jù)庫只讀鎖定命令，防止導(dǎo)出數(shù)據(jù)庫的時候有數(shù)據(jù)寫入。unlock tables命令解除鎖定
導(dǎo)出master數(shù)據(jù)庫中多余的 zxkang_sync 庫
[root@master ~]# mysqldump -uroot zxkang_sync -p > /home/vivoadmin/zxkang_sync.sql
[root@master ~]# rsync  -e "ssh -p22" -avpgolr /home/vivoadmin/zxkang_sync.sql 172.20.223.128:/home/vivoadmin   ＃將導(dǎo)出的sql文件上傳到slave機器上，此處如果無效，可以用scp發(fā)送到slave上，在用 mysql 的導(dǎo)入命令導(dǎo)入庫，一次性工作而已，不要怕麻煩

3）設(shè)置數(shù)據(jù)同步權(quán)限

mysql> grant replication slave,replication client on *.* to slave@'172.20.223.128' identified by "slave@123";
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

溫馨提示：

權(quán)限查看方式
mysql> show grants;
mysql> show grants for slave@'172.20.223.128';

4）查看主服務(wù)器master狀態(tài)(注意File與Position項，從服務(wù)器需要這兩項參數(shù))

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 | zxkang_sync      | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

下面是slave數(shù)據(jù)庫上的操作：

1）設(shè)置slave數(shù)據(jù)庫的my.cnf配置文件

[root@master ~]# vim /etc/my.cnf

server-id=2   #設(shè)置從服務(wù)器id，必須于主服務(wù)器不同
log-bin=mysql-bin   #啟動MySQ二進制日志系統(tǒng)

#如果需要同步的數(shù)據(jù)庫名相同
replicate-do-db=zxkang_sync  #需要同步的數(shù)據(jù)庫名。如果不指明同步哪些庫，就去掉這行，表示所有庫的同步（除了ignore忽略的庫）。

#如果需要同步的數(shù)據(jù)庫名不同
replicate-rewrite-db=zxkang_sync->test  # master 上的數(shù)據(jù)庫名為 zxkang_sync ， slave 上的庫名為 test

replicate-ignore-db=mysql  #不同步mysql系統(tǒng)數(shù)據(jù)庫
slave-skip-errors = all   #跳過所有的錯誤錯誤，繼續(xù)執(zhí)行復(fù)制操作

溫馨提示：
當(dāng)只針對某些庫的某張表進行同步時，如下，只同步 zxkang_sync 庫的 haha 表：

replicate-do-db = zxkang_sync
replicate-wild-do-table = zxkang_sync.haha       //當(dāng)只同步幾個或少數(shù)表時，可以這樣設(shè)置。注意這要跟上面的庫指定配合使用；

2）在slave數(shù)據(jù)庫中導(dǎo)入從master傳過來的數(shù)據(jù)。

mysql> CREATE DATABASE zxkang_sync CHARACTER SET utf8 COLLATE utf8_general_ci;   #先創(chuàng)建一個huanqiu空庫，否則下面導(dǎo)入數(shù)據(jù)時會報錯說此庫不存在。
mysql> use zxkang_sync;
mysql> source /home/vivoadmin/zxkang_sync.sql;   ＃導(dǎo)入master中多余的數(shù)據(jù)。
.......

3）配置主從同步指令

mysql> stop slave;   ＃執(zhí)行同步前，要先關(guān)閉slave
mysql> change  master to master_host='172.20.223.130',master_user='slave',master_password='slave@123',master_log_file='mysql-bin.000001',master_log_pos=120;

mysql> start slave;
mysql> show slave status \G;
.......
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.223.130
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 279
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: huanqiu
          Replicate_Ignore_DB: mysql
           .............
        Seconds_Behind_Master: 0

如上，當(dāng)IO和SQL線程的狀態(tài)均為Yes，則表示主從已實現(xiàn)同步了！

查看slave數(shù)據(jù)庫中的數(shù)據(jù)情況

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| zxkang_sync            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from huanqiu.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | terry |
|  2 | tony |
+----+-----------+
2 rows in set (0.00 sec)
下面測試下Mysql主從同步的效果

現(xiàn)在主數(shù)據(jù)庫上寫入新數(shù)據(jù)

mysql> unlock tables;    #解鎖，否則新數(shù)據(jù)無法寫入
mysql> insert into zxkang_sync.haha values(100,"anhui");
Query OK, 1 row affected (0.00 sec)

然后在slave數(shù)據(jù)庫上查看，發(fā)現(xiàn)master上新寫入的數(shù)據(jù)已經(jīng)同步過來了

mysql> select * from zxkang_sync.haha;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | terry |
|   2 | tony |
| 100 | anhui     |
+-----+-----------+
3 rows in set (0.00 sec)

至此，主從同步環(huán)境已經(jīng)實現(xiàn)！

重要提示：修改mysql配置文件，別忘了重啟數(shù)據(jù)庫使修改的配置生效

注意：
Mysql主從環(huán)境部署一段時間后，發(fā)現(xiàn)主從不同步時，如何進行數(shù)據(jù)同步至一致？
有以下兩種做法：

1）參考：mysql主從同步(2)-問題梳理 中的第（4）步的第二種方法

2）參考：mysql主從同步(3)-percona-toolkit工具（數(shù)據(jù)一致性監(jiān)測、延遲監(jiān)控）使用梳理

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容