» Unlabelled » Compile mysql 5.1 Linux with innodb and optimize

Compile mysql 5.1 Linux with innodb and optimize

- on 11.33 - No comments
Compile mysql 5.1 Linux with innodb and optimize
Compile manual mysql di linux tidak lah sulit, oke kita langsung install mysql di linux secara manual :)

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.

Demikian artikel .... yang erblog bisa bagikan, semoga bermanfaat dan silahkan dibagikan ke teman-teman anda yang membutuhkan. :) salam ER

Posting Komentar