• Home
  • The Song
  • The Avatar
  • The Cat
  • Contact the Cat

Gyp the Cat dot Com

Migrate MySQL Servers with Databases and Users
Computers, internet

Migrate MySQL Servers with Databases and Users

Moving databases between one MySQL server and another isn’t always easy, especially if you’re migrating database servers from one server to another.  Imagine for this tutorial that you want to take an entire MySQL setup and put it on another server.  We will also be exporting all the user accounts too, very useful for instance if we have lots of websites in our database.  This tutorial will show you how to do just that.

This tutorial has been written for Ubuntu 14.04 but it should be ok on the distro of your choice.  We will assume that you have a working MySQL database up and running.

We will be referring to:

  • Old Server – oldserver – This is the device you want to migrate the MySQL databases from
  • New Server – newserver – This is the new device you want the copied databases on

On the Old Server

SSH on your box as usual and we will create some working directories.

ssh [email protected]
cd /root
mkdir sql-dump
cd sql-dump
Now we will need to enter the MySQL configurator:
mysql -uroot -p
You should be prompted for your password and see something like the following:
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 132480
Server version: 5.5.43-0ubuntu0.14.04.1 (Ubuntu)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
At the command line enter the following to put our database in Read Only mode, please note that this step is optionally but you may want to do it if you have a very busy database and don’t want to lose any records.
set global read_only = on;
You should been provided with a response like the following:
Query OK, 0 rows affected (0.52 sec)
Type the following to exist out of the MySQL terminal:
quit
Now we will do a dump and compress it at the same time, this command takes all our databases, all our tables and all our user accounts and puts them all in a single sql file.
mysqldump –lock-all-tables -u root -p –all-databases | bzip2 -c > mysql-dump.sql.bz2
Enter your MySQL password when prompted.  You may want to go and get yourself a drink, because this command can take a while to run!
When it’s finished move onto your New Server.

On the New Server

ssh [email protected]
cd /root
mkdir sql-dump
cd sql-dump
This will let us create a working folder structure which we will use for the databases.
Now we need to grab the dump we did on the old server and move it onto this server.
scp [email protected]:/root/sql-dump/mysql-dump.sql.bz2
Extract that file:
bzip2 -d mysql-dump.sql.bz2
Import that file into our database:
mysql -u root -p < mysql-dump.sql
Enter your password when requested, and again if you need to get yourself a drink, now may be a good time to do that.
When it’s done your databases should be successfully imported!  The last thing we will need to do is to allow writes to our databases if we put them in read only mode before we migrated from the old server.
Enter the following:
mysql -uroot -p
Input your password when requested, and then type in:
set global read_only = off;
quit
Your databases should now be happily on your new server!

Related

Written by gyp - May 31, 2015 - 2236 Views
Tags | linux, mysql

You Might Also Like

Raspberry Pi Pyrit Benchmark

Pyrit Benchmark for Raspberry Pi

January 27, 2013

Strongswan IPSec (Including Cryptomap) to Microsoft Azure Virtual Network Gateway

March 15, 2019

How to Create a Site to Host VPN on Ubuntu for AWS, Azure and Linode with pfsense

October 3, 2015

No Comment

Please Post Your Comments & Reviews
Cancel reply

Your email address will not be published. Required fields are marked *

Previous Post
Next Post

Latest Posts

  • Monitoring Tor Usage in Azure Sentinel, ASC, MDATP and ALA
  • HTTP to HTTPS Redirect on Azure CDN
  • Strongswan IPSec (Including Cryptomap) to Microsoft Azure Virtual Network Gateway
  • Black Ops 3 NAT Type Strict & PS4 NAT Type 3 with pfSense Fixed!
  • Sorry for the lack of posts
  • How to Block Internet Access with Group Policy (GPO)
  • Enforcing Microsoft Office 365 and Azure Tennancy with McAfee Web Gateway (MWG)
  • Scanning Subnet for Issuing Certificate Authority with OpenSSL
  • How to Configure Windows 2012 NPS for Radius Authentication with Ubiquiti Unifi
  • Steam & Valve IP Ranges

Top Posts & Pages

  • How to Block Internet Access with Group Policy (GPO)
    How to Block Internet Access with Group Policy (GPO)
  • How to Configure Windows 2012 NPS for Radius Authentication with Ubiquiti Unifi
    How to Configure Windows 2012 NPS for Radius Authentication with Ubiquiti Unifi
  • ISEB Business Analysis (BA) Revision Notes
    ISEB Business Analysis (BA) Revision Notes
  • How to Add Different Disclaimers using alterMIME and Postfix based on Domain
    How to Add Different Disclaimers using alterMIME and Postfix based on Domain
  • Bonding Interfaces on Ubuntu 12.04LTS
    Bonding Interfaces on Ubuntu 12.04LTS
  • How to fix Mail Loops Back to Myself
    How to fix Mail Loops Back to Myself
  • Installing Nginx (LEMP) on Ubuntu 11.10
    Installing Nginx (LEMP) on Ubuntu 11.10
  • Why Does Lightening McQueen Stick His Tongue Out?
    Why Does Lightening McQueen Stick His Tongue Out?
  • Using SSH as a SOCKS VPN on Mac OS
    Using SSH as a SOCKS VPN on Mac OS
  • Strongswan IPSec (Including Cryptomap) to Microsoft Azure Virtual Network Gateway
    Strongswan IPSec (Including Cryptomap) to Microsoft Azure Virtual Network Gateway

Tags

apache2 azure azure log analytics blops business centos cheating cissp cloudflare cryptography dns game google gyp internet iphone ipsec isc linux mac marketing microsoft mw2 mx mysql nginx pfsense postfix proxy ps3 qualification radius revision security seo smtp socks squid ssh strongswan tinyproxy ubuntu windows 2012 wordpress xdecrypt.com
Gyp the Cat dot Com

Some rights retained Gyp the Cat Dot Com