upgrading MySQL on debian lenny with apt-get or aptitude

To clarify things, the version currently on Debian lenny is 5.0.51, this DOES have the 4GB limit mentioned in the previous post, what you need is 5.0.52+ (See previous post)

To get that on debian lenny, You do not need to recompile or compile anything

Add the line below to your /etc/apt/sources.list

deb http://backports.debian.org/debian-backports lenny-backports main

apt-get update
apt-get -t lenny-backports install mysql-server-5.1

This should have you running with a key_buffer of any size you wish that your ram permits, and to overcome the 4GB limit on the MyISAM key buffer

Happy Databasing

mysql key_buffer_size not limited to 4GB on 64Bit systems

Even though originally, key_buffer_size (The MyISAM index buffer) should have been limited to 4GBs on 32 and 64 bit operating systems, the limit did not apply any more for whatever reason the designer wanted to set this limit, and therefore, there is no 4GB limit on the  key buffer size for MyISAM tables.

The 4GB limit stays for join_buffer_size, sort_buffer_size, and myisam_sort_buffer_size, But even those had the 4GB limit removed on 5.1.23/5.2.6
In any case, all the literature on the internet stating that there is a 4GB limit on the key_buffer_size are either outdated, or based on outdated literature.
Settings for read_buffer_size and read_rnd_buffer_size
are limited to 2GB on all platforms.

Setting up MySQL on Ubuntu

Setting up MySQL to connect to from PLESK for example is very simple

apt-get install mysql-server

Then, you will need to allow mysql to listen to the outside world

Edit /etc/mysql/my.cnf

and comment out the line

bind-address = 127.0.0.1

then, you will need to allow the root user to connect from outside the server

On the command prompt

mysql -uroot –password=”aPassWord”

use mysql;
select host, user from user;

Now look for the root user with the hostname of the machine, assuming it came out as thishost

update user set host=’%’ where user=’root’ and host=’thishost’;

flush privileges;

quit

And you should be all set

If you don’t perform the above operations, PLESK will complain when you add the server to the list of database servers, You will probably see an error like

Error: Test connection to the database server has failed because of network problems:
Failed to connect to database: Can’t connect to MySQL server on ‘host.domain.com’ (10061)