配置mysql 多实例

MySQL多实例可以在一台服务器安装多个MySQL的独立实例,每个实例都有自己的端口,使用不同的数据库端口,指定不同的数据库文件目录, 可以进行数据库实例的隔离。
方法1:使用多个配置文件启动不同的进程来实现多实例,这种方式的优势逻辑简单,配置简单,缺点是管理起来不太方便。
方法2:是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例,这种方式定制每个实例的配置不太方面,优点是管理起来很方便,集中管理。
(1)、方法1
数据库安装这里不再详细介绍, 可以参考我之前写的博文(搜索mysql进行检索)

mysql3306实例的配置文件 /etc/my3306.cnf

[mysqld]
server-id=1
bind-address=0.0.0.0
datadir=/usr/local/mysql/data3306
basedir=/usr/local/mysql
socket=/tmp/mysql3306.sock
user=mysql
port=3306
log_output=file
slow_query_log=on
slow_query_log_file=/usr/local/mysql/mysql3306-slow.log
log_queries_not_using_indexes=on
long_query_time=10
binlog_format=MIXED
log-bin=/usr/local/mysql/mysql3306-bin
expire_logs_days=15
max_binlog_size=500M
character-set-server=utf8
lower_case_table_names=1
# general_log=1
# # general_log_file=/usr/local/mysql/mysql3306-query.log
pid-file=/usr/local/mysql/data3306/mysqld3306-pid
federated
max_connections=2000
character_set_server=utf8
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#
# # Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# # Settings user and group are ignored when systemd is used.
# # If you need to run mysqld under a different user or group,
# # customize your systemd unit file for mariadb according to the
# # instructions in http://fedoraproject.org/wiki/Systemd
# show_compatibility_56=on
#
# #
[client]
port=3306
socket=/tmp/mysql3306.sock
default-character-set=utf8
#
[mysqld_safe]
log-error=/var/log/mysql/mysql3306-error.log
#
# # #
# # # include all files from the config directory
# # #
# # !includedir /etc/my.cnf.d

初始化mysql3306
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data3306 --initialize

mysql3307实例的配置文件 /etc/my3307.cnf

[mysqld]
server-id=1
bind-address=0.0.0.0
datadir=/usr/local/mysql/data3307
basedir=/usr/local/mysql
socket=/tmp/mysql3307.sock
user=mysql
port=3307
log_output=file
slow_query_log=on
slow_query_log_file=/usr/local/mysql/mysql3307-slow.log
log_queries_not_using_indexes=on
long_query_time=10
binlog_format=MIXED
log-bin=/usr/local/mysql/mysql3307-bin
expire_logs_days=15
max_binlog_size=500M
character-set-server=utf8
lower_case_table_names=1
# general_log=1
# # general_log_file=/usr/local/mysql/mysql-query3307.log
pid-file=/usr/local/mysql/data3307/mysqld3307-pid
federated
max_connections=2000
character_set_server=utf8
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#
# # Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# # Settings user and group are ignored when systemd is used.
# # If you need to run mysqld under a different user or group,
# # customize your systemd unit file for mariadb according to the
# # instructions in http://fedoraproject.org/wiki/Systemd
# show_compatibility_56=on
#
# #
[client]
port=3307
socket=/tmp/mysql3307.sock
default-character-set=utf8
#
[mysqld_safe]
log-error=/var/log/mysql/mysql3307-error.log
#
# # #
# # # include all files from the config directory
# # #
# # !includedir /etc/my.cnf.d

初始化mysql3307
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data3307 --initialize

配置数据库服务
### 以mysql3306为例
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld3306

修改 /etc/init.d/mysqld3306 配置文件中的设置参数
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data3306
$bindir/mysqld_safe --defaults-file=/etc/my3306.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &

# chkconfig --add mysqld3306
# chkconfig mysqld3306 on
# service mysqld3306 start

登入数据库
# mysql -uroot -p -S /tmp/mysql3306.sock // 登录mysql3306实例库

# mysql -uroot -p -S /tmp/mysql3307.sock // 登录mysql3307实例库

(2)、方法2

# mysql多实例数据库配置文件/etc/my.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /data/mysql/mysql_multi.log

[mysqld3306]
basedir = /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
socket = /data/mysql/mysql_3306/data/mysql.sock
port = 3306
pid-file = /data/mysql/mysql_3306/data/mysql.pid
user = mysql
server_id = 3306
log_bin = /data/mysql/mysql_3306/data/mysql-bin

[mysqld3307]
basedir = /usr/local/mysql
datadir = /data/mysql/mysql_3307/data
socket = /data/mysql/mysql_3307/data/mysql.sock
port = 3307
pid-file = /data/mysql/mysql_3307/data/mysql.pid
user = mysql
server_id = 3307
log_bin = /data/mysql/mysql_3307/data/mysql-bin

多实例初始化
# /usr/local/mysql/bin/mysqld --initialize --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data
# /usr/local/mysql/bin/mysqld --initialize --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3307/data

mysqld_multi 服务配置
# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
# chmod +x /etc/init.d/mysqld_multi
通过mysqld_multi启动mysql多实例
# /etc/init.d/mysqld_multi start
查看数据库运行状态
#/etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

# killall -u mysql
# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running

可以单独启动3306数据库
# /etc/init.d/mysqld_multi start 3306
查看多实例数据库状态,可以看到3307没有运行
# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is not running

登入mysql多实例数据库
登入mysql3306
# /usr/local/mysql/bin/mysql -u root -p -S /data/mysql/mysql_3306/data/mysql.sock
登入mysql3307
# /usr/local/mysql/bin/mysql -u root -p -S /data/mysql/mysql_3307/data/mysql.sock
登录会提示要求重置密码

添加mysql环境变量
# echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
# source /etc/profile
可以使用mysqladmin来重置root密码
# mysqladmin -uroot password '新密码' -S /data/mysql/mysql_3306/data/mysql.sock

相关新闻

联系我们

全国服务热线

400-033-9553

电子邮件:admin@example.com
工作时间:09:00-17:00 周一至周五

在线客服
关注微信
关注微信
分享本页
返回顶部