New answers tagged

0

This should not a problem, provided you do not execute any DDL related to the Storage Engine on the Master. I have written about this many times Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns? Nov 16, 2011 : Log MySQL DB changing queries and users Mar 13, 2012 : http://dba.stackexchange.com/questions/14943/setting-up-...


0

UPDATE `GLOBAL_VARIABLES` SET `VARIABLE_VALUE`="InnoDB" WHERE `VARIABLE_NAME`="DEFAULT_STORAGE_ENGINE"


2

Since you are using MySQL 5.1.73, you can blow away the ibdata1 file without worries. First, let's look inside ibdata1 (picture created by Percona CTO Vadim Tkachenko) STEP 01 : Make sure there is no InnoDB Run the following SELECT table_schema,table_name, (data_length+index_length)/POWER(1024,3) table_sizegb FROM information_schema.tables ...


0

Restart the mysql services with innodb_file_per_table=on in my.cnf and rename table to another database and again to original data base then it will create file per table option


1

MyISAM tables have many problems and limitations, but one very convenient aspect to them in cases like this, is that the underlying .frm (table metadata) and MYI/MYD index and data files can be easily copied around since there are no transaction logs to worry about. If you don't care about the InnoDB tables at all, you could avoid the need to shrink the ...


2

The process you mentioned works fine but it takes much amount of time if the tables are bigger in size Here the way you can do it quickly .but it requires some down time depends on the table size . Take the full backup of 20 tables that are InnoDB. Drop those 20 tables that are InnoDB Stop mysql Delete the ibdata files Start the mysql services with ...


0

I will try to : Create empty table with indexes DISABLE KEYS Load Data myisampack table myisamchk -rq Hopefully myisamchk will build the indexes without ENABLE KEYS


0

Rolando, Thanks a lot for your explanation. I followed disc size and file access during this ordeal (46 hours, completed this morning) and yes .. even in MySql 5.6 this is what happens. My statements were: CREATE INDEX idx1 ....; CREATE INDEX idx2 ....; CREATE INDEX idx3 ....; ... and I saw in the "show processlist" how all the data was first copied to a ...


0

You must be doing something odd with your DDL or you have a very, very old version of MySQL. MyISAM Index creation can be very lethargic. I wrote about this almost ten years ago. Oct 10, 2006 : Why does mysql drop index very very slow in a large table? (from the MySQL General Discussion Forum) May 12, 2011 : Does MySQL still handle indexes in this way? (...


2

MyISAM table is stored as a heap, rows are being placed inside the data file into an empty place they can fit. If you are only inserting then that means appending new rows, but when the rows are being updated or deleted, some gaps are created and then later filled by other rows so the order of full table scan (which is what dump does by default) is not ...


2

This is a MyISAM table. It is easy to fix. Login to mysql and run this mysql> REPAIR TABLE mysql.proc; and you should be good to go. BTW this is where stored procedure are stored.



Top 50 recent answers are included