Skip to main content

Migrating from MySQL 5.5 to 5.6



  • Import of a MySQL 5.5 dump followed by the mysql_upgrade command while GTIDs are enabled causes troubles with some MySQL 5.6 releases. Do the upgrade with --gtid-mode=0 and enable it later on.
  • Some default values have changed: The most important one is innodb_file_per_table = 1. Some old variables and some deprecated commands have been removed. So test carefully!
  • The Query Cache is disabled by default in 5.6. Change to the previous behavior by setting query_cache_type=1 in my.cnf
  • 8 new reserved key words have been defined. The most important ones are get and partition.
  • Implicit GROUP BY sorting in MySQL 5.6 is deprecated.
  • When upgrading a master-slave setup from 5.5 to 5.6 without downtime, take care: MySQL 5.6 (by default) writes checksums into the binary log which MySQL 5.5 does not understand, so the 5.5 IO slave reports an error and replication stops. It’s because of this: 

            "It's because of binlog_checksum = crc32 set to default at 5.6.5. If you use 5.6's master and 5.5  (or  earlier)'s slave, you need to set binlog_checksum = none on 5.6."  If you forgot this, upgrade the second node also, then replication will resume.

  • After upgrading to MySQL 5.6 a performance decrease is expected, especially, for the single-threaded applications. So, it is highly recommended to test not only the application code with MySQL 5.6 but also the application performance under a high load before doing the upgrade on production. Otherwise, you might need to downgrade to MySQL 5.5 because of the performance issues.
  • mysqldump and MySQL Enterprise Backup (mysqlbackup) will break when concurrently DDL statements are issued: Do not run the DDL operations ALTER TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, REPAIR TABLE, or RESTORE TABLE while a backup operation is going on. The resulting backup might be corrupted or fail.
  • RESET SLAVE followed by a database restart behaves different in 5.6 compared to 5.1. Possibly use RESET SLAVE ALL instead.
  • The mysql.host table was removed. If you rely on those privilege rules you should change this before upgrading to MySQL 5.6
Labels:

Comments

Post a Comment

Popular posts from this blog

Warning: Using a password on the command line interface can be insecure

We usually use MySQL login information on shell scripts to load some data into database or to fetch some monitoring information. To login to the database will write login information on Shell Scripts as below #!/bin/sh username = someusername password = somepassword $ mysql -u$username -p$password -A -v -S By doing that so, we will hit a warning in stdout saying: Warning: Using a password on the command line interface can be insecure To avoid the warning in shell script, we need to so some workaround like below: 1. Create a hidden file configuration file > touch /tmp/.mysqllogin.cnf 2. Enter your database credential information on the hidden file: > vi /tmp/.mysqllogin.cnf [client] user=someusername password=somepassword 3. Save the file and try to login to mysql using  --defaults-extra-file  keyword like $ mysql --defaults-extra-file=/tmp/.mysqllogin.cnf -A -v By using this workaround you can avoid the warning.
Post a Comment
Read more

Unable to open the log file (mysqld)

While starting the server you may find the below errors sometimes, it's because of unintentionally deleting the MySQL Binary Log files. When starting mysql if the server unable to find the mysql-bin-log files then below errors will arise. 170418 12:15:19 InnoDB: 5.5.30 started; log sequence number 32347593422 /usr/sbin/mysqld: File './mysql-bin.000023' not found (Errcode: 2) 170418 12:15:19 [ERROR] Failed to open log (file './mysql-bin.000023', errno 2) 170418 12:15:19 [ERROR] Could not open log file 170418 12:15:19 [ERROR] Can't init tc log 170418 12:15:19 [ERROR] Aborting 170418 12:15:19 InnoDB: Starting shutdown... 170418 12:15:20 InnoDB: Shutdown completed; log sequence number 32347593422 170418 12:15:20 [Note] /usr/sbin/mysqld: Shutdown complete Do we have a solution to get rid of this and start MySQL again? Well, we have two types of workarounds to start MySQL again. 1. Deleting the missed files in binlog .index  file 2. Disabling the binlog log
Post a Comment
Read more

Monitoring MySQL with Percona Monitoring and Management

In this blog post we will see how to monitor MySQL databases using the open source monitoring tool provided by Percona, referred as Percona Monitoring and Management (PMM). About PMM: Percona Monitoring and Management (PMM) is an open-source platform for managing and monitoring MySQL and MongoDB performance. It is developed by Percona in collaboration with experts in the field of managed database services, support and consulting. PMM is a free and open-source solution that you can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible. Basic Requirements: 1. Need at least one Linux server to install PMM Client and PMM Server. 2. Need Operating System - root user credentials 3. Default port's(80) have to be open if using to or more servers. 4. A MySQL user is required to capture the Queries in the Database. 5. A Docker se
Post a Comment
Read more