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则表示状态正常