I’m relatively new but have just now made the upgrade from 11.1 to 11.2. In looking at the release notes, I saw the comment
File locations changes:
* MySQL socket file and pid file were moved from /var/lib/mysql to more reasonable location /var/run/mysql by default.
* MySQL log files are in /var/log/mysql.
Not sure what I was supposed to do with that information. So I ignored it. Now, I am finding some issues with it. The web server works, but any connection to the database fails with
2002: Can’t connect to local MySQL server through socket ‘/var/run/mysql/mysql.sock’ (2)
The thought dawned on me that maybe all my databases were erased and none set up. Going in to the terminal, I could connect and list and query the databases. So even though I backed them up, they still are there!
phpMyAdmin gives the same error message.
However, when I go into MySQL Administrator, it works fine.
I forgot where I saw it, but the socket is still set to var/lib/mysql. I could not find any mysql install log and would say what was in my cnf.my, but it still doesn’t exist just as it has not in the past. Not sure if I should allow the MySQL Administrator to create it since I always ignored the prompt in the past and it worked. (Just wishing to keep the defaults)
At first I thought the upgrade upgraded the MySQL, but it’s still 5.1.39 as I believe before. I’ve un-installed mysql-server and re-installed it with no change.
Can anyone can tell me how to get the socket to /var/run/mysql or what else is wrong? I read one place where mysql is supposed to set it up.
@OP: You may have edited that file or your local file ~/.my.cnf and changed the default location, which the libraries depend on. I would suggest letting it default to the location /var/run/mysql/mysql.sock.
Sorry, I may be dyslexic, too! I searched for both in /etc and could find neither. I even had searched for contents in /etc for “mysql.sock” and found sysconfig/postfix # Set this to yes, if SuSEconfig should setup ‘mysql.sock’ inside chroot jail
Note: You should set this to yes if you did POSTFIX_CHROOT=“yes” and
POSTFIX_WITH_MYSQL=“no”
and in apparmor.d/abstractions/mysql
/var/lib/mysql/mysql.sock rw,
/usr/share/mysql/charsets/ r,
/usr/share/mysql/charsets/*.xml r,
Neither seems relevant to me. But then, I’m not sure what apparmor really does. Any other ideas where it’s getting the default if my.cnf doesn’t exist?
With further searching I have come across someone else with a similar problem: Re: [opensuse] Build Service Update to mysql-5.1.34-29.1 on 10.3 has wr
My /usr/sbin/rcmysql (with 2009 date) has if test -z “$datadir”
then
datadir=/var/lib/mysql
fi
but does not have the socket line. It makes reference to /etc/my.cnf. How is the best way to create it? But even more so, how should I get mysql.sock file in the /var/run/mysql (doesn’t exist) instead of the /var/lib/mysql directory since even if I had a my.cnf, it wouldn’t point to anything that exist unless I point it to the old /var/lib/mysql. (Not sure I understand what a socket does nor how it’s created. From socket =?)
I also saw something about libmysqlclient16 and 15. I have both. Think that could be an issue and if I upgrade mysql-gui-tools I can get rid of 15 and maybe it will work? It’s many megabytes so will have to wait until I can go someplace to download it.
By the way, I had to build the kernel to get my ATI graphics to work. Think that has to do with anything?
It looks like your update wasn’t thorough enough because the 11.2 mysql package does contain the directory /var/run/mysql. Or perhaps you have a mix of the old and new packages. I guess this is one of the hazards of an incremental update.
You could just create it and restart mysql and see what happens.
Ok, I tried creating the directory and no change.
phpMyAdmin gives this explanation of the error: #2002 - The server is not responding (or the local MySQL server’s socket is not correctly configured)
Which makes sense since the error message from php says it can’t open it at /var/run/mysql so no socket exists.
How does the socket come into being?
I have uninstalled and reinstalled mysql gui tools and loaded the share instead of the share-compat to make sure the libmysqlclient15 is no longer there. Rebooted, and no difference.
The MySQLAdministrator gives much information, but I don’t know what to look for. It is using /var/lib/mysql/mysql.sock.
(Hmmmm…)
Looking at mysqld --help --verbose shows that the socket is /var/lib/mysql/mysql.sock (which we already knew).
Looking at /var/lib/mysql/(server).err shows
[Note] Plugin ‘FEDERATED’ is disabled.
InnoDB: Started; log sequence number 0 44323
[Note] Event Scheduler: Loaded 0 events
[Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.1.39’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Server (GPL)
Hmmm… Everything seems to work that is using the /var/lib/mysql/mysql.sock but some programs don’t work that use /var/run/mysql. Those two programs that I’m trying to use both are PHP programs. Looking at /etc/php5/php.ini has
; Default socket name for local MySQL connects. If empty, uses the built-in
; MySQL defaults.
mysqli.default_socket =
Built in? As in my issue is maybe with php? It’s 5.3. So, somehow it was using /var/lib, but now is using /var/run. How does it know that? Something is telling it to use 11.2’s /var/run. It’s hard to do a system search for where it’s set when you realize it could be something like $datadir/mysql.sock.
Should I try to make the system “built in” to point to /var/lib or the other programs point to /var/run? Don’t want to create more issues. Maybe if I create a my.cnf file and say socket = /var/lib/mysql/mysql.sock will fix it and any other issues? Or set php.ini to socket=…/var/lib/mysql/mysql.sock (or what?). What is best?
The directory that will contain the socket must exist and then the server creates the socket when it starts listening on it. The directory is contained in the mysql package for 11.2.
You should stick to the standard location specified by the 11.2 version of mysql and any associated libraries, which will be looking there for the socket.
It seems that you have a mix of 11.1 and 11.2 conventions on your machine, possibly because /etc/my.cnf wasn’t updated. Look to see if there is an /etc/my.cnf.rpmnew, which is the standard my.cnf from 11.2 which was not installed due to an existing /etc/my.cnf from 11.1.
I searched the whole file system for “my.cnf*” and I did find some something like my-small.cnf, my-large.cnf samples. When I look in the YAST installer, it showed in the file list a /etc/my.cnf but it’s not there. (Logging in as su in Konsole, cd /etc, ls my* shows no files)
Needing to get something to work, I modified php.ini to have socket=/var/lib/mysql/mysql.sock and everything works lovely. So unless someone can tell me where it’s setting the default to be /var/run/mysql and/or the proper way for setting things up, I guess I’ll go with this.
I don’t know if it’s because of me upgrading instead of installing fresh or my particular setup. I can’t believe I’m the only one. Rather than saying, hey, I got my problem working (even if not proper) let’s forget about it, it would be nice to know why, what happened, and how to fix it for the future. The upgrade put me behind my initial install in some ways and I’ve seen others complain. I’m afraid some would give up completely. Even I have considered giving up several times over this past year, but then I consider Microsoft, and go back to trying a little harder. Eventually I get some things working and move on, then forget about it. I think a lot of dedicated computer type people do it similarly. However, if Linux is ever going to be something the masses can use, there’s got to be a better way.
For example, just setting the time was a very burdensome and painful ordeal. Eventually, I did some order of combination of setting it in two different places and it stayed after rebooting. Then the upgrade messed it up again. After several days of it changing back after rebooting, I finally got it to stay. Not sure what I did, though. But that’s another topic, but demonstrates that there are some things wrong or at least can be made better. The average user doesn’t run a web server with mysql, but there are enough people out there who would give up with this much trouble.
Perhaps upgrading is not the right way. Installing the new system version parallel and then switching over may be better. Not sure how that would work with getting all the software installs over to the new. In fact, just for curiosity I would install separately to see if the /etc/my.cnf would appear but not sure how to set up the partitions for such parallel system. I might try to insert another drive, though small, and try it.
I suspect the upgrade from 11.1 to 11.2 didn’t go cleanly, at least for you. It’s not good that there is no /etc/my.cnf, it means something from the package was not installed. Let’s hope that any security updates to mysql don’t upset the status quo for you.
Got Apache 2 running on oS 11.2, but have similar problems with mySQL, ver. 5.1.47 (replacing an earlier attempt with 5.1.42). Yast’s file list for mysql-community-server puts my.cnf in /etc (and it is there), but there is no file /etc/my.cnf.rpmnew shown nor is there an existing directory or file called /var/run/mysql to match the entry in Yast’s file list.
As a possible indicator: at no time during the several installations of mySQL have I seen an opportunity to create the root user in mySQL or to assign that user a password. Thus I can’t reach the mySQL server from phpMyAdmin on another machine (oS 11.1).
konsultor, I find that interesting you have no /var/run/mysql directory in light of my problem. I’d be interested in knowing how your mysql works with PHP. However it sounds like you don’t have it working. Not sure if you upgraded or if this is a new install or if you have done this before. If upgrading, all your databases and passwords should be there – mine were. If a new install, you should be able to go into a terminal session and type mysql. Then you would need to SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(’<yourpassword>’);
Hi dt,
Lacking the directory /var/run/mysql, I can’t connect to the mySQL server, from its own console (konsole:bash). Which explains why I never saw the chance to set a p/w.
Checked run levels in Yast and saw that MySQL was not running. Started it by enabling mysql, which succeeded first try. Back at the root prompt, mysql command brings up a welcome message to the MySQL monitor with a mysql> prompt. (Just noticed that the MySQL command line uses BACK slashes–???) The \h help command has considerable depth–will look through that and report later.
ALSO, there’s now a mysql.sock entry in a /var/run/mysql directory. I feel I’m getting closer!
Duh! If only my reading comprehension were perfect !
I’ve realized that phpMyAdmin needs to be installed on the server, and not on the other machine from which one wants to manage MySQL. So easily I missed that idea in the download instructions.
config.inc.php was edited to add a user, and for just once I connected to papMyAdmin from another machine. Now can’t repeat that connection.
Also, still not sure how to configure a p/w for the base user of MySQL itself–is that done through mysql or phpMyAdmin?
Have set the root p/w, checked mysql.db to confirm.
My understanding is that I should create a non-root user. When I try to create ‘mysql’ as a user in Yast, I’m told that the user already exists, though I can see only my own logon identity under Yast/Security and Users (openSuse 11.1).
However, as SU I can’t log into mysql as the user mysql (Access denied) because I don’t know the p/w (apparently). This also happens when trying to sign in as user mysql without a p/w.
Why do you need to create a mysql user? It’s already been done for you. You don’t need to login as mysql to access the database.
You have to realise that the users in the mysql DB are not related to Linux users. The only connection is that if you use any mysql tool the default username is that of the current Linux user, but this can be overridden with the -u option. So if you have say a Joomla DB with the user joomlauser, you would do:
mysql -u joomlauser -p joomladb
meaning login to mysql as joomlauser, using a password, and work on joomladb.
Thanks, again. You are turning on the lights in the marquee.
Have created a user, given it a p/w, confirmed it’s access to the server. good–but only from localhost. Still missing that special something to allow me to connect from a remote machine. I know that something is completely obvious once you know it. What am I missing?
Three things, you must be able to connect to mysql from another machine, so 1. check your firewall rules, and 2. that mysql is listening on all interfaces (OotB it should), but to check:
netstat -at | grep mysql
Result should contain:
*:mysql
and not
localhost:mysql
and 3. you have to GRANT access from remote hosts. Generally those webapp instructions say something like:
GRANT ALL on joomladb.* TO joomlauser@localhost IDENTIFIED BY 'joomlapassword';
You have to add another rule like this:
GRANT ALL on joomladb.* TO joomlauser@remote.domain.name IDENTIFIED BY 'joomlapassword';
If you trust the complexity of your password and your LAN users, you could substitute ‘%’ for remote domain name, meaning all hosts. All the same, it’s best to minimise permissions, both in mysql and in the firewalls.
Did remember to add mysql server to the server’s firewall.
Problem was odd, on the remote machine. Even though the static IP for the mysql machine was entered properly in the ‘hosts’ file, ‘mysql’ was trying on a different IP address. When I used the IP rather than host name in launching ‘mysql’ it connected. I feel most of the lights are now on. Will confirm when Drupal is working and accessible remotely.
I feel most of the lights are now on. Will confirm when Drupal is working and accessible remotely.