MySQL编译安装多实例

MySQL数据库多实例安装

蒙山ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：18982081108（备注：SSL证书合作）期待与您的合作！

第1章 MySQL多实例介绍

简单的说，MySQL多实例就是在一台服务器上同时开启多个不同的服务端口（如：3306、3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。

这些MySQL多实例共用一套MySQL安装程序，使不同的my.cnf（也可以相同）配置文件、启动程序（也可以相同）和数据文件。在提供服务时，多实例MySQL在逻辑上看来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。

打个比方，MySQL多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（cpu,mem,disk）、软件资源（Centos操作系统）可以看做房子的卫生间、厨房、客厅，是房子的公用资源。

1.1 MySQL多实例的作用与问题

q 多实例的作用

1）有效利用服务器资源

当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务，且可以实现资源的逻辑隔离。

2）节约服务器资源

当公司资金紧张，但是数据库又需要各自尽量独立地提供服务，而且，需要主从复制等技术时，多实例就再好不过了。

q 多实例的弊端

MySQL多实例有它的好处，但也有弊端，比如，会存在资源互相抢占的问题。

当某个数据库实例并发很高或者有SQL慢查询时，整个实例会消耗大量的系统CPU、磁盘I/O等资源，导致服务器上的其他数据库实例提供服务的质量一起下降。不同实例获取的资源是相对独立的，无法像虚拟化一样完全隔离。

1.2 MySQL多实例常见的配置方案

1.2.1 单一配置文件、单一启动程序多实例部署方案（耦合度太高）

    是MySQL官网推荐的配置方法，即在同一个配置文件里面有多个实例的配置。对于该方案，缺点是耦合度太高，一个配置文件不好管理。工作开发和运维的统一原则：降低耦合度。

1.2.2 多配置文件、多启动程序部署方案

    不同的实例拥有不同的配置文件。

[root@mysql02 scripts]# tree /data/
/data/
├── 3306
│   ├── data          #<==3306实例的配置及数据文件
│   ├── my.cnf
│   └── mysql
└── 3307
    ├── data             #<==3307实例的数据及配置文件
    ├── my.cnf
    └── mysql
 
4 directories, 4 files

第2章 安装并配置多实例MySQL数据库

2.1 软件环境

2.1.1 操作系统环境

[root@server ~]# cat /etc/redhat-release
CentOS release 6.5 (Final)
[root@server ~]# uname -r
2.6.32-431.el6.x86_64
[root@server ~]# uname -m
x86_64

2.1.2 数据库版本

mysql-5.5.32.tar.gz

下载地址：
https://downloads.mysql.com/archives/community/?tpl=files&os=src&version=5.5.32

2.2 安装MySQL多实例（编译安装）

q 安装MySQL需要的依赖包

安装MySQL之前，最好先安装MySQL需要的依赖包，不然后面会出现很多报错信息，安装命令如下：

yum -y install ncurses-devel libaio-devel
rpm -qa ncurses-devel libaio-devel

提示：安装出现下面两条提示说明成功！

ncurses-devel-5.7-4.20090207.el6.x86_64
libaio-devel-0.3.107-10.el6.x86_64

q 安装编译MySQL需要的软件

yum -y install cmake

q 开始安装MySQL

以下步骤包括了创建mysql用户,下载mysql源码包，编译参数，编译安装，创建软连接。安装之前先自行下载mysql-5.5.32.tar.gz版本（5.5.55.tar.gz有BUG，不要使用）。

useradd mysql -s /sbin/nologin -M
id mysql
cd /usr/local/src/
tar xf mysql-5.5.32.tar.gz
cd mysql-5.5.32
cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.5.32 \
-DMYSQL_DATADIR=/opt/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/opt/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0
make
make install
cd ..
ln -s /opt/mysql-5.5.32 /opt/mysql
ls /opt/mysql/

q 创建多实例数据目录（以端口区分）

mkdir -p /data/{3306,3307}/data
chown -R mysql.mysql /data/*

2.3 创建多实例配置文件

[root@localhost 3306]# tree -L 1  /data/3306
/data/3306
├── data             #<==这是在初始化数据库时自动生成的，不用管
├── my.cnf           #<==这个是单实例的配置文件
└── mysql            #<==这个事自己编写的启动脚本
 
1 directory, 2 files

q 单实例配置文件my.cnf内容（3306）：

[client]
port        = 3306
socket      = /data/3306/mysql.sock
default-character-set=gbk
 
[mysql]
no-auto-rehash
 
[mysqld]
user        = mysql
port        = 3306
socket      = /data/3306/mysql.sock
basedir     = /opt/mysql
datadir     = /data/3306/data
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet = 8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192k
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
#log_long_format
#log-error = /data/3306/error.log
#log-slow-queries = /data/3306/slow.log
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
character_set_server=gbk
 
lower_case_table_name = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db = mysql
 
server-id = 1
 
 
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_write_io_threads = 4
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
 
[mysqld_safe]
log-error = /data/3306/mysql_3306.err
pid-file = /data/3306/mysqld.pid
 
 
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192

q 单实例配置文件my.cnf内容（3307）：

[client]
port        = 3307
socket      = /data/3307/mysql.sock
default-character-set=gbk
 
[mysql]
no-auto-rehash
 
[mysqld]
user        = mysql
port        = 3307
socket      = /data/3307/mysql.sock
basedir     = /opt/mysql
datadir     = /data/3307/data
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet = 8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192k
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
#long_query_time = 1
#log_long_format
#log-error = /data/3307/error.log
#log-slow-queries = /data/3307/slow.log
pid-file = /data/3307/mysql.pid
#log-bin = /data/3307/mysql-bin
relay-log = /data/3307/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
character_set_server=gbk
 
lower_case_table_name = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db = mysql
 
server-id = 3
 
 
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_write_io_threads = 4
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
 
[mysqld_safe]
log-error = /data/3307/mysql_3307.err
pid-file = /data/3307/mysqld.pid
 
 
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192

注：以上只是一个单实例的配置文件，如果实例不相同，则配置略有变化

q 启动文件mysql

#!/bin/bash
 
#init
port=3307
mysql_user="root"
mysql_pwd="123456"
CmdPath="/opt/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
 
#start mysql function
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 mysql 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 mysql function
 function restart_mysql() {
   printf "Restarting MySQL......\n"
   stop_mysql
   sleep 2
   start_mysql
  }
 
 case $1 in
 start)
   start_mysql
   ;;
 stop)
   stop_mysql
   ;;
 restart)
   restart_mysql
   ;;
 *)
   printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
 esac

2.4 初始化数据库

有多少个实例就执行多少次，只需要换3306

cd /opt/mysql/scripts
./mysql_install_db --basedir=/opt/mysql --datadir=/data/3306/data/ --user=mysql --collation-server=utf8_general_ci
./mysql_install_db --basedir=/opt/mysql --datadir=/data/3307/data/ --user=mysql --collation-server=utf8_general_ci
Installing MySQL system tables...
OK
Filling help tables...
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:
 
/opt/mysql/bin/mysqladmin -u root password 'new-password'
/opt/mysql/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'
 
Alternatively you can run:
/opt/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 /opt/mysql ; /opt/mysql/bin/mysqld_safe &
 
You can test the MySQL daemon with mysql-test-run.pl
cd /opt/mysql/mysql-test ; perl mysql-test-run.pl
 
Please report any problems with the /opt/mysql/scripts/mysqlbug script!

注：1、如果~/scripts/目录下没有mysql_install_db则说明编译没有成功。

2、有两个OK出现表示初始化成功，如果有WARINING或者ERROR，需要先解决。

第3章 MySQL多实例启动

启动命令：直接在mysql 后跟start参数

[root@localhost 3306]# /data/3306/mysql start
Starting MySQL......
[root@localhost 3306]# /data/3307/mysql start
Starting MySQL......
[root@localhost 3306]# netstat -anp |grep 330
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      17531/mysqld       
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      16784/mysqld       
unix  2      [ ACC ]     STREAM     LISTENING     52308  16784/mysqld        /data/3306/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     52809  17531/mysqld        /data/3307/mysql.sock

可能出现错误1：

[root@mysql02 3306]# 180124 01:24:15 mysqld_safe error: log-error set to '/data/3306/mysql_3306.err', however file don't exists. Create writable for user 'mysql'.

解决方式：放弃吧少年，5.5.55这个版本可能不适合你编译呀。

可能出现错误2：

180126 22:27:40 [ERROR] COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'
180126 22:27:40 [ERROR] Aborting

这个在配置文件里面加两行就搞定了：

[client]
 default-character-set=gbk
 
[mysqld]
 character_set_server=gbk

3.1 登录多实例数据库

q 多实例本地登录

多实例本地登录一般是通过socket文件来指定具体登录到哪个实例的，此文件的具体位置是在mysql编译过程中或者my.cnf指定的。在本地登录数据库时，登录程序会通过socket文件来判断登录的是哪个数据库实例。

cp /opt/mysql/bin/* /usr/local/sbin/   #<==首先把命令拷贝到PATH下
[root@localhost 3307]# mysql -uroot -p -S /data/3306/mysql.sock 
Enter password:         #<==这里默认没有密码，直接回车进入数据库。
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32-log Source distribution
 
Copyright (c) 2000, 2013, 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>

q 远程登录mysql实例

远程登录MySQL多实例中的一个实例时，通过TCP端口（port）来指定所要登录的MySQL实例，此端口的配置是在mysql配置文件my.cnf中指定的。

例如：mysql -uroot -p123456 -h20.0.0.15 -P 3307，当然是需要提前赋予登录的权限。

3.2 修改初始密码

修改密码时也需要指定sock文件，命令如下：

mysqladmin password 123456 -S /data/3306/mysql.sock
mysqladmin password 123456 -S /data/3307/mysql.sock
[root@localhost 3306]# mysql -uroot -p123456 -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.32-log Source distribution
 
Copyright (c) 2000, 2013, 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>

3.3 数据库安全

q 删除多余的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.02 sec)
 
mysql> drop database test;
Query OK, 0 rows affected (0.03 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

q 删除多余的用户

mysql> select user,host from mysql.user;
+------+-----------------------+
| user | host                  |
+------+-----------------------+
| root | 127.0.0.1             |
| root | ::1                   |
|      | localhost             |
| root | localhost             |
|      | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
6 rows in set (0.00 sec)
mysql> drop user ""@localhost;
Query OK, 0 rows affected (0.02 sec)
 
mysql> drop user "root"@"::1";
Query OK, 0 rows affected (0.00 sec)
 
mysql> drop user "root"@"localhost.localdomain";
Query OK, 0 rows affected (0.02 sec)
 
mysql> drop user ""@"localhost.localdomain";
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)

注：这里只留余root.127.0.0.1和root.localhost一共2个就好了。

q 安全要领

1）启动程序设置700，属主和用户组为mysql；

2）为MySQL超级用户root设置密码；

3）可以删除root用户，创建其他管理用户，例如：admin；

4）登录时尽量不要在命令行暴露密码，脚本备份中如果有密码，给设置700属主和用户组为mysql或root；

5）删除默认存在的test库；

6）删除无用的用户，只保留：root.127.0.0.1和root.localhost；

7）授权用户对应的主机尽量不要用%，权限不要给all，最小化授权，从库只给select；

8）清理mysql操作日志文件~/.mysql_history；

9）服务器禁止设置外网IP。