配置mysql 多实例
MySQL多实例可以在一台服务器安装多个MySQL的独立实例，每个实例都有自己的端口,使用不同的数据库端口,指定不同的数据库文件目录, 可以进行数据库实例的隔离。
方法1：使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便。
方法2：是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理。
（1）、方法1
数据库安装这里不再详细介绍, 可以参考我之前写的博文（搜索mysql进行检索）
mysql3306实例的配置文件 /etc/my3306.cnf
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | [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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | [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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [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
