MySQL数据库入门之多实例配置方法详解

吾爱主题阅读：301 2024-04-05 16:21:18 评论：0

本文实例讲述了MySQL 数据库入门之多实例配置方法。分享给大家供大家参考，具体如下：

所有的操作都是基于单实例的，mysql多实例在实际生产环境也是非常实用的，因为必须要掌握。

1、什么是多实例

多实例就是一台服务器上开启多个不同的服务端口（默认3306），运行多个mysql的服务进程，这此服务进程通过不同的socket监听不同的服务端口来提供各在的服务，所有实例之间共同使用一套MYSQL的安装程序，但各自使用不同的配置文件、启动程序、数据文件，在逻辑上是相对独立的。

多实例主要作用是：充分利用现有的服务器硬件资源，为不同的服务提供数据服务，但是如果某个实例并发比较高的，同样是会影响到其它实例的性能

2、安装多实例环境准备

安装前需要先安装mysql，但是只需将安装过程进行到make install即可（编译安装），如果使用免安装程序，只需解压软件包即可，今天的环境是通过免安装包来安装mysql主程序（其它的安装可以参考前面的安装过程自行测试）

系统环境

1 2 3 4 [root@centos6 ~]# cat /etc/redhat-release CentOS release 6.5 (Final) [root@centos6 ~]# uname -r 2.6.32-431.el6.x86_64

安装程序

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

首先将软件下载到本地

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

创建安装用户

1 2 3 4 [root@centos6 ~]#groupadd mysql [root@centos6 ~]#useradd mysql -s /sbin/nologin -g mysql -M [root@centos6 ~]#tail -1 /etc/passwd mysql:x:500:500::/home/mysql:/sbin/nologin

创建多实例的数据目录

1 2 3 4 5 6 [root@centos6 tools]# mkdir -p /data/{3306,3307} [root@centos6 tools]# tree /data/ /data/ + -- 3306 + -- 3307 2 directories, 0 files

3、安装MYSQL多实例

接下来进行安装mysql的多实例操作

解压软件

1 2 3 [root@centos6 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 [root@centos6 tools]# tar zxf mysql-5.5.52-linux2.6-x86_64.tar.gz

拷贝配置文件

1 2 3 4 5 6 7 [root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/my-small.cnf /data/3306/my.cnf [root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/mysql.server /data/3306/mysql [root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/my-small.cnf /data/3307/my.cnf [root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/mysql.server /data/3307/mysql

为一规范安装路径，将免安装包拷贝到应用程序目录下

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 [root@centos6 tools]# mv mysql-5.5.52-linux2.6-x86_64 /application/mysql [root@centos6 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 [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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 [root@backup 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

其它的配置可参考配置文件进行修改即可

多实例初始化操作

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 [root@centos6 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和一些文件

1 2 3 4 5 6 7 8 [root@centos6 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

另一个实例的初始化请参考上述操作进行，操作过程不再一一介绍

1 2 3 4 5 6 7 8 [root@centos6 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 、启动多实例并登录

启动服务

1 2 3 4 5 6 7 8 9 10 [root@backup 3307]# /data/3306/mysql start Starting MySQL... [root@backup 3307]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE / OFF NODE NAME mysqld 19986 mysql 10u IPv4 90967 0t0 TCP *:mysql (LISTEN) [root@backup 3307]# /data/3307/mysql start Starting MySQL... [root@backup 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)

检查端口

1 2 3 [root@backup 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

登陆多实例数据库

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 [root@backup ~]# 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 [root@backup ~]# 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)