配置MySQL Replication

-> MASTER_LOG_POS=228;
Query OK, 0 rows affected (0.01 sec)

注意到这里master_log_file和master_log_pos就是前面show master status的结果。

启动复制进程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

至此配置基本完成，在主库解开table的锁定
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

创建测试table，插入数据
mysql> use test
Database changed

mysql> create table testrep(i int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into testrep values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

然后再到从库查询
mysql> select * from testrep;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

可见数据已经从主库复制到从库。

在从库的数据目录下，有几个和复制相关的文件需要说明一下：
*-reloay-bin.* 从主库同步过来的Bin log文件，也叫中继日志
master.info 主库帐号信息和同步信息，这里记录了复制用户名和密码，需要保护好权限。
relay-log.info 跟踪执行同步过来的Bin log的执行情况

通过show processlist可以查看主从库用于复制的相关进程(在windows上实际实现为线程)的信息
主库：
mysql> show processlistG
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost:3736
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 2
User: rep
Host: localhost:3745
db: NULL
Command: Binlog Dump
Time: 68
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
2 rows in set (0.00 sec)

可以看到Id为2的进程是用于复制的进程，state可用于监控复制的状态，具体含义参考官方文档。

从库：
mysql> show processlistG
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost:3741
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 88
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 51
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)

可以看到从库启动了两个复制进程，一个用于和主库交互，取得日志，另外一个则用于应用日志到从库。MySQL的复制主要是通过解析主库的二进制日志，然后再在从库应用来实现的。这种方式和Oracle Streams的本质思想是一致的。通过MySQL自带的工具mysqlbinlog，可以dump出二进制日志中的具体内容，实际上就是一条条的sql语句：
D:Program FilesMySQLMySQL Server 5.0data>mysqlbinlog mysql-bin.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#071201 13:17:31 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.37-community-nt-log created 071201 13:17
:31 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
# at 98
#071201 13:18:43 server id 1 end_log_pos 228 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1196486323