MySQL

MySQL-mulit-instance

阅读预计 5 分钟   |  约 984 字  
本机MySQL多实例(不同端口号)安装
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
### 本地环境-ubuntu-14.04-LTS
#####MySQL-info-summary
bin-path:      /usr/bin/mysql
conf-path:     
    - /etc/mysql/3306_my.cnf
    - /etc/mysql/3316_my.cnf
    - /etc/mysql/3326_my.cnf
resource-path:
    - /var/lib/mysql_3306/data
    - /var/lib/mysql_3316/data  # 必须是 mysql:mysql 用户组
    - /var/lib/mysql_3326/data  # 必须是 mysql:mysql 用户组
sudo -i
# 初始化3316实例:
/usr/bin/mysql_install_db --defaults-file=/etc/mysql/3316_my.cnf --basedir=/usr --datadir=/var/lib/mysql_3316/data --user=mysql --log-error=/var/log/mysql/3316_error.log --pid-file=/var/run/mysqld/3316_mysqld.pid --socket=/var/run/mysqld/3316_mysqld.sock --port=3316
# 启动3316实例, 忽略密码验证
# 以安全模式启动mysqld(安全模式下, mysqld_safe 像一个守护进程, 当mysqld异常终止时(如 kill -9 xxx杀死mysqld), mysqld_safe会尝试新启动一个mysqld进程), 并且跳过权限验证
mysqld_safe --defaults-file=/etc/mysql/3316_my.cnf --user=mysql --skip-grant-tables &
# 无密码登录root用户
mysql -uroot -h127.0.0.1 -P3316
# (建议立即)修改root密码
use mysql;
update user set Password = PASSWORD("Monty") where User='root';
flush privileges;
quit;
# 关闭3316实例
mysqladmin -uroot -pMonty -S /var/run/mysqld/3316_mysqld.sock -vvv shutdown
# 重启mysql server(不再以`skip-grant-tables`参数启动, 需要root权限), root用户新密码登录
mysqld --defaults-file=/etc/mysql/3316_my.cnf --user=mysql &
mysql -uroot -pMonty -h127.0.0.1 -P3316

安装过程中遇到的几个问题

问题0: key_buffer_size is deprecate
1
2
3
root@XT044500:~# mysql_install_db  --defaults-file=/etc/mysql/3316_my.cnf  --basedir=/usr  --datadir=/var/lib/mysql_3316  --user=mysql
Installing MySQL system tables...
180704 19:19:59 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.

解决: MySQL 配置项, key_buffer 替换为 key_buffer_size

问题1: mysqld_safe Can’t log to error log and syslog at the same time
1
2
3
4
5
root@XT044500:~# mysqld_safe --defaults-file=/etc/mysql/3316_my.cnf --user=mysql
180705 11:03:22 mysqld_safe Can't log to error log and syslog at the same time.  Remove all --log-error configuration options for --syslog to take effect.
180705 11:03:22 mysqld_safe Logging to '/var/log/mysql/3316_error.log'.
180705 11:03:22 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql_3316
180705 11:03:22 mysqld_safe mysqld from pid file /var/run/mysqld/3316_mysqld.pid ended

解决: 注释掉 log_error = /var/log/mysql/3316_error.log 配置项
原因: (linux下)mysqld_safe 会默认向syslog里写如错误日志, 无需再向配置文件中的log_error项写入

问题2: mysqld.pid ended, 详细的问题描述在系统日志里
1
2
3
4
root@XT044500:~# mysqld_safe --defaults-file=/etc/mysql/3316_my.cnf --user=mysql
180705 11:14:00 mysqld_safe Logging to syslog.
180705 11:14:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql_3316/data
180705 11:14:00 mysqld_safe mysqld from pid file /var/run/mysqld/3316_mysqld.pid ended

syslog: Can’t find messagefile errmsg.sys

1
2
Jul  5 11:37:12 XT044500 mysqld: 180705 11:37:12 [Note] /usr/sbin/mysqld (mysqld 5.5.60-0ubuntu0.14.04.1) starting as process 28281 ...
Jul  5 11:37:12 XT044500 mysqld: 180705 11:37:12 [ERROR] Can't find messagefile '/usr/share/mysql_3316/errmsg.sys'

解决: 简单粗暴的处理, 注释掉 lc-messages-dir = /usr/share/mysql_3316 配置项

问题3: mysqld.pid ended, 详细的问题描述在系统日志里

syslog: Bind on unix socket: Permission denied

1
2
3
Jul  5 11:44:57 XT044500 mysqld: 180705 11:44:57 [ERROR] Can't start server : Bind on unix socket: Permission denied
Jul  5 11:44:57 XT044500 mysqld: 180705 11:44:57 [ERROR] Do you already have another mysqld server running on socket: /var/run/mysqld/3316_mysqld.sock ?
Jul  5 11:44:57 XT044500 mysqld: 180705 11:44:57 [ERROR] Aborting

解决: 编辑 apparmor, 然后重启

  1. vim /etc/apparmor.d/usr.sbin.mysqld
  2. /etc/init.d/apparmor restart
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
# `/etc/apparmor.d/usr.sbin.mysqld`
/usr/sbin/mysqld {
...
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /usr/share/mysql_3316/** r, # new add
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/lib/mysql-files/ r,
  /var/lib/mysql-files/** rwk,
  /var/lib/mysql_3316/ r, # new add
  /var/lib/mysql_3316/** rwk, # new add
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /var/run/mysqld/mysqld.pid rw,
  /var/run/mysqld/mysqld.sock w,
  /var/run/mysqld/3316_mysqld.pid rw, # new add
  /var/run/mysqld/3316_mysqld.sock w, # new add
  /run/mysqld/mysqld.pid rw,
  /run/mysqld/mysqld.sock w,
  /run/mysqld/3316_mysqld.pid rw, # new add
  /run/mysqld/3316_mysqld.sock w, # new add
...
}

原因: ubuntu 下, apparmor 限制

问题4: MySQL ERROR 1290 (HY000)
1
2
grant replication slave, replication client on *.* to 'robot'@'127.0.0.1' identified by 'Monty';
-- ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

解决: 先执行一遍 flush privileges; 就好

1
2
3
4
5
flush privileges;
-- Query OK, 0 rows affected, 3 warnings (0.02 sec)
grant replication slave, replication client on *.* to 'repl_user'@'127.0.0.1' identified by 'Monty';
-- Query OK, 0 rows affected (0.00 sec)