The Ubuntu 12.04.3 LTS release only provides MySQL 5.1 and MySQL 5.5 using the default Ubuntu package manager.
Oracle (owners of the MySQL(tm)) now provide Debian/Ubuntu APT repositories for all GA and DMR versions of MySQL including supporting Ubuntu 12.04.
The following steps demonstrate upgrading from the Ubuntu 5.5 server package to the Oracle 5.6 server package.
Verify MySQL Packages
$ apt-cache search mysql-server mysql-server - MySQL database server (metapackage depending on the latest version) mysql-server-5.5 - MySQL database server binaries and system database setup mysql-server-core-5.5 - MySQL database server binaries auth2db - Powerful and eye-candy IDS logger, log viewer and alert generator cacti - Frontend to rrdtool for monitoring systems and services torrentflux - web based, feature-rich BitTorrent download manager
Verify MySQL on Server
$ dpkg -l | grep mysql ii libdbd-mysql-perl 4.020-1build2 Perl5 database interface to the MySQL database ii libmysqlclient-dev 5.5.34-0ubuntu0.12.04.1 MySQL database development files ii libmysqlclient18 5.5.34-0ubuntu0.12.04.1 MySQL database client library ii mysql-client-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database client binaries ii mysql-client-core-5.5 5.5.34-0ubuntu0.12.04.1 MySQL database core client binaries ii mysql-common 5.5.34-0ubuntu0.12.04.1 MySQL database common files, e.g. /etc/mysql/my.cnf ii mysql-server-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database server binaries and system database setup ii mysql-server-core-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database server binaries ii php5-mysqlnd 5.3.10-1ubuntu3.8 MySQL module for php5 (Native Driver)
Results may vary based on dependencies.
Checking the MySQL error log (as it’s the right good practice to always do)
$ sudo tail -50 /var/log/mysql/error.log 150402 16:02:49 [Note] Plugin 'FEDERATED' is disabled. 150402 16:02:49 InnoDB: The InnoDB memory heap is disabled 150402 16:02:49 InnoDB: Mutexes and rw_locks use GCC atomic builtins 150402 16:02:49 InnoDB: Compressed tables use zlib 1.2.3.4 150402 16:02:49 InnoDB: Initializing buffer pool, size = 1.0G 150402 16:02:49 InnoDB: Completed initialization of buffer pool 150402 16:02:49 InnoDB: highest supported file format is Barracuda. 150402 16:02:49 InnoDB: Waiting for the background threads to start 150402 16:02:50 InnoDB: 5.5.31 started; log sequence number 20079278867 150402 16:02:50 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 150402 16:02:50 [Note] - '127.0.0.1' resolves to '127.0.0.1'; 150402 16:02:50 [Note] Server socket created on IP: '127.0.0.1'. 150402 16:02:50 [Note] Event Scheduler: Loaded 0 events 150402 16:02:50 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.31-0ubuntu0.12.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
A check shows that this is not the most current version available of 5.5 using the Ubuntu packages.
$ apt-cache show mysql-server-5.5 Package: mysql-server-5.5 Priority: optional Section: database Installed-Size: 31947 Maintainer: Ubuntu DevelopersOriginal-Maintainer: Debian MySQL Maintainers Architecture: amd64 Source: mysql-5.5 Version: 5.5.41-0ubuntu0.12.04.1 ... Package: mysql-server-5.5 Status: install ok installed Priority: optional Section: database Installed-Size: 31950 Maintainer: Ubuntu Developers Architecture: amd64 Source: mysql-5.5 Version: 5.5.31-0ubuntu0.12.04.1 ...
Just to be consistent with keeping current versions, you may choose to update MySQL 5.5 to the current available version.
$ sudo apt-get install mysql-server-5.5 ...
Installing Oracle APT Packaging
The recommended documented way to move to using the Oracle repo is:
cd /tmp # See https://dev.mysql.com/downloads/repo/apt/ for your right distro version wget https://dev.mysql.com/get/mysql-apt-config_0.3.3-2ubuntu12.04_all.deb sudo dpkg -i mysql-apt-config*.deb
This unfortunately uses a cursors based interface which is not something you automate for production systems and not the approach I would suggest.
So doing what this does
echo "deb http://repo.mysql.com/apt/ubuntu/ precise mysql-apt-config deb http://repo.mysql.com/apt/ubuntu/ precise mysql-5.6" | sudo tee /etc/apt/sources.list.d/mysql.list curl -s http://ronaldbradford.com/mysql/mysql.gpg | sudo apt-key add - sudo apt-get update
Now we can look at available versions.
$ apt-cache search mysql-server mysql-server-5.5 - MySQL database server binaries and system database setup mysql-server-core-5.5 - MySQL database server binaries auth2db - Powerful and eye-candy IDS logger, log viewer and alert generator cacti - Frontend to rrdtool for monitoring systems and services torrentflux - web based, feature-rich BitTorrent download manager mysql-community-server - MySQL Server mysql-server - MySQL Server meta package depending on latest version
This is where life gets a little confusing. Because Ubuntu supported MySQL 5.1 (as mysql-server) and MySQL 5.5 (as mysql-server-5.5) it can be misleading.
$ apt-cache show mysql-server $ apt-cache show mysql-server Package: mysql-server Source: mysql-community Version: 5.6.23-1ubuntu12.04 Architecture: amd64 Maintainer: MySQL Release EngineeringInstalled-Size: 46 Depends: mysql-community-server (= 5.6.23-1ubuntu12.04) Homepage: http://www.mysql.com/ Priority: optional Section: database Filename: pool/mysql-5.6/m/mysql-community/mysql-server_5.6.23-1ubuntu12.04_amd64.deb Size: 11644 SHA256: 1cb166cd230d2a4daca761ea80f2f34ee1fc0c92aaae972c914d81746f235d63 SHA1: 63548c852d5faeda751fbf038c0799fbbeac9905 MD5sum: da2f709a29a7cac97c834e6e69929891 Description: MySQL Server meta package depending on latest version The MySQL(TM) software delivers a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server. MySQL Server is intended for mission-critical, heavy-load production systems as well as for embedding into mass-deployed software. MySQL is a trademark of Oracle. This is a meta package that depends on the latest mysql server package available in the repository. Package: mysql-server Priority: optional Section: database Installed-Size: 114 Maintainer: Ubuntu Developers Original-Maintainer: Debian MySQL Maintainers Architecture: all Source: mysql-5.5 Version: 5.5.41-0ubuntu0.12.04.1 ...
We are looking to ensure the Maintainer is the Official Release.
Upgrading to MySQL 5.6
sudo service mysql stop ps -ef | grep mysql sudo apt-get install -y mysql-server
$ sudo apt-get install -y mysql-server ... The following extra packages will be installed: mysql-client mysql-common mysql-community-client mysql-community-server The following packages will be REMOVED: mysql-client-5.5 mysql-client-core-5.5 mysql-server-5.5 mysql-server-core-5.5 The following NEW packages will be installed: mysql-client mysql-community-client mysql-community-server mysql-server The following packages will be upgraded: mysql-common ... Configuration file `/etc/mysql/my.cnf' ==> Modified (by you or by a script) since installation. ==> Package distributor has shipped an updated version. What would you like to do about it ? Your options are: Y or I : install the package maintainer's version N or O : keep your currently-installed version D : show the differences between the versions Z : start a shell to examine the situation The default action is to keep your current version. *** my.cnf (Y/I/N/O/D/Z) [default=N] ? N ... Installing new version of config file /etc/apparmor.d/usr.sbin.mysqld ... 2015-04-02 16:53:07 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead. 2015-04-02 16:53:07 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 150402 16:53:14 mysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect ...
You may think the process is completed, but it is not. Always, Always check the error log. Have you checked your MySQL error log today?
$ sudo tail -300 /var/log/mysql/error.log ... 2015-04-02 16:53:14 20429 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000) 2015-04-02 16:53:14 20429 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead. 2015-04-02 16:53:14 20429 [Note] Plugin 'FEDERATED' is disabled. 2015-04-02 16:53:14 20429 [Note] InnoDB: Using atomics to ref count buffer pool pages 2015-04-02 16:53:14 20429 [Note] InnoDB: The InnoDB memory heap is disabled 2015-04-02 16:53:14 20429 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2015-04-02 16:53:14 20429 [Note] InnoDB: Memory barrier is not used 2015-04-02 16:53:14 20429 [Note] InnoDB: Compressed tables use zlib 1.2.3.4 2015-04-02 16:53:14 20429 [Note] InnoDB: Using Linux native AIO 2015-04-02 16:53:14 20429 [Note] InnoDB: Not using CPU crc32 instructions 2015-04-02 16:53:14 20429 [Note] InnoDB: Initializing buffer pool, size = 1.0G 2015-04-02 16:53:15 20429 [Note] InnoDB: Completed initialization of buffer pool 2015-04-02 16:53:15 20429 [Note] InnoDB: Highest supported file format is Barracuda. 2015-04-02 16:53:15 20429 [Note] InnoDB: 128 rollback segment(s) are active. 2015-04-02 16:53:15 20429 [Note] InnoDB: Waiting for purge to start 2015-04-02 16:53:15 20429 [Note] InnoDB: 5.6.23 started; log sequence number 20079286519 2015-04-02 16:53:15 20429 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 487fda28-d97a-11e4-9254-e0cb4e3feb73. 2015-04-02 16:53:15 20429 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 2015-04-02 16:53:15 20429 [Note] - '127.0.0.1' resolves to '127.0.0.1'; 2015-04-02 16:53:15 20429 [Note] Server socket created on IP: '127.0.0.1'. 2015-04-02 16:53:15 20429 [ERROR] Column count of mysql.events_waits_current is wrong. Expected 19, found 16. Created with MySQL 50541, now running 50623. Please use mysql_upgrade to fix this error. 2015-04-02 16:53:15 20429 [ERROR] Column count of mysql.events_waits_history is wrong. Expected 19, found 16. Created with MySQL 50541, now running 50623. Please use mysql_upgrade to fix this error. 2015-04-02 16:53:15 20429 [ERROR] Column count of mysql.events_waits_history_long is wrong. Expected 19, found 16. Created with MySQL 50541, now running 50623. Please use mysql_upgrade to fix this error. 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host_by_event_name' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Incorrect definition of table performance_schema.events_waits_summary_by_thread_by_event_name: expected column 'THREAD_ID' at position 0 to have type bigint(20), found type int(11). 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user_by_event_name' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_account_by_event_name' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Column count of mysql.file_summary_by_event_name is wrong. Expected 23, found 5. Created with MySQL 50541, now running 50623. Please use mysql_upgrade to fix this error. 2015-04-02 16:53:15 20429 [ERROR] Column count of mysql.file_summary_by_instance is wrong. Expected 25, found 6. Created with MySQL 50541, now running 50623. Please use mysql_upgrade to fix this error. 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Incorrect definition of table performance_schema.mutex_instances: expected column 'LOCKED_BY_THREAD_ID' at position 2 to have type bigint(20), found type int(11). 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'objects_summary_global_by_type' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Incorrect definition of table performance_schema.rwlock_instances: expected column 'WRITE_LOCKED_BY_THREAD_ID' at position 2 to have type bigint(20), found type int(11). 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'setup_actors' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'setup_objects' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_index_usage' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_table' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'table_lock_waits_summary_by_table' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Column count of mysql.threads is wrong. Expected 14, found 3. Created with MySQL 50541, now running 50623. Please use mysql_upgrade to fix this error. 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'events_stages_current' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'events_stages_history' has the wrong structure 2015-04-02 16:53:15 20429 [ERROR] Native table 'performance_schema'.'events_stages_history_long' has the wrong structure ...
Completing the MySQL 5.6 Upgrade
A MySQL upgrade of the meta schema is necessary.
$ sudo mysql_upgrade -uroot -p Enter password: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' Warning: Using a password on the command line interface can be insecure. 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 Running 'mysql_fix_privilege_tables'... Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' Warning: Using a password on the command line interface can be insecure. ... OK
$ sudo service mysql restart $ sudo service mysql restart * Stopping MySQL Community Server 5.6.23 .... * MySQL Community Server 5.6.23 is stopped * Re-starting MySQL Community Server 5.6.23 150402 17:06:17 mysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect. ...... * MySQL Community Server 5.6.23 is started
$ sudo tail -300 /var/log/mysql/error.log ... 2015-04-02 17:06:15 20429 [Note] /usr/sbin/mysqld: Shutdown complete 150402 17:06:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 150402 17:06:17 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2015-04-02 17:06:17 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead. 2015-04-02 17:06:17 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2015-04-02 17:06:17 20994 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000) 2015-04-02 17:06:17 20994 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000) 2015-04-02 17:06:17 20994 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead. 2015-04-02 17:06:17 20994 [Note] Plugin 'FEDERATED' is disabled. 2015-04-02 17:06:17 20994 [Note] InnoDB: Using atomics to ref count buffer pool pages 2015-04-02 17:06:17 20994 [Note] InnoDB: The InnoDB memory heap is disabled 2015-04-02 17:06:17 20994 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2015-04-02 17:06:17 20994 [Note] InnoDB: Memory barrier is not used 2015-04-02 17:06:17 20994 [Note] InnoDB: Compressed tables use zlib 1.2.3.4 2015-04-02 17:06:17 20994 [Note] InnoDB: Using Linux native AIO 2015-04-02 17:06:17 20994 [Note] InnoDB: Not using CPU crc32 instructions 2015-04-02 17:06:17 20994 [Note] InnoDB: Initializing buffer pool, size = 1.0G 2015-04-02 17:06:17 20994 [Note] InnoDB: Completed initialization of buffer pool 2015-04-02 17:06:17 20994 [Note] InnoDB: Highest supported file format is Barracuda. 2015-04-02 17:06:17 20994 [Note] InnoDB: 128 rollback segment(s) are active. 2015-04-02 17:06:17 20994 [Note] InnoDB: Waiting for purge to start 2015-04-02 17:06:17 20994 [Note] InnoDB: 5.6.23 started; log sequence number 20081020877 2015-04-02 17:06:17 20994 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 2015-04-02 17:06:17 20994 [Note] - '127.0.0.1' resolves to '127.0.0.1'; 2015-04-02 17:06:17 20994 [Note] Server socket created on IP: '127.0.0.1'. 2015-04-02 17:06:17 20994 [Note] Event Scheduler: Loaded 0 events 2015-04-02 17:06:17 20994 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.6.23' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)
Correcting errors
As you can see there are several warnings/errors when starting MySQL.
The first is
mysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect.
We solve this with
$ sudo rm -f /etc/mysql/conf.d/mysqld_safe_syslog.cnf $ sudo service mysql restart * Stopping MySQL Community Server 5.6.23 .... * MySQL Community Server 5.6.23 is stopped * Re-starting MySQL Community Server 5.6.23 ...... * MySQL Community Server 5.6.23 is started
This is an Ubuntu default that conflicts with the my.cnf log_error
were are familiar with in monitoring the MySQL error log. You can read my opinion on this in The correct approach to rolling MySQL logs
The second is
2015-04-02 17:06:17 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
We solve this with
sudo sed -ie "s/^key_buffer[^_]/key_buffer_size/" /etc/mysql/my.cnf
Next
2015-04-02 17:18:06 22123 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
We solve this with
sudo sed -ie "s/^myisam-recover[^-]/myisam-recover-options/" /etc/mysql/my.cnf
The warnings are interesting, and will part of the following post on MySQL 5.6 configuration changes discussed in the next point.
2015-04-02 17:22:08 22626 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000) 2015-04-02 17:22:08 22626 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)
Leveraging MySQL 5.6 benefits
We may now have a MySQL 5.6 installation however we are far from utilizing the benefits of MySQL 5.6 fully. In a subsequent post I will talk about the configuration options we need to now consider, both new options such as innodb_purge_threads
and important improvements such as sync_binlog
. There are far greater complex changes including innodb_file_per_table
, master_info_repository
and relay_log_info_repository
and then changes in defaults such as performance_schema
[…] Ubuntu 12.04.3 LTS release only provides MySQL 5.1 and MySQL 5.5 using the default Ubuntu package […]