vendredi 30 janvier 2015

How to change ibdata1 path in mysql?


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





Aucun commentaire:

Enregistrer un commentaire