I've been still running Debian Squeeze 6.0 GNU / Linux on few of the Linux / Apache / MySQL servers, I'm administrating and those servers are running few Wordperss / Joomla websites which lately face severe MySQL performance issues. I tried to optimize using various mysql performance optimization scripts such as mysql-tuner.pl, Tuning-primer.sh and Percona Toolkit – a collection of advanced command-line tools for system administrators and tech / support staff to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually. Though with above tools and some my.cnf tunizations I managed to achieve positive performance improvement results with above optimizations, still I didn't like how MyQSL served queries and since the SQL server is already about 5 years old (running version 5.1) and the PHP on sever is still at 5.3 branch, I was advised by my dear colleague Anatoliy to try version update as a mean to improve SQLserver performance. I took seriously the suggestion to try upgrade as a mean to resolve performance issues in this article I will explain in short what I had to do to make MySQL upgrade a success
Of course to try keep deb installed software versions as fresh as possible possible deb packagse, I'm already using Debian Back Ports (for those who hear it a first time Debian Backports is a special repository for Stable versioned Debian Desktop and Servers – supporting stable releases of Debian Linux) which allows you to keep install packages versions less outdated (than default installable software which usually are way behind latest stable package versions with 2-5 years).
If you happen to administer Stable Debian servers and you never used BackPorts I warmly recommend it as it often includes security patches of packages part of Debian stable releases that reached End Of Support (EOS) and already too old even for security updates to be issued by respective Debian Long Term Suport (LTS) repositories.
If you're like me and still in situation to manage remotely Debian 6.0 Squeeze and its the first time you hear about BackPorts and Debian LTShttps://wiki.debian.org/LTS/ to start using those two add to your /etc/apt/sources.list below 3 lines
Open with vim editor and press shift+G to go to last line of file and then press I to enter INSERT mode, once you're done to save, press (ESC) then press : and type x! – in short key combination for exit and save setting in vim is
Esc + :x!
debian-server:~# vim /etc/apt/sources.list
deb http://http.debian.net/debian squeeze-lts main contrib non-free
deb-src http://http.debian.net/debian squeeze-lts main contrib non-free
deb http://http.debian.net/debian-backports squeeze-backports main
If you haven't been added a security updates line in /etc/apt/sources.list make sure you add also:
deb http://security.debian.org/ squeeze/updates main contrib non-free
deb-src http://security.debian.org/ squeeze/updates main contrib non-free
Then to apply latest security updates and packages from LTS / Backports repository run the usual:
debian-server:~# apt-get update && apt-get –yes upgrade
….
If you need to search a package or install something from just added backports repository use:
debian-server:~# apt-cache -t squeeze-backports search "mysql-server"
auth2db – Powerful and eye-candy IDS logger, log viewer and alert generator
torrentflux – web based, feature-rich BitTorrent download manager
cacti – Frontend to rrdtool for monitoring systems and services
mysql-server-5.1 – MySQL database server binaries and system database setup
mysql-server-core-5.1 – MySQL database server binaries
mysql-server – MySQL database server (metapackage depending on the latest version)
To install specific packages only with all their dependencies from Backports while keeping rest of packages from Debian Stable:
debian-server:~# apt-get install -t squeeze-backports "package_name"
…
In same way you can also search or install specific packages from LTS repo:
debian-server:~# apt-get search -t squeeze-lts "package_name"
…
debian-server:~# apt-get install -t squeeze-lts "package_name"
…
Latest mysql available from Debian BackPorts and LTS is still quite old 5.1.73-1+deb6u1 therefore I made an extensive research online on how can I easily update MySQL 5.1 to MySQL 5.5 / 5.6 on Debian Stable Linux.
Luckily there were already DotDeb deb repositories for Debian LAMP (Linux / Apache / MySQL / PHP / Nginx ) running servers prepared in order to keep the essential Webserver services up2date even long after distro official support is over. I learned about existence of this repo thanks to a Ryan Tate's post who updates his LAMP stack on TurnKey Linux which by the way is based on slightly modified official stable Debian Linux releases packages.
To start using DotDeb repos add in /etc/apt/sources.list (depending whereh you're on Squeeze or Wheeze Debian):
deb http://packages.dotdeb.org squeeze all
deb-src http://packages.dotdeb.org squeeze all
or for Debian Wheezy add repos:
deb http://packages.dotdeb.org wheezy all
deb-src http://packages.dotdeb.org wheezy all
I was updating my DebianLatest MySQL / PHP / Apache release to Latest ones on (6.0.4) Squeeze so added above squeeze repos:
Before refreshing list of package repositories, to authenticate repos issue:
debian-server:~# wget -q http://www.dotdeb.org/dotdeb.gpg
debian-server:~# apt-key add dotdeb.gpg
…
Once again to update my packages from newly added DodDeb repository
debian-server:~# apt-get update
…
Before running the SQL upgrade to insure myself, I dumped all databases with:
debian-server:~# mysqldump -u root -p -A > /root/dump.sql
Finally I was brave enough to run apt-get dist-upgrade to update with latest LAMP packages
debian-server:~# apt-get dist-upgrade
Reading package lists… Done
Building dependency tree
Reading state information… Done
Calculating upgrade… Done
The following packages will be REMOVED:
mysql-client-5.1 mysql-server mysql-server-5.1
The following NEW packages will be installed:
libaio1 libmysqlclient18 mysql-client-5.5 mysql-client-core-5.5 python-chardet python-debian
The following packages will be upgraded:
curl krb5-multidev libapache2-mod-php5 libc-bin libc-dev-bin libc6 libc6-dev libc6-i386 libcurl3 libcurl3-gnutls libcurl4-openssl-dev libevent-1.4-2
libgssapi-krb5-2 libgssrpc4 libjasper1 libk5crypto3 libkadm5clnt-mit7 libkadm5srv-mit7 libkdb5-4 libkrb5-3 libkrb5-dev libkrb53 libkrb5support0 libmysqlclient-dev
libxml2 libxml2-dev locales mysql-client mysql-common ntp ntpdate php-pear php5 php5-cgi php5-cli php5-common php5-curl php5-dev php5-gd php5-imagick php5-mcrypt
php5-mysql php5-odbc php5-recode php5-sybase php5-xmlrpc php5-xsl python-reportbug reportbug unzip
50 upgraded, 6 newly installed, 3 to remove and 0 not upgraded.
Need to get 51.7 MB of archives.
After this operation, 1,926 kB of additional disk space will be used.
Do you want to continue [Y/n]? Y
…
As you see from above output above command updates Apache webservers / PHP and PHP related modules, however it doesn't update MySQL installed version, to update also MySQL server 5.1 to MySQL server 5.5
debian-server:~# apt-get install –yes mysql-server mysql-server-5.5
You will be prompted with the usual Debian ncurses text blue interface to set a root password to mysql server, just set it the same as it used to be on old upgraded MySQL 5.1 server.
Well now see whether mysql has properly restarted with ps auxwwf
debian-server:~# ps axuwwf|grep -i sql
root 22971 0.0 0.0 112360 884 pts/11 S+ 15:50 0:00 | \_ grep -i sql
root 19436 0.0 0.0 115464 1556 pts/1 S 12:53 0:00 /bin/sh /usr/bin/mysqld_safe
mysql 19837 4.0 2.3 728192 194552 pts/1 Sl 12:53 7:12 \_ /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib/mysql/plugin –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306
root 19838 0.0 0.0 110112 700 pts/1 S 12:53 0:00 \_ logger -t mysqld -p daemon.error
In my case it was running, however if it fails to run try to debug what is going wrong on initialization by manually executing init script /etc/init.d/mysql stop; /etc/init.d/mysql start and look for errors. You can also manually try to run mysqld_safe from console if it is not running run:
debian-server:~# /usr/bin/mysqld_safe &
…
This should give you a good hint on why it is failing to run
One more thing left is to check whether php modules load correctly to do so issue:
debian-server:~# php -v
Failed loading /usr/lib/php5/20090626/xcache.so: /usr/lib/php5/20090626/xcache.so: cannot open shared object file: No such file or directory
Failed loading /usr/lib/php5/20090626/xdebug.so: /usr/lib/php5/20090626/xdebug.so: cannot open shared object file: No such file or directory
You will likely get an exception (error) like above.
To solve the error, reinstall xcache and xcache-debug debs
debian-server:~# apt-get purge php5-xcache php5-xdebug
Now PHP + MySQL + Apache environment should be running much smootly.
Upgrading the MySQL server / PHP library to MySQL server 5.6 / PHP 5.5 on Wheeze Linux is done in very much analogous ways all you have to do is change the repositories with above wheeze 7.0 ones and to follow the process as described in this article. I haven't tested update on Wheezy yet, so if you happen to try my article with wheezy reports and got a positive upgrade result please drop a comment.