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]