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.
cd /rootmkdir sql-dumpcd 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
cd /rootmkdir sql-dumpcd 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]panya.com:/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!
No Comment