You should be very happy the mysqldump is two-thirds the size of the data. Why?
You should want to move data and indexes without copying unused space at the same time. What aspects would you have to consider by simply importing a mysqldump?
Aspect #1
InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.
Aspect #2
InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:
In light of this, you should keep in mind that a mysqldump is a logical representation of the data and indexes. There is no wasted space to be concerned with. You should not have to scp or rsync bloated InnoDB data or logs. Importing a mysqldump into a fresh installation of MySQL should be what you what want.
I have written about this many times:
Once you have loaded the test database with the mysqldump, you should run CHECKSUM TABLE
on random tables of various sizes. If all tables have the same checksum value, you could certify as good. Make a disklevel backup of the data on the prod server as well.
You can then do this in production
- shutdown mysql
- hose everything (including ibdata1, ib_logfile0, ib_logfile1) in /var/lib/mysql except for /var/lib/mysql/mysql
- Set
innodb_data_file_path=ibdata1:10M:autoextend
in /etc/my.cnf
- restart mysql (this recreates ibdata1, ib_logfile0, ib_logfile1)
- reload mysqldump
Your InnoDB architecture and data will then be lean and mean.
Unfortunately, yes, you'll need to reduce the size of your root LV.
(You could reduce the size of swap, too, but that's already a fairly small volume)
Even if you can use that 4MB, a snapshot of your root volume will become invalid quite quickly when LVM runs out of "free" disk space.
You get prompted with a text box asking how much of your disk to use when you do an Ubuntu install (even if you select "use entire disk
"). If you're building systems to muck around on, or virtual machines, I recommend setting this to 90%
(leaving 10% for LVM) - you probably won't miss the space, and you never know when it'll come in handy.
The mechanics of how to shrink the root volume aren't too hard to find on the web, but questions about that are probably best suited to unix.se.
Update: Make sure you don't rely on LVM as your only backup strategy - if you change enough data on disk and your snapshot runs out of free LVM space to use, the snapshot will simply stop working. Using LVM is one of the only ways you can take file-based backups of MySQL (esp. in conjunction with FLUSH TABLES WITH READ LOCK
) without shutting down the server, though, which is quite handy.
Best Answer
You can use following steps
On old Server
1.Stop mysql server
2.Copy contents of datadir to another location on disk as ... mysqlbackup
3.Start mysql server again
4.Compress the data (tar -czvf mysqlbackup.tar.gz mysqlbackup)
5.Copy the compressed file to new server
On New Server
1.Install MySQL [MySQL version should be same as of old server](don't start MySQL server)
2.Unzip compressed file (tar -xzvf mysqlbackup.tar.gz)
3.Move contents of mysqlbackup to the datadir.
4.Make sure that permissions of datadir are correct
5.Make sure your innodb_log_file_size is same on new server, or if it's not, don't copy the old log files (MySQL will generate these)
6.Start MySQL.
You can also look at How can I move a database from one server to another?