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 -r2.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/passwdmysql:x:500:500::/home/mysql:/sbin/nologin

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

解压软件

[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/mysqltotal 72drwxr-xr-x. 2 root root 4096 Dec 9 17:15 bin-rw-r--r--. 1 7161 31415 17987 Aug 26 19:24 COPYINGdrwxr-xr-x. 3 root root 4096 Dec 9 17:15 datadrwxr-xr-x. 2 root root 4096 Dec 9 17:15 docsdrwxr-xr-x. 3 root root 4096 Dec 9 17:15 include-rw-r--r--. 1 7161 31415 301 Aug 26 19:24 INSTALL-BINARYdrwxr-xr-x. 3 root root 4096 Dec 9 17:15 libdrwxr-xr-x. 4 root root 4096 Dec 9 17:15 mandrwxr-xr-x. 10 root root 4096 Dec 9 17:15 mysql-test-rw-r--r--. 1 7161 31415 2496 Aug 26 19:24 READMEdrwxr-xr-x. 2 root root 4096 Dec 9 17:15 scriptsdrwxr-xr-x. 27 root root 4096 Dec 9 17:15 sharedrwxr-xr-x. 4 root root 4096 Dec 9 17:15 sql-benchdrwxr-xr-x. 2 root root 4096 Dec 9 17:15 support-files

因为是多实例，其中参数需要修改，修改后的配置文件如下：配置文件my.cnf

[client]port = 3307socket = /data/3307/mysql.sock[mysql]no-auto-rehash[mysqld] user = mysqlport = 3307socket = /data/3307/mysql.sockbasedir = /application/mysqldatadir = /data/3307/data#log_long_format#log-error = /data/3307/error.log#log-slow-queries = /data/3307/slow.logpid-file = /data/3307/mysql.pidserver-id = 3 [mysqld_safe]log-error=/data/3307/mysql3307.errpid-file=/data/3307/mysqld.pid

[root@backup 3307]# cat mysql#!/bin/shinit port=3307mysql_user="root"mysql_pwd="migongge"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startupfunction_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"exitfi}#stop functionfunction_stop_mysql() {if [ ! -e "$mysql_sock" ];thenprintf "MySQL is stopped...\n"exitelseprintf "Stoping MySQL...\n"${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdownfi}#restart functionfunction_restart_mysql() { printf "Restarting MySQL...\n" function_stop_mysql sleep 2 function_start_mysql}case $1 instart)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=mysqlInstalling 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 ...OKFilling 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 ...OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE 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_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly 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.plcd /application/mysql/mysql-test ; perl mysql-test-run.plPlease report any problems at http://bugs.mysql.com/

[root@centos6 3306]# ll /data/3306/data/total 1136drwx------. 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.indexdrwx------. 2 mysql mysql 4096 Dec 9 18:02 performance_schemadrwx------. 2 mysql root  4096 Dec 9 18:02 test

[root@centos6 3307]# ll /data/3307/data/total 1136drwx------. 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.indexdrwx------. 2 mysql mysql 4096 Dec 9 18:40 performance_schemadrwx------. 2 mysql root  4096 Dec 9 18:40 test

[root@backup 3307]# /data/3306/mysql startStarting MySQL...[root@backup 3307]# lsof -i :3306COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 19986 mysql 10u IPv4 90967 0t0 TCP *:mysql (LISTEN)[root@backup 3307]# /data/3307/mysqlstart Starting MySQL...[root@backup 3307]# lsof -i :3307COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 21648 mysql 11u IPv4 92899 0t0 TCP *:opsession-prxy (LISTEN)

[root@backup 3307]# netstat -lntup|grep mysqltcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 21648/mysqldtcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 19986/mysqld

[root@backup ~]# mysql -S /data/3306/mysql.sockWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.51-log Source distributionCopyright (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> quitBye[root@backup ~]# mysql -S /data/3307/mysql.sockWelcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.51 Source distributionCopyright (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)

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

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

1、什么是多实例

2、安装多实例环境准备

更多相关文章

随机推荐