MySQL互為主從Replication

mysql互為主從Replication
一、環(huán)境
系統(tǒng)：CentOS x64

Mysql：Version 5.1.47

主機(jī)：A：192.168.10.101   root：linuxidc.com

      B：192.168.10.102   root：linuxidc.com

二、步驟
兩臺(tái)MySQL均如要開(kāi)啟binlog日志功能，開(kāi)啟方法：加入log-bin=mysql-bin

1、主機(jī)A配置文件如下：
# 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   #是要記錄日志的數(shù)據(jù)庫(kù)

binlog-ignore-db=mysql  #是不要記錄日志的數(shù)據(jù)庫(kù)名，多個(gè)數(shù)據(jù)庫(kù)中間用逗號(hào)(,)隔開(kāi)

replicate-do-db=iccstm1

replicate-ignore-db=mysql

log-slave-updates  #表示 如果一個(gè)MASTER 掛掉的話，另外一個(gè)馬上接管；一定要加上，否則不會(huì)把更新的記錄寫(xiě)到二進(jìn)制文件里

slave-skip-errors=all   #是跳過(guò)錯(cuò)誤，繼續(xù)執(zhí)行復(fù)制操作

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1 #這樣A的auto_increment字段產(chǎn)生的數(shù)值是：1, 3, 5, 7, …等奇數(shù)ID

[client]

default-character-set=utf8

[mysqld_safe]

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

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

2、主機(jī)B配置文件如下：
[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=2

binlog-do-db=iccstm1

binlog-ignore-db=mysql

replicate-do-db=iccstm1

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

[client]

default-character-set=utf8

[mysqld_safe]

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

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

3、SQL操作
可以選擇鎖表進(jìn)一步操作，這里鎖表的目的是為了生產(chǎn)環(huán)境中不讓進(jìn)新的數(shù)據(jù)，好讓從服務(wù)器定位同步位置。初次同步完成后，記得解鎖

【1】取得master值
A服務(wù)器：

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服務(wù)器：

mysql> show master status\G

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

            File: mysql-bin.000005

        Position: 44687

    Binlog_Do_DB: iccstm1

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)
 

記住上面兩個(gè)數(shù)值，master_log_file對(duì)應(yīng)File，master_log_pos對(duì)應(yīng)Position

【2】指定同步位置（以下操作順序不可亂）
&&&要先打開(kāi)兩臺(tái)服務(wù)器的允許遠(yuǎn)程&&&

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

   B服務(wù)器：

MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'linuxidc.com';
Query OK, 0 rows affected (0.00 sec)  

MySQL>flush privileges;
Query OK, 0 rows affected (0.00 sec)

   A服務(wù)器：

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

MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'linuxidc.com';
Query OK, 0 rows affected (0.00 sec)  

MySQL>flush privileges;
Query OK, 0 rows affected (0.00 sec)

B服務(wù)器：

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

A、B服務(wù)器分別查看從服務(wù)器狀態(tài)

A服務(wù)器

mysql>start slave;

mysql>show slave status\G

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

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服務(wù)器

mysql>start slave;

mysql>show slave status\G

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

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

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

Relay_Log_Space: 688

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)
 

Slave_IO_Running: Yes

Slave_SQL_Running: Yes
 

　　查看以上兩項(xiàng)的值，均為Yes則表示狀態(tài)正常

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]