CentOS7.X使用mysql_multi部署MySQL5.7.28多实例
程序员文章站
2024-02-10 13:04:28
...
由于客户环境部署要求其中一个实例表要区分大小写，另一个实例为不区分大小写。部署花了不少时间，遇到不少的坑，这里将我的部署记录下来，以便大家参考，部署过程如有发现不当之处，还请评论区指明，谢谢！！以下为具体部署过程记录：
安装环境准备
1.1 主机环境准备
1.1.1. HOST
配置主机的hosts(/etc/hosts),添加host_ip $hostname到/etc/hosts文件中。
[[email protected] ~]# echo "192.168.1.130 multidb" >> /etc/hosts
1.1.2. 关闭selinux
[[email protected] ~]# sed -i 's/SELINUX=enforcing/SELINUX=permissive/g' /etc/selinux/config
[[email protected] ~]# setenforce 0
1.1.3. 系统主机时间、时区、系统语言 修改时区
[[email protected] ~]# ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
修改系统语言环境
[[email protected] ~]# echo 'LANG="en_US.UTF-8"' >> /etc/profile
[[email protected] ~]# source /etc/profile
配置主机NTP时间同步
[[email protected] ~]# yum -y install ntp
[[email protected] ~]# systemctl enable ntpd
[[email protected] ~]# systemctl start ntpd
[[email protected] ~]# echo 'server ntp1.aliyun.com' >> /etc/ntp.conf
[[email protected] ~]# echo 'server ntp2.aliyun.com' >> /etc/ntp.conf
Mysql安装部署
2.1 环境清理
[[email protected]~]# rpm -e --nodeps mariadb-libs-*
[[email protected]~]# rpm -e mysql*
[[email protected]~]# yum list installed | grep mariadb ---确认已经清理干净
[[email protected]~]# yum list installed | grep mysql ---确认已经清理干净
[[email protected]~]# mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%M%S`.bak
2.2 安装依赖包
[[email protected] ~]# yum -y install bzr zlib-devel gcc-c++ ncurses ncurses-devel libev make cmake \
gcc autoconf automake zlib libxml libgcrypt libtool bison perl perl-devel libaio libaio-devel \
perl-Time-HiRes perl-DBD-MySQL perl-Digest-MD5 rsync perl-Data-Dumper net-tools \
wget vim openssl openssl-devel
2.3 创建用户
[[email protected] ~]# groupadd mysql
[[email protected] ~]# useradd mysql -g mysql -s /sbin/nologin -M
2.4 配置系统内核参数
[[email protected] ~]# vi /etc/sysctl.conf
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_keepalive_time = 120
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
vm.swappiness = 0
net.ipv4.ip_forward=1
net.bridge.bridge-nf-call-iptables=1
net.ipv4.neigh.default.gc_thresh1=4096
net.ipv4.neigh.default.gc_thresh2=6144
net.ipv4.neigh.default.gc_thresh3=8192
kernel.shmmax = 25769803774
kernel.shmmni = 4096
kernel.shmall = 16777216
kernel.sem = 1010 129280 1010 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
fs.file-max = 6815744
[[email protected] ~]# modprobe br_netfilter
[[email protected] ~]# sysctl -p
2.5 修改系统资源限制
[[email protected] ~]# vi /etc/security/limits.conf
mysql soft nproc 65535
mysql hard nproc 65535
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft stack 10240
2.6 修改环境变量
[[email protected] ~]# vi /etc/profile
if [ $USER = "mysql" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
[[email protected]~]# source /etc/profile
2.7 安装mysql多实例2.7.1. 创建软件安装目录(部署路径请根据实际修改)
[[email protected]~]# mkdir -pv /data/mysql/{3306,3307}
[[email protected]~]# mkdir -v /data/mysql/3306/{logs,data,binlog}
[[email protected]~]# mkdir -v /data/mysql/3307/{logs,data,binlog}
2.7.2. MySQL安装包下载
[[email protected]~]# cd /opt
[[email protected]~]# wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[[email protected]~]# tar zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[[email protected]~]# mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql
[[email protected]~]# chown -R mysql:mysql /usr/local/mysql
[[email protected]~]# chown -R mysql:mysql /data
2.7.3. MySQL参数配置 配置my.cnf参数文件（1）server_id=3306与server_id=3307数值请根据实际配置，注意配置的id值与局域网内其他各实例所配置的数值不可以冲突；（2）max_connections=1000配置MySQL数据库的最大连接数，根据实际需要配置，其他参数的优化根据实际需要修改或添加；（3）配置文件全部内容如下
[[email protected]~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /data/mysql/mysqld_multi.log
#user=root
#pass=
[mysql]
prompt="\\[email protected] \R:\m:\s [\d]> "
no-auto-rehash
[mysqld3306]
user = mysql
port = 3306
symbolic-links = 0
#basedir = /usr/
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/mysql3306.sock
pid-file = /data/mysql/3306/mysqld3306.pid
server_id = 3306
character_set_server = utf8
max_connections = 1000
skip_name_resolve = 1
open_files_limit = 65536
thread_cache_size = 64
table_open_cache = 4096
table_definition_cache = 1024
table_open_cache_instances = 64
max_prepared_stmt_count = 1048576
explicit_defaults_for_timestamp = true
log_timestamps = system
binlog_format = row
log_bin = /data/mysql/3306/binlog/mysql-bin
binlog_rows_query_log_events = on
expire_logs_days = 7
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
sync_binlog = 1
log_bin_trust_function_creators = 1
slow_query_log = on
slow_query_log_file = /data/mysql/3306/data/slow.log
log-error = /data/mysql/3306/logs/error.log
log_queries_not_using_indexes = on
long_query_time = 1.000000
gtid_mode = on
enforce_gtid_consistency = on
default_storage_engine = innodb
default_tmp_storage_engine = innodb
innodb_data_file_path = ibdata1:12M:autoextend:max:2000M
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2000M
innodb_buffer_pool_filename = ib_buffer_pool
innodb_log_files_in_group = 3
innodb_log_file_size = 512M
innodb_online_alter_log_max_size = 1024M
innodb_open_files = 4096
innodb_page_size = 16k
innodb_thread_concurrency = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_print_all_deadlocks = on
innodb_lock_wait_timeout = 20
innodb_spin_wait_delay = 128
innodb_autoinc_lock_mode = 2
innodb_io_capacity = 200
innodb_io_capacity_max = 2000
#innodb_flush_neighbors =
innodb_log_buffer_size = 8M
innodb_flush_log_at_timeout = 1
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 4
autocommit = 1
innodb_buffer_pool_dump_pct = 25
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
[mysqld3307]
user = mysql
port = 3307
symbolic-links = 0
lower_case_table_names = 1
#basedir = /usr/
datadir = /data/mysql/3307/data
socket = /data/mysql/3307/mysql3307.sock
pid-file = /data/mysql/3307/mysqld3307.pid
server_id = 3307
character_set_server = utf8
max_connections = 1000
skip_name_resolve = 1
open_files_limit = 65536
thread_cache_size = 64
table_open_cache = 4096
table_definition_cache = 1024
table_open_cache_instances = 64
max_prepared_stmt_count = 1048576
explicit_defaults_for_timestamp = true
log_timestamps = system
binlog_format = row
log_bin = /data/mysql/3307/binlog/mysql-bin
binlog_rows_query_log_events = on
expire_logs_days = 7
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
sync_binlog = 1
slow_query_log = on
slow_query_log_file = /data/mysql/3307/data/slow.log
log-error = /data/mysql/3307/logs/error.log
log_queries_not_using_indexes = on
long_query_time = 1.000000
gtid_mode = on
enforce_gtid_consistency = on
default_storage_engine = innodb
default_tmp_storage_engine = innodb
innodb_data_file_path = ibdata1:12M:autoextend:max:2000M
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2000M
innodb_buffer_pool_filename = ib_buffer_pool
innodb_log_files_in_group = 3
innodb_log_file_size = 512M
innodb_online_alter_log_max_size = 1024M
innodb_open_files = 4096
innodb_page_size = 16k
innodb_thread_concurrency = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_print_all_deadlocks = on
innodb_lock_wait_timeout = 20
innodb_spin_wait_delay = 128
innodb_autoinc_lock_mode = 2
innodb_io_capacity = 200
innodb_io_capacity_max = 2000
#innodb_flush_neighbors =
innodb_log_buffer_size = 8M
innodb_flush_log_at_timeout = 1
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 4
autocommit = 1
innodb_buffer_pool_dump_pct = 25
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
[mysqldump]
quick
max_allowed_packet = 32M
2.7.4. 配置MySQL环境变量
[[email protected]~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
[[email protected]~]# tail -1 /etc/profile
[[email protected]~]# source /etc/profile
2.7.5. testone实例初始化 操作实例初始化
[[email protected]~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3306/data/ > /tmp/3306.log 2>&1
[[email protected]~]# tail -100f /tmp/3306.log
—使用tail命令查看初始化日志，有出现如下内容，即表示初始化完成（其中#5+t+xYW+<t?即为root用户的临时密码）A temporary password is generated for [email protected]: #5+t+xYW+<t?
生成ssl文件
[[email protected]~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data
启动testone实例
[[email protected]~]# mysqld_multi start 3306
[[email protected]~]# tail -100f /data/mysql/3306/logs/error.log
----使用tail命令查看启动日志，有出现即表示启动成功
Version: '5.7.28-log' socket: '/data/mysql/3306/mysql3306.sock' port: 3306 MySQL Community Server (GPL)
修改root密码
[[email protected]~]# less /tmp/3306.log | grep 'A temporary password'
[[email protected]~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock
Enter password:
mysql> alter user 'root'@'localhost' identified by '统一密码';
mysql> flush privileges;
mysql> exit;
验证 testone root用户统一密码（界面正常输出information_schema内容表示正常）
[[email protected]~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock -e "show databases;" | grep information_schema
Enter password:
修改my.cnf配置文件，将修改的统一密码，添加到配置文件中
[[email protected]~]# sed -i "[email protected]^#[email protected][email protected]" /etc/my.cnf
[[email protected]~]# sed -i "[email protected]^#[email protected]=统一密码@g" /etc/my.cnf
[[email protected]~]# cat /etc/my.cnf | grep pass= ---使用cat命令查看配置文件pass字段输出的结果是否一致
停止testone实例
[[email protected]~]# mysqld_multi stop 3306
[[email protected]~]# netstat -tnlp|grep 3306 ---输入结果为空表示服务停止正常
2.7.6. testtwo实例初始化 操作实例初始化
[[email protected]~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3307/data/ > /tmp/3307.log 2>&1
[[email protected]~]# tail -100f /tmp/3307.log
—使用tail命令查看初始化日志，有出现如下内容，即表示初始化完成（其中-pn>t;Ye)Ay6=I即为root用户的临时密码）A temporary password is generated for [email protected]: -pn>t;Ye)Ay6=I 生成ssl文件
[[email protected]~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/data
启动testwo实例
[[email protected]~]# mysqld_multi start 3307
----使用tail命令查看启动日志，有出现即表示启动成功
[[email protected]~]# tail -100f /data/mysql/3307/logs/error.log
Version: '5.7.28-log' socket: '/data/mysql/3307/mysql3307.sock' port: 3307 MySQL Community Server (GPL)
修改root密码
[[email protected]~]# less /tmp/3307.log|grep 'A temporary password'
[[email protected]~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock
Enter password:
mysql> alter user 'root'@'localhost' identified by '统一密码';
mysql> flush privileges;
mysql> exit;
验证 testtwo root用户统一密码（界面正常输出information_schema内容表示正常）
[[email protected]~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock -e "show databases;" | grep information_schema
Enter password:
停止testtwo实例
[[email protected]~]# mysqld_multi stop 3307
[[email protected]~]# netstat -tnlp|grep 3307 ---输入结果为空表示服务停止正常
2.7.7. mysqld_multi多实例管理命令 启动全部实例
[[email protected]~]# mysqld_multi start
停止单个实例
[[email protected]~]# mysqld_multi stop 3306
[[email protected]~]# mysqld_multi stop 3307
启动单个实例
[[email protected]~]# mysqld_multi start 3306
[[email protected]~]# mysqld_multi start 3307
查看全部实例的状态(is running)
[[email protected]~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
查看单个实例状态
[[email protected]~]# mysqld_multi report 3306
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
[[email protected]~]# mysqld_multi report 3307
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
停止全部实例
[[email protected]~]# mysqld_multi stop
2.7.8. 数据导入（1）将待导入的数据库脚本（test.sql、testtwo.sql）文件（名称根据实际情况操作）上传至/data/路径下（2）使用命令确认两实例已经处于运行状态
[[email protected]~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
（3）进入testone的mysql数据库创建testone数据库实例、用户名及密码并导入数据
[[email protected]~]# cd /data/
[[email protected]~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock
Enter password:
mysql>create database testone default character set utf8 collate utf8_bin;
mysql> grant select,insert,update,delete,create,alter,execute on testone.* to 'testone'@'%' identified by '密码';
mysql> flush privileges;
mysql> exit
[[email protected]~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock testone < /data/test.sql
（3）进入testtwo的mysql数据库，创建testtwo服务数据库实例、用户名与密码并导入数据
[[email protected]~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock
Enter password:
mysql> create database testtwo default character set utf8 collate utf8_bin;
mysql> grant select,insert,update,delete,create,alter,execute on testtwo.* to 'testtwo'@'%' identified by '密码';
mysql> flush privileges;
mysql> exit
[[email protected]~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock testtwo < /data/testtwo.sql
2.7.9. 防火墙配置 根据实际要求，添加开放端口
[[email protected]~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[[email protected]~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent
重新载入
[[email protected]~]# firewall-cmd --reload
下一篇： Oracle的游标-CURSOR