MySQL 多实例详解

MySQL多实例配置方法

      1、单一配置文件

      2、多配置文件。

二、实战步骤：

   1、同步时间

   2、准备mysql依赖包

   3、环境准备

           3.1添加mysql用户

           3.2编译安装mysql

      4、安装MySQL多实例

   5、准备配置文件

   6、启动MySQL

   7、登录mysql与一些安全措施

   8、关闭MySQL

   9,、提供简易管理脚本

   10、为mysql的root用户创建密码

   11、附，多配置文件安装mysql多实例，这里增加mysql 的3309端口

   12、总结

1、同步时间

yum install epel*

ntp 202.120.2.101

yum install -y ntp

ntpdate 202.120.2.101

hwclock -w

2、准备mysql依赖包

yum install -y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*

yum install cmake -y

3、环境准备

3.1添加mysql用户

groupadd mysql

useradd -g mysql mysql

mkdir -pv /data/{3306,3307,3308}/data

chown -R mysql.mysql /data/

cd /usr/local/src/

3.2编译安装mysql

tar -xf mysql-5.5.45.tar.gz

cd mysql-5.5.45

chown mysql.mysql -R .

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DMYSQL_DATADIR=/data \

-DSYSCONFDIR=/etc \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_READLINE=1 \

-DWITH_SSL=system \

-DWITH_ZLIB=system \

-DWITH_LIBWRAP=0 \

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci

gmake

make install

4、安装MySQL多实例

cd /usr/local/mysql/

scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/

scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/

scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/

5、准备配置文件

mysqld_multi --example > /data/multi.cnf #提供多配置文件模板

vim /data/multi.cnf #修改模板，这里比较简易

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

user = root

#这个用户应该有关机权限，然后没有其他的权限。建议创建一个通用的,multi_admin用户控制其它的MySQL用户，这里有个例子

#GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password'

[mysqld1]

socket = /tmp/mysql.sock1

port = 3306

pid-file = /data/3306/mysql.pid

datadir = /data/3306/data

[mysqld2]

socket = /tmp/mysql.sock2

port = 3307

pid-file = /data/3307/mysql.pid

datadir = /data/3307/data

#language = /usr/local/mysql/share/mysql/english

#user = unix_user1

[mysqld3]

#mysqld = /path/to/mysqld_safe

#ledir = /path/to/mysqld-binary/

#mysqladmin = /path/to/mysqladmin

socket = /tmp/mysql.sock3

port = 3308

pid-file = /data/3308/mysql.pid

datadir = /data/3308/data

#language = /usr/local/mysql/share/mysql/swedish

#user = unix_user2

以上是简易的配置文件。这个配置文件只能让MySQL启动起来。

6、启动MySQL

mysqld_multi --defaults-file=/data/multi.cnf start 1,2,3

ss -tnl #查看监听端口

LISTEN 0 50 *:3306 *:*

LISTEN 0 50 *:3307 *:*

LISTEN 0 50 *:3308 *:*

7、登录mysql与一些安全措施

mysql -S /tmp/mysql.sock1 #指定socket登录

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.01 sec)

mysql> drop database test;

Query OK, 0 rows affected (0.04 sec)

mysql> select User,Host from mysql.user;

+------+-----------+

| User | Host |

+------+-----------+

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| root | localhost |

| | node1 |

| root | node1 |

+------+-----------+

6 rows in set (0.01 sec)

mysql> delete from mysql.user where User='';

Query OK, 2 rows affected (0.00 sec)

mysql> delete from mysql.user where Host='::1';

Query OK, 1 row affected (0.00 sec)

mysql> delete from mysql.user where Host='node1';

Query OK, 1 row affected (0.00 sec)

其余的操作类似，就不在演示

8、关闭MySQL

mysqladmin -S /tmp/mysql.sock1 shutdown 建议使用

musqld_multi --defaults-file stop [#,#]

killall mysqld 不建议使用

9,、提供简易管理脚本

#!/bin/bash

#

basedir=/usr/local/mysql

bindir=/usr/local/mysql/bin

conf=/data/multi.cnf

export PATH=$bindir:/$PATH

if test -x $bindir/mysqld_multi

then

mysqld_multi="$bindir/mysqld_multi";

else

echo "Can't execute $bindir/mysqld_multi from dir $basedir";

exit;

fi

case "$1" in

'start' )

"$mysqld_multi" --defaults-extra-file=$conf start $2

;;

'stop' )

"$mysqld_multi" --defaults-extra-file=$conf stop $2

;;

'report' )

"$mysqld_multi" --defaults-extra-file=$conf report $2

;;

'restart' )

"$mysqld_multi" --defaults-extra-file=$conf stop $2

"$mysqld_multi" --defaults-extra-file=$conf start $2

;;

*)

echo "Usage: $0 {start|stop|report|restart}" >&2

;;

esac

10、为mysql的root用户创建密码

mysqladmin -uroot -S /tmp/mysql.sock2 password "123456" #指定socket文件位置即可

其余的操作一样

11、附，多配置文件安装mysql多实例，这里增加mysql 的3309端口

mkdir -pv /data/3309/data

chown mysql.mysql -R 3309/

cd /usr/local/mysql

scripts/mysql_install_db --datadir=/data/3309/data/ --user=mysql --basedir=/usr/local/mysql/

cp support-files/my-small.cnf /data/3309/my.cnf

vim /data/3309/my.cnf

[client]

port = 3309 #这里也需要注意下吧

socket = /tmp/mysql.sock4

[mysqld]

port = 3309 #注意端口

socket = /tmp/mysql.sock4 #注意socket

skip-external-locking

key_buffer_size = 16K

max_allowed_packet = 1M

table_open_cache = 4

sort_buffer_size = 64K

read_buffer_size = 256K

read_rnd_buffer_size = 256K

net_buffer_length = 2K

thread_stack = 128K

datadir=/data/3309/data #注意datadir

server-id = 1

[mysqldump]

quick

max_allowed_packet = 16M

mysqld_safe --defaults-file=/data/3309/my.cnf 2&1> > /dev/null #启动mysqld

ss -tnl | grep 330 #查看端口

LISTEN 0 50 *:3306 *:*

LISTEN 0 50 *:3307 *:*

LISTEN 0 50 *:3308 *:*

LISTEN 0 50 *:3309 *:*