Compile mysql 5.1 with innodb and optimize for heavy usage

MySQL stopped default inclusion of InnoDB in latest 5.1.x, so if you need InnoDB, you have to compile it from source. I have done following steps in CentOS 5.4 server to compile MySQL and optimize it later for a heavy site:

1. Remove earlier installation of MySQL, if any and download source rpm from MySQL site. I’ve also removed earlier installation of PHP here to upgrade it:

$ rpm -e php-mysql mysql-server php php-devel php-pear

Download the latest source rpm from MySQL download site, you will get source rpm like below and install it. Installation of source rpm will put it as tar file in /usr/src/redhat/SOURCES, make sure to create /usr/src/redhat/SOURCES directory before installation:

$ ls
MySQL-community-5.1.45-1.rhel5.src.rpm

Add mysql user/group if its already not there:

$ groupadd mysql
$ useradd -g mysql mysql
 
$ rpm -i MySQL-community-5.1.45-1.rhel5.src.rpm
warning: user mysqldev does not exist - using root
warning: group mysqldev does not exist - using root
warning: user mysqldev does not exist - using root
warning: group mysqldev does not exist - using root
 
ignore above warnings. 
 
$ cd /usr/src/redhat/SOURCES
$ ls
mysql-5.1.45.tar.gz

2. Install required dependencies to compile MySQL:

$ yum install gcc gcc-c++ ncurses-devel

Uncompress and compile it:

$ tar xzf mysql-5.1.45.tar.gz
$ cd mysql-5.1.45
$ ./configure --prefix=/var/lib/mysql  --localstatedir=/var/lib/mysql/data --sysconfdir=/etc/mysql --with-mysqld-user=mysql --with-plugins=innobase,myisam --enable-thread-safe-client
$ make
$ make install

I need only InnoDB and MyISAM so enabled these storage engines with threads in configure command above. If you need, you can convert your existing MyISAM DB into InnoDB quickly. for more configure options you can check documentation here.

3. After compilation, you can check that everything related to MySQL is there in /var/lib/directory, change owner of this directory from root to mysql:

$ cd /var/lib/ 
$ ls mysql/
bin  docs  include  lib  libexec  mysql-test  share  sql-bench
$ chown -R mysql:mysql mysql/

Create data directory and initialize default mysql database:

$ cd mysql
$ bin/mysql_install_db --user=mysql

Copy startup script to /etc/init.d

$ cp /usr/src/redhat/SOURCES/mysql-5.1.45/support-files/mysql.server /etc/init.d/mysql
$ chmod +x /etc/init.d/mysql
$ chkconfig --level 345 mysql on

4. Create my.cnf file, start MySQL and update root password:

Create initial my.cnf file by coping sample file, tuned for heavy usage using InnoDB:

$ cp /usr/src/redhat/SOURCES/mysql-5.1.45/support-files/my-innodb-heavy-4G.cnf /etc/my.cnf

Open my.cnf and set values of some variables according to specs of your server:

$ vi /etc/my.cnf
innodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_file_size=100M
innodb_buffer_pool_size=5G
innodb_additional_mem_pool_size=20M
innodb_thread_concurrency=8

Here I’ve set buffer_pool_size as 5G, keep it increasing upto 80% of RAM in the server. Set thread concurrency according to available cpus*2. Here you can see more optimization tips. Start MySQL service now:

$ /etc/init.d/mysql start
Starting MySQL..                                           [  OK  ]

Jump into mysql prompt and update password:

$ mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.45-log Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> use mysql; 
mysql> UPDATE user SET Password=PASSWORD('yourpass') WHERE User='root' ;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
 
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit;

5. We are done with MySQL. You can also run Multiple MySQL servers in a single machine, I’ve already blogged steps to implement that. You are good at this point about MySQL, continuing reading if you need to install/config latest PHP also. I’m going to install PHP 5.2.x using CentOS’s Testing repository:

Create testing repository:

$ cat > /etc/yum.repos.d/CentOS-Testing.repo
[c5-testing]
name=CentOS-5 Testing 
baseurl=http://dev.centos.org/centos/$releasever/testing/$basearch/
enabled=1
gpgcheck=1
gpgkey=http://dev.centos.org/centos/RPM-GPG-KEY-CentOS-testing

Press Ctrl+d (once) to save file.

Install PHP stuff:

$ yum install --enablerepo=c5-testing php-mysql php php-devel php-pear php-mcrypt php-mbstring

6. Install PhpMyAdmin to provide a GUI for MySQL:

$ cd /opt/
$ wget http://downloads.sourceforge.net/project/phpmyadmin/phpMyAdmin/3.3.2/phpMyAdmin-3.3.2-english.zip?use_mirror=space
$ unzip phpMyAdmin-3.3.2-english.zip
$ mv phpMyAdmin-3.3.2 phpmyadmin
$ cd phpmyadmin
$ cp config.sample.inc.php config.inc.php

Update phpmyadmin config.inc.php according to your environment. Add an alias to access phpmyadin in your httpd.conf file:

$ vi /etc/httpd/conf/httpd.conf
### add following lines in this file
Alias /phpmyadmin/ "/opt/phpmyadmin/"
 
<Directory "/opt/phpmyadmin">
    Options Indexes MultiViews
    AllowOverride None
    Order allow,deny
    Allow from all
</Directory>

Restart httpd service to apply new upgrade of PHP and to access PhpMyAdmin and check. I also noticed one issue while trying to connect MySQL from remote, like from PhpMyAdmin. The programs searches for MySQL sock but unable to find. to resolve this create a symlink in to /var/lib/mysql:

$ ln -s /tmp/mysql.sock /var/lib/mysql

Check again and everything should run as expected.

This entry was posted on Wednesday, April 21st, 2010 at 6:41 pm and is filed under database. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Linux Admin Zone