a、启用二进制日志
--当前环境
root@localhost[(none)]> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.5.39 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.39 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
root@localhost[(none)]> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF | --该参数用于设定是否启用二进制日志
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
--以下为binary log相关参数
root@localhost[(none)]> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 0 |
+-----------------------------------------+----------------------+
--当前mysql服务器数据文件的缺省位置
root@localhost[(none)]> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
--停止mysql服务器
SUSE11b:~ # service mysql stop
Shutting down MySQL.... done
--编辑my.cnf来设定binary log日志位置(注，配置二进制日志路径及文件名后，系统变量log_bin被自动置为on)
suse11b:~ # vi /etc/my.cnf
suse11b:~ # grep -v ^# /etc/my.cnf
[mysqld]
log-error=/tmp/suse11b.err
log_bin=/var/lib/mysql/binarylog/binlog
suse11b:~ # mkdir -p /var/lib/mysql/binarylog
suse11b:~ # chown -R mysql:mysql /var/lib/mysql/binarylog
suse11b:~ # /etc/init.d/mysql start
Starting MySQL.. done
suse11b:~ # ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 39 Oct 3 13:41 /var/lib/mysql/binarylog/binlog.index #索引文件
-rw-rw---- 1 mysql mysql 107 Oct 3 13:41 /var/lib/mysql/binarylog/binlog.000001 #日志文件
b、切换日志
suse11b:~ # mysql -uroot -pxxx
root@localhost[(none)]> flush logs;
Query OK, 0 rows affected (0.04 sec)