MySQL 5.7 多实例单配置 & 多实例多配置文件安装 | 周末送资料

【作者】何海波，某商业银行运维工程师，主要负责商用中间件运维、mysql运维，个人爱好容器云。

---

01 MySQL 5.7 多实例单配置文件安装

1.创建mysql用户以及组

groupadd mysql 
useradd -r -g mysql -s /bin/false mysql

2.创建Mysql数据目录

mkdir -p /data/mysql/3306
mkdir -p /data/mysql/3307
mkdir -p /data/mysql/3308
chown -R mysql.mysql /data/

3.创建mysql logs目录

mkdir -p /logs/mysql/3306
mkdir -p /logs/mysql/3307
mkdir -p /logs/mysql/3308
mkdir -p /var/run/mysqld
chown -R mysql.mysql /logs/
chown -R mysql.mysql /var/run/mysqld

4.解压Mysql安装包

tar xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /
cd /
mv mysql-5.7.26-linux-glibc2.12-x86_64/ mysql
chown -R mysql.mysql /mysql

5.将Mysql加入环境变量

[root@mysqlmulti /]# . ~/.bash_profile 
[root@mysqlmulti /]# cat ~/.bash_profile 
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/mysql/bin
export PATH

6.初始化数据

cd /mysql/bin
./mysqld --initialize --user=mysql  --basedir=/mysql --datadir=/data/mysql/3306 --explicit_defaults_for_timestamp
./mysqld --initialize --user=mysql  --basedir=/mysql --datadir=/data/mysql/3307 --explicit_defaults_for_timestamp
./mysqld --initialize --user=mysql  --basedir=/mysql --datadir=/data/mysql/3308 --explicit_defaults_for_timestamp

7.修改配置文件

[root@mysqlmulti run]# cat /etc/my.cnf
[mysqld_multi]  
mysqld     = /mysql/bin/mysqld_safe  
mysqladmin = /mysql/bin/mysqladmin  
[mysqld3306]    
port = 3306  
server_id = 3306  
basedir =/mysql  
datadir =/data/mysql/3306  
log-bin=/data/mysql/3306/mysql-bin  
socket =/tmp/mysql3306.sock    
log-error =/logs/mysql/3306/mysqld3306.log  
pid-file =/var/run/mysqld/mysqld3306.pid  
[mysqld3307]    
port = 3307  
server_id = 3307  
basedir =/mysql  
datadir =/data/mysql/3307  
log-bin=/data/mysql/3307/mysql-bin  
socket =/tmp/mysql3307.sock    
log-error =/logs/mysql/3307/mysqld3307.log  
pid-file =/var/run/mysqld/mysqld3307.pid  
[mysqld3308]
port = 3308
server_id = 3308
basedir =/mysql          
datadir =/data/mysql/3308
log-bin=/data/mysql/3308/mysql-bin      
socket =/tmp/mysql3308.sock
log-error =/logs/mysql/3308/mysqld3308.log
pid-file =/var/run/mysqld/mysqld3308.pid

8.创建日志文件

touch /logs/mysql/3306/mysqld3306.log 
touch /logs/mysql/3307/mysqld3307.log 
touch /logs/mysql/3308/mysqld3308.log 
chown -R mysql.mysql /logs/

9.启动3306,3307,3308实例

mysqld_multi start 3306
mysqld_multi start 3307
mysqld_multi start 3308

10.访问3306、3307、3308实例，密码是第六步初始化数据生成的密码

mysql -uroot -p -S /tmp/mysql3306.sock
mysql -uroot -p -S /tmp/mysql3307.sock
mysql -uroot -p -S /tmp/mysql3308.sock

11.更改密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'wwwwww'; 
mysql> FLUSH PRIVILEGES;

12.查看多实例状态

[root@mysqlmulti bin]# mysqld_multi report
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Sat Jul 13 23:38:27 2019
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running

02 MySQL 5.7多实例多配置文件安装

本文旨在学习多实例多个cnf配置文件下的安装与配置。

本文中创建了3306、3307、3308三个实例。

1.创建mysql用户以及组

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

2.创建mysql数据目录

mkdir -p /data/{3306,3307,3308}/data
chown -R mysql.mysql /data/

3.创建mysql logs目录

mkdir -p /logs/{3306,3307,3308}
mkdir -p /logs/{3306,3307,3308}/redolog
mkdir -p /logs/{3306,3307,3308}/undolog
touch /logs/3306/mysqld3306.log 
touch /logs/3307/mysqld3307.log 
touch /logs/3308/mysqld3308.log 
chown -R mysql.mysql /logs/
mkdir -p /var/run/mysqld
chown -R mysql.mysql /var/run/mysqld

4.解压Mysql安装包

tar xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /
cd /
mv mysql-5.7.26-linux-glibc2.12-x86_64/ mysql
chown -R mysql.mysql /mysql

5.将Mysql加入环境变量

[root@mysqlmulti /]# . ~/.bash_profile 
[root@mysqlmulti /]# cat ~/.bash_profile 
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/mysql/bin
export PATH

6.修改配置文件

修改/data/3306/my3306.cnf文件

[root@mysql57 3306]# cat my3306.cnf 
[client]
port = 3306
socket = /data/3306/mysql3306.sock
[mysql]
auto-rehash
[mysqld_safe]
log-error = /logs/3306/mysqld3306.log
pid-error = /data/3306/mysql3306.pid
[mysqld]
user = mysql
port = 3306
socket= /data/3306/mysql3306.sock
pid-file = /data/3306/mysql3306.pid
datadir = /data/3306/data
basedir = /mysql
skip_name_resolve = 1
skip-external-locking
max_connections = 3000
max_connect_errors = 10
transaction_isolation = READ-COMMITTED
interactive_timeout=86400
wait_timeout=86400
back_log=600
####cache###### 
table_open_cache=2000
thread_cache_size=500
query_cache_size=128M
query_cache_min_res_unit=128k
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
join_buffer_size=2M
bulk_insert_buffer_size=16M
max_heap_table_size=64M
tmp_table_size=64M
###MyISAM####
key_buffer_size=64M
key_cache_block_size=4k
myisam_sort_buffer_size=2M
########innodb settings########
#innodb_page_size = 4k
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
#innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 50
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_log_group_home_dir = /logs/3306/redolog/
innodb_undo_directory = /logs/3306/undolog/
innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 4M
######mysqld-5.7########
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#skip-grant-tables

修改/data/3307/my3307.cnf文件

[root@mysql57 3307]# cat my3307.cnf 
[client]
port = 3307
socket = /data/3307/mysql3307.sock
[mysql]
auto-rehash
[mysqld_safe]
log-error = /logs/3307/mysqld3307.log
pid-error = /data/3307/mysql3307.pid
[mysqld]
user = mysql
port = 3307
socket= /data/3307/mysql3307.sock
pid-file = /data/3307/mysql3307.pid
datadir = /data/3307/data
basedir = /mysql
skip_name_resolve = 1
skip-external-locking
max_connections = 3000
max_connect_errors = 10
transaction_isolation = READ-COMMITTED
interactive_timeout=86400
wait_timeout=86400
back_log=600
####cache###### 
table_open_cache=2000
thread_cache_size=500
query_cache_size=128M
query_cache_min_res_unit=128k
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
join_buffer_size=2M
bulk_insert_buffer_size=16M
max_heap_table_size=64M
tmp_table_size=64M
###MyISAM####
key_buffer_size=64M
key_cache_block_size=4k
myisam_sort_buffer_size=2M
########innodb settings########
#innodb_page_size = 4k
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
#innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 50
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_log_group_home_dir = /logs/3307/redolog/
innodb_undo_directory = /logs/3307/undolog/
innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 4M
######mysqld-5.7########
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#skip-grant-tables

修改/data/3308/my3308.cnf文件

[root@mysql57 3308]# cat my3308.cnf 
[client]
port = 3308
socket = /data/3308/mysql3308.sock
[mysql]
auto-rehash
[mysqld_safe]
log-error = /logs/3308/mysqld3308.log
pid-error = /data/3308/mysql3308.pid
[mysqld]
user = mysql
port = 3308
socket= /data/3308/mysql3308.sock
pid-file = /data/3308/mysql3308.pid
datadir = /data/3308/data
basedir = /mysql
skip_name_resolve = 1
skip-external-locking
max_connections = 3000
max_connect_errors = 10
transaction_isolation = READ-COMMITTED
interactive_timeout=86400
wait_timeout=86400
back_log=600
####cache###### 
table_open_cache=2000
thread_cache_size=500
query_cache_size=128M
query_cache_min_res_unit=128k
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
join_buffer_size=2M
bulk_insert_buffer_size=16M
max_heap_table_size=64M
tmp_table_size=64M
###MyISAM####
key_buffer_size=64M
key_cache_block_size=4k
myisam_sort_buffer_size=2M
########innodb settings########
#innodb_page_size = 4k
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
#innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 50
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_log_group_home_dir = /logs/3308/redolog/
innodb_undo_directory = /logs/3308/undolog/
innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 4M
######mysqld-5.7########
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#skip-grant-tables

6.初始化数据

cd /mysql/bin
[root@mysql57 bin]# ./mysqld --initialize --user=mysql  --basedir=/mysql --datadir=/data/3306/data --explicit_defaults_for_timestamp
2019-07-15T11:51:33.672753Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-15T11:51:33.809864Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-15T11:51:33.883915Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e497ef6e-a6f6-11e9-8d94-080027704b98.
2019-07-15T11:51:33.923591Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-15T11:51:33.924124Z 1 [Note] A temporary password is generated for root@localhost: dxg4gx7od_0Y
[root@mysql57 bin]# ./mysqld --initialize --user=mysql  --basedir=/mysql --datadir=/data/3307/data --explicit_defaults_for_timestamp
2019-07-15T12:04:40.909570Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-15T12:04:41.109548Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-15T12:04:41.189840Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b9dd499e-a6f8-11e9-aafd-080027704b98.
2019-07-15T12:04:41.191921Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-15T12:04:41.192590Z 1 [Note] A temporary password is generated for root@localhost: yhFw?#fPl2&N
[root@mysql57 bin]# ./mysqld --initialize --user=mysql  --basedir=/mysql --datadir=/data/3308/data --explicit_defaults_for_timestamp
2019-07-15T12:14:45.071994Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-15T12:14:45.286324Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-15T12:14:45.373528Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 21fc66ed-a6fa-11e9-a44b-080027704b98.
2019-07-15T12:14:45.375971Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-15T12:14:45.376668Z 1 [Note] A temporary password is generated for root@localhost: u9;Cqar8>gaP

7.启动mysql，在/data/3306下创建mysql脚本，内容如下：

[root@mysql57 3306]# cat mysql
#!/bin/sh
port=3306
mysql_user="root"
mysql_pwd="wwwwww"
CmdPath="/mysql/bin"
mysql_sock="/data/${port}/mysql3306.sock"
#start Mysql Services
function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my3306.cnf 2>&1 > /dev/null &
    else
      printf "MySQL is running...\n"
      exit
    fi
}
#stop Mysql Services
function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql3306.sock shutdown
   fi
}
#restart Mysql Services
function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}
case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

分别在/data/3307和/data/3308下也创建类似mysql，需要需要里面3306为3307或3308

执行命令启动3306、3307、3308实例

[root@mysql57 bin]# /data/3306/mysql start
MySQL is running...
[root@mysql57 bin]# /data/3307/mysql start
MySQL is running...
[root@mysql57 bin]# /data/3308/mysql start
MySQL is running...

8.访问3306、3307、3308实例，密码是第六步初始化数据生成的密码

mysql -uroot -p -S /data/3306/mysql3306.sock
mysql -uroot -p -S /data/3307/mysql3307.sock
mysql -uroot -p -S /data/3308/mysql3308.sock

9.更改密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'wwwwww'; 
mysql> FLUSH PRIVILEGES;

10.查看多实例状态

[root@mysql57 ~]# ps -ef | grep mysqld | grep -v grep
root      6832     1  0 20:00 pts/1    00:00:00 /bin/sh /mysql/bin/mysqld_safe --defaults-file=/data/3306/my3306.cnf
mysql     7594  6832  0 20:00 pts/1    00:00:03 /mysql/bin/mysqld --defaults-file=/data/3306/my3306.cnf --basedir=/mysql --datadir=/data/3306/data --plugin-dir=/mysql/lib/plugin --user=mysql --log-error=/logs/3306/mysqld3306.log --pid-file=/data/3306/mysql3306.pid --socket=/data/3306/mysql3306.sock --port=3306
root      7810     1  0 20:13 pts/2    00:00:00 /bin/sh /mysql/bin/mysqld_safe --defaults-file=/data/3307/my3307.cnf
mysql     8572  7810  1 20:13 pts/2    00:00:06 /mysql/bin/mysqld --defaults-file=/data/3307/my3307.cnf --basedir=/mysql --datadir=/data/3307/data --plugin-dir=/mysql/lib/plugin --user=mysql --log-error=/logs/3307/mysqld3307.log --pid-file=/data/3307/mysql3307.pid --socket=/data/3307/mysql3307.sock --port=3307
root      8892     1  0 20:20 pts/5    00:00:00 /bin/sh /mysql/bin/mysqld_safe --defaults-file=/data/3308/my3308.cnf
mysql     9654  8892  5 20:20 pts/5    00:00:05 /mysql/bin/mysqld --defaults-file=/data/3308/my3308.cnf --basedir=/mysql --datadir=/data/3308/data --plugin-dir=/mysql/lib/plugin --user=mysql --log-error=/logs/3308/mysqld3308.log --pid-file=/data/3308/mysql3308.pid --socket=/data/3308/mysql3308.sock --port=3308
[root@mysql57 3308]# netstat -unltp | grep 3306 
tcp6       0      0 :::3306                 :::*                    LISTEN      7594/mysqld         
[root@mysql57 3308]# netstat -unltp | grep 3307
tcp6       0      0 :::3307                 :::*                    LISTEN      8572/mysqld         
[root@mysql57 3308]# netstat -unltp | grep 3308
tcp6       0      0 :::3308                 :::*                    LISTEN      9654/mysqld

11.停止mysql

[root@mysql57 3308]# /data/3306/mysql stop && /data/3307/mysql stop && /data/3308/mysql stop
Stoping MySQL...
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Stoping MySQL...
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Stoping MySQL...
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

如有任何问题，欢迎到社区原文下评论交流

mysql 5.7多实例单配置文件安装

http://www.talkwithtrend.com/Article/245297

mysql 5.7多实例多配置文件安装

http://www.talkwithtrend.com/Article/245307

觉得本文有用，请转发或点击“在看”，让更多同行看到

 资料/文章推荐：

• Mysql最佳安全配置手册

  http://www.talkwithtrend.com/Document/detail/tid/426401

• Mysql5.7—运维常用备份方式（超全）

  http://www.talkwithtrend.com/Document/detail/tid/402419

欢迎关注社区 “mySQL”技术主题 ，将会不断更新优质资料、文章。地址：

http://www.talkwithtrend.com/Topic/3873

下载 twt 社区客户端 APP

与更多同行在一起

高手随时解答你的疑难问题

轻松订阅各领域技术主题

浏览下载最新文章资料

长按识别二维码即可下载

或到应用商店搜索“twt”

长按二维码关注公众号

*本公众号所发布内容仅代表作者观点，不代表社区立场