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

1. 错误

1.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

1.2. 5.6升级到5.7后,密码修正错误

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即可将对应账号表做数据修正

2. 关于Mysql用户创建

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

2.1. 创建账号

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

2.2. 显示帮助信息,注意auth_option选项

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'
}

2.3. 查看默认认证插件

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.10 sec)

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

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)

2.5. 账户创建

// 创建账户,所有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';

2.6. 创建账号资源限制

// 创建新账户,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;

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

3.1. docker安装mysql客户端

// 安装客户端
adk add mariadb-client

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

3.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

3.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. 导出到CSV

4.1. 使用mysqldump命令工具

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

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

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

// 导出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']

// 导出
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}] ...]

4.2. 使用SELECT…INTO OUTFILE

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

/* 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';

4.3. 导出错误

4.4. 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”, 解决方案:

  1. 指定导出目录为:secure-file-priv指定的目录
  2. 或者在mysqld服务启动时候禁用secure-file-priv,即配置:
[mysqld]
secure-file-priv = ""

5. .my.cnf配置多个client

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

5.1. 配置多个客户端选项

 # 默认客户端配置
[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

5.2. alias执行

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

6. 参考