mysql数据库流程化安装 -m6米乐安卓版下载

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</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

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 = /mysql/data/mysql3306/socket/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
米乐app官网下载 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安装传家宝了