MySQL数据库多实例安装
蒙山ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：18982081108（备注：SSL证书合作）期待与您的合作！
简单的说，MySQL多实例就是在一台服务器上同时开启多个不同的服务端口（如：3306、3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。
这些MySQL多实例共用一套MySQL安装程序，使不同的my.cnf（也可以相同）配置文件、启动程序（也可以相同）和数据文件。在提供服务时，多实例MySQL在逻辑上看来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。
打个比方，MySQL多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（cpu,mem,disk）、软件资源（Centos操作系统）可以看做房子的卫生间、厨房、客厅，是房子的公用资源。
q 多实例的作用
1）有效利用服务器资源
当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务，且可以实现资源的逻辑隔离。
2）节约服务器资源
当公司资金紧张，但是数据库又需要各自尽量独立地提供服务，而且，需要主从复制等技术时，多实例就再好不过了。
q 多实例的弊端
MySQL多实例有它的好处，但也有弊端，比如，会存在资源互相抢占的问题。
当某个数据库实例并发很高或者有SQL慢查询时，整个实例会消耗大量的系统CPU、磁盘I/O等资源，导致服务器上的其他数据库实例提供服务的质量一起下降。不同实例获取的资源是相对独立的，无法像虚拟化一样完全隔离。
是MySQL官网推荐的配置方法，即在同一个配置文件里面有多个实例的配置。对于该方案，缺点是耦合度太高，一个配置文件不好管理。工作开发和运维的统一原则：降低耦合度。
不同的实例拥有不同的配置文件。
[root@mysql02 scripts]# tree /data/ /data/ ├── 3306 │ ├── data #<==3306实例的配置及数据文件 │ ├── my.cnf │ └── mysql └── 3307 ├── data #<==3307实例的数据及配置文件 ├── my.cnf └── mysql 4 directories, 4 files
[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
mysql-5.5.32.tar.gz
下载地址： https://downloads.mysql.com/archives/community/?tpl=files&os=src&version=5.5.32
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/*[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有多少个实例就执行多少次，只需要换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，需要先解决。
启动命令：直接在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
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，当然是需要提前赋予登录的权限。
修改密码时也需要指定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>
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。