MySQL互為主從Replication

mysql互為主從Replication
一、環境
系統：CentOS x64

Mysql：Version 5.1.47

主機：A：192.168.10.101   root：linuxidc.com

      B：192.168.10.102   root：linuxidc.com

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

1、主機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   #是要記錄日志的數據庫

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]

default-character-set=utf8

[mysqld_safe]

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

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

2、主機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操作
可以選擇鎖表進一步操作，這里鎖表的目的是為了生產環境中不讓進新的數據，好讓從服務器定位同步位置。初次同步完成后，記得解鎖

【1】取得master值
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

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)
 

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

【2】指定同步位置（以下操作順序不可亂）
&&&要先打開兩臺服務器的允許遠程&&&

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

   B服務器：

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服務器：

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服務器：

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服務器分別查看從服務器狀態

A服務器

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服務器

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
 

　　查看以上兩項的值，均為Yes則表示狀態正常

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]