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状态.