實驗環(huán)境：

master and the slave server machine have the same configuration as followings:

[root@master1 ~]# uname -a

Linux master1 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux

mysql> select @@version;

+---------------------------------------+

| @@version |

+---------------------------------------+

| 5.6.19-enterprise-commercial-advanced |

+---------------------------------------+

1 row in set (0.00 sec)

master IP: 192.168.92.11

slave IP: 192.168.92.111

二，主從服務器分別作的事情

Master sever:

changes data

keeps log of changes

slave server:

ask master for events

executes events

三，復制的類型(同步|異步|半同步)

Synchronouse replication

1,data is replicated and appllied then committed

2,provides consistency ,but slower

3,provided by MySQL Cluster

Asynchronous replication

1,transactions committed immediately and replicated

2,no consistency,but faster

3,provided by MySQL Server

SemiSyncReplication

1,provided by Google

四，復制所需要的日志

Binary log的作用：

1,log every change (select 是不記錄的，只記錄改變的)

2,split into transaction groups

兩個復制相關的二進制文件：

File: master_bin.NNNNNN

1,The actual contents of the binlog

File: master_bin.index

1,an index file over the files above

五，復制所用到的線程

Master: I/O thread

Slave: I/O thread and SQL Thread

master.info contains:

1，Read coordinates: which contains master log name and master log position

2，Connection information: which contains the following two information:

a，host,user,password ,port

b，SSL keys and certificates

relay-log.info contains:

1，Group master coordinates: which contains master log name and master log position

2，Group relay log coordinates: which contains relay log name and relay log position

六，具體的實施步驟：

The following 8 Steps are need to be taken to configure the master slave replication:

1,Fix my.cnf file for master and slave

2,add user and grants on master

3,make sure the related configuration

4,on the master,use the SHOW MASTER STATUS statement to determine the current binary log file name and position:

5,load backup dump file into master

6,configure slave

7,start slave

8,check slave status show slave statusG

1，F(xiàn)ix my.cnf file for master and slave

Master configuration --required(必選擇)

log_bin = master_bin

server_id =11

配置好了后，重新啟動mysql服務

[root@master1 ~]# cd /etc/rc.d/init.d/

[root@master1 init.d]# service mysql restart

Shutting down MySQL.. [ OK ]

Starting MySQL......... [ OK ]

slave configuration --required(必選擇)

server_id=111 The master and slave must have the different server_id

配置好了后，重新啟動mysql服務

[root@slave1 init.d]# service mysql restart

Shutting down MySQL.. [ OK ]

Starting MySQL......... [ OK ]

2，add user and grants on master

mysql> CREATE USER IDENTIFIED BY 'slavepass';

mysql> GRANT REPLICATION SLAVE ON *.* TO ;

3,make sure the related configuration

show variables like 'server%';

show variables like 'log%';

show grants for ;

mysql> show variables like 'server%';

+----------------+--------------------------------------+

| Variable_name | Value |

+----------------+--------------------------------------+

| server_id | 11 |

| server_id_bits | 32 |

| server_uuid | 303c6931-0d5e-11e4-9f5c-000c29f09a2c |

+----------------+--------------------------------------+

3 rows in set (0.00 sec)

show variables like 'log%'; 看log_bin是否開啟用

mysql> show variables like 'log%';

+----------------------------------------+---------------------------------+

| Variable_name | Value |

+----------------------------------------+---------------------------------+

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/master_bin |

| log_bin_index | /var/lib/mysql/master_bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_error | /var/lib/mysql/master1.err |

| log_output | FILE |

| log_queries_not_using_indexes | OFF |

| log_slave_updates | OFF |

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| log_throttle_queries_not_using_indexes | 0 |

| log_warnings | 1 |

+----------------------------------------+---------------------------------+

13 rows in set (0.00 sec)

mysql> show grants for ;

+------------------------------------------------------------------------------------------------------------------------------+

| Grants for |

+------------------------------------------------------------------------------------------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO IDENTIFIED BY PASSWORD '*809534247D21AC735802078139D8A854F45C31F3' |

+------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

4,on the master,use the SHOW MASTER STATUS statement to determine the current binary log file name and position:

mysql> show master statusG

*************************** 1. row ***************************

File: master_bin.000001

Position: 589

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

5,load backup dump file into master

一定要先創(chuàng)建一個數(shù)據(jù)庫

mysql> create database cddl;

Query OK, 1 row affected (0.02 sec)

還原數(shù)據(jù)庫到master上：

mysql -h 192.168.92.11 -uroot -ppassword cddl< /mysql_installer/cddl20140702.sql

6,configure slave

CHANGE MASTER TO

MASTER_HOST='192.168.92.11',

MASTER_USER='repl',

MASTER_PASSWORD='slavepass',

MASTER_PORT=3306,

MASTER_LOG_FILE='master_bin.000001',

MASTER_LOG_POS=589,

MASTER_CONNECT_RETRY=10;

7,start slave

mysql> start slave;

Query OK, 0 rows affected (0.25 sec)

8,check slave status

mysql> show slave statusG;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.92.11

Master_User: repl

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: master_bin.000002

Read_Master_Log_Pos: 120

Relay_Log_File: slave1-relay-bin.000002

Relay_Log_Pos: 43341241

Relay_Master_Log_File: master_bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

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: 43341546

Relay_Log_Space: 46042813

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: 62237

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 11

Master_UUID: 24f8486c-0d8c-11e4-a088-000c29f09a2c

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: creating table

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.01 sec)

ERROR:

No query specified

從上面可以看出備庫正在做復制。

mysql> show slave statusG;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.92.11

Master_User: repl

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: master_bin.000002

Read_Master_Log_Pos: 120

Relay_Log_File: slave1-relay-bin.000003

Relay_Log_Pos: 284

Relay_Master_Log_File: master_bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

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: 120

Relay_Log_Space: 46042639

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:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 11

Master_UUID: 24f8486c-0d8c-11e4-a088-000c29f09a2c

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

ERROR:

No query specified

從上面可以看出replicate完成，至此最簡單的 master - slave配置成功。

下面測試一下主從復制：

master:

mysql> create table TT(id int ,name varchar(30));

Query OK, 0 rows affected (0.10 sec)

mysql> insert into TT VALUES (1,'FAFAFAFAFA');

Query OK, 1 row affected (0.09 sec)

mysql> insert into TT VALUES (1,'FAFAFAFAFA2');

Query OK, 1 row affected (0.04 sec)

mysql> insert into TT VALUES (3,'FAFAFAFAFA3');

Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

在slave上查詢:

mysql> select * from TT;

+------+-------------+

| id | name |

+------+-------------+

| 1 | FAFAFAFAFA |

| 1 | FAFAFAFAFA2 |

| 3 | FAFAFAFAFA3 |

+------+-------------+

3 rows in set (0.02 sec)

可以看出順利的傳到slave 庫上來了。

更多信息請查看IT技術專欄