Chinaunix首页 | 论坛 | 博客
  • 博客访问: 201571
  • 博文数量: 87
  • 博客积分: 10
  • 博客等级: 民兵
  • 技术积分: 840
  • 用 户 组: 普通用户
  • 注册时间: 2012-07-18 21:12
文章分类

全部博文(87)

文章存档

2014年(86)

我的朋友

分类: Mysql/postgreSQL

2014-05-19 17:13:04

参考:http://blog.csdn.net/mchdba/article/details/25738991
http://blog.chinaunix.net/uid-20639775-id-1630187.html
大家的做法都是从新做从库,或者换个master接入点。

1、通过show slave status\G;slave上找到大致的log文件名和位置,然后到master上的ndb_binlog_index表上找到和该log名和位置最 近的epoch
2
、根据前面的epoch到另外一个SQL节点中找到日志文件和位置,比如:
SELECT POSITION,FILE FROM ndb_binlog_index WHERE epoch='60086592471055';
3
、将master上的ndb_binlog_index和另一个sql的这个表在该epoch附近进行对比!验证数据的正确性!
4
、然后再更改slave上的master_host,master_log_filemaster_log_pos从另外一个SQL节点做同步,比如 采用如下命令:

change master to master_host='192.168.3.224',master_user='replication',master_password='123456',
master_log_file='mysql-bin.000021', master_log_pos=1012;

start slave;


mysql> show slave status;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error                                                                               | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error                                                                           | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
| Waiting for master to send event | xx.xx.xx.xx | repl        |        3306 |            60 | mysql-bin.000012 |                 637 | mysqld-relay-bin.000026 |          1970 | mysql-bin.000011      | Yes              | No                |                 |                     |                    |                        |                         |                             |       1590 | The incident LOST_EVENTS occured on the master. Message: error writing to the binary log |            0 |                6850 |            8435 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |           1590 | The incident LOST_EVENTS occured on the master. Message: error writing to the binary log |                             |               55 | e4079c0a-cf4a-11e3-8a94-597645f6032b | /data/mysql/master.info |         0 |                NULL |                         |              86400 |             |                         | 140519 10:45:17          |                |                    |                    |                   |             0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
1 row in set (0.00 sec)


mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)


mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)


mysql> CHANGE MASTER TO   MASTER_HOST='xx.xx.xx.xx',  MASTER_USER='repl',   MASTER_PASSWORD='***',  MASTER_LOG_FILE='mysql-bin.000012',   MASTER_LOG_POS=637; 
Query OK, 0 rows affected, 2 warnings (0.00 sec)


mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


mysql> show slave status;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                                     | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
| Waiting for master to send event |xx.xx.xx.xx    | repl        |        3306 |            60 | mysql-bin.000012 |                 637 | mysqld-relay-bin.000002 |           283 | mysql-bin.000012      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 637 |             457 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |               55 | e4079c0a-cf4a-11e3-8a94-597645f6032b | /data/mysql/master.info |         0 |                NULL | Slave has read all relay log; waiting for the slave I/O thread to update it |              86400 |             |                         |                          |                |                    |                    |                   |             0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
1 row in set (0.00 sec)

阅读(1169) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~