mysql 实现数据同步
用于工作需要 今天研究了下mysql 的主从数据同步
master :
mysql: 5.0.26-community-nt-log
ip :192.168.4.100
slave:
mysql:5.5.19
ip:192.168.17.129
master
在my.cnf 中的
[mysqld]server-id=2replicate-do-db=test 要同步的库read-only=1 slave-net-timeout=10 relay-log-index = /data/mysql/relaylog/relaylogrelay-log-info-file = /data/mysql/relaylog/relaylogrelay-log = /data/mysql/relaylog/relaylog如果 /data/mysql/relaylog 目录不存在 则需要创建，并赋给 mysql 用户 读写权限 不然在start slave 的时候 会报 error:13d的权限错误[root@localhost relaylog]# /etc/init.d/mysql restartmysql 5.5 以后貌似不能支持直接在 my.cnf 里面修改master 相关的参数 采用如下sql 执行 mysql> change master to master_user='slave001'; mysql> change master to master_password='123456'; 此处需要和show master status\G; 对应mysql> change master to master_log_file='mysql-bin.000001';mysql> change master to master_log_pos='6908'; mysql> start slave; mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.4.100 Master_User: slave001 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 6908 Relay_Log_File: relaylog.000657 Relay_Log_Pos: 244 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test 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: 6908 Relay_Log_Space: 530 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: 0Master_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 row in set (0.03 sec)正常 OK 更新test 表中的数据时 二进制文件在变化有 6908-----7478如果在建立slave 的时候 master Position 发送变化 需要reset slave 后从新 按照上面的方法 重建 [root@localhost relaylog]# cat relaylog/data/mysql/relaylog/relaylog.000679814mysql-bin.0000017478二进制的文件为的位置为 6908 一致
