Mysql Tip - Mysql常见问题s(持续更新)

2018/05/28 共 1861 字 5 分钟阅读
DATABASE mysql tips

AI 摘要: 本文主要介绍了编码规范和SQL操作，包括数据库规范指南和从现网数据库导出记录到测试库的操作。

1. 编码规范

1.1. 数据库规范指南

https://www.sqlstyle.guide/#general-1

2. SQL 操作

2.1. 从现网数据库导出符合指定 SQL 查询条件的记录，导入到测试库中

1
2
3
4
5
6
-- 正式库导出指定结算日期虚拟单
mysqldump -h DB_HOST_ONLINE -u DB_USER -pPASSWD ONLINE_DB TABLE \
--single-transaction --skip-add-drop-table --no-create-info --where="f_settle_date = 1682870400 and f_settle_state = 0" > virtual_order_data.sql

-- 测试导入
mysql -h DB_HOST_TEST -u DB_USER -pPASSWD TEST_DB < virtual_order_data.sql

2.2. 导出到 CSV

2.2.1. 使用 mysqldump 命令工具

mysqldump 支持导出单张表或多张表、单个库、所有库几种模式，导出的文件：

something.sql：包含DROP TABLE语句
something.txt：以tab分隔的文件

更改输出的格式，默认-T会导出以tab分隔的文件，可以通过--fields-terminated-by=,(逗号做字段分隔)、--fields-enclosed-by="(双引号包裹字段)：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// 导出csv文件
- `-p`：指示要输入密码
- `-T`：指定导出路径

 -T, --tab=name      Create tab-separated textfile for each table to given
                      path. (Create .sql and .txt files.) NOTE: This only works
                      if mysqldump is run on the same machine as the mysqld
                      server.
 -t, --no-create-info
                      Don't write table creation info.

mysqldump -u [username] -p -t -T /path/to/directory [database] --fields-enclosed-by=" --fields-terminated-by=,

"1","foo1","bar","2017-12-15 04:20:43"
"2","foo2","baz","2017-12-15 04:20:43"
"3","foo3","bat","2017-12-15 04:20:43"

tips：分隔指定和LOAD DATA一致：[TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char']

 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
// 导出
shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

// 载入
LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

2.2.2. 使用 SELECT…INTO OUTFILE

注意，该方式会导出数据写入到 Mysql 服务所在的机器上，参考： https://electrictoolbox.com/mysql-export-data-csv/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
/* Add column headers */
SELECT 'OrderId','CustomerID','EmployeeID','OrderDate','RequiredDate','ShippedDate','ShipVia','Freight','ShipName','ShipAddress','ShipCity','ShipRegion','ShipPostalCode','ShipCountry','OrderID','ProductId','UnitPrice','Quantity','Discount'

UNION ALL

/* Now the actual query */
SELECT o.OrderId, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, o.ShipCity, o.ShipRegion, o.ShipPostalCode, o.ShipCountry, od.OrderID, od.ProductId, od.UnitPrice, od.Quantity, od.Discount

FROM `Orders` o  LEFT JOIN `Order Details` od ON od.OrderID = o.OrderID

/* Save the query results to a file */
INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

2.3. CASE WHEN xx=xx THEN yy END

1
2
3
4
5
6
7
select from_unixtime(f_settle_date, "%Y-%m-%d"), f_order_source,
CASE
WHEN f_order_source=101 THEN "奖励"
WHEN f_order_source=102 THEN "惩罚"
WHEN f_order_source between 120 and 200 THEN "人工虚拟单"
end as virtualName
, count(*) as cnt from user_order where f_settle_date > unix_timestamp("2022-02-01") and f_order_source >=100 and f_order_source<=200  group by f_settle_date, f_order_source

2.4. SHOW 命令

1
2
3
4
5
6
7
8
// 查询表索引
show index from tb1

// 查询表创建语句
show create table tb1

// 查看表字段
desc tabl1

3. 服务运维

3.1. 主从配置参考

3.2. binlog 介绍

mysql-binlog 参考: https://www.cnblogs.com/rickiyang/p/13841811.html

3.3. Docker 内 Mysql 客户端连接相关问题

docker 安装 mysql 客户端

1
2
3
4
5
// 安装客户端
adk add mariadb-client

// 基于busybox安装telnet，调试远程端口开放
apk add busybox-extras

3.4. .my.cnf 配置多个 client

.my.cnf 配置多个 client，方便快速连接操作

3.4.1. 配置多个客户端选项

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 # 默认客户端配置
[client]
host=localhost
port=3306
user=root
password=test_admin
database=test_db

 # 客户端A
[client-A]
host=192.168.10.20
port=3306
user=root
password=test_admin
database=test_db

 # 客户端B
[cleint-B]
host=10.40.10.20
port=3306
user=root
password=n
database=test_db

3.4.2. alias 执行

1
2
echo alias mysql-a='mysql --defaults-group-suffix=-A' >> ~/.bashrc
echo alias mysql-b='mysql --defaults-group-suffix=-B' >> ~/.bashrc

3.5. Mysql 用户创建

参考：https://dev.mysql.com/doc/refman/8.0/en/create-user.html#create-user-authentication

3.5.1. 显示帮助信息，注意 auth_option 选项

1
2
3
4
5
6
7
8
9
mysql> help create user;
Name: 'CREATE USER'
...
auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

3.5.2. 查看默认认证插件

1
2
3
4
5
6
7
mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.10 sec)

3.5.3. 查看当前 Mysql 服务的相关账户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select concat(User,'@',Host) from mysql.user;
+----------------------------+
| concat(User,'@',Host)      |
+----------------------------+
| root@%                     |
| test@%                     |
| mysql.infoschema@localhost |
| mysql.session@localhost    |
| mysql.sys@localhost        |
| root@localhost             |
+----------------------------+
7 rows in set (0.01 sec)

3.5.4. 账户创建 SQL

帐户名的主机名部分（如果省略）默认为’%’。
user 命名帐户的每个值后面都可以有一个可选 auth_option 值，该值指示帐户的身份验证方式。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 创建账户，所有IP可以连入，采用默认的认证插件(default_authentication_plugin)
CREATE UESR 'clark'@'%' IDENTIFIED BY 'auth_string';
// 等价于
CREATE USER 'clark'@'%' IDENTIFIED WITH 'caching_sha2_password'

// 创建原生账户密码支持
create user metabase IDENTIFIED WITH mysql_native_password by 'metabase';

// 查看账户插件情况情况
mysql> show create user test\G;
*************************** 1. row ***************************
CREATE USER for test@%: CREATE USER 'test'@'%' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.02 sec)

// 更改账户认证插件
mysql> ALTER USER 'test'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'heyman';

// 更改用户名
mysql> rename user oldname@'%' to newname@'%';

// 全部授权
mysql> grant all on *.* to newname@'%';

3.6. 修改 Mysql 密码

参考： https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-password-management

1
2
3
4
5
6
1. 通过alter user操作，推荐
alter user root@'%' identified by 'YouPassword';

2. 通过set-password操作，需要flush privileges操作，不推荐
set password root@'%' = 'YouPassword';
flush privileges;

3.6.1. 创建账号同时，附加资源限制

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// 创建新账户，180天过期，连续3次错误锁定2天
CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

// 连续创建多个账户，每个账户采用不同的验证方式，同时又查询资源限制(每小时60次)
CREATE USER
  'jeffrey'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'new_password1',
  'jeanne'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY 'new_password2'
  REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60;

4. 错误

4.1. 导出错误 secure-file-priv 问题

The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

权限文件，查看：SHOW VARIABLES LIKE “secure_file_priv”，解决方案：

指定导出目录为：secure-file-priv 指定的目录
或者在 mysqld 服务启动时候禁用secure-file-priv，即配置：

1
2
[mysqld]
secure-file-priv = ""

4.2. 套接字连接错误

执行：mysql -h localhost -u root -p出现以下错误：

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/run/mysqld/mysqld.sock’ (2)

原因是客户端无法直接直接通过Unix Socket连到 mysql 服务，可以通过指定 Host 为 IP 或者是通过指定协议，切换到TCP Socket模式：

mysql -h 127.0.0.1 -u root -p
mysql -h localhost -u root --protocol=tcp -p

4.3. 认证插件校验问题

执行：mysql -h 127.0.0.1 -u test1 -p出现以下错误：

ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: Error loading shared library /usr/lib/mariadb/plugin/caching_sha2_password.so: No such file or directory

原因是无法从本地加载caching_sha2_password.so库文件，因为 Docker 镜像是Alpine，通过https://pkgs.alpinelinux.org/contents，站点可以检索到对应的库的安装文件，安装即可：

apk add mariadb-connector-c

或者，通过数据库将auth_option改成支持原生密码的模式：

ALTER USER 'test1'@'%' IDENTIFIED WITH mysql_native_password BY 'heyman.';

4.4. 解析告警

1
2020-03-10T11:24:23.197539Z 29 [Warning] IP address '10.34.5.119' could not be resolved: Temporary failure in name resolution

修正：基于/etc/my.cnf加入，skip_name_resolve=ON

4.5. 5.6 升级到 5.7 后，密码修正错误

1
2
ERROR 1558 (HY000): Column count of mysql.user is wrong. Expected 42, found 39. Created with MySQL 50173, now running 50555. Please use mysql_upgrade to fix this error.
————————————————

修正：运行mysql_upgrade即可将对应账号表做数据修正

4.6. 更新、插入、删除要带子表

问题：You can’t specify target table for update in FROM clause

解决方案，可以通过子表解决

1
2
3
4
5
6
7
UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM (SELECT * FROM myTable) AS something
    INNER JOIN ...
)