Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL 5.6.12 binary log path switching binlog     - Linux firewall settings instance (Linux)

- Getting Started with Linux: Nginx Web Server How to Block Specific User Agents (UA) (Server)

- Cacti monitoring service Nginx (Linux)

- Analysis of MySQL Dockerfile 5.6 (Database)

- Android official recommendation: DialogFragment create dialog (Programming)

- Oracle set the timer task JOB scheduler to execute stored procedures or PL / SQL code block (Database)

- Shutdown - an advanced shutdown artifact (Linux)

- Ubuntu 64-bit installation Adobe Reader 9.5.5 (Linux)

- Using Python and OpenCV detecting image barcode (Programming)

- Java and Python use make way dictionary word search script (Programming)

- Linux Network Programming - raw socket Example: sending a UDP packet (Programming)

- shellinabox: one uses AJAX Web-based terminal emulator (Linux)

- How to find an IP address through the command line (Linux)

- Vagrant build LNMP environment (Server)

- Help you make Git Bisect (Linux)

- The Linux-based security settings Ipchains Firewall (Linux)

- Linux network security strategy (Linux)

- ApacheDS configuration of users and user groups to achieve SSO (Server)

- The script Linux command (Linux)

- CentOS 6.7 compile and install LAMP (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:      
 
- VMware ghost Linux card error (Linux)
- JavaScript cross-browser event object library (Programming)
- PostgreSQL export data dictionary documents (Database)
- Use PXE with kickstart to install Linux automatically (Linux)
- PHP Performance Analysis and Experiment: Performance Micro Analysis (Programming)
- Linux Getting Started Tutorial: Ubuntu laptop screen brightness adjustment (Linux)
- Configuring Haproxy log support (syslog logging support) (Server)
- Live Wallpaper for Linux release adds dynamic background (Linux)
- VirtualBox CentOS is configured as a local disk mirroring software source (Linux)
- The formatted Linux hard drive and mount (Linux)
- Oracle SDE and maintain common commands - Display space (Database)
- You really do need to know a variety of programming languages (Programming)
- Linux security configuration (Linux)
- Linux detection command (vmstat) (Linux)
- Hadoop 0.23 compile common errors (Server)
- Spring REST Exception Handling (Programming)
- How to fix Ubuntu / Mint can not add PPA source of error (Linux)
- Selection sort, insertion sort, and Shell sort (Programming)
- Linux Tutorial ---- fundamentals of data traffic redirection (Linux)
- Analysis of potential problems through custom Orabbix monitoring Oracle (Database)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.