John Hesch

Recently I needed to move 19 databases from server A to sever B. The new server B was a scaled down Linux server with no web services so I couldn't use phpMyAdmin, the MySQL database administration tool. I needed to learn how to take care of this task using the command line only.

After moving 19 databases, I created a fairly easy system for exporting, transferring, importing, and creating permissions.

Step 1: Dump Database

Use SSH and log into server A. Navigate to a temporary folder or create a new folder for dumping the databases into. From the command line issue the following command

This will dump the structure and data from the database you named above into the directory you are currently in.

Step 2: Transfer File

Now you need to transfer the file to the new server.

Replace DATABASE_NAME with the name you gave the file in step 1. Replace USERNAME with the login name on server B. I used root. Replace IP_ADDRESS with the IP of server B. DIRECTORY_ON_SERVER_B is the name of the directory on server B where you want to transfer the file to. It doesn't really matter where you put it.

Once you issue the above command, you will be asked to provide the password for the username you supplied. Once accepted, the file will be transferred.

Now log into server B and navigate to the directory where you transferred the database file to.

Step 3: Create Database

This command will create a new database with the name you provide. Should be the same name as you used on server A. After entering the above command provide the MySQL root password and the new database will be created.

Step 4: Restore Data

This command will import the structure and data from the database on server A. Again you will be asked for the MySQL root password.

Step 5: Grant Users

Now you need to create a new user and give that user permission to access the database.

Step 6: Flush Privileges

Now tell MySQL to reload the new privileges you created.

That's it. Repeat for each database you are moving.

Here are a few other commands that came in handy

Show the databases:

Show Grants:

Delete Grants:

I learned something interesting today. I am finishing up writing a blogging application and I wanted to transfer either a success or error message after a post was created. I was using the following code:

But for some reason the session variables were not transfering to the index.php page. I Googled my problem and come to find out

gets executed so fast that the session doesn't have a chance to set the variables. The solution is to use session_write_close();. This forces session data to be saved before the browser changes to the new page. Now my code looks like:

Works perfectly!

A List Apart has an informative article on protecting your code from cross-site scripting. What I liked about this article is that they weren't platform or programming language specific. Fundamentals were taught leaving the user to apply the techniques to the users specific environment.

This is part one of a two part artcile.

I'm not sure if anyone is still reading this blog but if so, what would you consider to be the best way to use IE hacks in your stylesheets? IE allows the use of conditional statements like

This allows you to load an IE specific stylesheet. You can learn more here

Or incremental overrides, like so

Or using hacks like this or any other type

The most frustrating thing about coding websites using stylesheets is trying to get the same look in the different browsers.

For some reason my server is seeing spikes in the number of processes which is causing my server load to increase to levels that basically make the server inoperable. This is happening several times a day. Until I find out what the cause is, I implemented some code to check the server load every other minute and if the load is above 5, restart httpd.

Save this as loadavg.pl and run it with cron. We shall see.

I found a really cool PHP, MySQL, CSS, and JS Quick Lookup tool. Since I now spend all my time building web applications in PHP, MySQL and CSS this tool saves me a lot of time.

Update:

Never mind, I finally figured it out. I was referencing the function Get() in the GetList() function and Get() was not searching by ItemID.

I have posted this problem on several PHP topic forums and have been working on it for several days and I cannot get it to work properly. You're my last hope.

I have the following function written in PHP

There are two records in the database. They each have a unique ItemID but the same IOrderID. When I try to display the results of the query in the above function I get two instances of the first record and not one instance of both records.

If I echo the query in MySQL I return both records as expected.

Here is what I'm using to display the results

If I add the following to display the database results within the function

The results of the echo are the two records as expected.

I can't figure out why my code above won't return both records. Any ideas?

If you haven't done so already, you should update the copyright year on your website. I learned several years ago that this task can be automated if you program in PHP. I use the following on KZION.

This bit of code simply prints the current year. Digital Web Magazine has the code to do the same thing using SSI or Smarty.

Mark Fleming wrote me with a problem he was having with text that exceeds the column width. Usually plain text is not a problem, if done properly it should wrap and not exceed beyond the column boundary. But hyperlinked text can often extend beyond the column boundary and so I supplied him with the following code, which can be used in lots of different situations.

Of course if you want to use this code you would have to apply the correct variable names but you get the idea.

Here's an interesting method of downloading recent delicious bookmarks and storing them in a database and in a Gmail account making the text of the pages searchable by storing a cached copy. Run it once a day using cron to download a copy of the page and forward it to a gMail account so that you can have an archived copy of the page and can quickly search pages that have been tagged. The tags become the subject of the email and the "from" address is the url of the page.