|
|
楼主 |
发表于 2015-12-22 21:31
|
显示全部楼层
MySQL互为主从Replication
[日期：2012-07-31] 来源：Linux社区 作者：tonychiu [字体：大 中 小]
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则表示状态正常 |
|
匿名
发表于 2015-11-10 16:49
发表于 2015-11-1 18:22