专栏首页民工哥技术之路MySQL数据库入门——多实例配置

MySQL数据库入门——多实例配置

MySQL数据库入门——多实例配置

前面介绍了相关的基础命令操作,所有的操作都是基于单实例的,mysql多实例在实际生产环境也是非常实用的,因为必须要掌握

1、什么是多实例

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

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

2、安装多实例环境准备

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

系统环境

[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

首先将软件下载到本地

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

创建安装用户

[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

创建多实例的数据目录

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

3、安装MYSQL多实例

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

解压软件

[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

拷贝配置文件

[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

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

[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

[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

[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 

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

多实例初始化操作

[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和一些文件
[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

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

[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 、启动多实例并登录

启动服务

[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)

检查端口

[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

登陆多实例数据库

[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) 

成功登陆,并在3306实例中创建数据库,但是3307实例上查看并没有创建过的数据,说明两个实例是独立的

注:如果再需要新增一个实例,基本的配置步骤同上述一样,只需要相应修改配置文件与启动程序文件中的端口号与数据目录的路径即可,最后可以将多实例数据库启动命令加入开机自启动

本文分享自微信公众号 - 民工哥技术之路(jishuroad),作者:民工哥

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-12-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • CentOS 下 MySQL 8.0 安装部署,超详细!

    MySQL 8正式版8.0.11已发布,官方表示MySQL8要比MySQL 5.7快2倍,还带来了大量的改进和更快的性能!到底谁最牛呢?请看:MySQL 5.7...

    民工哥
  • Linux系统MySQL数据库主从同步实战过程

    今天我们是用二进制安装包进行布署MySQL数据库服务,其它方式的安装布署方法请参考前面的文章

    民工哥
  • Linux系统集群架构线上项目配置实战(四)

    项目到目前为止,前面将所有应用工程的安装与配置已阐述完成,今天将要介绍的是数据库的安装、配置、主从同步等相关过程

    民工哥
  • 原 数据库主从配置日志

    霡霂
  • Mac上做java开发(一):​安装mysql

    首先推荐一款mac软件包管理用具brew,具体安装方法这里就不介绍了,百度一堆方法,brew类似Python的conda包管理工具。

    用户5473628
  • MySQL数据库安装笔记教程

    下载之后,我选择直接解压到D盘,文件地址是:D:\mysql-5.7.23-winx64,这里说这么清楚,是为了接下来的环境配置。你也可以根据自己个人喜好选择对...

    挨踢小子部落阁
  • Linux下MySQL的彻底卸载和安装配置字符集

    前言: Linux环境下MySQL的安装和配置在网上已经有很多教程了。之所以写这篇文章是因为在配置字符集的时候找了网上的一些教程发现并不能用导致折腾了一阵子。...

    逸鹏
  • 【MySQL】标准化安装教程

    yum-y install make gcc-c++cmake bison-devel ncurses-devel numactl libaio

    MySQL技术
  • [Linux]mysql命令导入sql数据

    雨落凋殇
  • 19.多端项目上线部署(1)同步数据库

    玩蛇的胖纸

扫码关注云+社区

领取腾讯云代金券