MySQL 5.6.12切换binlog二进制日志路径

前言：
有一个mysql学生说他们因为binlog产生太大了，需要把日志路径放到另外的磁盘上面去，问我有啥时机的操作方案，share弄了一个mysql的binlog的日志路径切换的例子给他。正好今天有空，就拿mysql5.6.12来做个实例，给大家演示一下。

1，查看binlog地址

[root@mysql5612 ~]# more /usr/local/mysql/my.cnf |grep log-binlog-bin =/home/data/mysql/binlog/mysql-bin.log[root@mysql5612 ~]#

2，验证binlog的正常使用

[root@mysql5612 binlog]# pwd/home/data/mysql/binlog[root@mysql5612 binlog]# mysqlWarning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.Commands end with ; or g.Your MySQL connection id is 390217Server version: 5.6.12-log Source distributionCopyright （c） 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type "help;" or "h" for help. Type "c" to clear the current input statement.mysql> create table z2 select 2 as a;ERROR 1046 （3D000）: No database selectedmysql> create table test.z2 select 2 as a;Query OK, 1 row affected （0.04 sec）Records: 1Duplicates: 0Warnings: 0mysql> exitBye[root@mysql5612 binlog]# ll总用量 6240204-rw-rw----. 1 mysql mysql 1073742187 6月 8 2015 mysql-bin.000048-rw-rw----. 1 mysql mysql 1073741968 6月 8 2015 mysql-bin.000049-rw-rw----. 1 mysql mysql 1073742063 6月 8 2015 mysql-bin.000050-rw-rw----. 1 mysql mysql 1073741957 6月 8 2015 mysql-bin.000051-rw-rw----. 1 mysql mysql 1073742142 6月 8 2015 mysql-bin.000052-rw-rw----. 1 mysql mysql 1021194604 12月 10 20:44 mysql-bin.000053-rw-rw----. 1 mysql mysql615 6月 8 2015 mysql-bin.index[root@mysql5612 binlog]#

看到binlog日志更新了，在20:44时间处，binlog日志mysql-bin.000053有更新记录。然后冲洗mysql服务，看看binlog是否会重新生成：

[root@mysql5612 binlog]# service mysqld56 restartShutting down MySQL................. [确定]Starting MySQL.....[确定][root@mysql5612 binlog]# [root@mysql5612 binlog]# ll总用量 997276-rw-rw----. 1 mysql mysql 1021194627 12月 10 20:46 mysql-bin.000053-rw-rw----. 1 mysql mysql399 12月 10 20:47 mysql-bin.000054-rw-rw----. 1 mysql mysql 82 12月 10 20:46 mysql-bin.index[root@mysql5612 binlog]#

果然，有新的mysql-bin.000054日志生成了。

3，去修改binlog日志路径

建立新的binlog日志路径：

[root@mysql5612 binlog]# mkdir -p /home/data/mysql/binlog_new[root@mysql5612 binlog]# [root@mysql5612 binlog]# chown -R mysql.mysql /home/data/mysql/binlog_new[root@mysql5612 binlog]#

然后修改my.cnf，设置新的log-bin路径：[root@mysql5612 binlog]# vim /usr/local/mysql/my.cnflog-bin =/home/data/mysql/binlog_new/mysql-bin查看配置文件的binlog路径：

[root@mysql5612 mysql]# more /usr/local/mysql/my.cnf |grep log-binlog-bin =/home/data/mysql/binlog_new/mysql-bin[root@mysql5612 mysql]#

4，重启mysql服务

[root@mysql5612 mysql]# service mysqld56 restartShutting down MySQL..[确定]Starting MySQL.....[确定][root@mysql5612 mysql]#

5，验证新的binlog

查看生成的日志，有新的如下所示：

[root@mysql5612 mysql]# cd /home/data/mysql/binlog_new/[root@mysql5612 binlog_new]# ll总用量 12-rw-rw----. 1 mysql mysql 143 12月 10 21:09 mysql-bin.000001-rw-rw----. 1 mysql mysql 399 12月 10 21:10 mysql-bin.000002-rw-rw----. 1 mysql mysql90 12月 10 21:10 mysql-bin.index[root@mysql5612 binlog_new]#

建立新表，录入数据：

[root@mysql5612 binlog_new]# mysqlWarning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.Commands end with ; or g.Your MySQL connection id is 2Server version: 5.6.12-log Source distributionCopyright （c） 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type "help;" or "h" for help. Type "c" to clear the current input statement.mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table z3 select 3 as a;Query OK, 1 row affected （0.01 sec）Records: 1Duplicates: 0Warnings: 0mysql> insert into z3 select 4;Query OK, 1 row affected （0.00 sec）Records: 1Duplicates: 0Warnings: 0mysql> insert into z3 select 5;Query OK, 1 row affected （0.00 sec）Records: 1Duplicates: 0Warnings: 0mysql> select * from z4;ERROR 1146 （42S02）: Table "test.z4" doesn"t existmysql> select * from z3;+---+| a |+---+| 3 || 4 || 5 |+---+3 rows in set （0.00 sec）mysql>

再去查看binlog，mysql-bin.000002从399增大到1085，表示有新的二进制日志产生了：

[root@mysql5612 binlog_new]# ll总用量 12-rw-rw----. 1 mysql mysql143 12月 10 21:09 mysql-bin.000001-rw-rw----. 1 mysql mysql 1085 12月 10 21:11 mysql-bin.000002-rw-rw----. 1 mysql mysql 90 12月 10 21:10 mysql-bin.index[root@mysql5612 binlog_new]#

再使用mysqlbinlog工具去看下产生的新日志是否刚在建立的z3表记录，看到有所有关于test库建立的z3表的操作记录，如下所示：

[root@mysql5612 binlog_new]# /usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -vmysql-bin.000002/*！50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*！40019 SET @@session.max_insert_delayed_threads=0*/;/*！50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*！*/;# at 4#151210 21:10:05 server id 72end_log_pos 120 CRC32 0xa723f142 Start: binlog v 4, server v 5.6.12-log created 151210 21:10:05 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*！*/;# at 120#151210 21:10:07 server id 72end_log_pos 206 CRC32 0x447f5733 Query thread_id=1 exec_time=0 error_code=0SET TIMESTAMP=1449753007/*！*/;SET @@session.pseudo_thread_id=1/*！*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*！*/;SET @@session.sql_mode=1075838976/*！*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*！*/;/*！C utf8mb4 *//*！*/;SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*！*/;SET @@session.time_zone="SYSTEM"/*！*/;SET @@session.lc_time_names=0/*！*/;SET @@session.collation_database=DEFAULT/*！*/;BEGIN/*！*/;# at 206#151210 21:10:07 server id 72end_log_pos 274 CRC32 0xde0b7250 Table_map: `access_log`.`access_log` mapped to number 70# at 274#151210 21:10:07 server id 72end_log_pos 368 CRC32 0xa03a9659 Write_rows: table id 70 flags: STMT_END_F### INSERT INTO `access_log`.`access_log`### SET### @1=10534### @2=1### @3=1449753007### @4="park_user@192.168.121.243"### @5="park_user@192.168.%"# at 368#151210 21:10:07 server id 72end_log_pos 399 CRC32 0x3ccf3c72 Xid = 3COMMIT/*！*/;# at 399#151210 21:10:58 server id 72end_log_pos 471 CRC32 0xef9ce950 Query thread_id=2 exec_time=0 error_code=0SET TIMESTAMP=1449753058/*！*/;BEGIN/*！*/;# at 471#151210 21:10:58 server id 72end_log_pos 593 CRC32 0x92e79f36 Query thread_id=2 exec_time=0 error_code=0use `test`/*！*/;SET TIMESTAMP=1449753058/*！*/;CREATE TABLE `z3` （`a` int（1） NOT NULL DEFAULT "0"）/*！*/;# at 593#151210 21:10:58 server id 72end_log_pos 638 CRC32 0x65f13b58 Table_map: `test`.`z3` mapped to number 107# at 638#151210 21:10:58 server id 72end_log_pos 678 CRC32 0xaa7fb7e1 Write_rows: table id 107 flags: STMT_END_F### INSERT INTO `test`.`z3`### SET### @1=3# at 678#151210 21:10:58 server id 72end_log_pos 709 CRC32 0x218a319c Xid = 60COMMIT/*！*/;# at 709#151210 21:11:04 server id 72end_log_pos 781 CRC32 0x9662b95e Query thread_id=2 exec_time=0 error_code=0SET TIMESTAMP=1449753064/*！*/;BEGIN/*！*/;# at 781#151210 21:11:04 server id 72end_log_pos 826 CRC32 0x46f32822 Table_map: `test`.`z3` mapped to number 107# at 826#151210 21:11:04 server id 72end_log_pos 866 CRC32 0xafb27f1e Write_rows: table id 107 flags: STMT_END_F### INSERT INTO `test`.`z3`### SET### @1=4# at 866#151210 21:11:04 server id 72end_log_pos 897 CRC32 0x351c7718 Xid = 63COMMIT/*！*/;# at 897#151210 21:11:10 server id 72end_log_pos 969 CRC32 0x76931e05 Query thread_id=2 exec_time=0 error_code=0SET TIMESTAMP=1449753070/*！*/;BEGIN/*！*/;# at 969#151210 21:11:10 server id 72end_log_pos 1014 CRC32 0xe7e8947bTable_map: `test`.`z3` mapped to number 107# at 1014#151210 21:11:10 server id 72end_log_pos 1054 CRC32 0xbdafa096Write_rows: table id 107 flags: STMT_END_F### INSERT INTO `test`.`z3`### SET### @1=5# at 1054#151210 21:11:10 server id 72end_log_pos 1085 CRC32 0x831695c0Xid = 64COMMIT/*！*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*！50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*！50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@mysql5612 binlog_new]#

这表明我们的binlog路径切换操作成功完成了。<！-- Baidu Button BEGIN -->本文永久更新链接地址