Mysql5.6.12 to switch binlog binary log path

1305 people read comment(0) Collection Report
Classification:

Preface
A MySQL students said because they binlog produce too big and need to log path to put the additional disk above, ask me what time of the operation of the program, share get a MySQL binary log log path switching examples to him. Just today is empty, take mysql5.6.12 to do an example, give you a demonstration.

1, see the binlog address

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

2, verify the normal use of binlog

Binlog] [root@mysql5612# PWD
/home/data/mysql/binlog
Binlog] [root@mysql5612# MySQL
Using a password Warning:On TheLine interface can be insecure. command
WelcomeTo TheMonitor. Commands MySQLEnd With;Or\g.
MySQL connection YourID Is Three hundred and ninety thousand two hundred and seventeen
ServerVersion:Five point six.12-LogDistribution Source

Copyright (c)Two thousand,Two thousand and thirteen, OracleAnd/Or ItsAll rights reserved. affiliates.

OracleIsRegistered trademark aOfCorporation OracleAnd/Or Its
Other names may be trademarks affiliates.OfRespective their
Owners.

'help Type; ';'Or'\h'ForType'\c'help.ToClearTheInput statement. current

Create table Z2 select mysql>Two AsA;
ERROROne thousand and forty-six(ThreeD000): database selected No
Create table test.z2 select mysql>Two AsA;
OK Query,OneAffected row (Zero point zero fourSEC)
Records:OneDuplicates:ZeroWarnings:Zero

Mysql>Exit
Bye
Binlog] [root@mysql5612# ll
Total dosageSix million two hundred and forty thousand two hundred and four
-rw-rw1. MySQL MySQL 82015 mysql-bin.000048 10737421876
-rw-rw1. MySQL MySQL 82015 mysql-bin.000049 10737419686
-rw-rw1. MySQL MySQL 82015 mysql-bin.000050 10737420636
-rw-rw1. MySQL MySQL 82015 mysql-bin.000051 10737419576
-rw-rw1. MySQL MySQL 82015 mysql-bin.000052 10737421426
-rw-rw1. MySQL MySQL 10 20:44 mysql-bin.000053 102119460412
-rw-rw1. MySQL MySQL 82015 mysql-bin.index 6156
Binlog] [root@mysql5612#

See binlog log updated, in the 20:44 time, binlog log mysql-bin.000053 has updated records. Then rinse the MySQL service to see if binlog will be re generated:

Binlog] [root@mysql5612#Service Mysqld56 Restart
Shutting Down MySQL... and...[determination]
Starting MySQL....[determination]
Binlog] [root@mysql5612#
Binlog] [root@mysql5612#Ll
The total amount of 997276
-rw-rw----1.Mysql Mysql102119462712 month 1020: 46 Mysql-bin.000053
-rw-rw----1.Mysql Mysql39912 month 1020: 47 Mysql-bin.000054
-rw-rw----1.Mysql Mysql8212 month 1020: 46 Mysql-bin.index
Binlog] [root@mysql5612#

Sure enough, there's a new mysql-bin.000054 log generated.

Blog address of the original csdn:Http://prog3.com/sbdm/blog/mchdba/article/details/50254903, without the original author Huang Shan (mchdba) allowed, may not be reproduced


3, to modify the binlog log path

Create a new binlog log path:

[root@mysql5612Binlog]# MKDIR -p /home/data/mysql/binlog_new
[root@mysql5612Binlog]#
[root@mysql5612Binlog]Chown -R mysql.mysql /home/data/mysql/binlog_new #
[root@mysql5612Binlog]#

And then modify the my.cnf, set the new log-bin path:

[root@mysql5612Binlog]# VIM /usr/local/mysql/my.cnf
Log-bin =/home/data/mysql/binlog_new/mysql-bin

View the configuration file for the binlog path:

[root@mysql5612Mysql]More /usr/local/mysql/my.cnf |grep log-bin #
Log-bin =/home/data/mysql/binlog_new/mysql-bin
[root@mysql5612Mysql]#

4, restart the MySQL service

[root@mysql5612Mysql]# service mysqld56 restart
ShuttingDownMySQL[determine]
Starting MySQL... [determine]
[root@mysql5612Mysql]#

5, verify the new binlog

View the generated log, with the new as shown below:

[root@mysql5612Mysql]# CD /home/data/mysql/binlog_new/
[root@mysql5612Binlog_new]# ll
Total dosageTwelve
-rw-rw----.OneMySQL MySQLOne hundred and forty-three TwelvemonthTen Twenty-one09 mysql-bin.000001
-rw-rw----.OneMySQL MySQLThree hundred and ninety-nine TwelvemonthTen Twenty-one:TenMysql-bin.000002
-rw-rw----.OneMySQL MySQLNinety TwelvemonthTen Twenty-one:TenMysql-bin.Index
[root@mysql5612Binlog_new]#

Create a new table, input data:

Binlog_new] [root@mysql5612# MySQL
Using a password Warning:On TheLine interface can be insecure. command
WelcomeTo TheMonitor. Commands MySQLEnd With;Or\g.
MySQL connection YourID Is Two
ServerVersion:Five point six.12-LogDistribution Source

Copyright (c)Two thousand,Two thousand and thirteen, OracleAnd/Or ItsAll rights reserved. affiliates.

OracleIsRegistered trademark aOfCorporation OracleAnd/Or Its
Other names may be trademarks affiliates.OfRespective their
Owners.

'help Type; ';'Or'\h'ForType'\c'help.ToClearTheInput statement. current

Use test mysql>;
Table information ReadingForCompletionOfTableAndNames column
Can turn off this feature YouTo GetQuicker startup aWith-A

Changed Database
Create table Z3 select mysql>Three AsA;
OK Query,OneAffected row (Zero point zero oneSEC)
Records:OneDuplicates:ZeroWarnings:Zero

Insert mysql>IntoSelect Z3Four;
OK Query,OneAffected row (ZeroSEC)
Records:OneDuplicates:ZeroWarnings:Zero

Insert mysql>IntoSelect Z3Five;
OK Query,OneAffected row (ZeroSEC)
Records:OneDuplicates:ZeroWarnings:Zero

Select mysql> *FromZ4;
ERROROne thousand one hundred and forty-six(Forty-twoS02):'test.z4'doesn't exist Table
Select mysql> *FromZ3;
+---+
| a |
+---+
|Three|
|Four|
|Five|
+---+
ThreeRowsIn Set(ZeroSEC)

Mysql>

To view binlog, mysql-bin.000002 increased from 399 to 1085, which indicates that a new binary log is generated:

Binlog_new]# ll [root@mysql5612
The total amount of 12
-Rw-rw----.OneMySQL MySQLOne hundred and forty-three TwelvemonthTen Twenty-one:09 mysql-bin.000001
-Rw-rw----.OneMySQL MySQLOne thousand and eighty-five TwelvemonthTen Twenty-one:ElevenMysql-bin.000002
-Rw-rw----.OneMySQL MySQLNinety TwelvemonthTen Twenty-one:TenMysql-bin.index
Binlog_new]# [root@mysql5612

Then use the mysqlbinlog tool to see if the new log is just in the establishment of the Z3 table records, see all about the test library to establish the Z3 table of the operation records, as shown below:

Binlog_new]# /usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin [root@mysql5612.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
21:10:05 server ID #151210 72 end_log_pos 120 0xa723f142 Start: binlog V 4 V 5.6.12-log created server 151210 at 21:10:05 startup CRC32.
This binlog is either # Warning: in use or was not closed properly.
ROLLBACK* / / *!;
# at 120
21:10:07 server #151210 ID end_log_pos 206 CRC32 72 0x447f5733 Query thread_id=1 exec_time=0 error_code=0
TIMESTAMP= SETOne billion four hundred and forty-nine million seven hundred and fifty-three thousand and seven* / / *!;
@@session.pseudo_thread_id= SETOne* / / *!;
@@session.foreign_key_checks= SETOne, @@session.sql_auto_is_null=Zero, @@session.unique_checks=One, @@session.autocommit=One* / / *!;
@@session.sql_mode= SETOne billion seventy-five million eight hundred and thirty-eight thousand nine hundred and seventy-six* / / *!;
@@session.auto_increment_increment= SETOne, @@session.auto_increment_offset=One* / / *!;
\C utf8mb4 * / / *!* / / *!;
@@session.character_set_client= SETForty-five, @@session.collation_connection=Forty-five, @@session.collation_server=Forty-five* / / *!;
@@session.time_zone= SET'SYSTEM'* / / *!;
@@session.lc_time_names= SETZero* / / *!;
@@session.collation_database=DEFAULT SET* / / *!;
BEGIN
* / / *!;
# at 206
21:10:07 server ID #151210 72 end_log_pos 274 CRC32 0xde0b7250 Table_map: `access_log`.`access_log` mapped to Number 70
# at 274
21:10:07 server ID #151210 72 end_log_pos 368 CRC32 0xa03a9659 Write_rows: table ID flags: 70 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
21:10:07 server ID #151210 72 end_log_pos 399 0x3ccf3c72 Xid CRC32 = 3
COMMIT* / / *!;
# at 399
21:10:58 server #151210 ID end_log_pos 471 CRC32 72 0xef9ce950 Query thread_id=2 exec_time=0 error_code=0
TIMESTAMP= SETOne billion four hundred and forty-nine million seven hundred and fifty-three thousand and fifty-eight* / / *!;
BEGIN
* / / *!;
# at 471
# 151210 21:10:58服务器ID 72 end_log_pos 593 CRC32 0x92e79f36查询thread_id = 2 = 0 = 0 error_code exec_time
使用测试/ *!*;
设置时间戳=十四亿四千九百七十五万三千零五十八/ *!*;
创建` Z3 `(
一个int默认“0”/ *!*# 593
# 151210 21:10:58服务器ID 72 end_log_pos 638 CRC32 0x65f13b58 table_map:`测试`。` Z3 `映射到107号
# 638
# 151210 21:10:58服务器ID 72 end_log_pos 678 CRC32 0xaa7fb7e1 write_rows:表ID 107旗:stmt_end_f
# # #插入` ` ` Z3 `测试。
# # #集
# # # @ 1 = 3
# 678
# 151210 21:10:58服务器ID 72 end_log_pos 709 CRC32 0x218a319c XID = 60
承诺/ *!*# 709
# 151210 21:11:04服务器ID 72 end_log_pos 781 CRC32 0x9662b95e查询thread_id = 2 = 0 = 0 error_code exec_time
设置时间戳=十四亿四千九百七十五万三千零六十四/ *!*;
开始
/ *!*# 781
# 151210 21:11:04服务器ID 72 end_log_pos 826 CRC32 0x46f32822 table_map:`测试`。` Z3 `映射到107号
# 826
# 151210 21:11:04服务器ID 72 end_log_pos 866 CRC32 0xafb27f1e write_rows:表ID 107旗:stmt_end_f
# # #插入` ` ` Z3 `测试。
# # #集
# # # @ 1 = 4
# 866
# 151210 21:11:04服务器ID 72 end_log_pos 897 CRC32 0x351c7718 XID = 63
承诺/ *!*# 897
# 151210 21:11:10服务器ID 72 end_log_pos 969 CRC32 0x76931e05查询thread_id = 2 = 0 = 0 error_code exec_time
设置时间戳=十四亿四千九百七十五万三千零七十/ *!*;
开始
/ *!*# 969
# 151210 21:11:10服务器ID 72 end_log_pos 1014 CRC32 0xe7e8947b table_map:`测试`。` Z3 `映射到107号
# 1014
# 151210 21:11:10服务器ID 72 end_log_pos 1054 CRC32 0xbdafa096 write_rows:表ID 107旗:stmt_end_f
# # #插入` ` ` Z3 `测试。
# # #集
# # # @ 1 = 5
# 1054
# 151210 21:11:10服务器ID 72 end_log_pos 1085 CRC32 0x831695c0 XID = 64
承诺/ *!*;
分隔符;
日志文件#结束
回滚/ * *添加/ mysqlbinlog/ *!50003集completion_type = @ old_completion_type *// *!50530集@ @会话。pseudo_slave_mode = 0 * /;
[根@ mysql5612 binlog_new ] #

这表明我们的binlog路径切换操作成功完成了。

猜你在找
查看评论
*以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1003794次
    • 积分:一万六千零八十九
    • 等级:
    • 排名:319名第
    • 原创:438篇
    • 转载:3篇
    • 译文:2篇
    • 评论:523条
    我的课程
    博客专栏
    技术网址连接
    http://dev.mysql.com/doc/
    文章分类
    文章存档
    Latest comments