MySQL数据库流程化安装

发表于： 2023-08-23 09:09:38
分类：网络资源
浏览：浏览(104)

一、系统准备

1、RHEL关闭防火墙

systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service

2、关闭Selinux

##重启后生效
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
##重启后检查
getenforce

3、/etc/hosts解析（示例如下）

vim /etc/hostname
mysql8

vim /etc/hosts
192.168.11.11   mysql8

4、挂载Yum源

mount /dev/cdrom /mnt
mkdir /etc/yum.repos.d/bak -p
mv /etc/yum.repos.d/* /etc/yum.repos.d/bak
cat<<EOF>/etc/yum.repos.d/local.repo
[local]
name=local
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF

5、安装Yum包

yum -y install wget cmake gcc gcc-c++ numactl autoconf ncurses ncurses-devel libaio-devel openssl openssl-devel perl-devel perl-JSON.noarch vim-enhanced-7.4.160-5.el7.x86_64
yum -y install lvm2 rz vim

6、清理系统环境

Centos7或者RHEL7版本的系统默认自带安装了MariaDB，需要先清理

[root@mysql8 ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64
[root@mysql8 ~]# yum list installed | grep mariadb
mariadb-libs.x86_64                   1:5.5.52-1.el7                   @anaconda
[root@mysql8 ~]# yum -y remove mariadb-libs.x86_64
[root@mysql8 ~]# yum list installed | grep mariadb
[root@mysql8 ~]# rpm -qa |grep mariadb

7、确定时区与时间是否正确

[root@localhost tmp]# date
Fri Oct 21 23:59:28 CST 2022
[root@localhost tmp]# date +"%Y-%m-%d %H:%M:%S"
2022-10-21 23:53:35
[root@localhost tmp]# timedatectl | grep -i "Time zone"
       Time zone: Asia/Shanghai (CST, +0800)

如果不对，修改如下
[root@localhost ~]# timedatectl set-timezone Asia/Shanghai
[root@localhost ~]# date -s '2023-05-16 10:05:30'

8、设置Mysql系统账户资源限制

vim /etc/security/limits.conf
mysql soft nproc 2047
mysql hard nproc 16384
mysql soft nofile 1024
mysql hard nofile 65536

二、MySQL安装

1、创建数据库用户，创建实例所需目录

root 用户操作：
mkdir -p /mysql/app/mysql3306
mkdir -p /mysql/conf/
mkdir -p /mysql/data/mysql3306
mkdir -p /mysql/data/mysql3306/data/
mkdir -p /mysql/data/mysql3306/pid/
mkdir -p /mysql/data/mysql3306/socket/
mkdir -p /mysql/data/mysql3306/log/
mkdir -p /mysql/data/mysql3306/binlog/
mkdir -p /mysql/data/mysql3306/relaylog/
mkdir -p /mysql/data/mysql3306/slowlog/
mkdir -p /mysql/data/mysql3306/tmp/
mkdir -p /mysql/data/mysql3306/errlog/
mkdir -p /mysql/data/mysql3306/generallog/

检查系统原有的Mysql用户和组，删除mysql用户重新添加

userdel mysql
groupadd mysql
useradd -g mysql mysql
chown -R mysql:mysql /mysql
echo Mysql12# | passwd --stdin mysql

2、上传软件包并解压安装程序包

md5值验证，保证下载到的软件包无破损木马

md5sum mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
0bb9fd978d8b122d7846efc37884c0bb mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

cd /mysql/app/
tar xvf /soft/mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz -C /mysql/app/
mv /mysql/app/mysql-8.0.33-linux-glibc2.12-x86_64 /mysql/app/mysql8.0.33
chown -R mysql:mysql /soft
chown -R mysql:mysql /mysql
chown -R mysql:mysql /data
chown -R mysql:mysql /backup

3、配置Mysql用户环境变量

cat >> /home/mysql/.bash_profile << "EOF"
MYSQL_HOME=/mysql/app/mysql8.0.33
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin:/mysql/app/mysql-shell/bin:/mysql/app/mysqlrouter/bin
EOF
source /home/mysql/.bash_profile
which mysql

4、创建参数文件

计算方式

innodb_buffer_pool_size = 服务器内存 * 70% = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

su - mysql
vim /mysql/conf/my3306.cnf
[client]
socket = /data/mysql/3306/data/mysql.sock
port=3306
[mysqld]
#server configurationn
user = mysql
server_id = 100
port=3306
default_storage_engine= InnoDB
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,ARCHIIIVE,MEMORY
basedir=/mysql/app/mysql8.0.33
datadir=/mysql/data/mysql3306/data/
socket=/mysql/data/mysql3306/socket/mysql.sock
pid-file=/mysql/data/mysql3306/pid/mysqld.pid
max_allowed_packet = 128M
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
lower_case_table_names = 1
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
default-time_zone='+8:00'
authentication_policy=mysql_native_password   # 加此参数可远程登陆
# connection #
interactive_timeout = 300
wait_timeout = 300
lock_wait_timeout = 300
max_user_connections = 800
skip_name_resolve = 1
max_connections = 3000
max_connect_errors = 1000
# log settings #
slow_query_log = ON
long_query_time = 2
log_queries_not_using_indexes = 1
slow_query_log_file = /mysql/data/mysql3306/slowlog/slow3306.log
binlog_expire_logs_seconds = 604800  
binlog_gtid_simple_recovery = 1
sync_binlog = 1
binlog_format = ROW
log_error = /mysql/data/mysql3306/errlog/err3306.log
log_error_verbosity = 3
log_bin = /mysql/data/mysql3306/binlog/mysql_bin
log_bin_index = /mysql/data/mysql3306/binlog/mysql_binlog.index
general_log_file = /mysql/data/mysql3306/generallog/general.log
log_slow_admin_statements = 1
min_examined_row_limit = 100
log_throttle_queries_not_using_indexes = 1000
innodb_flush_log_at_trx_commit=1
log_timestamps = system
log_replica_updates=1
# innodb settings #
innodb_buffer_pool_size = 22G
innodb_buffer_pool_chunk_size = 128M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_redo_log_capacity = 200M
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_buffer_pool_dump_pct = 40
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size=40M
innodb_open_files = 4096

#单机模式不要添加#
# replication settings #
gtid_mode = ON
enforce_gtid_consistency = ON
plugin_dir=/mysql/app/mysql8.0.33/lib/plugin/

5、数据库初始化

su - mysql
mysqld  --defaults-file=/mysql/conf/my3306.cnf --initialize --user=mysql --basedir=/mysql/app/mysql8.0.33  --datadir=/mysql/data/mysql3306/data/

6、启动MySQL

mysqld_safe --defaults-file=/mysql/conf/my3306.cnf --user=mysql &

7、第一次登录MySQL

需要到 err3306.log 去查询临时生成密码

[mysql@mysql8 ~]$ cat /mysql/data/mysql3306/errlog/err3306.log | grep password
2022-09-19T08:24:49.938088Z 0 [Note] [MY-010309] [Server] Auto generated RSA key files through --sha256_password_auto_generate_rsa_keys are placed in data directory.
2022-09-19T08:24:49.938148Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
2022-09-19T08:24:49.941785Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: e5Xl>+apoP?g
2022-09-19T08:25:47.675052Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory.
2022-09-19T08:25:47.675084Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.

mysql -uroot -p'yyy=SB-3f/hf'  -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock
mysql> select version();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

8、修改用户密码

alter user root@'localhost' identified by 'Okdd3adacxaf##';

9、配置root可远程登陆

create user root@'%' identified by 'Okdd3adacxaf##';
grant all  privileges on *.* to root@'%'  with grant option;
flush privileges;

使用如下语句创建 root 用户是无法通过 navicat 等客户端登录的，由于从 MySQL8 开始，身份验证插件发生改变，默认的 “caching_sha2_password” 不允许远程登录，故需将此插件修改为 “mysql_native_password” 便可登录。

mysql> select user,host,plugin from mysql.user;  
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Okdd3adacxaf##';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

10、设置socket软连接

[mysql@node1 ~]$ ln -s /mysql/data/mysql3306/socket/mysql.sock /tmp/mysql.sock

[mysql@node1 ~]$ mysql -uroot -p'Okdd3adacxaf##'mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

三、设置开机自启动

1、设置配置

[root@node1 system]# 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=notify
Timeout=0
ExecStart=/mysql/app/mysql8.0.33/bin/mysqld --defaults-file=/mysql/conf/my3306.cnf $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILEE=65536
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false

2、执行命令让配置生效

systemctl daemon-reload

3、mysql服务相关命令

启动

systemctl start mysqld

关闭

systemctl stop mysqld

查看

systemctl status mysqld

4、设置MySQL为开机自启动

[root@node1 ~]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

四、备份

1、逻辑备份

crontab -e
0 1 * * * /backup/backup.sh > /tmp/backup_$(date +"\%Y-\%m-\%d").log

[root@mysql backup]# cat backup.sh
#!/bin/sh
# File: /data/backup/mysql_backup.sh
# Database info
DB_USER="root"
DB_PASS="Okdd3adacxaf##"
DB_HOST="192.168.11.11"
DB_PORT="3306"

BIN_DIR="/mysql/app/mysql8.0.33/bin"
BCK_DIR="/backup"
DATE=`date +%Y%m%d_%H`

#REMOTE_SERVER="remote_server_address"
#REMOTE_DIR="/db_backup/mysql"

# 创建备份目录
#if [ ! -d $BACKUP_DIR ]; then
#  mkdir -p $BACKUP_DIR
#fi

echo '开始备份。。。。。。。。。。。。。。'
$BIN_DIR/mysqldump -u$DB_USER -h$DB_HOST -p$DB_PASS -P$DB_PORT -F --flush-privileges --single-transaction  --max_allowed_packet=256M --set-gtid-purged=OFF -B lowdata -S /data/mysql/mysql3306/tmp/mysql3306.sock > $BCK_DIR/mysqlbackup_data_$DATE.sql;
echo '结束备份。。。。。。。。。。。。。。'

#上传备份文件到远程服务器
#scp $BACKUP_DIR/$DB_NAME-$(date +%Y%m%d).tar.gz $REMOTE_SERVER:$REMOTE_DIR

#删除过期文件
find /backup  -mtime +7 -name "*.sql"  -exec rm -f {} \;
find /backup  -mtime +7 -name "mysqlbackup_data_$DATE.sql"  -exec rm -f {} \;

这篇文章杂揉了强哥的MySQL数据库安装 + MySQL DBA精英实战课 + MySQL实战，写出来的一篇文章，感觉可以当我的MySQL安装传家宝了

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论