mysql互為主從Replication

mysql互為主從Replication

系統：CentOS x64

Mysql：Version 5.1.47

主機：A：192.168.10.101   root：xxxxx.com

     B：192.168.10.102   root：xxxxx.com

兩台MySQL均如要開啟binlog日志功能，開啟方法：加入log-bin=mysql-bin

# vi /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/lib/mysql/mysql.err

log = /var/lib/mysql/query_log.log

log-slow-queries =   /var/lib/mysql/slow_query_log.log

user=mysql

default-character-set=utf8

init_connect='SET NAMES utf8'

# Disabling symbolic-links is recommended to   prevent assorted security risks

symbolic-links=0

log-bin=mysql-bin

server-id=1

binlog-do-db=iccstm1   #是要記錄日志的資料庫

binlog-ignore-db=mysql  #是不要記錄日志的資料庫名，多個資料庫中間用逗号(,)隔開

replicate-do-db=iccstm1

replicate-ignore-db=mysql

log-slave-updates    #表示如果一個MASTER 挂掉的話，另外一個馬上接管；一定要加上，否則不會把更新的記錄寫到二進制檔案裡

slave-skip-errors=all   #是跳過錯誤，繼續執行複制操作

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1 #這樣A的auto_increment字段産生的數值是：1,   3, 5, 7, …等奇數ID

[client]

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

log-slow-queries = /var/lib/mysql/slow_query_log.log

# Disabling symbolic-links is recommended to prevent assorted   security risks

server-id=2

binlog-do-db=iccstm1

binlog-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

auto_increment_offset=2

可以選擇鎖表進一步操作，這裡鎖表的目的是為了生産環境中不讓進新的資料，好讓從伺服器定位同步位置。初次同步完成後，記得解鎖

A伺服器：

mysql> show master   status\G

***************************   1. row ***************************

File: mysql-bin.000007

       Position: 42617

   Binlog_Do_DB: iccstm1

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

B伺服器：

mysql> show master status\G

*************************** 1. row   ***************************

File: mysql-bin.000005

       Position: 44687

     Binlog_Do_DB: iccstm1

1 row in set   (0.00 sec)

記住上面兩個數值，master_log_file對應File，master_log_pos對應Position

&&&要先打開兩台伺服器的允許遠端&&&

Mysql> grant all privileges on *.* to 'root'@'%' identified by 'xxxxx.com' with grant option;

  B伺服器：

MySQL>   grant replication slave on *.* to 'replication'@'%' identified by   'xxxxx.com';

Query OK, 0 rows affected (0.00 sec)  

MySQL>flush   privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>change   master to master_host='192.168.10.102', master_user='replication',   master_password='xxxxx',master_log_file='mysql-bin.000005',master_log_pos=44687;

MySQL>   grant replication slave on *.* to 'replication'@'%' identified by   'shecs.com';

mysql>change   master to master_host='192.168.10.101', master_user='replication',   master_password='xxxxx.com',master_log_file='mysql-bin.000007',master_log_pos=42617;

A、B伺服器分别檢視從伺服器狀态

A伺服器

mysql>start slave;

mysql>show slave status\G

Slave_IO_State:   Waiting for master to send event

Master_Host:   192.168.10.102

Master_User:   replication

Master_Port:   3306

Connect_Retry:   60

Master_Log_File:   mysql-bin.000007

Read_Master_Log_Pos:   42617

Relay_Log_File:   mysqld-relay-bin.000002

Relay_Log_Pos:   46592

Relay_Master_Log_File:   mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:   iccstm1

Replicate_Ignore_DB:   mysql

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:   42617

Relay_Log_Space:   40748

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:

1   row in set (0.00 sec)

B伺服器

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.10.101

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 44687

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 532

Relay_Master_Log_File: mysql-bin.000005

Replicate_Do_DB: iccstm1

Replicate_Ignore_DB: mysql

Last_Errno: 0

Skip_Counter: 0

Exec_Master_Log_Pos: 44687

Relay_Log_Space: 688

Until_Condition: None

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_SQL_Errno: 0

　　檢視以上兩項的值，均為Yes則表示狀态正常