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

Sojourning Dinosaur

Veritas omnia vincit 信使可覆

Copying or moving all of MySQL databases

August 25, 2016 by mhan Leave a Comment

This is just one of the ways you can move all of the MySQL databases from one server to another.  This was tested on Ubuntu 16.04.01 LTS (xenial) distro.

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 SQL dump.

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

IMPORTANT: If your file is large, or you just have a lot of records, you may want to make sure you have something bigger than 16M for max_allowed_packet attribute in your my.cnf (usually found under /etc/mysql/ or /etc/mysql/mysql.conf.d/) on your new server where you’re doing the import, else the server could hang on a large insert operation and your MySQL server may actually decide to go away, literally.  On one of the servers I had it for 1024M just for this operation and brought it back low afterwards.

Posted in: System administration Tagged: 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