• Home
  • Logos (λόγος)
  • Haaretz (הארץ)
    • Recipes
    • 魚
  • Kendo (剣道)
  • Development
  • Wiki
  • 韓承佑

Sojourning Dinosaur

Veritas omnia vincit 信使可覆

Moving all of the databases from one server to another

August 17, 2016 by mhan Leave a Comment

Log in as an admin on MySQL Console and lock the database to allow only read operations.

mysql> flush tables with read lock;
mysql> set global read_only = on;
mysql> exit

Dump all of the databases into a file.

$ mysqldump --lock-all-tables -u root -p --all-databases > dbs.sql

Copy the dump to the new server. RSYNC is preferred over SCP, especially if the file is large.

$ rsync -tvz --progress dbs.sql mhan@newserver.com:~/files/
or
$ scp dbs.sql mhan@newserver.com:~/files/

The DB can be (optionally) unlocked. This may or may not be a good thing to do in your case. Do it at your own risk.

mysql> set global read_only = off;
mysql> unlock tables;
mysql> exit

On the new server, execute this command to import the new dumped SQL file.

$ mysql -u root -p < ~/files/dbs.sql

https://wiki.michaelhan.net/MySQL

Posted in: Development Tagged: development, mysql, sysadmin
Visit Us On TwitterVisit Us On FacebookVisit Us On Linkedin

TEMPUS FUGIT

December 2019
S M T W T F S
« Oct    
1234567
891011121314
15161718192021
22232425262728
293031  

Recent Posts

  • Notes on [brain] fatigue
  • Notes on neck health
  • Wi-Fi 6 is coming
  • poetryforlifeforever.com
  • Googlebot searching far in the future

Recent Comments

    Archives

    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • February 2019
    • January 2019
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • February 2017
    • January 2017
    • October 2016
    • September 2016
    • August 2016
    • July 2016

    Categories

    • AI
    • Development
    • Haaretz (הארץ)
    • Kendo (剣道)
    • Logos (λόγος)
    • Recipes
    • System administration
    • Uncategorized

    Copyright © 2016 ~ 2019 Michael Han