MySQL 数据库多实例安装
第 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_COKKATION=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 -h10.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.
来源: http://www.bubuko.com/infodetail-2477910.html