mysql的常用操作

1.数据库查询数据库大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

2.查询数据库的连接数

show status like 'Threads%'; 

3.查询mysql服务器CPU过高

• https://www.cnblogs.com/wcm19910616/p/11057880.html

-- 列出前100条 mysql运行的线程
show processlist;

-- 列出全部线程
show full processlist;

4.修改所有表字符集为utf8mb4

• 博客：https://www.jianshu.com/p/4510c8e2e1e2

• 执行如下SQL创建函数

DELIMITER $$

CREATE PROCEDURE UP_CHANGE_UTF8MB4()
  COMMENT '将当前数据库中所有表的字符集转换成utf8mb4'
BEGIN
  DECLARE $i INT;
  DECLARE $cnt INT;
  DECLARE $NAME VARCHAR(64);

  #创建临时表，代替游标
  DROP TABLE IF EXISTS tmp_Table_name;
  CREATE TEMPORARY TABLE tmp_Table_name (
    id INT NOT NULL AUTO_INCREMENT,
    table_name VARCHAR(64) NOT NULL,
    PRIMARY KEY (`id`)
  );

  # 插入要处理的表名到临时表中
  INSERT INTO tmp_Table_name (table_name)
    SELECT
      table_name
    FROM information_schema.`TABLES`
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA = DATABASE();

  #循环处理每一张表，改表的字符集
  SET $i = 1;
  SELECT
    COUNT(1) INTO $cnt
  FROM tmp_Table_name;
  WHILE $i <= $cnt DO
    SELECT
      table_name INTO $NAME
    FROM tmp_Table_name
    WHERE id = $i;
    
   SET @asql = CONCAT('ALTER TABLE ', $NAME, '  CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; ');
    PREPARE asql FROM @asql;
    EXECUTE asql;
    
    SET @asql = CONCAT('ALTER TABLE ', $NAME, ' CONVERT TO CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; ');
    PREPARE asql FROM @asql;
    SELECT @asql;
    EXECUTE asql;

    SET $i = $i + 1;
  END WHILE;
  DEALLOCATE PREPARE asql;
  DROP TABLE tmp_Table_name;
END$$
DELIMITER ;

5.关于批量修改数据库字段

• 对数据库表中的某个字段做检索和替换

-- 使用update批量替换某个字段
UPDATE `table_name` SET `field_name` = REPLACE ( `field_name`, 'https://web.a.com', 'https://web.b.com' ) WHERE field_name LIKE '%https://web.a.com%';

UPDATE  `表`  SET `字段`  =  REPLACE (`字段`,'字符串','替换为的字符串' )  where  `字段`  LIKE  '匹配规则'

6.mysql阅读二进制日志内容

# 使用mysqlbinlog命令读取二进制日志
$ mysqlbinlog --no-defaults mysql-bin.000001   >  binlog.log

# 过滤信息
$ sed -nr '/#210625  9:45:00/,/#210625  9:45:59/p'  binlog.log   >  9_45.log

binlog_format=row

-- 验证相关参数
binlog_format=row
binlog_row_image=minimal
binlog_rows_query_log_events=on
 
 
-- 结果总结
binlog_format=row
01：直接用"mysqlbinlog PATH/二进制日志文件"来查看二进制日志文件。
02：binlog日志文件中对于DML数据操纵语言是加密的(需要解密才能看到)
03：binlog日志文件中对于DDL数据定义语言是明文的(也就是可以直接看到)
 
binlog_row_image=minimal
01：用"mysqlbinlog --base64-output=decode-row -vv PATH/二进制日志文件"来查看二进制日志文件;
02：binlog日志文件中对于DML数据操作语言insert;只记录在哪些字段上插入了数据；
03：binlog日志文件中对于DML数据操作语言update;不会记录更新前的数据,只会记录更新了的字段的值;
04：binlog日志文件中对于DML数据操作语言delete;不会记录删除前的数据; 
 
 
binlog_rows_query_log_events=on
01：用"mysqlbinlog --base64-output=decode-row -vv PATH/二进制日志文件"来查看二进制日志文件。
02：binlog日志文件中对于DML数据操纵语言,会显示具体执行的SQL语句。
03：我们在mysql中用show binlog events in "二进制日志文件";命令可以看到DDL语句，也可以看到DML语句。

binlog_format=row

mysqlbinlog --no-defaults --base64-output=decode-row -vv mysql-bin.000007 

7.关于mysql授权相关

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '123456';
flush privileges;

-- 授权视图权限
GRANT Select, Show View, Trigger ON TABLE `databases_name`.`view_name` TO `user_name`@`%`;

-- 查询所有视图
show table status where comment='view';

8.数据库报错

8.1.mysql用户登陆报错

报错信息：

linux ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

报错原因：

账户：'root'@'localhost'  的密码错误导致的

处理方法：

修改mysql配置文件,添加如下配置，重启数据库
[mysqld]
skip-grant-tables

然后可以直接使用root账户登录，然后去修改root账户密码
set password for root@localhost=password('你的密码');