Posted on
by

mysql-logo3 So, the other week, I migrated all my sites over to a new server. This was accomplished with minimum fuss, using lots of rsync magic and juggling of DNS ttls.

The part of the migration I imagined would be the most complicated, moving several tens of MySQL databases running on the old server to the new, turned out to be pretty straightforward, and essentially completed with one command. I thought others might find it handy to know how…

First, I brought down Apache on both servers, so I could be sure that nobody was going to try and write to the database. This may not really be required, since mysqldump can handle dumping live databases consistently, for MyISAM at least, but in never hurts to be paranoid.

Next, I needed to move all the databases, together with their access permissions and users (so the full MySQL schema) to the other server. One way to do that is to copy the whole /var/lib/mysql directory over. However, I had a lot of cruft in there (old bin-logs etc), plus there were a number of articles suggesting that the straight binary copy had a number of issues, especially for mixed storage engine environments. So, I opted for the mysqldump method.

Traditionally, this takes a lot of SCPing. Here’s how to do it with one command, using the magic of Unix pipes:

mysqldump -u root -pPASSWORD --all-databases | ssh USER@NEW.HOST.COM 'cat - | mysql -u root -pPASSWORD'

Boom. This ran surprisingly quickly for me, and you can of course just as easily use this method to transfer a single database.

Three gotchas:

  • If the link dies, you need to start again, so don’t do this over a flakey connection, and I suggest you run the command in a screen if the first server isn’t localhost.
  • You need to restart the database server on the target machine for the new users and privileges to come into effect.
  • On Debian, you may see an error along the lines of:

    Got error: 1045: Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES) when trying to connect

    Fix this by executing the command:

    GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'THEPASSWORD' WITH GRANT OPTION;

    Where THEPASSWORD is the password found in /etc/mysql/debian.cnf

One final note: this will only work if both servers are the same major version number. I was moving between two Debian 6.0 installs, so YMMV.

Happy Easter!

17 thoughts on “Moving a MySQL database between servers using a single SSH command”

  2. Thanks for posting Marcus! I couldn’t believe this could be done in one line but it worked a treat. Darn quick too for small databases.

    I did this from CentOS 6 to RHEL6. I nearly missed your point about the new users and passwords. Restarting mysqld is an essential step.

    Thanks again!!

  4. Copy files with tar, this preserves time/date stamps and permissions nicely.

    tar cf - * | ( cd /target; tar xfp -)

    Backup and a restore a mySql database:

    mysqldump -u username -ppassword database_name > FILE.sql
mysql -u username -ppassword database_name < FILE.sql

    Or, perhaps you want to move a bunch of mysql databases from one server to another in one line? Yes you do, and thanks to Marcus here’s how:

    mysqldump -u root -pPASSWORD --all-databases | ssh USER@NEW.HOST.COM 'cat - | mysql -u root -pPASSWORD'

    List the most visted IP addresses in a default Apache access.log:

    less access.log | cut -d '-' -f 1 | sort -r | uniq -c | sort -gr | head -20

    Figure out which process is “grabbing” a drive you want to unmount (from Donncha):

    # umount /media/disk/
umount: /media/disk: device is busy
# fuser -m /dev/sdc1
/dev/sdc1: 538
# ps auxw| grep 538
donncha 538 0.4 2.7 219212 56792 ? SLl Feb11 11:25 rhythmbox

    Sometimes when pasting code in vi, it over-indents. Vim.org has the answer for this:

    > All you need to do is "set paste", then paste your stuff, and then "set nopaste" again.

     

    That’s all so far.

  12. Many many tanks. This work perfect and success to transfer 3 Gb in 37 DB from fedora 15 with MySQL version 5.1.51-log to fedora server 21 with mariadb 10.0.15-MariaDB.

  13. Copiar Bases de Datos MySQL entre servidores
    leave a comment »

    Tanto trabajo que pasé el otro día y BUM! La respuesta era sencilla
    mysqldump -u root -p CLAVE –all-databases | ssh root@NUEVO_SERVIDOR ‘cat – | mysql -u root -p_CLAVE’
    Esto pedirá la clave de ssh del usuario que se utilizó y todo copiado.
    Luego va a tirar un error ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)
    La solución es en línea de comando de MySQL:
    GRANT ALL PRIVILEGES ON *.* TO ‘debian-sys-maint’@’localhost’ IDENTIFIED BY ‘THEPASSWORD’ WITH GRANT OPTION;
    La copia ideal es entre dos servidores con mismo sistema operativo (y misma versión de MySQL)
    El link original aquí
    Share this:TwitterCorreo electrónicoImprimirMe gusta:Me gusta Cargando…

    Relacionado

    Written by scrooge_74
    junio 9, 2015 a 10:40 am

    Publicado en comentarios

  14. Copier tout un serveur MySQL et ses bases de données vers un autre serveur

    Si vous avez besoin d’une solution Quick & Dirty pour copier toutes les bases de données d’un serveur MySQL vers un autre, voici la commande à utiliser…
    On l’exécute depuis le serveur source:

    ?Download copier-contenu-mysql-versautreserveur.txt1
    mysqldump -u root -pMOTDEPASSE –all-databases | ssh utilisateur@serveurdedestination ‘cat – | mysql -u root -pMOTDEPASSE’

    Attention il faut bien remplacer les textes “motdepasse” par les mots de passe de l’utilisateur root MySQL de vos serveurs et “utilisateur@serveurdedestination” par l’utilisateur et le serveur cible.
    Astuce découverte chez Marcus
    Partager :Pocket

    Tags: MySQL

    Publié par Gonzague Dambricourt le dimanche 8 février 2015 – 10:19 dans la catégorie Informatique et a 377 vues.

    Vous pouvez commenter ce billet mais les trackbacks sont désactivés.

Leave a Reply