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
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 '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)
A服务器:
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';
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='xxxxx.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则表示状态正常