MySQL单机多实例

发表于 | 更新于 | 分类于 | 评论数： | 阅读次数：
本文字数： 4.8k | 阅读时长 ≈ 4 分钟

这里演示在单台机器上利用mysqld_multi运行两个MySQL实例,MySQL版本5.1.73

配置文件

创建mysql_multi所需的配置文件

touch /etc/mysql_multi.cnf

编辑配置文件如下

vim /etc/mysql_multi.cnf

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
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /data/mysql/log/mysqld_multi.log

[mysqld3306]
user = mysql
port = 3306
socket = /data/mysql/3306/mysql.sock
pid-file = /data/mysql/3306/mysqld.pid
log-error = /data/mysql/3306/mysqld.log
datadir = /data/mysql/3306/data
max_connections = 200
default-storage-engine = innodb
character_set_server = utf8

[mysqld3307]
user = mysql
port = 3307
socket = /data/mysql/3307/mysql.sock
pid-file = /data/mysql/3307/mysqld.pid
log-error = /data/mysql/3307/mysqld.log
datadir = /data/mysql/3307/data
max_connections = 200
default-storage-engine = innodb
character_set_server = utf8

初始化DB

mysql_install_db —datadir=/data/mysql/3306/data —user=mysql
mysql_install_db —datadir=/data/mysql/3307/data —user=mysql

查看mysql用户组 不存在则创建一个

查看

grep mysql /etc/group

创建

groupadd mysql

查看mysql用户 不存在则创建一个

查看

grep mysql /etc/passwd

创建

useradd mysql -g mysql -M -s /sbin/nologin

1
2
3
4
增加一个名为CentOS Mysql的用户。
-g：指定新用户所属的用户组(group)
-M：不建立根目录
-s：定义其使用的shell，/sbin/nologin代表用户不能登录系统。

给mysql用户添加权限

chown -R mysql.mysql /data/mysql

启用MySQL实例

mysqld_multi --defaults-extra-file=/etc/mysql_multi.cnf start

查看启用的MySQL实例状态

1
2
3
4
5
6
7
8
9
10
[root@HSB-DEV-001 log]mysqld_multi --defaults-extra-file=/etc/mysql_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

[root@HSB-DEV-001 mysql]ps -aef | grep mysql
root     28064     1  0 19:08 pts/7    00:00:00 /bin/sh /usr/bin/mysqld_safe --user=mysql --port=3306 --socket=/data/mysql/3306/mysql.sock --pid-file=/data/mysql/3306/mysqld.pid --log-error=/data/mysql/3306/mysqld.log --datadir=/data/mysql/3306/data --max_connections=200 --default-storage-engine=innodb --character_set_server=utf8
root     28072     1  0 19:08 pts/7    00:00:00 /bin/sh /usr/bin/mysqld_safe --user=mysql --port=3307 --socket=/data/mysql/3307/mysql.sock --pid-file=/data/mysql/3307/mysqld.pid --log-error=/data/mysql/3307/mysqld.log --datadir=/data/mysql/3307/data --max_connections=200 --default-storage-engine=innodb --character_set_server=utf8
mysql    28304 28064  0 19:08 pts/7    00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/data/mysql/3306/data --user=mysql --max_connections=200 --default-storage-engine=innodb --character_set_server=utf8 --log-error=/data/mysql/3306/mysqld.log --pid-file=/data/mysql/3306/mysqld.pid --socket=/data/mysql/3306/mysql.sock --port=3306
mysql    28308 28072  0 19:08 pts/7    00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/data/mysql/3307/data --user=mysql --max_connections=200 --default-storage-engine=innodb --character_set_server=utf8 --log-error=/data/mysql/3307/mysqld.log --pid-file=/data/mysql/3307/mysqld.pid --socket=/data/mysql/3307/mysql.sock --port=3307

设置密码和权限

登录MySQL

mysql -h 127.0.0.1 -P3306 -uroot -p

修改密码

SET PASSWORD=password('123456');

设置权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

查看权限

SHOW GRANTS FOR 'root'@'%';

设置mysqld_multi为开机自启动

  1. sudo vim /etc/rc.d/rc.local

    1
    2
    #!/bin/bash
    mysqld_multi --defaults-extra-file=/etc/mysql_multi.cnf start

  2. 赋予rc.local可执行权限chmod +x /etc/rc.d/rc.local

  3. sudo vim /usr/lib/systemd/system/rc-local.service

    1
    2
    [Install]
    WantedBy=multi-user.target

  4. 设置rc-local.service服务开机运行systemctl enable rc-local.service

问题解决

Warning: World-writable config file ‘/etc/mysql_multi.cnf’ is ignored

1
2
3
4
5
6
[root@HSB-DEV-001 ~]# /usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/mysql_multi.cnf report
Warning: World-writable config file '/etc/mysql_multi.cnf' is ignored
Warning: World-writable config file '/etc/mysql_multi.cnf' is ignored
Reporting MySQL servers
Warning: World-writable config file '/etc/mysql_multi.cnf' is ignored
MySQL server from group: mysqld3307 is running

当配置文件的权限为任何人都可访问时,MySQL任务不安全会忽略配置文件,改为644即可

1
[root@HSB-DEV-001 ~]# chmod 644 /etc/mysql_multi.cnf

doesn’t have a default value

重启MySQL实例后执行SQL命令报如下错误,经查是开启了MySQL严格模式导致的,使用select @@sql_mode;语句查看MySQL实例的模式,发现确实是开启了严格模式,查看MySQL配置配置,发现已经设置不使用严格模式,最后发现是MySQL读取了多个配置文件导致的,MySQL在读取了首选配置文件后又读取了别的配置文件导致设置的默认被覆盖.

查看MySQL模式

1
2
3
4
5
6
7
MariaDB [(none)]> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

在sql_mode中有STRICT_TRANS_TABLES则表示开启严格模式, 使用mysql严格模式可以使数据更加安全严格，缺点是减少了对空数据入库的兼容性, 严格模式功能说明:

  1. 不支持对not null字段插入null值
  2. 不支持对自增长字段插入值
  3. 不支持text字段有默认值

查看MySQL默认配置文件和优先顺序

1
2
[CaseZheng@VM_187_252_centos ~]$ mysql --help|grep 'my.cnf'
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf