文先生的博客 求职，坐标深圳。（wenfh2020@126.com）

[数据库] mysql 常用命令配置

2021-03-17
database

记录 mysql 常用配置和命令。

1. 安装

1.1. server

  • mysql 5.6

    脚本一键安装。安装包默认是 5.6.22 版本，mysql 用户名和密码是 root，可自行修改脚本配置。

1
2
3
4

wget https://raw.githubusercontent.com/wenfh2020/shell/master/mysql/mysql_setup.sh
chmod +x mysql_setup.sh
./mysql_setup.sh
service mysqld start
  • mysql 5.7。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

cd /usr/local/src
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
rpm -ivh mysql57-community-release-el7-11.noarch.rpm
yum install -y mysql-server
systemctl start mysqld
# 开机启动。
systemctl enable mysqld
# 获取临时密码。
cat /var/log/mysqld.log|grep 'A temporary password'
# 登录修改密码。
mysql -u root -p
# 允许设置密码。
mysql> alter user user() identified by "root";
# 设置新密码。
mysql> update mysql.user set authentication_string=password('root') where user='root' and Host ='localhost';

1.2. mysqlclient

1
2
3
4
5

# centos
yum install mysql -y

# ubuntu
apt-get install mysql-client libmysqlclient-dev python3-dev

2. 主从配置

需要开通端口允许访问，或者关闭防火墙测试。

1
2

# centos7
systemctl stop firewalld.service
类型 IP
master 192.168.0.200
slave 192.168.0.201

2.1. master

  • 修改配置，然后重启。配置 my.cnf，填充同步日志（log-bin）和服务id（server_id）（唯一，随便填，这里使用 ip 最后数字）。
1
2
3
4
5
6

# find / -name 'my.cnf'
# /usr/local/mysql/my.cnf
# vim /usr/local/mysql/my.cnf
[mysqld]
log-bin=mysql-bin
server_id=200
  • 授权 slave，ip: 192.168.0.201，user: mytest，pwd: mytest。
1

GRANT REPLICATION SLAVE,FILE ON *.* TO 'mytest'@'192.168.0.201' IDENTIFIED BY 'mytest';
  • 查询 master 数据状态。
1
2
3
4
5
6

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 |      890 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
  • 配置 slave，重启 slave。参考下面章节。
  • 配置完成后，master 写入数据，查看 slave 同步状况。
1
2
3
4
5
6
7
8
9
10
11
12
13

# 创建测试数据库。
mysql> CREATE DATABASE IF NOT EXISTS mytest DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 创建测试表。
mysql> use mytest;
mysql> CREATE TABLE `test_async_mysql` (
    ->     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->     `value` varchar(32) NOT NULL,
    ->     PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

# 插入数据
mysql> insert into mytest.test_async_mysql (value) values ('hello world');

2.2. slave

  • 修改配置 my.cnf，然后重启。
1
2
3

[mysqld]
log-bin=mysql-bin
server_id=201
  • 设置 master 接入信息。
1

mysql> change master to master_host='192.168.0.200',master_user='mytest',master_password='mytest',master_log_file='mysql-bin.000009',master_log_pos=890;
  • 开启 slave 功能。
1

mysql> start slave;
  • 查看主从同步情况。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.200
                  Master_User: mytest
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 1055
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 448
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes （正常）
            Slave_SQL_Running: Yes （正常）

3. 慢日志

参考：Mysql数据库慢查询日志的使用

3.1. 配置

3.1.1. 修改配置文件

通过修改配置文件设置，永久生效。

  • 查找 mysql 配置文件。
1
2

find / -name 'my.cnf'
/usr/local/mysql/my.cnf
  • 修改配置文件内容。
1
2
3
4

# vim /usr/local/mysql/my.cnf
slow_query_log=ON
long_query_time=1
slow_query_log_file=/data/mysql/localhost-slow.log
  • 重新启动
1

service mysqld restart

3.1.2. 命令设置

通过命令设置，临时生效。

  • 查询慢日志设置。
1
2
3
4
5
6
7
8

mysql> show variables like 'slow%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_launch_time    | 2                              |
| slow_query_log      | OFF                            |
| slow_query_log_file | /data/mysql/localhost-slow.log |
+---------------------+--------------------------------+
  • 开启慢日志。
1
2
3
4
5
6
7

mysql> set global slow_query_log ='ON';
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
  • 显示慢日志超时时间。
1
2
3
4
5
6

mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
  • 设置慢日志超时时间。
1
2
3
4
5
6
7
8
9
10
11

# 设置。
mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

# 效果。
mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

3.2. 分析

3.2.1. 日志文件

1
2
3
4
5
6
7

mysql> select count(*) from mytest.test_async_mysql;
+----------+
| count(*) |
+----------+
|  3117853 |
+----------+
1 row in set (31.03 sec)

1
2
3
4
5
6
7

➜  src more /data/mysql/localhost-slow.log
...
# Time: 210319 16:24:12
# User@Host: root[root] @ localhost []  Id:    50
# Query_time: 31.034545  Lock_time: 0.000166 Rows_sent: 1  Rows_examined: 3117853
SET timestamp=1616142252;
select count(*) from mytest.test_async_mysql;

3.2.2. mysqldumpslow

参考：mysql慢日志 :slow query log 分析数据

1
2
3
4
5
6
7
8
9

mysqldumpslow /data/mysql/localhost-slow.log


Reading mysql slow query log from /data/mysql/localhost-slow.log
Count: 3  Time=301.42s (904s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[127.0.0.1]
  update mytest.test_async_mysql set value = 'S' where id = N

Count: 1  Time=31.03s (31s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select count(*) from mytest.test_async_mysql

4. explain 优化

参考：【MySQL优化】——看懂explain

1
2
3
4
5
6

mysql> explain select count(*) from mytest.test_async_mysql;
+----+-------------+------------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+------------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_async_mysql | index | NULL          | PRIMARY | 4       | NULL | 2743104 | Using index |
+----+-------------+------------------+-------+---------------+---------+---------+------+---------+-------------+

5. 功能

5.1. 远程访问

允许 mytest 用户远程访问。

1
2
3
4

mysql> mysql -uroot -proot;
mysql> use mysql;
mysql> update user set host = '%' where user ='mytest';
mysql> flush privileges;

5.2. 连接情况

  • 当前所有连接。show PROCESSLIST / show full PROCESSLIST。
1
2
3
4
5
6
7
8

mysql> show PROCESSLIST;
+------+------+-----------------+--------+---------+------+----------+------------------+
| Id   | User | Host            | db     | Command | Time | State    | Info             |
+------+------+-----------------+--------+---------+------+----------+------------------+
|  945 | root | 127.0.0.1:24046 | mytest | Query   |    0 | starting | show PROCESSLIST |
| 1387 | root | 127.0.0.1:35109 | mysql  | Sleep   |  816 |          | NULL             |
| 1388 | root | 127.0.0.1:35111 | mysql  | Sleep   |  816 |          | NULL             |
...
  • 查看最大连接数。
1
2
3
4
5
6

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
  • 设置最大连接数。
1
2
3
4
5
6
7
8
9

mysql> set GLOBAL max_connections = 200;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+
  • 当前使用连接数。
1
2
3
4
5
6

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 102   |
+----------------------+-------+

6. 命令

6.1. 数据库操作

  • 展示数据库。
1

show databases;
  • 建库。
1

CREATE DATABASE IF NOT EXISTS mytest DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
  • 选择数据库。
1

use mytest

6.2. 表操作

  • 展示数据库表。
1

show tables;
  • 建表。
1
2
3
4
5

CREATE TABLE `test_async_mysql` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `value` varchar(32) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
  • 查表详情。
1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT 
    COLUMN_NAME, 
    COLUMN_TYPE, 
    IS_NULLABLE, 
    COLUMN_KEY, 
    COLUMN_DEFAULT, 
    EXTRA, 
    COLUMN_COMMENT 
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_NAME = 'user_setting_info'
    AND TABLE_SCHEMA = 'db_user';
  • 改表。
1
2
3
4
5
6
7
8
9

-- 添加字段
ALTER TABLE `group_info`
CHANGE COLUMN `city_id` `group_city_id` int NOT NULL DEFAULT 0 COMMENT '群组城市 ID',
CHANGE COLUMN `city_name` `group_city_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '群组城市名称';

-- 修改字段
ALTER TABLE `group_info`
CHANGE COLUMN `city_id` `group_city_id` int NOT NULL DEFAULT 0 COMMENT '城市 ID',
CHANGE COLUMN `city_name` `group_city_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '城市名称';

6.3. 数据操作

  • 查数据。
1

select value from mytest.test_async_mysql where id = 1;
  • 插入数据。
1

insert into mytest.test_async_mysql (value) values ('hello world');
  • 改数据。
1

update mytest.test_async_mysql set value = 'hello world 2' where id = 1;
  • 删除数据。
1

delete from mytest.test_async_mysql where id = 1;

7. demo

简单读取用户数据库表，向黑名单表插入数据。

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
40
41
42
43
44
45
46
47
48
49
50
51

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // 连接数据库
    db, err := sql.Open("mysql", "root:mima123456@tcp(192.168.1.81:3306)/db_user")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 检查数据库连接
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    // 查询 user_info 表的 user_id
    rows, err := db.Query("select user_id from user_info")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    // 循环处理查询结果
    for rows.Next() {
        var userID int
        if err := rows.Scan(&userID); err != nil {
            log.Fatal(err)
        }

        // 插入数据到 user_black_list 表
        _, err := db.Exec("insert into user_black_list (user_id, black_list) values (?, ?)", userID, "[]")
        if err != nil {
            log.Fatal(err)
        }
        fmt.Printf("Inserted user_id %d into user_black_list\n", userID)
    }

    // 检查是否有错误
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
}

8. 参考

作者公众号
微信公众号，干货持续更新~
目录

我的链接

文集

归档