安装并配置多实例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..tar.gz

cd  mysql-5.6.

4、编译安装Mysql

cmake \

-DCMAKE_INSTALL_PREFIX=/application/mysql-5.6. \

-DMYSQL_DATADIR=/application/mysql-5.6./data \

-DMYSQL_UNIX_ADDR=/application/mysql-5.6./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= \

-DWITH_FEDERATED_STORAGE_ENGINE= \

-DWITH_BLACKHOLE_STORAGE_ENGINE= \

-DWITHOUT_EXAMPLE_STORAGE_ENGINE= \

-DWITHOUT_PARTITION_STORAGE_ENGINE= \

-DWITH_FAST_MUTEXES= \

-DWITH_ZLIB=bundled \

-DENABLED_LOCAL_INFILE=1

make &&  make install

5、创建软连接

ln -s /application/mysql-5.6./ /application/mysql

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

mkdir -p /data/{,}/data

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

vim /data//my.cnf

[client]

port            =

socket          = /data//mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user    = mysql

port    =

socket  = /data//mysql.sock

basedir = /application/mysql

datadir = /data//data

open_files_limit =

back_log =

max_connections =

max_connect_errors =

table_open_cache =

external-locking = FALSE

max_allowed_packet =8M

sort_buffer_size = 1M

join_buffer_size = 1M

thread_cache_size =

thread_concurrency =

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 =

#log_long_format

#log-error = /data//error.log

#log-slow-queries = /data//slow.log

pid-file = /data//mysql.pid

#log-bin = /data//mysql-bin

relay-log = /data//relay-bin

relay-log-info-file = /data//relay-log.info

binlog_cache_size = 1M

max_binlog_cache_size = 1M

max_binlog_size = 2M

expire_logs_days =

key_buffer_size = 16M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 1M

lower_case_table_names =

skip-name-resolve

slave-skip-errors = ,

replicate-ignore-db=mysql

server-id =

innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size = 32M

innodb_data_file_path = ibdata1:128M:autoextend

innodb_file_io_threads =

innodb_thread_concurrency =

innodb_flush_log_at_trx_commit =

innodb_log_buffer_size = 2M

innodb_log_file_size = 4M

innodb_log_files_in_group =

innodb_max_dirty_pages_pct =

innodb_lock_wait_timeout =

innodb_file_per_table =

[mysqldump]

quick

max_allowed_packet = 2M

[mysqld_safe]

log-error=/data//mysql_3306.err

pid-file=/data//mysqld.pid

vim /data//mysql

#!/bin/bash

port=

mysql_user="root"

mysql_pwd=""

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 >& > /dev/null &

    [ $? -eq  ] && action "Mysql start" /bin/true || action "Mysql start" /bin/false

    else

        printf "MySQL is running...\n"

        exit

    fi

}

stop(){

    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

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

    fi

}

restart(){

    printf "Restarting MySQL...\n"

    stop

    sleep

    start

}

Usage(){

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

    exit

}

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//data --user=mysql

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

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

11、启动Mysql多实例数据库

/data//mysql start

/data//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//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//mysql.sock password ''

[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

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  {} \;

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

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

/data//mysql stop

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

mkdir  /data//data -p

\cp /data//my.cnf /data//

\cp /data//mysql /data//
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

巴特西

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

最新文章

热门文章