a logic equipped proud solider of thought (nichiyume) wrote in gotroot,
a logic equipped proud solider of thought

  • Mood:
  • Music:

I like the times I get to feel geeky

Here is my story of how I copied the databases, and am restoring them on the new server.
first I had the databases left to copy...

mysql>show databases -> ;
| Database                            |
| admin_appl                          |
| arizonabpm_com_-_forums             |
| arizonabpm_com_-_gallery            |
| azcoyote_com_-_gallery              |
| azhardcorejunkies_com_-_main        |
| blargatron-systems_com_-_blargatron |

| bonedrypaintball_com_-_main         |
| bonedrypaintball_com_-_osc          |
| bonedrypaintball_com_-_stats        |
| cheddarcore_com_-_phpbb2            |
| cyborgcow_net_-_chat                |
| cyborgcow_net_-_data                |
| cyborgcow_net_-_djskott             |
| cyborgcow_net_-_hippie              |
| cyborgcow_net_-_wolfpack            |
| energy_cyborgcow_net_-_istb         |
| killerbunnies_net_-_forums          |
| legalizemarijuananow_org_-_lmn      |
| mysql                               |
| political-nutshell_com_-_akju       |
| political-nutshell_com_-_forums     |
| projectargo_com_-_coppermine        |
| projectargo_com_-_phplist           |
| projectargo_com_-_rwdownload        |
| sigonomicron_com_-_mainsite         |
| starstrukdezigns_com_-_site         |
| test                                |
| thehotpink_com_-_phpbb              |
28 rows in set 0.41 sec)
I need to copy all of the database into a scripted output for its restoring on the new server. There exists a quick program for this, mysqldump. And so I thought, lets use it!
root@moo:/home/riii[root@moo riii]# mysqldump -opt -u root -p cheddarcore_com__phpbb2 > cheddarcore
mysqldump: invalid option -- o

Now here I thought I was doing everything allright, piping the output into a .dump file for our enjoyment, but something wasnt right.
[root@moo riii]# mysqldump --opt -u root -p cheddarcore_com_-_phpbb2 > cheddarcore_com_-_phpbb2.dump

There I had forgotten the extra - infront of --opt. From the manual: "This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly."

This is fine and dandy for one, but I have 28 databases to copy. Unfortunately I'm not a bash scripter, and Tedde wasnt around at the moment to throw one together so I used alot of copy and paste and did the command:
root@moo:/home/riii/mysqlbackup[root@moo mysqlbackup]#
mysqldump --opt -u root -p arizonabpm_com_-_forums > ari
zonabpm_com_-_forums.dump;mysqldump --opt -u root -p arizonabpm_com_-_gallery >
arizonabpm_com_-_gallery.dump;mysqldump --opt -u root -p azcoyote_com_-_gallery
> azcoyote_com_-_gallery.dump;mysqldump --opt -u root -p thehotpink_com_-_phpbb
> thehotpink_com_-_phpbb;mysqldump --opt -u root -p starstrukdezigns_com_-_site
> starstrukdezigns_com_-_site.dump;mysqldump --opt -u root -p projectargo_com_-_
rwdownload > projectargo_com_-_rwdownload.dump;mysqldump --opt -u root -p projec
targo_com_-_phplist > projectargo_com_-_phplist.dump;mysqldump --opt -u root -p
projectargo_com_-_coppermine > projectargo_com_-_coppermine.dump;mysqldump --opt
-u root -p political-nutshell_com_-_forums > political-nutshell_com_-_forums.du
mp;mysqldump --opt -u root -p political-nutshell_com_-_akju > political-nutshell
_com_-_akju.dump;mysqldump --opt -u root -p legalizemarijuananow_org_-_lmn > leg
alizemarijuananow_org_-_lmn.dump;mysqldump --opt -u root -p killerbunnies_net_-_
forums > killerbunnies_net_-_forums.dump;mysqldump --opt -u root -p energy_cybor
gcow_net_-_istb > energy_cyborgcow_net_-_istb.dump;mysqldump --opt -u root -p cy
borgcow_net_-_hippie > cyborgcow_net_-_hippie.dump;mysqldump --opt -u root -p cy
borgcow_net_-_djskott > cyborgcow_net_-_djskott.dump;mysqldump --opt -u root -p
cyborgcow_net_-_data > cyborgcow_net_-_data.dump;mysqldump --opt -u root -p bone
drypaintball_com_-_stats > bonedrypaintball_com_-_stats.dump;mysqldump --opt -u
root -p bonedrypaintball_com_-_osc > bonedrypaintball_com_-_osc.dump;mysqldump -
-opt -u root -p azhardcorejunkies_com_-_main > azhardcorejunkies_com_-_main.dump
;mysqldump --opt -u root -p bonedrypaintball_com_-_main > bonedrypaintball_com_-
_main.dump;mysqldump --opt -u root -p azhardcorejunkies_com_-_main > azhardcorej

Enter password: |
mysqldump: Got error: 1045: Access denied for user: 'root@localhost' Using password: YES) when trying to connect
Enter password: |

Oh crap, whats my password! Once I remembered it, I had to put it in 28 times. You'd think after that, I'd still remember it!
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
mysqldump: Got error: 1017: Can't find file: 'threadviews.MYD' errno: 2) when using LOCK TABLES
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:
Enter password:

That can't be good. It looks like there is a missing table in a database. Google brought up this SQL command that might fix it. I'll wait until its on the new server to update it though.
CREATE TABLE threadviews
KEY threadid threadid)

So now I have a directory full of database .dump files, next I needed to put it on the new server for restoring.
root@moo:/home/riii/mysqlbackup[root@moo mysqlbackup]# tar -zcvf mysqlbackup.tar.gz *

and then copy it all to the new server!
root@moo:/home/riii/mysqlbackup[root@moo mysqlbackup]# scp mysqlbackup.tar.gz riii@ushi.cyborgcow.net</a>:
riii@ushi.cyborgcow.net's password:
stdin: is not a ttty
mysqlbackup.tar.gz 100% 8168KB 722.6KB/s 00:11

Then I uncompressed the tarball archive:
$riii@ushi: tar -zxf mysqlbackup.tar.gz ~/mysqlbackup/

For simplicity sake, and to get around different mysql passwords, I then needed to create new databases under each user that requires one, and edit the gallery/forum code to use the new database and users. This means that it will be truly transparent to everyone who is being moved and has a forum, except for being unsynchronized for the short time between being moved or not. Hopefully this isnt a long time. Although the primary users on the account cannot have a different mysql password, I can make a new user with a different one, and I still don't need to know their passwords to get things working!

Lastly, the command to restore them:
$riii@ushi: mysql -u root -p mark_phpbb2 < cheddarcore_com_-_phpbb2.dump

Once Those are restored, the only thing left to do is to change the nameserver moo.cyborgcow.net to point to ushi.cyborgcow.net, the new server. This will fix all the dns and mean there will be no inconsistiencies! Huzzah and the server move is finished except for some RSYNC and more SCP tarball transfers of missing files on the large sites, and fixing broken code. I will be looking at each site in depth to see if I need to fix any to be relative to work on the new server. Good php and perl programming would have relative instead of hard links to the mounted filesystem.

And thats what I'm doing today. :)
  • Post a new comment


    default userpic

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.