Posts Tagged ‘MySQL’

Hello there, It’s been quite some time since I posted. I got pretty busy with my freelance and wasn’t able to get to writing any new posts. I’m hoping to get back in the groove and give at least one post a week.

So what have I been up to? Well, work mostly 🙂 That’s a good thing though so I’m not complaining. I have some pretty steady clients and have been actively advertising on Facebook for more clients. Other then that I picked up a few more games which has re-inspired me to continue with my game development. I started a couple small projects using FLEX and the Flixel game engine. Flixel isn’t not too bad but it is in the very early stages of development. Check out a couple of the apps I’ve posted on my site! I’ve also resurrected my browser based RPG. I’m might start a new string of blog posts following my progress with that as well. It will be written in PHP/MySQL with supporting Javascript libraries like jQuery.

That’s about all for now 🙂 I’ll keep you all posted!

– Miah

Advertisements

Tonight I’m going to put down something I learned just yesterday. How to easily migrate MySQL Databases across servers via the command line. Now I’m a beginner Linux Admin as my focus is on PHP Development but ever since I got myself a dedicated box at Rackspace I’ve had to learn a few tricks. Over the past month I have learned a host of them due to migrating from one Rackspace box to another. My biggest challenge was moving some of my MySQL Databases. A couple of them were quite large and PHPMyAdmin was just not up to the challenge. So after some searching and one phone call to the excellent Rackspace support team I had my solution and I am going to share this tidbit with you all.

The solution is something that any MySQL guru probably already knows but I think beginners and amateurs in command line techniques will benefit. It consist of three commands. They are as follows:

mysqldump --database --opt [DB NAME] > [DST FILE NAME].sql
scp [DST FILE NAME].sql [RMT BOX UN]@[RMT BOX IP]:[RMT BOX DST DIR]
mysql [DB NAME] < [TRANSFERRED FILE NAME].sql

The first and last commands are mysql commands and the middle one is a Linux command. What these three commands basically do when used in this sequence is:

  1. Dump a DB to a SQL file
  2. Transfer the created SQL file to another machine. (scp = Secure copy)
  3. Fill a DB with the contents of a SQL file.

When you run the scp command you will be prompted for the password associated with the username you are trying to connect with. Now scp only works on the same network but you can get around this by downloading the sql file via FTP and then uploading it to the destination machine.

You will also need to create the DB before running the last command.

Now this is pretty straight forward and easy to accomplish and now that I have this knowledge I am able to move databases in less then a minute! This made my last night of migrating painless. Also it will make things easy when I need to synce the servers DB’s with my own local machine for development or bug testing.

Here are links to the documentation for each command if you want to delve deeper into any of the available options.

mysqldump
scp
MySQL – The official website

All questions and comments are welcome!

Thanks,
– Miah