How to change ibdata1 path in mysql? [closed]

Question

My ibdata1 file is keep on increasing in size and for a temporary solution, we want to move the file to a separate location with more hard disk space.

But, when I change the my.cnf file parameters data_dir and innodb_data_home_dir and restart the service, it is not starting and throwing errors like

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists
ERROR! The server quit without updating PID file

Please help. I need it immediately as it is the produciton database and the space is almost full.

Here is the configuration file for your reference:

[mysqld]
port=3316
datadir=/data/databases/mysql
socket=/var/lib/mysql/mysql.sock
user=root
slow_query_log = 0
long_query_time = 10
log-output = FILE
sql_mode = STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_UNSIGNED_SUBTRACTION
max_sp_recursion_depth = 40
group_concat_max_len = 30000
max_tmp_tables = 64
key_buffer_size = 64M
max_allowed_packet = 16M
table_open_cache = 256
sort_buffer_size = 8M
read_buffer_size = 8M
join_buffer_size = 8M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size = 16M
binlog_cache_size = 8M
thread_stack = 384K
thread_concurrency = 23
innodb_thread_concurrency = 46
innodb_data_home_dir = /data/databases/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/databases/mysql
innodb_buffer_pool_size = 30G
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 600
innodb_rollback_on_timeout = 1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqldump]
quick
max_allowed_packet = 16M
[mysqlhotcopy]
interactive-timeout

MySQL version is 5.5.20

Answer 1

I've just done what you want! You'll have to move the entire datadir and not just a single file - not sure if that's possible.

For example, in my datadir, you will find:

pol@pol:~/Downloads/sware/mysql/mysql-5.5.58-linux-glibc2.12-x86_64/data$ ls -lista
total 28724
32774484  5120 -rw-rw----  1 pol pol   5242880 Oct 24 12:47 ib_logfile0
32774483 18432 -rw-rw----  1 pol pol  18874368 Oct 24 12:47 ibdata1
32774487    16 -rw-rw----  1 pol pol     13498 Oct 24 12:47 mysql-bin.000001
32774488     4 drwx------  2 pol pol      4096 Oct 24 12:47 ptest
32774151     4 drwxrwxr-x  6 pol pol      4096 Oct 21 11:55 .
32774465     4 -rw-r-----  1 pol pol      2219 Oct 21 11:46 pol.err
32774486     4 -rw-rw----  1 pol pol         6 Oct 21 11:46 pol.pid
32774482     4 -rw-rw----  1 pol pol        19 Oct 21 11:46 mysql-bin.index
32774485  5120 -rw-rw----  1 pol pol   5242880 Oct 21 11:46 ib_logfile1
32643837     4 drwxrwxr-x 13 pol pol      4096 Oct 21 11:40 ..
32774334     4 drwx------  2 pol root     4096 Oct 21 11:34 mysql
32774404     4 drwx------  2 pol pol      4096 Oct 21 11:34 performance_schema
32774152     4 drwxrwxr-x  2 pol pol      4096 Oct 21 11:26 test

Files like ibdata1 and ib_logfile are very important! So, it's important to copy the whole datadir!

Step 0 - backup everything - DBA's motto - "Backup or F***up!" :-)

Also, you say that this is a production server - test on a non-prod machine first.

First, take a copy of your datadir - mine's called simply data. As a dba, you should always have a path for backing out your changes!

cp -r ./data ./databak

Then copy your old data to your new directory - with your mysqld server stopped, otherwise you will have inconsistencies.

cp -r data datatest

Then remove your old data directory

rm -rf data

Edit your my.cnf file replacing your old datadir location with your new one, or adding it, if you were using a default.

vi my.cnf

Then kick off your server again.

Et voilà!