Chia Sẽ Kinh Nghiệm Về IT



Tìm Kiếm Với Google
-


MySQL Master, Slave, Cluster, Replication.. Database Availability... MySQL Database Replication và Failover

Gởi Ðề Tài Mới  Gửi trả lời
 
Công Cụ Xếp Bài
Tuổi 01-11-2009, 01:05 PM   #1
hoctinhoc
Guest
 
Trả Lời: n/a
MySQL Master Master Repliction Tutorial
MySQL Master Master Repliction Tutorial



This tutorial describes how to set up MySQL master-master replication. We need to replicate MySQL servers to achieve high-availability (HA). In my case I need two masters that are synchronized with each other so that if one of them drops down, other could take over and no data is lost. Similarly when the first one goes up again, it will still be used as slave for the live one.
Here is a basic step by step tutorial, that will cover the mysql master and slave replication and also will describe the mysql master and master replication.
Notions: we will call system 1 as master1 and slave2 and system2 as master2 and slave 1.
Step 1:

Install mysql on master 1 and slave 1. configure network services on both system, like


Master 1/Slave 2 ip: 192.168.16.4
Master 2/Slave 1 ip : 192.168.16.5

Step 2:

On Master 1, make changes in my.cnf:




Mã:

  [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=1

[mysql.server]
user=mysql
basedir=/var/lib


[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Step 3:

On master 1, create a replication slave account in mysql.




Mã:

  mysql> grant replication slave on *.* to 'replication'@192.168.16.5 \
identified by 'slave';


  and restart the mysql master1.
Step 4:

Now edit my.cnf on Slave1 or Master2 :




Mã:

     [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
  
  server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Step 5:



Mã:

Restart mysql slave 1 and at
  mysql> start slave;
mysql> show slave status\G;
  
*************************** 1. row ***************************

 
Slave_IO_State: Waiting for master to send event Master_Host: 192.168.16.4 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: MASTERMYSQL01-bin.000009 Read_Master_Log_Pos: 4 Relay_Log_File: MASTERMYSQL02-relay-bin.000015 Relay_Log_Pos: 3630 Relay_Master_Log_File: MASTERMYSQL01-bin.000009 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: 4 Relay_Log_Space: 3630 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: 1519187
1 row in set (0.00 sec)
Above highlighted rows must be indicate related log files and Slave_IO_Running and Slave_SQL_Running: must be to YES.

Step 6:

On master 1:





Mã:

  mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|MysqlMYSQL01-bin.000008 |      410 | adam         |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.

Step 7:

On Master2/Slave 1, edit my.cnf and master entries into it:




Mã:

   [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

log-bin                     #information for becoming master added
binlog-do-db=adam 

[mysql.server]
user=mysql
basedir=/var/lib

  [mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Step 8:

Create a replication slave account on master2 for master1:




Mã:

  mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave2';
Step 9:

Edit my.cnf on master1 for information of its master.




Mã:

  [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock


# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1




log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test

server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306

  [mysql.server]user=mysqlbasedir=/var/lib
Step 10:

Restart both mysql master1 and master2.
On mysql master1:




Mã:

  mysql> start slave;
  On mysql master2: 
  mysql > show master status;
  On mysql master 1:
  mysql> show slave status\G;
  
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.16.5
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: Mysql1MYSQL02-bin.000008
        Read_Master_Log_Pos: 410
             Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
              Relay_Log_Pos: 445
      Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
           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: 410
            Relay_Log_Space: 445
            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: 103799
1 row in set (0.00 sec)
ERROR:
No query specified
Check for the hightlighted rows, make sure its running. Now you can create tables in the database and you will see changes in slave. Enjoy!!


Theo: howtoforge



  Trả lời ngay kèm theo trích dẫn này
Gửi trả lời


Công Cụ
Xếp Bài

Quyền Hạn Của Bạn
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is Mở
Hình Cảm xúc đang Mở
[IMG] đang Mở
Mã HTML đang Tắt




Bây giờ là 02:21 AM. Giờ GMT +7



Diễn đàn tin học QuantriNet
quantrinet.com | quantrimang.co.cc
Founded by Trương Văn Phương | Developed by QuantriNet's members.
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.