MySQL – Fastest Way to Move Multiple Databases to a New Server

migrationMySQL

I have several small to medium sized mysql databases, say about 40 and I need to migrate them from one whm server with different cpanel accounts to another one with the accounts set up from the previous server and old versions of the databases I am moving already in place.

Could anyone recommend the quickest way of doing this, I was going to manually dump each one and import it across but it seems very time consuming, I would like to cut out my machine as the middle man if possible and automate were I can.

Best Answer

I do not know much about cPanel, but I know how to transfer a database very fast to another server - if you have access to ssh.

use mysqldump with the proper parameters and chain it with ssh. so the database is imported while the sorce database still exports. no temporary files are used (except mysql internally ;))

sourceserver# mysqldump --user=user1 --all-databases | ssh targethost 'mysql --user=user2'

if you authenticate to the source server with your private key and ssh-agent, you could use the -A option of ssh to connect. So you do not need to care about authorization on target side. But keep in mind:

         Agent forwarding should be enabled with caution.  Users with the
         ability to bypass file permissions on the remote host (for the
         agent's Unix-domain socket) can access the local agent through
         the forwarded connection.  An attacker cannot obtain key material
         from the agent, however they can perform operations on the keys
         that enable them to authenticate using the identities loaded into
         the agent.

(source: man 1 ssh)

hope this helps a bit

Related Solutions

MySQL – Export and Import Privileges to a New Server

Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

The first mysql command uses SQL to generate valid SQL which is piped to the second mysql command. The output is then piped through sed to add pretty comments.

The $@ in the command will allow you to call it as: mygrants --host=prod-db1 --user=admin --password=secret

You can use your full unix tool kit on this like so:

mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret

That is THE right way to move users. Your MySQL ACL is modified with pure SQL.

Mysql – Fastest way to move MySQL database to a new system while minimizing downtime

I would suggest:

Taking a snapshot whilst briefly in read lock with FLUSH TABLES WITH READ LOCK;.
Bring that dataset up on your secondary machine and check consistency.
Setup replication from the old machine to the new.

Then at your cutover point:

Bring down the client facing IP on the old machine (you have a separate one, right?).
Issue FLUSH LOGS; on the old machine.
Ensure the new machine is in sync. 0 seconds behind.
Stop the old machine and double check the last binlog size against the new machine's position.
Issue STOP SLAVE; RESET MASTER; on the new machine.
Bring up the client facing IP on the new machine and arping to ensure the clients see it.

There are a few finer details, such as whether you are a heavy InnoDB user. But that is the general jist.

Best Answer

Related Solutions

MySQL – Export and Import Privileges to a New Server

Mysql – Fastest way to move MySQL database to a new system while minimizing downtime

Related Topic