2023.03.16 搭建测试环境数据库 && 数据库自动备份

安装Mysql

上传安装包

将Mysql安装包 拷贝到需要的服务器上：
或者本地上传，这个自己搞

scp -r mysql-8.0.31-linux-glibc2.12-x86_64.tar root@82.xxx.xxx.236:/opt/cpfile

创建目录

解压上传的安装包 并更改名称为 mysqlbase
创建目录：/opt/mysqlbase/

创建mysql用户 并添加到mysql用户组

查看linux是否存在mysql 用户组

cat /etc/group | grep mysql 
> mysql:x:1000:
或
groups mysql 
> mysql : mysql

添加用户组和用户

 groupadd mysql && useradd -r -g mysql mysql

创建数据目录 && 授权

mkdir /opt/mysqlbase/data
授权
chown mysql:mysql -R /opt/mysqlbase/data

上传配置文件 my.cnf

本地创建my.cnf文件 并 上传my.cnf文件到 /etc/my.cnf
注意：下方要写正确的目录地址。

[mysqld]
user=mysql
port = 3306
basedir = /opt/mysqlbase
datadir = /opt/mysqlbase/data

character-set-server=utf8
server-id = 1
max_connections = 5000
max_connect_errors = 10
table_open_cache = 4096
event_scheduler = ON
skip-name-resolve
lower_case_table_names = 1
group_concat_max_len = 102400

max_allowed_packet = 64M
binlog_cache_size = 8M

max_heap_table_size = 128M
read_rnd_buffer_size = 64M
sort_buffer_size = 64M
join_buffer_size = 128M
thread_cache_size = 64
log_bin_trust_function_creators = 1
key_buffer_size = 512M
read_buffer_size = 32M
read_rnd_buffer_size = 128M
bulk_insert_buffer_size = 512M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

######READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = READ-COMMITTED
tmp_table_size = 512M

log-bin = mysql-bin
slave-skip-errors = 1007,1008,1032,1050,1051,1054,1060,1061,1062,1068,1091,1146
relay_log_index=mysql-bin-relay
binlog_format=row
expire_logs_days = 15

slow_query_log = 1
slow_query_log_file = /opt/mysqlbase/data/slow.log
long_query_time = 1
log_timestamps=system

####### InnoDB
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 16
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 256M
innodb_log_files_in_group = 4
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_print_all_deadlocks = 1
#innodb_force_recovery=1

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash
 
[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M
 
[mysqlhotcopy]
interactive-timeout
 
[mysqld_safe]
open-files-limit = 65535
log-error=/opt/mysqlbase/data/mysqld.log
pid-file=/opt/mysqlbase/data/mysqld.pid

安装libaio

执行yum install -y libaio命令安装libaio包

初始化 数据库

使用mysqld 命令初始化数据库
进入目录 /opt/mysqlbase/bin
执行命令：
路径要改为自己服务器的路径

./mysqld --defaults-file=/etc/my.cnf --basedir=/opt/mysqlbase/ --datadir=/opt/mysqlbase/data/ --user=mysql --initialize

初始化之后 查看临时用户和密码：

查看临时用户密码：
一般初始化会控制台会将密码打印出来

如果未打印使用命令查看

查看数据库首次登录密码：cat /data/mysql/mysql.err
[root@localhost bin]# cat /data/mysql/mysql.err
2021-09-21