How to Search a MySQL Database

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

MySQL Optimization: Part 2

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

Useful RPM Tips

As I mentioned in an earlier post, YUM is a powerful package management system, commonly used in RPM-based Linux distributions, including Red Hat Enterprise Linux, CentOS, and Fedora. We have covered some of the basic YUM commands. Now, here are some helpful commands not in YUM that you can use to better manage your RPM packages.

1. Reset file permissions – This is for those times when you have made changes to an application that you probably should not have made. RPM can reset the permissions for all files in the package and return them to the state they were in when you installed it. Simply run this command:

rpm --setperms

2. Extract one file from an rpm – Whenever you do not actually want to install an RPM, you can extract its contents. This is also useful if you have changed something in one of the files and just need to extract the default file, like logrotate.conf. Run this command:

rpm2cpio logrotate-1.0-1.i386.rpm |cpio -ivd etc/logrotate.conf

3. Query 3rd party packages – In this example, suppose you are using CentOS, but you want to query packages from other repositories. This command will allow you to find packages not in the CentOS repository.

rpm -qa --qf '%{NAME} %{VENDOR}\n' | grep -v CentOS

MySQL Enterprise Released

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

PHP: How to Select Multiple Database Tables

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