Tag Archives: multiple mysql on single linux host

MULTIPLE INSTANCES OF MYSQL ON A SINGLE LINUX SERVER


Bagaimana membuat multipe instance mysql di 1 server linux?  Anda dapat menggunakan mysqld_multi dengan melakukan sedikit config di /etc/my.cnf.  Pastikan di my.conf tidak ada port yang sama:) Untuk mencreate multiple instance segaja dibuat dengan menggunakan shell script untuk memberikan kemudahkan kepada admin dan dapat mencreate dalam waktu 1 menit:)
Berikut adalah langkah-langkahnya:
  1. Pastikan mysql sudah terinstall di linux server anda, gunakan command rpm -aq | grep mysql , rpm -aq | grep mysql-server, atau jalankan aja service mysqlnya (/etc/init.d/mysqld restart).
  2. cat > /etc/my.cnf dan buat copykan beris berikut ini:
    mysqld]
    skip-external-locking
    [mysqld_multi]
    mysqld     = /usr/bin/mysqld_safe
    mysqladmin = /usr/bin/mysqladmin
    log        = /var/log/mysqld_multi.log
    user       = mysql
  3. cd /home/ & vim /etc/create_multiple_instance_mysql.sh
    Create file baru dan copykan baris dibawah “create_multipe_instance_mysql.sh ” dan silahkan rubah beberapa parameter, seperti “ID, PORT, CLIENT_NAME, ROOT_PASSWORD,CLIENT_USER,CLIENT_PASSWORD”

    #!/bin/bash
    # Tested on Centos 5.5 & MYSQL 5.07
    # Created : 8 Agustus 2011 By mustafa@intelix.co.id
    ID="2"
    PORT="3308"
    CLIENT_NAME="usr8000" 
    ROOT_PASSWD="rootpasswd"
    CLIENT_USER="ecentrix"
    CLIENT_PASSWD="ecentrixpwd"
     
    echo "" >> /etc/my.cnf
    echo "[mysqld${ID}]" >> /etc/my.cnf
    echo "port              = ${PORT}" >> /etc/my.cnf
    echo "datadir           = /home/${CLIENT_NAME}/mysql/" >> /etc/my.cnf
    echo "pid-file  = /home/${CLIENT_NAME}/mysql/mysql.pid" >> /etc/my.cnf
    echo "socket            = /home/${CLIENT_NAME}/mysql/mysql.sock" >> /etc/my.cnf
    echo "user              = mysql" >> /etc/my.cnf
    echo "log-error = /var/log/log-error-${CLIENT_NAME}.err" >> /etc/my.cnf
     
    mkdir -p /home/${CLIENT_NAME}/mysql
    chown -R mysql.mysql /home/${CLIENT_NAME}
    mysql_install_db --user=mysql --datadir=/home/"${CLIENT_NAME}"/mysql/
    chown -R mysql.mysql /home/${CLIENT_NAME}
    mysqld_multi start ${ID}
     
    sleep 5
     
    echo "1. USE MYSQL; UPDATE USER SET password=password('${ROOT_PASSWD}') WHERE USER='root';"
    mysql -uroot -P ${PORT} -S /home/${CLIENT_NAME}/mysql/mysql.sock -e "USE mysql; UPDATE user SET password=password('${ROOT_PASSWD}') WHERE user='root';"
     
    sleep 5
     
    echo "2. FLUSH PRIVILEGES;"
    mysql -uroot -P ${PORT} -S /home/${CLIENT_NAME}/mysql/mysql.sock -e "FLUSH PRIVILEGES;"
     
    sleep 5
     
    echo "3. GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY '${ROOT_PASSWD}' WITH GRANT OPTION;"
    mysql -uroot -p${ROOT_PASSWD} -P ${PORT} -S /home/${CLIENT_NAME}/mysql/mysql.sock -e "GRANT ALL PRIVILEGES ON *.* to root@'%' IDENTIFIED BY '${ROOT_PASSWD}' WITH GRANT OPTION;"
     
    sleep 5
    echo "4. GRANT ALL PRIVILEGES ON *.* TO ${CLIENT_USER}@'%' IDENTIFIED BY '${CLIENT_PASSWD}' WITH GRANT OPTION;"
    mysql -uroot -p${ROOT_PASSWD} -P ${PORT} -S /home/${CLIENT_NAME}/mysql/mysql.sock -e "GRANT ALL PRIVILEGES ON *.* TO ${CLIENT_USER}@'%' IDENTIFIED BY '${CLIENT_PASSWD}' WITH GRANT OPTION;"
     
    sleep 5
     
    echo "5. GRANT ALL PRIVILEGES ON *.* TO ${CLIENT_USER}@localhost IDENTIFIED BY '${CLIENT_PASSWD}' WITH GRANT OPTION;"
    mysql -uroot -p${ROOT_PASSWD} -P ${PORT} -S /home/${CLIENT_NAME}/mysql/mysql.sock -e "GRANT ALL PRIVILEGES ON *.* TO ${CLIENT_USER}@localhost IDENTIFIED BY '${CLIENT_PASSWD}' WITH GRANT OPTION;"
     
    echo ""
     
    mysqld_multi report ${ID} 
  4. chmod +x /home/create_multipe_instance_mysql.sh
    [root@node2 moses]# sh create_multipe_instance_mysql.sh
    Apabile berhasik maka akan muncul seperti tampilah dbwah ini:

    The latest information about MySQL is available on the web at
    http://www.mysql.com
    Support MySQL by buying support/licenses at http://shop.mysql.com
    1. USE MYSQL; UPDATE USER SET password=password('rootpasswd') WHERE USER='root';
    2. FLUSH PRIVILEGES;
    3. GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'rootpasswd' WITH GRANT OPTION;
    4. GRANT ALL PRIVILEGES ON *.* TO ecentrix@'%' IDENTIFIED BY 'ecentrixpwd' WITH GRANT OPTION;
    5. GRANT ALL PRIVILEGES ON *.* TO ecentrix@localhost IDENTIFIED BY 'ecentrixpwd' WITH GRANT OPTION;
     
    Reporting MySQL servers
    MySQL server from group: mysqld2 is running
  5. Untuk melakukan shutdown ke instance tertentu gunakan command:
    mysqladmin -uroot -prootpasswd -S /home/usr8000/mysql/mysql.sock  shutdown 
  6. Untuk menstart cukup menjalankan command
    mysqld_multi start 2

  7. Untuk melihat semua instance, gunakan command:
    mysqld_multi report 
Advertisements
shisdew

Listens until think alike

moses.spaceku@yahoo.com / voip ipbx

Hosted PBX, IP-PBX SOHO/ CALL CENTER, VOICE GATEWAY, VOICE CARD, COST EFECTIVE SOLUTIONS (LCR), GSM/CDMA GATEWAY

%d bloggers like this: