troubleshooting MySQL on Jessie

Kernels & Hardware, configuring network, installing services

troubleshooting MySQL on Jessie

Postby mzimmers » 2017-03-19 15:34

Hi, all -

I'm trying to troubleshoot a connection issue with MySQL on my Jessie desktop. The server is up and running, and I seem to be able to connect from the command line, but I'm having trouble with a GUI client (connection failure).

I found a web page that talks about log files:

http://www.pontikis.net/blog/how-and-when-to-enable-mysql-logs

And it mentions some files that I don't have, such as /etc/mysql/conf.d/mysqld_safe_syslog.cnf, and other files, like /etc/mysql.my.cnf are very different. This may be due to the age of the article, but can anyone tell me whether the information about enabling the general query log seems accurate? I don't want to start this logging until I know how to turn it off, as I've heard these log files can grow very rapidly.

Thank you.
mzimmers
 
Posts: 34
Joined: 2016-12-11 23:49

Re: troubleshooting MySQL on Jessie

Postby prppedro » 2017-03-21 12:05

Well, first things first, we need to know which version are you running.

Problems connecting to a localhost MySQL frequently does have something to do with Unix sockets. May be your MySQL isn't placing it in an expected place by your operating system. I had such problem with XAMPP, which stood in /opt dir... So the system couldn't locate MySQL socket. A lazy workaround were to use 127.0.0.1 instead of "localhost", at least in PHP applications.
http://tumblr.tadeu.org (Here for English only texts)

In my experience, there's no such thing as Year of Linux...
User avatar
prppedro
 
Posts: 17
Joined: 2017-03-13 16:09
Location: São Paulo/SP

Re: troubleshooting MySQL on Jessie

Postby mzimmers » 2017-03-21 14:18

Thanks for the reply. I'm running Jessie 8.7. I've tried both 127.0.0.1 and localhost. Both work from the CLI; neither work from Workbench.

I've looked in syslog for anything related to MySQL and didn't see anything. I've been advised to use caution when working with MySQL logs (uses up disk space really fast, evidently). Can I just enable login failures or something like that?
mzimmers
 
Posts: 34
Joined: 2016-12-11 23:49

Re: troubleshooting MySQL on Jessie

Postby prppedro » 2017-03-21 16:25

Well, may be you're looking for General Query log. This MySQL KB article discusses it: https://dev.mysql.com/doc/refman/5.7/en/query-log.html

Assuming you're running the standard repository version of MySQL, your socket should lie in /var/lib/mysql/mysql.sock. I suspect that, for some strange reason, your server is doing the contrary thing: may be it's accepting ONLY connections from local socket. So, before enabling the clumbersome MySQL logs, try to create a connection using your local sock. But first be sure that it's located at that address.

When creating the connection, you may use something like this:
Image
http://tumblr.tadeu.org (Here for English only texts)

In my experience, there's no such thing as Year of Linux...
User avatar
prppedro
 
Posts: 17
Joined: 2017-03-13 16:09
Location: São Paulo/SP

Re: troubleshooting MySQL on Jessie

Postby mzimmers » 2017-03-21 18:48

I don't have anything in the directory:

I just tried re-installing mysql-server=5.5.54-0+deb8u1, and now I get an error when I try to connect:

Code: Select all
root@debian:/home/mzimmers# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)


Here are the contents of the directory you mentioned:

Code: Select all
root@debian:/var/lib/mysql# ls
auto.cnf    client-cert.pem  ib_buffer_pool  ib_logfile1    private_key.pem  server-key.pem
ca-key.pem  client-key.pem   ibdata1        mysql       public_key.pem   sys
ca.pem       debian-5.5.flag  ib_logfile0     performance_schema  server-cert.pem
root@debian:/var/lib/mysql#


So, it appears that I've somehow screwed up the installation. I once was able to connect with a simple "mysql" command.

If I want to wipe out everything I've done so far, what steps do I need to take in addition to the apt-get clean and purge commands?

Thank you
mzimmers
 
Posts: 34
Joined: 2016-12-11 23:49

Re: troubleshooting MySQL on Jessie

Postby prppedro » 2017-03-21 20:09

Seems like your MySQL Server isn't running, at all. Issue a "sudo service mysql status" to confirm. In any case, seems like there's already some databases in your server. Don't purge the package before backuping that /var/lib/mysql folder.

Then,it's safe to clear it. I'm not sure if purging the package really deletes MySQL. If I'm not mistaken, dpkg will ask you about keeping the data. But backup it, just in case. The same goes for config files in /etc/mysql. Personally, I do not reuse the old configfiles... Instead, they serve me as references for tuning up the version I just installed, but it's totally up to you, anyway.

Purge mysql-server package. Run the clean command. Then, if you wish, you may use the repository provided by Oracle itself (https://dev.mysql.com/downloads/repo/apt/). Grab that .deb package, run dpkg -i <packagename.deb>. Then run apt-get update.

Finally, just run apt-get mysql-server.

I tend to think MySQL official package better than the one on official Debian repository.

Try it. See if it works. Also, you may purge mysql-client and mysql-workbench as well and then install a newer version from MySQL repository.
http://tumblr.tadeu.org (Here for English only texts)

In my experience, there's no such thing as Year of Linux...
User avatar
prppedro
 
Posts: 17
Joined: 2017-03-13 16:09
Location: São Paulo/SP

Re: troubleshooting MySQL on Jessie

Postby mzimmers » 2017-03-21 22:50

Hi, prppedro - I already started hacking away before I got your post, so I deleted some directories you said not to. I'm hopeful that wasn't a big deal, because I didn't need anything from it anyway (unless the system did).

So, I cleaned everything up (decided to start from scratch), and did an install of mysql-server=5.5.54-0+deb8u1. No error messages or anything, but after the install, I did this:

Code: Select all
root@debian:/home/mzimmers/Downloads# service mysql status
● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql)
   Active: active (running) since Tue 2017-03-21 17:30:50 MDT; 30s ago
 Main PID: 505 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysql.service
           ├─8752 /bin/sh /usr/bin/mysqld_safe
           ├─8824 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-...
           └─8825 logger -t mysqld -p daemon.error

Mar 21 17:30:52 debian /etc/mysql/debian-start[8882]: mysql.help_category                                OK
Mar 21 17:30:52 debian /etc/mysql/debian-start[8882]: mysql.help_keyword                                 OK
Mar 21 17:30:52 debian /etc/mysql/debian-start[8882]: mysql.help_relation                                OK
Mar 21 17:30:52 debian /etc/mysql/debian-start[8882]: mysql.help_topic                                   OK
Mar 21 17:30:52 debian /etc/mysql/debian-start[8882]: mysql.host                                         OK
Mar 21 17:30:52 debian /etc/mysql/debian-start[8882]: mysql.ndb_binlog_index                             OK
Mar 21 17:30:52 debian /etc/mysql/debian-start[8882]: mysql.plugin                                       OK
Mar 21 17:30:52 debian /etc/mysql/debian-start[8882]: mysql.proc                                         OK
Mar 21 17:30:52 debian /etc/mysql/debian-start[8882]: mysql.procs_priv                                   OK
Mar 21 17:30:52 debian /etc/mysql/debian-start[8882]: mysql.proxies_priv                                 OK
root@debian:/home/mzimmers/Downloads# service mysql-server status
● mysql-server.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)
root@debian:/home/mzimmers/Downloads# service mysql-server status
● mysql-server.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)
root@debian:/home/mzimmers/Downloads# service mysql-server start
Failed to start mysql-server.service: Unit mysql-server.service failed to load: No such file or directory.
root@debian:/home/mzimmers/Downloads#


So, I'm back to trying to figure out what I did wrong...is there an error log I should look into for this?

Thanks.

EDIT: I should point out that when I ran mysql-apt-config_0.8.3-1_all.deb, I was unable to get the version (5.5.54) I need, so I didn't choose anything. What is the correct way of using this tool to get the version I want?
mzimmers
 
Posts: 34
Joined: 2016-12-11 23:49

Re: troubleshooting MySQL on Jessie

Postby prppedro » 2017-03-22 15:41

Hi, mzimmers. I've been comparing your "status" output to mine. Well, there's no entry for mysql-server. In services it's simply called mysql and yours seems to be running, like mine is:
Code: Select all
root@athena:/home/prppedro# service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled)
   Active: active (running) since Wed 2017-03-15 12:51:42 BRT; 1 weeks 0 days ago
 Main PID: 572 (mysqld)
   CGroup: /system.slice/mysql.service
           └─572 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysq...

Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable.
root@athena:/home/prppedro#


Is there, however, a strange difference: while mine lists a running process (that with PID 572) of mysqld --daemonize, yours seems to be in a kind of "safe mode". There's three process in your tree, while the starter process (PID 505) already quit (thus it says exited, code 0 etc.):
Code: Select all
           ├─8752 /bin/sh /usr/bin/mysqld_safe
           ├─8824 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-...
           └─8825 logger -t mysqld -p daemon.error


I don't know if it's singularity of Debian repository version or an abnormal situations. But 'mysqld_safe' tends to be called only when MySQL process dies for any reason, for all I know. The second command suggests there's an instance of mysqld running. And there's a logger command, which pipes error info right into syslog. BTW, talking about logs, I don't exactly where to look. But seems like there's a failure running the daemon. It won't log anything if not running. Let's try running it in verbose mode and see what it says:
Code: Select all
/usr/sbin/mysqld --verbose


Also, run this:
Code: Select all
netstat -tulpn | grep mysqld


In normal situations it should return something like this:
Code: Select all
root@athena:/home/prppedro# netstat -tulpn | grep mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      572/mysqld     


An empty result means that even if mysqld is running, it didn't open any TCP/UDP sockets.

Finally, it's right: that version you want to use is not available there. So forget about it. Let's see if we can work around these issues, then...
http://tumblr.tadeu.org (Here for English only texts)

In my experience, there's no such thing as Year of Linux...
User avatar
prppedro
 
Posts: 17
Joined: 2017-03-13 16:09
Location: São Paulo/SP

Re: troubleshooting MySQL on Jessie

Postby mzimmers » 2017-03-22 19:05

Hi, prppedro -

If you look at the full output in my most recent post, you'll see that the server started, but exited very quickly. (I ran those 3 service commands with little delay between them.)

Here's some more output based on your suggestions:

Code: Select all
root@debian:/home/mzimmers/Downloads# /usr/sbin/mysqld --verbose
170322 13:50:26 [Note] /usr/sbin/mysqld (mysqld 5.5.54-0+deb8u1) starting as process 12356 ...
170322 13:50:26 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

170322 13:50:26 [ERROR] Aborting

170322 13:50:26 [Note] /usr/sbin/mysqld: Shutdown complete


Code: Select all
root@debian:/home/mzimmers/Downloads# netstat -tulpn | grep mysqld
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      8824/mysqld     
root@debian:/home/mzimmers/Downloads#


Thanks for helping me with this.
mzimmers
 
Posts: 34
Joined: 2016-12-11 23:49

Re: troubleshooting MySQL on Jessie

Postby prppedro » 2017-03-23 01:33

So, there's something odd happening. I'm using Debian 9, so there's no way for me to test MySQL server behavior in 8.7. Also, my database server runs from an standalone XAMPP setup, which settles in /opt directory. But my guess is that there's some config failure. As you can see by netstat -tulpn command, it IS running and there's a socket open. If you can connect with mysql CLI utility, it is indeed up.

My last guess is that Workbench may be trying to speak a different protocol. I don't know. It times out or the daemon simply refuses the connection?

Anyway, the log you must enable to check general operations, ranging from logins to queries, is the aforementioned "General Query Log". According to your "mysql status" command, it's running. You issued the following commands using "mysql-server" instead. But now, doesn't matter. Do this:
Code: Select all
# service mysql stop
# mkdir /var/run/mysqld
# chown -R mysql /var/run/mysqld
# mysqld --user=mysql --daemonize --log-error --general-log --general-log-file=/tmp/gnq.log --pid-file=/var/run/mysqld/mysqld.pid


Then, try to reach it via Workbench. Before trying anything else, check /tmp/gnq.log. Let's see if it gives us any clue on whats happening. Please note that I'm not caring about the program output, but I'm pretty sure that we can find something at /var/log/daemon.log.

And, at last, pardon me for my English. I'm quite sleepy and tired now, and it worses my already dangling English writing.
http://tumblr.tadeu.org (Here for English only texts)

In my experience, there's no such thing as Year of Linux...
User avatar
prppedro
 
Posts: 17
Joined: 2017-03-13 16:09
Location: São Paulo/SP


Return to System configuration

Who is online

Users browsing this forum: No registered users and 10 guests

fashionable