mysql问题持续更新

本人主要使用的系统是centos7.9
数据库主要使用的是mysql8.0.35
下载centos7.9.2009地址： https://mirrors.aliyun.com/centos/7/isos/x86_64/

https://mirrors.aliyun.com/centos/7/isos/x86_64/CentOS-7-x86_64-DVD-2009.iso?spm=a2c6h.25603864.0.0.74092d1ca0zzLC

下载mysql地址：https://dev.mysql.com/downloads/mysql/

wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.35-linux-glibc2.17-x86_64.tar

一、 mysql各类设置

1、设置mysql的忽略大小写

#放在[mysqld]下面，忽略大小写
lower_case_table_names = 1

2、设置mysql用户密码难度和定时过期

mysql8

#放在[mysqld]下面，忽略大小写
plugin-load=validate_password.so
# 设置密码策略要求
validate_password_length = 8
validate_password_mixed_case_count = 1
validate_password_number_count = 1
validate_password_special_char_count = 1
default_password_lifetime = 90

plugin-load=validate_password.so  #加载"validate_password.so" 的插件，用于检查密码是否符合特定的复杂性要求。
validate_password_length = 8  #用户在创建新密码时，密码的长度必须至少为8个字符。
validate_password_mixed_case_count = 1 #密码中必须至少有一个大写字母和一个或多个小写字母。这是为了确保密码的复杂性，防止密码过于简单。
validate_password_number_count = 1 #密码中必须至少有一个数字。这也是为了增加密码的复杂性。
validate_password_special_char_count = 1 #密码中必须至少有一个特殊字符（例如 !@#$%^&*()_+）。这是为了防止密码过于简单，增加密码的复杂性。
default_password_lifetime = 90 #密码的默认有效期为90天。这意味着在用户没有更改密码的情况下，密码将在90天后过期，用户需要重新设置。这可以帮助确保用户定期更改密码，从而增加系统的安全性。

mysql5.7

#放在[mysqld]下面，忽略大小写,不用加载插件
validate_password_length = 8
validate_password_mixed_case_count = 1
validate_password_number_count = 1
validate_password_special_char_count = 1
default_password_lifetime = 90

3、mysql导出数据，利用mysqldump工具

（1）需要输入密码
[root@ ~]# mysqldump -h 192.168.48.144 -P 13306 -uroot -p ceshi > /root/ceshi.sql
将数据库ceshi的数据导出到/root/ceshi.sql文件中。

-h 192.168.48.144：指定要连接的MySQL服务器的IP地址为192.168.48.144。
-P 13306：指定要连接的MySQL服务器的端口号为13306。
-uroot：指定连接MySQL服务器的用户名为root。
-p：提示输入连接MySQL服务器的密码。
ceshi：指定要导出数据的数据库名称为ceshi。
> /root/ceshi.sql：将导出的数据保存到/root/ceshi.sql文件中。

4、二进制安装后如何设置全局变量

[root@baseCentos7 support-files]# cp mysql.server /etc/init.d/mysql
[root@baseCentos7 support-files]# systemctl mysql restart
[root@baseCentos7 init.d]# chkconfig --add mysql
[root@baseCentos7 init.d]# chkconfig --level 345 mysql on
[root@baseCentos7 init.d]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL....... SUCCESS!

5、navicat如何实现定时备份

二、遇到问题

1、select command denied to user 'zabbix@192.168.48.1’for table user

use mysql;
select * from user where user='zabbix';
#将下面五个N修改为Y即可，根源是权限不够，缺少user的权限

2、安装zabbix时装不上，在zabbix_server.log中报错connection to database ‘zabbix’ failed: [2002] Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

解决方式：修改zabbix_server.conf文件中 DBSocket=/app/local/mysql8/mysql.sock

3、Authentication plugin ‘caching_sha2_password‘

mysql8之前的加密规则mysql_native_password，之后的加密规则是caching_sha2_password

#修改账户密码加密规则且更新用户密码
mysql> alter user 'root'@'%' identified by '123456' password expire never;
Query OK, 0 rows affected (0.00 sec)

mysql>  alter user 'root'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

三、常用sql

1、查询

1、查询数据库大小

use information_schema;
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='数据库名';

2、数据库导入导出

1、导出

1、windows

前提：需要安装mysql-workbench-community-8.0.12-winx64.msi软件

@echo off
setlocal


set "localhost_name=192.168.48.128"
set "username=root"
set "database_name=ceshi"
set "mypassword=123456"

set "max_backup_files=7*24"

set "max_backup_files_date=7"

set "backup_dir=C:\ceshi\databak\ceshi\"
set "YYYY=%date:~0,4%"
set "MM=%date:~5,2%"
set "DD=%date:~8,2%"

set "HH=%time:~0,2%"
set "NN=%time:~3,2%"
set "SS=%time:~6,2%"

set "datetime=%YYYY%%MM%%DD%%HH%%NN%%SS%"
set "backup_file=%backup_dir%\%database_name%-%datetime%.sql"

set "log_file=%backup_dir%\backup.log"
set "error_message=Backup failed at %datetime%"
set "success_message=Backup successful at %datetime%"


echo Starting backup at %datetime% >> "%log_file%"
C:
cd C:\Program Files\MySQL\MySQL Workbench 8.0 CE

mysqldump --extended-insert=false -h "%localhost_name%" -u "%username%" --password="%mypassword%" --databases %database_name% > "%backup_file%"


forfiles /p "%backup_dir%" /s /m %database_name%-*.sql /d -%max_backup_files_date% /c "cmd /c del /q @path"


setlocal enabledelayedexpansion
for /F "skip=%max_backup_files% delims=" %%F in ('dir /B /O:D "%backup_dir%\%database_name%*.sql"') do (
    set "file_to_delete=%backup_dir%\%%F"
    echo Deleting old backup: !file_to_delete!
    del "!file_to_delete!"
)

if %errorlevel% neq 0 (
    echo %error_message% >> "%log_file%"
) else (
    echo %success_message% >> "%log_file%"
)

echo Backup completed.

endlocal

2、linux

#!/bin/bash
 
# 定义属性
MYSQL_HOST="127.0.0.1"
 
MYSQL_PORT="13306"
 
MYSQL_USER="root"
 
MYSQL_PASSWORD="123456"
 
 
BACKUP=/root/backup_mysql
 
DATETIME=$(date +%Y%m%d%H%M%S)
 
# 打印日志
function log_correct () {
    USER=$(whoami)
    echo "${DATETIME} ${USER} execute $0 [INFO] $@ " >> "/root/shell/backup_log.txt" 
}
 
log_correct "开始执行 Mysql 备份任务"
 
# 自动获得所有的数据库
# 如果机器中没有加入 MySQL 环境，请将下面的 mysql 改为 mysql 文件的全路径
DATABASES=`mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -e "SHOW DATABASES;"`
# 创建备份目录
mkdir -p "${BACKUP}/$DATETIME"
 
for db in $DATABASES; do
    # 排除表头和一些无需备份的数据库
    if [[ "$db" != "Database" ]] && [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != "sys" ]] ; then
        # 备份操作
        # 如果机器中没有加入 MySQL 环境，请将下面的 mysqldump 改为 mysqldump 文件的全路径
        log_correct "备份: 【$db】"
        mysqldump -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD} --databases $db > ${BACKUP}/$DATETIME/$db.sql
        log_correct "数据库【$db】已备份到：${BACKUP}/$DATETIME/$db.sql 下"
    fi
done
 
# 删除7天前的文件
find $BACKUP -type f -mtime +7 -exec rm -f {} \;
 
log_correct "完成 Mysql 备份任务"