On Sunday I usually do my weekly backup. Once I woke up, I’ve prepared a coffee and I started with the usual procedures. But when I’ve tried to do my local server backup, I got a MySQL error. For some reason I wasn’t able to login to the local mysql-serve using the terminal nor phpMyAdmin. After some back and forth, I’ve decided it will be easier to do a complete reinstall. Here are the steps that fixed the problem (still not sure what was, though).
The first logical step was to search for the issue and I found this thread in Ask Ubuntu. I’ve followed the instructions and I’m sharing them with you, with some slight modifications. Keep in mind these steps will delete your local databases, make sure you have a backup or you’ll loose the data.
- Backup your my.cnf file in /etc/mysql and my.cnf.fallback and remove them from their original locations. I haven’t used it, but it’s a good step, just in case.
- Verify you don’t have a my.cnf file placed somewhere else. In the original thread, the home directory was mentioned, but I had this file located in /etc/alternatives/my.cnf.
Run sudo find / -name “my.cnf”, just in case. This may take time, so prepare a coffee and wait for a bit. If you found some results, delete them.
In my case I found two more files: /var/lib/dpkg/alternatives/my.cnf and /etc/alternatives/my.cnf
- Remove the folder /etc/mysql/mysql.conf.d/, using this:
sudo rm -r /etc/mysql/mysql.conf.d/
- Backup and remove /etc/mysql/debian.cnf files (not sure if needed, but just in case).
- Now it’s time to completely remove mysq-server from your installation. Note: this will delete your localhost databases, make sure you have backup copies (if possible).
sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo rm -rf /var/lib/mysql
I wanted to clean everything, so:
sudo apt autoremove
Now it’s time to install a new MySQL Server:
sudo apt install mysql-server
Follow the steps in the terminal and you’ll have a bran new MySQL server installed locally.
After the installation, I got these error in the terminal:
Job for mysql.service failed because the control process exited with error code. See “systemctl status mysql.service” and “journalctl -xe” for details.
invoke-rc.d: initscript mysql, action “start” failed.
dpkg: error processing package mysql-server-5.7 (–configure):
subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
mysql-server depends on mysql-server-5.7; however:
Package mysql-server-5.7 is not configured yet.
dpkg: error processing package mysql-server (–configure):
dependency problems – leaving unconfigured
No apport report written because the error message indicates its a followup error from a previous failure.
Processing triggers for systemd (229-4ubuntu12) …
Processing triggers for ureadahead (0.100.0-19) …
Errors were encountered while processing:
E: Sub-process /usr/bin/dpkg returned an error code (1)
In case your syslog shows an error like mysqld: Can’t read dir of ‘/etc/mysql/conf.d/ create a symbolic link
ln -s /etc/mysql/mysql.conf.d /etc/mysql/conf.d.
Then the service should be able to start with service mysql start. Most likely you’ll need to use “sudo”.
Following these steps I’ve finally managed to login to my local MySQL server back again and everything is working fine now.
Photo credits – https://unsplash.com/photos/xekxE_VR0Ec