MySQL5.6多实例安装
Linux：centos 6.8mysql：5.6.23
一、安装多实例前准备
因为我的是新机器所以有好多软件没有，可能会导致之后编译出错
1.安装依赖包
[root@mysql5.6 ~]# yum install -y git gcc gcc-c++ ncurses-devel bison
2.mysql安装包
这里下载安装包时要注意了，不要选跟你的Linux系统不同的位数（64 and 32）。
二、安装mysql
1.解压安装包
[root@mysql5.6 ~]# tar xf mysql-5.6.23.tar.gz[root@mysql5.6 ~]# mv mysql-5.6.23 mysql ##改名[root@mysql5.6 ~]# mv mysql /usr/local/ ##移动到指定位置
2.创建用户
[root@mysql5.6 ~]# useradd user ##创建用户[root@mysql5.6 ~]# chown -R mysql.mysql /data ##更改属主属组
3.进行编译安装
[root@mysql5.6 ~]# cd /usr/local/mysql/
[root@mysql5.6 mysql]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.6 \ ## MySQL安装路径
-DMYSQL_DATADIR=/usr/local/mysql-5.6/data \ ## MySQL数据存放路径-DMYSQL_UNIX_ADDR=/usr/local/mysql-5.6/tmp/mysql.sock \ ## MySQL socke路径-DDEFAULT_CHARSET=utf8 \ ## MySQL 字符集-DDEFAULT_COLLATION=utf8_general_ci \-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_ENGING=1 \-DWITH_FAST_MUTEXES=1 \-DWITH_ZLIB=bundled \-DENABLED_LOCAL_INFIL=1 \-DWITH_READLINE=1 \-DWITH_EMBEDDED_SERVER=1 \[root@mysql5.6 mysql]# echo $? ##查看命令是否执行正确
0 ##回复‘0’表示成功，回复非‘0’表示执行错误
[root@mysql5.6 mysql]# make && make install
[root@mysql5.6 mysql]# echo $? ##在不确定是要使用这个检查
0
4.创建MySQL多实例的数据文件目录（3306,3307）
[root@mysql5.6 mysql]# mkdir -p /data/{3306,3307}/data ##因为是双实例[root@mysql5.6 mysql]# tree /data/data├── 3306 <- 3306实例的目录
│ └── data <- 3306实例的数据文件目录├── 3307 <- 3307实例的目录
│ └── data <- 3307实例的数据文件目录
5.创建数据库（3306,3307）配置文件
3306 配置文件
prot = 3306socket= /data/3306/mysql.sock
no-auto-rehash
## 服务端配置文件user = mysql ## mysql启动用户port = 3306 ## 监听端口号socket= /data/3306/mysql.sock ## 指定sock路径basedir = /usr/local/mysql-5.6 ## 数据库安装路径datadir = /data/3306/data ## 数据存放目录open_files_limit = 1024back_log = 600max_connections = 800max_connect_errors= 3000table_cache = 614external-locking = FALSE
max_allowed_packet= 8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100thread_concurrency= 2query_cache_size = 2M
query_cache_limit = 2M
query_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation= READ-COMMITTEDtmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1log-error = /data/3306/error.logslow-query-log-file = /data/3306/slow.logpid-file = /data/3306/mysql.pidlog-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M#myisam_sort_buffer_size = 1M#myisam_max_sort_file_size = 10G#myisam_max_extra_sort_file_size = 10G#myisam_repair_threads= 1#myisam_recover
lower_case_table_names= 1skip-name-resolve
slave-skip-errors = 1032,1062replicate-ignore-db = mysql
server-id = 1
#innodb_additional_mem_pool_size = 4M#innodb_buffer_pool_size = 23M#innodb_data_file_path = ibdata1:128M:autoextend#innodb_file_io_threads = 4#innodb_thread_concurrency = 8#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
quick
max_allowed_packet = 2M
log-error = /data/3306/mysql_90root3306.err
pid-file = /data/3306/mysql.pid
3307 配置文件
[root@mysql5.6 ~]# vim /data/3307/my.cnf
prot = 3307socket= /data/3307/mysql.sock
no-auto-rehash
user = mysql
port = 3307socket= /data/3307/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3307/data
open_files_limit = 1024back_log = 600max_connections = 800max_connect_errors= 3000#table_cache = 614external-locking = FALSE
max_allowed_packet= 8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100thread_concurrency= 2query_cache_size = 2M
query_cache_limit = 2M
query_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation= READ-COMMITTEDtmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1log-error = /data/3307/error.logslow-query-log-file = /data/3307/slow.logpid-file = /data/3307/mysql.pidlog-bin = /data/3307/mysql-bin
relay-log = /data/3307/relay-bin
relay-log-info-file = /data/3307/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M#myisam_sort_buffer_size = 1M#myisam_max_sort_file_size = 10G#myisam_max_extra_sort_file_size = 10G#myisam_repair_threads= 1#myisam_recover
lower_case_table_names= 1skip-name-resolve
slave-skip-errors = 1032,1062replicate-ignore-db = mysql
server-id = 2
#innodb_additional_mem_pool_size = 4M#innodb_buffer_pool_size = 23M#innodb_data_file_path = ibdata1:1200M:autoextend#innodb_file_io_threads = 4#innodb_thread_concurrency = 8#innodb_flush_log_at_trx_commit = 2#innodb_log_buffer_size = 2M#innodb_log_file_size = 200M#innodb_log_files_in_group = 3#innodb_max_dirty_pages_pct = 90#innodb_lock_wait_timeout = 120#innodb_file_per_table = 0
quick
max_allowed_packet = 2M
log-error = /data/3307/mysql_90root3307.err
pid-file = /data/3307/mysql.pid
6.配置MySQL命令 全局路径
[root@mysql5.6 ~]# echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile[root@mysql5.6 ~]# source /etc/profile
7.初始化数据库
[root@mysql5.6 ~]# cd /usr/local/mysql/scripts/[root@mysql5.6 scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data/ --user=mysql[root@mysql5.6 scripts]# echo $?0[root@mysql5.6 scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data/ --user=mysql[root@mysql5.6 scripts]# echo $?0
8.多实例服务 启动和停止命令
启动命令
[root@mysql5.6 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 [root@mysql5.6 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 >
停止命令
[root@mysql5.6 ~]# /usr/local/mysql/bin/mysqladmin -uroot -p -S /data/3306/mysql.sock shutdown
[root@mysql5.6 ~]# /usr/local/mysql/bin/mysqladmin -uroot -p -S /data/3307/mysql.sock shutdown
查看服务启动状态
[root@mysql5.6 ~]# netstat -anpt|grep mysqltcp 0 0 :::3306 :::* LISTEN 20886/mysqld
tcp 0 0 :::3307 :::* LISTEN 20045/mysqld
三、进入MySQL
1.进入MySQL(3306,3307)数据库
[root@mysql5.6 ~]# mysql -uroot -p -S /data/3306/mysql.sockmysql> show variables like 'port';
+---------------+-------+| Variable_name | Value |
+---------------+-------+| port | 3306|
+---------------+-------+[root@mysql5.6 ~]# mysql -uroot -p -S /data/3307/mysql.sockmysql> show variables like 'port';
+---------------+-------+| Variable_name | Value |
+---------------+-------+| port | 3307|
+---------------+-------+
2.更改root密码
mysql> update mysql.user set password=passwort('newpass') where user='root';
mysql> flush privileges
四、编写启动脚本
3306启动脚本
[root@mysql5.6 ~]# vim /data/3306/mysql#!/bin/bash# initport=3306mysql_user="root"mysql_pwd=""CmdPath="/usr/local/mysql/bin"mysql_sock="/data/${port}/mysql.sock"
# Startup functionfunction_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 functionfunction_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 functionfunction_restart_mysql(){ printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 instart) function_start_mysql
;;
restart) function_restart_mysql
;;
stop) function_stop_mysql
;;
*) echo "/data/${port}/mysql {start|restart|stop}"
;;esac
3307启动脚本
#!/bin/bash# initport=3307mysql_user="root"mysql_pwd=""CmdPath="/usr/local/mysql/bin"mysql_sock="/data/${port}/mysql.sock"
# Startup functionfunction_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 functionfunction_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 functionfunction_restart_mysql(){ printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 instart) function_start_mysql
;;
restart) function_restart_mysql
;;
stop) function_stop_mysql
;;
*) echo "/data/${port}/mysql {start|restart|stop}"
;;esac
页:
[1]