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.
