mysql常用操作及常见问题

常用操作

mysql备份：

--整库备份(排除mysql、information_schema等库)
mysql -e "show databases;" -uroot -p| grep -Ev "Database|information_schema|performance_schema|mysql|test" | xargs mysqldump -uroot -p --databases > /opt/mysql_bak.sql
--指定库备份
docker exec 容器ID mysqldump -uroot -p密码 --databases 库名 > 库名.sql
--仅导出表和数据
mysqldump -h localhost -uroot -p123456  database table > dump.sql
--导出整个数据库结构（不包含数据）
mysqldump -h localhost -uroot -p123456  -d database > dump.sql
--导出单个数据表结构（不包含数据）
mysqldump -h localhost -uroot -p123456  -d database table > dump.sql
--导出存储过程和函数
docker exec 容器ID mysqldump -R -ndt 库名 -u root -p123456 > test.sql
--导入sql时指定utf-8字符集
mysql -u root -p123456 --default-character-set=utf8 databasename < xxx.sql

 

--创建用户：
create user 'username'@'%' IDENTIFIED BY 'password';
--创建库：
create database if not exists database_name default character set = 'utf8';
--给用户赋权：
GRANT all privileges ON database_name.* to 'username'@'%';
GRANT Alter, Create, Delete, Index, Insert, Select, Update ON *.* TO `username`@`%`;
--修改用户密码：
ALTER USER 'root'@'%' IDENTIFIED BY 'mysql,.1q';
update user set password=password('123') where user='root' and host='localhost';
--5.7版本修改密码【5.7版本密码字段是authentication_string】
update user set authentication_string = password('root'), password_expired = 'N', password_last_changed = now() where user = 'root';
 --取消授权：
revoke all on database_name.* from username@'%';
revoke all on *.* from username@'%';
--查看用户权限：
show grants for username;

 

 注意：在操作 mysql 库做用户的增删修改的时候，操作完毕的时候最好使用 flush privileges 命令刷新一下权限。否则可能会修改不生效

 

Mysql设置最大连接数

# 查看当前连接数
mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 83    |
| Threads_created   | 53896 |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

# 临时设置最大连接数
set GLOBAL max_connections=100;

# 永久设置【数据库重启后生效】
[root@localhost config]# cat mysql.conf.d/mysqld.cnf
[mysqld]
max_connections=5000

 

常见问题：

1、表名查询不区分大小写

在my.cnf或my.ini配置文件中[mysqld]添加如下内容

lower_case_table_names=1

然后重启mysql服务

 

日志记录

1、开启日志记录

show variables like 'general_log';  -- 查看日志是否开启

show variables like 'log_output';  -- 看看日志输出类型  table或file

show variables like 'general_log_file';  -- 看看日志文件保存位置

set global general_log_file='tmp/general.lg'; -- 设置日志文件保存位置

set global general_log=ON/OFF; -- 开启/关闭日志功能

set global log_output='table'; -- 设置输出类型为 table

set global log_output='file';   -- 设置输出类型为file

2、查询

SELECT * from mysql.general_log ORDER BY event_time DESC;

3、清空表（delete对于这个表，不允许使用，只能用truncate）

truncate table mysql.general_log;

慢查询

show variables like "%slow%"; //查看慢查询设置

set slow_query_log='ON';      //启用慢查询

set global long_query_time=2; //设置成2秒，加上global,下次进mysql已然生效

 导出用户权限脚本

#!/bin/bash
#Function export user privileges

pwd=123456
expgrants()
{
  mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}
expgrants > ./grants.sql

[root@mysql ]# ./exp_grant.sh

 查询某个数据库表大小

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE (data_length / 1024 / 1024, 2) AS '数据容量(MB)',
    TRUNCATE (index_length / 1024 / 1024, 2) AS '索引容量(MB)'
FROM
    information_schema. TABLES
WHERE
    table_schema = 'dbname' -- 数据库名字
ORDER BY
    table_rows DESC;

 

导入sql报错：Invalid default value for 'time'

出现问题原因：MySQL5.7版本之后,date, datetime类型设置默认值"0000-00-00",出现异常:Invalid default value for 'time'

解决办法：在mysql配置文件[mysqld]下添加如下配置【NO_ZERO_IN_DATE, NO_ZERO_DATE两个选项禁止了0000这样的日期和时间】

sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

关闭mysql dns反向解析

mysql默认状态会自动反向解析,使用show processlist查看发现有大量的unauthenticated user提示。
根据mysql官方网站得知，这是特殊设定。不管链接的的方式是经过 hosts 或是 IP 的模式，它都会对 DNS 做反查 mysqld 会尝试去反查 IP -> dns ，由于反查解析过慢，就无法应付过量的查询。

# 在mysql配置文件中添加以下配置
[mysqld]
skip-name-resolve

# 重启数据库服务后查看是否生效
show variables like "%skip%";

 

定时器创建

# 从2021年12月6日开始每晚10点执行update语句
CREATE DEFINER=`root`@`%` EVENT `update_status` ON SCHEDULE EVERY 1 DAY STARTS '2021-12-06 22:00:00' ON COMPLETION NOT PRESERVE ENABLE DO UPDATE business_register_request b,
sys_user u,
sys_organization o 
SET b.STATUS = '2',
u.STATUS = '0',
o.STATUS = '0' 
WHERE
  u.id = b.register_user_id 
  AND u.org_id = o.id 
  AND b.STATUS = '1' 
  AND u.STATUS = '2' 
  AND o.STATUS = '2'