使用mysqld_multi 实现Mysql 5.6.36 + 5.7.18 单机多实例多版本安装
Mysql 5.6.36 + 5.7.18 单机多实例多版本安装
随着硬件层面的发展,各种高性能服务器如雨后春笋般出现,但高性能服务器不免造成浪费, MySQL单机多实例,是指在一台物理服务器上运行多个MySQL数据库实例,版本亦可相同版本也可不同。
MySQL单机多实例的优点：
1、节省成本,减少服务器、IP、机柜的数量,总体运营成本低于单机单实例方案。
2、提升利用率,利用NUMA特性,对MySQL实例进行CPU绑定。
3、提升用户体验,大幅度提高IO响应时间,有助于提升用户的体验。
mysql-5.6.36下载地址:
https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
mysql-5.7.18下载地址：
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
环境说明
mysqld – MySQL 5.6.36 #正常安装
mysqld1 – MySQL 5.6.36 1
mysqld2 – MySQL 5.6.36 2
mysqld3 – MySQL 5.7.18 3
mysqld4 – MySQL 5.7.18 4
第一步安装mysql5.6.36 ：
详细安装步骤这里就不重复介绍：http://www.cnblogs.com/imweihao/p/7156754.html
1.关闭数据库
[root@001 ~]# service mysqld stop
2.创建新实例的数据目录
[root@001 ~]# mkdir -p /data/mysqldata1 #5.6
[root@001 ~]# mkdir -p /data/mysqldata2 #5.6
[root@001 ~]# mkdir -p /data/mysqldata3 #5.7
[root@001 ~]# mkdir -p /data/mysqldata4 #5.7
4.设置对应的mysql权限
[root@001 ~]# chown -R mysql:mysql /data/
[root@001 ~]# ll /data/
5.将如下黄色部分配置内容添加到原有配置文件中
[client]
user=mysql #根据实际情况设置用户名
password=123456
[mysqld]
[mysqld_multi] # 这里使用了client标签中的user，故这里不再定义user
mysqld = /usr/local/mysql57/bin/mysqld_safe
log = /var/log/mysqld_multi.log
[mysqld1]
server-id = 21
datadir = /data/mysqldata1
basedir = /usr/local/mysql # basedir定义使用了5.6的mysql版本
port = 3307
socket = /tmp/mysql.sock1
plugin_dir=/data/mysqldata1 ## plugin 目录也变了
[mysqld2]
server-id = 22
datadir = /data/mysqldata2
basedir = /usr/local/mysql # basedir定义使用了5.6的mysql版本
port = 3308
socket = /tmp/mysql.sock2
plugin_dir=/data/mysqldata2 # plugin 目录也变了
[mysqld3]
server-id = 33
datadir = /data/mysqldata3
basedir = /usr/local/mysql57 # basedir定义了使用5.7的mysql版本
port = 3309
socket = /tmp/mysql.sock3
#这里无需特别配置mysqld, 可以继承使用[mysqld_multi]中的配置，然后根据basedir找到对应的mysqld
[mysqld4]
server-id = 44
datadir = /data/mysqldata4 # basedir定义使用了5.6的mysql版本
basedir = /usr/local/mysql57
port = 3310
socket = /tmp/mysql.sock4
#--------------以下参数是网上的模板，只是将个别size调小-----------
[mysqld]
########basic settings########
server-id = 100
port = 3306
user = mysql
bind_address = 0.0.0.0
#autocommit = 0
character_set_server=utf8
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/mysqldata
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2
min_examined_row_limit = 100
########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
########innodb settings########
innodb_page_size = 8192
innodb_buffer_pool_size = 1G # 该参数减小到1G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
#innodb_log_group_home_dir = /redolog/
#innodb_undo_directory = /undolog/
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 128M # 该参数减小到 128M
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 1G # 该参数减小到1G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on
#配置文件来自网络
==================================================
6.上传mysql5.7
[root@001 ~]# rz
[root@001 ~]# tar xf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@001 ~]# cd /usr/local
[root@001 ~]# mv mysql-5.7.18-linux-glibc2.5-x86_64/ mysql57
[root@001 local]# chown -R mysql:mysql mysql57
7.初始化mysql 5.6.36 （1，2）#mysql 5.6和5.7 初始化有所不同
[root@001 ~]# cd /usr/local/mysql
[root@001 mysql]# scripts/mysql_install_db --user=mysql --datadir=/data/mysqldata1/
[root@001 mysql]# scripts/mysql_install_db --user=mysql --datadir=/data/mysqldata2/
8.初始化mysql 5.7.18 （3，4）#mysql 5.6和5.7 初始化有所不同
[root@001 mysql57]# cd /usr/local/mysql57
[root@001 mysql57]# bin/mysqld --initialize --user=mysql --datadir=/data/mysqldata3
[root@001 mysql57]# bin/mysqld --initialize --user=mysql --datadir=/data/mysqldata4
9.查看是否认到标签
[root@001 mysql57]# mysqld_multi report
10.启动多实例
[root@001 mysql57]# mysqld_multi start 1
[root@001 mysql57]# mysqld_multi start 2
[root@001 mysql57]# mysqld_multi start 3
[root@001 mysql57]# mysqld_multi start 4
[root@001 mysql57]# service mysqld start #原先的mysql 也可以启动起来
[root@001 mysql57]# mysqld_multi report
[root@001 mysql57]# netstat -anptu |grep mysql
11.后续配置
拷贝启动脚本，方便自启
[root@001 mysql57]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multid
[root@001 mysql57]# chkconfig mysqld_multid on
#查看发现几个实例uuid一样删除即可
[root@001 mysql57]# rm -rf /data/mysqldata1/auto.cnf
[root@001 mysql57]# rm -rf /data/mysqldata2/auto.cnf
[root@001 mysql57]# rm -rf /data/mysqldata3/auto.cnf
[root@001 mysql57]# rm -rf /data/mysqldata4/auto.cnf
12.登陆：mysql
12.1 登陆正常安装的mysql-5.6.36 端口：3306
[root@001 mysql57]# mysql -uroot -p123456
mysql> select version();
mysql> show variables like 'port';
12.2 登陆mysql 1，2，并修改密码
mysql1 和 mysql2初始状态没有密码，以前可以直接使用mysql -S mysql.sock登录，而现在登录的时候特别注意，因为我们使用了[client]标签,登录的时候如果不加-p参数会默认使用标签下的user和password, 然后导致登录不进去，所以需要使用如下登录方式：
mysql 1 端口：3307 版本5.6.32
[root@001 mysql57]# mysql -u root -P3307 -S /tmp/mysql.sock1 -p
Enter password: #密码为空
mysql> select version();
mysql> show variables like 'port';
mysql> set password = password("123"); #修改密码
mysql> quit
mysql 2 端口：3308 版本5.6.32
[root@001 mysql57]# mysql -u root -P3308 -S /tmp/mysql.sock2 -p
Enter password: #密码为空
mysql> select version();
mysql> show variables like 'port';
mysql> set password = password("123");#修改密码
mysql> quit
12.3 登陆mysql 3，4，并修改密码
mysql 3 端口：3309 版本5.7.18
mysql3 和 mysql4有初始状态密码,因为配置文件中指定了日志文件所以需要到数据目录
查看error.log 中的初始化密码
[root@001 mysql57]# grep 'password' /data/mysqldata3/error.log
[root@001 mysql57]# grep 'password' /data/mysqldata4/error.log
[root@001 mysql57]# mysql -u root -P3309 -S /tmp/mysql.sock3 -p
Enter password: #填入初始密码
mysql> set password = '123'; #5.7需先修改密码，并退出重新登陆
mysql> quit
[root@001 mysql57]# mysql -u root -P3309 -S /tmp/mysql.sock3 -p123 #输入刚设置好的密码
mysql> select version();
mysql> show variables like 'port';
mysql> quit
mysql 4 端口：3310 版本5.7.18
[root@001 mysql57]# mysql -u root -P3310 -S /tmp/mysql.sock4 -p
Enter password: #填入初始密码
mysql> set password = '123'; #5.7需先修改密码，并退出重新登陆
mysql> quit
[root@001 mysql57]# mysql -u root -P3310 -S /tmp/mysql.sock4 -p123 #输入刚设置好的密码
mysql> select version();
mysql> show variables like 'port';
mysql> quit
到此安装成功
--设置login-path
设置login-path主要为了能够简化登录，同时还可以让每个数据库的密码都不同，避免使用[client]下的统一用户名密码
1-设置
[root@001 mysql57]# mysql_config_editor set -G mysql1 -u root -p -S /tmp/mysql.sock1
Enter password: #输入密码
[root@001 mysql57]# mysql_config_editor set -G mysql2 -u root -p -S /tmp/mysql.sock2
Enter password: #输入密码
[root@001 mysql57]# mysql_config_editor set -G mysql3 -u root -p -S /tmp/mysql.sock3
Enter password: #输入密码
[root@001 mysql57]# mysql_config_editor set -G mysql4 -u root -p -S /tmp/mysql.sock4
Enter password: #输入密码
2-登陆方式
[root@001 mysql57]# mysql --login-path=mysql1
[root@001 mysql57]# mysql --login-path=mysql2
[root@001 mysql57]# mysql --login-path=mysql3
[root@001 mysql57]# mysql --login-path=mysql4
#以后可以使用mysql --login-path=mysql1 这种方式登录
附：
安装mysql-5.6.36
具体步骤详见：http://www.cnblogs.com/imweihao/p/7156754.html
安装mysql-5.7.18
具体步骤详见：http://www.cnblogs.com/imweihao/p/7196516.html
