目录
1 什么是多实例
mysql多实例就是在一台服务器上同时开启多个不同的服务端口(如3306、3307),同时运行多个Mysql服务进程,这些服务进程通过不同的
socket
监听不同的服务端口来提供服务。
这些Mysql多实例共用一套Mysql安装程序,使用不同的my.cnf
(也可以相同)配置文件、启动程序(也可以相同)和数据文件。在提供服务时,多实例Mysql在逻辑上看来是各自独立的,他们根据配置文件的对应设定值,获得服务器相应数量的硬件资源。
其实很多网络服务都是可以配置多实例的,例如Nginx
、Apache
、Haproxy
、Redis
、memcache
等都可以配置多实例,在门户网站使用很广泛。
2 MySQL多实例的生产应用场景
1、资金紧张型公司的选择,公司业务访问量又不太大的。
2、并发访问不是特别大的业务,这样服务器资源就可以得到充分的利用
3、门户网站应用MySQL多实例,因为门户网站配置硬件好的服务器,可以节省IDC机柜空间,同时,跑多实例也会减少资源跑步满的浪费。
3 MySQL多实例常见的配置方案
1、单一配置文件、单一启动程序多实例部署方案
2、多配置文件、多启动程序部署方案,这个是主流的。
4 MySQL多实例多配置文件配置
# 安装依赖包
[root@db02 ~]# yum install ncurses-devel libaio-devel cmake -y
[root@db02 ~]# rpm -qa ncurses-devel libaio-devel cmake
ncurses-devel-5.7-4.20090207.el6.x86_64
libaio-devel-0.3.107-10.el6.x86_64
cmake-2.8.12.2-4.el6.x86_64
# 安装mysql
[root@db02 ~]# useradd mysql -s /sbin/nologin -M
[root@db02 ~]# id mysql
uid=501(mysql) gid=501(mysql) groups=501(mysql)
# 下载mysql-5.5.53.tar.gz包
[root@db02 ~]# cd /home/oldboy/tools/
[root@db02 tools]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.5.53.tar.gz
[root@db02 tools]# cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.53 \
-DMYSQL_DATADIR=/application/mysql-5.5.53/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.53/tmp/mysql.sock \
-DMYSQL_USER=mysql \
-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 \
-DWITHREADLINE=1 \
-DWITHEMBEDDED_SERVER=1 \
-DWITH_DEBUG=0
[root@db02 tools]# make && make install
# 做个软链接
[root@db02 tools]# ln -s /application/mysql-5.5.53/ /application/mysql
# 创建实例的目录
[root@db02 ~]# mkdir -p /data/{3306,3307}/data
[root@db02 ~]# tree /data/
/data/
├── 3306 #3306实例的目录
│ └── data #3306实例的数据文件目录
└── 3307 #3307实例的目录
└── data #3307实例的数据文件目录
4 directories, 0 files
# 配置文件下载地址(https://nextcloud.rensongqi.com/s/ZSpTf5S6LsdkyCi)
[root@db02 ~]# find /data -type f -name "mysql"
/data/3306/mysql
/data/3307/mysql
[root@db02 ~]# find /data -type f -name "mysql"|xargs chmod +x
[root@db02 ~]# find /data -type f -name "mysql"|xargs ls -l
-rwxr-xr-x 1 root root 1135 May 27 19:44 /data/3306/mysql
-rwxr-xr-x 1 root root 1125 May 27 19:46 /data/3307/mysql
[root@db02 ~]# chown -R mysql:mysql /data
[root@db02 ~]# ll -d /data
drwxr-xr-x 4 mysql mysql 4096 May 27 16:03 /data
[root@db02 ~]# tree /data
/data
├── 3306
│ ├── data
│ ├── my.cnf
│ └── mysql
└── 3307
├── data
├── my.cnf
└── mysql
4 directories, 4 files
# 初始化实例
[root@db02 ~]# cd /application/mysql/scripts/
[root@db02 scripts]# ./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3306/data --user=mysql
......
OK
Filling help tables...
180527 19:51:10 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
180527 19:51:10 [Note] /application/mysql//bin/mysqld (mysqld 5.5.53) starting as process 20332 ...
OK
......
[root@db02 scripts]# ./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307/data --user=mysql
[root@db02 ~]# netstat -lntup|grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 25616/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 26335/mysqld
# 设置环境变量
[root@db02 ~]# cp /application/mysql/bin/* /usr/local/sbin/
# 测试登录
[root@db02 3306]# mysql -uroot -S /data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.53-log Source distribution
Copyright (c) 2000, 2013, 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> exit
Bye
# 登录3307
[root@db02 3306]# mysql -uroot -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.53 Source distribution
Copyright (c) 2000, 2013, 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> exit
Bye
# 设置开机自启动
[root@db02 ~]# echo "#mysql multi instances" >>/etc/rc.local
[root@db02 ~]# echo "/data/3306/mysql start" >>/etc/rc.local
[root@db02 ~]# echo "/data/3307/mysql start" >>/etc/rc.local
# 多实例停止
/data/3306/mysql stop
/data/3307/mysql stop
# 再次创建3308实例
mkdir -p /data/3308/data
\cp /data/3306/{my.cnf,mysql} /data/3308/
sed -i 's/3306/3308/g' /data/3308/my.cnf /data/3308/mysql
sed -i 's/server-id = 1/server-id = 8/g' /data/3308/my.cnf
chown -R mysql:mysql /data/3308/
chmod +x /data/3308/mysql
./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308/data --user=mysql
/data/3308/mysql start
[root@db02 ~]# netstat -lntup|grep 3308
tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 27981/mysqld
# 初始化设置登陆密码
[root@db02 ~]# mysqladmin -uroot password 123456 -S /data/3306/mysql.sock
[root@db02 ~]# mysqladmin -uroot password 123456 -S /data/3307/mysql.sock
[root@db02 ~]# mysqladmin -uroot password 123456 -S /data/3308/mysql.sock
# 上边都是本地多实例登录,远程登陆多实例需要指定端口
msyql -uroot -pxxxxxx -h 10.0.0.52 -P 3307
# 数据库优化配置,删除无用的数据库test,及调整user用户
drop database test;
use mysql
mysql> select user,host from user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | db02 |
| root | db02 |
| | localhost |
| root | localhost |
+------+-----------+
mysql> drop user ""@db02;
Query OK, 0 rows affected (0.00 sec)
mysql> drop user ""@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> drop user "root"@"::1";
Query OK, 0 rows affected (0.00 sec)
mysql> drop user "root"@db02;
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)
- 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
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
5 MySQL多实例服务启动失败排查办法
1、如果没有显示Mysql对应实例的端口,请稍微等待几秒后再检查,MySQL服务的启动比Web等服务会慢一些
2、如果还不行,可以查看MySQL服务对应实例的错误字日,错误日志路径在my.cnf配置的最下面定义。
grep log-error my.cnf|tail -1
转载至https://blog.csdn.net/mr_rsq/article/details/80466345