This tutorial goes over all the necessary steps to restore a MySQL backup via the shell or command line.

Determine The Basics About Your Database

We need some basic information about the MySQL installation before we get started restoring the backup.

First, lets determine the location of the mysql databases. It is generally located at /var/mysql/data but it is always good to double check. Issue the following command to see where it is:


This will output something like the following and tells us that the mysql databases are located at /usr/local/var. This location will be used for the remainder of the tutorial so please be sure to replace it with the location that you received from the above command.



Remove The Old Database(s)

If MySQL is not corrupted and your backup files are set to drop the table before inserting the new data. You can skip this section. If you are not sure it will not hurt to complete the following steps but we will need to take the database offline for a few minutes.

First we need to shutdown mysql with the following command:


Now we need to backup the current mysql data directory and create a new one. This can be done with the following 2 commands:


Next, we will install the basic mysql table by running the mysql_install.sh script. Generally, it is located at /usr/local/mysql/bin/mysql_install_db. If it is not there you can use the following command to find it.


Note: you may need to update your locate database if the script is not found with the above command. To update your database issue one of the following commands and then repeat the above command.


Now that we have found the script we can run it


Restore the Backup

The first step is to take your backup file and uncompress it.


If mysql.sql was not listed in the backup you can either manually reset the permissions or copy it from the backup you just made.


Now lets set the ownership of the directory and all files in the directory. Make sure to use the appropriate group and user with the below command.


Now create any databases that you are going to restore with a command like the following (repeat the command for every database that you will restore):


Restore the backup with the below command and repeat the command for every sql file/database that you will restore.


Startup MySQL

Whether you are an individual user sending email to friends & colleagues, or use a commercial email-sending service like Constant Contact, it is important to regularly check your sending ip address hasn't been black listed.

Today we released a simple tool that lets you easily test if your mail server is black listed with any popular black-list DNS service:

Email mail server Blacklist Checker



-

Don't know your sending mail server ip address? Send an email to yourself, then view the message headers.

View 1 Comment(s)

Everyone knows video helps conversions. But most people who incorporate a video on their landing pages, use Amazon S3 to host the video, or use one of the dozen video sites like Viddler, Vimeo, Veoh etc.



Back in 2007, Adrian wrote about how we were migrating all of our videos from YouTube to ScreenCast.

Back then (maybe) it made sense... YouTube didn't have High Quality and we were doing primarily screen-cast type videos.

But nowadays, if you're not hosting your landing-page videos on YouTube, you're missing out big time!

5 reasons to use YouTube for your landing page videos

1. YouTube is where your traffic is



2. YouTube now offers full high definition

3. YouTube is the longest standing video service. Owned by Google, it's future is rock solid while some of the other video-hosting sites are facing financial difficulties.

4. YouTube channels and subscriptions actually work. Offer great content and you'll build a loyal following.

5. PPC traffic to Youtube videos on your landing page equals free traffic.

This is Key:

If you embed a YouTube video on your landing page (use &autoplay=1 to get the video to automatically play as son as the page loads), every page view translates to 1 view on YouTube.

The more views your video gets, the higher it will rank in YouTube and Google organic searches! Tag your video with the right keywords and include them in the video title. Pretty soon, you'll find you're getting lots of free clicks from Google search results.

You can even turn-off your PPC traffic later on and the traffic will continue.

View 1 Comment(s)

In a previous post, Kyle covered How to fix MySQL Replication, by using a script that looks up records in the master/slave, inserting any missing records as well as updating any ones that don't exactly match.

But how can you tell your replicated MySQL databases are no longer in sync?

Since some queries are never safe to run in a replicated environment (i.e. DELETE FROM table LIMIT 1), without having the proper experience, it's easy to develop replication inconsistencies that could lead to fatal errors.

The easiest way to monitor replicated databases are in-sync is by using MySQL built-in CHECKSUM function, that calculates a checksum for each table based on the values, number of rows and number of columns in the table.

Calculating a table checksum is a very quick operation!

If a table on the master generates a different checksum than the same table on the slave, it means the two are not in sync and you should look deeper.

The code below handles verifying two databases (master/slave or master/master), printing a list of all tables where the checksum doesn't match:


I strongly recommend setting this up on a cronjob, so that you identify any replication issues as quickly as possible.

Be sure to also read my post about a Better MySQL Replication monitor. Don't trust MySQL built-in "Seconds behind slave" reading, it's not reliable.

jQuery is a JavaScript Library that includes document traversing, drag&drop, date picker, tabs animating, and Ajax interactions for rapid web development. It's widely deployed and used by Google, Digg, Mozilla as well as many other huge sites.

As part of this post, I'll demonstrate how to use jQuery for real time server-side form validation.

The Challenge

Most HTML forms use simple Javascript to validate user input, before the form is submitted to the server.

A javascript function runs inside the browser and validates that a date field is valid, a username field doesn't contain invalid characters etc.

Providing user input passes these simple client-side validation tests, the form is submitted to the server, where the server performs additional validations on the entered data.

Some examples where this approach is lacking:

* A username may be valid on the client side, but already taken by the time the form is submitted.

* Information on the server-end is required to validate a password

* USPS lookup is necessary to validate an address / zip code etc.

jQuery

Since data on the server side is required for some validations, we need a way to ping the server and validate fields as the user is typing text in the form.

jQuery to the rescue!

Start by including jQuery in your html code:


Designating a field as required is easy - simply add a hidden input tag with the name of require_FIELDNAME:


Integrating server-side validation with a form field and displaying an error message if validation fails, can be done with a single call:




Complete Example

This example features server-side validation of a password and username (check for invalid characters and verify username is available), displaying an inline error message if validation fails.

Here's how the end result looks like:



HTML code:


The validateusername.php file:


The validatepassword.php file:



View 3 Comment(s)

MySQL replication log files are simple text files, with a dump of all queries issued on the designated server.

At certain times, when MySQL slave servers fall behind, it may be useful to copy the log files over from the master to the slave machines and then manually process them.

To process a MySQL replication log file, copy the file from the master to the slave machine, then issue this command:


Replace 1234 with the last position the slave was able to read
Replace mysql-bin.000001 with the master file name
And replace USERNAME/PASSWORD with the slave database credentials