Geek Answers Handbook

Moving InnoDb Database

I have an InnoDb DB innodb_db_1. I have included innodb_file_per_table.

If I go to var/lib/mysql/innodb_db_1/, I will find the files table_name.ibd, table_name.frm, db.opt.

Now I am trying to copy these files to another database, for example, to innodb_db_2( var/lib/mysql/innodb_db_2/), but nothing happened.

But if my database is MyIsam, I can copy this way and everything will be fine.

What are some suggestions for moving the database by copying the InnoDb database file?

+4
source share
4 answers

, /var/lib/mysql/ibdata 1. , .ibd MySQL.

. :

:

, .ibd. ibdata1. , , , . InnoDB, , .

.ibd MySQL, . InnoDB , .

, ALTER TABLE mytable ENGINE=MyISAM, .frm , ALTER TABLE mytable ENGINE=InnoDB, . FLUSH TABLES WITH READ LOCK MyISAM.

. , , . - .

+4

, - ( MyISAM, , ).

: mysqldump. :

mysqldump -h yourHost -u yourUser -pYourPassword yourDatabase yourTable > dumpFile.sql

:

mysql -h yourHost -u yourUser -pYourPassword yourNewDatabase < dumpFile.sql

: mysqldump - .

InnoDB , : InnoDB

+3

MyISAM (, ), InnoDB, -.

  • MyISAM , .

  • InnoDB , (ibdata1). , .ibd, .

MySQL 5.6 . MySQL 5.6, , .

:

  • mysqldump [options] database_name > dumpfile.sql --databases, , DATABASE (DROP DATABASE, CREATE DATABASE USE), , , . mysql [options] < dumpfile.sql.

  • CREATE TABLE db2.t1 LIKE db1.t1; INSERT INTO db2.t1 SELECT * FROM db1.t1; ( )

  • ALTER TABLE , MyISAM, FLUSH TABLES WITH READ LOCK;, , InnoDB. , , .

+2

Mysql InnoDB pc A pc B.

:

  • innodb_file_per_table

MySql 150 ( aprox. 60Gb). sqldumps ( ).

, , " " mysql (mysql doc), .

, , dba stackexchange.

I write this because (assuming you can follow the indicated conditions), this is the fastest (and easiest) way to move the (large) MySql InnoDb, and no one has mentioned it yet.

+1
source

More articles:


All Articles