Data migration tips

Anytime you move to a new web host or upgrade to a new server, you will need to migrate your data. In the old days, data was often stored in flat files or simply directly within the HTML files. While the management eventually proved to be inefficient, it was much easier to move files. With databases, it can be trickier. Here are a few things to consider:

1. If you are moving to an identical setup (i.e. same version of the same content management system), a basic SQL export will work just fine with minimal to no data loss.

2. If you are changing versions of a CMS, you should read its documentation to see if migration is even possible and what steps you need to take to make it happen. If at all possible, upgrade to the identical version before migration.

3. When moving to a completely different CMS or database system, you will need to prepare your data. This can involve manipulating large chunks of a MySQL database or even exporting everything to XML. Some software like WordPress will import RSS posts from an XML file without any extra configuration, making a migration rather painless.

4. Backup everything as often as you can. If you mess something up, it is better to have to start over with your backup copy than to have to fix the only copy you have.

Photo Source: Flickr

Database management with MySQL Workbench

MySQL is one of the most popular databases for web servers. The MySQL website offers a free download of MySQL Workbench, a GUI database management program. I decided to download it and give it a try. It is available for Microsoft Windows, Mac OS X, and several versions of Linux. I tried out the Ubuntu version.

When you first start the application, you are greeted with your Workspace and three categories: SQL Development, Data Modeling, and Server Administration. I wanted to see how easy it was to connect to a database, so I immediately clicked “New Connection”. By default, it brings up your local databases, which is what I wanted to edit, so I did not have to configure anything, only enter my password. The workbench instantly presented me with a new tab containing all of my SQL databases. To edit any one, you just click on it and begin.

I honestly do not even know what data modeling is, so I left that section alone. I went straight to server administration and clicked “New server instance”. Again there were several configuration options, but it was already set to connect to my local server. Within seconds I had live graphs, server status, configuration options, and logs for my MySQL server.
Read More >>

Quad-Core vs Dual-Core servers

Many web hosts are proudly offering servers with single quad-core processors or with two dual-core processors. While they certainly sound impressive, are there actual benefits? Does a quad-core system deliver better results than a dual-core of the same processing speed? The truth is that it depends on what the server’s applications need.

For a dedicated server that is running a single web server (even with virtual hosts) and maybe a single database server, quad-core is probably overkill and will not produce any tangible benefits. For quad-core to be beneficial, there needs to be at least three CPU-intense processes running simultaneously.

An example of of when a quad-core server makes sense is when there are three, four, or more intense web applications running that all access databases. Another example would be a virtualization situation, where the server is running dense virtual machines that require their own processing power. In such a case, the benefits of a quad-core system is that compartmentalization. There also seem to be indications that have two dual-core processors has benefits over a single quad-core processor for the same reasons mentioned above, although I have not seen any proven data to confirm it.

Photo Source: Flickr

Twitter the latest among big players to leave MySQL for Cassandra

With a name that even sounds like the beautiful love interest of a secret affair, Cassandra appears to be positioning itself to steal the hearts of those once in love with MySQL. Twitter is now set to replace its MySQLdatabase system with the open source home wrecker.

Cassandra was originally developed by Facebook and was released to the public via Google’s code repository in 2008. In 2009, the Apache Foundation added it to its project, and in February of 2010, Apache made it a top-level project. It is designed to work with large amounts of data by spreading the data across multiple servers. Its notable feature is that it has no single point of failure.

Twitter joined a growing list of major companies that have adopted Cassandra. Among them are Digg, Cisco, IBM. Cassandra is free and open source software released under the Apache License 2 and is available for download from Apache.org.

Photo: Flickr

How to check and optimize MySQL databases automatically

MySQL databases require regular maintenance, but usually the databases just need to be re-optimized and checked for errors. In such cases, having to go through each database on your server can be time consuming. Following these steps, you can setup a cron job that will automatically check and repair your databases.

1. Login to the server via SSH
2. Edit the crontab file found in /etc/crontab

# crontab -e

3. Enter the following line in the crontab file:

0 1 * * * mysqlcheck -Aao –auto-repair -u root -p[password] > /dev/null

This will check all databases on the server. If you do not have root access and only want to check your own website, you can edit your cron in your control panel or replace the “root” user with your MySQL username. Save the file, and cron will now start mysqlcheck at 1 am everyday to optimize and check all databases.

Source: My Digital Life
Photo: Flickr

Before you commit your data…

When you have a small to medium-sized business (SMB), it is tempted to take lowest service offer, especially when a vendor makes big promises. There are plenty of database vendors on the market, and some of them offer very attractive custom proprietary packages or cloud computing solutions. But before you commit your data to what will likely be its permanent resting place, consider a few things:

1. If a vendor makes you custom database software and then goes out of business, what happens to your data?
2. If a vendor’s cloud computing infrastructure breaks down, what happens to your data?
3. If later on down the road, you want to transfer your databases to something more standard, will you lose data in the conversion?

The answer to all three is that some or all of your data could go up in smoke. That is why more businesses, even large ones are opting for free and open source solutions like MySQL or PostgreSQL. You can still hire someone to set it up and manage it, but when that company fades away, your data is still safe. Even if you opt for a cloud solution, having access to your open source databases means that you can pull them at any time and move them to another server with minimal data loss.

Photo: Flickr

How to create a MySQL database in cPanel

Question: How can I use cPanel to create a MySQL database?

Answer: cPanel uses its own web-based frontend to make database creation very easy. Just follow these simple steps:

1. Login to cPanel
2. Under the Databases section, click “MySQL Databases”
3. Where it says “Create New Database” enter a short name for your database.
4. Click “Create Database”.

Next, you will need to have a user for the database. If you do not have any users, you will need to create one. If you do, skip to step 4.

1. Scroll down to the bottom until you see “Add New User”.
2. Enter a name and password.
3. Click “Create User”
4. Under “Add User To Database” find your user in the dropdown menu and then find the database you created in the second dropdown menu.
5. Click “Add”.

You have now created a database that will give permissions to the user you created. When you install scripts or applications, they will access the database using the username and password you specified. Therefore, make sure you remember it, but also make sure it is secure.

Restoring a huge MySQL database

Question: I have a shared hosting account but need to import a 3GB MySQL database. I do not have shell access, and phpMyAdmin always times out. How can I restore it?

Answer: You can use a script called BigDump to import your dumped MySQL database a little at a time. This will prevent it from timing out when PHP has an imposed limit. According to their site, this script is for people who want:

“To restore the very large backup of your mySQL database (or a part of it) into the new or the same mySQL database. You can’t access the server shell and you can’t import the dump using phpMyAdmin or any other scripts due to hard memory resp. runtime limit of the web server. ”

BigDump is free software released under the GNU General Public License.

Photo: Flickr

How to Import a MySQL Database

In a previous post, I explained a couple methods of exporting MySQL databases. Today, we will do just the opposite: import a mysql database. As with the previous example, you may use either phpMyAdmin, your web hosting company’s custom control panel interface (if available), or the Linux command line, if you have the necessary permissions to do so.

Command line

To import a mysql database, upload your sql file to your server and login to your server via SSH. Then, enter the following commands:

mysql -p -h localhost dbname < dbname.sql

Replace “dbname” with the name of your database in the first instance and the name of your sql file in the second.

phpMyAdmin

Although this requires more steps, some people might find it easier.

1. Login to phpMyAdmin.
2. Click on your database name.
3. At the top, click “Import”
4. Click “Browse…” and find the sql file on your computer.
5. Click “Go”.

If you are working with a very large sql file, it may timeout in phpMyAdmin, depending on your server’s settings. In such a case, the command line may be a better option.

How to Export a MySQL Database

Question: How do I export and download a MySQL database?

Answer:

There are two primary ways to export a MySQL database if you are a website owner. The first is to use phpMyAdmin, a web-based graphical interface for managing databases. The second is to login through SSH and use MySQL commands. A third option, if available, is to use your web host’s control panel system.

phpMyAdmin

1. Login to phpMyAdmin (The method of reaching it varies based on host. You can reach it through many web hosting control panels).
2. Click on the database you want to download.
3. Click the “Export” tab.
4. Select a format (i.e. CSV, SQL, XML).
5. Check “Save as file” and decide whether or not you want a compressed file (helpful for big databases).
6. Click “Go”.

Command Line

In some cases, especially if you have a very large database, it might not be possible to use phpMyAdmin. If you have SSH access, logging into mysql directly is an easy way export a database.

1. Login to your server via SSH
2. Enter this command:

mysqldump -u username -ppassword database_name > dump.sql

3. Use a regular file transfer method, such as FTP, to download the dump.sql file.

Photo: Flickr