安装必要工具
# 安装必要工具
yum -y install gcc gcc-c++ cmake make autoconf libtool-ltdl-devel gd-devel freetype-devel libxml2-devel libjpeg-devel libpng-devel openssl-devel curl-devel bison patch unzip libmcrypt-devel libmhash-devel libevent-devel ncurses-devel mlocate flex libaio-devel ntp openldap-devel libcurl-devel perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker iotop bzip2* ntpdate wget zip htop vim iftop nc nmap dstat sysstat lrzsz screen lftp curl strace lsof telnet tree tcpdump nmon busybox screen zlib zlib-devel glib glib-devel bzip2-devel pcre-devel openssl python-devel readline-devel tk-devel libselinux-python vim net-snmp net-snmp-devel net-snmp-utils nfs-utils rpcbind man glibc glibc-devel glib2 glib2-devel e2fsprogs-devel libxslt libxslt-devel openldap-clients openldap-servers parted
验收机器
# 查看CPU信息(型号)
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
# 查看物理CPU个数
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
# 查看每个物理CPU中core的个数(即核数)
cat /proc/cpuinfo| grep "cpu cores"| uniq
# 查看逻辑CPU的个数
cat /proc/cpuinfo| grep "processor"| wc -l
初始化
# 关闭所有防火墙
systemctl stop firewalld
systemctl status firewalld
systemctl disable firewalld
# 关闭selinux
vim /etc/selinux/config
SELINUX=disabled
数据库
# 下载安装包
cd /usr/local/src
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
# 验证签名
md5sum mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
# 官网签名值
56b94aef45542efdc8714423e0bd241f mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
# 解压到mysql目录
mkdir mysql
tar -xvf mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar -C mysql
# 删除原始包
rpm -qa | grep mysql
rpm -qa|grep mariadb
yum remove mariadb-libs
# 进入目录依次按照
cd /usr/local/src/mysql
rpm -ivh mysql-community-common-5.7.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.30-1.el7.x86_64.rpm
# 查看按照情况
rpm -qal |grep mysql
# 操作命令
systemctl start mysqld.service
systemctl status mysqld.service
systemctl stop mysqld.service
systemctl enable mysqld.service
# 查看启动状态
ps -ef|grep mysql
netstat -anop|grep 3306
# 查看临时密码
grep 'temporary password' /var/log/mysqld.log > mysqlpassword.txt
# lop.>sfs/4rN
# mysql -uroot -p
# 自定义数据库目录,方便后续扩展
# 拷贝/var/lib/mysql到/data下
# 保留原始/var/lib/mysql不删除
cd /data && mkdir -p dbs/logs/mysql
chown -R mysql:mysql dbs/
cp -p -r /var/lib/mysql ./
# 修改数据指向目录
vim /etc/my.cnf
# user changed dir
datadir=/data/dbs/mysql
# 登录数据库查看
# 确认数据目录改变
mysql -uroot -p
show variables like "%datadir%";
# 最后确认要开机自启动
systemctl enable mysqld.service
数据库授权
-- 测试数据库
-- 专属用户
CREATE USER 'pj_name'@'%' IDENTIFIED BY 'passwd';
-- 基础能库
create database if not exists pj_xxx default charset utf8mb4 collate utf8mb4_general_ci;
grant insert,delete,update,select,create,drop,alter,index,lock tables on pj_xxx.* to 'pj_name'@'%' ;
FLUSH PRIVILEGES;
优化配置
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# datadir=/var/lib/mysql
# 数据挂数据盘
datadir=/data/dbs/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 统一编码
character_set_server=utf8mb4
# 默认timestamp
explicit_defaults_for_timestamp=true
# 优化配置
autocommit=1
# 最大连接数
max_connections=900
# 超请求连接数堆栈的数量
# 不可超过系统值 cat /proc/sys/net/ipv4/tcp_max_syn_backlog
back_log=200
# 连接超时时间15s
connect_timeout=15
# 从连接超时时间30s
slave_net_timeout=30
# 资源等待超时时间30s
innodb_lock_wait_timeout=30
# 允许最大连接错误数
max_connect_errors=18446744073709551615
# 设置物理内存的75%
innodb_buffer_pool_size=6G
# 并行线程数量,默认为0
innodb_thread_concurrency=8
# 显示默认事务
transaction_isolation=REPEATABLE-READ
# 不反向解释域名
skip_name_resolve=1
# 独享表空间
innodb_file_per_table=1
# 单列索引长度扩大到3072字节
innodb_large_prefix=1
# 打印deadlocks日志
innodb_print_all_deadlocks=1
innodb_sort_buffer_size=16M
# 允许单个记录超过限制值
max_allowed_packet=64M
# MySQL读入缓冲区的大小
read_buffer_size=16M
# MySQL的随机读缓冲区大小
read_rnd_buffer_size=8M
# MySQL的顺序读缓冲区大小
sort_buffer_size=8M
# 开启慢查询日志
slow_query_log=1
# 超出次设定值的SQL即被记录到慢查询日志
long_query_time=6
# 指定慢查询日志位置
slow_query_log_file=/data/dbs/logs/mysql/slow.log
# 记录下没有使用索引的查询
log_queries_not_using_indexes=1
# 记录管理语句
log_slow_admin_statements=1
# 开启复制从库复制的慢查询的日志
log_slow_slave_statements=1
# 设置每分钟增长的没有使用索引查询的日志数量
log_throttle_queries_not_using_indexes=10
# 自动清除过期日志的时间
expire_logs_days=90
# 默认值是1GB
max_binlog_size=128M
# 超过100行才记录慢查询
min_examined_row_limit=100
导入数据
# 导入系统基础表
mysql -u pj_name -p pj_xxx < /root/up_files/sql/pj_xxx/pj_xxx.sql
# 导入数据
mysql -u pj_name -p
-- 导入基础数据
use pj_xxx;
source /root/up_files/sql/pj_xxx/sys_dict.sql
source /root/up_files/sql/pj_xxx/sys_dict_item.sql
source /root/up_files/sql/pj_xxx/sys_oauth_client_details.sql
source /root/up_files/sql/pj_xxx/user_type.sql
字节单位在线转换:
https://www.osgeo.cn/app/s2644
网友评论