mysql 5.5.*下的主从模式(思路很清晰，很好)

试验环境:
服务器安装是按照张宴的环境配置的 http://blog.s135.com/nginx_php_v6/
主服务器:CentOS 5.4 Mysql 5.5.3 -m3 源码 IP:192.168.1.22
从服务器:CentOS 5.4 Mysql 5.5.3 -m3 源码 IP:192.168.1.33

配置:
一、主服务器
1.1、创建一个复制用户，具有replication slave 权限。

1. mysql>grant replication slave on *.* to ‘slave001’@’192.168.1.22′ identified by ‘slave001 ’; 

1.2、编辑my.cnf文件
vi /data0/mysql/3306/my.cnf
添加
server-id=1
并开启log-bin二进制日志文件
log-bin=mysql-bin
1.3、启动mysql数据库

1. /data0/mysql/3306/mysql start

1.4、设置读锁

1. mysql>flush tables with read lock; 

1.5、得到binlog日志文件名和偏移量

1. mysql>show master status;  
2. +——————+———-+————–+——————+  
3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
4. +——————+———-+————–+——————+  
5. | mysql-bin.0000010 | 106| | |  
6. +——————+———-+————–+——————+ 

1.6、备份要同步的数据库

1. mysqldump test1 > test1.sql 

1.7、解锁

1. mysql>unlock tables; 

二、从服务器
2.1、编辑my.cnf文件
vi /data0/mysql/3306/my.cnf
添加
server-id=2
注:需要把默认的server-id=1去掉
不要尝试把master配置属性写在my.cnf 中，5.1.7以后，mysql已经不支持这样做了
2.2、启动从数据库

1. /data0/mysql/3306/mysql start 
   

2.3、对从数据库进行相应设置

1. mysql> change master to  
2. -> master_host='192.168.1.22', 
   
3. -> master_user='slave001'，
   
4. -> master_password='slave001'， 
   
5. -> master_log_file='mysql-bin.0000010', 
   
6. -> master_log_pos=106; 

2.4、启动从服务器slave线程

1. mysql>start slave; 

执行show processlist命令显示以下进程:

1. mysql>show processlist\G  
2.  
3. *************************** 2. row ***************************  
4. Id: 2  
5. User: system user  
6. Host:  
7. db: NULL  
8. Command: Connect  
9. Time: 2579  
10. State: Has read all relay log; waiting for the slave I/O thread to update it 

Info: NULL表示slave已经连接上master，开始接受并执行日志
2.5、查看slave线程状态

1. mysql>show slave status;  
2. *************************** 1. row ***************************  
3. Slave_IO_State: Waiting for master to send event  
4. Master_Host: 192.168.1.22  
5. Master_User: repl  
6. Master_Port: 3306  
7. Connect_Retry: 60  
8. Master_Log_File: mysql-bin.0000010  
9. Read_Master_Log_Pos: 106  
10. Relay_Log_File: centos-relay-bin.000002  
11. Relay_Log_Pos: 529  
12. Relay_Master_Log_File: mysql-bin.0000010  
13. Slave_IO_Running: Yes  
14. Slave_SQL_Running: Yes  
15. Replicate_Do_DB:  
16. Replicate_Ignore_DB:  
17. Replicate_Do_Table:  
18. Replicate_Ignore_Table:  
19. Replicate_Wild_Do_Table:  
20. Replicate_Wild_Ignore_Table:  
21. Last_Errno: 0  
22. Last_Error:  
23. Skip_Counter: 0  
24. Exec_Master_Log_Pos: 106  
25. Relay_Log_Space: 830  
26. Until_Condition: None  
27. Until_Log_File:  
28. Until_Log_Pos: 0  
29. Master_SSL_Allowed: No  
30. Master_SSL_CA_File:  
31. Master_SSL_CA_Path:  
32. Master_SSL_Cert:  
33. Master_SSL_Cipher:  
34. Master_SSL_Key:  
35. Seconds_Behind_Master: 0  
36. Master_SSL_Verify_Server_Cert: No  
37. Last_IO_Errno: 0  
38. Last_IO_Error:  
39. Last_SQL_Errno: 0  
40. Last_SQL_Error:  
41. 1 row in set (0.00 sec) 

验证是否配置正确
在从服务器上执行

1. mysql> show slave status\G
2. Waiting for master to send event  
3. Slave_IO_Running: Yes  
4. Slave_SQL_Running: Yes 

如以上二行同时为Yes 说明配置成功
PS：show slave status\G 后不要添加 ； 号， 不然会出 ERROR
测试：
1、在主服务器test数据库中创建user表

1. mysql>use test1;  
2. mysql>create table user(id int); 

2、在从服务器中查看user表

1. mysql>use test1;  
2. mysql> show tables like ‘user’;  
3. +———————-+  
4. | Tables_in_test(user) |  
5. +———————-+  
6. | user |  
7. +———————-+  
8. 1 row in set (0.00 sec) 

说明主从数据同步成功。
常见问题归纳：
1.在从数据库中查看slave状态时出现
The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)
说明方从服务器里my.cnf中的server-id有相同的。
解决办法:
修改my.cnf里的server-id，并重启数据库服务。my.cnf文件默认有server-id=1
其它说明
主服务器my.cnf
#binlog-do-db=需要备份的数据库名，可写多行
#binlog-ignore-db=不需要备份的数据库名，可写多行
从服务器my.cnf
配置已经无用，要在mysql中执行

三、手动更新在主服务器上执行

1. mysql>flush tables with read lock;  
2. Query OK,rows affected (0.01 sec)  
3. mysql>show master status;  
4. +——————+———-+————–+——————+  
5. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
6. +——————+———-+————–+——————+  
7. | mysql-bin.0000011 | 260| | |  
8. +——————+———-+————–+——————

在从服务器上执行

1. mysql>select master_pos_wait(‘mysql-bin.0000011′,’260′);  
2. +————————————————–+  
3. | master_pos_wait(‘mysql-bin.0000011′,’260′) |  
4. +————————————————–+  
5. | 0 |  
6. +————————————————–+  
7. 1 row in set (0.01 sec)
   

注意，执行这条语句的时候，可能会有MySQL server has gone away 错误，木有关系，多执行几次就好了

同步完成后，在主服务器上执行解锁

1. mysql>unlock tables;
   

MySQL数据库的主从配置(多主对一从)

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://lhflinux.blog.51cto.com/1961662/528940
一、实验环境部署

主服务器 192.168.18.42 端口3306  ==》 从服务器 192.168.18.44 端口 3306  
主服务器 192.168.18.43 端口3306  ==》 从服务器 192.168.18.44 端口 3307

##数据库，已经安装mysql服务，安装部分略。从服务器上的多个mysql实例，请看另一篇帖子《用mysql_multi 实现一台机器跑多台mysql 》

二、部署服务器

1.在两台主服务器上赋予从机权限，有多台丛机，就执行多次（我们这里两台主库使用统一帐号密码）。

mysql> grant replication slave on *.* to 'backup'@'192.168.18.44' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

2.在主服务器上配置my.cnf

vi /etc/my.cnf

server-id = 1
log-bin = mysql-bin  #保证binlog可读
read-only = 0  #主机，读写都可以
#binlog-do-db = test   #需要备份数据，多个写多行，不写全部都备份
binlog-ignore-db = mysql #不需要备份的数据库，多个写多行

编辑后重启数据库 # service mysqld restart

3.配置从库服务器的my.cnf

vi /etc/my.cnf

[mysqld_multi]
mysqld = /mysql/bin/mysqld_safe
mysqladmin = /mysql/bin/mysqladmin

[mysqld1]
port    = 3306
socket  = /tmp/mysql3306.sock
pid-file = /data/mysql/data1/mysql3306.pid
datadir = /data/mysql/data1
skip-name-resolve
log-bin = mysql-bin-3306
log_slave_updates
expire_logs_days = 7
log-error = /data/mysql/data1/mysql3306.err
log_slow_queries = mysql3306-slow.log
long_query_time = 3
query_cache_size = 64M
query_cache_limit = 2M
slave-net-timeout = 10
server-id = 2                     #server id 不要与主库的重复
master-host = 192.168.18.42        #对应主库的 ip地址
master-user = backup               # slave 帐号
master-password = 123456           # 密码
master-port = 3306                 #主库端口
replicate-ignore-db=mysql           #跳过不备份的库
master-info-file = master.1842.info
master-connect-retry = 10
relay-log = relay-bin-1842          #中继日志
relay-log-index = relay-bin-1842
relay-log-info-file = relay-log-1842.info
default-character-set=gbk
innodb_data_home_dir = /data/mysql/data1
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /data/mysql/data1
innodb_buffer_pool_size = 3072M
innodb_file_per_table
innodb_open_files = 800
#innodb_flush_method = O_DIRECT
innodb_flush_method = O_DSYNC
skip-locking
key_buffer = 32M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 8M
net_buffer_length = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 32M
max_connections = 100
read_only
wait_timeout=288000
interactive_timeout=288000
log-bin-trust-function-creators=1
replicate-ignore-db = test
replicate-ignore-table = mysql.columns_priv
replicate-ignore-table = mysql.host
replicate-ignore-table = mysql.db
replicate-ignore-table = mysql.procs_priv
replicate-ignore-table = mysql.tables_priv
replicate-ignore-table = mysql.user



[mysqld2]
port    = 3307
socket  = /tmp/mysql3307.sock
pid-file = /data/mysql/data2/mysql3307.pid
datadir = /data/mysql/data2
skip-name-resolve
log-bin = mysql-bin-3307
log_slave_updates
expire_logs_days = 7
log-error = /data/mysql/data1/mysql3307.err
log_slow_queries = mysql3307-slow.log
long_query_time = 3
query_cache_size = 64M
query_cache_limit = 2M
slave-net-timeout = 10
server-id = 2                     #server id 不要与主库的重复
master-host = 192.168.18.43        #对应主库的 ip地址
master-user = backup               # slave 帐号
master-password = 123456           # 密码
master-port = 3306                 #主库端口
replicate-ignore-db=mysql           #跳过不备份的库
master-info-file = master.1843.info
master-connect-retry = 10
relay-log = relay-bin-1843
relay-log-index = relay-bin-1843
relay-log-info-file = relay-log-1843.info
default-character-set=gbk
innodb_data_home_dir = /data/mysql/data2
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /data/mysql/data2
innodb_buffer_pool_size = 3072M
innodb_file_per_table
innodb_open_files = 800
#innodb_flush_method = O_DIRECT
innodb_flush_method = O_DSYNC
skip-locking
key_buffer = 32M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 8M
net_buffer_length = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 32M
max_connections = 100
read_only
wait_timeout=288000
interactive_timeout=288000
log-bin-trust-function-creators=1
replicate-ignore-db = test
replicate-ignore-table = mysql.columns_priv
replicate-ignore-table = mysql.host
replicate-ignore-table = mysql.db
replicate-ignore-table = mysql.procs_priv
replicate-ignore-table = mysql.tables_priv
replicate-ignore-table = mysql.user

# 因为图方便就把参数全部抓出来了 对主从有用的也就标注的几个

4.配置完成后重启大从服务器
[root@localhost data1]# mysqld_multi --config-file=/etc/my.cnf --user=root --password=123456 report 1,2
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

5.在从库上进行chang master ；
1）.在date1上
mysql -uroot -p -S /tmp/mysql3306.sock

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

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.18.43',
    -> MASTER_PORT=3306,
    -> MASTER_USER='backup',
    -> MASTER_PASSWORD='123456'
Query OK, 0 rows affected (0.05 sec)

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

2）。在date2上

mysql -uroot -p -S /tmp/mysql3307.sock

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

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.18.42',
    -> MASTER_PORT=3306,
    -> MASTER_USER='backup',
    -> MASTER_PASSWORD='123456'
Query OK, 0 rows affected (0.05 sec)

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


三、验证：
1.在大从服务器上

  show slave status\G;

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

2.在主库创建数据库（18.43上）

mysql> create database haifengtest;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| haifengtest        |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

从库查看 (mysql3307.sock上）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| haifengtest        |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)
四、一种常见的问题。
在show slave status\G；时发现下面的问题，（因为我事先在从上创建了该库）
  Relay_Master_Log_File: mysql-bin.000005
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB: mysql
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1007
                 Last_Error: Error 'Can't create database 'haifengtest'; database exists' on query. Default database: 'haifengtest'. Query: 'create database haifengtest'

如果Replication在Slave上出现上面错误而停止，一般都期望Slave能忽略这个错误，继续进行同步，而不是重新启动Slave。
　这时可以使用 SQL_SLAVE_SKIP_COUNTER
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

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

在验证一次
  show slave status\G;

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

ok 搞定。。。。
本文出自 “海风的linux之路” 博客，请务必保留此出处http://lhflinux.blog.51cto.com/1961662/528940

困惑解决msyql主从同步问题

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://jooben.blog.51cto.com/253727/411539
目前从库不同步，
查看状态：
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: xxxx
                  Master_User: xxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: xxx-bin.000001
          Read_Master_Log_Pos: 1440046
               Relay_Log_File: host-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:xxx-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: xxx
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1440046
              Relay_Log_Space: 106
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

从服务器大量错误日志：
101027 16:50:58 [Note] Slave I/O thread: connected to master 'xxx@xxxxxx:3306',replication started in log 'xxx-bin.000001' at position 1440046
101027 16:50:58 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
101027 16:50:58 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236
101027 16:50:58 [Note] Slave I/O thread exiting, read up to log 'xxx-bin.000001', position 1440046
主状态：

mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 1440046
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

按照正常流程解决，应该是：
slave stop;
CHANGE MASTER TO MASTER_HOST='xxx',MASTER_USER='xx',MASTER_PASSWORD='xx',MASTER_PORT=3306,MASTER_LOG_FILE='xxx-bin.000001',MASTER_LOG_POS=1440046;
slave start;

问题依然存在，

后来经过以下步骤解决，不过有点困惑：
1、重启主库
2、给从库重新授权
3、执行以下命令
slave stop;
reset slave;
slave start;

若在从机遇到下面的错误，主要是主从同步的问题，Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

若在从机遇到下面的错误，主要是主从同步的问题，Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

可以执行一下命令

stop slave;

reset slave;

start slave；



安装成功后，我又重新安装配置了几次，发现如果没有执行reset slave，问题就无法解决。

MySQL数据库错误server_errno=2013的解决

一组MySQL复制环境中的Master意外掉电，重启后Master运行正常，但该复制环境中的其它slave端，Error Log中却抛出的如下错误信息：

110110 15:21:25 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
110110 15:21:25 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'forummysql01-bin.002937' position 243387731
110110 15:21:25 [Note] Slave: connected to master 'repl@192.168.1.31:3306',replication resumed in log 'forummysql01-bin.002937' at position 243387731
110110 15:21:25 [ERROR] Error reading packet from server:Client requested master to start replication from impossible position( server_errno=1236)
110110 15:21:25 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
110110 15:21:25 [Note] Slave I/O thread exiting, read up to log 'forummysql01-bin.002937', position 243387731
通过mysql命令行连接到slave端，执行show slave status查看复制状态：

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.31
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: forummysql01-bin.002937
Read_Master_Log_Pos: 243387731
Relay_Log_File: phpmysql02-relay-bin.33417576
Relay_Log_Pos: 243387875
Relay_Master_Log_File: forummysql01-bin.002937
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 243387731
Relay_Log_Space: 243387875
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: NULL
Salve的io线程没有运行，看起来是接收日志出现了问题，尝试启动该线程：

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

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.31
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: forummysql01-bin.002937
Read_Master_Log_Pos: 243387731
Relay_Log_File: phpmysql02-relay-bin.33417576
Relay_Log_Pos: 243387875
Relay_Master_Log_File: forummysql01-bin.002937
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 243387731
Relay_Log_Space: 243387875
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: NULL
1 row in set (0.00 sec)
看起来 没有反应，其中是有反映，执行启动io线程的命令后，Error Log文件中又抛出了日志文件位置异常的信息。看来还是得到master端，查看一下报错的日志文件指定位置到底执行的什么操作，以及该位置是否存在？

通过mysqlbinlog命令可以查看二进制日志文件中的内容，在master端执行命令如下：

[root@forummysql01 data]# mysqlbinlog --start-position=243387732 forummysql01-bin.002937
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
还别说，这个位置看起来啥都没有做，稳妥起见，三思将整个forummysql01-bin.002937文件中的内容均提取出来查看一下，再次执行mysqlbinlog命令，这次不再指定position：

[root@forummysql01 data]# mysqlbinlog ./forummysql01-bin.002937 > /home/jss/bin-002937.log
我们只需要查看一下该文件最后几行的信息即可，例如：

[root@forummysql01 data]# tail -50 /home/jss/bin-002937.log
.............................
# at 243297123
#110110 15:02:19 server id 1 end_log_pos 243297459 Query thread_id=1773644066 exec_time=0 error_code=0
SET TIMESTAMP=1294642939/*!*/;
INSERT INTO cdb_sessions (sid, ip1, ip2, ip3, ip4, uid, username, groupid, styleid, invisible, action, lastactivity, lastolupdate, seccode, fid, tid)
VALUES ('HQFzjy', '202', '160', '180', '187', '0', '', '7', '1', '0', '3', '1294642939', '0', '232485', '27', '4583')
/*!*/;
................
................
................

# at 243308840
#110110 15:02:20 server id 1 end_log_pos 243315309 Query thread_id=1773638971 exec_time=0 error_code=0
SET TIMESTAMP=1294642940/*!*/;
update group_topic set TOPIC_TIT.............................
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
可以看到该bin文件中最后的位置点是243315309，与错误日志中“'forummysql01-bin.002937', position 243387731”相差较大，提示的错误点在二进制日志文件中确实不存在，我将其理解为逻辑错误，应该是由于master意外掉电，重新启动时自动flush了binlog，而slave并未获取到这个信息导致，因此解决该问题也比较简单，直接重置同步的master位置应该就可以。这里三思选择将日志文件序号递增(也可以选择将position位置号提前)，执行命令如下：

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.101',
-> MASTER_PORT=3306,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='******',
-> MASTER_LOG_FILE='forummysql01-bin.002938',
-> MASTER_LOG_POS=0;
Query OK, 0 rows affected (0.01 sec)
然后再重新启动slave，查看状态：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.31
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: forummysql01-bin.002938
Read_Master_Log_Pos: 35910271
Relay_Log_File: phpmysql02-relay-bin.000003
Relay_Log_Pos: 21407790
Relay_Master_Log_File: forummysql01-bin.002938
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 21407646
Relay_Log_Space: 35910415
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: 2215
1 row in set (0.00 sec)
Slave相关进程已启动，Error Log文件中也没有再抛出错误信息。等待一段时间，让slave赶上master的进度，其它slave也参照此步骤操作，整个复制环境就恢复了。

mysql数据库重启失败，报错Binlog has bad magic number,谢谢了

我的数据库版本是5.1.44，操作系统是redhat
今天启动数据报错，下面是错误日志，谁能帮忙看看，谢谢了
100316 10:58:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
100316 10:58:48 [Note] Plugin 'FEDERATED' is disabled.
100316 10:58:49  InnoDB: Started; log sequence number 6 4161791115
100316 10:58:49 [ERROR] Binlog has bad magic number;  It's not a binary log file that can be used by this version of MySQL
100316 10:58:49 [ERROR] Can't init tc log
100316 10:58:49 [ERROR] Aborting

100316 10:58:49  InnoDB: Starting shutdown...
100316 10:58:54  InnoDB: Shutdown completed; log sequence number 6 4161791115
100316 10:58:54 [Note] /usr/sbin/mysqld: Shutdown complete

100316 10:58:54 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended
100316 10:59:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
100316 10:59:57 [Note] Plugin 'FEDERATED' is disabled.
100316 10:59:58  InnoDB: Started; log sequence number 6 4161791115
100316 10:59:58 [ERROR] Binlog has bad magic number;  It's not a binary log file that can be used by this version of MySQL
100316 10:59:58 [ERROR] Can't init tc log
100316 10:59:58 [ERROR] Aborting

100316 10:59:58  InnoDB: Starting shutdown...
100316 11:00:03  InnoDB: Shutdown completed; log sequence number 6 4161791115
100316 11:00:03 [Note] /usr/sbin/mysqld: Shutdown complete

100316 11:00:03 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended

[ 本帖最后由 lanbaibai 于 2010-3-16 11:56 编辑 ]
mysql, 数据库, 重启, Binlog, magic
相关帖子
本版精华
淘宝的oracle DBA们都干嘛去了请教一个5167报错中国移动（正式有编制）招J2ee/云计算/数据库/测试/IOS/安卓/大数据/运营等模拟控制文件丢失的数据库恢复ORACLE数据库迁移问题请教：数据库中没有该用户，为啥还能导出该用户下的表有多少兄弟做过从oracle到mysql的迁移？timesten应用场合的数据量不能太大，那么，处理四百万条记录，可以吗？MYSQL BETWEEN JOIN时 不走索引如下
使用道具 举报
Linux下如何用shell获取指定文件 ...  |  求助，这个代码是什么意思？  |  问一个编译源代码的问题  |  多进程下，子进程报错后如何退出 ...
lanbaibai

注册会员
初级会员
精华贴数0专家积分4 技术积分1773 社区积分0 注册时间2004-7-10
论坛徽章:1

加好友发消息
2#
 发表于 2010-3-16 17:31:18 |只看该作者
问题解决了，
檢查後依然不能開始MySQL 服務器 ……
打開 /var/log/syslog 看看有沒有以下一行
Feb 26 20:45:59 Yubis-production /etc/init.d/mysql[6254]: ^G/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
Feb 26 20:45:59 Yubis-production /etc/init.d/mysql[6254]: error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)’
Feb 26 20:45:59 Yubis-production /etc/init.d/mysql[6254]: Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists!
Feb 26 20:45:59 Yubis-production /etc/init.d/mysql[6254]:
Feb 26 20:46:04 Yubis-production mysqld_safe[6319]: started
Feb 26 20:46:04 Yubis-production mysqld[6328]: 090226 20:46:04 InnoDB: Started; log sequence number 0 43665
Feb 26 20:46:04 Yubis-production mysqld[6328]: 090226 20:46:04 [ERROR] Binlog has bad magic number; It’s not a binary log file that can be used by this version of MySQL

如果存在的話, 應該是你的 MySQL 服務器沒有正常關上, 那就 …
打開 /var/log/mysql
keithyau@Yubis-production:/var/lib$ sudo ls -l /var/log/mysql/
total 295696
-rw-rw—- 1 mysql adm 104885450 2009-02-18 12:36 mysql-bin.000077
-rw-rw—- 1 mysql adm 105175310 2009-02-24 11:27 mysql-bin.000078
-rw-rw—- 1 mysql adm 83164522 2009-02-26 19:24 mysql-bin.000079
-rw-rw—- 1 mysql adm 98 2009-02-26 19:56 mysql-bin.000080
-rw-rw—- 1 mysql adm 9233377 2009-02-27 16:10 mysql-bin.000081
-rw-rw—- 1 mysql adm 160 2009-02-26 21:08 mysql-bin.index
把最後一個 bin 檔案刪除
sudo rm /var/log/mysql/mysql-bin.00081
再打開 mysql-bin.index 把最後一行刪除
sudo vi /var/log/mysql/mysql-bin.index
/var/log/mysql/mysql-bin.000077
/var/log/mysql/mysql-bin.000078
/var/log/mysql/mysql-bin.000079
/var/log/mysql/mysql-bin.000080
/var/log/mysql/mysql-bin.000081

重新打開 MySQL 資料庫就完成了
Sudo /etc/init.d/mysql restart

Binlog has bad magic number

症状：

mysql数据库无法正常启动，日志如下：

111110 14:41:22  InnoDB: Starting shutdown...
111110 14:41:28  InnoDB: Shutdown completed; log sequence number 0 103559
111110 14:41:28 [Note] MySQL: Shutdown complete

111110 14:43:06 [Note] Plugin 'FEDERATED' is disabled.
111110 14:43:06  InnoDB: Started; log sequence number 0 103559
111110 14:43:06 [ERROR] Binlog has bad magic number;  It's not a binary log file that can be used by this version of MySQL
111110 14:43:06 [ERROR] Can't init tc log
111110 14:43:06 [ERROR] Aborting

111110 14:43:06  InnoDB: Starting shutdown...
111110 14:43:11  InnoDB: Shutdown completed; log sequence number 0 103559
111110 14:43:11 [Note] MySQL: Shutdown complete

解决办法：

删除最后一个mysql-bin.x，打开mysql-bin.index 文件，删除对应的mysql-bin.x条目，重启。（未经测试）

MySQL主从失败 错误Got fatal error 1236解决方法

由于主服务器异外重启, 导致从报错, 错误如下:

show slave status错误:

mysql> show slave status\G
Master_Log_File: mysql-bin.000288
Read_Master_Log_Pos: 627806304
Relay_Log_File: mysql-relay-bin.000990
Relay_Log_Pos: 627806457
Relay_Master_Log_File: mysql-bin.000288
Slave_IO_Running: No
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 627806304
Relay_Log_Space: 627806663

......
Last_IO_Error: Got fatal error 1236 from master when  reading data from binary log:
'Client requested master to start  replication from impossible position'

mysql错误日志:

tail /data/mysql/mysql-error.log
111010 17:35:49 [ERROR] Error reading packet from server: Client requested master
 to start replication from impossible position ( server_errno=1236)
111010 17:35:49 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data
from binary log: 'Client requested master to start replication from impossible
position', Error_code: 1236
111010 17:35:49 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000288',
position 627806304

按照习惯, 先尝试必改position位置.

mysql> stop slave;
mysql> change master to master_log_file='mysql-bin.000288',master_log_pos=627625751;
mysql> start slave;

错误依旧, 接下来登陆到主服务器查看binlog日志.
先按照错误点的标记去主服务器日志中查找:

[root@db1 ~]# mysqlbinlog --start-position=627655136 /data/mysql/binlog/mysql-bin.000288
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111010 13:31:19 server id 4 end_log_pos 106 Start: binlog v 4, server v 5.1.45-log
created 111010 13:31:19
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
F1aTTg8EAAAAZgAAAGoAAAABAAQANS4xLjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

没有看到这个位置.

[root@db1 ~]# mysqlbinlog /data/mysql/binlog/mysql-bin.000288 > test.txt

less text.txt
看最后一部分
# at 627625495
#111010 16:35:46 server id 1 end_log_pos 627625631 Query thread_id=45613333
exec_time=32758 error_code=0
SET TIMESTAMP=1318289746/*!*/;
delete from freeshipping_bef_update where part='AR-4006WLM' and code=''
/*!*/;
# at 627625631
#111010 16:35:46 server id 1 end_log_pos 627625751 Query thread_id=45613333
exec_time=32758 error_code=0
SET TIMESTAMP=1318289746/*!*/;
delete from shippingFee_special where part='AR-4006WLM'
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

找到最接近错误标记627655136的一个position是627625631.

再回到slave机器上change master, 将postion指向这个位置.

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

mysql> change master to master_log_file='mysql-bin.000288',master_log_pos=627625631;
Query OK, 0 rows affected (0.06 sec)

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

再次查看

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 192.168.21.105
Master_User: rep
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000289
Read_Master_Log_Pos: 25433767
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 630
Relay_Master_Log_File: mysql-bin.000289
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主从同步正常了, 同样的方法修复其它slave机器.

mysqld 启动时出错，日志显示

　ERROR] Binlog has bad magic number;  It's not a binary log file that can be used by this version of MySQL

　处理方法

　删除mysql－bin日志最后一条记录，并在mysql.bin-index 中删除相应的记录