0%

MySQL使用技巧

发表于 更新于 分类于 Disqus：
本文字数： 4.7k 阅读时长 ≈ 8 分钟

MySQL使用技巧

mysql 修改密码

方法1： 用SET PASSWORD命令

首先登录MySQL。
格式：mysql> set password for 用户名@localhost = password(‘新密码’);
例子：mysql> set password for root@localhost = password(‘1234’);

方法2：用mysqladmin

格式：mysqladmin -u用户名 -p旧密码 password 新密码
例子：mysqladmin -uroot -p123456 password 123

方法3：用UPDATE直接编辑user表

首先登录MySQL。
mysql> use mysql;
mysql> update user set password=password(‘1234’) where user=’root’ and host=’localhost’;
mysql> flush privileges;

方法4：在忘记root密码的时候，可以这样

以windows为例：

  1. 关闭正在运行的MySQL服务。
  2. 打开DOS窗口，转到mysql\bin目录。
  3. 输入mysqld –skip-grant-tables 回车。–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
  4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。
  5. 输入mysql回车，如果成功，将出现MySQL提示符 >。
  6. 连接权限数据库： use mysql; 。
  7. 改密码：update user set password=password(“123”) where user=”root”;（别忘了最后加分号） 。
  8. 刷新权限（必须步骤）：flush privileges;　。
  9. 退出 quit。
  10. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

mysql 内部变量

  • 查询数据库存储路径： SHOW VARIABLES WHERE Variable_name LIKE '%datadir%';

初始化 Mysql

在更换 mysql 数据目录 datadir=~/mysql后，可通过如下命令初始化 mysql 数据目录。

1
2
3
4
5
# mysql-5.5
scripts/mysql_install_db --user=root --datadir=/Volumes/databases

# mysql-5.7
bin/mysqld --initialize-insecure

正则获取表名

1
2
3
4
5
6
7
8
SELECT 
    table_name, 
		table_rows
FROM
    information_schema.tables
WHERE
    table_schema = 'api' and table_name REGEXP '^oss_photo_[0-9]*$'
ORDER BY table_rows desc;

批量 kill mysql sql

直接执行 kill 操作是不优雅的，会有业务问题。

事由： Alter table 引发的惨案。

遇到的情况是对业务日志处理，表大小： 100GB+ 记录条数：7kw+ ，Mysql 版本是：5.7.22-log 。

  1. 在执行新增字段时导致了锁表。
  2. 锁表后因还有此表相关 Insert into SQL 执行，导致阻塞了 Mysql 所有的线程。显示用户正在运行的线程： show processlist; 或者 select * from information_schema.processlist;， 若要查看完成 SQL 可通过 show full processlist 命令，这里提示一点只有 root 用户能查询到所有的线程，其他用户只能查看到自己的线程。
  3. 相关业务受影响，服务端无法响应。
解决方案：
  1. 暂停日志记录。
  2. kill 掉所有与日志表相关的 Insert into SQL。

通过以下命令查询表 <Table-Name> 相关的SQL。

1
select * from information_schema.PROCESSLIST where Info like 'INSERT INTO <Table-Name>%';

  • 按客户端 IP 分组，看哪个客户端的链接数最多

    1
    select client_ip,count(client_ip) as client_num from (select substring_index(host, ':', 1) as client_ip from processlist ) as connect_info group by client_ip order by client_num desc;

  • 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

    1
    select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

  • 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面kill

    1
    select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
参考

MySQL Online DDL

Online DDL: MySQL >= 5.6.7。

1
alter table t_base_user modify telephone varchar(50), ALGORITHM=INPLACE, LOCK=NONE;

LOCK 类型

  1. LOCK=EXCLUSIVE : 表示独占锁，DDL语句执行期间会阻塞该表的所有请求。
  2. LOCK=SHARED：共享锁，DDL语句执行期间会阻塞除查询外的所有DML操作，如: insert，update等。
  3. LOCK=NONE: 允许所有查询以及DML操作。
  4. LOCK=DEFAULT 默认级别，MySQL尽可能允许最大的并发操作。

当我们不显示指定时，默认就为LOCK=DEFAULT类型。

注：不是所有的DDL操作LOCK类型都可以指定为NONE的。如果DDL操作的类型，不能以请求的锁定类型执行，则会执行失败。

参考

MySQL 生成测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE TABLE `test` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `user_id` VARCHAR (20) NOT NULL,
    `group_id` INT (11) NOT NULL,
    `create_time` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

delimiter $$
CREATE FUNCTION rand_string(n int) RETURNS varchar(255) 
begin        
  declare chars_str varchar(100) 
  default "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
  declare return_str varchar(255) default "";        
  declare i int default 0;
  while i < n do        
      set return_str=concat(return_str,substring(chars_str,floor(1+rand()*62),1));
      set i= i+1;        
  end while;        
  return return_str;    
end $$
delimiter ;
delimiter $$

CREATE  PROCEDURE `insert_data`(IN n int)
BEGIN  
  DECLARE i INT DEFAULT 1;
    WHILE (i <= n ) DO
      INSERT into test (user_id,group_id,create_time ) VALUEs (rand_string(20),FLOOR(RAND() * 100) ,now() );
            set i=i+1;
    END WHILE;
END $$
delimiter ;

call insert_data(1000000);

# 快速创建其他数据表并插入数据
create table test_myisam engine=myisam as select * from test;
参考

MySQL 8.0 安装完成后配置

  1. 查找 root 密码。

    1
    2
    $ grep "A temporary password" /var/log/mysqld.log
    2021-01-11T03:35:29.613489Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: d+CGdmfw;5/!

  2. 设置 root 为简单密码。

    注意：设置成简单免密，只为了提高本地开发的便利性，若是修改线上服务器 root 账号密码，还是要设置个复杂点的密码以提高安全性。

    1
    2
    3
    4
    set GLOBAL validate_password.policy=0;
    SET GLOBAL validate_password.length=4;

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';

  3. 开启外网访问权限。

1
2
3
4
5
6
7
8
9
10
11
# 开启外网访问
use mysql;
# 更新域属性，'%'表示允许外部访问：
update user set host='%' where user ='root';
# FLUSH 的作用
# 将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。
# MySQL用户数据和权限有修改后，希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令。
FLUSH PRIVILEGES;

# 授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
参考