A few days ago, I tried to backup my database. It is mySQL community edition 5.7 running on an AWS EC2 instance. This has always worked. The backup command was:
mysqldump -h localhost -uroot -pmypwd --opt my_db > "/home/ec2-user/dumps/DB-`date '+%Y-%m-%d'`.sql"
I got an error about root@localhost now having access with password (YES) … or something to this affect. I used to have all these priviledges, but I thought I missed something. So, I was following instructions to grant priviledges, and something must have happened.
So, now I am trying to start my mysql with this command:
sudo systemctl stop mysqld
and I get this for a response:
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details
systemctl status mysqld.service; shows
mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Sun 2019-12-08 21:13:15 UTC; 7min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 8544 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 8523 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 32570 (code=exited, status=0/SUCCESS)
Dec 08 21:13:12 ip-172-31-44-188.us-east-2.compute.internal systemd[1]: mysqld.service holdoff time over, scheduling restart.
Dec 08 21:13:12 ip-172-31-44-188.us-east-2.compute.internal systemd[1]: Starting MySQL Server...
Dec 08 21:13:13 ip-172-31-44-188.us-east-2.compute.internal systemd[1]: mysqld.service: control process exited, code=exited status=1
Dec 08 21:13:15 ip-172-31-44-188.us-east-2.compute.internal systemd[1]: Stopped MySQL Server.
Dec 08 21:13:15 ip-172-31-44-188.us-east-2.compute.internal systemd[1]: Unit mysqld.service entered failed state.
Dec 08 21:13:15 ip-172-31-44-188.us-east-2.compute.internal systemd[1]: mysqld.service failed.
journalctl -xe; shows
Dec 08 21:20:07 ip-172-31-44-188.us-east-2.compute.internal dhclient[3032]: XMT: Solicit on eth0, interval 123510ms.
Dec 08 21:20:21 ip-172-31-44-188.us-east-2.compute.internal sshd[8934]: Invalid user db2as from 129.211.141.207 port 41044
Dec 08 21:20:21 ip-172-31-44-188.us-east-2.compute.internal sshd[8934]: input_userauth_request: invalid user db2as [preauth]
Dec 08 21:20:22 ip-172-31-44-188.us-east-2.compute.internal sshd[8934]: Received disconnect from 129.211.141.207 port 41044:11: Bye Bye [preauth]
Dec 08 21:20:22 ip-172-31-44-188.us-east-2.compute.internal sshd[8934]: Disconnected from 129.211.141.207 port 41044 [preauth]
Dec 08 21:20:42 ip-172-31-44-188.us-east-2.compute.internal sudo[8954]: ec2-user : TTY=pts/0 ; PWD=/home/ec2-user ; USER=root ; COMMAND=/bin/systemctl
Dec 08 21:20:42 ip-172-31-44-188.us-east-2.compute.internal sudo[8954]: pam_unix(sudo:session): session opened for user root by ec2-user(uid=0)
Dec 08 21:20:42 ip-172-31-44-188.us-east-2.compute.internal sudo[8954]: pam_unix(sudo:session): session closed for user root
Dec 08 21:21:55 ip-172-31-44-188.us-east-2.compute.internal sudo[9015]: ec2-user : TTY=pts/0 ; PWD=/home/ec2-user ; USER=root ; COMMAND=/bin/journalct
Dec 08 21:21:55 ip-172-31-44-188.us-east-2.compute.internal sudo[9015]: pam_unix(sudo:session): session opened for user root by ec2-user(uid=0)
cat /car/log/mysqld.log
2019-12-08T21:12:48.819704Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.28) starting as process 8051 ...
2019-12-08T21:12:48.822644Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-12-08T21:12:48.822664Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-12-08T21:12:48.822673Z 0 [Note] InnoDB: Uses event mutexes
2019-12-08T21:12:48.822681Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-12-08T21:12:48.822684Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-12-08T21:12:48.822687Z 0 [Note] InnoDB: Using Linux native AIO
2019-12-08T21:12:48.822928Z 0 [Note] InnoDB: Number of pools: 1
2019-12-08T21:12:48.823025Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-12-08T21:12:48.824620Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-12-08T21:12:48.832709Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-12-08T21:12:48.834792Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-12-08T21:12:48.846485Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-12-08T21:12:48.856380Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-12-08T21:12:48.856437Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-12-08T21:12:48.888321Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-12-08T21:12:48.889256Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-12-08T21:12:48.889264Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-12-08T21:12:48.889512Z 0 [Note] InnoDB: Waiting for purge to start
2019-12-08T21:12:48.939745Z 0 [Note] InnoDB: 5.7.28 started; log sequence number 3507184
2019-12-08T21:12:48.940051Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-12-08T21:12:48.946177Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2019-12-08T21:12:48.946195Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2019-12-08T21:12:48.946829Z 0 [Warning] CA certificate ca.pem is self signed.
2019-12-08T21:12:48.946859Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2019-12-08T21:12:48.947194Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-12-08T21:12:48.947221Z 0 [Note] IPv6 is available.
2019-12-08T21:12:48.947231Z 0 [Note] - '::' resolves to '::';
2019-12-08T21:12:48.947249Z 0 [Note] Server socket created on IP: '::'.
2019-12-08T21:12:48.947277Z 0 [ERROR] Could not open unix socket lock file /var/lib/mysql/mysql.sock.lock.
2019-12-08T21:12:48.947281Z 0 [ERROR] Unable to setup unix socket lock file.
2019-12-08T21:12:48.947284Z 0 [ERROR] Aborting
2019-12-08T21:12:48.949382Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-12-08T21:12:48.950598Z 0 [Note] InnoDB: Buffer pool(s) load completed at 191208 21:12:48
2019-12-08T21:12:48.950618Z 0 [Note] Binlog end
2019-12-08T21:12:48.950662Z 0 [Note] Shutting down plugin 'validate_password'
2019-12-08T21:12:48.950670Z 0 [Note] Shutting down plugin 'ngram'
2019-12-08T21:12:48.950673Z 0 [Note] Shutting down plugin 'partition'
2019-12-08T21:12:48.950676Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2019-12-08T21:12:48.950678Z 0 [Note] Shutting down plugin 'ARCHIVE'
2019-12-08T21:12:48.950681Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2019-12-08T21:12:48.950712Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2019-12-08T21:12:48.950715Z 0 [Note] Shutting down plugin 'MyISAM'
2019-12-08T21:12:48.950724Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2019-12-08T21:12:48.950727Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2019-12-08T21:12:48.950729Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2019-12-08T21:12:48.950731Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2019-12-08T21:12:48.950734Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2019-12-08T21:12:48.950736Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2019-12-08T21:12:48.950738Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2019-12-08T21:12:48.950741Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2019-12-08T21:12:48.950743Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2019-12-08T21:12:48.950745Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2019-12-08T21:12:48.950748Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2019-12-08T21:12:48.950750Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2019-12-08T21:12:48.950752Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2019-12-08T21:12:48.950754Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2019-12-08T21:12:48.950757Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2019-12-08T21:12:48.950759Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2019-12-08T21:12:48.950761Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2019-12-08T21:12:48.950763Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2019-12-08T21:12:48.950765Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2019-12-08T21:12:48.950768Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2019-12-08T21:12:48.950770Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2019-12-08T21:12:48.950777Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2019-12-08T21:12:48.950780Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2019-12-08T21:12:48.950782Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2019-12-08T21:12:48.950784Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2019-12-08T21:12:48.950786Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2019-12-08T21:12:48.950788Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2019-12-08T21:12:48.950791Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2019-12-08T21:12:48.950793Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2019-12-08T21:12:48.950795Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2019-12-08T21:12:48.950801Z 0 [Note] Shutting down plugin 'InnoDB'
2019-12-08T21:12:48.950867Z 0 [Note] InnoDB: FTS optimize thread exiting.
2019-12-08T21:12:48.950966Z 0 [Note] InnoDB: Starting shutdown...
2019-12-08T21:12:49.051293Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2019-12-08T21:12:49.051544Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 191208 21:12:49
2019-12-08T21:12:50.559818Z 0 [Note] InnoDB: Shutdown completed; log sequence number 3507219
2019-12-08T21:12:50.561394Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2019-12-08T21:12:50.561410Z 0 [Note] Shutting down plugin 'MEMORY'
2019-12-08T21:12:50.561416Z 0 [Note] Shutting down plugin 'CSV'
2019-12-08T21:12:50.561420Z 0 [Note] Shutting down plugin 'sha256_password'
2019-12-08T21:12:50.561423Z 0 [Note] Shutting down plugin 'mysql_native_password'
2019-12-08T21:12:50.561595Z 0 [Note] Shutting down plugin 'binlog'
2019-12-08T21:12:50.561788Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
A command of: ps -ef | grep mysql
does not show anything running on the MySQL ports …. so I am a very curious to what is happening. Why MySQL can't access the port that it needs, or get access to the socket it needs.
I'll keep looking, but if anyone knows what the issue is, I could use some help.
Thanks!
Best Answer
Check there isn't another mysqld instance running. If there isn't, delete /var/lib/mysql/mysql.sock.lock and start again.