• 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 - 3367 Views
Tags | linux, mysql

You Might Also Like

Saving Bandwidth the Postfix Way

October 26, 2010

Installing LEMP on Ubuntu 14.04 with Nginx and NAXSI

June 20, 2015

Enforcing Microsoft Office 365 and Azure Tennancy with McAfee Web Gateway (MWG)

May 27, 2017

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

  • How to Convert CSV to Parquet Easily with Python on Linux Shell
  • Kusto Geolocation IP Lookup
  • 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

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
  • Kusto Geolocation IP Lookup
    Kusto Geolocation IP Lookup
  • Tinyproxy A Quick and Easy Proxy Server on Ubuntu
    Tinyproxy A Quick and Easy Proxy Server on Ubuntu
  • Monitoring Tor Usage in Azure Sentinel, ASC, MDATP and ALA
    Monitoring Tor Usage in Azure Sentinel, ASC, MDATP and ALA
  • How to DNSPerf on Ubuntu 14.04 with Installation and Quick Start
    How to DNSPerf on Ubuntu 14.04 with Installation and Quick Start
  • How to Add Different Disclaimers using alterMIME and Postfix based on Domain
    How to Add Different Disclaimers using alterMIME and Postfix based on Domain
  • Blocking Countries on Nginx without the GeoIP Module
    Blocking Countries on Nginx without the GeoIP Module
  • How to Enable Squid Anonymous Stealth Mode
    How to Enable Squid Anonymous Stealth Mode
  • Configuring Suite B, VPN-A and VPN-B in IPSec with Strongswan
    Configuring Suite B, VPN-A and VPN-B in IPSec with Strongswan

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