由于环境的需要，一个实例所有表要区分大小表，另一个实例不区分。部署过来也参数了很多资料，也遇到些坑，这里将我的部署记录下来，以便大家参考，操作过程如有不当之处，还请指点，谢谢！！ 具体部署过程记录如下：
- 安装环境准备 1.1 主机环境准备 1.1.1. HOST 配置每台主机的hosts(/etc/hosts),添加host_ip $hostname到/etc/hosts文件中。
[root@~]# echo " 192.168.8.100 multidb" >> /etc/hosts
1.1.2. 关闭selinux
[root@~]# sed -i 's/SELINUX=enforcing/SELINUX=permissive/g' /etc/selinux/config
[root@~]# setenforce 0
1.1.3. 系统主机时间、时区、系统语言 修改时区
[root@~]# ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
修改系统语言环境
[root@~]# echo 'LANG="en_US.UTF-8"' >> /etc/profile
[root@~]# source /etc/profile
配置主机NTP时间同步
[root@~]# yum -y install ntp
[root@~]# systemctl enable ntpd
[root@~]# systemctl start ntpd
[root@~]# echo 'server ntp1.aliyun.com' >> /etc/ntp.conf
[root@~]# echo 'server ntp2.aliyun.com' >> /etc/ntp.conf
- Mysql安装部署 2.1 环境清理
[root@~]# rpm -e --nodeps mariadb-libs-*
[root@~]# rpm -e mysql*
[root@~]# yum list installed | grep mariadb ---确认已经清理干净
[root@~]# yum list installed | grep mysql ---确认已经清理干净
[root@~]# mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%M%S`.bak
2.2 安装依赖包
[root@~]# 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 创建用户
[root@~]# groupadd mysql
[root@~]# useradd mysql -g mysql -s /sbin/nologin -M
2.4 配置系统内核参数
[root@~]# 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
[root@~]# modprobe br_netfilter
[root@~]# sysctl -p
2.5 修改系统资源限制
[root@~]# 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 修改环境变量
[root@~]# 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
[root@~]# source /etc/profile
2.7 安装mysql多实例 2.7.1. 创建软件安装目录(部署路径请根据实际修改)
[root@~]# mkdir -pv /data/mysql/{3306,3307}
[root@~]# mkdir -v /data/mysql/3306/{logs,data,binlog}
[root@~]# mkdir -v /data/mysql/3307/{logs,data,binlog}
2.7.2. boost源码包下载
[root@opt~]# cd /opt
[root@opt~]# wget -c http://www.sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
[root@opt~]# tar zxvf boost_1_59_0.tar.gz
[root@opt~]# mv boost_1_59_0 /usr/local/boost
2.7.3. 下载并编译安装MySQL5.7.28 [root@opt~]# cd /opt```
[root@opt~]# wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.28.tar.gz
[root@opt~]# tar zxvf mysql-boost-5.7.28.tar.gz
[root@opt~]# cd mysql-5.7.28
[root@mysql-5.7.28~]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql
DMYSQL_DATADIR=/usr/local/mysql/data
DMYSQL_UNIX_ADDR=/usr/local/mysql/tmp/mysql.sock
DDEFAULT_CHARSET=utf8
DDEFAULT_COLLATION=utf8_bin
DEXTRA_CHARSETS=all
DENABLED_LOCAL_INFILE=ON
DWITH_INNOBASE_STORAGE_ENGINE=1
DWITH_FEDERATED_STORAGE_ENGINE=1
DWITH_BLACKHOLE_STORAGE_ENGINE=1
DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
DWITHOUT_PARTITION_STORAGE_ENGINE=1
DWITH_FAST_MUTEXES=1
DWITH_SSL=system
DWITH_ZLIB=bundled
DWITH_EDITLINE=bundled
DWITH_BOOST=/usr/local/boost
DENABLED_LOCAL_INFILE=1
DWITH_READLINE=1
DWITH_EMBEDDED_SERVER=1
DWITH_DEBUG=0
[root@mysql-5.7.28~]# make
[root@mysql-5.7.28~]# make install
2.7.4. 配置MySQL环境变量
[root@~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile [root@~]# tail -1 /etc/profile [root@~]# source /etc/profile
2.7.5. MySQL参数配置
（1）server_id=3306与server_id=3307数值请根据实际配置，注意配置的id值与局域网内其他各实例所配置的数值不可以冲突；
（2）max_connections=1000配置MySQL数据库的最大连接数，根据实际需要配置；
（3）以下参数仅供参考，部署时根据实际需要添加配置，两份配置文件全部内容如下
**3306配置文件内容：**
[root@~]# vim /data/mysql/3306/my3306.cnf [client] port = 3306 socket = /data/mysql/3306/mysql3306.sock
[mysql] prompt="\u@jsshapp \R:\m:\s [\d]> " no-auto-rehash
[mysqld] user = mysql port = 3306 symbolic-links = 0 basedir = /usr/local/mysql 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
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:1024M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend
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 = 32k
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
**3307配置(有添加lower_case_table_names)文件内容:**
[root@~]# vim /data/mysql/3307/my3307.cnf [client] port = 3307 socket = /data/mysql/3307/mysql3307.sock
[mysql] prompt="\u@jsshapp \R:\m:\s [\d]> " no-auto-rehash
[mysqld] user = mysql port = 3307 symbolic-links = 0 lower_case_table_names = 1 basedir = /usr/local/mysql 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:1024M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend
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 = 32k
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 [root@~]# chown -R mysql:mysql /data [root@~]# chown -R mysql:mysql /usr/local/mysql
2.7.6. dbone实例初始化
操作实例初始化
[root@~]# mysqld --defaults-file=/data/mysql/3306/my3306.cnf --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3306/data/
生成ssl文件
[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data
启动dbone实例
[root@~]# mysqld_safe --defaults-file=/data/mysql/3306/my3306.cnf --user=mysql &
修改root密码
[root@~]# less /data/mysql/3306/logs/error.log|grep 'A temporary password' [root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock Enter password: mysql> alter user 'root'@'localhost' identified by '密码'; mysql> flush privileges; mysql> exit;
通过tcp/ip协议登陆mysql验证
[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock
停止dbone实例
[root@~]# mysqladmin -uroot -p -S /data/mysql/3306/mysql3306.sock shutdown Enter password:
2.7.7. dbtwo实例初始化
操作实例初始化
[root@~]# mysqld --defaults-file=/data/mysql/3307/my3307.cnf --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3307/data/
生成ssl文件
[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/data
启动dbtwo实例
[root@~]# mysqld_safe --defaults-file=/data/mysql/3307/my3307.cnf --user=mysql &
修改root密码
[root@~]# less /data/mysql/3307/logs/error.log|grep 'A temporary password' [root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock Enter password: mysql> alter user 'root'@'localhost' identified by '密码'; mysql> flush privileges; mysql> exit;
通过tcp/ip协议登陆mysql验证
[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock
停止dbtwo实例
[root@~]# mysqladmin -uroot -p -S /data/mysql/3307/mysql3307.sock shutdown Enter password:
2.7.8. 配置多实例系统启动脚本
配置实例3306系统服务脚本
[root@~]# vim /etc/init.d/dbone #!/bin/sh #chkconfig: 2345 80 90 #Simple dbone mysql init.d script conceived to work on Linux systems
SOCK="/data/mysql/3306/mysql3306.sock" CNF="/data/mysql/3306/my3306.cnf" MYSQL_USER="mysql" MYSQL_PWD="密码" CmdPath="/usr/local/mysql/bin"
#startup function function_start_mysql() { printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=${CNF} --user=${MYSQL_USER} 2>&1 > /dev/null & }
#stop function function_stop_mysql() { printf "Stoping MySQL...\n" ${CmdPath}/mysqladmin -u ${MYSQL_USER} -p${MYSQL_PWD} -S ${SOCK} shutdown }
#restart function 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: dbone {start|stop|restart}\n" esac
配置实例3307系统服务脚本
[root@~]# vim /etc/init.d/dbtwo #!/bin/sh #chkconfig: 2345 81 92 #Simple dbtwo mysql init.d script conceived to work on Linux systems
SOCK="/data/mysql/3307/mysql3307.sock" CNF="/data/mysql/3307/my3307.cnf" MYSQL_USER="mysql" MYSQL_PWD="密码" CmdPath="/usr/local/mysql/bin"
#startup function function_start_mysql() { printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=${CNF} --user=${MYSQL_USER} 2>&1 > /dev/null & }
#stop function function_stop_mysql() { printf "Stoping MySQL...\n" ${CmdPath}/mysqladmin -u ${MYSQL_USER} -p${MYSQL_PWD} -S ${SOCK} shutdown }
#restart function 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: dbtwo {start|stop|restart}\n" esac
配置授权添加系统服务
[root@~]# chmod +x /etc/init.d/dbone [root@~]# chmod +x /etc/init.d/dbtwo [root@~]# chkconfig --add dbone [root@~]# chkconfig –add dbtwo [root@~]# chkconfig --list dbone [root@~]# chkconfig --list dbtwo
启动两个db服务
[root@~]# service dbone start [root@~]# service dbtwo start
停止两个db服务
[root@~]# service dbone stop [root@~]# service dbtwo stop
查看两个db服务进程
[root@~]# netstat -tnlp|grep 3306 [root@~]# netstat -tnlp|grep 3307
2.7.9. 数据导入
第一个实例创建库与用户密码：
[root@~]# 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,execute on testone.* to 'testone'@'%' identified by '密码'; mysql> set global log_bin_trust_function_creators=TRUE; mysql> flush privileges; mysql> exit [root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock testone < /data/test.sql
第二个实例创建库与用户密码：
[root@~]# 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,execute on testtwo.* to 'testtwo'@'%' identified by '密码'; mysql> flush privileges; mysql> exit [root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock testtwo < /data/testtwo.sql
2.7.10. 防火墙配置
根据实际要求，添加开放端口
[root@~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent [root@~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent
重新载入
[root@~]# firewall-cmd --reload
