MySQL Database Migration – Moving Large Databases with Mysqldump

backupmigrationMySQLmysqldump

I need to move a mysql database that is about 17 GB that is on a Cpanel/Centos based server that I do not have root access, to a Centos server that I have full root access. I do have limited SSH on the Cpanel with mysqldump available(I think).

When doing a test database download from the Cpanel Mysql backup utility, the server ran out of ram and shut down. I only did about 250 MB as a test for the download.

My choices seem to be:

Master/Slave replication(I don't have root so that seems out)
Rsync the database files(I don't have root so that seems out)
Mysqldump
PhpMyadmin(Crashes the server when the tables have too many rows)

At this point I am planning on doing a mysqldump from the Cpanel server. I don't want to have it locked down for a super long time, since our website is live. I saw that I can use the mysqldump with the -r and it will cause the buffer to go row by row instead of loading the whole table into the buffer. Some of the tables have 2 million rows. Most are under 50 thousand rows.

What is the best way to move the database without locking down the system or causing the server to crash because of the ram being overloaded?

Both servers have powerful CPU's and 16 GB of ram with fairly current MySql versions.

Would you recommend a table by table dump or is that even necessary?

I might be able to get the current Cpanel host to setup my.cnf for me to set up the master slave connection(Not sure about that one though)

Best Answer

What you need to do is mysqldump separate tables in batches and load them.

On your new CentOS server, write the following

MYSQL_SOURCE_HOST=10.20.30.40
MYSQL_SOURCE_USER=root
MYSQL_SOURCE_PASS=rootpassword
SRC_CONN="-h${MYSQL_SOURCE_HOST} -u${MYSQL_SOURCE_USER} -p${MYSQL_SOURCE_PASS}"

MYSQL_TARGET_HOST=localhost
MYSQL_TARGET_USER=root
MYSQL_TARGET_PASS=rootpassword
TGT_CONN="-h${MYSQL_TARGET_HOST} -u${MYSQL_TARGET_USER} -p${MYSQL_TARGET_PASS}"

SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema = 'xcart'"
SQL="${SQL} ORDER BY data_length"
mysql ${SRC_CONN} -ANe"${SQL}" > /tmp/ListOfTables.txt

COMMIT_COUNT=0
COMMIT_LIMIT=10
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers --events"
for TBL in `cat /tmp/ListOfTables.txt`
do
    mysqldump ${SRC_CONN} ${MYSQLDUMP_OPTIONS} xcart ${TBL} | mysql ${TGT_CONN} -Dxcart &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ] ; then wait ; fi

I have other ideas from my old post : How can I optimize a mysqldump of a large database?

Give it a Try !!!

Related Solutions

How to Set Up a MySQL Slave Using Mysqldump

If you are dumping a mysql database that has a mixture of InnoDB and MyISAM and you have scheduled downtime:

mysqldump -u... -p... --master-data=1 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

Since --opt is enabled by default, the following options are already enabled

--add-drop-table
--add-locks
--create-options
--quick
--lock-tables
--set-charset
--disable-keys

If you are concerned about InnoDB/MyISAM together in the mysqldump and you want the database placed in a read-only state, try putting a read lock across all tables manually.

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

The reason this is better to do is that --single-transaction does not protect MyISAM from changing during the dump.

Before launching the mysqldump, make sure binary logging is enabled on the master. If it is not, do the following:

Step 01) Add this to /etc/my.cnf on the master

[mysqld]
log-bin=mysql-bin

Step 02) service mysql restart

This will enable binary logging on the master.

On the new slave, you can run the following command:

CHANGE MASTER TO
MASTER_HOST='IP of the master',
MASTER_PORT=3306,
MASTER_USER='whatever_username',
MASTER_PASSWORD='whatever_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

Make sure the rep_username exists in the master. If it does not, run this command on the master:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT
ON *.* TO 'whatever_username'@'%' IDENTIFIED BY 'whatever_password';

Then do the mysqldump to /root/mydata.sql.

Move the /root/mydata.sql from the master to the slave.

Next, execute in the mysql client this command on the slave:

source /root/mydata.sql

This will load mysqldump in the slave and the set the correct log file and log position in /var/lib/mysql/master.info.

Finally, run START SLAVE; on the slave and mysql replication should get going.

Run SHOW SLAVE STATUS\G to check replication status.

Give it a Try !!!

MySQL Migration from Slave to Master

I was left wondering because you mentioned SSH which implied connecting from your database server to a different machine (free tip: You use "SSH" to connect to a machine, but the things you type after that are "shell commands", not "SSH commands." I googled that phrase just now and was horrified at how frequently they're improperly called "SSH commands") ... and in Sqlbot's world, a "slave" is a different machine -- a replication slave -- which also was initially confusing.

You mentioned MYD and MYI files but not IBD files, so either you are using MyISAM and not InnoDB, or you're using a mix but you never set innodb_file_per_table = 1.

Your path to recovery depends on whether you are using InnoDB.

If you're not using InnoDB, then you can -- believe it or not -- just copy the old database directories and files into place and fix the permissions if needed. MySQL is extremely forgiving about this with MyISAM tables.

Connect to your MySQL server and locate the data directory. From an old CentOS machine of mine (probably still the same for you):

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

Find the comparable directory on your old hard drive, and copy all of the directories -- but not the files, and not the "mysql" directory -- into your datadir (probably /var/lib/mysql).

Then, if the files aren't owned by the "mysql" user,

root@host# chown -R mysql:mysql /var/lib/mysql

Then see if it worked.

mysql> SHOW DATABASES;

If you are using MyISAM only, your databases and tables should be on the new server, though you will need to do CHECK TABLES on all of your tables or use the mysqlcheck utility to verify that they are all intact and repair them as needed.

If you're using InnoDB, the steps will be different, so see if this works for you.

Best Answer

Related Solutions

How to Set Up a MySQL Slave Using Mysqldump

MySQL Migration from Slave to Master

Related Question