mysql

Moving complete mysql from one server to another without mysqldump

Whenever it comes to transfer database (mysql) from one server to other, the first obvious choice that comes to one’s mind is mysqldump. All you need to do is run the command:

mysqldump -u root -p --opt [database name] > [database name].sql

It will output a sql file for your database. Scp the sql file to your new servers, create a database with the same name in the new machine and run this:

mysql -u root -p newdatabase < /path/to/newdatabase.sql

All is well unless your database is of limited size. But suppose your database is in GBs meaning you’re f’ckd!! This method will still work but it will take years (not literally) to generate all the tables and fields in the new database using the dumped sql file. Also it will eat up a lot of resources on your new server.

The alternative is rather way too simple. All you need to do is go to your mysql data directory. If you are using ubuntu, most probably you are looking for this location:

/var/lib/mysql/

Before proceeding ahead, you may want to stop your mysql server. Just run this:

sudo service mysql stop

Also as we are going to access files that need root permissions, better do:

sudo bash

We will now zip all the contents of the mysql data directory.

zip -r completemysql.zip /var/lib/mysql/*

This is will create a zip file containing all you mysql data. Now scp this to the new servers (or machine).
Once you ssh to your new server with the zip file already t transferred in its home. Unzip it in a directory(we name it ‘database’)

unzip completemysql.zip -d database

Now delete all the log files from the unzip location ( ~/database in our case).
It should look something like:

rm ib_logfile0 ib_logfile1

Before proceeding ahead we need to stop the mysql server. Run:

sudo service mysql stop

to confirm the mysql server stopped do:

mysqladmin -u root -p status

If this gives an error, it means the mysql server is not running, which is what we want in our case.
Now copy all the files from ~/database directory to the mysql data directory of this machine.

cp -R ~/database/* /var/lib/mysql/

We are almost there, if at this moment you start your mysql server and try show databases it will list all your databases from the previous server. However when you try to access them, you may not be able to do so and get following permission error :

ERROR 1018 (HY000): Can't read dir of './<your_database_dir>/' (errno: 13)

This is because the owner of the copied files is the ‘root’ and we need to make it ‘mysql’. We can resolve this by:

chown -R mysql:mysql /var/lib/mysql/
chmod -R 755 /var/lib/mysql/

Now start the mysql server and its done.

sudo service mysql start

cheers!!

Advertisements
Standard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s