一分钟部署 MySql 多实例

2017-09-20 mysql多实例

0x01 到底什么是MySql多实例

1
2
3
说的通俗些,就是通过读取不同的my.cnf配置,同时启动多个mysqld进程,共用系统资源
这也就意味着,当其中一个实例并发较高或者慢查询较多的情况下,其它实例的性能也会因此受到很大的影响
这种情况通常用于机器性能较好且单实例mysql无法充分利用系统资源的情况下

0x02 此处,我们选择自己手工编译安装mysql-5.5.55,根据大家自己实际的机器性能,速度可能会稍慢些,请耐心等待

演示环境:

1
2
3
4
CentOS 6.8 x86_64     最小化,带基础环境库安装
eth0 ip: 192.168.3.51
eth1 ip: 192.168.4.19
eth2 ip: 192.168.5.19

安装mysql所需的各种依赖库并创建mysql的服务用户

1
2
# yum install ncurses-devel libaio-devel cmake bison -y
# useradd mysql -s /sbin/nologin -M

下载并编译 mysql-5.5.55

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.55.tar.gz
# tar xf mysql-5.5.55.tar.gz
# cd mysql-5.5.55
# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.55 \
-DMYSQL_DATADIR=/usr/local/mysql-5.5.55/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql-5.5.55/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-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 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0
# make && make install
# ln -s /usr/local/mysql-5.5.55/ /usr/local/mysql
# ll /usr/local/mysql/

0x03 创建好用于存放各个实例数据和配置文件的存放目录,此处会同时模拟创建三个mysql实例,目录就直接以端口号来命名,如下

1
# mkdir -p /data/{3306,3307,3308}/data

0x04 分别在上面三个不同的目录下编辑my.cnf配置,注意,这里只是3306的配置,关于另外两个实例,3307和3308,配置参数基本都是一模样的,只需要改下路径和实例id即可,具体如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
# vi /data/3306/my.cnf
[client] # 客户端配置,不同的实例,要注意修改下面的路径
port            = 3306
socket          = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld] # mysql 服务配置,不同的实例,要注意修改下面的路径
user    = mysql
port    = 3306
socket  = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_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
#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 = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 1	# 这个就是标示不同实例的id,不同的实例配置中务必要用不同的id
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

确认下每个实例id,切记这里不同的实例不能使用同一个id,另外,我们要对/data目录进行授权,让mysql能写,因为等会儿要初始化,它会往这个目录生成系统库

1
2
3
4
5
6
7
# grep server-id 330{6..8}/my.cnf
# chown -R mysql.mysql /data/
# find /data/ -type f -name "my.cnf" | xargs ls -l
# find /data/ -type f -name "mysql" | xargs chmod 700	脚本里有root密码,为了防止其它人看见,要把权限重设下
# find /data/ -type f -name "mysql" | xargs ls -l
# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bash_profile
# source ~/.bash_profile

0x04 多实例数据库的初始化方式,注意,这里数据的存放目录务必要指向到我们事先创建的目录下,如,/data/port/data

1
2
3
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3306/data --user=mysql
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3307/data --user=mysql
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3308/data --user=mysql

这里data下的mysql,是我们自己写的mysql启动关闭脚本,具体脚本内容还在下面

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# tree -L 3 /data/
├── 3306
│   ├── data
│   │   ├── mysql
│   │   ├── performance_schema
│   │   └── test
│   ├── my.cnf
│   └── mysql
├── 3307
│   ├── data
│   │   ├── mysql
│   │   ├── performance_schema
│   │   └── test
│   ├── my.cnf
│   └── mysql
└── 3308
    ├── data
    │   ├── mysql
    │   ├── performance_schema
    │   └── test
    ├── my.cnf
    └── mysql

0x05 此时,我们就可以通过自己的mysql小脚本来同时启动多个实例,另外,不知道是什么原因脚本在启动时错误日志写不进去,不得不在这里先手动做了下授权

1
2
3
4
5
6
# echo "" > /data/3306/mysql_3306.err
# echo "" > /data/3307/mysql_3307.err
# echo "" > /data/3308/mysql_3308.err
# chown -R mysql.mysql /data/3306/mysql_3306.err
# chown -R mysql.mysql /data/3307/mysql_3307.err
# chown -R mysql.mysql /data/3308/mysql_3308.err

脚本自身非常简单,其实真正有用的就两句,完全是为了后续操作方便,在每个实例目录下都要有这么一个脚本,不同的脚本里仅仅也只是端口和密码不一样而已,具体内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#!/bin/sh
port=3306
mysql_user="root"
mysql_pwd="admin"
CmdPath="/usr/local/mysql/bin/"
mysql_sock="/data/${port}/mysql.sock"
#start Mysql Services
function_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 Mysql Services
function_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 Mysql Services
function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}
case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

尝试同时启动三个实例

1
2
# /data/3306/mysql start && /data/3307/mysql start && /data/3308/mysql start
# netstat -tulnp

0x06 修改不同实例的root密码,想要进到不同的实例中就要指定对应的socket才行

1
2
3
4
# mysqladmin -u root password "admin123" -S /data/3306/mysql.sock
# mysqladmin -u root password "admin456" -S /data/3307/mysql.sock
# mysqladmin -u root password "admin456" -S /data/3308/mysql.sock
# /data/3306/mysql stop && /data/3307/mysql stop && /data/3308/mysql stop

0x07 多实例登陆mysql,顺手清除一些没用的库和匿名用户:

1
2
3
# mysql -u root -p -S /data/3306/mysql.sock
# mysql -u root -p -S /data/3307/mysql.sock
# mysql -u root -p -S /data/3308/mysql.sock

0x08 设置允许root远程连接,这里是为了学习,实际生产环境中,严禁直接这么干

1
2
mysql> GRANT SHUTDOWN ON *.* TO 'root'@'%' IDENTIFIED BY 'admin';
mysql> flush privileges;



小结:
    这里仅仅只是为了自己平时学习之用,如果是用于实际生产环境还需要自行仔细优化加固 ^_^

#mysql多实例