安装并配置多实例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            = 3306
socket          = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user    = mysql
port    = 3306
socket  = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_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.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 6
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
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
[mysqldump]
quick
max_allowed_packet = 2M

[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid

vim /data/3306/mysql

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

start(){
    if [ ! -e "$mysql_sock" ];then
         printf "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
    else
        printf "MySQL is running...\n"
        exit 1
    fi
}

stop(){
    if [ ! -e "$mysql_sock" ];then
        printf "MySQL is stopped...\n"
        exit 1
    else
        printf "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"
    stop
    sleep 2
    start
}

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


case "$1" in

start)
    start
    ;;
stop)
    stop
    ;;
restart)
    restart
    ;;
*)
    Usage
    ;;
esac
    

同样，需要在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