mysql多執行個體配置使用方法

    mysql多執行個體是在一台伺服器安裝mysql資料庫軟體,配置不同的監聽端口,應用程式可以根據端口連接配接不同的資料庫,庫與庫互不影響.

1.下載下傳和安裝mysql資料庫

[root@node3 ~]# tar xvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz  -C /usr/local/

[root@node3 ~]# cd /usr/local/

[root@node3 local]# mv mysql-5.6.23-linux-glibc2.5-x86_64/ mysql-5.6.23

[root@node3 local]# chown  -R root:mysql mysql-5.6.23/

[root@node3 local]# chown  -R mysql:mysql mysql-5.6.23/data/

[root@node3 local]# cd mysql-5.6.23/

[root@node3 mysql-5.6.23]# ./scripts/mysql_install_db --user=mysql --group=mysql --basedir=/usr/local/mysql-5.6.23 --datadir=/usr/local/mysql-5.6.23/data 

[root@node3 mysql-5.6.23]# cp -a my.cnf  /etc/

[root@node3 mysql-5.6.23]# cp -a support-files/mysql.server  /etc/init.d/mysqld

[root@node3 mysql-5.6.23]# chkconfig --add mysql

[root@node3 mysql-5.6.23]# chkconfig  mysqld on

2.修改配置檔案

[root@node3 mysql-5.6.23]# vim /etc/my.cnf     --其它的參數可以删除,如果需要加參數可以在各執行個體選項中添加

[mysqld_multi]

mysqld=/usr/local/mysql-5.6.23/bin/mysqld_safe

mysqladmin=/usr/local/mysql-5.6.23/bin/mysqladmin

log=/var/log/mysql.log

[mysqld1]

socket=/tmp/mysql1.sock

port=3306

server-id=1

pid-file=/data/mysql1.pid

datadir=/data1

user=mysql

[mysqld2]

socket=/tmp/mysql2.sock

port=3307

server-id=2

pid-file=/data/mysql2.pid

datadir=/data2

[mysqld3]

socket=/tmp/mysql3.sock

port=3308

server-id=3

pid-file=/data/mysql3.pid

datadir=/data3

[mysqld4]

socket=/tmp/mysql4.sock

port=3309

server-id=4

pid-file=/data/mysql4.pid

datadir=/data4

[root@node3 mysql-5.6.23]#

3.建立資料目錄和初使化資料庫

[root@node3 mysql-5.6.23]# mkdir /data{1,2,3,4}    --建立資料存放的檔案夾

[root@node3 mysql-5.6.23]# chown -R mysql:mysql /data{1,2,3,4}     --添加相應的權限

[root@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/scripts/mysql_install_db  --user=mysql --group=mysql --basedir=/usr/local/mysql-5.6.23 --datadir=/data1 &   --初使化資料庫

[root@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/scripts/mysql_install_db  --user=mysql --group=mysql --basedir=/usr/local/mysql-5.6.23 --datadir=/data2 &

[root@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/scripts/mysql_install_db  --user=mysql --group=mysql --basedir=/usr/local/mysql-5.6.23 --datadir=/data3 &

[root@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/scripts/mysql_install_db  --user=mysql --group=mysql --basedir=/usr/local/mysql-5.6.23 --datadir=/data4 &

[root@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqld_multi --defaults-extra-file=/etc/my.cnf start 1,2,3,4    --啟動資料庫執行個體

[root@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqld_multi  report  --檢視執行個體是否啟動

Reporting MySQL servers

MySQL server from group: mysqld1 is running

MySQL server from group: mysqld2 is running

MySQL server from group: mysqld3 is running

MySQL server from group: mysqld4 is running

[root@node3 mysql-5.6.23]# netstat -antup | grep mysql

tcp        0      0 :::3307      :::*                        LISTEN      22774/mysqld     

tcp        0      0 :::3308      :::*                        LISTEN      22811/mysqld      

tcp        0      0 :::3309      :::*                        LISTEN      22801/mysqld      

tcp        0      0 :::3306      :::*                        LISTEN      22810/mysqld       

[root@node3 mysql-5.6.23]# 

4.登陸各執行個體,驗證各執行個體是互相獨立的

[root@node3 mysql-5.6.23]# mysqladmin -u root password 'system' -S /tmp/mysql1.sock  --修改初使密碼

Warning: Using a password on the command line interface can be insecure.

[root@node3 mysql-5.6.23]# mysql -u root -p  -S /tmp/mysql1.sock   --登陸執行個體1

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> create database tong;

Query OK, 1 row affected (0.02 sec)

mysql> \u tong

Database changed

mysql> create table t(a int);

Query OK, 0 rows affected (0.29 sec)

mysql> insert into t values(1);

Query OK, 1 row affected (0.05 sec)

mysql> exit

Bye

[root@node3 mysql-5.6.23]# mysql -u root -p  -S /tmp/mysql2.sock   --執行個體2不能用執行個體1的密碼

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@node3 mysql-5.6.23]# mysqladmin -u root password 'system2' -S /tmp/mysql2.sock 

[root@node3 mysql-5.6.23]# mysql -u root -p  -S /tmp/mysql2.sock   --執行個體2可以登陸了

Your MySQL connection id is 6

ERROR 1049 (42000): Unknown database 'tong'

5.關閉單個執行個體

[root@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqld_multi  stop 1-2

[root@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqld_multi  report  

MySQL server from group: mysqld1 is not running

MySQL server from group: mysqld2 is not running

本文轉自 z597011036 51CTO部落格，原文連結：http://blog.51cto.com/tongcheng/1640977，如需轉載請自行聯系原作者