mysql數(shù)據(jù)庫AB復制如何配置

這篇文章主要介紹了MySQL數(shù)據(jù)庫AB復制如何配置，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

創(chuàng)新互聯(lián)是一家成都網(wǎng)站制作、網(wǎng)站建設，提供網(wǎng)頁設計，網(wǎng)站設計，網(wǎng)站制作，建網(wǎng)站，按需求定制設計，網(wǎng)站開發(fā)公司，從2013年創(chuàng)立是互聯(lián)行業(yè)建設者，服務者。以提升客戶品牌價值為核心業(yè)務，全程參與項目的網(wǎng)站策劃設計制作，前端開發(fā)，后臺程序制作以及后期項目運營并提出專業(yè)建議和思路。

使用倆臺mysql服務器實現(xiàn)AB，主從復制。

一、在主ＭＡＳＴＥＲ服務器配置

MASTER 172.16.1.3
BACKUP 172.16.1.2

１、編輯my.cnf文件
#在原有基礎上添加這倆行

[root@zhaoyun ~]# cat /etc/my.cnf
[mysqld]
log-bin=/mysql/bin    #開啟binlog
server-id=1               ＃配置不和另一臺重復就行
２、重啟服務
[root@zhaoyun ~]# service mysqld restart
停止MySQL：[確定]
啟動MySQL：[確定]
３、授權(quán)用戶
mysql> grant replication slave on *.* to zhaoyun@172.16.1.2 identified by '123456'
[root@zhaoyun ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql> grant replication slave on *.* to zhaoyun@172.16.1.2 identified by '123456';
Query OK, 0 rows affected (0.15 sec)
mysql>
4、在B服務器測試是否可以登錄
[root@BACKUP ~]# mysql -uzhaoyun -p123456 -h272.16.1.3
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show grants ;
+----------------------------------------------------------------------------------------------------+
| Grants for zhaoyun@172.16.1.2                                                                     |
+----------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'zhaoyun'@'172.16.1.2' IDENTIFIED BY PASSWORD '565491d704013245' |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5、查看master的狀態(tài)
mysql> show master status ;
+------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.000001 |      315 |              |                  |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)

file字段是master的binlog文件名，position是binlog的節(jié)點。
二、配置BACKUP
1、編輯配置文件my.cnf,添加4行。
[mysqld]
server-id=2
master-host=172.16.1.3    #MASTER服務器的ip地址
master-user=zhaoyun      #連接MASTER服務器的用戶名
master-password=123456 #密碼
2、重啟服務
[root@BACKUP ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
3、重啟服務后會在目錄下生成幾個文件
[root@BACKUP ]# ls
         ib_logfile1 mysqld-relay-bin.000001 mysqld-relay-bin.index test
ibdata1      master.info   mysql.sock
ib_logfile0 mysql         relay-log.info
[root@BACKUP mysql]# pwd
/var/lib/mysql
mysqld-relay-bin.000001 #binload文件，從master復制而來
mysqld-relay-bin.index   #binload的信息
master.info      #master信息
relay-log.info   #中繼日志信息
4、查看slave的狀態(tài)
[root@BACKUP ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show slave status \G ;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 172.16.1.3
                Master_User: zhaoyun
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqld-bin.000001
        Read_Master_Log_Pos: 315
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 453
      Relay_Master_Log_File: mysqld-bin.000001
           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: 315
            Relay_Log_Space: 453
            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
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
#這個是主服務的binlog文件的狀態(tài)，如果出現(xiàn)IO是NO的話，需檢查這倆個文件的狀態(tài)。
      Master_Log_File: mysqld-bin.000001
       Read_Master_Log_Pos: 315

#這倆條是slave的IO進程，和SQL進程的狀態(tài)，AB復制的服務只有都為yes時才可用。

    Slave_IO_Running: YES
   Slave_SQL_Running: YES
#IO進程為NO可以將BACKUP的數(shù)據(jù)文件刪除，重啟服務重新同步就行了。

5、到現(xiàn)在配置基本完成

三、創(chuàng)建一個表進行測試，是否同步成功。
1、在master上創(chuàng)建。
mysql> create database master ;
Query OK, 1 row affected (0.00 sec)
mysql> use master
Database changed
mysql> create table master(id int,name char(5));
Query OK, 0 rows affected (0.04 se
2、在backup查看
[root@BACKUP ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show database ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)
mysql> use master
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables ;
+------------------+
| Tables_in_master |
+------------------+
| master           |
+------------------+
1 row in set (0.00 sec)
mysql>
可以看到數(shù)據(jù)已經(jīng)同步過來了。到此實驗成功。

故障排除：

IO 等于NO ：需要檢查節(jié)點和binlog文件名是否和在master看到的一致，如果不一致可以手動改寫
命令
先停止slave服務
mysql>slave stop;
mysql>change master to master_log_file="在master看到的binlog文件名";
mysql>change master to master_log_pos=100; 這個數(shù)字是在master看到的。
mysql>slave start ;
mysql> show master status ;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 |      507 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
SQL等于NO，可以試著刪除幾個文件重啟服務重新同步
mysqld-relay-bin.000001 #binload文件，從master復制而來
mysqld-relay-bin.index   #binload的信息
master.info      #master信息
relay-log.info   #中繼日志信息

感謝你能夠認真閱讀完這篇文章，希望小編分享的“mysql數(shù)據(jù)庫AB復制如何配置”這篇文章對大家有幫助，同時也希望大家多多支持創(chuàng)新互聯(lián)，關注創(chuàng)新互聯(lián)行業(yè)資訊頻道，更多相關知識等著你來學習!

當前標題：mysql數(shù)據(jù)庫AB復制如何配置
標題URL：http://bm7419.com/article18/jdspgp.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供域名注冊、動態(tài)網(wǎng)站、手機網(wǎng)站建設、App開發(fā)、網(wǎng)站內(nèi)鏈、網(wǎng)站設計

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容