MySQL資料庫入門——多執行個體配置

標籤：檢查   tcp   mysql的安裝   ase   ...   啟動服務   safe   dex   include   

 

前面介紹了相關的基礎命令操作，所有的操作都是基於單一實例的，mysql多執行個體在實際生產環境也是非常實用的，因為必須要掌握

1、什麼是多執行個體

   多執行個體就是一台伺服器上開啟多個不同的服務連接埠（預設3306），運行多個mysql的服務進程，這此服務進程通過不同的socket監聽不同的服務連接埠來提供各在的服務，所有執行個體之間共同使用一套MYSQL的安裝程式，但各自使用不同的設定檔、啟動程式、資料檔案，在邏輯上是相對獨立的。

    多執行個體主要作用是：充分利用現有的伺服器硬體資源，為不同的服務提供資料服務，但是如果某個執行個體並發比較高的，同樣是會影響到其它執行個體的效能

 

2、安裝多執行個體環境準備

安裝前需要先安裝mysql，但是只需將安裝過程進行到make install即可（編譯安裝），如果使用免安裝程式，只需解壓軟體包即可，今天的環境是通過免安裝包來安裝mysql主程式（其它的安裝可以參考前面的安裝過程自行測試）

系統內容

[[email protected] ~]# cat /etc/redhat-release 

CentOS release 6.5 (Final)

[[email protected] ~]# uname -r

2.6.32-431.el6.x86_64

安裝程式

mysql-5.5.52-linux2.6-x86_64.tar.gz

首先將軟體下載到本地

wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.52-linux2.6-x86_64.tar.gz

建立安裝使用者

[[email protected] ~]#groupadd mysql

[[email protected] ~]#useradd mysql -s /sbin/nologin -g mysql -M

[[email protected] ~]#tail -1 /etc/passwd

mysql:x:500:500::/home/mysql:/sbin/nologin

建立多執行個體的資料目錄

[[email protected] tools]# mkdir -p /data/{3306,3307}

[[email protected] tools]# tree /data/

/data/

+-- 3306

+-- 3307

2 directories, 0 files

 

3、安裝MYSQL多執行個體

    接下來進行安裝mysql的多執行個體操作

解壓軟體

[[email protected] tools]# ll mysql-5.5.52-linux2.6-x86_64.tar.gz 

-rw-r--r--. 1 root root 185855000 Aug 26 21:38 mysql-5.5.52-linux2.6-x86_64.tar.gz

[[email protected] tools]# tar zxf mysql-5.5.52-linux2.6-x86_64.tar.gz

拷貝設定檔

[[email protected] mysql-5.5.52-linux2.6-x86_64]# cp support-files/my-small.cnf /data/3306/my.cnf

[[email protected] mysql-5.5.52-linux2.6-x86_64]# cp support-files/mysql.server /data/3306/mysql

[[email protected] mysql-5.5.52-linux2.6-x86_64]# cp support-files/my-small.cnf /data/3307/my.cnf

[[email protected] mysql-5.5.52-linux2.6-x86_64]# cp support-files/mysql.server /data/3307/mysql

為一規範安裝路徑，將免安裝包拷貝到應用程式目錄下

[[email protected] tools]# mv mysql-5.5.52-linux2.6-x86_64 /application/mysql

[[email protected] tools]# ll /application/mysql

total 72

drwxr-xr-x.  2 root root   4096 Dec  9 17:15 bin

-rw-r--r--.  1 7161 31415 17987 Aug 26 19:24 COPYING

drwxr-xr-x.  3 root root   4096 Dec  9 17:15 data

drwxr-xr-x.  2 root root   4096 Dec  9 17:15 docs

drwxr-xr-x.  3 root root   4096 Dec  9 17:15 include

-rw-r--r--.  1 7161 31415   301 Aug 26 19:24 INSTALL-BINARY

drwxr-xr-x.  3 root root   4096 Dec  9 17:15 lib

drwxr-xr-x.  4 root root   4096 Dec  9 17:15 man

drwxr-xr-x. 10 root root   4096 Dec  9 17:15 mysql-test

-rw-r--r--.  1 7161 31415  2496 Aug 26 19:24 README

drwxr-xr-x.  2 root root   4096 Dec  9 17:15 scripts

drwxr-xr-x. 27 root root   4096 Dec  9 17:15 share

drwxr-xr-x.  4 root root   4096 Dec  9 17:15 sql-bench

drwxr-xr-x.  2 root root   4096 Dec  9 17:15 support-files

修改設定檔與開機檔案

因為是多執行個體，其中參數需要修改，修改後的設定檔如下

設定檔my.cnf

[client]

port = 3307

socket = /data/3307/mysql.sock

[mysql]

no-auto-rehash

[mysqld] user = mysql

port = 3307

socket = /data/3307/mysql.sock

basedir = /application/mysql

datadir = /data/3307/data

#log_long_format

#log-error = /data/3307/error.log

#log-slow-queries = /data/3307/slow.log

pid-file = /data/3307/mysql.pid

server-id = 3    

[mysqld_safe]

log-error=/data/3307/mysql3307.err

pid-file=/data/3307/mysqld.pid

啟動程式檔案mysql

[[email protected] 3307]# cat mysql

#!/bin/sh

init port=3307

mysql_user="root"

mysql_pwd="migongge"

CmdPath="/application/mysql/bin"

mysql_sock="/data/${port}/mysql.sock"

#startup

function_start_mysql() {

if [ ! -e "$mysql_sock" ];then

   printf "Starting MySQL...\n"

/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &

else

  printf "MySQL is running...\n"

exit

fi

}

#stop function

function_stop_mysql() {

if [ ! -e "$mysql_sock" ];then

printf "MySQL is stopped...\n"

exit

else

printf "Stoping MySQL...\n"

${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

fi

}

#restart function

function_restart_mysql() {

   printf "Restarting MySQL...\n"

   function_stop_mysql

   sleep 2

   function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

*)

printf "Usage: /data/${port}/mysql {start|stop|restart}\n"

esac

其它的配置可參考設定檔進行修改即可

多執行個體初始化操作

[[email protected] 3306]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql

Installing MySQL system tables...

161209 18:02:17 [Warning] ‘THREAD_CONCURRENCY‘ is deprecated and will be removed in a future release.

161209 18:02:17 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52-log) starting as process 3336 ...

OK

Filling help tables...

161209 18:02:17 [Warning] ‘THREAD_CONCURRENCY‘ is deprecated and will be removed in a future release.

161209 18:02:17 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52-log) starting as process 3343 ...

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

 

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

 

/application/mysql/bin/mysqladmin -u root password ‘new-password‘

/application/mysql/bin/mysqladmin -u root -h centos6 password ‘new-password‘

 

Alternatively you can run:

/application/mysql/bin/mysql_secure_installation

 

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /application/mysql ; /application/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd /application/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

初始化成功後，會在資料目錄下產生一個資料目錄data和一些檔案

[[email protected] 3306]# ll /data/3306/data/

total 1136

drwx------. 2 mysql root     4096 Dec  9 18:02 mysql

-rw-rw----. 1 mysql mysql   27693 Dec  9 18:02 mysql-bin.000001

-rw-rw----. 1 mysql mysql 1114546 Dec  9 18:02 mysql-bin.000002

-rw-rw----. 1 mysql mysql      38 Dec  9 18:02 mysql-bin.index

drwx------. 2 mysql mysql    4096 Dec  9 18:02 performance_schema

drwx------. 2 mysql root     4096 Dec  9 18:02 test

 

另一個執行個體的初始化請參考上述操作進行，操作過程不再一一介紹

[[email protected] 3307]# ll /data/3307/data/

total 1136

drwx------. 2 mysql root     4096 Dec  9 18:40 mysql

-rw-rw----. 1 mysql mysql   27693 Dec  9 18:40 mysql-bin.000001

-rw-rw----. 1 mysql mysql 1114546 Dec  9 18:40 mysql-bin.000002

-rw-rw----. 1 mysql mysql      38 Dec  9 18:40 mysql-bin.index

drwx------. 2 mysql mysql    4096 Dec  9 18:40 performance_schema

drwx------. 2 mysql root     4096 Dec  9 18:40 test

 

4 、啟動多執行個體並登入

啟動服務

[[email protected] 3307]# /data/3306/mysql start

Starting MySQL...

[[email protected] 3307]# lsof -i :3306

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysqld 19986 mysql 10u IPv4 90967 0t0 TCP *:mysql (LISTEN)

[[email protected] 3307]# /data/3307/mysql

start Starting MySQL...

[[email protected] 3307]# lsof -i :3307

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysqld 21648 mysql 11u IPv4 92899 0t0 TCP *:opsession-prxy (LISTEN)

檢查連接埠

[[email protected] 3307]# netstat -lntup|grep mysql

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 21648/mysqld

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 19986/mysqld

登陸多執行個體資料庫

[[email protected] ~]# mysql -S /data/3306/mysql.sock

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

Your MySQL connection id is 1

Server version: 5.5.51-log Source distribution

Copyright (c) 2000, 2016, 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 data3306;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| data3306 |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

mysql> quit

Bye

[[email protected] ~]# mysql -S /data/3307/mysql.sock

Welcome to the MySQL monitor.

Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.51 Source distribution

Copyright (c) 2000, 2016, 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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.05 sec)

成功登陸，並在3306執行個體中建立資料庫，但是3307執行個體上查看並沒有建立過的資料，說明兩個執行個體是獨立的

註：如果再需要新增一個執行個體，基本的配置步驟同上述一樣，只需要相應修改設定檔與啟動程式檔案中的連接埠號碼與資料目錄的路徑即可，最後可以將多執行個體資料庫啟動命令加入開機自啟動

 

MySQL資料庫入門——多執行個體配置