Once you decide to use mysqldump, be aware that the quickest way to
export/import large files is to use the --tab feature on export and
mysqlimport to load the data...
Essentially:
On the old (4.0) server:
mysqldump --tab=/var/tmp/directory mydatabase
On the new (4.1) server (assuming you have a new empty mysql data
directory with just your MyISAM based mysql database to ensure your
permissions files are there):
mysql -e "create database mydatabase;"
cat /var/tmp/directory/*.sql | mysql mydatabase
mysqlimport mydatabase /var/tmp/directory/*.txt
Essentially you are creating a text .sql file for each table with the
create table command, and a .txt file with the raw data in tab
delimitted format... mysqlimport imports the whole data file as one
SQL command, using traditional mysqldump you get a unique SQL insert
command for each line of data... doing it once means only writing the
indexes etc. once and other time saving advantages... it's far
quicker to insert many rows of data as a single INSERT command, than
it is to do it row by row. So if you have a large data set and you
are doing the export/import thing, that is the way to go...
That said there is another option... in theory you can upgrade to 4.1
keeping your shared table files, then tell each table to "ALTER TABLE
engine=innodb", this will force it to rewrite the table from scratch,
and if you have innodb_file_per_table set, it will be created
accordingly... The benefit here is your downtime is minimal but the
problem is at the end of the day you are still left with your shared
innodb table space, and even though it may be mostly empty, you can't
clean it up and make it smaller.
Best Regards, Bruce
On Aug 23, 2005, at 6:19 AM, Rafal Kedziorski wrote:
Hi,
we have an J2EE application which ist using MySQL 4.0. There is an
bug, which was fixed in MySQL 4.1. We are using tracactions and
InnoDB is don't use query cache. Now we have to migrate our DB to
MySQL 4.1 for use this feature. In our actual installation we store
our data in one inndodb file. After migration we wan't use file per
table.
What is the best and fastest way to make migration?
Best Regards,
Rafal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
