ubuntu mysql5.7 multi 单机多端口

一、配置

 vi /etc/mysql/mysqld_multi.cnf

[mysqld_multi]
mysqld      = /usr/sbin/mysqld
mysqladmin  = /usr/bin/mysqladmin
log         = /var/log/mysql/mysqld_multi.log

[mysqld1]
port            = 3307
socket          = /tmp/mysqld_1.sock
pid-file        = /tmp/mysqld_1.pid
log_error       = /var/log/mysql/error_1.log
datadir         = /data/mysql/1
bind-address    = 127.0.0.1
user            = mysql
basedir         = /usr
tmpdir          = /tmp
skip-external-locking
skip-name-resolve
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4

explicit_defaults_for_timestamp = true

sql-mode                = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

expire_logs_days        = 10
max_binlog_size         = 100M

[mysqld2]
port            = 3308
socket          = /tmp/mysqld_2.sock
pid-file        = /tmp/mysqld_2.pid
log_error       = /var/log/mysql/error_2.log
datadir         = /data/mysql/2
bind-address    = 127.0.0.1
user            = mysql
basedir         = /usr
tmpdir          = /tmp
skip-external-locking
skip-name-resolve
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4

explicit_defaults_for_timestamp = true

sql-mode                = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

expire_logs_days        = 10
max_binlog_size         = 100M

[mysqld3]
port            = 3309
socket          = /tmp/mysqld_3.sock
pid-file        = /tmp/mysqld_3.pid
log_error       = /var/log/mysql/error_3.log
datadir         = /data/mysql/3
bind-address    = 127.0.0.1
user            = mysql
basedir         = /usr
tmpdir          = /tmp
skip-external-locking
skip-name-resolve
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4

explicit_defaults_for_timestamp = true

sql-mode                = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

expire_logs_days        = 10
max_binlog_size         = 100M

二、设置文件权限

vi /etc/apparmor.d/usr.sbin.mysqld

    /tmp/mysqld_1.pid rw,
    /tmp/mysqld_1.sock rw,
    /tmp/mysqld_1.sock.lock rw,
    /tmp/mysqld_2.pid rw,
    /tmp/mysqld_2.sock rw,
    /tmp/mysqld_2.sock.lock rw,
    /tmp/mysqld_3.pid rw,
    /tmp/mysqld_3.sock rw,
    /tmp/mysqld_3.sock.lock rw,
    /data/mysql/ r,
    /data/mysql/** rwk,

service apparmor reload

三、初始化数据目录

sudo -u mysql /usr/sbin/mysqld --defaults-file=/etc/mysql/mysqld_multi.cnf --datadir=/data/mysql/1 --user=mysql --initialize
sudo -u mysql /usr/sbin/mysqld --defaults-file=/etc/mysql/mysqld_multi.cnf --datadir=/data/mysql/2 --user=mysql --initialize
sudo -u mysql /usr/sbin/mysqld --defaults-file=/etc/mysql/mysqld_multi.cnf --datadir=/data/mysql/3 --user=mysql --initialize

四、启动

/usr/bin/mysqld_multi --defaults-file=/etc/mysql/mysqld_multi.cnf start 1-3

五、修改root密码

/usr/bin/mysqladmin -u root -p -P 3307 -S /tmp/mysqld_1.sock password