This week I was approached by a friend who was not aware of the resource available in 5.6 although it’s being very well commented and has been used by many that received that as a very good new feature. In fact, it was a bit intrusive to change transaction log sizes and the # of files when using old versions of MySQL; this is something that sometimes was able to put someone else at risk. <\/p>\n
With the 5.6++ that operation became simple since it will resize and create new files automatically after detect new configurations related to the redo\/transaction logs files and one doesn’t need to move files anymore<\/em>, as was done using prior 5.6 versions. From now on, one will just need to change configuration dynamically\/using configuration file and restart mysqld. <\/p>\n On 5.6 it’s just a matter of adjust innodb system variables globally, in case of innodb_fast_shutdown to sync log buffer and log files, edit my.cnf to add or update the values of the system variables that handle transaction log behaviour and then, give mysql a restart. I’ve got some log output of all the action that are involved in doing that.<\/p>\n Below you’ll be able to check that innodb_fast_shutdown was configured to make mysqld sync all the buffer content with disk files and then, shutdown. This is the moment in which the adjusts on system variables innodb related was done. When mysqld was started, it read the new configurations and adjusted sizes and the # of transaction logs files. <\/p>\n More information about you can find clicking the link below: I hope that help!<\/p>\n","protected":false},"excerpt":{"rendered":" This week I was approached by a friend who was not aware of the resource available in 5.6 although it’s being very well commented and has been used by many that received that as a very good new feature. In fact, it was a bit intrusive to change transaction log sizes and the # of […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[],"_links":{"self":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/866"}],"collection":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=866"}],"version-history":[{"count":6,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/866\/revisions"}],"predecessor-version":[{"id":876,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/866\/revisions\/876"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=866"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=866"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=866"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}[root@localhost ~]# mysql -p\r\nEnter password:\r\nWelcome to the MySQL monitor. Commands end with ; or \\g.\r\nYour MySQL connection id is 2\r\nServer version: 5.6.15-log MySQL Community Server (GPL)\r\n\r\nCopyright (c) 2000, 2013, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql> show variables like 'innodb_log_%';\r\n+-----------------------------+----------+\r\n| Variable_name | Value |\r\n+-----------------------------+----------+\r\n| innodb_log_file_size | 50331648 |\r\n| innodb_log_files_in_group | 2 |\r\n| innodb_log_group_home_dir | .\/ |\r\n+-----------------------------+----------+\r\n5 rows in set (0.00 sec)\r\n\r\nmysql> set global innodb_fast_shutdown=1;\r\nQuery OK, 0 rows affected (0.01 sec)\r\n\r\n# stopped mysql\r\n# edited configuration file\r\n[mysqld]\r\ninnodb_log_file_size=16M\r\ninnodb_log_files_in_group=4\r\n\r\n# mysqld restart\r\n[root@localhost ~]# \/etc\/init.d\/mysql restart\r\nShutting down MySQL.. SUCCESS!\r\nStarting MySQL... SUCCESS!\r\n\r\n# logs and auto adjust of files\r\n[root@localhost ~]# tail -f \/var\/lib\/mysql\/localhost.localdomain.err\r\n2014-02-27 07:10:29 2266 [Note] InnoDB: 128 rollback segment(s) are active.\r\n2014-02-27 07:10:29 2266 [Note] InnoDB: Waiting for purge to start\r\n2014-02-27 07:10:29 2266 [Note] InnoDB: 5.6.15 started; log sequence number 1828674\r\n2014-02-27 07:10:30 2266 [Note] Server hostname (bind-address): '*'; port: 3306\r\n2014-02-27 07:10:30 2266 [Note] IPv6 is available.\r\n2014-02-27 07:10:30 2266 [Note] - '::' resolves to '::';\r\n2014-02-27 07:10:30 2266 [Note] Server socket created on IP: '::'.\r\n2014-02-27 07:10:30 2266 [Note] Event Scheduler: Loaded 0 events\r\n2014-02-27 07:10:30 2266 [Note] \/usr\/sbin\/mysqld: ready for connections.\r\nVersion: '5.6.15-log' socket: '\/var\/lib\/mysql\/mysql.sock' port: 3306 MySQL Community Server (GPL)\r\n2014-02-27 07:11:29 2266 [Note] \/usr\/sbin\/mysqld: Normal shutdown\r\n\r\n[...]\r\n\r\n2014-02-27 07:11:30 2266 [Note] Shutting down plugin 'binlog'\r\n2014-02-27 07:11:30 2266 [Note] \/usr\/sbin\/mysqld: Shutdown complete\r\n\r\n140227 07:11:30 mysqld_safe mysqld from pid file \/var\/lib\/mysql\/localhost.localdomain.pid ended\r\n140227 07:11:31 mysqld_safe Starting mysqld daemon with databases from \/var\/lib\/mysql\r\n2014-02-27 07:11:31 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).\r\n2014-02-27 07:11:31 2488 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=localhost-bin' to avoid this problem.\r\n2014-02-27 07:11:31 2488 [Note] Plugin 'FEDERATED' is disabled.\r\n2014-02-27 07:11:32 2488 [Note] InnoDB: Completed initialization of buffer pool\r\n2014-02-27 07:11:32 2488 [Note] InnoDB: Highest supported file format is Barracuda.\r\n2014-02-27 07:11:32 2488 [Warning] InnoDB: Resizing redo log from 2*3072 to 4*1024 pages, LSN=1828684\r\n2014-02-27 07:11:32 2488 [Warning] InnoDB: Starting to delete and rewrite log files.\r\n2014-02-27 07:11:32 2488 [Note] InnoDB: Setting log file .\/ib_logfile101 size to 16 MB\r\n2014-02-27 07:11:32 2488 [Note] InnoDB: Setting log file .\/ib_logfile1 size to 16 MB\r\n2014-02-27 07:11:33 2488 [Note] InnoDB: Setting log file .\/ib_logfile2 size to 16 MB\r\n2014-02-27 07:11:33 2488 [Note] InnoDB: Setting log file .\/ib_logfile3 size to 16 MB\r\n2014-02-27 07:11:33 2488 [Note] InnoDB: Renaming log file .\/ib_logfile101 to .\/ib_logfile0\r\n2014-02-27 07:11:33 2488 [Warning] InnoDB: New log files created, LSN=1828684\r\n<\/strong>2014-02-27 07:11:33 2488 [Note] InnoDB: 128 rollback segment(s) are active.\r\n2014-02-27 07:11:33 2488 [Note] InnoDB: Waiting for purge to start\r\n2014-02-27 07:11:33 2488 [Note] InnoDB: 5.6.15 started; log sequence number 1828684\r\n2014-02-27 07:11:33 2488 [Note] Server hostname (bind-address): '*'; port: 3306\r\n2014-02-27 07:11:33 2488 [Note] IPv6 is available.\r\n2014-02-27 07:11:33 2488 [Note] - '::' resolves to '::';\r\n2014-02-27 07:11:33 2488 [Note] Server socket created on IP: '::'.\r\n2014-02-27 07:11:33 2488 [Note] Event Scheduler: Loaded 0 events\r\n2014-02-27 07:11:33 2488 [Note] \/usr\/sbin\/mysqld: ready for connections.\r\nVersion: '5.6.15-log' socket: '\/var\/lib\/mysql\/mysql.sock' port: 3306 MySQL Community Server (GPL)\r\n[...]\r\n\r\n# checking system variables again\r\nmysql> show variables like 'innodb_log%';\r\n+-----------------------------+----------+\r\n| Variable_name | Value |\r\n+-----------------------------+----------+\r\n| innodb_log_buffer_size | 8388608 |\r\n| innodb_log_compressed_pages | ON |\r\n| innodb_log_file_size | 16777216 |\r\n| innodb_log_files_in_group | 4 |\r\n| innodb_log_group_home_dir | .\/ |\r\n+------------------------<\/pre>\n
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/innodb-data-log-reconfiguration.html<\/p>\n