0

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
jynus
  • 15,057
  • 2
  • 37
  • 46
James Gaul
  • 1
  • 1
  • 3

1 Answers1

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.

knirb
  • 1
  • 1