1.#安裝基本軟件包
[root@XAG137 ~]# yum -y install nano vim wget curl net-tools lsof zip unzip
2.关闭firewall：
#停止firewall
[root@XAG137 ~]# systemctl stop firewalld.service
#禁止firewall开机启动
[root@XAG137 ~]# systemctl disable firewalld.service
- 主机名设置
[root@XAG137 ~]# hostname
XAG137
[root@XAG137 ~]# vim /etc/sysconfig/network
[root@XAG137 ~]# cat /etc/sysconfig/network
# Created by anaconda
NETWORKING=yes
HOSTNAME=XAG137
4.关闭selinux
[root@XAG137 ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
[root@XAG137 ~]# setenforce 0
- 卸载mysql 或 mariadb
[root@XAG137 ~]# rpm -qa|grep -i mysql
[root@XAG137 ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
[root@XAG137 ~]# yum remove mariadb*
[root@XAG137 ~]# rm /etc/my.cnf
- 安装系统依赖包
[root@XAG137 ~]# yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel libaio-devel perl libaio wget lrzsz vim libnuma* bzip2 xz
- 修改系统限制参数
[root@XAG137 ~]#cat >> /etc/security/limits.conf << EOF
---------------------------------------------------------------------
#
###custom
#
* soft nofile 65535
* hard nofile 65535
* soft nproc 65535
* hard nproc 65535
EOF
---------------------------------------------------------------------
- 修改内核参数
[root@XAG137 ~]# cat >>/etc/sysctl.conf <<"EOF"
vm.swappiness=0
#增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535
#减少断开连接时 ，资源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10
#改变本地的端口范围
net.ipv4.ip_local_port_range = 1024 65535
#允许更多的连接进入队列
net.ipv4.tcp_max_syn_backlog = 4096
#对于只在本地使用的数据库服务器
net.ipv4.tcp_fin_timeout = 30
#端口监听队列
net.core.somaxconn=65535
#接受数据的速率
net.core.netdev_max_backlog=65535
net.core.wmem_default=87380
net.core.wmem_max=16777216
net.core.rmem_default=87380
net.core.rmem_max=16777216
#减少失效连接所占用的TCP资源的数量，加快资源回收的效率
net.ipv4.tcp_keepalive_time = 120
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
#Linux内核参数中最重要的参数之一，用于定义单个共享内存段的最大值( 这个值的大小对于64位linux系统，可取的最大值为物理内存值-1byte)
kernel.shmmax = 4294967295
#就是告诉Linux内核除非虚拟内存完全满了，否则不要使用交换区
Vm.swappiness = 0
EOF
[root@XAG137 ~]# sysctl -p
- 添加mysql 组和mysql 用户。所有的文件和目录应该在mysql 用户下
[root@XAG137 ~]# groupadd mysql
[root@XAG137 ~]# useradd -g mysql mysql -d /home/mysql -s /sbin/nologin
- 创建目录&授权
mkdir -p /usr/local/mysql/data
mkdir -p /usr/local/mysql/etc
mkdir -p /usr/local/mysql/log
mkdir -p /usr/local/mysql/tmp
mkdir -p /usr/local/mysql/dumps
mkdir -p /usr/local/mysql/undo
mkdir -p /usr/local/mysql/redo
chown -R mysql:mysql /usr/local/mysql/
- 添加环境变量
[root@XAG137 mysql]# sed -i '$a export PATH=$PATH:/usr/local/servers/mysql/bin' /etc/profile;
[root@XAG137 mysql]# source /etc/profile
12.安裝位置
[root@XAG137 ~]# cd /usr/local/src
[root@XAG137 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
#解压安装包
[root@XAG137 ~]# mkdir -p /usr/local/servers
[root@XAG137 ~]# tar -zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz -C /usr/local/servers/
[root@XAG137 ~]# mv /usr/local/servers/mysql-5.7.27-linux-glibc2.12-x86_64 /usr/local/servers/mysql
[root@XAG137 ~]# chown mysql.mysql -R /usr/local/servers/mysql
- 配置參數my.cnf
[root@XAG137 ~]# touch /usr/local/mysql/log/error.log
[root@XAG137 ~]# mkdir -p /usr/local/mysql/binlog/
[root@XAG137 ~]# chown mysql.mysql -R /usr/local/mysql/
[root@XAG137 ~]# chown mysql.mysql -R /usr/local/servers/mysql
[root@XAG137 ~]# vim /etc/my.cnf
[client]
port=3306
socket=/usr/local/mysql/tmp/mysql.socket
[mysqld]
port = 3306
innodb_undo_directory=/usr/local/mysql/undo
innodb_undo_tablespaces=4
socket = /usr/local/mysql/tmp/mysql.socket
basedir = /usr/local/servers/mysql
datadir = /usr/local/mysql/data
log-error = /usr/local/mysql/log/error.log
lower_case_table_names = 1
server-id = 137
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_data_file_path=ibdata1:512M:autoextend
default-storage-engine=INNODB
character-set-server=utf8mb4
general_log=0
general_log_file=/usr/loacl/mysql/log/general.log
pid-file=/usr/local/mysql/data/mysql.pid
slow-query-log
slow_query_log_file=/usr/local/mysql/log/slow.log
tmpdir=/usr/local/mysql/tmp
long_query_time=0.1
max-connections=2000
#开启二进制日志
log_bin=/usr/local/mysql/binlog/binlog
binlog_format=row
default_authentication_plugin=mysql_native_password
open_files_limit=65535
#開啟独立表空间模式
innodb_file_per_table = 1
#增加sort_buffer_size 来加速ORDER BY 或者GROUP BY 操作
sort_buffer_size = 1048576
#增加 max_length_for_sort_data 優化 Filesort 排序
max_length_for_sort_data = 8096
#加上这一行，设置时间
log_timestamps = SYSTEM
innodb_log_file_size=128M
innodb_log_files_in_group=3
innodb_log_group_home_dir=/usr/local/mysql/redo
innodb_buffer_pool_instances=8
innodb_buffer_pool_chunk_size=128M
innodb_buffer_pool_size=2048M
event_scheduler=1
[mysql]
no-auto-rehash
default-character-set=utf8mb4
prompt=\\u@\\h : \\d【\\r:\\m:\\s】\\c SQL->
- 初始化mysql ，它会生成一个临时密码
[root@XAG137 src]# /usr/local/servers/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql &
[1] 18851
[root@XAG137 ~]# tail -100f /usr/local/mysql/log/error.log
2019-09-19T07:01:02.649515Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-09-19T07:01:02.649566Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2019-09-19T07:01:02.649569Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
100 200 300 400 500
2019-09-19T07:01:03.221053Z 0 [Warning] InnoDB: New log files created, LSN=49320
2019-09-19T07:01:03.290330Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-09-19T07:01:03.389654Z 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: 3e7915f5-daab-11e9-ac9b-000c2929f2d3.
2019-09-19T07:01:03.392285Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-09-19T07:01:03.396016Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
- 启动
/usr/local/servers/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[root@XAG137 ~]# tail -20f /usr/local/mysql/log/error.log
2019-09-19T07:05:05.310722Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-09-19T07:05:05.317287Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-09-19T07:05:05.317537Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-09-19T07:05:05.372708Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-09-19T07:05:05.375784Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-09-19T07:05:05.375935Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-09-19T07:05:05.376708Z 0 [Note] InnoDB: Waiting for purge to start
2019-09-19T07:05:05.427514Z 0 [Note] InnoDB: 5.7.27 started; log sequence number 2540597
2019-09-19T07:05:05.429396Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql/data/ib_buffer_pool
2019-09-19T07:05:05.429713Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-09-19T07:05:05.436886Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190919 15:05:05
2019-09-19T07:05:05.487963Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-09-19T07:05:05.488108Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-09-19T07:05:05.489577Z 0 [Note] IPv6 is available.
2019-09-19T07:05:05.489658Z 0 [Note] - '::' resolves to '::';
2019-09-19T07:05:05.489707Z 0 [Note] Server socket created on IP: '::'.
2019-09-19T07:05:05.497413Z 0 [Note] Failed to start slave threads for channel ''
2019-09-19T07:05:05.505176Z 0 [Note] Event Scheduler: Loaded 0 events
2019-09-19T07:05:05.505507Z 0 [Note] /usr/local/servers/mysql/bin/mysqld: ready for connections.
Version: '5.7.27-log' socket: '/usr/local/mysql/tmp/mysql.socket' port: 3306 MySQL Community Server (GPL)
16登錄(OK)
/usr/local/servers/mysql/bin/mysql -uroot -p --socket=/usr/local/mysql/tmp/mysql.socket
#第一次登录修改root初始化密码
alter user 'root'@'localhost' identified with mysql_native_password by '123456' PASSWORD EXPIRE NEVER; #永不过期;
17 . 创建root@%
create user 'root'@'%' identified by '123456' PASSWORD EXPIRE NEVER;
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;
- 快捷登陆
#停mysql
/usr/local/servers/mysql/bin/mysqladmin -h127.0.0.1 -P 3306 -uroot -p'123456' shutdown &
#启动mysql
/usr/local/servers/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
or
/usr/local/servers/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --socket=/usr/local/mysql/tmp/mysql.socket &
#login
/usr/local/servers/mysql/bin/mysql -h127.0.0.1 -P 3306 -uroot -p
cat >>/root/.bashrc <<"EOF"
#
alias mysql.start="/usr/local/servers/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --socket=/usr/local/mysql/tmp/mysql.socket &"
alias mysql.stop="/usr/local/servers/mysql/bin/mysqladmin -h127.0.0.1 -P 3306 -uroot -p'123456' shutdown &"
alias mysql.login="/usr/local/servers/mysql/bin/mysql -h127.0.0.1 -P 3306 -uroot -p"
EOF
source /root/.bash_profile
- 启动脚本
#复制启动文件
[root@XAG8 mysql]# cp /usr/local/servers/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@XAG8 ~]# vim /etc/init.d/mysqld
修改部分：
basedir=/usr/local/servers/mysql
datadir=/usr/local/mysql/data
另外
bindir=/usr/local/servers/mysql/bin
sbindir=/usr/local/servers/mysql/bin
libexecdir=/usr/local/servers/mysql/bin
#启动脚本有两个，分别是 /usr/local/servers/mysql/bin/mysqld_safe
#和 /usr/local/servers/mysql/support-files/mysql.server（即/etc/init.d/mysqld）。
#当启动mysqld时，mysqld_safe同时启动，mysqld_safe监控mysqld服务,
#记录错误日志，并在mysqld因故障停止时将其重启
- 启动mysqld服务
#重啟
[root@XAG8 ~]# /etc/init.d/mysqld restart
若想设置开机启动：
[root@XAG8 ~]# chmod 755 /etc/init.d/mysqld ---增加执行权限
[root@XAG8 ~]# chkconfig --add mysqld ---加入自动启动项
[root@XAG8 ~]# chkconfig --level 345 mysqld on ---设置MySQL在345等级自动启动
[root@XAG8 ~]# service mysqld stop
[root@XAG8 ~]# service mysqld restart
[root@XAG8 ~]# service mysqld status
#启动mysql（方法2）
[root@XAG8 ]# /usr/local/servers/mysql/bin/mysqld_safe --user=mysql &
#检查mysql是否启动
[root@XAG8 ]# ps -ef|grep mysql
- 添加和调整mysql innodb log文件
mkdir -p /usr/local/mysql/redo
mkdir -p /usr/local/mysql/backup
chown -R mysql:mysql /usr/local/mysql/
#检查当前redo文件
root@127.0.0.1 : (none)【03:39:44】3 SQL->show variables like 'innodb%log%';
+----------------------------------+------------+
| Variable_name | Value |
+----------------------------------+------------+
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
+----------------------------------+------------+
15 rows in set (0.01 sec)
[root@XAG816 data]# cd /usr/local/mysql/data
[root@XAG816 data]# ls ib_logfile*
ib_logfile0 ib_logfile1
[root@XAG816 data]# service mysqld stop
Shutting down MySQL.... SUCCESS!
[root@XAG816 data]# mv /usr/local/mysql/data/ib_logfile* /usr/local/mysql/backup/
[root@XAG8 ~]# vim /etc/my.cnf
#add redo config [mysqld]下
innodb_log_file_size=128M
innodb_log_files_in_group=3
innodb_log_group_home_dir=/usr/local/mysql/redo
innodb_buffer_pool_instances=8
innodb_buffer_pool_chunk_size=128M
innodb_buffer_pool_size=2048M
root@127.0.0.1 : (none)【03:49:51】3 SQL->show variables like 'innodb%log%';
+----------------------------------+-----------------------+
| Variable_name | Value |
+----------------------------------+-----------------------+
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 134217728 |
| innodb_log_files_in_group | 3 |
| innodb_log_group_home_dir | /usr/local/mysql/redo |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
+----------------------------------+-----------------------+
root@127.0.0.1 : (none)【03:51:28】9 SQL->show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 2147483648 |
+-------------------------------------+----------------+
innodb_log_file_size : 日志组中的每个日志文件的大小(单位 字节)。如果 n 是日志组中日志文件的数目，
那么理想的数值为 1M 至下面设置的缓冲池(buffer pool)大小的 1/n。较大的值，可以减少刷新缓冲池的次数，
从而减少磁盘 I/O。但是大的日志文件意味着在崩溃时需要更长的时间来恢复数据。
日志文件总和必须小于 2 GB，3.23.55 和 4.0.9 以上为小于 4 GB。在 my.cnf 中以数字格式设置。
innodb_buffer_pool_size ：InnoDB 用来高速缓冲数据和索引内存缓冲大小。 更大的设置可以使访问数据时减少磁盘 I/O。
在一个专用的[数据库]服务器上可以将它设置为物理内存的 80 %。
不要将它设置太大，因为物理内存的使用竞争可能会影响操作系统的页面调用。在 my.cnf 中以数字格式设置。
innodb_buffer_pool_size设置为3G，innodb_buffer_pool_instances设置为8。innodb_buffer_pool_chunk_size默认值为128M。
3G是有效的innodb_buffer_pool_size值，因为3G是innodb_buffer_pool_instances = 8 * innodb_buffer_pool_chunk_size = 128M的整數倍
2. InnoDB Buffer Pool 主要配置参数
buffer pool主要的配置参数如下：
innodb_buffer_pool_size 指定buffer pool的大小，在有足够内存空间的情况下，尽可能提高该参数的大小，以提高性能表现。一般地，在只运行mysql的机器上，建议将该参数设置为物理内存空间的50%-75%。
innodb_buffer_pool_instances 该参数表示将buffer pool划分为若干个独立的缓存池实例。对于高并发的应用场景，将buffer pool划分为多个可以有效减少因为内存资源竞争带来的额外消耗。此参数只有在pool_size 参数设定大于1G的情况下指定才有效，假定将instances指定为N，则每个buffer pool占用的内存带下为size/N。在资源足够的情况下，建议通过组合pool size 与 instances 参数，保证每个独立缓存池的大小大于1G，以获得更佳的性能表现。
innodb_old_blocks_pct 指定buffer pool 老年代占用总空间的比例大小，取值范围为5-95，默认值为37（即3/8）
innodb_old_blocks_time 指定读入buffer pool的内存分页，当其被首次访问后，延迟old_blocks_time(ms)时间，移动至新生代。设定为0表示，读入buffer pool的内存分页会在第一次被访问后立即移动至新生代。设定为大于0的数，表示当内存分页被首次访问后，必须等待至少特定的时间才可以被移动至新生代。
将该参数设定为大于0，可以避免仅读取一次的查询过度占用buffer pool的新生代。那些仅被读取一次的内存分页会随着时间的推移，逐渐从老年代中移出。而对于需要进行buffer pool预热的情形，则建议将该参数设定为0，以保证读取的数据能够及时移动至新生代。该参数可以在运行时进行设置，对于那些需要全表扫描或者进行数据备份的场景，可以临时将该参数设定为较大的数值，以避免临时操作对buffer pool使用带来明显的占用和影响。
#如下所示， kiocb的第二、三列都不为0，表示ORACLE的异步IO特性已经启用
[oracle@mpdb ~]$ cat /proc/slabinfo | grep kio
kioctx 70 70 384 10 1 : tunables 54 27 8 : slabdata 7 7 0
kiocb 180 180 256 15 1 : tunables 120 60 8 : slabdata 12 12 0
#启用异步I/O
mysql> SHOW VARIABLES LIKE 'innodb_use_native_aio';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_use_native_aio | ON |
+-----------------------+-------+
| 序号 | RAM 缓 | innodb_buffer_pool_size緩中池大小（范围） |
|---|---|---|
| 1 | 4GB | 1GB~2GB |
| 2 | 8 GB | 4GB~6GB |
| 3 | 12 GB | 6GB~10GB |
| 4 | 16 GB | 10GB~12GB |
| 5 | 32 GB | 24GB~28GB |
| 6 | 64 GB | 45GB~56GB |
| 7 | 128 GB | 108GB~116GB |
| 8 | 256 GB | 220GB~245GB |
参考
https://www.cnblogs.com/bjx2020/p/10682518.html
网友评论