When MySQL works correctly, it can be a thing of beauty, but when something goes wrong, it can drive you mad. Here are a few things you can do to troubleshoot connection problems:

1. Make sure your username and password are correct.
2. Double-check the hostname. Although “localhost” works on most servers, it may not on yours.
3. Test the connection string (if you wrote the code yourself). You may have a simple typo.
4. If you have your own server, check the mysql server to see if it is running and running without errors.
5. Does the database you are trying to connect to actually exist? Sometimes automatic database creation fails, and you are left wondering why you cannot connect to it.
6. If you can connect locally but cannot connect remotely, check your firewall settings to see if port 3306 (or whichever port you use for mysql) is open.
7. Finally, be sure your mysql user has the necessary privileges to perform whatever task you are trying to accomplish.

Photo Source: Flickr

Continue reading: Troubleshooting Database Connections

With web-based control panels and web software like phpMyAdmin, you have numerous options available for searching a MySQL Database. If the need should ever arise where those tools are not available, however, it is a good idea to know how to search a database from the command line.

To search MySQL from the command line, you need to log in to MySQL and perform a query.

Login as root:

mysql -u root -p
You should get a prompt like “mysql>”

To do a basic search, use the SELECT command. For example, to search for all records with the title of “Sales” that also have the number “44″, enter:

mysql> SELECT * FROM [table name] WHERE title = "Sales" AND number = '44';

You can perform similar queries on any tables in your database. For a more in-depth guide to queries, consult the MySQL documentation.

Photo Source: Wikimedia Commons

Continue reading: How to Search a MySQL Database

1. Query cache - On most servers, there are certain MySQL queries that you and/or your scripts will run more often than others. In fact, you may run the same query hundreds or even thousands of times in a single day. The query_cache setting will save the most used queries in memory for quick access

query_cache_limit=1M
query_cache_size=32M (32MB for every 1GB of RAM)
query_cache_type=1

2. Key buffer size - This refers to the size of buffers used for indexes. A larger buffer will result in faster SQL response time when a command is issued. The key_buffer_size should be at least 1/4 but no more than 1/2 of the RAM size of the server.

key_buffer=256M (128MB for every 1GB of RAM)

3. Table cache - Just like MySQL can cache queries, it can also cache tables. If you think about how often a database table would be accessed, especially when using content management systems and web applications, this only makes sense. You can adjust this according to your memory.

table_cache=1500

Next week, we will cover more MySQL optimization tips.

Source: linuxstuffs

Continue reading: MySQL Optimization: Part 2

If you are running a dedicated server, optimization is very important to maximize speed, efficiency, and save time and energy. We have already looked at ways to optimize Apache web server, but many dynamic websites also use databases that hold the data for their dynamic web applications.

You can accomplish basic optimization of database tables from within your web-based control panel or in phpMyAdmin. You should do this routinely. But there are also ways you can optimize the server itself to serve databases faster and more securely. Over the remainder of this week, I will show you a few tips to optimize your MySQL server. Today, I will just point you to the right file.

To begin configuring your MySQL server, you will need to locate the configuration file. This will differ, depending on your operating system and even from one Linux distribution to another. Most commonly, it will be in a directory like /etc/mysql, and the file will be called my.cnf. You will need to edit it as root:

# nano /etc/mysql/my.cnf

Tomorrow, we will start to plow through this file for optimization tweaks.

Continue reading: MySQL Server Optimization

Monday, the database giant, Oracle, announced the release of MySQL Enterprise. In addition to the standard, freely available database software, this new packaged version of MySQL will include comprehensive support and monitoring tools. The primary tool available with this release is MySQL Monitor 2.2, which monitors performance and security. Other important tools include the Query Analyzer and MySQL Connector Plugins.

“DBAs and developers need solutions that help them manage their MySQL servers efficiently and allow them to identify performance issues before they become expensive, time-consuming problems,” said Tomas Ulin, director, MySQL Development, Oracle.

MySQL is one of the most widely-used database servers on the Web, and most web hosting providers offer it. While the underlying code for the database software is free and open source, there is also a commercially licensed version. MySQL was owned by Sun Microsystems until Oracle recently bought Sun and all of its software products.

Source: MarketWatch

Continue reading: MySQL Enterprise Released

In the web hosting world, certain database software is commonplace. Most system administrators, even the new ones, have heard of MySQL, MSSQL, and/or PostgreSQL. Using SQL databases typically requires a database server to be running on the system. MySQL, for example, runs as a service in Linux called mysqld, or something similar. An alternative to running these types of services is to use SQLite.

Because it does not run via a sever, SQLite does not require the configuration and maintenance of other database systems. As a result, it is considered light and easy to use. Rather than running on a server, the database code is linked directly with the software using it. The library itself is small and can be installed in smaller installations.

According to their website, SQLite is the most often deployed database engine used in many web applications. It is free and open source, without a license, released into the public domain. You can download the libraries at the SQLite website.

Continue reading: SQLite for Your Database Needs

Selecting a MySQL database table with a PHP document is a quick way to get certain output onto a web page with very little coding or effort. You may want nothing more than a simple printout of the database table or something more complex like a full web application.

Regardless of the scenario, selecting multiple database tables in the same query is a little more tricky. Here is a syntax that worked for me. First connect to your database the way you normally would, then enter your query like this:

$result = mysql_query("SELECT * FROM table1, table2 ");

Replace “table1″ and “table2″ with the actual names of your tables. Next, you can show rows from both tables, but if any of the rows have the same names in both tables, you will have to specify them specifically in the query.

Finally, display the results however you like:

while($row = mysql_fetch_array($result))
{
echo "<div id='corn'> ";
echo $row['corn'];
echo "</div> <div id='wheat'> ";
echo $row['wheat'];
echo "</div> ";

For more information on MySQL Select, see w3schools.com
Photo: Wikimedia Commons

Continue reading: PHP: How to Select Multiple Database Tables

After you create a MySQL database, the next thing you will most likely want to do is to fill it with data. To do that, you need to setup tables. If you are installing a content management system or other type of script with an installer, it will probably create the tables for you. If that is not the case, you must create the tables yourself. While creating them from the command line is not terribly difficult, using the phpMyAdmin web interface is probably the easiest method.

To create a new table, follow these steps:

1. Login to your phpMyAdmin installation either through your control panel or directly.

2. On the left, click the database you want to manipulate.

3. At the bottom, underneath the current tables, find “Create new table on database” and enter the name and number of fields each row will have.

4. Enter the field names (each row will have a name assigned to it)

5. Select the data type. The default INT is for integer (i.e. a number). You can see a complete lists of data types on the mysql website

6. Select a maximum length or leave it blank

7. Set the type of collation, essentially the encoding, such as UTF-8.

8. When you have all of the information inputed, scroll to the bottom and click “Save”.

With that, you are all finished with your first table.

Continue reading: How to create a table using phpMyAdmin

In a previous post, we learned how to create a MySQL database from the Linux command line. Today, we will learn how to accomplish the same task with a graphical interface using the free web-based MySQL administration tool: phpMyAdmin. Just follow these steps.

1. Login to phpMyAdmin either through your server’s control panel or directly.

Note: Some panels, such as cPanel require you to create databases through their interface. If so, follow their instructions

2. In the main Home section, look for “MySQL localhost“, and under “Create new database”, enter the name you want to use

3. Click “Create”

That is all it takes! You now have a new database. To assign users to the database, click the “Privileges” tab while you are still on that database’s page. Once you have the permissions you want, you can add, delete, and configure databases with relative ease.

Continue reading: How to create a database in phpMyAdmin

There are a few of ways to find files on a Linux server, but most of them involve actually searching through each file in the filesystem until the correct one is located. This can be time consuming and taxing on the server’s CPU load, especially if you have a lot of files.

Linux has two commands that make searching a little easier: locate and slocate. Unlike other find utilities, locate searches through a database that contains information about the filesystem, bringing up the search results almost instantaneously. The command to update the database is called “updatedb”, and many Linux distributions have the command run via cron every day.

The alternative version of locate, called slocate, is a security-enhanced version that only allows the user to find files he or she has the permission to access. While locate is a great tool for finding things on a server, it does have its issues. For one, you will only find files that were added or changed prior to the last updatedb execution. Furthermore, the very process of updating the database can be taxing on the server, even if it is only once a day. For the right situations, however, locate is a very useful Linux tool.

Photo Source: Flickr

Continue reading: Finding Linux files with "locate"

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

Continue reading: Data migration tips

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.

Continue reading: Database management with MySQL Workbench