mysql 不记录binglog

cccc0912 发布于 2014/01/13 15:30
阅读 457
收藏 1

mysql搭建主从,但是主服务器mysql   不记录binglog日志,主配置文件如下:

[client]
port = 3306
socket = /data/mysql/mysqld.sock
[mysqld]
port = 3306
socket = /data/mysql/mysqld.sock


character-set-server = utf8
default-storage-engine = InnoDB
net_read_timeout=60
net_write_timeout=90
skip-name-resolve
back_log = 100
max_connections = 1000
max_connect_errors = 10
table_open_cache = 1024
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
tmp_table_size = 64M
slow_query_log
long_query_time = 6
read_rnd_buffer_size = 16M
lower_case_table_names=1

skip-external-locking
key_buffer_size = 384M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
log-bin=mysql-bin
max_binlog_size = 100M
server-id = 1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-do-db=seven,upairs,upairs_2,upairs_3,upairs_4
binlog-ignore-db=mysql
expire_logs_days = 90

[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout


log相关variables值:

| Variable_name                           | Value                        |
+-----------------------------------------+------------------------------+
| back_log                                | 100                          |
| binlog_cache_size                       | 2097152                      |
| binlog_direct_non_transactional_updates | OFF                          |
| binlog_format                           | STATEMENT                    |
| binlog_stmt_cache_size                  | 32768                        |
| expire_logs_days                        | 90                           |
| general_log                             | OFF                          |
| general_log_file                        | /data/mysql/server5.log      |
| innodb_flush_log_at_trx_commit          | 1                            |
| innodb_locks_unsafe_for_binlog          | OFF                          |
| innodb_log_buffer_size                  | 8388608                      |
| innodb_log_file_size                    | 5242880                      |
| innodb_log_files_in_group               | 2                            |
| innodb_log_group_home_dir               | ./                           |
| innodb_mirrored_log_groups              | 1                            |
| log                                     | OFF                          |
| log_bin                                 | ON                           |
| log_bin_trust_function_creators         | OFF                          |
| log_error                               | /data/mysql/server5.err      |
| log_output                              | FILE                         |
| log_queries_not_using_indexes           | OFF                          |
| log_slave_updates                       | OFF                          |
| log_slow_queries                        | ON                           |
| log_warnings                            | 1                            |
| max_binlog_cache_size                   | 18446744073709547520         |
| max_binlog_size                         | 104857600                    |
| max_binlog_stmt_cache_size              | 18446744073709547520         |
| max_relay_log_size                      | 0                            |
| relay_log                               |                              |
| relay_log_index                         |                              |
| relay_log_info_file                     | relay-log.info               |
| relay_log_purge                         | ON                           |
| relay_log_recovery                      | OFF                          |
| relay_log_space_limit                   | 0                            |
| slow_query_log                          | ON                           |
| slow_query_log_file                     | /data/mysql/server5-slow.log |
| sql_log_bin                             | ON                           |
| sql_log_off                             | OFF                          |
| sync_binlog                             | 1                            |
| sync_relay_log                          | 0                            |
| sync_relay_log_info                     | 0                            |

加载中
0
c
cccc0912

补充: binglog几乎都只有以下内容

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140113 15:10:01 server id 1  end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 140113 15:10:01 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
SZHTUg8BAAAAZwAAAGsAAAABAAQANS41LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABJkdNSEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

0
eechen
eechen
在/etc/mysql/my.cnf里给log_bin配个绝对路径(确保MySQL有权限写入),重启MySQL服务试试:

log_bin = /var/log/mysql/mysql-bin

0
喂码呢
喂码呢

引用来自“cccc0912”的答案

补充: binglog几乎都只有以下内容

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140113 15:10:01 server id 1  end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 140113 15:10:01 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
SZHTUg8BAAAAZwAAAGsAAAABAAQANS41LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABJkdNSEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

修改一下binlog_format
0
八宝旗
八宝旗
binlog-do-db=seven,upairs,upairs_2,upairs_3,upairs_4

binlog-ignore-db=mysql

还有检查下是不是在binlog-do-db中配置的库下进行的操作

最好先use database

0
netjourney
netjourney
# Warning: this binlog is either in use or was not closed properly.
返回顶部
顶部