mysql5.7.19 迁移数据库失败

在服务器的 mysql 版本是 mysq l5.7.19，迁移后 mysql 日志内容如下

重点是这里

2017-09-29T13:24:30.716454+08:00 60 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000002, pos: 1699), semi-sync up to file , position 0.

这说明 BINLOG 未开启，检查 BINLOG 是否开启

mysql> show variables like 'log_%'
    -> ;
+----------------------------------------+---------------------+
| Variable_name                          | Value               |
+----------------------------------------+---------------------+
| log_bin                                | OFF                 |
| log_bin_basename                       |                     |
| log_bin_index                          |                     |
| log_bin_trust_function_creators        | OFF                 |
| log_bin_use_v1_row_events              | OFF                 |
| log_builtin_as_identified_by_password  | OFF                 |
| log_error                              | /var/log/mysqld.log |
| log_error_verbosity                    | 3                   |
| log_output                             | FILE                |
| log_queries_not_using_indexes          | OFF                 |
| log_slave_updates                      | OFF                 |
| log_slow_admin_statements              | OFF                 |
| log_slow_slave_statements              | OFF                 |
| log_statements_unsafe_for_binlog       | ON                  |
| log_syslog                             | OFF                 |
| log_syslog_facility                    | daemon              |
| log_syslog_include_pid                 | ON                  |
| log_syslog_tag                         |                     |
| log_throttle_queries_not_using_indexes | 0                   |
| log_timestamps                         | UTC                 |
| log_warnings                           | 2                   |
+----------------------------------------+---------------------+
21 rows in set (0.01 sec)

发现值为 OFF

编辑 my.cnf

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

重启

service mysql.server start

报错

dump data error: [Reason: 2017-09-29 14:42:14 [ERROR] - Could not exec:select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Threads_running', return:The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56' 2017-09-29 14:42:19 [ERROR] - Could not exec:select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Threads_running', return:The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56' 2017-09-29 14:42:24 [ERROR] - Could not exec:select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Threads_running', return:The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56' 2017-09-29 14:42:29 [ERROR] - Checking MySQL workload failed, please retry later ]

解决方法是配置文件增加后重启

show_compatibility_56=ON

文章作者：阿文

文章链接： https://www.awen.me/post/27747.html