mysql多实例安装配置_安装并配置多实例Mysql数据库

1、安装Mysql需要的依赖包

yum -y install ncurses-devel libaio-devel cmake

2、创建Mysql用户账号

useradd -s /sbin/nologin -M mysql

3、上传mysql源码包或直接使用wget下载(下载地址：http://ftp.jaist.ac.jp/pub/mysql/Downloads/)

wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/mysql-5.6.39.tar.gz

tar xf mysql-5.6.39.tar.gz

cd mysql-5.6.39

4、编译安装Mysql

cmake \-DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.39\-DMYSQL_DATADIR=/application/mysql-5.6.39/data \-DMYSQL_UNIX_ADDR=/application/mysql-5.6.39/tmp/mysql.sock \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=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

make &&  make install

5、创建软连接

ln -s /application/mysql-5.6.39/ /application/mysql

6、创建Mysql多实例的数据文件目录

mkdir -p /data/{3306,3307}/data

7、为了让Mysql多实例之间比此独立，要为每一个实例建立一个my.cnf配置文件和一个启动文件mysql,让他们分别对应自己的数据文件目录data。

vim /data/3306/my.cnf

[client]

port= 3306socket= /data/3306/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user=mysql

port= 3306socket= /data/3306/mysql.sock

basedir= /application/mysql

datadir= /data/3306/data

open_files_limit= 1024back_log= 600max_connections= 800max_connect_errors= 3000table_open_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=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-bin

relay-log-info-file = /data/3306/relay-log.infobinlog_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

lower_case_table_names= 1skip-name-resolve

slave-skip-errors = 1032,1062replicate-ignore-db=mysql

server-id = 6innodb_additional_mem_pool_size=4M

innodb_buffer_pool_size=32M

innodb_data_file_path=ibdata1:128M:autoextend

innodb_file_io_threads= 4innodb_thread_concurrency= 8innodb_flush_log_at_trx_commit= 2innodb_log_buffer_size=2M

innodb_log_file_size=4M

innodb_log_files_in_group= 3innodb_max_dirty_pages_pct= 90innodb_lock_wait_timeout= 120innodb_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

View Code

vim /data/3306/mysql

#!/bin/bash

port=3306mysql_user="root"mysql_pwd="123456"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"./etc/init.d/functions

start(){if [ ! -e "$mysql_sock" ];thenprintf"Starting MySQL...\n"

/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &[ $? -eq 0 ] && action "Mysql start" /bin/true || action "Mysql start" /bin/false

elseprintf"MySQL is running...\n"exit1

fi}

stop(){if [ ! -e "$mysql_sock" ];thenprintf"MySQL is stopped...\n"exit1

elseprintf"Stoping MySQL...\n"${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

[ $? -eq 0 ] && action "Stop mysql" /bin/true || action "Stop mysql" /bin/false

fi}

restart(){

printf"Restarting MySQL...\n"stopsleep 2start

}

Usage(){echo "Usage: /data/${port}/mysql (start|stop|restart)"exit1}case "$1" instart)

start

;;

stop)

stop

;;

restart)

restart

;;*)

Usage

;;esac

View Code

同样，需要在3307下也放入上面两个文件，只需要更改3306位3307即可。

8、配置Mysql多实例的文件权限，生产环境需要将Mysql脚本的权限设置为700，因为脚本里面有Mysql的root登录密码。

chown -R mysql.mysql /data

find /data -name mysql | xargs chmod 700

9、将Mysql命令加入环境变量(务必把Mysql命令放在PATH路径中其他路径的前面，防止使用的mysql命令和编译安装的命令不是同一个，进而产生错误)

echo "export PATH=/application/mysql-5.6.39/bin:$PATH" >> /etc/profile

. /etc/profile

#ln -s /application/mysql/bin/* /usr/local/sbin/

10、初始化Mysql多实例的数据库文件

cd /application/mysql/scripts/./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql

./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql

#有两个OK，表示初始化成功

11、启动Mysql多实例数据库

/data/3306/mysql start/data/3307/mysql start

[root@localhost data]# netstat -tlunp | grep 330

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 16502/mysqld

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 17940/mysqld

如果Mysql多实例服务没有启动，请查看Mysql服务对应实例的错误日志，错误日志路径在my.cnf配置的最下面定义

12、配置开机启动，确保mysql脚本有执行权限

echo -e "/data/3306/msyql start\n/data/3307/mysql start" >> /etc/rc.local

13、登录Mysql测试

Mysql -uroot -S /data/3306/mysql.sock

Mysql -uroot -S /data/3307/mysql.sock

14、 默认情况下，Mysql管理员的root账号是无密码的，登录不同的实例需要指定不同的实例的mysql.sock文件路径，这个mysql.sock是在my.cnf配置文件里指定的。我们需要修改Mysql管理员账号的密码，可以通过mysqladmin命令为不同的数据库设置独立的密码，命令如下：

mysqladmin -u root -S /data/3306/mysql.sock password '123456'

[root@localhost data]# mysql -uroot -p -S /data/3306/mysql.sock

Enter password:123456

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.16 Source distribution

Copyright (c) 2000, 2011, 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>

3307实例的设置方法和3306实例的相同，只是连接时的mysql.sock路径不同而已。

15、配置mysql脚本的权限

find /data/ -type f -name "mysql" -exec chmod 700{} \;find /data -type f -name "mysql" -exec chown root.root {} \;

16、停止Mysql的方式(温馨提示，生产环境，禁止使用kill -9 、pkill、killall -9等命令强制杀死数据库，这会引起数据库无法启动故障的发生)

/data/3306/mysql stop

17、如何在3306和3307实例的基础上，再增加一个Mysql实例

mkdir /data/3308/data -p

\cp /data/3306/my.cnf /data/3308/\cp /data/3306/mysql /data/3308/

sed -i 's/3306/3308/g' /data/3308/my.cnf

sed -i 's/server-id = 6/server-id = 8/g' /data/3308/my.cnf

sed -i 's/3306/3308/g' /data/3308/mysql

chown -R mysql.mysql /data/3308

chmod 700 /data/3308/mysql

chown root.root /data/3308/mysql

cd /application/mysql/scripts

./mysql_install_db --defaults-file=/data/3308/my.cnf --datadir=/data/3308/data --basedir=/application/mysql --user=mysql

chown -R mysql.mysql /data/3308

/data/3308/mysql start

mysqladmin -uroot -S /data/3308/mysql.sock password "`sed -n "4p" /data/3308/mysql | awk -F "[=\"]" '{print $3}'`" ##修改密码

netstat -tlunp | grep 3308