I woke up this morning to find my sites were all down due to no database connection. Upon trying to restart from virtualmin I get this error: Redirecting to /bin/systemctl start mariadb.service
Job for mariadb.service failed because the control process exited with error code. See “systemctl status mariadb.service” and “journalctl -xe” for details.
Here is the log:
http://paste.ubuntu.com/19357576/
I tried renaming the 3 ibdata mysql files and then restarting but that did not work.
If you are using Innodb (and looks like you are) you cant just delete or rename ib_logfile* and ibdata*, doing this you are asking for troubles. For any change affecting ib_logfile* and ibdata* files there is procedure you must follow and its important you follow that.
Did you make any changes in DB conf file recently especially one connected with Innodb settings?
No I havent messed with anything. Went to bed and everything was fine, woke up and found the sites are on line but no db connections. I use uptimerobot to monitor my sites and I got a message from them just after 7am. I wasnt even on line when mariadb crashed.
I have since reduced the buffer pool size from 500mb to 250mb in my.conf. and rebooted server.
I have the original ib data files and renamed them so that I could restart sql. new ibdata files were created upon trying to restart sql. So i have both the old and the new. error log says invalid option --Mysql in the logs. seems like that is the problem
[mysqld]
innodb_buffer_pool_size=250M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_file_per_table = 1
thread_concurrency = 8
query_cache_size = 16M
thread_cache_size = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 4M
read_buffer_size = 1M
sort_buffer_size = 1M
table_open_cache = 256
max_allowed_packet = 1M
key_buffer_size = 256M
MySQL:
local-infile=0
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
well, heres the latest. I renamed my original mysql folder, uninstalled mariadb and installed mariadb from command line.
Results, a new empty mysql folder was created in the install process.
systemctl status mariadb.service -l still says failed to start mariadb.
So, where do we go from here?
Can you post entire /etc/my.cnf using code tags and check if you have same file in some other place.
[mysqld]
innodb_buffer_pool_size=250M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_file_per_table = 1
thread_concurrency = 8
query_cache_size = 16M
thread_cache_size = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 4M
read_buffer_size = 1M
sort_buffer_size = 1M
table_open_cache = 256
max_allowed_packet = 1M
key_buffer_size = 256M
MySQL:
local-infile=0
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
no other my.cnf located on server.
I abandoned mariadb and decided on a fresh install of mysql. Before I did that I renamed var/lib/mysql to var/lib/mysqlOLD for peace of mind.
After the install I copied the old mysql directory into the new empty mysql directory. upon opening myphpadmin all tables were visible and editable. That is the good news. the bad news is I get this error in virtualmin when I click on manage database:
SQL show index from estat_admin_assert failed : Table ‘352processserver.estat_admin_assert’ doesn’t exist
The databases that are myISAM and innoDB seem to be populated correctly and editable but not accessible from the web.
Is there something simple that I can do to correct this error. My sites cannot connect to the databases yet.
What OS are you using?
centos 7
ok so I created a brand new database in virtualmin. then I imported my backed up database from my desktop. Great, everything uploaded…but when I go to the website it still says cannot connect to database. its not user name password etc. There is a fundamental problem connecting to the mysql from the web. This is driving me delirious. Why cant i get the db to move? I deleted the htaccess in case that was the culprit. Permissions look ok, what should they be?
Centos 7 comes with MariaDB and even when there is no problems switching from that to MySQL will bring some problems. You didnt find what was the problem in first place and then you decided to uninstall MariaDB and got o install MySQL. Now what you see could be connected with old problem, could be new or both. Thats why is important to find the solution because uninstall and install rarely helps.
Now with all your actions you just made everything more complicated.
With no clue what did you do there i will tell you what i saw wrong with your old conf:
[mysqld]
…
MySQL: local-infile=0
…
should be
[mysqld]
…
local-infile=0
…
…
it still says cannot connect to database. its not user name password etc.
Use SSH and try to login in your DB with same username and psw.
Why cant i get the db to move?
Move where?
Permissions look ok, what should they be?
mysql:mysql
What server is this - VPS, Dedicated, from hosting company, server at home… DB is local or remote… anything from the logs, if yes post it here. And please use <.code> <./code> or <.blockcode> <./blockcode> tags (without dots) when you copy paste the files or logs, it makes them much easier and faster to read.
vps on linode.
So the old my.conf file has something you didnt like. I certainly didnt change that. Is that a critical error or just a better way? If it is critical, then that could be the problem. I did try to troubleshoot maria but there was nothing I could do and the logs were of no help to me. I pasted the logs in the first post above. Did you see anything there?
Your original error:
160714 7:26:57 [ERROR] /usr/libexec/mysqld: unknown option ‘–MySQL:’
and you had in my.cnf:
[mysqld]
…
MySQL: local-infile=0
…
So i suspect it could be the reason you had with MariaDB.
Forgot to say, that line would 100% prevent your DB to start so this could be the only problem you had. You should go back to MariaDB as default for Centos 7 plus its faster and better DB than MySQL.
So, since this happened while I was not on line, would you speculate that someone somehow changed that and only that? The problem I had was I could not even restart mysql. Logs show it spontaneously shut down at 704am.
I’m pretty sure if someone wanted to hack you he would not try to strength your security. That line is not written by default during the installation so someone must put it there. Who? I dont know but for sure it was not a hacker. It could be that you or someone else made the changes and forgot to restart the DB and then during server restart the changes activated. Or it could be something else and the log files should show something.
When it comes to DB if you dont know what are you doing best would be to leave it or ask/pay some SysAdmin to do the job for you.
It wasnt me for sure. MySQL crashed spontaneously and for no apparent reason. I would have no reason to edit that file ever. This is the second time on two different servers that that file had been modified. Last time the Innodb pool line had been altered by dropping part of the line. I will make a copy of that file in the future and change permissions to not writable. I still dont know if restoring that file will allow mariadb to restart. but at this point I have nothing to lose by uninstalling my sql and installing mariabd and experimenting. I have moved most of my sites to my other server.
For what i know that file would not change alone or modified by other software without human intervention. It could be that someone was playing around with Vmin settings and changed that file.
Only thing what come into my mind is to install MariaDB and then try to use Vmin post-installation wizard again, this should immediately show if Vmin can communicate with the DB or not.
Great advice for me and everyone else. I will let you know if your fix works. I am the only one to use my server so I can say with certainty that no one that I know would have access…not sure about vengeful tech support guys at these vps outfits.
Well…
I managed to get mysql uninstalled and a new copy of mariadb running.
But now I have no virtualmin menu. Nada. I am about ready to throw in the towel.