MySQL 5.5: World’s Most Popular Opensource Database

MySQL is a fast, easy-to-use relational database. It is currently the most popular open-source database. It is commonly used in conjunction with PHP scripts to create powerful and dynamic server-side applications.

MySQL is used for many small and big businesses. It is developed, marketed, and supported by MySQL AB, a Swedish company. It is written in C and C++.

This language is sporting a barrier of entry low enough to attract novice developers yet strong enough to power some of the world’s most popular websites, among them Yahoo!, BBC News, the US Census Bureau, and Craigslist.

This reference is created to help you quickly navigate some of MySQL’s most popular features. Covering topics such as configuration, administration software, backup procedures, SQL features, and user management, this reference will serve as a handy desk for countless projects to come.

Reasons For MySQL Popularity

MySQL became so popular because of the following reasons:

• MySQL is an open-source database so you don’t have to pay a single penny to use it.
  
• MySQL is a very powerful program so it can handle a large set of functionalities of the most expensive and powerful database packages.
  
• MySQL is customizable because it is an open-source database and the open-source GPL license facilities, programmers, to modify the SQL software according to their own specific environment.
  
• MySQL is quicker than other databases so it can work well even with a large data set.
  
• MySQL supports many operating systems with many languages like PHP, PERL, C, C++, JAVA, etc.
  
• MySQL uses a standard form of the well-known SQL data language.
  
• MySQL is very friendly with PHP, the most popular language for web development.

Configuration

MySQL supports over 260 configuration parameters, capable of controlling behavior regarding memory, logging error reporting, and much more. While it’s possible to tweak these parameters by passing them as flags when starting the MySQL server, typically you’ll want to ensure they’re always set at server startup, done by adding them to my.cnf file.

my.cnf File

my.cnf file’s range of impact on the MySQL server is location-dependent:

my.cnf File Syntax

my.cnf the file is a text file broken into several sections. Each section defines the context of the parameters defined within, the context being specific to a particular MySQL client. For example:

# All clients
[client]
port = 3306
# The mysql client
[mysql]
safe-updates
# The mysqldump client
[mysqldump]
quick

Viewing Configuration Parameters

You can view MySQL’s configuration parameters and their current values using one of the following commands:

From the mysqladmin client:

%>mysqladmin -u root -p variables

From inside the MySQL client:

mysql>SHOW VARIABLES;

You can find a specific parameter using the LIKE operator :

mysql>SHOW VARIABLES LIKE "key%";

Storage Engines

How to install MySQL

Download MySQL

Follow these steps:-

Step 1– Go to MySQL official website
https://www.mysql.com/downloads/

Step 2- Choose the version number for MySQL community server which you want.

Installing MySQL on Windows

Your downloaded MySQL is neatly packaged with an installer. Download the installer package, unzip it anywhere, and run setup.exe.

By default, this process will install everything under C:\mysql.

Verify MySQL installation

Once MySQL has been successfully installed, the base tables have been initialized, and the server has been started, you can verify it's working via some simple tests.

Open your MySQL Command Line Client, it should appear with a mysql> prompt. If you have set any password, write your password here. Now, you are connected to the MySQL server and you can execute all the SQL commands at mysql> prompt as follows:

Data Types

MySQL supports a rich set of data types capable of representing nearly every conceivable data formate, ranging across dates and times, currency, strings, integers, and floats. This section defines each type and its respective range.

Date and Time Types

Note:- MySQL is fairly flexible in terms of how it accepts a date and time type values. For instance, DATE, DATETIME, and TIMESTAMP will all accept ‘2008-09-02’, ‘2008/09/02’, and ‘2008*09*02’ as valid date values.

Numeric Types

String Types

Popular Administration Services

Web frameworks help the programmer to embrace best practices, simultaneously decreasing errors and eliminating redundant code. If you haven’t yet settled upon a framework, consider checking out one or several of the following popular solutions:-

MySQL’s Clients

MySQL is bundled with quite a few clients capable of doing everything from performing backups, managing the MySQL server, converting table formats, and stress-testing the database. In this section, I’ll briefly introduce the most commonly used clients.

Key Administration Task

Logging into the MySQL server
To login into the MySQL server using the MySQL client, you’ll typically provide your MySQL username and password:-

%>mysql -u username -p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.22-rc-community MySQL Community
Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear
the buffer.
mysql>

Once you’re logged in, you can select a database or begin carrying out other administrative tasks. To save some time you can pass the desired database along on the command line when logging in:

%>mysql -u username -p database_name

If you are connecting to a remote database, pass the hostname or IP address along using the -h option:-

%>mysql -h hostname -u username -p database_name

To log out of the MySQL server, use quit or the \q flag:-

mysql>quit
Bye
%>

Modify the MySQL Prompt

MySQL’s default prompt is enough to remind you are currently logged into MySQL rather than into an operating system shell. However, like most shells, you can modify MySQL’s prompt to your liking. For instance, when logged into the MySQL client execute the following command to change your prompt to MySQL (user@host)

mysql>prompt mysql (\U)>
mysql (root@localhost)>

Databases

Tables

Managing Users

MySQL is packaged with a powerful security model, capable of controlling practically every conceivable user action, ranging from which commands can execute to how many queries can execute in an hour. This model works in a two-step sequence:

Although covering the nuances surrounding MySQL’s privilege tables is beyond the scope of this document, the remainder of this section should give you ample reminders regarding commonplace tasks. You are, however, encouraged to carefully review the privilege table documentation found in the MySQL manual, as it’s easy to make a mistake when using this powerful feature.

To grant a user all privileges on all databases, use *.* in place of the database name.

Note:- Be sure to include the IDENTIFIED BY clause when creating new users! Neglecting to include this information results in a user being created without a required password.

Key SQL Tasks

While executing standard SQL statements is likely an old hat for most users, it may be more difficult to recall the syntax pertinent to some of MySQL’s relatively new SQL features, namely Stored Routines, Views, and Triggers.

Stored Routines

MySQL collectively refers to stored procedures and stored functions as stored routines. Stored procedures are executed using the CALL statement, and can return values as MySQL variables, whereas stored functions can be called directly from within a MySQL like any other standard MySQL function.

In this section, a brief refresher is provided regarding managing what is arguably the more useful of the two, namely stored functions.

Creating a Stored Function
A stored function is created using the CREATE FUNCTION command. A simple example follows:

mysql>DELIMITER $
mysql>CREATE FUNCTION calculate_bonus
        ->(employee_id INTEGER) RETURNS DECIMAL(5,2)
        ->BEGIN
        ->DECLARE article_count INTEGER;
        ->DECLARE bonus DECIMAL(10,2);
        ->SELECT count(id) AS article_count FROM articles
        ->WHERE author_id = employee_id;
        ->SET bonus = article_count * 10;
        ->RETURN bonus;
        ->END;
        ->$
mysql>DELIMITER ;

Once created you can call calculate_bonus() from within a query: mysql>SELECT name, phone, calculate_bonus(id) FROM authors;

Note:- Stored procedures and functions support complex logical syntax features, including conditionals and looping statements.

Altering a Stored Function

To modify an existing function, use the ALTER FUNCTION command:

mysql>DELIMITER $
mysql>ALTER FUNCTION calculate_bonus
        ->MODIFIED FUNCTION BODY...
        ->$
mysql>DELIMITER $

Deleting a Stored Function
To delete a stored function, use the DROP FUNCTION command:

mysql>DROP FUNCTION calculate_bonus;

Views

Views can greatly simplify the execution and management of an otherwise complex query by assigning an alias to it, allowing the developers to execute the query by its alias rather than repeatedly entering the query in its entirety.

Triggers

Triggers are automatically activated in accordance with a specific table-related event. They are useful for automating table updates which should occur when another table is modified in some way.

Performing Backups

Performing regular backups is an essential part of even the smallest database project. Fortunately, MySQL makes this very easy by offering several backup solutions.

Copying Files

If your tables use the MyISAM storage engine, you can backup the database simply by copying the files used to store the tables and data. To do so, you’ll need to first execute the LOCK TABLES command (only a read lock is required), followed by FLUSH TABLES. Once executed, copy the files, and when the copy is complete, execute UNLOCK TABLES.

Creating Delimited Backups

To back up the table data in delimited format, use the SELECT INTO OUTFILE command. For instance, to back up the author’s table used in previous examples, execute:

mysql>SELECT * INTO OUTFILE 'authors090308.sql' FROM authors;

Using mysqldump

The mysqldump client is convenient because it supports creating backups of all databases using any MySQL storage engine, not to mention that it automatically takes care of important details such as locking the tables during the backup.

The mysqldump client supports an enormous number of options, and it’s recommended you take some time to review them in the MySQL manual, however, this section will give you enough to at least remind you of what’s required to perform a variety of different backups.

Backing Up a Specific Database

To back up a single database, just pass the database name to the mysqldump client, piping the output to a text file:

%>mysqldump [options] database_name > backup0903.sql

Of course, you’ll require proper permissions to execute mysqldump in conjunction with a specific database ( namely the SELECT and LOCK privileges), therefore you’ll typically also need to pass along your username and password. In this case, this command typically looks similar to:

%>mysqldump -u root -p database_name > backup0903.sql

Backing Up Specific Tables

To backup specific tables, you will need to identify the database, followed by each specific table name you’d like to backup:

%>mysqldump [options] database_name table_name [table_
name2...] > backupfile.sql

Backing Up All Databases

To back up all databases, pass the –all-databases option:

%>mysqldump [options] --all-databases > backupfile.sql

Using mysqlhotcopy

If all of your backup tables use the MyISAM storage engine, and you are able to log into the server where the tables are stored, the mysqlhotcopy might be the ideal solution due to its speed advantage.

To back up the Codersera database to a directory located at/home/Jason/backups using mysqlhotcopy, execute:

%>mysqlhotcopy -u root -p codersera /home/jason/backups

To copy multiple databases, just string each database name together:

%>mysqlhotcopy -u root -p codersera wjgilmore /home/jason/backups

Note:- MySQL’s replication features make it possible to maintain a consistently synchronized version of the live database. Replication is out of the scope of this reference card, but be sure to visit the MySQL documentation (http://dev.mysql.com/doc/) if replication is more suitable for your needs.

MySQL’s Date and Time Features

In addition to temporal data types, MySQL supports over 50 functions intended to facilitate the retrieval and manipulation of date-related data. Rather than exhaustively itemize these functions, it seems useful to instead offer a number of common examples that may help jumpstart your search for the appropriate approach to carry out date and time-related queries.

SQL, Lisp and Haskell are the only programming languages that i’ve seen where one spends more time thinking than typing.– Philip Greenspun

FAQ's