安装系统依赖包
[root@xag200 ~]# yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel libaio-devel perl libaio wget lrzsz vim libnuma* bzip2 xz
添加mysql 组和mysql 用户。所有的文件和目录应该在mysql 用户下
[root@xag200 ~]# groupadd mysql
[root@xag200 ~]# useradd -g mysql mysql -d /home/mysql -s /sbin/nologin
创建目录&授权
mkdir -p /usr/local/mysql/data
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@xag200 mysql]# sed -i '$a export PATH=$PATH:/usr/local/servers/mysql/bin' /etc/profile;
[root@xag200 mysql]# source /etc/profile
15.安裝位置
[root@xag200 ~]# cd /usr/local/src
[root@xag200 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
#解压安装包
[root@xag200 ~]# mkdir -p /usr/local/servers
[root@xag200 ~]# tar -zxvf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local/servers/
[root@xag200 ~]# mv /usr/local/servers/mysql-5.7.33-linux-glibc2.12-x86_64 /usr/local/servers/mysql
[root@xag200 ~]# chown mysql.mysql -R /usr/local/servers/mysql
配置參數my.cnf
[root@xag200 ~]# touch /usr/local/mysql/log/error.log
[root@xag200 ~]# mkdir -p /usr/local/mysql/binlog/
[root@xag200 ~]# chown mysql.mysql -R /usr/local/mysql/
[root@xag200 ~]# chown mysql.mysql -R /usr/local/servers/mysql
[root@xag200 ~]# 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 = 200
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/local/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=200
#开启二进制日志
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@xag200 src]#
/usr/local/servers/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql &
---------------------------------------------------------------------------------------------------------
。。。
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@xag200 ~]# tail -20f /usr/local/mysql/log/error.log
。。。
Version: '5.7.33-log' socket: '/usr/local/mysql/tmp/mysql.socket' port: 3306 MySQL Community Server (GPL)
19.登錄(OK)
/usr/local/servers/mysql/bin/mysql -S /usr/local/mysql/tmp/mysql.socket
or
/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;
20 . 创建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 <
#
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@xag200 mysql]# cp /usr/local/servers/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@xag200 ~]# 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@xag200 ~]# /etc/init.d/mysqld restart
若想设置开机启动:
[root@xag200 ~]# chmod 755 /etc/init.d/mysqld ---增加执行权限
[root@xag200 ~]# chkconfig --add mysqld ---加入自动启动项
[root@xag200 ~]# chkconfig --level 345 mysqld on ---设置MySQL在345等级自动启动
[root@xag200 ~]# service mysqld stop
[root@xag200 ~]# service mysqld restart
[root@xag200 ~]# service mysqld status
#启动mysql(方法2)
[root@xag200 ]# /usr/local/servers/mysql/bin/mysqld_safe --user=mysql &
#检查mysql是否启动
[root@xag200 ]# ps -ef|grep mysql
添加和调整mysql innodb log文件 ( 上面已調整 log,如其他人安裝的mysql 為調整的則如下方法調整)
#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@xag200 data]# cd /usr/local/mysql/data
[root@xag200 data]# ls ib_logfile*
ib_logfile0 ib_logfile1
[root@xag200 data]# service mysqld stop
Shutting down MySQL.... SUCCESS!
[root@xag200 data]# mv /usr/local/mysql/data/ib_logfile* /usr/local/mysql/backup/
[root@xag200 ~]# 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,表示异步IO特性已经启用 (centos7.5 未查到)
[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 |
+-----------------------+-------+
25.查看UUID (配置主从节点时,两节点的UUID 必须不同)
[root@xag200 data]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=09808e50-616c-11eb-b3c4-000c299e2e81
序号
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