Mysql的复制功能

Mysql复制的功能主要由2个线程完成，IO线程和SQL线程。IO线程负责从Master读需要执行的命令， SQL线程执行备份操作。

 

在Slave端的管理主要有如下几个命令：

 

1。CHANGE MASTER TO == 配置连接到那个Master服务器, 连接的用户名, 密码, MASTER-bin log的文件名, 开始位置等.都由这个参数设置.

 

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=0;

 

完整的语法参考: http://dev.mysql.com/doc/refman/5.1/en/change-master-to.html

 

2. START SLAVE == 启动一个slave 线程. 可以把IO线程和SQL线程分开启动.

 

START SLAVE SQL_THREAD UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE = 'mysql-bin.000018', RELAY_LOG_POS = 0

 

这个UNTIL 参数表示运行到某个位置SLAVE就停止.

 

3. STOP SLAVE == 停止slave进程.

 

4. Show slave staut \G == 显示Slave进程的状态.

 

 

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.56.117.81
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000018

          Read_Master_Log_Pos: 392370385
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 18358522
        Relay_Master_Log_File: mysql-bin.000018


             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: ipata2.rpt_chart_test_log,ipata2.cron_task,ipata2                                                                                                 .rpt_report_case_list,ipata2.exchange_table_bak,ipata2.exchange_table,ipata2.cro                                                                                                 n_task_history
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: ipata2.rpt\_%temp\_charting\_thread%,ipata2.rpt\_                                                                                                 %temp\_chart\_id%,ipata2.rpt\_%_temp
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 392370385
              Relay_Log_Space: 18358678
              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:
1 row in set (0.00 sec)

 

5. 在my.cnf里面的配置选项:

 

replicate-ignore-table = ipata2.rpt_chart_test_log


replicate-ignore-table = ipata2.rpt_report_case_list
replicate-wild-ignore-table= ipata2.rpt\_%temp\_charting\_thread%
replicate-wild-ignore-table= ipata2.rpt\_%_temp 



master-host     =   10.56.117.81
master-user     =   backup
master-password =   backup

slave-skip-errors=1062,1050,1053  <== 忽略的MYSQL错误代码,不然复制的时候遇到错误,SQL线程就停止

 

6. 错误处理, 操作前一定要记录当前的MASTER log的位置:

 

 Relay_Master_Log_File: mysql-bin.000018


 Exec_Master_Log_Pos: 392370385

 

7. 跳过某次错误:

 

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;

 

8. REST SLAVE == 重置Slave状态.