长江的乱想
0%

MySQL问题点点滴滴

发表于 更新于 Valine：
本文字数： 5.9k 阅读时长 ≈ 5 分钟

    工作上遇到一些数据库的问题，现在把使用MySQL的问题都记录下来。(遥想上一次写SQL语句，还是2007-2008年间，用着SQL server 2005)

创建的用户没办法创建数据库

1
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';

    创建pig用户后

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
mysql -upig -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> create database pig_db
    -> ;
ERROR 1044 (42000): Access denied for user 'pig'@'%' to database 'pig_db'

    根据这个链接, 查看了下权限

1
2
3
4
5
6
7
mysql> show grants;
+---------------------------------------+
| Grants for distcoder@%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO `distcoder`@`%` |
+---------------------------------------+
1 row in set (0.01 sec)

    没有权限。搜了下怎么添加权限，根据这个链接，登录root用户，给pig用户添加权限:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql -uroot -p           
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL ON *.* TO 'distcoder'@'%';
Query OK, 0 rows affected (0.00 sec)

    再用pig用户登录，查看下权限：

1
2
3
4
5
6
7
8
9
10
11
mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for pig@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `pig`@`%`                                                                                                                                               |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `pig`@`%` |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> create database pig_db;
Query OK, 1 row affected (0.00 sec)

开启日志记录查询

查询日志功能是否开启

1
2
3
4
5
6
7
8
 mysql> show variables like 'general%';
+------------------+------------------------------------+
| Variable_name    | Value                              |
+------------------+------------------------------------+
| general_log      | OFF                                |
| general_log_file | /usr/local/mysql/data/macdeMBP.log |
+------------------+------------------------------------+
2 rows in set (0.01 sec)

    可以看到general_logmore默认是是OFF，也就是关闭日志功能的。日志文件保存的路径在general_log_file中

开启日志

1
2
3
4
5
6
7
8
9
10
11
 mysql> set global general_log='ON';
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'general%';
+------------------+------------------------------------+
| Variable_name    | Value                              |
+------------------+------------------------------------+
| general_log      | ON                                 |
| general_log_file | /usr/local/mysql/data/macdeMBP.log |
+------------------+------------------------------------+
2 rows in set (0.01 sec)

mysqldump导出数据

导出数据操作

数据导出：

1
mysqldump -u'test' -p'test' -P3306 db1 table1 > dump.txt

数据导入:
    连接数据库，然后执行：
1
mysql> source dump.txt

导出遇到的问题

问题
1
2
3
mysqldump -u'test' -p'test'   -P3306   --all-databases > all_database_sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

解决办法：

1
mysqldump -u'test' -p'test'   -P3306  --no-tablespaces  --all-databases > all_database_sql