I installed the mysql-server from the repositories, and wanted to change the datadir to a custom location. With the datadir set to /var/lib/mysql I was able to start the server, but to the new location I get an error.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Here are the changes to my.cnf:
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /media/db2/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
I was searching this and other sites, came across this link and changed my /etc/apparmor.d/usr.sbin.mysqld file. Here are the relevant changes
# /var/lib/mysql/ r,
# /var/lib/mysql/** rwk,
/media/db2/mysql/ r,
/media/db2/mysql/** rwk,
I then was able to restart the mysql service with
sudo service mysql restart
but was not able to open up mysql with
mysql -u root -p
I got another error similar to the above. So I searched some more and copied the files from /var/lib/mysql to my new dir.
Now when I try to restart mysql it just hangs. The log file in /var/log/mysql/error.log shows:
130125 12:35:41 InnoDB: Waiting for the background threads to start
130125 12:35:42 InnoDB: 1.1.8 started; log sequence number 1595675
130125 12:35:42 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130125 12:35:42 [Note] - '127.0.0.1' resolves to '127.0.0.1';
130125 12:35:42 [Note] Server socket created on IP: '127.0.0.1'.
130125 12:35:42 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
130125 12:35:42 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)
130125 12:35:43 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
130125 12:35:43 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130125 12:35:43 InnoDB: The InnoDB memory heap is disabled
130125 12:35:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130125 12:35:43 InnoDB: Compressed tables use zlib 1.2.7
130125 12:35:43 InnoDB: Using Linux native AIO
130125 12:35:43 InnoDB: Initializing buffer pool, size = 128.0M
130125 12:35:43 InnoDB: Completed initialization of buffer pool
130125 12:35:43 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 49439
130125 12:35:43 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1595675
130125 12:35:45 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
I think appamor is the issue, and it's basically a permissions issue...
here are permissions from the location where I want the db files to be...
$ ls /media/db2/mysql -al
total 28732
drwxrwxrwx 3 mysql root 4096 Jan 25 12:39 .
drwxr-xr-x 4 root root 4096 Jan 25 2013 ..
-rw-rw---- 1 mysql mysql 18874368 Jan 25 2013 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Jan 25 12:39 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jan 25 2013 ib_logfile1
drwx------ 2 root root 4096 Jan 25 2013 mysql
any help would be appreciated. Thanks!
I was able to resolve this issue by chowning the datadir folder so that the mysql user was the owner
Not sure if that will solve it for others, but that worked for me.