- 安装系统依赖包
[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
13.1 数据库目录规划
| 序号 | 文件类型 | 实例3306 | 软链 |
|---|---|---|---|
| 1 | datadir | /usr/local/mysql/data | /data/mysql/data |
| 2 | my.cnf | /usr/local/mysql/etc/my.cnf | - |
| 3 | log-error | /usr/local/mysql/log/error.log | - |
| 4 | log_bin | /usr/local/mysql/binlog/binlog | /data/mysql/binlog/binlog |
| 5 | slow_query_log_file | /usr/local/mysql/log/slow.log | - |
| 6 | socket | /usr/local/mysql/tmp/mysql.socket | - |
| 7 | pid-file | /usr/local/mysql/data/mysql.pid | - |
13.2 创建目录&授权
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
mkdir -p /usr/local/mysql/etc/
mkdir -p /usr/local/mysql/binlog/
touch /usr/local/mysql/log/error.log
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
删除系统自带的my.cnf
[root@xag200 ~]# rm -f /etc/my.cnf
[root@xag200 ~]# vim /usr/local/mysql/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,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
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
#设置为true时 数据行更新时，timestamp类型字段不更新为当前时间。
explicit_defaults_for_timestamp=true
[mysql]
no-auto-rehash
default-character-set=utf8mb4
prompt= "\\u@\\h:\\d [\\r:\\m:\\s] \\c SQL->"
---------------------------------------------------------------------------------
- 初始化mysql ，它会生成一个临时空密码
cd /usr/local/servers/mysql/bin/
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
or
#推荐
mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --initialize-insecure --user=mysql &
mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --initialize-insecure --user=mysql &
---------------------------------------------------------------------------------
[root@xag200 bin]# grep 'temporary password' /usr/local/mysql/log/error.log
[Note] A temporary password is generated for root@localhost:
oftbjy:rt84H
生成ssl (可选)
[root@xag200 bin]# pwd
/usr/local/servers/mysql/bin
[root@xag200 bin]#
mysql_ssl_rsa_setup --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
18.1 设置启动项
vim /usr/lib/systemd/system/mysqld.service
------------------------------------------------------
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/usr/local/mysql/data/mysql.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
#ExecStartPre=/usr/bin/mysqld_pre_systemd
# Start main service
ExecStart=/usr/local/servers/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/data/mysql.pid $MYSQLD_OPTS
# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
------------------------------------------------------
systemctl daemon-reload
systemctl enable mysqld.service
systemctl is-enabled mysqld
18.2 启动
systemctl start mysqld.service
or
/usr/local/servers/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/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.1 Securing the Initial MySQL Accounts
重置密码(上一步已经重置过了 这次可以忽略)
删除匿名用户
关闭root用户的远程登录
删除测试数据库
----------------------------
[root@xag200 bin]# pwd
/usr/local/servers/mysql/bin
[root@xag200 bin]# mysql_secure_installation
导入时区信息
[root@xag200 bin]# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
Enter password:
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
[root@xag200 bin]# /usr/local/servers/mysql/bin/mysql -h127.0.0.1 -P 3306 -uroot -p12345678
root@127.0.0.1:(none) [11:15:01] 2 SQL->use mysql;
root@127.0.0.1:mysql [11:15:13] 3 SQL->show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
...
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
...
+---------------------------+
31 rows in set (0.01 sec)
19.2 测试
[root@xag200 sysconfig]# mysqladmin version -uroot -p
Enter password:
mysqladmin Ver 8.42 Distrib 5.7.33, for linux-glibc2.12 on x86_64
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.7.33-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /usr/local/mysql/tmp/mysql.socket
Uptime: 13 min 18 sec
Threads: 3 Questions: 8730 Slow queries: 0 Opens: 120 Flush tables: 1 Open tables: 113 Queries per second avg: 10.939
查看变量
[root@xag200 sysconfig]# mysqladmin variables -uroot -p12345678
...
19.3 登錄(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 '12345678' PASSWORD EXPIRE NEVER;
20 . 创建root@%
root@localhost:mysql [11:25:32] 5 SQL->select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
create user 'root'@'%' identified by '12345678' PASSWORD EXPIRE NEVER;
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;
#創建查詢賬戶
create user 'zabbix'@'%' identified by 'Zab@1234' PASSWORD EXPIRE NEVER;
grant select on *.* to 'zabbix'@'%' with grant option;
flush privileges;
- 快捷登陆
#停mysql
/usr/local/servers/mysql/bin/mysqladmin -h127.0.0.1 -P 3306 -uroot -p'12345678' shutdown &
#启动mysql
/usr/local/servers/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf &
or
/usr/local/servers/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/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=/usr/local/mysql/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'12345678' shutdown &"
alias mysql.login="/usr/local/servers/mysql/bin/mysql -h127.0.0.1 -P 3306 -uroot -p"
EOF
source /root/.bash_profile
- 启动mysqld服务
设置开机启动(前面已配置)
systemctl enable mysqld.service
[root@xag200 ~]# systemctl list-unit-files mysqld.service
UNIT FILE STATE
mysqld.service enabled
[root@xag200 ~]# systemctl start mysqld.service
[root@xag200 ~]# systemctl status mysqld.service
...
Active: active (running)
-----------------------------------------------------------------------------
#检查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 |
+-----------------------+-------+
24.查看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 |
查询当前使用的my.cnf路径
yum -y install mlocate
[root@xag200 etc]# locate my.cnf
locate: can not stat () `/var/lib/mlocate/mlocate.db': No such file or directory
[root@xag200 etc]# updatedb
[root@xag200 etc]# locate my.cnf
/usr/local/mysql/etc/my.cnf
1.查看是否使用了指定目录的my.cnf
(无输出表示没有，如有则表示mysql启动时是加载了这个配置文件）
[root@xag200 etc]# ps aux|grep mysql|grep 'my.cnf'
无输出
2.查看mysql默认读取my.cnf的目录
[root@xag200 etc]# mysql --help|grep 'my.cnf'
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
~/.my.cnf
综上情况分析 mysql启动时是加载如下配置文件：
/usr/local/mysql/etc/my.cnf
网友评论