Langkau ke kandungan utama

Banyak Pangkalan Data MySQL dalam Satu Komputer Pelayan

Pasang 2 atau lebih Pangkalan Data MySQL dalam Satu Komputer Pelayan

Prolog: Persekitaran

  1. OS: Debian 8.9
  2. MySQL: Versi 5.5
  3. kebenaran super user

Peringatan! sebelum mengubah apa-apa tetapan

salin dulu fail tetapan asal untuk backup
me@local$:\>sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

konfigurasi

mysqld_multi --example
Contoh lengkap untuk konfigurasi mysql multi adalah dengan arahan di atas
me@local$\>sudo mysqld_multi --example
# This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf
#
# SOME IMPORTANT NOTES FOLLOW:
#
# 1.COMMON USER
#
#   Make sure that the MySQL user, who is stopping the mysqld services, has
#   the same password to all MySQL servers being accessed by mysqld_multi.
#   This user needs to have the 'Shutdown_priv' -privilege, but for security
#   reasons should have no other privileges. It is advised that you create a
#   common 'multi_admin' user for all MySQL servers being controlled by
#   mysqld_multi. Here is an example how to do it:
#
#   GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password'
#
#   You will need to apply the above to all MySQL servers that are being
#   controlled by mysqld_multi. 'multi_admin' will shutdown the servers
#   using 'mysqladmin' -binary, when 'mysqld_multi stop' is being called.
#
# 2.PID-FILE
#
#   If you are using mysqld_safe to start mysqld, make sure that every
#   MySQL server has a separate pid-file. In order to use mysqld_safe
#   via mysqld_multi, you need to use two options:
#
#   mysqld=/path/to/mysqld_safe
#   ledir=/path/to/mysqld-binary/
#
#   ledir (library executable directory), is an option that only mysqld_safe
#   accepts, so you will get an error if you try to pass it to mysqld directly.
#   For this reason you might want to use the above options within [mysqld#]
#   group directly.
#
# 3.DATA DIRECTORY
#
#   It is NOT advised to run many MySQL servers within the same data directory.
#   You can do so, but please make sure to understand and deal with the
#   underlying caveats. In short they are:
#   - Speed penalty
#   - Risk of table/data corruption
#   - Data synchronising problems between the running servers
#   - Heavily media (disk) bound
#   - Relies on the system (external) file locking
#   - Is not applicable with all table types. (Such as InnoDB)
#     Trying so will end up with undesirable results.
#
# 4.TCP/IP Port
#
#   Every server requires one and it must be unique.
#
# 5.[mysqld#] Groups
#
#   In the example below the first and the fifth mysqld group was
#   intentionally left out. You may have 'gaps' in the config file. This
#   gives you more flexibility.
#
# 6.MySQL Server User
#
#   You can pass the user=... option inside [mysqld#] groups. This
#   can be very handy in some cases, but then you need to run mysqld_multi
#   as UNIX root.
#
# 7.A Start-up Manage Script for mysqld_multi
#
#   In the recent MySQL distributions you can find a file called
#   mysqld_multi.server.sh. It is a wrapper for mysqld_multi. This can
#   be used to start and stop multiple servers during boot and shutdown.
#
#   You can place the file in /etc/init.d/mysqld_multi.server.sh and
#   make the needed symbolic links to it from various run levels
#   (as per Linux/Unix standard). You may even replace the
#   /etc/init.d/mysql.server script with it.
#
#   Before using, you must create a my.cnf file either in /usr/my.cnf
#   or /root/.my.cnf and add the [mysqld_multi] and [mysqld#] groups.
#
#   The script can be found from support-files/mysqld_multi.server.sh
#   in MySQL distribution. (Verify the script before using)
#

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = my_password

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /var/lib/mysql2/hostname.pid2
datadir    = /var/lib/mysql2
language   = /usr/share/mysql/mysql/english
user       = unix_user1

[mysqld3]
mysqld     = /path/to/mysqld_safe
ledir      = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /var/lib/mysql3/hostname.pid3
datadir    = /var/lib/mysql3
language   = /usr/share/mysql/mysql/swedish
user       = unix_user2

[mysqld4]
socket     = /tmp/mysql.sock4
port       = 3309
pid-file   = /var/lib/mysql4/hostname.pid4
datadir    = /var/lib/mysql4
language   = /usr/share/mysql/mysql/estonia
user       = unix_user3

[mysqld6]
socket     = /tmp/mysql.sock6
port       = 3311
pid-file   = /var/lib/mysql6/hostname.pid6
datadir    = /var/lib/mysql6
language   = /usr/share/mysql/mysql/japanese
user       = unix_user4
Kita akan dapat melihat satu contoh konfigurasi fail yang lengkap untuk memasang banyak pelayan pangkalan data mysql dalam satu PC / Komputer pelayan.
berikut adalah contoh konfigurasi yang aku gunakan (betul punya ni).

Jadikan tetapan ini adalah tetapan baru dalam /etc/mysql/my.cnf

[mysqld_multi]
mysqld          = /usr/bin/mysqld_safe
mysqladmin      = /usr/bin/mysqladmin
user            = multi_admin
password        = MultiAdminPassword

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
datadir         = /var/lib/mysql

[mysqld2]
socket          = /tmp/mysql.sock2
port            = 3307
pid-file        = /var/lib/mysql2/hostname.pid2
datadir         = /var/lib/mysql2
user            = multi_admin


[mysqld3]
socket          = /tmp/mysql.sock3
port            = 3308
pid-file        = /var/lib/mysql3/hostname.pid3
datadir         = /var/lib/mysql3
user            = multi_admin


[mysqld4]
socket          = /tmp/mysql.sock4
port            = 3309
pid-file        = /var/lib/mysql4/hostname.pid4
datadir         = /var/lib/mysql4
user            = multi_admin


[mysqld5]
socket          = /tmp/mysql.sock5
port            = 3310
pid-file        = /var/lib/mysql5/hostname.pid5
datadir         = /var/lib/mysql5
user            = multi_admin



basedir         = /usr
bindir          = /usr/bin
bind-address    = 0.0.0.0


[mysqld_safe]
syslog
Mulakan semula servis mysql anda
me@local:$\> sudo service mysql restart
Cuba lihat proses yang sedang berjalan dalam komputer pelayan anda.
me@local:$\>$\> ps axf | grep mysql
30043 pts/5    S+     0:00  |   \_ grep mysql
18147 pts/5    S      0:00 /bin/sh /usr/bin/mysqld_safe --socket=/tmp/mysql.sock2 --port=3307 --pid-file=/var/lib/mysql2/hostname.pid2 --datadir=/var/lib/mysql2 --user=mysql
18516 pts/5    Sl     0:01  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql2 --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=diman.err --pid-file=/var/lib/mysql2/hostname.pid2 --socket=/tmp/mysql.sock2 --port=3307
18168 pts/5    S      0:00 /bin/sh /usr/bin/mysqld_safe --socket=/tmp/mysql.sock4 --port=3309 --pid-file=/var/lib/mysql4/hostname.pid4 --datadir=/var/lib/mysql4 --user=mysql
18544 pts/5    Sl     0:01  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql4 --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=diman.err --pid-file=/var/lib/mysql4/hostname.pid4 --socket=/tmp/mysql.sock4 --port=3309
18182 pts/5    S      0:00 /bin/sh /usr/bin/mysqld_safe --socket=/tmp/mysql.sock5 --port=3310 --pid-file=/var/lib/mysql5/hostname.pid5 --datadir=/var/lib/mysql5 --user=mysql
18547 pts/5    Sl     0:01  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql5 --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=diman.err --pid-file=/var/lib/mysql5/hostname.pid5 --socket=/tmp/mysql.sock5 --port=3310
23621 pts/5    S      0:00 /bin/sh /usr/bin/mysqld_safe --socket=/tmp/mysql.sock3 --port=3308 --pid-file=/var/lib/mysql3/hostname.pid3 --datadir=/var/lib/mysql3 --user=mysql
23819 pts/5    Sl     0:01  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql3 --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=diman.err --pid-file=/var/lib/mysql3/hostname.pid3 --socket=/tmp/mysql.sock3 --port=3308
26504 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe
26640 ?        Sl     0:01  \_ /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
26641 ?        S      0:00  \_ logger -t mysqld -p daemon.error
Nampaknya semua servis mysql telah berjalan.

Bina jadual asas dalam setiap pangakalan data tadi

untuk setiap pangkalan data yang telah dipasang, kita perlu isikan / install jadual-jadual asas untuk mysql
me@local$\> sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql2
me@local$\> sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql3
me@local$\> sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql4
me@local$\> sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql5

Kemudian, kita perlu setkan password untuk root

me@local$\> /usr/bin/mysqladmin -u root password "mysql3307" --port=3307 --socket=/tmp/mysql.sock2

me@local$\> /usr/bin/mysqladmin -u root password "mysql3308" --port=3308 --socket=/tmp/mysql.sock3

me@local$\> /usr/bin/mysqladmin -u root password "mysql3309" --port=3309 --socket=/tmp/mysql.sock4

me@local$\> /usr/bin/mysqladmin -u root password "mysql3310" --port=3310 --socket=/tmp/mysql.sock5

!CUBA sambung ke server

me@local$\> mysql -u root -p --socket=/tmp/mysql.sock2
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.5.57-0+deb8u1 (Debian)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Seterusnya, untuk setiap pelayan mysql, kita perlu setkan satu nama pengguna khas untuk kegunaan mysqld_multi sepertimana yang kita setkan dalam /etc/mysql.mysq.cnf

untuk setiap server
  1. log in ke server
$\> mysql -u root -p --port=3307 --socket=/tmp/mysql.sock2
$\> mysql -u root -p --port=3308 --socket=/tmp/mysql.sock3
$\> mysql -u root -p --port=3309 --socket=/tmp/mysql.sock4
$\> mysql -u root -p --port=3310 --socket=/tmp/mysql.sock5
  1. tambah pengguna multi_admin dan berikan kebenaran untuk hentikan servis kepada pengguna tersebut
GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multi_admin_multipass';
contoh lengkap:
$\> mysql -u root -p --port=3307 --socket=/tmp/mysql.sock2
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.5.57-0+deb8u1 (Debian)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multi_admin_multipass';
  • kesannya, kita boleh gunakan arahan mysqld_multi untuk hentikan server.

mysql juga ada arahan khusus untuk ini iaitu mysqld_multi

Jom cuba.
# status / laporan
me@local:$\>sudo mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
MySQL server from group: mysqld5 is running

# laporan khusus
me@local:$\> sudo mysqld_multi report 2
Reporting MySQL servers
MySQL server from group: mysqld2 is running

# mula servis
me@local:$\> sudo mysqld_multi start

# matikan servis
me@local:$\> sudo mysqld_multi stop

# matikan servis server khusus
me@local:$\> sudo mysqld_multi stop 2
me@local:$\> sudo mysqld_multi stop 2
me@local:$\> sudo mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
MySQL server from group: mysqld5 is running

# mula servis server tertentu
me@local:$\> sudo mysqld_multi start 2

me@local:$\> sudo mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
MySQL server from group: mysqld5 is running

Tips tambahan: PHPMYADMIN

Dalam tetapan phpmyadmin, iaitu di /var/www/html/phpmyadmin/config.inc.php
tambah dalam bilangan server
/*
 * localhost
 */

$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['verbose'] = 'localhost';
$cfg['Servers'][$i]['extension'] = 'mysql';

/*
 * localhost2
 */

$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3307';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['verbose'] = 'localhost:3307';
$cfg['Servers'][$i]['extension'] = 'mysql';

/*
 * localhost3
 */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3308';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['verbose'] = 'localhost:3308';
$cfg['Servers'][$i]['extension'] = 'mysql';

/*
 * localhost2
 */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3309';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['verbose'] = 'localhost:3309';
$cfg['Servers'][$i]['extension'] = 'mysql';

/*
 * localhost2
 */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3310';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['verbose'] = 'localhost:3310';
$cfg['Servers'][$i]['extension'] = 'mysql';
Untuk apakah ini semua?


1. Aku sedang test mysql multi master replication
2. test HAproxy
3. hari ni terjumpa http://www.proxysql.com/compare yang memang nampak menarik dan tertarik.. jadi aku rasa nak cuba. Nanti kalau ada masa aku buat tutorial untuk setting server proxysql ni.


Itu sahaja. Moga-moga bermanfaat.


Ulasan

Catat Ulasan

Comment

Catatan popular daripada blog ini

Apa Maksud RON dalam pengkelasan Petrol?

RON dan Petrol Oleh yusdirman, Oktober 2014 RON adalah singkatan daripada Research Octane Number Ianya kaedah penarafan bahan api. Lebih tinggi nilai RON, lebih mahal harga PETROL. Adakah minyak RON 97 lebih berkuasa berbanding RON 95? RON merujuk kepada tahap bahan api menahan rintangan "ketukan" semasa proses pembakaran oleh palam pencucuh dalam enjin. Ianya merujuk kepada tahap ketahanan bahan api untuk tidak terbakar sebelum dinyalakan oleh palam pencucuh dalam enjin. "ketukan" dalam konteks RON ini adalah apabila campuran bahan api dan udara dalam enjin terbakar lebih awal sebelum api dari palam pencucuh menbakarnya. Pembakaran awal ini mungkin disebabkan enjin terlalu panas. Haba dari silinder enjin itu sendiri mungkin telah menyalakan bahan api sebelum palam pencucuh menyala. Ini boleh menurunkan prestasi dan mungkin boleh memudaratkan enjin! Jadi, RON bukannya bermaksud LEBIH KUASA! Ianya bermaksud bahan api itu dapat menahan tekanan dari
Catat Ulasan
lagi ..

Load Balancing dengan Nginx dan Puma

Load Balancing dengan Nginx dan Puma Tutorial ini adalah sangat ringkas hanya untuk menunjukkan kebolehan nginx sebagai load balancer yang dapat menjadi 'orang tengah' antara banyak server di belakangnya Keperluan 1. Paling kurang 2 server aplikasi 2. Satu server diinstall nginx Contoh Persekitaran yang sekarang 1. server1: os: debian application: ruby on rails web server : puma dns : myaplication1.yusdirman.com 2. server2: myaplication2.yusdirman.com os:debian application: ruby on rails web server : puma dns : myaplication1.yusdirman.com 3. nginx diinstall di server1 /ets/nginx/nginx.conf setup (default): worker_processes  2; worker_processes  auto; worker_rlimit_nofile 65536; error_log  logs/error.log  notice; error_log  logs/error.log  info; events {   worker_connections  65536; } http {   include       mime.types;   default_type  application/octet-stream;   sendfile        on;   tcp_nopush on;   tcp_nodelay on;   keepalive_tim
Catat Ulasan
lagi ..

Server Redundancy - Pertindanan Komputer Pelayan

alkisahnya tercari-cari untuk buat server redundancy ( pertindihan komputer pelayan ) atas sebab perlunya server yang sentiasa on-line tanpa ada downtime yang lama. Dan, cabarannya adalah memang kami tak pernah jalani apa-apa latihan, pengalaman dan apa jua pengetahuan berkenaan teknologi ini. Yang pastinya, perlu banyak mengoogle. Setakat ini, ada banyak kaedah untuk memastikan aplikasi masih terus boleh berfungsi walaupun server mati/rosak. 1. guna 2 server yang sama. Bila satu mati, satu lagi yang masih hidup dapat terus melayan pengguna. 2. guna 2 talian rangkaian yang berbeza. Bila satu talian rosak/putus, masih ada satu lagi talian rangkaian yang berfungsi. 3. storan di server guna raid ( redundant array of independent disk ) atau dalam bahasa melayu : barisan disk berlainan yang bertindan. Raid dapat menjadikan sebilangan hd menjadi 1 dengan semua data pada 1 hd adalah sama dengan data pada hd 2. (mirroring) 4. satu IP maya untuk sebilangan server. 2 server yang mena
Catat Ulasan
lagi ..