Mysql 5.6 upgrade (from 5.5) single and replicated instance.



I have been working on an upgrade path from 5.5. to 5.6.  The MySQL documentation is good and explains what could be a muliple phase upgrade to get to 5.5 to then upgrade to 5.6, see

Here are few notes which I hope helps.  There looks to be two ways to upgrade from 5.5 to 5.6. 
  • Import/Export
  • Binary Upgrade


The Import/Export route is really only for smaller databases where using mysqldump is more appropriate.  For larger databases the binary upgrade is a more feasible option.  The upgrade method is fairly similar for both:
Binary
  •          Take a backup of the database (for back out only)
  •          Shut the database down (ensure the shutdown is clean)
  •          Upgrade the binaries (cannot use rpm -Uvh --nodeps, use rpm -e and rpm -i)
  •          Start the database
  •          Run mysql_upgrade (in the mysqld.log you will see lots of 'wrong structure' errors
  •          Bounce the database
  •          Backup the database as you no longer have a valid backup

Import/Export
  •          Run mysqldump for the entire database, this will also be the back out of the database
  •          Stop the database
  •          Upgrade the binaries (cannot use rpm -Uvh --nodeps, use rpm -e and rpm -i)
  •          Start the database
  •          Import the database
  •          Run mysql_upgrade
  •          Bounce the database
  •          Backup the database as you no longer have a valid backup
The next thing is the differences in my.cnf. 
TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 [Note] Plugin 'FEDERATED' is disabled.  
InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.

I did as a test try using MEB as an upgrade method but it failed.  When you start the database after the binary upgrade and restore it fails and mysqld crashes won't start, so it is impossible to run the mysql_upgrade command.  So this is a complete non starter.

I first tried an upgrade of the existing 5.5 database using a 'rpm -Uvh *' command this failed with dependency issues.   I then tried installing the packages individually using the --nodeps options:
  [root@mysqldb5 mysql-5.6]# rpm -Uvh --nodeps MySQL-server-5.6.10-1.linux_glibc2.5.x86_64.rpm
Preparing...                ########################################### [100%]

******************************************************************
A MySQL server package (MySQL-server-advanced-5.5.16-1.rhel4) is installed.

Upgrading directly from MySQL 5.5 to MySQL 5.6 may not be safe in all cases.  A manual dump and restore using mysqldump is recommended.  It is important to review the MySQL manual's Upgrading section for version-specific incompatibilities.

A manual upgrade is required.

- Ensure that you have a complete, working backup of your data and my.cnf   files
- Shut down the MySQL server cleanly
- Remove the existing MySQL packages.  Usually this command will   list the packages you should remove:   rpm -qa | grep -i '^mysql-'

You may choose to use 'rpm --nodeps -ev <package-name>' to remove   the package which contains the mysqlclient shared library.  The library will be reinstalled by the MySQL-shared-compat package. 
- Install the new MySQL packages supplied by Oracle and/or its affiliates
- Ensure that the MySQL server is started
- Run the 'mysql_upgrade' program

This is a brief description of the upgrade process.  Important details can be found in the MySQL manual, in the Upgrading section.
******************************************************************
error: %pre(MySQL-server-5.6.10-1.linux_glibc2.5.x86_64) scriptlet failed, exit status 1
error:   install: %pre scriptlet failed (2), skipping MySQL-server-5.6.10-1.linux_glibc2.5


Something to note is the the new parameters for 5.6.  A major one is explicit_defaults_for_timestamp, if not included it will cause an error in the mysqld.log.  This stops nonstandard TIMESTAMP datatype behaviors.  TIMESTAMP columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.
http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
Parameter
Old Default
New Default
back_log
50
Autosized using max_connections
binlog_checksum
NONE
CRC32
--binlog-row-event-max-size
1024
8192
flush_time
1800 (on Windows)
0
innodb_autoextend_increment
8
64
innodb_buffer_pool_instances
1
8 (platform dependent)
innodb_checksum_algorithm
INNODB
CRC32
innodb_concurrency_tickets
500
5000
innodb_file_per_table
0
1
innodb_old_blocks_time
0
1000
innodb_open_files
300
Autosized using innodb_file_per_table, table_open_cache
innodb_stats_on_metadata
ON
OFF
join_buffer_size
128KB
256KB
max_allowed_packet
1MB
4MB
max_connect_errors
10
100
sync_master_info
0
10000
sync_relay_log
0
10000
sync_relay_log_info
0
10000


I have added these to my test instance and all looks good.



 Upgrading A Replicated Setup
1. Stop replication on slave databases
stop slave;
2. Shutdown slave databases
mysqladmin -u root shutdown
3. Upgrade the slave:
I had to remove the old binaries and install new ones. This involved a rpm -e to remove the old version and a rpm -i to install the new 5.6 version.
4. Start slave databases on (5.6.10) - mysqld_safe &.   Below are some of the errors I got:
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'users' has the wrong structure
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
    2013-04-19 13:56:08 8510 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure
    2013-04-19 13:56:08 8510 [Note] Event Scheduler: Loaded 0 events
    2013-04-19 13:56:08 8510 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.6.10-log'  socket: '/db/DB1/config/mysql.sock'  port: 13306  MySQL Community Server (GPL)
    2013-04-19 13:56:08 8510 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
    2013-04-19 13:56:08 8510 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
    2013-04-19 13:56:08 8510 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 11269, relay log '/db/DB1/logs/relay-logs/relay-log.000002' position: 11415
    2013-04-19 13:56:08 8510 [Note] Slave I/O thread: connected to master 'repl@172.22.187.118:13306',replication started in log 'mysql-bin.000001' at position 11269
    2013-04-19 13:56:08 8510 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
    2013-04-19 13:56:08 8510 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master, maybe it is a *VERY OLD MASTER*. Error_code: 1193
   
5. Start replication on slave database
    DB1> start slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)

    DB1> show warnings;
    +-------+------+--------------------------+
    | Level | Code | Message                  |
    +-------+------+--------------------------+
    | Note  | 1254 | Slave is already running |
    +-------+------+--------------------------+
    1 row in set (0.00 sec)

    DB1> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.22.187.118
                      Master_User: repl
                      Master_Port: 13306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 11269
                   Relay_Log_File: relay-log.000004
                    Relay_Log_Pos: 270
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB:
              Replicate_Ignore_DB:
               Replicate_Do_Table:
           Replicate_Ignore_Table:
          Replicate_Wild_Do_Table:
      Replicate_Wild_Ignore_Table:
                       Last_Errno: 0
                       Last_Error:
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 11269
                  Relay_Log_Space: 437
                  Until_Condition: None
                   Until_Log_File:
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File:
               Master_SSL_CA_Path:
                  Master_SSL_Cert:
                Master_SSL_Cipher:
                   Master_SSL_Key:
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error:
                   Last_SQL_Errno: 0
                   Last_SQL_Error:
      Replicate_Ignore_Server_Ids:
                 Master_Server_Id: 1
                      Master_UUID:        --> New in 5.6
                 Master_Info_File: /db/DB1/data/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Master_Retry_Count: 86400
                      Master_Bind:
          Last_IO_Error_Timestamp:
         Last_SQL_Error_Timestamp:
                   Master_SSL_Crl:
               Master_SSL_Crlpath:
               Retrieved_Gtid_Set:
                Executed_Gtid_Set:
                    Auto_Position: 0
    1 row in set (0.00 sec)
   
    2013-04-19 14:00:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 1, Error_code: 2003
    2013-04-19 14:01:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 2, Error_code: 2003
    2013-04-19 14:02:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 3, Error_code: 2003
    2013-04-19 14:03:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 4, Error_code: 2003
    2013-04-19 14:04:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 5, Error_code: 2003
    2013-04-19 14:05:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 6, Error_code: 2003
    2013-04-19 14:06:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 7, Error_code: 2003
    2013-04-19 14:07:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 8, Error_code: 2003
    2013-04-19 14:08:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 9, Error_code: 2003
    2013-04-19 14:09:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 10, Error_code: 2003
    2013-04-19 14:10:32 8510 [ERROR] Slave I/O: error reconnecting to master 'repl@172.22.187.118:13306' - retry-time: 60  retries: 11, Error_code: 2003
Until the master has been upgraded
    2013-04-19 14:11:32 8510 [Note] Slave: connected to master 'repl@172.22.187.118:13306',replication resumed in log 'mysql-bin.000001' at position 11269

 6. Shutdown master database
mysqladmin -u root shutdown

 7. Upgrade MySQL rpms on master to 5.6.10
 I had to remove the old binaries and install new ones. This involved a rpm -e to remove the old version and a rpm -i to install the new 5.6 version.
8. Start master database on "master" with 5.6.10.  This produces the following errors:
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_user_by_event_name' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'users' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
    2013-04-19 09:11:28 4160 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure
    2013-04-19 09:11:28 4160 [Note] Event Scheduler: Loaded 0 events
    2013-04-19 09:11:28 4160 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.6.10-log'  socket: '/db/DB1/config/mysql.sock'  port: 13306  MySQL Community Server (GPL)
9. Run "/usr/bin/mysql_upgrade" command on "master"
    [mysql@mysqldb5 DB1 /home/mysql ]$ mysql_upgrade
    Looking for 'mysql' as: mysql
    Looking for 'mysqlcheck' as: mysqlcheck
    Running 'mysqlcheck' with connection arguments: '--port=13306' '--socket=/db/DB1/config/mysql.sock'
    Warning: Using a password on the command line interface can be insecure.
    Running 'mysqlcheck' with connection arguments: '--port=13306' '--socket=/db/DB1/config/mysql.sock'
    Warning: Using a password on the command line interface can be insecure.
    mysql.backup_history                               OK
    mysql.backup_progress                              OK
    mysql.columns_priv                                 OK
    mysql.db                                           OK
    mysql.event                                        OK
    mysql.func                                         OK
    mysql.general_log                                  OK
    mysql.help_category                                OK
    mysql.help_keyword                                 OK
    mysql.help_relation                                OK
    mysql.help_topic                                   OK
    mysql.host                                         OK
    mysql.ndb_binlog_index                             OK
    mysql.plugin                                       OK
    mysql.proc                                         OK
    mysql.procs_priv                                   OK
    mysql.proxies_priv                                 OK
    mysql.servers                                      OK
    mysql.slow_log                                     OK
    mysql.tables_priv                                  OK
    mysql.time_zone                                    OK
    mysql.time_zone_leap_second                        OK
    mysql.time_zone_name                               OK
    mysql.time_zone_transition                         OK
    mysql.time_zone_transition_type                    OK
    mysql.user                                         OK
    suetest.child                                      OK
    suetest.innodb_monitor                             OK
    suetest.parent                                     OK
    suetest.product                                    OK
    suetest.suepart                                    OK
    suetest.suetest                                    OK
    suetest.suetestpart                                OK
    suetest.tblsessions                                OK
    suetest.tblsessions2                               OK
    testdb.smalltab                                    OK
    upgrade_db.tab1                                    OK
    vantage.assumptivedata                             OK
    vantage.assumptivedata1                            OK
    vantage.marketdata                                 OK
    vantage.suetest                                    OK
    Running 'mysql_fix_privilege_tables'...
    Warning: Using a password on the command line interface can be insecure.
    OK

 10. I was hoping that upgrading the master the 'mysql_upgrade' would replicate to the master.  Instead I bounce the database(s) to ensure there are no issues on startup.

 Slave
2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure
        2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure
        2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
        2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'users' has the wrong structure
        2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
        2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
        2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
        2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
        2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
        2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
        2013-04-19 14:22:43 10377 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure
        2013-04-19 14:22:43 10377 [Note] Event Scheduler: Loaded 0 events
        2013-04-19 14:22:43 10377 [Note] /usr/sbin/mysqld: ready for connections.
        Version: '5.6.10-log'  socket: '/db/DB1/config/mysql.sock'  port: 13306  MySQL Community Server (GPL)Master  - Clean restart

 Must run mysql_upgrade on the slave
    2013-04-19 14:23:44 10377 [Note] /usr/sbin/mysqld: Shutdown complete

    130419 14:23:44 mysqld_safe mysqld from pid file /db/DB1/config/mysqld.pid ended
    130419 14:24:02 mysqld_safe Starting mysqld daemon with databases from /db/DB1/data
    2013-04-19 14:24:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2013-04-19 14:24:02 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 4857)
    2013-04-19 14:24:02 11254 [Note] Plugin 'FEDERATED' is disabled.
    2013-04-19 14:24:02 2b205c0d1590 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
    2013-04-19 14:24:02 11254 [Note] InnoDB: The InnoDB memory heap is disabled
    2013-04-19 14:24:02 11254 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2013-04-19 14:24:02 11254 [Note] InnoDB: Compressed tables use zlib 1.2.3
    2013-04-19 14:24:02 11254 [Note] InnoDB: CPU does not support crc32 instructions
    2013-04-19 14:24:02 11254 [Note] InnoDB: Using Linux native AIO
    2013-04-19 14:24:02 11254 [Note] InnoDB: Initializing buffer pool, size = 2.0G
    2013-04-19 14:24:03 11254 [Note] InnoDB: Completed initialization of buffer pool
    2013-04-19 14:24:03 11254 [Note] InnoDB: Highest supported file format is Barracuda.
    2013-04-19 14:24:03 11254 [Note] InnoDB: 128 rollback segment(s) are active.
    2013-04-19 14:24:03 11254 [Note] InnoDB: Waiting for purge to start
    2013-04-19 14:24:03 11254 [Note] InnoDB: 1.2.10 started; log sequence number 61435425807
    2013-04-19 14:24:03 11254 [Note] Server hostname (bind-address): '*'; port: 13306
    2013-04-19 14:24:03 11254 [Note] IPv6 is available.
    2013-04-19 14:24:03 11254 [Note]   - '::' resolves to '::';
    2013-04-19 14:24:03 11254 [Note] Server socket created on IP: '::'.
    2013-04-19 14:24:03 11254 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
    2013-04-19 14:24:03 11254 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
    2013-04-19 14:24:03 11254 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000003' at position 120, relay log '/db/DB1/logs/relay-logs/relay-log.000012' position: 283
    2013-04-19 14:24:03 11254 [Note] Slave I/O thread: connected to master 'repl@172.22.187.118:13306',replication started in log 'mysql-bin.000003' at position 120
    2013-04-19 14:24:03 11254 [Note] Event Scheduler: Loaded 0 events
    2013-04-19 14:24:03 11254 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.6.10-log'  socket: '/db/DB1/config/mysql.sock'  port: 13306  MySQL Community Server (GPL)

11.  Test replication.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)