MySQL互為主從Replication |
發(fā)布時間: 2012/9/6 18:03:00 |
mysql互為主從Replication Mysql:Version 5.1.47 主機:A:192.168.10.101 root:linuxidc.com B:192.168.10.102 root:linuxidc.com 二、步驟 1、主機A配置文件如下: [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 #是跳過錯誤,繼續(xù)執(zhí)行復制操作 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配置文件如下: 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值 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'; MySQL>flush privileges;
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'; MySQL>flush privileges;
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服務器分別查看從服務器狀態(tài) 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則表示狀態(tài)正常 本文出自:億恩科技【mszdt.com】 |