当有人开始向我的数据库插入数据时，我想从 mySQL 中删除不必要的用户、更改密码和主机。
我删除了所有用户，只留下我的数据库正在使用的名为 forge 的用户。对于该用户，我将主机/服务器中的 % 替换为 127.0.0.1，这在测试数据库上一切正常。
在我的生产数据库中，我删除了所有预期 forge 的用户，并且输入了 localhost 而不是 127.0.0.1，这是一个错误。
现在网站无法访问数据库了，我也能访问控制台，但无法登录mysql：
错误 1045 (28000)：拒绝用户“forge”@“localhost”访问（使用密码：）
我到处寻找解决方案，尝试了很多方法，但问题仍然存在。我尝试了 skip-grant-tables 并使用 mysqld --init-file 尝试更改用户设置，但没有任何帮助。
欢迎提出任何想法。我不想使用备份，因为它太旧了，而且我会丢失很多数据。
Service is running
service mysql status
mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
Active: active (running) since Mon 2018-12-24 17:37:22 UTC; 41min ago
Process: 3291 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/my
Process: 3282 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exit
Main PID: 3293 (mysqld)
Tasks: 28 (limit: 1158)
CGroup: /system.slice/mysql.service
└─3293 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
我还收到如下错误：
2018-12-24T18:36:53.029878Z 0 [Warning] TIMESTAMP with implicit DEFAULT value i.
2018-12-24T18:36:53.030026Z 0 [Warning] Can't create test file /var/lib/mysql/At
2018-12-24T18:36:53.030059Z 0 [Note] mysqld (mysqld 5.7.24-0ubuntu0.18.04.1) st.
2018-12-24T18:36:53.033961Z 0 [Warning] Can't create test file /var/lib/mysql/At
2018-12-24T18:36:53.033976Z 0 [Warning] Can't create test file /var/lib/mysql/At
2018-12-24T18:36:53.034275Z 0 [Warning] One can only use the --user switch if rt
2018-12-24T18:36:53.034446Z 0 [ERROR] Could not open file '/var/log/mysql/errord
2018-12-24T18:36:53.042201Z 0 [ERROR] Aborting
2018-12-24T18:36:53.042807Z 0 [Note] Binlog end
2018-12-24T18:36:53.043396Z 0 [Note] mysqld: Shutdown complete
也
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/ru)
日志
2018-12-25T14:44:24.211390Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see doc umentation for more details).
2018-12-25T14:44:24.213508Z 0 [Note] mysqld (mysqld 5.7.24-0ubuntu0.18.04.1) starting as process 28769 ...
2018-12-25T14:44:24.218278Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-12-25T14:44:24.218310Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-12-25T14:44:24.218314Z 0 [Note] InnoDB: Uses event mutexes
2018-12-25T14:44:24.218318Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-12-25T14:44:24.218321Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-12-25T14:44:24.218324Z 0 [Note] InnoDB: Using Linux native AIO
2018-12-25T14:44:24.218577Z 0 [Note] InnoDB: Number of pools: 1
2018-12-25T14:44:24.218683Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-12-25T14:44:24.220346Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-12-25T14:44:24.234602Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-12-25T14:44:24.238742Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-12-25T14:44:24.265021Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-12-25T14:44:24.265092Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12MB. Physically writing the file full; Please wait ...
2018-12-25T14:44:24.287401Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-12-25T14:44:24.288164Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-12-25T14:44:24.288175Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-12-25T14:44:24.288497Z 0 [Note] InnoDB: Waiting for purge to start
2018-12-25T14:44:24.338698Z 0 [Note] InnoDB: 5.7.24 started; log sequence number 123120393
2018-12-25T14:44:24.339090Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-12-25T14:44:24.339388Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-12-25T14:44:24.343947Z 0 [Note] InnoDB: Buffer pool(s) load completed at 181225 14:44:24
2018-12-25T14:44:24.347901Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-12-25T14:44:24.347915Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2018-12-25T14:44:24.347990Z 0 [Note] IPv6 is available.
2018-12-25T14:44:24.347996Z 0 [Note] - '::' resolves to '::';
2018-12-25T14:44:24.348020Z 0 [Note] Server socket created on IP: '::'.
2018-12-25T14:44:24.348058Z 0 [ERROR] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.
2018-12-25T14:44:24.348062Z 0 [ERROR] Unable to setup unix socket lock file.
2018-12-25T14:44:24.348065Z 0 [ERROR] Aborting
2018-12-25T14:44:24.348071Z 0 [Note] Binlog end
2018-12-25T14:44:24.348116Z 0 [Note] Shutting down plugin 'auth_socket'
2018-12-25T14:44:24.348121Z 0 [Note] Shutting down plugin 'ngram'
2018-12-25T14:44:24.348123Z 0 [Note] Shutting down plugin 'partition'
2018-12-25T14:44:24.348126Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2018-12-25T14:44:24.348128Z 0 [Note] Shutting down plugin 'ARCHIVE'
2018-12-25T14:44:24.348130Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2018-12-25T14:44:24.348179Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2018-12-25T14:44:24.348182Z 0 [Note] Shutting down plugin 'MyISAM'
2018-12-25T14:44:24.348192Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2018-12-25T14:44:24.348195Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2018-12-25T14:44:24.348198Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2018-12-25T14:44:24.348200Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2018-12-25T14:44:24.348202Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2018-12-25T14:44:24.348204Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2018-12-25T14:44:24.348215Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2018-12-25T14:44:24.348217Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2018-12-25T14:44:24.348219Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2018-12-25T14:44:24.348221Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2018-12-25T14:44:24.348223Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2018-12-25T14:44:24.348225Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2018-12-25T14:44:24.348227Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2018-12-25T14:44:24.348229Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2018-12-25T14:44:24.348231Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2018-12-25T14:44:24.348233Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2018-12-25T14:44:24.348235Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2018-12-25T14:44:24.348237Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2018-12-25T14:44:24.348238Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2018-12-25T14:44:24.348240Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2018-12-25T14:44:24.348242Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2018-12-25T14:44:24.348244Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2018-12-25T14:44:24.348246Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2018-12-25T14:44:24.348248Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2018-12-25T14:44:24.348250Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2018-12-25T14:44:24.348252Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2018-12-25T14:44:24.348254Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2018-12-25T14:44:24.348256Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2018-12-25T14:44:24.348258Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2018-12-25T14:44:24.348260Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2018-12-25T14:44:24.348262Z 0 [Note] Shutting down plugin 'InnoDB'
2018-12-25T14:44:24.348334Z 0 [Note] InnoDB: FTS optimize thread exiting.
2018-12-25T14:44:24.348485Z 0 [Note] InnoDB: Starting shutdown...
2018-12-25T14:44:24.448847Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2018-12-25T14:44:24.449063Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 181225 14:44:24
2018-12-25T14:44:25.957567Z 0 [Note] InnoDB: Shutdown completed; log sequence number 123120412
2018-12-25T14:44:25.959808Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2018-12-25T14:44:25.959831Z 0 [Note] Shutting down plugin 'MEMORY'
2018-12-25T14:44:25.959839Z 0 [Note] Shutting down plugin 'CSV'
2018-12-25T14:44:25.959844Z 0 [Note] Shutting down plugin 'sha256_password'
2018-12-25T14:44:25.959847Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-12-25T14:44:25.960071Z 0 [Note] Shutting down plugin 'binlog'
2018-12-25T14:44:25.960297Z 0 [Note] mysqld: Shutdown complete
2018-12-25T14:45:18.574624Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-25T14:45:18.576730Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.24-0ubuntu0.18.04.1) starting as process 29019 ...
2018-12-25T14:45:18.580978Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-12-25T14:45:18.581003Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-12-25T14:45:18.581007Z 0 [Note] InnoDB: Uses event mutexes
2018-12-25T14:45:18.581010Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-12-25T14:45:18.581014Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-12-25T14:45:18.581017Z 0 [Note] InnoDB: Using Linux native AIO
2018-12-25T14:45:18.581264Z 0 [Note] InnoDB: Number of pools: 1
2018-12-25T14:45:18.581374Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-12-25T14:45:18.582997Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-12-25T14:45:18.591560Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-12-25T14:45:18.593907Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-12-25T14:45:18.605972Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-12-25T14:45:18.620997Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-12-25T14:45:18.621068Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-12-25T14:45:18.642100Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-12-25T14:45:18.642948Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-12-25T14:45:18.642960Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-12-25T14:45:18.643253Z 0 [Note] InnoDB: Waiting for purge to start
2018-12-25T14:45:18.693516Z 0 [Note] InnoDB: 5.7.24 started; log sequence number 123120412
2018-12-25T14:45:18.693923Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-12-25T14:45:18.699135Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-12-25T14:45:18.699162Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2018-12-25T14:45:18.699301Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-12-25T14:45:18.699521Z 0 [Note] IPv6 is available.
2018-12-25T14:45:18.699531Z 0 [Note] - '::' resolves to '::';
2018-12-25T14:45:18.699555Z 0 [Note] Server socket created on IP: '::'.
2018-12-25T14:45:18.700926Z 0 [Note] InnoDB: Buffer pool(s) load completed at 181225 14:45:18
2018-12-25T14:45:18.728760Z 0 [Note] Event Scheduler: Loaded 0 events
2018-12-25T14:45:18.728960Z 0 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.7.24-0ubuntu0.18.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
2018-12-25T14:45:19.028308Z 2 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:20.394050Z 3 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:21.501361Z 4 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:22.616129Z 5 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:23.728224Z 6 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:24.840142Z 7 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:25.946301Z 8 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:29.266520Z 11 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:30.377526Z 12 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:31.481280Z 13 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:32.578283Z 14 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:33.136082Z 15 [Note] Access denied for user 'username'@'localhost' (using password: YES)
2018-12-25T14:45:33.683151Z 16 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:34.781366Z 17 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:35.876729Z 18 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:36.977013Z 19 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
2018-12-25T14:45:38.083751Z 20 [Note] Access denied for user 'forge'@'localhost' (using password: YES)
答案1
两种解决方案：
MySQLd 尝试创建套接字和锁文件，但缺少父目录。
sudo mkdir /var/run/mysqld sudo chown mysql:mysql /var/run/mysqld- 添加
skip-external-locking到my.cnf该[mysqld]部分下，然后重新启动服务。
答案2
我找到了一个解决方案。
我将 mysql 用户文件复制到 /var/lib/mysql/mysql ，还必须将它们 chown 到 mysql，重新启动 mysql，然后我就可以使用备份文件中的用户登录。
所有数据库均被保存。