[Tutorial] Moving a [very] large database without any data loss!

[TheGarfield]

Hello everyone,

This will be my "Moving a [very] large database without any data loss" tutorial...

- Before we start:
Once you notice that your database is getting bigger (average 40MB), you should transfer it to the final environment you would run it on, and stay! I mean, moving large databases from a server to another, is NOT healthy or safe for your website! It's always a risk you should take when moving a database!

Another thing, you should know that the bigger your database is, the harder the transfer will be... It will take time but you should really do that carefully, focus on that before you do ANYTHING! Close your phone and turn off the TV! It will take time so be ready!
One last thing before we start, I don't say that the way I'm doing the transfer is the smartest way, nor the easiest way, all I know is that it works!

- Tools: We will need the following tools:
Server:
- PHPMyAdmin if you're using MySQL (or phpPgAdmin if you're using pgSQL) on both old and new server! (for most of hosts, this is included in cPanel, managed servers should have that too!).
- Your old database (that we're going to call Source) and your new database (that we'll call Destination)

Your computer:
- Notepad++, or any text editor (avoid Notepad, it's buggy when you try to open large files). Free, Link to the website
- GSplit, to split large files... Free, Link to the website

Notice:
1. Please follow these things STEP BY STEP! Even if you're a guru.


- Let's start

Step 1:
Close your forum, this is important before you do anything else, you don't want people to post things that are NOT going to be copied! And in general, you don't want surprises when moving to the new server!

Step 2:
Log in to your phpMyAdmin, (or to cPanel if you have it, and then look for phpMyAdmin), if you don't know how to do that, then contact your host support to know how to get on your phpMyAdmin.

Notice: Never tell your host that you're picking up your data and moving, they might block you from doing that! Server change should be done discreetly!

Step 3:
From the left menu of your phpMyAdmin (from now on I'll talk about phpMyAdmin, there's no big difference between it and phpPgAdmin), click on the database that your forum is installed on, (forgot its name? You can still find that information in your 'inc/config.php' file, the $config['database']['datase'] variable), now click "Export" as shown below:



Step 4:
Do the following:
Export method: Select "Custom - display all possible options"



Tables: click 'Select All', push "CTRL" button on your keybord (Windows) and unselect "posts", "privatemessages" and if you have the profile comments plugin, unselect "profilecomments" too as following:






Why those specific tables? They are the biggest tables on your database, posts contains every single post on your forum, privatemessages for every single PM and profilecomments for every single comment! We will take care of them later! (Note: if you have any other plugin that stores a lot of data you might need to unselect it too, check your plugins and try to know if they should be unselected, I will be here for help)


Step 5:
Under the "Output", select "Save output to a file", and then Compression: "gzipped"



Now don't change any other settings, scroll down the page and click "Go" to download the file, need few seconds as generating a GZ file takes time!
Download the file that should be called "your_forum_name.sql.gz" (.gz is the extension so you might not see it) but it's an archive!

Tips:
- For very large forums (over 1GB database), the file generated should NOT exceed 10MB, Otherwise you have plugins that are crowding your database!
- Don't close the phpMyAdmin on your old server

Step 6:
Now go to your destination database, open phpMyAdmin, open your destination database, (if you haven't created one yet, create one, if you have cPanel, go to MySQL databases, create a new database, create a new user and assign the new user to the database, and give that new user all the privileges) << Look for a cPanel MySQL tutorial if you don't know how to do that!

Step 7:
I'm assuming you clicked on your database on the left menu of your phpMyAdmin, click now "Import"



File to import: Select "Browse your computer:" and select the file we just generated from your computer...
Charaster set of the file: UTF8 (don't play around with this!!)

Partial import: UNTICK the "Allow the interruption"....

Then click GO and wait...

If an error says that your file size exceeded the limit in your PHP configuration, you should definitely call your host and ask them to increase the following values, or if you own the server, edit the php.ini file and increase the "upload_max_filesize", "memory_limit" and "post_max_size" values... We just can't work on a database that can't import files that are as big as 5MB!

Step 8:
A success message should appear and you should start recognizing some tables that are on your old database, in fact, all the tables with all the data are there, all we're missing is the posts, privatemessages (and maybe profilecomments if you have that script on your MyBB)
Now, let's get back to the old database (source), and on the same page (Export), we will do the following:

The following steps should be redone for every table we unselected a few moments ago, I will talk about that later:

Step 9:
- Table(s): Unselect All
and the select the posts:



Output: Save output to a file
Compression: GZipped

and now: Data-dump options:
under "Syntax to use when inserting data:"
Select "include column names in every INSERT statement"



The settings above are VITAL!! If you don't do that, your database is going to be a mess! Don't bother finishing this tutorial if you aren't following this step by step!

Click "Go" to download the posts table, note that it will have the same name as the previous download, make the difference between those two files

Now that we have that file, the work begins...
1. Un-gzip it (WinRar/WinZip) do that very well, open the directory when it has been un-gzipped, you should have a file your_db_name.sql

2. Install GSplit and then open it:
Click "Original File" under General from the left menu and click "Browse", select the file "xxxx.sql" that we have un-gzipped.




Now click Type and size under "Pieces", under "Block Pieces properties" select "I want to split after the nth occurrence of a specified pattern"



from the dropdown menu that is just below, select "Split before the occurence number"

enter a value from 5000 to 8000 instead of 10, and "of this pattern", write "INSERT INTO"



The above settings are VITAL!! Especially the "BEFORE" instead of "AFTER"... << If you haven't changed this setting, your files aren't going to be useful anymore and thus you can say goodbye to your data!

Basically what we're doing is that we're going to split the table.sql into many small files, but that are readable and understandable by MySQL and PHPMyAdmin, we should NOT deform the data in anyway!!

Let's carry on:
On GSplit, under the "Pieces", click "Filenames", under "Piece Name Mask", replace disk{num}.gsd by disk{num}.sql




Now, under the "Pieces", click "Other properties", under the "Tags and headers" tab, tick "Do not add GSplit tags to piece files




Our .sql file is ready to be splitted, click "Destination Folder" under "General" to know where your file is going to be splitted, change that if you need, (the software will generate many files)!

Now click "Split File!" under "General"! and hit the "Split!" button! Wait until it's done





Open your destination folder (where the files have been extracted to) and check that they exist!




Now using Notepad++, open the last file generated (that has the biggest number), for example, if the software has generated 10 files, the last one would be disk10.sql, open the last one with Notepad++, you're surprised right?

It contains so much precious information, now scroll down the file, (while scrolling down, make sure that every line starts with "INSERT INTO"

At the bottom, you will find some lines like:

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Delete them, keeping them will generate an error!

Now for each file "disk{num}.sql" generated, you should do the following:

Go to your phpMyAdmin, that is on your Destination server (the new database), click "Import" from the header as shown before, select "Browse your computer:" and select the file from your computer, and then click "Go", wait few minutes because the file isn't very small!



WARNING: You should start by disk1.sql and then disk2.sql ..... until your last file!
NEVER IMPORT FILES RANDOMLY OR FROM LAST TO FIRST!!

WARNING: DO NOT FORGET ANY FILE!! BE VERY VERY CAREFUL ABOUT THAT, YOU SHOULD IMPORT EVERY SINGLE FILE, FROM THE FIRST ONE TO THE LAST ONE! ANY ONE LOST MEANS +8000 POSTS LOST!!! WRITE DOWN A PAPER EVERY SINGLE FILE YOU IMPORT SO THAT YOU ARE SURE YOU'RE NOT FORGETTING ANYONE!!


If everything goes right, you can delete the files disk1.sql, disk2.sql..... (Or change their locations, because you're going to generate others from the other table, you don't want to be confused which file belongs to which table)!!

now go back to the phpMyAdmin on your old server, unselect the table posts and select privatemessages, redo the Step 9 for that new table!

You should redo that step for each large table you have (in my case, it was posts, privatemessages and profilecomments, for a basic MyBB forum that has no plugins on, it should be posts and privatemessages only)


Step 10:
Now that the work on the database has finally ended! We will move to the files:

Download, using an FTP Client (I advice FileZilla), your MyBB Forum files from your old host, to an empty folder on your computer (don't download them to your desktop , there mare more than 1000 files)

Open your '[your folder]/inc/config.php' file, edit the values of these variables:

$config['database']['type'] DON'T CHANGE, $config['database']['database'] YOUR NEW DATABASE NAME (that we were just working on)

$config['database']['table_prefix'] DON'T CHANGE
$config['database']['hostname'], usually 'localhost', but call your host to know more about that!

$config['database']['username'], the username of the user you associated to your database (we talked about that above, but look for a MySQL/cPanel tutorial to know better about that, as that user has to be associated to your database and must have all the privileges!)

$config['database']['password'], the password of the user above!

Save!
Now upload your MyBB Forum files from your computer to your new host (or server) using your FTP client (again, I advice FileZilla).

Notice: if you had anything like a Firewall on a folder or something like that on your old host, you will need to delete the file ".htaccess" that is located in that specific folder, but don't mess up with the general ".htaccess" that is located in your MyBB folder... And then redo another Firewall...

Now go to your website, (if you have done anything like "DNS change" or something, you'll have to wait!!), after you've done that, your website should work fine... Go to your Admin CP, open your forum and there you are!



END

[Yaldaram]

Great Guide!

[seeker]

Great effort TheGarfield, and really good for people on shared hosting, IMO.

Great Guide!

What about a simple 1-2-3?
1) mysqldump/ (can handle large db's, especially if you shut down Apache/Mysql for a brief time)
2) rsync/ (can handle HUGE files)
3) run sql file

[TheGarfield]

Great effort TheGarfield, and really good for people on shared hosting, IMO.

Great Guide!

What about a simple 1-2-3?
1) mysqldump/ (can handle large db's, especially if you shut down Apache/Mysql for a brief time)
2) rsync/ (can handle HUGE files)
3) run sql file

That means you'll work with SHELL, so you're the owner of your server! Well what about those who don't have a SHELL access?

Above I did everything with phpMyAdmin (which is for me "Heaven")
And as I said my solution isn't the most optimized solution, but my old webhost was buggy when it comes to downloading all the database at once, so I had to opt for that solution

[sunjava1]

Good Tutorial

[seeker]

....That means you'll work with SHELL, so you're the owner of your server! Well what about those who don't have a SHELL access?
..........

That's exactly what I said:
Great effort TheGarfield, and really good for people on shared hosting, IMO.

[TheGarfield]

Oh! I just understood something, you actually need to be the root on both the old and the new server if you want to transfer DB with shell, hmmm, that wasn't my case I moved from a host to a server...

but anyway, mysqldump is a very complex command:

mysqldump does not dump the INFORMATION_SCHEMA database by default.
If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead
Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.
(source: MySQL doc)

About rsync:

The Apache HTTP Server only supports rsync for updating mirrors.
rsync -avz --delete --safe-links rsync.apache.org::apache-dist /path/to/mirror
(source: Wiki)


I don't understand a word from what is written above! Maybe you can do a tut to explain how to do a backup transition using your SHELL commands?
(PS: I have a server )

[seeker]

..........
I don't understand a word from what is written above! Maybe you can do a tut to explain how to do a backup transition using your SHELL commands?
(PS: I have a server )

OK, I don't have much time, but will gladly show you.

FYI: INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases... (you do not need this "dumped" for transferring a DB, and I don't think you want to import one from another server either)



#

Moving a very large database without any data loss (the easy way)

1) Choose one of these, depending on what you want to do:

mysqldump -u root -pmypassword_with a 'p' in front --all-databases > root/allfresh_thurs.sql

mysqldump -u root -pmypassword_with a 'p' in front --databases testme3 testme82 > root/allfresh_thurs.sql

or for just 1 db still use "databases" w/ an 's', followed by the name of the one DB. 

2) rsync is amazing, but you need to (really should) have SSH keys set-up for secure login without a password.

Use this code on the server with the file being transferred away:

With port 22 for ssh:
rsync -av -e ssh /root/allfresh_thurs.sql [email protected]:/root/

With Non-standard port:
rsync -av -e 'ssh -p 75xx' /root/allfresh_thurs.sql [email protected]:/root/

Note: rsync often works on shared hosting (place the command as a cron job), but is easiest for transferring (large files and folders, or sync for backup, etc) within the same account, since most shared hosting will not give enough perms to do SSH transfers.


3) Load sql FAST:
mysql -u root -pmypassword_with a 'p' in front < /root/allfresh_thurs.sql


###
Note: The ssh keys/rsync details took some time and effort to learn, at first.
Don't give up, this way is awesome!

[TheGarfield]

oh thank you very much

I just got something, you need to be the root on both servers, :o

I will def. do that when trying to move from my current server to another server...

I appreciated your help

[seeker]

Sure, you are welcome.
Your tut is very helpful for people w/ shared hosting...