mysql复制的功能_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状态.

分享到：

2010-07-10 18:26

浏览 1254

分类:数据库

评论