• 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

TEMPUS FUGIT

January 2021
S M T W T F S
 12
3456789
10111213141516
17181920212223
24252627282930
31  
« Nov    

Recent Posts

  • 一以貫之
  • 濫觴
  • 갚아야 할 빚만 있는 삶
  • 아브라함과의 언약
  • Ταχύτητα

Recent Comments

    Archives

    • January 2021
    • November 2020
    • October 2020
    • September 2020
    • July 2020
    • June 2020
    • May 2020
    • March 2020
    • 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
    • Current events
    • Development
    • Haaretz (הארץ)
    • Kendo (剣道)
    • Lexicons
    • Logos (λόγος)
    • Recipes
    • System administration
    • Uncategorized

    Copyright © 2016 ~ 2021 Michael Han