MySQL Replication 主备配置详细说明

环境配置：
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
在 Logging and Replication 后面增加:
# Replication Master Server (default)
server-id = 1//服务器ID号，整数值，保证唯一标识一台服务器就可以 
log-bin=mysql-bin //打开二进制日志 
binlog-do-db=repl //需要同步的数据库，如果没有本行，即表示同步所有的数据库
binlog-ignore-db=mysql //忽略mysql数据库复制
#mysql -uroot -pivgivgivg  //登陆
mysql>grant replication slave on *.* to 'copy'@'192.168.11.12' 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.000002 |      107  | test  | mysql            |
+------------------+----------+--------------+------------------+
从服务器配置
mysql5.1.7版本以后已经不支持把master配置属性写入my.ini文件中了，只需把同步的数据库和需要忽略的数据库写入
vi /etc/my.cnf
# Replication Slave
server-id=2
log-bin=mysql-bin
replicate-do-db=test
replicate-ignore-db=mysql
重启mysql服务：service mysqld restart
#mysql -uroot -pivgivgivg  //登陆
mysql> stop slave;  //停止从服务线程
mysql> change master to master_host='192.168.11.11',master_user='copy',master_password='ivgivgivg'; //配置连接主服务器配置
mysql> start slave;//启动从服务线程
mysql> show slave status\\G;//查看从服务器状态
*************************** 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 specified
-------------------------------------------------------
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这俩个都是yes就表示正常
对于已经存在的库的操作:
主数据库机:
登陆mysql,主机阻断写操作:
mysql>FLUSH TABLES WITH READ LOCK;
查看主服务器的状态:
mysql>SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |  1529881 | openser      | mysql,test       |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
导出需要同步的库:
mysqldump -u root -p123456 --opt -R openser > openser20121203.sql
解锁:
mysql> UNLOCK TABLES;
把备份好的库传到备用数据库
备数据库机:
登陆mysql,创建同名库:
mysql> CREATE DATABASE openser DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
导入数据库:
mysql -u root -p123456 openser < openser20121127.sql
mysql>SLAVE STOP;
mysql>reset slave;
mysql>CHANGE MASTER TO MASTER_HOST='192.168.21.26',
mysql>MASTER_USER='repl_user',
mysql>MASTER_PASSWORD='123456',
mysql>MASTER_LOG_FILE='mysql-bin.000002',
mysql>MASTER_LOG_POS=1529881;
mysql>start slave;
mysql>show slave status\\G