Instantly share code, notes, and snippets.

Embed
What would you like to do?
Pasang 2 atau lebih 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            = mysql

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

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

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


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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment