2.11.13 Copying MySQL Databases to Another Machine
In cases where you need to transfer databases between different architectures, you can use mysqldump to create a file containing SQL statements. You can then transfer the file to the other machine and feed it as input to the mysql client.
Use mysqldump --help to see what options are available.
The easiest [although not the fastest] way to move a database between two machines is to run the following commands on the machine on which the database is located:
mysqladmin -h 'other_hostname' create db_name
mysqldump db_name | mysql -h 'other_hostname' db_nameIf you want to copy a database from a remote machine over a slow network, you can use these commands:
mysqladmin create db_name
mysqldump -h 'other_hostname' --compress db_name | mysql db_nameYou can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this:
mysqldump --quick db_name | gzip > db_name.gzTransfer the file containing the database contents to the target machine and run these commands there:
mysqladmin create db_name
gunzip < db_name.gz | mysql db_name You can also use mysqldump and mysqlimport to transfer the
database. For large tables, this is much faster than simply using mysqldump. In the following commands, DUMPDIR represents the full path name of the directory you use to store the output from
mysqldump.
First, create the directory for the output files and dump the database:
mkdir DUMPDIR
mysqldump --tab=DUMPDIR db_name Then transfer the files in the DUMPDIR directory to some corresponding directory on the target machine and load the files into MySQL there:
mysqladmin create db_name # create database
cat DUMPDIR/*.sql | mysql db_name # create tables in database
mysqlimport db_name DUMPDIR/*.txt # load data into tables Do not forget to copy the mysql database because that is
where the grant tables are stored. You might have to run commands as the MySQL root user on the new machine until you have the mysql database in place.
After you import the mysql database on the new machine, execute mysqladmin flush-privileges so that the server reloads the grant table information.
Note
You can copy the .frm, .MYI, and .MYD files for MyISAM tables between different architectures that support the same floating-point format. [MySQL takes care of any byte-swapping issues.] See Section 15.2, “The MyISAM Storage Engine”.
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: 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: 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:mysqldump -u root -p --opt [database name] > [database name].sqlmysql -u root -p newdatabase < /path/to/newdatabase.sql
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 './/' [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!!
Standard