2

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)

  • Big Questions : 1) What version of MySQL are you running (SELECT VERSION();) ? 2) Is all the data InnoDB ? 3) Can you stop all writes to the database during maintenance window ? 4) Do you have Linux root access, mysql root access, or neither ? – RolandoMySQLDBA Oct 25 '14 at 22:39
  • On Cpanel server no root access. MySQL version 5.5.37-cll, mysql root access. All tables Mysiam. I do have SSH access, but jailed. – adjc98 Oct 25 '14 at 22:41
  • I have 3 hours for a maintenance window, and can stop the database at that time. – adjc98 Oct 25 '14 at 22:43
  • Can you find out if server_id is explicitly set in my.cnf ? If you have mysql root access, please run SHOW GRANTS;. Does the output of SHOW GRANTS; say GRANT ALL PRIVILEGES ON *.* ... ? – RolandoMySQLDBA Oct 25 '14 at 22:44
  • It shows GRANT ALL PRIVILEGES ON on each of the databases separately and to all databases made in the future by a wildcard: GRANT ALL PRIVILEGES ON server\_%.* TO Checking on server_id – adjc98 Oct 25 '14 at 22:51
2

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

  • I forgot to mention that there are actually four servers. Two files servers and two MySQL servers. The Centos server is not able to connect to the separate MySQL that is affiliated with the Cpanel server because it is firewalled off from the rest of the world. – adjc98 Oct 25 '14 at 23:45
  • If the script works, please mark the answer accepted by clicking on the checkmark located at the upper left corner of my answer. – RolandoMySQLDBA Oct 25 '14 at 23:48
  • I will try running it when I get a chance and get am able to get my firewall open. – adjc98 Oct 25 '14 at 23:55
  • 1
    Don't forget the --events --routines and --compress options in mysqldump. – Michael - sqlbot Oct 29 '14 at 0:58
  • 1
    Hey @Michael-sqlbot, I updated the script. Thanks :-) – RolandoMySQLDBA Oct 29 '14 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.