0

I wanted to remove unnecessary users, change passwords and hosts from mySQL as somebody started inserting data into my database.

I removed all users except the one my database is using called forge. For that user I have replaced % in host/server with 127.0.0.1 and that all works fine on the test database.

On my production database I deleted all users expect forge, and made a mistake by entering localhost instead of 127.0.0.1.

Now the site cannot access the database. I also have access to the console and I cannot login to mysql:

ERROR 1045 (28000): Access denied for user 'forge'@'localhost' (using password:)

I have searched everywhere for the solution, and tried many things, but the issue remains. I tried skip-grant-tables and also using mysqld --init-file where I tried to change the users settings, but nothing helped.

Any ideas are welcome. I do not want to use backup as it is old and I would lose a lot of data.

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

I am also getting errors like:

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

As well as

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/ru)

LOGS

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)
3
  • Please add full logs Commented Dec 24, 2018 at 21:08
  • Added error.log
    – Apps Force
    Commented Dec 25, 2018 at 14:56
  • I tried also creating a new user using: GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'username'@'127.0.0.1' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'username'@'::1' IDENTIFIED BY 'password'; and loading via file. However I cannot log in as seen in logs
    – Apps Force
    Commented Dec 25, 2018 at 14:56

2 Answers 2

0

Two solutions:

  1. MySQLd was attempting to create the socket and lock file but the parent directory was missing.

    sudo mkdir /var/run/mysqld
    sudo chown mysql:mysql /var/run/mysqld
    
  2. Add skip-external-locking to my.cnf under the [mysqld] section, and then reboot the service.
2
  • I already have that directory and socket and lock files in it. It is owned by the right user/group drwxr-xr-x 2 mysql mysql 100 Dec 25 14:45 mysqld . There is skip-external-locking in my.cnf already. Anything else?
    – Apps Force
    Commented Dec 26, 2018 at 11:04
  • Try mysql_upgrade -u adminname -p and restart service Commented Dec 26, 2018 at 12:41
0

I found a solution.

I copied mysql user files to /var/lib/mysql/mysql Also hadto chown them to mysql, restart mysql, and I can log in with the users from the backup files.

All databases are preserved.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .