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: 80
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: 20
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: 80
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: 20
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: 80
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: 20
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: 80
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: 20
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: 80
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: 20
Joined: 2017-03-13 16:09
Location: São Paulo/SP

Re: troubleshooting MySQL on Jessie

Postby mzimmers » 2017-03-23 13:22

Interesting...it seems to have started, but is "inactive" (whatever that means). Here's some output:

Code: Select all
root@debian:/var/run# ls -ld mysqld/
drwxr-xr-x 2 mysql root 40 Mar 23 08:01 mysqld/
root@debian:/var/run# mysqld --user=mysql --daemonize --log-error --general-log --general-log-file=/tmp/gnq.log --pid-file=/var/run/mysqld/mysqld.pid
170323  8:04:44 [Note] mysqld (mysqld 5.5.54-0+deb8u1-log) starting as process 15987 ...
root@debian:/var/run# service mysql status
● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql)
   Active: inactive (dead) since Thu 2017-03-23 08:01:33 MDT; 3min 44s ago
  Process: 15771 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
 Main PID: 505 (code=exited, status=0/SUCCESS)

Mar 23 08:01:32 debian systemd[1]: Stopping LSB: Start and stop the mysql database server daemon...
Mar 23 08:01:32 debian mysqld[8825]: 170323  8:01:32 [Note] /usr/sbin/mysqld: Normal shutdown
Mar 23 08:01:32 debian mysqld[8825]:
Mar 23 08:01:32 debian mysqld[8825]: 170323  8:01:32 [Note] Event Scheduler: Purging the queue. 0 events
Mar 23 08:01:32 debian mysqld[8825]: 170323  8:01:32  InnoDB: Starting shutdown...
Mar 23 08:01:32 debian mysqld[8825]: 170323  8:01:32  InnoDB: Shutdown completed; log sequence number 1595685
Mar 23 08:01:32 debian mysqld[8825]: 170323  8:01:32 [Note] /usr/sbin/mysqld: Shutdown complete
Mar 23 08:01:32 debian mysqld[8825]:
Mar 23 08:01:33 debian mysql[15771]: Stopping MySQL database server: mysqld.
Mar 23 08:01:33 debian systemd[1]: Stopped LSB: Start and stop the mysql database server daemon.
root@debian:/var/run# more /tmp/gnq.log
/tmp/gnq.log: No such file or directory
root@debian:/var/run# service mysqld status
● mysqld.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)


If I'm interpreting this correctly, it's trying to start up, but stopping almost immediately. Strange, particularly since this is a clean installation (or so I thought).

Thanks for the help...and your English is excellent.
mzimmers
 
Posts: 80
Joined: 2016-12-11 23:49

Re: troubleshooting MySQL on Jessie

Postby prppedro » 2017-03-23 14:47

mzimmers wrote:Thanks for the help...and your English is excellent.

hahaha, I'm a "tryhard" when it comes to languages that aren't my own. Glad to know my efforts weren't fruitless, at all.

Now, back to MySQL... According to the logs, you've stopped it. So, it's alright, so far. After stopping it and calling with directly trough the terminal causes MySQL daemon to start outside services framework, so it's quite probably that "status" sub-command won't return any information about our running process. In such situation, try using that "netstat -tulpn | grep mysql" command. If it lists nothing, the server didn't went up at all.

But, for now, we don't need to run netstat to know that. /tmp/gnq.log isn't being generated, at all, suggesting it's indeed failed at start. Let's try another strategy:
Code: Select all
// Yes, we need to do it again, just in case, as the service sometimes erases the directory
# killall mysqld
# mdkir /var/run/mysqld
# chown -R mysql:mysql /var/run/mysqld
# nohup mysqld --user=mysql --general-log --general-log-file=/tmp/gnq.log --pid-file=/var/run/mysqld/mysqld.pid &


Then, it should do something like:
Code: Select all
root@athena:/home/prppedro# nohup mysqld --user=mysql --general-log --general-log-file=/tmp/gnq.log --pid-file=/var/run/mysqld/mysqld.pid &
[1] 11664
root@athena:/home/prppedro# nohup: ignoring input and appending output to ‘nohup.out’



Just hit enter again and you'll be at the terminal once again. To find out what's wrong, just type: "cat nohup.out | grep error". And it will show us why the daemon failed to start.
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: 20
Joined: 2017-03-13 16:09
Location: São Paulo/SP

Re: troubleshooting MySQL on Jessie

Postby mzimmers » 2017-03-23 15:47

Hi -

I don't know how I missed this before, but evidently, the daemon did produce an error log the last time. Here's what it says:
Code: Select all
root@debian:/var/run/mysqld# pwd
/var/run/mysqld
root@debian:/var/run/mysqld# more mysqld.err
170323  8:04:44 [Note] Plugin 'FEDERATED' is disabled.
170323  8:04:44 InnoDB: The InnoDB memory heap is disabled
170323  8:04:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170323  8:04:44 InnoDB: Compressed tables use zlib 1.2.8
170323  8:04:44 InnoDB: Using Linux native AIO
170323  8:04:44 InnoDB: Initializing buffer pool, size = 128.0M
170323  8:04:44 InnoDB: Completed initialization of buffer pool
170323  8:04:44 InnoDB: highest supported file format is Barracuda.
170323  8:04:44  InnoDB: Waiting for the background threads to start
170323  8:04:45 InnoDB: 5.5.54 started; log sequence number 1595685
170323  8:04:45 [ERROR] mysqld: unknown option '--daemonize'
170323  8:04:45 [ERROR] Aborting

170323  8:04:45  InnoDB: Starting shutdown...
170323  8:04:46  InnoDB: Shutdown completed; log sequence number 1595685
170323  8:04:46 [Note] mysqld: Shutdown complete

root@debian:/var/run/mysqld#


I removed the --daemonize option, and it seemed to run, though I had to hard-kill it. Here's the output from that run:

Code: Select all
170323 10:27:37 [Note] Plugin 'FEDERATED' is disabled.
170323 10:27:37 InnoDB: The InnoDB memory heap is disabled
170323 10:27:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170323 10:27:37 InnoDB: Compressed tables use zlib 1.2.8
170323 10:27:37 InnoDB: Using Linux native AIO
170323 10:27:37 InnoDB: Initializing buffer pool, size = 128.0M
170323 10:27:37 InnoDB: Completed initialization of buffer pool
170323 10:27:37 InnoDB: highest supported file format is Barracuda.
170323 10:27:37  InnoDB: Waiting for the background threads to start
170323 10:27:38 InnoDB: 5.5.54 started; log sequence number 1595685
170323 10:27:38 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
170323 10:27:38 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
170323 10:27:38 [Note] Server socket created on IP: '0.0.0.0'.
170323 10:27:38 [Note] Event Scheduler: Loaded 0 events
170323 10:27:38 [Note] mysqld: ready for connections.
Version: '5.5.54-0+deb8u1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)


And now I do get the gnq.log file:

Code: Select all
root@debian:/tmp# more gnq.log
mysqld, Version: 5.5.54-0+deb8u1-log ((Debian)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
170323 10:27:59       1 Connect   root@localhost on
          1 Connect   Access denied for user 'root'@'localhost' (using password: NO)
170323 10:28:03       2 Connect   root@localhost on
          2 Query   set autocommit=1
          2 Query   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
          2 Query   SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
          2 Query   SELECT current_user()
          2 Query   SET CHARACTER SET utf8
          2 Query   SET NAMES utf8
          2 Query   SHOW SESSION VARIABLES LIKE 'sql_mode'
          2 Query   SELECT CONNECTION_ID()
          2 Query   SHOW SESSION STATUS LIKE 'Ssl_cipher'
          2 Query   set autocommit=1
          3 Connect   root@localhost on
          3 Query   set autocommit=1
          3 Query   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
          3 Query   SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
          3 Query   SELECT current_user()
          3 Query   SET CHARACTER SET utf8
          3 Query   SET NAMES utf8
          3 Query   SET SQL_SAFE_UPDATES=1
          3 Query   SELECT CONNECTION_ID()
          3 Query   SHOW SESSION STATUS LIKE 'Ssl_cipher'
          3 Query   set autocommit=1
          3 Query   SHOW SESSION VARIABLES LIKE 'sql_mode'
          3 Query   SHOW SESSION VARIABLES LIKE 'version_comment'
          3 Query   SHOW SESSION VARIABLES LIKE 'version'
          3 Query   SELECT current_user()
          3 Query   SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
          2 Query   SHOW DATABASES
170323 10:28:04       2 Query   SHOW DATABASES
          3 Query   SHOW SESSION VARIABLES LIKE 'version_compile_os'
          4 Connect   root@localhost on
          4 Connect   Access denied for user 'root'@'localhost' (using password: NO)
170323 10:28:42       3 Quit   
          2 Quit   
170323 10:28:45       5 Connect   root@localhost on
          5 Connect   Access denied for user 'root'@'localhost' (using password: NO)
root@debian:/tmp#


To my novice eyes, this doesn't look good to me:
Tcp port: 0 Unix socket: (null)


So, I didn't run your nohup command yet...I thought you'd want to look at this first.
mzimmers
 
Posts: 80
Joined: 2016-12-11 23:49

Re: troubleshooting MySQL on Jessie

Postby prppedro » 2017-03-23 19:40

Damn... It's becoming tougher than I ever imagined. I've been thinking about a possible solution. But firstly:

1) Perhaps 5.5.x version had a different parameter than "--daemonize". I don't know... But it wasn't very useful in your case, anyway.
2) When mysqld runs from terminal, kill is the only way to stop it
3) Nope, it's normal, I got the same thing here:
Code: Select all
mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: (null)

4) The thing with nohup is fairly simple: it prevents a process unstopable by ^C from clogging your terminal. It redirects the output to a file and runs the procces in the background

Now, talking about the possible solution: seems like your server is binding to 0.0.0.0. If I'm not mistaken, MySQL Server can't function using universal bind. You SHOULD specify the IP address. Look at your config files for "bind-address" parameter. I found mine at /etc/mysql/mysql.conf.d/mysqld.cnf. The whole section reads:
Code: Select all
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address    = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


And it's a fairly default config. I suppose it's connecting as localhost. It resolves to 127.0.0.1, and MySQL might be refusing it for not being 0.0.0.0. I have a vague memory of MySQL taking 0.0.0.0 too literal or, I don't know, not accepting universal address (you choose what picture you like most. hahaha).

So, try changing bind-address to 127.0.0.1. Also, try to connect to the server using socket (/var/run/mysqld/mysqld.sock) only to check what it says in General Query Log (gnq.log, in this particular situation).

[I'm little busy now... When I get to my home, I'll study options more carefully]
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: 20
Joined: 2017-03-13 16:09
Location: São Paulo/SP

Re: troubleshooting MySQL on Jessie

Postby mzimmers » 2017-03-23 21:49

I just did a remove/purge of everything MySQL, and re-installed the server. Here's the current status:

I don't have a path /etc/mysql/mysql.conf.d/. I do have a path /etc/mysql/conf.d/ but there's no mysqld.cnf file there.

According to mysql --help:

Code: Select all
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf


- I don't have an /etc/my.cnf file
- /etc/mysql/my.cnf exists and has the bind-address line you mention. ** prior to my re-install, it did not **
- I don't have a /usr/etc directory
- I don't have a ~/.my.cnf file (as root or as my own username)

I then installed mysql-workbench, and I no longer get a connection error. Success!

So, I don't know what I did wrong originally, though I suspect I had some files left from my inadvertent installation of 5.7.17 that were causing me problems. In any event, we seem to be good now. Thanks very much for all the help!
mzimmers
 
Posts: 80
Joined: 2016-12-11 23:49

Next

Return to System configuration

Who is online

Users browsing this forum: No registered users and 4 guests

fashionable