环境配置：
A：192.168.11.11 B：192.168.11.12 mysql> select version(); +------------+ | version() | +------------+ | 5.5.12-log | +------------+ CentOS release 5.4 (Final)主服务器配置
vi /etc/my.cnf # Replication Master Server (default) server-id = 1//服务器ID号，整数值，保证唯一标识一台服务器就可以 log-bin=mysql-bin //打开二进制日志 binlog-do-db=repl //需要同步的数据库，如果没有本行，即表示同步所有的数据库 binlog-ignore-db=mysql //忽略mysql数据库复制#主主需加入的部分
log-slave-updates sync_binlog=1 auto_increment_offset=1 auto_increment_increment=2 replicate-do-db = test replicate-ignore-db = mysql#mysql -uroot -pivgivgivg //登陆
mysql>grant replication slave on *.* to identified by 'ivgivgivg';//在master机上为slave机添加一同步帐号 mysql>flush privileges; //生效 重启master机的mysql服务：service mysqld restart #mysql -uroot -pivgivgivg //登陆mysql> flush tables with read lock;
mysql>show master status //查看主日志情况 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 107 | test | mysql | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql>unlock tables; 注：这里锁表的目的是为了生产环境中不让进新的数据，好让从服务器定位同步位置。初次同步完成后，记得解锁。 主2服务器配置 vi /etc/my.cnf# Replication Slave
server-id=2 log-bin=mysql-bin replicate-do-db=test replicate-ignore-db=mysql #主主需加入的部分 log-slave-updates sync_binlog=1 auto_increment_offset=2 auto_increment_increment=2 binlog-do-db=test binlog-ignore-db=mysql#mysql -uroot -pivgivgivg //登陆
mysql>grant replication slave on *.* to identified by 'ivgivgivg';//在master机上为slave机添加一同步帐号 mysql>flush privileges; //生效 重启master机的mysql服务：service mysqld restart #mysql -uroot -pivgivgivg //登陆 mysql>show master status //查看服务器情况 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 107 | test | mysql | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)主1服务器再配置:
#mysql -uroot -pivgivgivg //登陆 mysql> change master to master_host='192.168.11.12',master_user='copy',master_password='ivgivgivg',master_log_file='binlog.000005', master_log_pos=107; ;//配置连接主2服务器 mysql> start slave;//启动从服务线程主2服务器再配置:
#mysql -uroot -pivgivgivg //登陆 mysql> change master to master_host='192.168.11.11',master_user='copy',master_password='ivgivgivg',master_log_file='binlog.000003', master_log_pos=107;//配置连接主1服务器 mysql> start slave;//启动从服务线程注：master_log_file，master_log_pos由上面主服务器查出的状态值中确定。master_log_file对应File，master_log_pos对应Position。
分别登陆2台数据库， mysql> show slave statusG;//查看从服务器状态 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.11 Master_User: copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 107 Relay_Log_File: node2-relay-bin.000003 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test 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: 107 Relay_Log_Space: 555 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: 1 1 row in set (0.00 sec)ERROR:
No query specifiedSlave_IO_Running: Yes
Slave_SQL_Running: Yes这俩个都是也是就表示正常