Difference between revisions of "Mysql"

From Atomicorp Wiki
Jump to: navigation, search
m (Installation)
 
(11 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
== Features ==
 
== Features ==
  
* Based on Mysql 5.1.x branch
+
* Based on Mysql 5.5.x branch
 
* Uses the design from Fedora, RHEL, and CentOS
 
* Uses the design from Fedora, RHEL, and CentOS
 
  
 
== Installation ==
 
== Installation ==
  
 +
To Upgrade to Mysql 5.5 on CentOS/RHEL/Fedora
  
To Upgrade to Mysql 5.1 on CentOS/RHEL/Fedora
+
'''Steps 3 and 4 only apply if you are upgrading from MySQL 5.0.x See version differences in following link''' http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html
  
Step 1) Set up the atomic channel:  
+
Step 1) Set up the atomic channel (See [[Getting_Started]]):  
  
 
  wget -q -O - http://www.atomicorp.com/installers/atomic | sh
 
  wget -q -O - http://www.atomicorp.com/installers/atomic | sh
  
Step 2) Upgrade to Mysql 5.1
+
Step 2) Upgrade to Mysql 5.5
  
 
  yum upgrade mysql
 
  yum upgrade mysql
  
Step 3) Update /etc/my.cnf, the following token has been deprecated and must be removed
+
Step 3) MySQL 5.1 removed the BERKLEY storage engine. Since it was not commonly used, to save resources it was recommended this be turned off. You '''must''' comment the following out or remove it from /etc/my.cnf or MySQL 5.1 '''will not start!'''
  
 
  skip-bdb
 
  skip-bdb
  
 
+
Step 4) Update your MySQL tables
Step 4) Update your mysql tables
+
  
 
  mysql_upgrade -u root -p
 
  mysql_upgrade -u root -p
 
   
 
   
or for plesk environments:
+
or for Plesk environments (and then the NOTE - this is the exact same login as the Plesk superuser e.g. use "-u admin" and then your admin password):
  
 
  mysql_upgrade -u admin -p
 
  mysql_upgrade -u admin -p
Line 42: Line 41:
 
== Known Issues ==
 
== Known Issues ==
  
 +
== MySQL Library Differs ==
 +
 +
When using some tools (most notably PHPMyAdmin) you may get a notice like this
 +
 +
Your PHP MySQL library version 5.0.90 differs from your MySQL server version 5.1.50. This may cause unpredictable behavior.
 +
 +
This message can be safely ignored. The reason that the library cannot be upgraded with the server is that, in Enterprise Linux, everything is compiled around the old library. Upgrading the library would break a number of programs compiled against it so, for compatibility's sake, it must remain as the one provided by the vendor directly.
  
 +
If you REALLY need the latest libraries you can recompile php from the SRPMS.
  
 
== Problems with MySQL databases and PLESK 9 after upgrade ==
 
== Problems with MySQL databases and PLESK 9 after upgrade ==
Line 57: Line 64:
 
Issue the following command from the shell:
 
Issue the following command from the shell:
  
  mysqlcheck --check-upgrade --all-databases --auto-repair -uadmin -p''youradminpassword''
+
  mysqlcheck --check-upgrade --all-databases --auto-repair -u admin -p''youradminpassword''
  
 
followed by:
 
followed by:

Latest revision as of 16:08, 11 May 2013

Contents

[edit] Features

  • Based on Mysql 5.5.x branch
  • Uses the design from Fedora, RHEL, and CentOS

[edit] Installation

To Upgrade to Mysql 5.5 on CentOS/RHEL/Fedora

Steps 3 and 4 only apply if you are upgrading from MySQL 5.0.x See version differences in following link http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html

Step 1) Set up the atomic channel (See Getting_Started):

wget -q -O - http://www.atomicorp.com/installers/atomic | sh

Step 2) Upgrade to Mysql 5.5

yum upgrade mysql

Step 3) MySQL 5.1 removed the BERKLEY storage engine. Since it was not commonly used, to save resources it was recommended this be turned off. You must comment the following out or remove it from /etc/my.cnf or MySQL 5.1 will not start!

skip-bdb

Step 4) Update your MySQL tables

mysql_upgrade -u root -p

or for Plesk environments (and then the NOTE - this is the exact same login as the Plesk superuser e.g. use "-u admin" and then your admin password):

mysql_upgrade -u admin -p

Step 5) Restart mysql

/etc/init.d/mysqld restart

[edit] Support Packages

Mysqltuner, an excellent tool for investigating performance issues with mysql

 yum install mysqltuner

[edit] Known Issues

[edit] MySQL Library Differs

When using some tools (most notably PHPMyAdmin) you may get a notice like this

Your PHP MySQL library version 5.0.90 differs from your MySQL server version 5.1.50. This may cause unpredictable behavior.

This message can be safely ignored. The reason that the library cannot be upgraded with the server is that, in Enterprise Linux, everything is compiled around the old library. Upgrading the library would break a number of programs compiled against it so, for compatibility's sake, it must remain as the one provided by the vendor directly.

If you REALLY need the latest libraries you can recompile php from the SRPMS.

[edit] Problems with MySQL databases and PLESK 9 after upgrade

If you get the following error after upgrading when you try to log into PLESK then you need to upgrade your database tables:

ERROR: PleskFatalException
bad column in table: `backup_time` time NOT NULL default '00:00:00',
[...]


Issue the following command from the shell:

mysqlcheck --check-upgrade --all-databases --auto-repair -u admin -pyouradminpassword

followed by:

cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql --no-defaults --force --user=admin --password=youradminpassword --host=localhost --database=mysql

This script updates all the mysql privilege tables to be usable by the current version of MySQL.

References:

[1] http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html

[2] http://forum.parallels.com/pda/index.php/t-85461.html

Personal tools