Mysql运维

• mysql8修改登陆host: update user set host='%' where user='root';
  host还原为localhost: update user set host='localhost' where user='root';
  Aa123456!
• ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '设置的密码';
• 创建用户
  CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
  授予权限：
  GRANT ALL PRIVILEGES ON . TO 'test'@'localhost' WITH GRANT OPTION;
  注意：WITH GRANT OPTION 是可选的，它允许用户将权限授予其他用户。如果您不希望该用户能够授予权限，则可以省略此部分。
  刷新操作，立刻生效
  FLUSH PRIVILEGES;

Centos6安装二进制MySQL5.7

由于centos6的yum源停更,索性安装个二进制的mysql5.7

• 1.下载mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz
  2. 创建一个mysql用户和组
  groupadd mysql
  useradd -r -g mysql -s /bin/false mysql
  3. cd /usr/local
  tar zxvf /path/to/mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz
  ln -s full-path-to-mysql-VERSION-OS mysql
  export PATH=$PATH:/usr/local/mysql/bin
  tips: bin/mysqld --initialize --user=mysql #会产生随机密码
  bin/mysqld --initialize-insecure --user=mysql #不会产生密码,也就是空密码.
  4. cd /usr/local/mysql/etc
  [root@iZ2ze0qgk4uiaovdokqk9uZ etc]# cat my.cnf
  [mysqld]
  basedir=/usr/local/mysql
  datadir=/usr/local/mysql/data
  port = 3307
  user=mysql
  其他参数复制的原rds上的参数.
  5.进行初始化 bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf
  --initialize-insecure --user=mysql
  追加:如果后期追加了其他参数,数据库启动不了,可能还需要进行初始化;感觉还是yum安装的好.
  6.启动mysql: mysqld_safe &
  也可以使用:/usr/local/mysql/support-files/mysql.server start,因为此命令会调用mysqld_safe
  7.连接后，使用ALTER USER语句来分配新的root密码:
  ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';
  或使用:update user set authentication_string=PASSWORD("hahaha") where user='root';
  8.mysql> use mysql;
  update user set host = '%' where user = 'root';
  9.查看用户
  select user,host from mysql.user;

总结:之前程序用的是rds-mysql,从阿里云上迁自建数据库,最好把原来的参数一块迁过来,使用数据同步,虽然慢,但是能保证源表和目标之间数据相同,否则程序就是不认自建的Mysql.

2024年5月3号开始追加

错误日志

二进制日志

二进制日志(BINLOG)记录了所有的DDL (数据定义语言)语句和DML (数据操纵语言)语句,但不包括数据查询(SELECT, SHOW)语句。

• 二进制日志格式
  
  默认是Row格式,可以修改SET GLOBAL binlog_format = 'statement';,。全局变量对所有新建立的连接有效，但不会影响已经存在的连接。也可以修改当前会话链接

SET binlog_format = 'statement';`,如果永久变更,需要在配置文件中添加`
[mysqld]
binlog_format=ROW

• 二进制日志查看
  
  查看cat binlog.index看当前新的二进制文件是哪个,比如当前是binlog.000017
  比如想看test数据库中的二进制mysqlbinlog -d test -v binlog.000017
  ,从中就能看出最近执行的命令(查询除外)
  
• 二进制日志的删除
  
  在mysql中执行截图中命令,可以手动删除.
  Mysql中也设置了二进制日志的过期自动删除,通过show VARIABLES like '%binlog_expire%';查看过期时间为30天.
• 查询日志
  查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。如果需要开启查询日志,可以设置以下配置:
  show VARIABLES like '%general%';查询查询日志的当前设置,可以临时全局修改(不支持当前会后)
  SET GLOBAL general_log = 1;
  自定义查询日志的name
  SET GLOBAL GENERAL_LOG_FILE='/var/lib/mysql/mysql_query.log'
  如果需要永久修改,按截图所示:
  
  如果不用可以关闭查询日志.

慢日志

查询慢日志,默认是关闭状态show VARIABLES like '%slow_query_log%'

额外补充知识点:在数据库中按照表中行数进行降序给各个表排序,好快速知道那些表行数最多

SELECT 
    table_schema AS 'Database',
    table_name AS 'Table',
    table_rows AS 'Number of Rows'
FROM 
    information_schema.TABLES 
WHERE 
    table_schema = 'js_*_dev_db' -- 替换为你的数据库名
ORDER BY 
    table_rows DESC;

---

开启慢日志操作:在/etc/my.cnf中[mysqld]下添加

slow_query_log = 1 #启用慢查询日志。
slow_query_log_file=/var/lib/mysql/slow-log-file.log
long_query_time = 2 #设置慢查询的时间阈值
log_queries_not_using_indexes = 1 #记录未使用索引的查询。
#log_slow_admin_statements=1 #记录执行较慢的管理语句
#管理语句通常指的是那些影响服务器配置或元数据的命令，如analyze table、optimize table、alter table、analyze index 等。这些命令通常不是用于查询数据的，而是用于维护或更改数据库结构或性能。

• 更改自增ID的起始
  mysql表中删除了旧数据,但是ID是自增的依然很大,通过alter table 表名 auto_increment=当前ID最大值+1;,比如原先表ID最大为1000,删除了500条,还剩500条,那`alter table 表名 auto_increment=501',也即是后来新增的数据ID将会从501开始.