7.4.4. MySQL多实例安装

Date:

2018-11

7.4.4.1. MySQL多实例介绍

MySQL多实例特点：

  • 安装一套MySQL程序

  • 启用多个服务，每个实例一个服务

  • 每个服务一个端口。例如双实例可以监听到3306、3307端口

  • 每个实例使用指定的配置文件

  • 每个实例指定MySQL数据根目录

MySQL多实例作用：

  • 充分利用MySQL服务器的资源。

  • 可以节约服务器资源。

MySQL多实例问题：

  • 某一实例资源占用过多时，其他实例会服务质量下降

多实例安装方式：

  • 编译安装多实例

  • rpm安装，然后修改成多实例

7.4.4.2. MySQL双实例安装(编译安装)

7.4.4.2.1. MySQL下载

1[root@mysql_001 tools]# pwd
2/data/tools
3[root@mysql_001 tools]# wget http://ftp.iij.ad.jp/pub/db/mysql/Downloads/MySQL-5.5/mysql-5.5.60.tar.gz

7.4.4.2.2. MySQL多实例编译安装

  1. 防火墙、selinux、时间同步配置系统准备命令集合

1ntpdate pool.ntp.org
2sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
3setenforce 0
4/etc/init.d/iptables stop
5chkconfig iptables off

注意

时间同步最好加入到定时任务。这样保证以后时间如果有错误的时候会自动更正。

  • echo "#time sysc by myhome at 2018-03-30" >>/var/spool/cron/root

  • echo "*/5 * * * * /usr/sbin/ntpdate pool.ntp.org >/dev/null 2&1" >>/var/spool/cron/root

  1. 安装MySQL需要预安装的依赖包

1[root@mysql_001 ~]# yum install ncurses-devel libaio-devel cmake -y

 1[root@mysql_001 tools]# cd mysql-5.5.60
 2[root@mysql_001 mysql-5.5.60]# cmake . -DCMAKE_INSTALL_PREFIX=/app/mysql-5.5.60 \
 3> -DMYSQL_DATADIR=/app/mysql-5.5.60/data \
 4> -DMYSQL_UNIX_ADDR=/app/mysql-5.5.60/tmp/mysql.sock \
 5> -DDEFAULT_CHARSET=utf8 \
 6> -DDEFAULT_COLLATION=utf8_general_ci \
 7> -DWITH_EXTRA_CHARSETS=all \
 8> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
 9> -DWITH_FEDERATED_STORAGE_ENGINE=1 \
10> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
11> -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
12> -DWITH_ZLIB=bundled \
13> -DWITH_SSL=bundled \
14> -DENABLED_LOCAL_INFILE=ON \
15> -DWITH_EMBEDDED_SERVER=1 \
16> -DENABLE_DOWNLOADS=1 \
17> -DWITH_DEBUG=0

 1cmake . -DCMAKE_INSTALL_PREFIX=/app/mysql-5.5.60 \
 2-DMYSQL_DATADIR=/app/mysql-5.5.60/data \
 3-DMYSQL_UNIX_ADDR=/app/mysql-5.5.60/tmp/mysql.sock \
 4-DDEFAULT_CHARSET=utf8 \
 5-DDEFAULT_COLLATION=utf8_general_ci \
 6-DWITH_EXTRA_CHARSETS=all \
 7-DWITH_INNOBASE_STORAGE_ENGINE=1 \
 8-DWITH_FEDERATED_STORAGE_ENGINE=1 \
 9-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
10-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
11-DWITH_ZLIB=bundled \
12-DWITH_SSL=bundled \
13-DENABLED_LOCAL_INFILE=ON \
14-DWITH_EMBEDDED_SERVER=1 \
15-DENABLE_DOWNLOADS=1 \
16-DWITH_DEBUG=0

1[root@mysql_001 mysql-5.5.60]# make && make install

创建软连接

1[root@mysql_001 mysql-5.5.60]# ln -s /app/mysql-5.5.60 /app/mysql
2
3[root@mysql_001 mysql-5.5.60]# ll /app/mysql
4lrwxrwxrwx 1 root root 17 Nov 27 00:30 /app/mysql -> /app/mysql-5.5.60

配置系统环境变量，使mysql命令可以直接使用不用输入全路径

1echo "export PATH=/app/mysql/bin:$PATH" >>/etc/profile
2source /etc/profile
3echo $PATH

数据目录创建：

 1[root@mysql_001 ~]# mkdir /data/{3306,3307}/data -p
 2
 3[root@mysql_001 ~]# tree -L 2 /data/
 4/data/
 5├── 3306
 6│   └── data
 7├── 3307
 8│   └── data
 9├── lost+found
10└── tools
11    ├── mysql-5.5.60
12    └── mysql-5.5.60.tar.gz
13
147 directories, 1 file

更改数据目录所属用户和组：

1id mysql
2useradd -s /sbin/nologin -M mysql
3id mysql
4
5ll /data/
6chown -R mysql.mysql /data/{3306,3307}
7ll /data/

7.4.4.2.3. 多实例配置文件配置

3306配置清单

1vi /data/3306/my.cnf

具体配置内容：

 1[client]
 2port      = 3306
 3socket    =/data/3306/mysql.sock
 4[mysql]
 5no-auto-rehash
 6[mysqld]
 7user    = mysql
 8port    = 3306
 9socket  =/data/3306/mysql.sock
10basedir = /app/mysql
11datadir = /data/3306/data
12open_files_limit    = 1024
13back_log = 600
14max_connections = 800
15max_connect_errors = 3000
16table_open_cache = 614
17external-locking = FALSE
18max_allowed_packet =8M
19sort_buffer_size = 1M
20join_buffer_size = 1M
21thread_cache_size = 100
22thread_concurrency = 2
23query_cache_size = 2M
24query_cache_limit = 1M
25query_cache_min_res_unit = 2k
26#default_table_type = InnoDB
27thread_stack = 192K
28#transaction_isolation = READ-COMMITTED
29tmp_table_size = 2M
30max_heap_table_size = 2M
31#long_query_time = 1
32#log_long_format
33#log-error = /data/3306/error.log
34#log-slow-queries = /data/3306/slow.log
35pid-file = /data/3306/mysql.pid
36#log-bin = /data/3306/mysql-bin
37relay-log = /data/3306/relay-bin
38relay-log-info-file = /data/3306/relay-log.info
39binlog_cache_size = 1M
40max_binlog_cache_size = 1M
41max_binlog_size = 2M
42expire_logs_days = 7
43key_buffer_size = 16M
44read_buffer_size = 1M
45read_rnd_buffer_size = 1M
46bulk_insert_buffer_size = 1M
47lower_case_table_names = 1
48skip-name-resolve
49slave-skip-errors = 1032,1062
50replicate-ignore-db=mysql
51server-id = 6
52innodb_additional_mem_pool_size = 4M
53innodb_buffer_pool_size = 32M
54innodb_data_file_path = ibdata1:128M:autoextend
55innodb_file_io_threads = 4
56innodb_thread_concurrency = 8
57innodb_flush_log_at_trx_commit = 2
58innodb_log_buffer_size = 2M
59innodb_log_file_size = 4M
60innodb_log_files_in_group = 3
61innodb_max_dirty_pages_pct = 90
62innodb_lock_wait_timeout = 120
63innodb_file_per_table = 0
64[mysqldump]
65quick
66max_allowed_packet = 2M
67[mysqld_safe]
68log-error=/data/3306/mysql_3306.err
69pid-file=/data/3306/mysql.pid

3307配置清单

1vi /data/3307/my.cnf

具体配置内容：

 1[client]
 2port      = 3307
 3socket    =/data/3307/mysql.sock
 4[mysql]
 5no-auto-rehash
 6[mysqld]
 7user    = mysql
 8port    = 3307
 9socket  =/data/3307/mysql.sock
10basedir = /app/mysql
11datadir = /data/3307/data
12open_files_limit    = 1024
13back_log = 600
14max_connections = 800
15max_connect_errors = 3000
16table_open_cache = 614
17external-locking = FALSE
18max_allowed_packet =8M
19sort_buffer_size = 1M
20join_buffer_size = 1M
21thread_cache_size = 100
22thread_concurrency = 2
23query_cache_size = 2M
24query_cache_limit = 1M
25query_cache_min_res_unit = 2k
26#default_table_type = InnoDB
27thread_stack = 192K
28#transaction_isolation = READ-COMMITTED
29tmp_table_size = 2M
30max_heap_table_size = 2M
31#long_query_time = 1
32#log_long_format
33#log-error = /data/3307/error.log
34#log-slow-queries = /data/3307/slow.log
35pid-file = /data/3307/mysql.pid
36#log-bin = /data/3307/mysql-bin
37relay-log = /data/3307/relay-bin
38relay-log-info-file = /data/3307/relay-log.info
39binlog_cache_size = 1M
40max_binlog_cache_size = 1M
41max_binlog_size = 2M
42expire_logs_days = 7
43key_buffer_size = 16M
44read_buffer_size = 1M
45read_rnd_buffer_size = 1M
46bulk_insert_buffer_size = 1M
47lower_case_table_names = 1
48skip-name-resolve
49slave-skip-errors = 1032,1062
50replicate-ignore-db=mysql
51server-id = 7
52innodb_additional_mem_pool_size = 4M
53innodb_buffer_pool_size = 32M
54innodb_data_file_path = ibdata1:128M:autoextend
55innodb_file_io_threads = 4
56innodb_thread_concurrency = 8
57innodb_flush_log_at_trx_commit = 2
58innodb_log_buffer_size = 2M
59innodb_log_file_size = 4M
60innodb_log_files_in_group = 3
61innodb_max_dirty_pages_pct = 90
62innodb_lock_wait_timeout = 120
63innodb_file_per_table = 0
64[mysqldump]
65quick
66max_allowed_packet = 2M
67[mysqld_safe]
68log-error=/data/3307/mysql_3307.err
69pid-file=/data/3307/mysql.pid

7.4.4.2.4. MySQL多实例数据库初始化

1/app/mysql/scripts/mysql_install_db  --defaults-file=/data/3306/my.cnf --basedir=/app/mysql/ --datadir=/data/3306/data --user=mysql
2
3/app/mysql/scripts/mysql_install_db  --defaults-file=/data/3307/my.cnf --basedir=/app/mysql/ --datadir=/data/3307/data --user=mysql

7.4.4.2.5. MySQL多实例启动

经测试用下面命令启动正常：

1mysqld --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
2mysqld --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &

查看服务端口：

1ss -lntup|grep 330|column -t
2netstat -lntp|grep 330

7.4.4.2.6. 多实例数据库配置账号密码

1mysqladmin -u root -S /data/3306/mysql.sock password '3306'
2mysqladmin -u root -S /data/3307/mysql.sock password '3307'

7.4.4.2.7. 多实例数据库登录

没有配置用户密码：

1mysql -S /data/3307/mysql.sock
2mysql -S /data/3306/mysql.sock

配置了账号密码：

1mysql -uroot -p3306 -S /data/3306/mysql.sock

或者：mysql -uroot -p’3306’ -S /data/3306/mysql.sock 或者：mysql -uroot -p -S /data/3306/mysql.sock

7.4.4.2.8. 清除不中的账号

1select user,host from mysql.user;
2drop user "root"@"::1";
3drop user ""@"localhost";
4drop user ""@"demo";
5drop user "root"@"demo";
6flush privileges;
7drop database test;
8select user,host from mysql.user;

7.4.4.2.9. MySQL多实例关闭

有账号密码：

1mysqladmin -u root -p3306 -S /data/3306/mysql.sock shutdown

没有账号密码：

1mysqladmin -S /data/3306/mysql.sock shutdown

7.4.4.3. MySQL双实例添加一个实例

7.4.4.4. MySQL使用rpm安装配置多实例