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

Continue reading: Twitter the latest among big players to leave MySQL for Cassandra

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

Continue reading: How to check and optimize MySQL databases automatically

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

Continue reading: Before you commit your data...

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.

Continue reading: How to create a MySQL database in cPanel

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

Continue reading: Restoring a huge 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.

Continue reading: How to Import 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

Continue reading: How to Export a MySQL Database

Shareholders of Sun Microsystems voted to approve a $7.4 billion deal that will allow Oracle to acquire Sun. The vote only took three minutes, indicating that it was little more than a formality. Sun had been courted by others, including IBM, but the Oracle offer was apparently too sweet for shareholders to pass up.

Our previous reports on this developing story posed questions about the future of several key web technologies, including MySQL and Java, both Sun products. Oracle has now created a nice little empire for itself, and fears that it will fracture or dissolve its new software acquisitions might be unfounded. Many large and small to medium sized businesses (SMB) rely on both MySQL for web applications, while others rely on Java and Sun’s server hardware for their websites.

MySQL database software is the third leg of the famous LAMP stack, which includes Linux, Apache HTTP Server, and PHP scripting language. Accoding to its website, over 100 million copies of the software have been downloaded or distributed. It is a favorite among web hosts and website owners alike. MySQL is available under multiple licenses, including free and open source models. Oracle has long been a database leader, offering a range of proprietary solutions.

Continue reading: Oracle-Sun deal approved

One feature some consumers forget to look over before signing up for a hosting package is the number of MySQL databases offered. The most common type of database, MySQL is required to use any of the most popular blogging or CMS platforms.

One clever trick hosts use to keep usage down is limit the number of MySQL databases a customer can use. It’s not uncommon for entry-level hosting plans to have only one or possibly no databases. Because each CMS or blog installation requires a separate database, a limit on MySQL limits the number of sites you can run. Hosts may also achieve the same effect by restricting the number of domain names that can be added to an account.

Before signing up for a hosting package, make sure you have enough databases. Higher-end plans will give you at least 10. Of course, you can always use static HTML instead to get around a MySQL limit, but most hosters these days prefer the ease of use offered by a CMS.

Continue reading: The Database Dilemma