Scheduled Maintenance: We are aware of an issue with Google, AOL, and Yahoo services as email providers which are blocking new registrations. We are trying to fix the issue and we have several internal and external support tickets in process to resolve the issue. Please see: viewtopic.php?t=158230
troubleshooting MySQL on Jessie
troubleshooting MySQL on Jessie
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-wh ... 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.
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-wh ... 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.
Re: troubleshooting MySQL on Jessie
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.
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.
Linux User #457653
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
Re: troubleshooting MySQL on Jessie
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?
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?
Re: troubleshooting MySQL on Jessie
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:
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:
Linux User #457653
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
Re: troubleshooting MySQL on Jessie
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:
Here are the contents of the directory you mentioned:
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
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)
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#
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
Re: troubleshooting MySQL on Jessie
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.
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.
Linux User #457653
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
Re: troubleshooting MySQL on Jessie
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:
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?
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#
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?
Re: troubleshooting MySQL on Jessie
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:
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.):
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:
Also, run this:
In normal situations it should return something like this:
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...
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#
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
Code: Select all
/usr/sbin/mysqld --verbose
Code: Select all
netstat -tulpn | grep mysqld
Code: Select all
root@athena:/home/prppedro# netstat -tulpn | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 572/mysqld
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...
Linux User #457653
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
Re: troubleshooting MySQL on Jessie
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:
Thanks for helping me with this.
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#
Re: troubleshooting MySQL on Jessie
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:
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.
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
And, at last, pardon me for my English. I'm quite sleepy and tired now, and it worses my already dangling English writing.
Linux User #457653
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
Re: troubleshooting MySQL on Jessie
Interesting...it seems to have started, but is "inactive" (whatever that means). Here's some output:
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.
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)
Thanks for the help...and your English is excellent.
Re: troubleshooting MySQL on Jessie
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.mzimmers wrote: Thanks for the help...and your English is excellent.
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 &
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’
Linux User #457653
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
Re: troubleshooting MySQL on Jessie
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:
I removed the --daemonize option, and it seemed to run, though I had to hard-kill it. Here's the output from that run:
And now I do get the gnq.log file:
To my novice eyes, this doesn't look good to me:
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#
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)
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#
So, I didn't run your nohup command yet...I thought you'd want to look at this first.Tcp port: 0 Unix socket: (null)
Re: troubleshooting MySQL on Jessie
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:
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:
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]
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)
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
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]
Linux User #457653
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
Re: troubleshooting MySQL on Jessie
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:
- 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!
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
- /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!
Re: troubleshooting MySQL on Jessie
Well, not that modern Workbench wouldn't work with older versions. Mine connects sometimes to a very old NT 4 server, when I need to extract data from an ancient DB. But Oracle is Oracle, go figure...
Glad it's working anyway. And, perhaps, it was indeed a problem with config/lib.
Cheers,
-trp
Glad it's working anyway. And, perhaps, it was indeed a problem with config/lib.
Cheers,
-trp
Linux User #457653
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
My PC specs: a bunch of old Intel chips, some memory, a just good enough GPU. I guess it ran Crysis, though UPS didn't quite like it.
In my experience, there's no such thing as Year of Linux...
Re: troubleshooting MySQL on Jessie
I think the problem was that I'd inadvertently created a Frankenstein MySQL through my various installations and removals. The server would come up, but the configuration files were all fouled up, so it couldn't really do anything (like accept connections). Once I thoroughly scrubbed my system of all MySQL files, the installation went well.
Re: troubleshooting MySQL on Jessie
I believe that part of the initial confusion was that the prime MYSQL configuration file has historically been named my.cnfmzimmers wrote:I think the problem was that I'd inadvertently created a Frankenstein MySQL through my various installations and removals. The server would come up, but the configuration files were all fouled up, so it couldn't really do anything (like accept connections). Once I thoroughly scrubbed my system of all MySQL files, the installation went well.
There are numerous example files for differing configurations that assume you know this.