MySQL互为主从Replication
MySQL互为主从Replication
mysql互为主从Replication一、环境
系统：CentOS x64
Mysql：Version 5.1.47
主机：A：192.168.10.101 root：bkjia.com
B：192.168.10.102 root：bkjia.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 'bkjia.com' with grant option;
B服务器：
|
MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'bkjia.com'; MySQL>flush privileges;
|
A服务器：
|
mysql>change master to master_host='192.168.10.102', master_user='replication', master_password='bkjia.com',master_log_file='mysql-bin.000005',master_log_pos=44687; MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'bkjia.com'; MySQL>flush privileges;
|
B服务器：
|
mysql>change master to master_host='192.168.10.101', master_user='replication', master_password='bkjia.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则表示状态正常
评论暂时关闭