Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL 5.6.12 binary log path switching binlog     - Linux Getting Started tutorial: Ubuntu 14.04 in the installation Sogou Pinyin (Linux)

- Oracle Data File Management (Database)

- Apache Web Security Linux systems (Linux)

- DOM event handlers add notes (Programming)

- C # using the HttpClient Note: Preheat the long connection (Programming)

- Binary search is really easy as you think you do (Programming)

- RHEL6.4 one key installation Redmine (Linux)

- Solaris 10 nagios monitoring system (Linux)

- GRUB how to load Linux kernel (Linux)

- How to install Eclipse Luna IDE on CentOS 7 / RHEL 7 (Linux)

- How to add two-factor authentication for Linux systems SSH (Linux)

- Transfer files and permissions from Windows to Linux system by Samba (Linux)

- Linux character device - a simple character device model (Linux)

- extundelete: the Linux-based open source data recovery tools (Linux)

- Linux environment variables inside (Linux)

- Comparison of C # and Java (Programming)

- Ubuntu 14.04 installation configuration GNOME Classic interface (Linux)

- 20 Advanced Java interview questions summary (Programming)

- Android imitation UC browser scroll up and down functions (Programming)

- KVM virtualization nested configuration (Server)

 
         
  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:      
 
- Linux network security backdoor technology and advanced skill practice (Linux)
- String JavaScript type system (Programming)
- Ubuntu install Scala 2.10.x version (Linux)
- Linux System Administrator common interview questions and answers 30 (Linux)
- C ++ precision performance test function (Programming)
- Recover Ubuntu 14.04 wakes up from standby in case mouse keyboard appears dead (Linux)
- Java inner classes (Programming)
- Apache POI Excel Document Processing (Linux)
- Java garbage collection and heap memory layout (Programming)
- HAproxy let IP recording back-end RS (Server)
- Linux systems for entry-learning: Install closed-source packages in Debian (Linux)
- RedHat virtual machine to install VMware Tools (Linux)
- Oracle 12c users create (Database)
- DupeGuru- find and remove duplicate files (Linux)
- Use OpenSSL to generate a certificate (Linux)
- Lua and C ++ (Programming)
- Struts2 configure a static resource files without Struts processing (regular match) (Programming)
- Linux environment installation of rvm and ruby (Linux)
- Performance Optimization: Using Ramlog transfer log files to memory (Linux)
- Two alert log ORA Errors (Database)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.