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

这个不会。

已于 2023-03-24 11:11:08 修改

阅读量336

点赞数

分类专栏：学习文章标签：数据库 mysql linux

于 2023-03-17 16:19:59 首次发布

本文链接：https://blog.csdn.net/BaD_Guy__/article/details/129584987

版权

学习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

安装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-21T15:32:28.547395Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.16) initializing of server in progress as process 43232
2021-09-21T15:32:35.938131Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ;;ghjew%&1eZ
2021-09-21T15:32:37.923324Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.16) initializing of server has completed

用户名：密码
root@localhost: ;;ghjew%&1eZ

启动数据库

第一种启动方式：
将安装目录的mysql.server 复制到init.d目录下

cp /opt/mysqlbase/support-files/mysql.server /etc/init.d/mysql

启动数据库

/etc/init.d/mysql start

第二种启动方式：

先将mysql的bin目录配置成全局可用的:
编辑/etc/profile文件
vim /etc/profile
在profile末尾加上export PATH=$PATH:/usr/local/mysql/bin
使设置生效
source /etc/profile
注意：如果此时报错说明设置有问题，重新检查设置

在这里插入图片描述

mysqld  --defaults-file=/etc/my.cnf  &
ps -ef|grep mysql查看MySQL服务是否启动，如下图所示表示mysql已经启动

在这里插入图片描述

登陆数据库

使用 mysql 命令登陆数据库并修改密码
进入/opt/mysqlbase/bin

mysql -u root -p

输入上方临时密码，后进入界面修改密码

=======================可能会遇到的问题====================================
mysql -u root -p登录数据库，
此时可能汇报command not find 
[root@localhost init.d]# mysql -u root -p
bash: mysql: command not found...
Packages providing this file are:
'mariadb'
'mysql'
解决办法：ln -s /usr/local/mysql/bin/mysql /usr/bin
=======================可能会遇到的问题====================================

登录mysql时可能会遇到如下问题mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
[root@localhost init.d]# mysql -u root -p
mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
[root@localhost init.d]# 
解决办法：
ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
=======================可能会遇到的问题====================================
登陆后首次执行命令汇报这个：
You must reset your password using ALTER USER statement before executing this statement.
解决办法：修改密码！！！

=========================正常情况↓==================================


[root@VM-16-12-centos mysqlbase]# mysql -u root -p
Enter password: //输入密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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.

`第一步修改密码，否则不能进行任何操作。`
`注释：修改root用户密码 为xxx`
mysql> alter user user() identified by "xxx";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


`注释：切换数据库 mysql`
mysql> use mysql;
Database changed
`注释：查看用户的 host访问权限，localhost为本地访问权限，如果需要使用远程访问需要改成 "%" `
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)
`注释：修改 root的host，改为%，以便远程访问`
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

`注释：查看用户的 host访问权限`
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)


mysql> quit
Bye

修改完成后使用远程工具进行链接，如果不好用重启一下数据库

/etc/init.d/mysql restart

安装完成

必然成功！

重启数据库

进入 /opt/mysqlbase/support-files

./mysql.server restart

在这里插入图片描述

数据库每天自动备份

要求：每天凌晨两天自动备份

创建备份目录

创建备份目录：

mkdir /opt/mysql_back

上传数据库备份脚本并给脚本文件授权

本地创建脚本 mysql_back.sh 上传到/opt/mysql_back
给脚本文件授权：chmod +x ./mysql_backup.sh
注意：数据库的路径要写正确！！
mysql_back.sh 内容如下：

#!/bin/bash
###backup info
db_host=127.0.0.1
user=ecology
passwd=Weaver.2022
keepdate=`date +%Y%m%d --date="-7 day"`
expirdate=`date +%Y%m%d --date="-15 day"`

####MySQL info
backdate=`date +%Y%m%d`
datadir="/opt/mysql_back"
backdir="${datadir}/${backdate}"
mysqldir="/opt/mysqlbase/bin/mysql"
dumpdir="/opt/mysqlbase/bin/mysqldump"
errlog="${datadir}/mysqldump.log"
backlog="${datadir}/back_info.log"

####check backup directory && backup all database
test ! -d "${backdir}" && mkdir  "${backdir}"

for db_list in `${mysqldir} -u${user} -p${passwd} -h${db_host} -e 'show databases'|egrep -v "Database|information_schema|performance_schema|mysql|sys"`   
  do
     ${dumpdir} -h${db_host} -u${user} -p${passwd} --default-character-set=utf8 --single-transaction --master-data=2 --set-gtid-purged=OFF -f -R ${db_list} --log-error=${errlog}  > ${backdir}/${db_list}.sql
 done

####handle backupfile 
cd ${datadir}
tar zcvf ${backdate}.tar.gz ${backdate}
rm -rf ${backdir}

####backup info

echo 'backup date: '`date "+%Y-%m-%d %H:%m:%S"` >> ${backlog}

if [ ! -f "${keepdate}.tar.gz" ];then
echo "old backup not found !" >> ${backlog}

else
if [ `du -k ${keepdate}.tar.gz |awk '{print $1}'` -gt 102400 ]
then
rm -rf ${expirdate}.tar.gz
else
echo 'backup file size is less than 100M, please check if the backup is successful!' >> ${backlog}
fi
fi

####send mail
#[ -s ${errlog} ] && /bin/mail -s "DB Backup Error on MySQL Server" -a ${errlog} test1@mail.com,test2@mail.com < ${backlog}

if [ -s ${errlog} ];then
echo 'back not compalete or error!'
else
echo 'backup successd!' >> ${backlog}

echo "###############################################"
echo ""
echo 'backup successd !!! '
echo ""
echo "###############################################"

fi

cat ${errlog} >> ${errlog}.old
rm -rf ${errlog}

测试脚本

上传之后手动调用一下，看看会不会出现问题

sh ./mysql_back.sh

在这里插入图片描述

定时执行数据库备份脚本

定时任务需要使用到 crontab 组件的支持
crontab介绍：crontab命令常见于Unix和类Unix的操作系统之中，用于设置周期性被执行的指令
添加计划任务，检测或安装 crontab，执行crontab命令如果报 command not found，就表明没有安装。
安装命令如下：

yum -y install vixie-cron
yum -y install crontabs

crontab 绑定数据库备份脚本

启动服务 : service crond start

检测cron定时服务是否自启用： systemctl is-enabled crond.service

enable表示已启用自启动
disable标识未启用自启动
如果未启用，则开启cron自启用： systemctl enable crond.service
如果已经启用，想要cron关闭自启动：systemctl disable crond.service

添加计划任务

添加计划任务（linux编辑器必知必会（:wq））
使用命令：

添加计划任务： crontab -e

在这里插入图片描述

每天凌晨3点运行此文件，对数据库进行备份
00 02 * * * /opt/mysql_back/mysql_back.sh
前面是分钟后面是小时，控制台回显“crontab：installing new crontab” 表示添加调度任务成功。
添加或者修改任务后需要重启定时器
systemctl restart crond.service

查看crond状态是否开启 service crond status

查看定时任务列表：crontab -l
（可以设置一分钟执行一次，查看效果 */1 * * * * /opt/mysql_back/mysql_back.sh，用crontab -e 编辑的任务在crontab: installing new crontab后三分钟之内是不会执行的，所以你在测试的时候，至少时间后推三分钟！）
*/3 * * * * /opt/mysql_back/mysql_back.sh

其它问题

navicat 链接数据库 1251问题

在这里插入图片描述

出现原因：是mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password
解决办法：把mysql用户登录密码加密规则还原成mysql_native_password.

登陆mysql

` 修改用户的密码&加密规则 。xxx = 新密码 `
mysql> ALTER USER 'root' IDENTIFIED WITH mysql_native_password BY 'xxx';
Query OK, 0 rows affected (0.00 sec)

` 改完刷新数据库`
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)