一台服务器搭建多个数据库，基于centos7，MySQL5.6版本

搭建这种服务器之前，必须要有丰富的Linux功底和对MySQL配置文件有深刻的了解哦。

一台服务器搭建多台数据库，首先就要解决以下几个问题：

1.my.cnf配置文件路径，默认指向/etc/my.cnf；

2.端口号，默认3306端口。

3.数据库目录，主程序目录；

4.启动脚本，进入数据库方法等；

 

测试环境准备：

关闭selinux、防火墙、配置好主机名，安装好MySQL需要的依赖软件；

[root@DB3 ~]# yum -y install ncurses-devel cmake bison gcc gcc-c++ libaio ncurses autoconf

[root@DB3 ~]# getenforce && systemctl status firewall ; cat /etc/hostname
Disabled
Unit firewall.service could not be found.
DB3

创建数据库目录，MySQL用户和组。

[root@DB3 tools]# groupadd mysql
[root@DB3 tools]# useradd mysql -g mysql -M -s /sbin/nologin

[root@DB3 tools]# mkdir -p /data/{mysql3306,mysql4306}/{data,binlog,info}

[root@DB3 tools]# chown mysql:mysql -R /data/mysql*

搭建第一个数据库

[root@DB3 mysql-5.6.41]# cmake -DMYSQL_USER=mysql  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql3306 -DMYSQL_DATADIR=/data/mysql3306/data -DSYSCONFDIR=/usr/local/mysql3306/ -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/data/mysql3306/mysqld.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

[root@DB3 mysql-5.6.41]# make && make install

编译选项就不一一说明了，在我以前的博客里有说明：https://blog.csdn.net/weixin_44316575/article/details/87184137

进入到第一个数据库程序文件拷贝主配置文件

[root@DB3 tools]# cd /usr/local/mysql3306/

[root@DB3 mysql3306]# cp support-files/my-default.cnf my.cnf

编辑数据库配置文件，注意一些重要文件的区分。

[root@DB3 mysql3306]# vim my.cnf

[client]
port = 3306
default-character-set = utf8
socket = /data/mysql3306/info/mysql.sock
[mysqld]
basedir = /usr/local/mysql3306
datadir = /data/mysql3306/data
port = 3306
socket = /data/mysql3306/info/mysql.sock
pid-file = /data/mysql3306/info/mysqld.pid
default-storage-engine = INNODB
character-set-server = utf8
collation-server = utf8_general_ci
# 开启不区分大小
lower_case_table_names = 1
# 这个设置可以接受多种多样的值来改变服务器行为。
sql-mode  =  STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTI
ON
log-error = /data/mysql3306/info/mysql-error.log
slow_query_log_file = /data/mysql3306/info/mysql-slow.log
server_id = 1001
sync_binlog = 1
log-bin = /data/mysql3306/binlog/mysql-bin
log-bin-index = /data/mysql3306/binlog/mysql-bin.index

生成数据库，先移走自带的配置文件避免冲突，改名数据库进入命令避免冲突。

[root@DB3 mysql3306]# mv /etc/my.cnf /opt/
[root@DB3 mysql3306]# mv bin/mysql bin/mysql3306
[root@DB3 mysql3306]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql3306/data

拷贝系统服务文件。

[root@DB3 mysql3306]# cp support-files/mysql.server /etc/init.d/mysql3306

编辑系统服务文件，指定主配置文件。

[root@DB3 mysql3306]# vim /etc/init.d/mysql3306

  conf=/usr/local/mysql3306/my.cnf

[root@DB3 mysql3306]# /etc/init.d/mysql3306 start
Starting MySQL. SUCCESS!

[root@DB3 mysql3306]# ./bin/mysql3306 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41-log Source distribution

Copyright (c) 2000, 2018, 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.00 sec)

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> create database user;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| user               |
+--------------------+
4 rows in set (0.00 sec)

搭建第二个数据库

[root@DB3 mysql-5.6.41]# rm -f CMakeCache.txt Makefile
[root@DB3 mysql-5.6.41]# cmake -DMYSQL_USER=mysql  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql4306 -DMYSQL_DATADIR=/data/mysql4306/data -DSYSCONFDIR=/usr/local/mysql4306/ -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/data/mysql4306/mysql4306.sock -DMYSQL_TCP_PORT=4306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

[root@DB3 mysql-5.6.41]# make && make install

[root@DB3 mysql-5.6.41]# cd /usr/local/mysql4306/
[root@DB3 mysql4306]# mv bin/mysql bin/mysql4306
 

[root@DB3 mysql4306]# cp ../mysql3306/my.cnf .
[root@DB3 mysql4306]# vim my.cnf

利用正则表达式快速的将3306更改为4306，将mysql.sock更改为mysql4306.sock，将mysqld.pid更改为mysqld4306.pid。

:% s/3306/4306/g
:% s/mysql.sock/mysql4306.sock/g
:% s/mysqld.pid/mysqld4306.pid/g

[root@DB3 mysql4306]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql4306/data

[root@DB3 mysql4306]# cp support-files/mysql.server /etc/init.d/mysql4306
[root@DB3 mysql4306]# vim /etc/init.d/mysql4306

  conf=/usr/local/mysql4306/my.cnf

[root@DB3 mysql4306]# /etc/init.d/mysql4306 start
Starting MySQL.. SUCCESS!

[root@DB3 mysql4306]# netstat -nutpl|grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      33551/mysqld        
tcp6       0      0 :::4306                 :::*                    LISTEN      49333/mysqld 

[root@DB3 mysql4306]# ./bin/mysql4306 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41-log Source distribution

Copyright (c) 2000, 2018, 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.02 sec)

mysql> create database passwd;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| passwd             |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> \q
Bye

[root@DB3 mysql4306]# ls /data/mysql4306/data/
auto.cnf  ibdata1  ib_logfile0  ib_logfile1  mysql  passwd  performance_schema  test
[root@DB3 mysql4306]# ls /data/mysql3306/data/
auto.cnf  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  user