0

This question already has an answer here:

I'm trying to move my MySQL datadir from /var/lib/mysql over to /home/mysql (an ssd drive as I'm having iowait issues on /var/lib/mysql when MySQL is running on that drive).

The OS is CentOS 5.6 (final), MySQL version is 5.5.17-log and Selinux is disabled.


I've read many how-tos on the web and here is the procedure I'm using:

  1. mkdir /home/mysql and then chown mysql:mysql /home/mysql

  2. flush tables;

  3. service mysqld stop

  4. cp -R -p /var/lib/mysql/* /home/mysql/

  5. mv /var/lib/mysql /var/lib/mysql_off

  6. in my.cnf change datadir=/home/mysql and change socket=/home/mysql/mysql.sock

  7. service mysqld start (it starts fine,OK)


When I go to load my website (or any other sites on this same server) I get this error:

error sql: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

So it's clearly trying to access the old socket location which is invalid since I'm changing the MySQL directory to mysql_old.

The mysql error log does not show any issues when i start up and does show the new socket location, with no errors (see bottom for the mysql error log)

Solutions I've tried: adding to my.cnf:

[client]
socket=/home/mysql/mysql.sock

and then starting mysqld -> same error.


I know I can just make a sym link of /var/lib/mysql -> /home/mysql and that will solve this but I want to know why I'm getting that error even with the [client] socket=/home/mysql/mysql.sock and how to fix this so that I don't have to make a symlink.


Other info:

ls -la of actual /home/mysql:

drwxr-xr-x 26 mysql      mysql           4096 Aug 16 21:40 mysql

ls -la of actual /var/lib/mysql:

drwxr-xr-x 26 mysql     mysql   4096 Aug 16 21:41 mysql

[root@x home]# sestatus -v 
<p>SELinux status: disabled

mysqld error log with socket and data dir set to my new locations: /home/mysql/:

140816 21:39:36 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140816 21:39:55 mysqld_safe Starting mysqld daemon with databases from /home/mysql
140816 21:39:55 [Note] Plugin 'FEDERATED' is disabled.
140816 21:39:55 InnoDB: The InnoDB memory heap is disabled
140816 21:39:55 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140816 21:39:55 InnoDB: Compressed tables use zlib 1.2.3
140816 21:39:55 InnoDB: Using Linux native AIO
140816 21:39:55 InnoDB: Initializing buffer pool, size = 256.0M
140816 21:39:55 InnoDB: Completed initialization of buffer pool
140816 21:39:55 InnoDB: highest supported file format is Barracuda.
140816 21:39:55  InnoDB: Waiting for the background threads to start
140816 21:39:56 InnoDB: 1.1.8 started; log sequence number 784856558
140816 21:39:56 [Warning] 'user' entry 'root@xxx.xxxx.com' ignored in --skip-name-resolve mode.
140816 21:39:56 [Warning] 'user' entry '@xxxx.xxxx.com' ignored in --skip-name-resolve mode.
140816 21:39:56 [Note] Event Scheduler: Loaded 0 events
140816 21:39:56 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.17-log'  socket: '/home/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi  

mysqld error log prior with socket and data dir set to: /var/lib/mysql:

140816 21:40:09 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140816 21:40:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140816 21:40:59 [Note] Plugin 'FEDERATED' is disabled.
140816 21:40:59 InnoDB: The InnoDB memory heap is disabled
140816 21:40:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140816 21:40:59 InnoDB: Compressed tables use zlib 1.2.3
140816 21:40:59 InnoDB: Using Linux native AIO
140816 21:40:59 InnoDB: Initializing buffer pool, size = 256.0M
140816 21:40:59 InnoDB: Completed initialization of buffer pool
140816 21:40:59 InnoDB: highest supported file format is Barracuda.
140816 21:40:59  InnoDB: Waiting for the background threads to start
140816 21:41:00 InnoDB: 1.1.8 started; log sequence number 784856558
140816 21:41:00 [Warning] 'user' entry 'root@xxxx.xxxx.com' ignored in --skip-name-resolve mode.
140816 21:41:00 [Warning] 'user' entry '@xxx.xxxx.com' ignored in --skip-name-resolve mode.
140816 21:41:00 [Note] Event Scheduler: Loaded 0 events
140816 21:41:00 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.17-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi

marked as duplicate by mustaccio, McNets, hot2use, Mr.Brownstone, Paul White Dec 15 '18 at 5:13

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Please include the result of shell> selinuxenabled && echo "yes!" – Morgan Tocker Aug 17 '14 at 3:08
  • I should have included that selinux is not enabled (its disabled): [root@x home]# sestatus -v SELinux status: disabled – James Gaul Aug 17 '14 at 4:07
  • @James Gaul first thing first, what do you get when you run "mysql" the command line client with the configured parameters with a non-root user, can you log in? (check that /home/mysql/mysql.sock is accessible for non-root users) – jynus Aug 17 '14 at 9:50
  • Thanks jynus, im getting the same socket error when i log in non root into mysql via CLI, the web site (php that access mysql) that i copied and pasted the sock error code from does not use root to access the DB but its own user/password. ive added the mysql error log for both start ups and there are no errors/differences in the log, and each startup error log notes the proper sock file location (and no error) – James Gaul Aug 17 '14 at 12:52
  • 1
    Then your problem is on mysql client configuration, check that you have mysql.sock with the right permissions: in my case: $ mysql --socket=mysql.sock ERROR 2002 (HY000): Can't connect to local MySQL server through socket 'mysql.sock' (13) $ sudo chmod o+w mysql.sock $ mysql --socket=mysql.sock Welcome to the MySQL monitor. Other things that could go wrong: skip-networking not recognizing localhost for authentication and client configuration not being used: check with mysql --print-defaults. – jynus Aug 17 '14 at 15:18
0

This is not a real solution, but having the same issue, setting the protocol to tcp in my.cnf, I was able to workaround the issue like this:

[client]
# avoid using mysql.sock
protocol=tcp

Alternatively, you can specify it when running the mysql client mysql -u username -p --host=127.0.0.1

As per https://bugs.mysql.com/bug.php?id=31577

Note, the above solutions as suggested in the comments did not work for me.

Not the answer you're looking for? Browse other questions tagged or ask your own question.