mysql复制 relay-log问题

songpure520

我这是在slave机器上
日志里出现如此错误：

[root@server2 mysqld]# tail /var/log/mysqld.log

081020 00:12:44  mysqld ended

081020 00:24:15  mysqld started
081020  0:24:16 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
081020  0:24:16 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.00001' (relay_log_pos 236)
081020  0:24:16 [ERROR] Could not find target log during relay log initialization
081020  0:24:16 [ERROR] Failed to initialize the master info structure
081020  0:24:16 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

说不能打开mysqld-relay-bin.00001 但是我有这个文件

[root@server2 mysqld]# ll /var/run/mysqld/mysqld-relay-bin.000001
-rw-rw---- 1 mysql mysql 117 Oct 19 21:59 /var/run/mysqld/mysqld-relay-bin.000001

mysql> show slave status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+----------------+--------------+-------------+-------------+---------------+-----------------+---------------------+------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| 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 |
+----------------+--------------+-------------+-------------+---------------+-----------------+---------------------+------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
|                | 192.168.0.86 | bakup       |        3306 |            10 |                 |                   4 | mysqld-relay-bin.00001 |           236 | mysqld-bin.000001     | No               | No                | test,test       |                     |                    |                        |                         |                             |          0 |            |            0 |                  98 |               0 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL |
+----------------+--------------+-------------+-------------+---------------+-----------------+---------------------+------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.01 sec)

这到底如何解决啊！！

[ 本帖最后由 songpure520 于 2008-10-20 15:04 编辑 ]

songpure520

我在slave上删了/var/lib/mysql下的所有日志文件，包括master.info relay-log.info 等等，然后敲了reset slave;就没有出现那种日志呢

[ 本帖最后由 songpure520 于 2008-10-22 16:18 编辑 ]

yueliangdao0608

重新CHNAGE MASTER TO 一下就可以了。

songpure520

现在日志里出现：

081020 18:02:11 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
081020 18:02:11 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './--log-bin.000001' position: 4
081020 18:02:11 [Note] Slave I/O thread: connected to master 'bakup@192.168.0.86:3306',  replication started in log 'FIRST' at position 4
081020 18:03:17 [Note] Slave I/O thread killed while reading event
081020 18:03:17 [Note] Slave I/O thread exiting, read up to log 'mysqld-bin.000001', position 98
081020 18:03:17 [Note] Error reading relay log event: slave SQL thread was killed
081020 18:03:19 [Note] Slave SQL thread initialized, starting replication in log 'mysqld-bin.000001' at position 98, relay log './--log-bin.000002' position: 236
081020 18:03:19 [Note] Slave I/O thread: connected to master 'bakup@192.168.0.86:3306',  replication started in log 'mysqld-bin.000001' at position 98

[ 本帖最后由 songpure520 于 2008-10-21 10:33 编辑 ]

yueliangdao0608

原帖由 songpure520 于 2008-10-21 10:31 发表
现在日志里出现：

手动编辑relay-log

songpure520

现在的情况是这样。。。。。
主服务器：

mysql> show processlist;
+----+-------+---------------+------+-------------+------+----------------------------------------------------------------+------------------+
| Id | User  | Host          | db   | Command     | Time | State                                                          | Info             |
+----+-------+---------------+------+-------------+------+----------------------------------------------------------------+------------------+
|  8 | bakup | server2:39802 | NULL | Binlog Dump |  217 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |
|  9 | root  | localhost     | NULL | Query       |    0 | NULL                                                           | show processlist |
+----+-------+---------------+------+-------------+------+----------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

[root@server1 mysqld]# tail /var/log/mysqld.log
Version: '5.0.45-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
081020 18:00:52 [Note] /usr/libexec/mysqld: Normal shutdown

081020 18:00:52 [Note] /usr/libexec/mysqld: Shutdown complete

081020 18:00:52  mysqld ended

081020 18:08:19  mysqld started
081020 18:08:19 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

从服务器：

mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                 | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |  202 | Waiting for master to send event                                      | NULL             |
|  2 | system user |           | NULL | Connect |  202 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  6 | root        | localhost | NULL | Query   |    0 | NULL                                                                  | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

081020 20:06:51  mysqld started
081020 20:06:51 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
081020 20:06:51 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
081020 20:06:51 [Note] Slave I/O thread: connected to master 'bakup@192.168.0.86:3306',  replication started in log 'mysqld-bin.000001' at position 98
081020 20:06:51 [Note] Slave SQL thread initialized, starting replication in log 'mysqld-bin.000001' at position 98, relay log './--log-bin.000007' position: 236

先在问题是从服务器并没有同步成功！！我在主服务器增加一个表，从服务器并没有同步过来！！不知道为什么，但是所有状态都显示没有什么新事件发生。

[ 本帖最后由 songpure520 于 2008-10-21 14:22 编辑 ]

songpure520

081020 18:03:17 [Note] Error reading relay log event: slave SQL thread was killed  为什么会产生读出中继日志的SQL线程会kill！！

[ 本帖最后由 songpure520 于 2008-10-22 18:00 编辑 ]

songpure520

两边也不同步，但是没有任何的错误日志显示

用 LOAD DATA FROM MASTER；命令出现mysql>  LOAD DATA FROM MASTER;
ERROR 1219 (HY000): Error running query on master: Access denied; you need the RELOAD privilege for this operation
错误，提示我没权限，但是我的用户已经给了权限了！

mysql> show grants for bakup@192.168.0.85;
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for bakup@192.168.0.85                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, FILE, SUPER, REPLICATION SLAVE ON *.* TO 'bakup'@'192.168.0.85' IDENTIFIED BY PASSWORD '4e469e355b706e6f' |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[ 本帖最后由 songpure520 于 2008-10-22 18:03 编辑 ]

guangzidao

今天也遇到这个问题，居然都没人回答 ？

Coolriver

第一，你删掉了master.info，replay.info你的同步节点已经乱了，你的Master在有写入的情况，很难找到Binlog同步到那里。所以简单的Change master to只能得到一个看似同步，但实际数据很难保证。

如果数据量不大的情况，可以重建Slave。
另一个方法，前提需要Slave开启同步日值写入log_slave_updates. 查看Slave的Binlog执行情况，和Master的做对比，找到最后同步节点，然后利用Change master to重新执行同步。