1

I need to move a MySQL DB to a new location. The DB is only 200MB in size according to workbench, but there are some tables with millions of rows which report indexes and data length of several GB. As a result the EXPORT is 15GB in size, and it takes many hours to export the DB, and copy it to new location, and many more again to import it. During this process the website needs to be offline so that the database is not changed. First attempt too over 36 hours, and the import failed. This is simply too long, is there any better/quicker way to do this then the horrible import/export? I am used to working with MSSQL which is very quick to do this sort of thing, take db offline, copy the file to new location, mount DB, done. it takes minutes and is so easy. Never understood why MySQL makes this so hard.

snake
  • 732
  • 1
  • 6
  • 11
  • Which MySQL engine are you using? MySQL? InnoDB? other? – Jocelyn Jul 31 '16 at 13:39
  • You can do the same with MySQL, "take db offline copy the *db files* to new location, mount DB, done". But please make sure that you have same version of mysql installed and while copying the db files, no read write operations are being done. – thekosmix Aug 01 '16 at 06:09
  • there is no such option with MySQL, the INNODB databases are not in separate files. It stores all data for all databases in same file. So I cannot do this as there are many other databases on the server. – snake Aug 10 '16 at 08:22

1 Answers1

0

Percona XtraBackup is a useful tool, if you have InnoDB tables innobackupex will help to make a hot-backup and restore.

Also configuring InnoDB File-Per-Table the backup will be like MyISAM tables.

If you have MyISAM tables the process is:

  1. Stop server origin server.
  2. Copy (and compress) your database folder in a secure place, for example: /var/lib/mysql/your_database.
  3. Restore the files in the same place in destination server after stop MySQL server.
  4. Start destination server.

Both servers must have a minimal difference in version because MyISAM have their own format version.

Community
  • 1
  • 1
Ivan Cachicatari
  • 4,212
  • 2
  • 21
  • 41