Author Topic: How to update mysql from 5.1 to 5.6  (Read 63321 times)

0 Members and 1 Guest are viewing this topic.

Offline
*
Re: How to update mysql from 5.1 to 5.6
« Reply #15 on: January 14, 2016, 07:44:55 AM »
There are so much requirement about MySQL5.6, you should update the MySQL to 5.6!
I focus on Magento, Drupal, WordPress and LNAMP.
English Blog: http://www.webdux.com , Chinese Blog: http://www.caosir.com , Company website: http://www.ebaishun.com

Offline
*
Re: How to update mysql from 5.1 to 5.6
« Reply #16 on: February 05, 2016, 09:08:36 PM »
you didn't have to pay for that since you can google for the instructions.
The easiest way is to install MariaDB including MariaDB repo.

https://mariadb.com/kb/en/mariadb/documentation/getting-started/binary-packages/rpm/yum/

Thanks. It almost worked.
Had issues with MySQL failing to start.
Used the following fix:
Quote
Symptom MySQL service failed to start after upgrading from MySQL 5.1 to 5.6 version.

Given below is the error found after upgrading to MySQL server version 5.6 from 5.1. You can get the below error in MySQL error log file.

2014-11-29 02:22:56 2175 [ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
2014-11-29 02:22:56 2175 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2014-11-29 02:22:56 2175 [ERROR] Plugin ‘InnoDB’ init function returned error.
2014-11-29 02:22:56 2175 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
2014-11-29 02:22:56 2175 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-11-29 02:22:56 2175 [ERROR] Aborting

Solution :

To solve this problem add the below given line in /etc/my.cnf file inside [mysqld] block.

innodb_data_file_path = ibdata1:10M:autoextend

---------------------------

service mysqld restart

MySQL updated sucesfully

Offline
*
Re: How to update mysql from 5.1 to 5.6
« Reply #17 on: March 14, 2016, 06:10:00 PM »
Backup your MySQL data files before updating

Update

One more step to do is to update MySQL.user table.
From console run the following command.
#mysql_upgrade --upgrade-system-tables

To verify run the following command from MySQL
#mysql
mysql> SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';

SHould be the following (43 columns in 5.6) :

+--------------+
| column_count |
+--------------+
|           43 |
+--------------+
1 row in set (0.00 sec)

After the step below MySQL 5.6 should work smoothly with CWP.

you didn't have to pay for that since you can google for the instructions.
The easiest way is to install MariaDB including MariaDB repo.

https://mariadb.com/kb/en/mariadb/documentation/getting-started/binary-packages/rpm/yum/

Thanks. It almost worked.
Had issues with MySQL failing to start.
Used the following fix:
Quote
Symptom MySQL service failed to start after upgrading from MySQL 5.1 to 5.6 version.

Given below is the error found after upgrading to MySQL server version 5.6 from 5.1. You can get the below error in MySQL error log file.

2014-11-29 02:22:56 2175 [ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
2014-11-29 02:22:56 2175 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2014-11-29 02:22:56 2175 [ERROR] Plugin ‘InnoDB’ init function returned error.
2014-11-29 02:22:56 2175 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
2014-11-29 02:22:56 2175 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-11-29 02:22:56 2175 [ERROR] Aborting

Solution :

To solve this problem add the below given line in /etc/my.cnf file inside [mysqld] block.

innodb_data_file_path = ibdata1:10M:autoextend

---------------------------

service mysqld restart

MySQL updated sucesfully


Offline
***
Re: How to update mysql from 5.1 to 5.6
« Reply #18 on: July 20, 2016, 06:48:53 AM »
I found a very simple solution and it did it successfully

Upgrading MySQL to 5.5 in CentOS Web Panel [CWP]
https://blog.bullten.com/upgrading-mysql-to-5-5-in-centos-web-panel-cwp/

Upgrading PhpMyAdmin in CentOS Web Panel [CWP]
https://blog.bullten.com/upgrading-phpmyadmin-in-centos-web-panel-cwp/

Ok, now are are at version 5.5. Next step you need to upgrade 5.6 or 5.7 (upto you)
Detail at this url : https://kb.plesk.com/en/127962 , look for section "For centos". By the way, follow the following (what I did) for a shortcut :

Stop MySQL service:
# service mysqld stop

Back up MySQL databases: (Optional)
# mkdir /var/lib/mysqlcopy
# cp -aR /var/lib/mysql/* /var/lib/mysqlcopy

Disable Atomic repository, if it is enabled:(Optional)
# vi atomic.repo
  enabled = 0

Start installing , important!
# yum install http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm

Disable upgrade to MySQL 5.7, enable upgrade to MySQL 5.6: (Optional, if you dont want 5.7 by default)
# vi /etc/yum.repos.d/mysql-community.repo
  [mysql56-community]
  enabled=1
  [mysql57-community]
  enabled=0

Install MySQL packages:
# yum install mysql

Start MySQL service:
# service mysqld start

Upgrade your database structure
mysql_upgrade

Important! You must add this line to your mysql configuration, or else you could not add emails account and other SQL query from panel might be failed
sql_mode=NO_ENGINE_SUBSTITUTION

(steps: IN CWP, under menu MySQL Services> Mysql Configuration, add the line above under [mysqld]  section )


Congratulations, you now at Mysql 5.7 , you are able to use
- Foreign keys for MyISAM DB engine
- Fulltext index for InnoDB engine






« Last Edit: July 23, 2016, 04:54:03 AM by locvfx »

Offline
*
Re: How to update mysql from 5.1 to 5.6
« Reply #19 on: July 21, 2016, 01:42:27 PM »


In the above link / article the remo repo will be enabled..

3) Now upgrade MySQL Server.

yum --enablerepo=remi update mysql-server
sudo mysql_upgrade


Question there is no need to disable the remi repo afterwards as it is done later for the atomic repo for upgrading to mysql 5.6 / 5.7? ? It will not break future updates of CWP ?






« Last Edit: July 21, 2016, 04:16:54 PM by devnull »

Offline
***
Re: How to update mysql from 5.1 to 5.6
« Reply #20 on: July 23, 2016, 04:55:56 AM »


In the above link / article the remo repo will be enabled..

3) Now upgrade MySQL Server.

yum --enablerepo=remi update mysql-server
sudo mysql_upgrade


Question there is no need to disable the remi repo afterwards as it is done later for the atomic repo for upgrading to mysql 5.6 / 5.7? ? It will not break future updates of CWP ?

I'm not really sure about it and that's an optional step. I didn't disable it

Offline
*
Re: How to update mysql from 5.1 to 5.6
« Reply #21 on: July 24, 2016, 08:07:38 PM »


Hello locvfx,

Thanks for your answer. I just do not want to break the CWP panel by using other repos.

Maybe a CWP developer can shed a light on this if it is wise to enable other repos.....


Offline
*
Re: How to update mysql from 5.1 to 5.6
« Reply #22 on: February 12, 2017, 08:25:17 PM »
Seriously, MySQL 5.1 is 9 years old! Why is the latest version of CWP still using it? Isn't that kind of backwards?
"The general availability of MySQL 5.6 was announced in February 2013."
That is FOUR years ago. Why isn't MySQL 5.6 or greater the new standard?

Taken from the Wikipedia page for MySQL:

Version 5.1: production release 27 November 2008 (event scheduler, partitioning, plugin API, row-based replication, server log tables)
Version 5.1 contained 20 known crashing and wrong result bugs in addition to the 35 present in version 5.0 (almost all fixed as of release 5.1.51).[32]
MySQL 5.1 and 6.0-alpha showed poor performance when used for data warehousing – partly due to its inability to utilize multiple CPU cores for processing a single query.[33]
Oracle acquired Sun Microsystems on 27 January 2010.[34][35][36]
The day Oracle announced the purchase of Sun, Michael "Monty" Widenius forked MySQL, launching MariaDB, and took a swath of MySQL developers with him.[37]
MySQL Server 5.5 was generally available (as of December 2010). Enhancements and features include:
The default storage engine is InnoDB, which supports transactions and referential integrity constraints.
Improved InnoDB I/O subsystem[38]
Improved SMP support[39]
Semisynchronous replication.
SIGNAL and RESIGNAL statement in compliance with the SQL standard.
Support for supplementary Unicode character sets utf16, utf32, and utf8mb4.
New options for user-defined partitioning.

MySQL Server 6.0.11-alpha was announced[40] on 22 May 2009 as the last release of the 6.0 line. Future MySQL Server development uses a New Release Model. Features developed for 6.0 are being incorporated into future releases.

The general availability of MySQL 5.6 was announced in February 2013. New features included performance improvements to the query optimizer, higher transactional throughput in InnoDB, new NoSQL-style memcached APIs, improvements to partitioning for querying and managing very large tables, TIMESTAMP column type that correctly stores milliseconds, improvements to replication, and better performance monitoring by expanding the data available through the PERFORMANCE_SCHEMA.[41] The InnoDB storage engine also included support for full-text search and improved group commit performance.

The general availability of MySQL 5.7 was announced in October 2015.[42]

MySQL Server 8.0.0-dmr (Milestone Release) was announced 12 September 2016.[43]