mysql資料庫AB複制配置

                                         mysql AB複制配置

使用倆台mysql伺服器實作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： [确定]

３、授權使用者

[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> 

Query OK, 0 rows affected (0.15 sec)

mysql>

4、在B伺服器測試是否可以登入

[root@BACKUP ~]# mysql -uzhaoyun -p123456 -h172.16.1.3

Your MySQL connection id is 4

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 ;

+----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

5、檢視master的狀态

mysql> show master status ;

+------------+----------+--------------+------------------+

| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |

| bin.000001 |      315  |              |                  | 

file字段是master的binlog檔案名，position是binlog的節點。

二、配置BACKUP

1、編輯配置檔案 my.cnf,添加4行。

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 mysql]# 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的狀态

[root@BACKUP ~]# mysql -uroot -p123456

Your MySQL connection id is 5

Server version: 5.0.77 Source distribution

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

ERROR: 

No query specified

#這個是主服務的binlog檔案的狀态，如果出現IO是NO的話，需檢查這倆個檔案的狀态。

      Master_Log_File: mysqld-bin.000001

       Read_Master_Log_Pos: 315

 #這倆條是slave的IO程序，和SQL程序的狀态，AB複制的服務隻有都為yes時才可用。

    Slave_IO_Running: YES

   Slave_SQL_Running: YES

#IO程序為NO可以将BACKUP的資料檔案删除，重新開機服務重新同步就行了。

5、到現在配置基本完成

三、建立一個表進行測試，是否同步成功。

1、在master上建立。

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 

Your MySQL connection id is 6

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

mysql> show tables ;

+------------------+

| Tables_in_master |

| master           | 

可以看到資料已經同步過來了。到此實驗成功。

故障排除：

IO 等于NO ： 需要檢查節點和binlog檔案名是否和在master看到的一緻，如果不一緻可以手動改寫

指令

先停止slave服務

mysql>slave stop;

mysql>change master to master_log_file="在master看到的binlog檔案名";

mysql>change master to master_log_pos=100; 這個數字是在master看到的。

mysql>slave start ;

change master to master_host='10.102.10.2',master_user='zhaoyun',master_password='monitor',master_log_file='mysql-bin.000003',master_log_pos='33221'

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

| mysqld-bin.000001 |      507 |              |                  |

SQL等于NO，可以試着删除幾個檔案重新開機服務重新同步

忽略mysql slave錯誤

mysql> show variables like '%skip%';

mysql> set global sql_slave_skip_counter=10;

本文轉自zhaoyun00 51CTO部落格，原文連結:http://blog.51cto.com/zhaoyun/733377