linux7.X二进制方式安装MySQL5.7.X

标签：log linux7 二进制 MySQL5.7 -- innodb mysql 3306 data

概述

系统环境

服务器型号	VMware Virtual Platform
操作系统版本	RHEL 7.6 x86_64
主机名	linuxpg51
Pub IP	192.168.115.51
数据库安装版本	mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper
安装路径规划	[mysql@linuxpg51 /]$ tree -L 1 /mysql /mysql ├── app ├── backup ├── data └── log

安装前准备

操作系统环境

[root@linuxpg51 mysql]# uname -a
Linux linuxpg51 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
[root@linuxpg51 mysql]# cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core)

修改hosts

[root@linuxpg51 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.115.51 linuxpg51

配置yum

1.挂载镜像至本地
mount -o loop /iso/CentOS-7.6-x86_64.iso /mnt

2.移动系统原yum仓库.repo配置文件
mkdir /etc/yum.repos.d/yum_old
mv /etc/yum.repos.d/* /etc/yum.repos.d/yum_old

3.编辑本地yum仓库指向文件
#cd /etc/yum.repos.d/
#vim yum.repo        ##文件命名以.repo结尾
##内容：
[source]
#仓库名称
name=localserver
#仓库描述
baseurl=file:///mnt
#仓库地址
gpgcheck=0
#检查软件是否为官方软件(0代表否，1代表是)
enabled=1
#此仓库在系统中是否生效(可不写，默认为1)
gpgkey=file:///mnt/RPM-GPG-KEY-CentOS-7

4.刷新仓库
yum clean all #刷新仓库配置
yum repolist all #报告yum仓库的状态

5.设置开机启动(自动挂载)
vim /etc/fstab   文件追加：
/iso/CentOS-7.6-x86_64.iso   /mnt   iso9660   ro      0 0

检查要求的软件包

rpm -q --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" \
perl-DBD-MySQL.x86_64 \
perl-Digest-MD5.x86_64 \
numactl.x86_64 \
libaio.x86_64 | grep "not installed\|未安装"

安装缺失包
# yum install <pkg1> <pkg2> -y

创建用户

# groupadd mysql
# useradd -r -g mysql -d /home/mysql -m mysql

注意：mysql官方文档是禁止操作系统mysql用户登陆的，但是生产环境root权限太大不符合实际。
# groupadd mysql
# useradd -r -g mysql -s /bin/false mysql

检查防火墙

在RHEL 7中，防火墙firewalld取代了iptables
# systemctl status firewalld
如果firewalld是运行状态，则关闭iptables服务
# systemctl stop firewalld
# systemctl disable firewalld
# systemctl is-enabled firewalld

# systemctl stop iptables
# systemctl disable iptables
# systemctl is-enabled iptables

关闭SELINUX

# /usr/sbin/sestatus -v
如果selinux为enable状态，则修改/etc/selinux/config文件：
SELINUX=disabled
并重启服务器

控制资源分配限制

echo "session required pam_limits.so" >> /etc/pam.d/login
cat /etc/pam.d/login

修改资源限制参数

vi /etc/security/limits.conf
mysql soft nofile 131072 
mysql hard nofile 131072 
mysql soft core 1024000
mysql hard core 1024000
mysql hard nproc unlimited
mysql soft nproc unlimited
mysql hard stack unlimited
mysql soft stack unlimited

禁用Transparent HugePages和numa

在grub文件的GRUB_CMDLINE_LINUX项加入transparent_hugepage=never 和 numa=off
# vi /etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="crashkernel=512M rd.lvm.lv=rhel/root rd.lvm.lv=rhel/swap rd.lvm.lv=rhel/usr rhgb quiet transparent_hugepage=never numa=off"
GRUB_DISABLE_RECOVERY="true"
重新生成grub配置文件
# grub2-mkconfig -o /boot/grub2/grub.cfg
重启主机，检查
# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
# [root@paas-0220602 ~]# dmesg | grep -i numa
[    0.000000] Command line: BOOT_IMAGE=/boot/vmlinuz-3.10.0-957.el7.x86_64 root=UUID=ce4017c2-343c-450c-b7cc-196c398ec56e ro crashkernel=auto rhgb quiet numa=off transparent_hugepage=never
[    0.000000] NUMA turned off
[    0.000000] Kernel command line: BOOT_IMAGE=/boot/vmlinuz-3.10.0-957.el7.x86_64 root=UUID=ce4017c2-343c-450c-b7cc-196c398ec56e ro crashkernel=auto rhgb quiet numa=off transparent_hugepage=never

禁用avahi-daemon服务

# systemctl stop avahi-daemon
# systemctl disable avahi-daemon
# systemctl status avahi-daemon

设置IO调度

sas:deadline
ssd:noop
echo deadline > /sys/block/$sdx/queue/scheduler （$sdx为你的数据目录盘）
[root@paas-0220602 vdb]# dmesg | grep -i scheduler
[    1.097380] io scheduler noop registered
[    1.097383] io scheduler deadline registered (default)
[    1.097409] io scheduler cfq registered
[    1.097413] io scheduler mq-deadline registered
[    1.097416] io scheduler kyber registered

设置内核参数

向/etc/sysctl.conf中添加或修改如下参数：
vm.swappiness=10	--(物理内存剩余百分之10后开始使用虚拟内存)
vm.min_free_kbytes=51200  --(最小空闲内存限制,生产环境建议设置524288k=512M)  --测试环境(50M)

执行以下命令生效
# /sbin/sysctl -p

安装建库

规划MySQL相关路径（软件安装、数据文件、备份文件、日志）

安装目录：/mysql/app/mysql
数据目录：/mysql/data
日志目录：/mysql/log
备份目录：/mysql/backup

mkdir -p /mysql/app
mkdir -p /mysql/log
mkdir -p /mysql/data
mkdir -p /mysql/backup
mkdir -p /mysql/data/3306/data
mkdir -p /mysql/log/3306
chown -R mysql:mysql /mysql

安装MySQL软件

MySQL下载地址（软件归档）

[root@linuxpg51 soft]# cd /soft/
[root@linuxpg51 soft]# tar -zxvf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /mysql/app/mysql
mysql/                               mysql-5.7.30-linux-glibc2.12-x86_64/ 
[root@linuxpg51 soft]# tar -zxvf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /mysql/app/

ln -s mysql-5.7.25-linux-glibc2.12-x86_64 mysql
root@linuxpg51 soft]# cd /mysql/app/
[root@linuxpg51 app]# ls
mysql-5.7.30-linux-glibc2.12-x86_64
[root@linuxpg51 app]# ln -s mysql-5.7.30-linux-glibc2.12-x86_64/ mysql	(做个软连接方便后期软件升级)
[root@linuxpg51 app]# ls -l
total 0
lrwxrwxrwx 1 root root  36 Sep  7 10:21 mysql -> mysql-5.7.30-linux-glibc2.12-x86_64/
drwxr-xr-x 9 root root 129 Sep  7 10:15 mysql-5.7.30-linux-glibc2.12-x86_64

卸载自带mysql

--检查
# rpm -qa |grep mysql
*mysql*
--卸载：
# rpm -e *mysql*

配置环境变量

配置root和mysql用户的环境变量，在PATH中增加mysql的路径
--将MySQL软件的bin路径写入环境变量中
[mysql@linuxpg51 ~]$ cat ~/.bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/mysql/app/mysql/bin
export PATH

--生效：
[mysql@linuxpg51 ~]$ source ~/.bash_profile 

--验证：
[mysql@linuxpg51 ~]$ mysql --version
mysql  Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using  EditLine wrapper
[mysql@linuxpg51 ~]$ which mysql
/mysql/app/mysql/bin/mysql

root用户自行配置此处略

准备参数文件

vi /mysql/data/3306/my.cnf

/etc/my.cnf    ---- rhel
/etc/mysql/my.cnf  ---- debain


[client]
port=3306
socket = /mysql/data/3306/mysql.sock

[mysql]
no-beep
prompt="\u@mysqldb \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8

[mysqld]
########basic settings########
lower_case_table_names=1
server-id=3306
port=3306
user = mysql
bind_address= 0.0.0.0
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket = /mysql/data/3306/mysql.sock
pid-file = /mysql/data/3306/mysql.pid
character-set-server=utf8
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
#sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

#query_cache_type = 1
#query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M

back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000

########log settings########
log-output=FILE
general_log = 0 
general_log_file=/mysql/log/3306/studydb-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/studydb-query.err
long_query_time=10
log-error=/mysql/log/3306/studydb-error.err

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
#binlog_expire_logs_seconds=2592000
expire_logs_days = 7
min_examined_row_limit = 100
log_bin=/mysql/log/3306/binlog/studydb-binlog
log_bin_index=/mysql/log/3306/binlog/studydb-binlog.index
binlog_format='ROW'
binlog_rows_query_log_events=on

########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#log_bin = bin.log
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
#binlog_format = row 
#relay_log = relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors

########innodb settings########
#根据服务器IOPS能力适当调整
#一般配普通SSD盘的话,可以适当调整到10000-20000
#配置高端PCIE SSD,则可以调整的更高,比如50000-80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT

innodb_log_file_size = 200M
innodb_log_files_in_group = 2 
innodb_log_buffer_size = 16M

#innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G

innodb_flush_neighbors = 1
innodb_purge_threads = 4
#innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G

innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
default_authentication_plugin=mysql_native_password
#default_authentication_plugin=caching_sha2_password

my.cnf配置文件各个参数详解请自行检索

初始化MySQL

# init databases -- my.cnf 每台机都不同
rm -rf /mysql/data/3306/data/*
/mysql/app/mysql/bin/mysqld  --defaults-file=/mysql/data/3306/my.cnf  --initialize --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data
ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sock

初始化数据库会提供初始密码，需注意保存，后续用于登录以及改密码：
2023-09-10T16:04:31.112830+08:00 1 [Note] A temporary password is generated for root@localhost: wq:qhj&<k6YR

定制化参数需要提前创建相关路径并设置正确权限，否则初始化Aborting。

配置MySQL服务脚本

[root@linuxpg51 init.d]# cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/
[root@linuxpg51 init.d]# vi /etc/init.d/mysql.server
--脚本相关路径需根据实际情况自行调整：
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data

# Set some defaults
mysqld_pid_file_path=/mysql/data/3306/mysql.pid
if test -z "$basedir"
then
  basedir=/mysql/app/mysql
  bindir=/mysql/app/mysql/bin
  if test -z "$datadir"
  then
    datadir=/mysql/data/3306/data
  fi
  sbindir=/mysql/app/mysql/bin
  libexecdir=/mysql/app/mysql/bin
  
# may be overwritten at next upgrade.
  $bindir/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &


启动MySQL
# service mysql.server start

[mysql@linuxpg51 ~]$ ps -ef |grep mysql
mysql     14528  14236  0 16:04 pts/1    00:00:00 tail -f studydb-error.err
mysql     16811      1  0 16:34 pts/0    00:00:00 /bin/sh /mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --datadir=/mysql/data/3306/data --pid-file=/mysql/data/3306/mysql.pid
mysql     17956  16811  0 16:34 pts/0    00:00:03 /mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3306/my.cnf --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data --plugin-dir=/mysql/app/mysql/lib/plugin --log-error=/mysq/log/3306/studydb-error.err --open-files-limit=65536 --pid-file=/mysql/data/3306/mysql.pid --socket=/mysql/data/3306/mysql.sock --port=3306
mysql     18868  14154  0 16:48 pts/0    00:00:00 ps -ef
mysql     18869  14154  0 16:48 pts/0    00:00:00 grep --color=auto mysql

手动编写启动脚本

[root@performance 3306]# pwd
/mysql/data/3306
[root@performance 3306]# cat mysql.start 
/bin/sh /mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --user=mysql &

手动停止
mysqladmin -uroot -p shutdown -S  /mysql/data/3306/mysql.sock

修改root口令

[mysql@linuxpg51 ~]$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.30-log

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@mysqldb 16:50:  [(none)]> status
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
root@mysqldb 16:50:  [(none)]> alter user 'root'@'localhost' identified by 'mysql';
Query OK, 0 rows affected (0.00 sec)
或者
set password = PASSWORD('rootroot');

远程连接测试

1.创建一个数据库
create database testdb;

2.创建用户与分配权限
grant all privileges on *.* to 'testu'@'%' identified by 'mysql' with grant option;
flush privileges;
3.检查用户
select host,user from mysql.`user`;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | testu         |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4.创建表和数据插入
create table EMP (
EMPNO int(4) not null,
ENAME varchar(10),
JOB varchar(9),
MGR int(4),
HIREDATE date,
SAL int(7 ),
COMM int(7 ),
DEPTNO int(2)
);
alter table EMP add constraint PK_EMP primary key (EMPNO);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369, 'SMITH', 'CLERK', 7902, str_to_date('17-12-1980', '%d-%m-%Y'), 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7499, 'ALLEN', 'SALESMAN', 7698, str_to_date('20-02-1981', '%d-%m-%Y'), 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7521, 'WARD', 'SALESMAN', 7698, str_to_date('22-02-1981', '%d-%m-%Y'), 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7566, 'JONES', 'MANAGER', 7839, str_to_date('02-04-1981', '%d-%m-%Y'), 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7654, 'MARTIN', 'SALESMAN', 7698, str_to_date('28-09-1981', '%d-%m-%Y'), 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7698, 'BLAKE', 'MANAGER', 7839, str_to_date('01-05-1981', '%d-%m-%Y'), 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7782, 'CLARK', 'MANAGER', 7839, str_to_date('09-06-1981', '%d-%m-%Y'), 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7788, 'SCOTT', 'ANALYST', 7566, str_to_date('19-04-1987', '%d-%m-%Y'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7839, 'KING', 'PRESIDENT', null, str_to_date('17-11-1981', '%d-%m-%Y'), 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7844, 'TURNER', 'SALESMAN', 7698, str_to_date('08-09-1981', '%d-%m-%Y'), 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7876, 'ADAMS', 'CLERK', 7788, str_to_date('23-05-1987', '%d-%m-%Y'), 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7900, 'JAMES', 'CLERK', 7698, str_to_date('03-12-1981', '%d-%m-%Y'), 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7902, 'FORD', 'ANALYST', 7566, str_to_date('03-12-1981', '%d-%m-%Y'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7934, 'MILLER', 'CLERK', 7782, str_to_date('23-01-1982', '%d-%m-%Y'), 1300, null, 10);
commit;

linux7.X二进制方式安装MySQL5.7.X_linux

linux7.X二进制方式安装MySQL5.7.X_linux_02

至此，linux7.X二进制方式安装MySQL5.7.X测试完成。

标签：log,linux7,二进制,MySQL5.7,--,innodb,mysql,3306,data
From： https://blog.51cto.com/u_11585528/7427273