Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL 5.6.12 binary log path switching binlog     - How to use Quagga BGP (Border Gateway Protocol) router to filter BGP routing (Linux)

- Ubuntu 14.04 install Sublime Text 3 plug and use SublimeClang (Linux)

- Spring-depth understanding of the various annotations (Programming)

- Build your own Python coding environment (Linux)

- Ubuntu deploying Solr (4.4) to Tomcat (7.0.53) (Server)

- Linux system package manager (rpm, yum, source packages installation) (Linux)

- Nonstandard IMP-00010 error processing one case (Database)

- Java NIO1: I / O model overview (Programming)

- Git uses a small mind (Linux)

- Introduction and bash history command to quickly call (Linux)

- Linux (CentOS) SSH login without password authentication (Linux)

- Python substring format (Programming)

- On Android running ClojureScript (Linux)

- Linux kernel source tree to establish load module hello (Linux)

- How to make a U disk to install Ubuntu (Linux)

- Build their own recursive DNS server (Server)

- Making Linux root file system problems on-link library (Programming)

- Lua4.0 interpreter entrance (Programming)

- Installing software on Ubuntu: apt-get and dpkg difference (Linux)

- 11 examples in Linux df command (Linux)

 
         
  MySQL 5.6.12 binary log path switching binlog
     
  Add Date : 2018-11-21      
         
       
         
  Preface:
There is a mysql binlog because students said they have much, and the need to log onto another disk path to go above and asked me what's the timing of the operation of the program, share get an example of a mysql binlog log path switching to him. Just today free, Take mysql5.6.12 to be examples to demo it for you.

1, views binlog address

[Root @ mysql5612 ~] # more /usr/local/mysql/my.cnf | grep log-bin
log-bin = / home / data / mysql / binlog / mysql-bin.log
[Root @ mysql5612 ~] #
2. Verify binlog normal use

[Root @ mysql5612 binlog] # pwd
/ Home / data / mysql / binlog
[Root @ mysql5612 binlog] # mysql
Warning: 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 390217
Server version: 5.6.12-log Source distribution

Copyright (c) 2000, 2013, Oracle and / or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and / or its
affiliates. Other names may be trademarks of their respective
owners.

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 selected
mysql> create table test.z2 select 2 as a;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> exit
Bye
[Root @ mysql5612 binlog] # ll
The total amount of 6,240,204
-rw-rw ----. 1 mysql mysql 1073742187. 6 Yue 8 2015 mysql-bin.000048
-rw-rw ----. 1 mysql mysql 1073741968. 6 Yue 8 2015 mysql-bin.000049
-rw-rw ----. 1 mysql mysql 1073742063. 6 Yue 8 2015 mysql-bin.000050
-rw-rw ----. 1 mysql mysql 1073741957. 6 Yue 8 2015 mysql-bin.000051
-rw-rw ----. 1 mysql mysql 1073742142. 6 Yue 8 2015 mysql-bin.000052
-rw-rw ----. 1 mysql mysql 1021194604 12 Yue 10 20:44 mysql-bin.000053
-rw-rw ----. 1 mysql mysql 615. 6 Yue 8 2015 mysql-bin.index
[Root @ mysql5612 binlog] #
See binlog log updated at 20:44 at the time, binlog log mysql-bin.000053 updated records. Then rinse mysql service to see if binlog regenerated:

[Root @ mysql5612 binlog] # service mysqld56 restart
Shutting down MySQL ................. [OK]
Starting MySQL ..... [OK]
[Root @ mysql5612 binlog] #
[Root @ mysql5612 binlog] # ll
The total amount of 997 276
-rw-rw ----. 1 mysql mysql 1021194627 12 Yue 10 20:46 mysql-bin.000053
-rw-rw ----. 1 mysql mysql 399 12 Yue 10 20:47 mysql-bin.000054
-rw-rw ----. 1 mysql mysql 82 12 Yue 10 20:46 mysql-bin.index
[Root @ mysql5612 binlog] #
Sure enough, there is a new log is generated.->(mysql-bin.000054)

3, to amend the binlog log path

Establish new binlog log path:

[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] #
Then modify the my.cnf, setting a new log-bin path:

[Root @ mysql5612 binlog] # vim /usr/local/mysql/my.cnf
log-bin = / home / data / mysql / binlog_new / mysql-bin
See binlog path to the configuration file:

[Root @ mysql5612 mysql] # more /usr/local/mysql/my.cnf | grep log-bin
log-bin = / home / data / mysql / binlog_new / mysql-bin
[Root @ mysql5612 mysql] #
4, restart the mysql service

[Root @ mysql5612 mysql] # service mysqld56 restart
Shutting down MySQL .. [OK]
Starting MySQL ..... [OK]
[Root @ mysql5612 mysql] #
5. Verify that the new binlog

View the logs generated, there is new is as follows:

[Root @ mysql5612 mysql] # cd / home / data / mysql / binlog_new /
[Root @ mysql5612 binlog_new] # ll
The total amount of 12
-rw-rw ----. 1 mysql mysql 143 12 Yue 10 21:09 mysql-bin.000001
-rw-rw ----. 1 mysql mysql 399 12 Yue 10 21:10 mysql-bin.000002
-rw-rw ----. 1 mysql mysql 90 12 Yue 10 21:10 mysql-bin.index
[Root @ mysql5612 binlog_new] #
Create a new table, input data:

[Root @ mysql5612 binlog_new] # mysql
Warning: 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 2
Server version: 5.6.12-log Source distribution

Copyright (c) 2000, 2013, Oracle and / or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and / or its
affiliates. Other names may be trademarks of their respective
owners.

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 names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table z3 select 3 as a;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into z3 select 4;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into z3 select 5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from z4;
ERROR 1146 (42S02): Table 'test.z4' does not exist
mysql> select * from z3;
+ --- +
| A |
+ --- +
| 3 |
| 4 |
| 5 |
+ --- +
3 rows in set (0.00 sec)

mysql>
Go to View binlog, mysql-bin.000002 increased from 399 to 1085, indicate a new binary log produced:

[Root @ mysql5612 binlog_new] # ll
The total amount of 12
-rw-rw ----. 1 mysql mysql 143 12 Yue 10 21:09 mysql-bin.000001
-rw-rw ----. 1 mysql mysql 1085 12 Yue 10 21:11 mysql-bin.000002
-rw-rw ----. 1 mysql mysql 90 12 Yue 10 21:10 mysql-bin.index
[Root @ mysql5612 binlog_new] #
Then use mysqlbinlog tool to look to generate new log is created in just z3 table records, saw logs all actions concerning test library built z3 table, as follows:

[Root @ mysql5612 binlog_new] # / usr / local / mysql / bin / mysqlbinlog --base64-output = DECODE-ROWS -v mysql-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 72 end_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 72 end_log_pos 206 CRC32 0x447f5733 Query thread_id = 1 exec_time = 0 error_code = 0
SET 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 72 end_log_pos 274 CRC32 0xde0b7250 Table_map: `access_log`.`access_log` mapped to number 70
# At 274
# 151210 21:10:07 server id 72 end_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 72 end_log_pos 399 CRC32 0x3ccf3c72 Xid = 3
COMMIT / * * /!;
# At 399
# 151210 21:10:58 server id 72 end_log_pos 471 CRC32 0xef9ce950 Query thread_id = 2 exec_time = 0 error_code = 0
SET TIMESTAMP = 1449753058 / * * /!;
BEGIN
/ * * /!;
# At 471
# 151210 21:10:58 server id 72 end_log_pos 593 CRC32 0x92e79f36 Query thread_id = 2 exec_time = 0 error_code = 0
use `test` / * * /!;
SET TIMESTAMP = 1449753058 / * * /!;
CREATE TABLE `z3` (
  `A` int (1) NOT NULL DEFAULT '0'
)
/ * * /!;
# At 593
# 151210 21:10:58 server id 72 end_log_pos 638 CRC32 0x65f13b58 Table_map: `test`.`z3` mapped to number 107
# At 638
# 151210 21:10:58 server id 72 end_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 72 end_log_pos 709 CRC32 0x218a319c Xid = 60
COMMIT / * * /!;
# At 709
# 151210 21:11:04 server id 72 end_log_pos 781 CRC32 0x9662b95e Query thread_id = 2 exec_time = 0 error_code = 0
SET TIMESTAMP = 1449753064 / * * /!;
BEGIN
/ * * /!;
# At 781
# 151210 21:11:04 server id 72 end_log_pos 826 CRC32 0x46f32822 Table_map: `test`.`z3` mapped to number 107
# At 826
# 151210 21:11:04 server id 72 end_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 72 end_log_pos 897 CRC32 0x351c7718 Xid = 63
COMMIT / * * /!;
# At 897
# 151210 21:11:10 server id 72 end_log_pos 969 CRC32 0x76931e05 Query thread_id = 2 exec_time = 0 error_code = 0
SET TIMESTAMP = 1449753070 / * * /!;
BEGIN
/ * * /!;
# At 969
# 151210 21:11:10 server id 72 end_log_pos 1014 CRC32 0xe7e8947b Table_map: `test`.`z3` mapped to number 107
# At 1014
# 151210 21:11:10 server id 72 end_log_pos 1054 CRC32 0xbdafa096 Write_rows: table id 107 flags: STMT_END_F
### INSERT INTO `test`.`z3`
### SET
### @ 1 = 5
# At 1054
# 151210 21:11:10 server id 72 end_log_pos 1085 CRC32 0x831695c0 Xid = 64
COMMIT / * * /!;
DELIMITER;
# End of log file
ROLLBACK / * added by mysqlbinlog * /;
! / * 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE * /;
! / * 50530 SET @@ SESSION.PSEUDO_SLAVE_MODE = 0 * /;
[Root @ mysql5612 binlog_new] #
This shows that our binlog path switching operation completed successfully.
     
         
       
         
  More:      
 
- Extended VMware Ubuntu root partition size (Linux)
- Heartbeat cluster components Overview (Server)
- PL / SQL in forall simple test (Database)
- Why you can have JavaScript string method (Programming)
- Security Features Linux and Unix operating system, programming (Linux)
- Use SocketServer write a sockets program (Programming)
- Windows7 system using Vagrant to build Linux virtualized development environment (Linux)
- OpenSSH version smooth upgrade method (Linux)
- Analysis of native Java keywords (Programming)
- How to install Hadoop on CentOS7 (Server)
- Unsafe reboot start (Linux)
- Linux process scheduling opportunity (Programming)
- Oracle VirtualBox Problem Solving Case (Linux)
- How to achieve SSH without password login (Server)
- High-performance JavaScript reflows and repaints (Programming)
- Linux Getting Started tutorial: Experience QEMU virtual machine articles (Linux)
- Java application server WildFly (Server)
- How to install and use the Snort in Ubuntu 15.04 (Linux)
- Installation on the way to the root source Ubuntu Server 14.04 LTS version Odoo8.0 (Server)
- Linux Command-line MySQL summary (Database)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.