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:
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:
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