If you just created the /data/mysql folder and moved the ib* files to
that
folder from /var/lib/mysql, you may have to contend with the internal
data
dictionary with ibdata1.
You do the following:
1) Put the mysql data back into /var/lib/mysql
2) Configure my.cnf to set datadir=/var/lib/mysql
3) Restart mysqld
Once you put it back the way it was, then
4) mysqldump -h... -u... -p... --single-transaction --all-databases
--routines --triggers > AllData.sql
5) Make /data/mysql TOTALLY EMPTY.
6) Configure my.cnf to set datadir=/data/mysql
7) Restart mysqld
At this point, mysqld will regenerate a new ibdata1 file with a clean
internal data dictionary.
8) mysql -h... -u... -p... < AllData.sql
All data get put into this new data folder and will reload the
internal
data dictionary elements pertaining to its new location.
Moving MyISAM is simple. Moving InnoDB is a little more work but doing
it
this way ensures ibdata1 is in a proper state.
-----Original Message-----
From: Juan Eduardo Moreno [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 06, 2008 9:12 AM
To: Krishna Chandra Prajapati
Cc: mysql
Subject: Re: innodb disabled
Hi,
Can you send the error log file of mysql server in your machine?.
Regards,
Juan
On 5/6/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote:
Hi Juan,
I have verified directory permissions, its shown below.
linux76:~# ls -l /data/mysql/
total 535088
drwx------ 2 mysql mysql 20480 2008-05-04 23:44 dip
-rw-rw---- 1 mysql mysql 10485760 2007-12-23 01:21 ibdata1
-rw-rw---- 1 mysql mysql 268435456 2008-01-13 01:17 ib_logfile0
-rw-rw---- 1 mysql mysql 268435456 2008-01-13 01:17 ib_logfile1
drwx------ 2 mysql root 4096 2008-03-28 02:58 mysql
In error log file there is error.
debian-sys-maint error
The above error come while starting mysql for the first time. Then i
have
given the permissions for debian sys-maint from
/etc/mysql/debian.cnf.
After that restart mysql don't gives any error. still innodb is
disabled.
Thanks,
Prajapati
On Tue, May 6, 2008 at 7:03 AM, Juan Eduardo Moreno <
[EMAIL PROTECTED]> wrote:
Hi,
The InnoDB engine was disable because some ib_log files or Ibdata
files, InnoDB can´t read. In some cases, could be a directory
permissions
or some error in creation of ib_logfiles when database is started.
If
you
want , try to see in the error log file generated in order to see
more
details. If you don´t understand nothing in the error log file,
please
send
this errors to the list.
Regards,
Juan
On Tue, May 6, 2008 at 5:27 AM, Krishna Chandra Prajapati <
[EMAIL PROTECTED]> wrote:
Hi all,
I have change the data directory from /var/lib/mysql to
/data/mysql
directory and adding innodb_file_per_table. By doing this innodb
storage
engine get dissabled. my.cnf doesn't contains "#skip-innodb". Is
it's
adding
will enable or not
HOW CAN I ENABLE INNODB
mysql> show engines;
+------------+----------+----------------------------------------------------------------+
| Engine | Support |
Comment |
+------------+----------+----------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great
performance |
| MEMORY | YES | Hash based, stored in memory, useful
for
temporary
tables |
| InnoDB | DISABLED | Supports transactions, row-level
locking,
and
foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level
locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you
write
to it
disappears) |
| EXAMPLE | NO | Example storage
engine |
| ARCHIVE | YES | Archive storage
engine |
| CSV | YES | CSV storage
engine |
| ndbcluster | DISABLED | Clustered, fault-tolerant,
memory-based
tables |
| FEDERATED | YES | Federated MySQL storage
engine |
| MRG_MYISAM | YES | Collection of identical MyISAM
tables |
| ISAM | NO | Obsolete storage
engine |
+------------+----------+----------------------------------------------------------------+
12 rows in set (0.00 sec)
# /etc/mysql/my.cnf: The global mysql configuration file.
# $Header:
/var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1-r1,v
1.3
2006/02/01 20:25:56 vivo Exp $
#
# $Revision:$
# $Id:$
# The following options will be passed to all MySQL clients
[client]
#password =
port = 3306
socket =
/var/run/mysqld/mysqld.sock
[mysql]
character-sets-dir=utf8
default-character-set=utf8
[mysqladmin]
character-sets-dir=utf8
default-character-set=utf8
[mysqlcheck]
character-sets-dir=utf8
default-character-set=utf8
[mysqldump]
character-sets-dir=utf8
default-character-set=utf8
[mysqlimport]
character-sets-dir=utf8
default-character-set=utf8
[mysqlshow]
character-sets-dir=utf8
default-character-set=utf8
[myisamchk]
character-sets-dir=utf8
default-character-set=utf8
[myisampack]
character-sets-dir=utf8
# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log =
/var/log/mysql/mysql.err
# add a section [mysqld-4.1] or [mysqld-5.0] for specific
configurations.
[mysqld]
old_passwords = false # inserted by debconf
character-set-server = utf8
default-character-set = utf8
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysqld.err
basedir = /usr
#datadir = /var/lib/mysql
skip-locking
#set default_storage_engine=InnoDB
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 9M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 64M
query_cache_type = 1
query_cache_size = 100M
language = /usr/share/mysql/english
set-variable = max_connections=1024
# Begin replication section
log-bin=/var/db/db05-master-bin.log
server-id=5
# End replication section
#security:
#using "localhost" in connects use sockets by default
#skip-networking
#bind-address = 127.0.0.1
# Point the following paths to different dedicated disks
tmpdir = /tmp/
#log-update =
/path-to-dedicated-directory/hostname
# you need debug use flag enabled to use this ones.
# if needed uncomment them, start the server and issue
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's appening in your server ;)
#log = /tmp/mysqld.sql
#gdb
#debug = d:t:i:o,/tmp/mysqld.trace
#one-thread
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000
# The following is the InnoDB configuration
#
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb
boxes.
# you might want to bump this up a bit on boxes with more RAM
innodb_buffer_pool_size = 1G
# this is the default, increase if you have lots of tables
innodb_additional_mem_pool_size = 128M
#
# i'd like to use /var/lib/mysql/innodb, but that is seen as a
database :-(
# and upstream wants things to be under /var/lib/mysql/, so
that's
the
route
# we have to take for the moment
#innodb_data_home_dir = /var/lib/mysql/
innodb_data_home_dir = /data/mysql/
innodb_log_arch_dir = /data/mysql/
innodb_log_group_home_dir = /data/mysql/
datadir = /data/mysql/
#innodb_file_per_table
# you may wish to change this size to be more suitable for your
system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:10M:autoextend
# we keep this at around 25% of of innodb_buffer_pool_size
# sensible values range from 1MB to
(1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 256M
# this is the default, increase if you have very large
transactions.
innodb_log_buffer_size = 256M
# this is the default, and won't hurt you.
# you shouldn't need to tweak it.
set-variable = innodb_log_files_in_group=2
# see the innodb config docs, the other options are not always
safe
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
# Remove the next comment character if you are not familiar with
SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
#vim: set tabstop=4 :
#vim: set shiftwidth=4 :
Krishna Chandra Prajapati
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]