MySQL/MySQL Practical Guide

Installing MySQL

All in one solutions

As MySQL alone isn't enough to run a real database server, the more practical way to install it is to deploy an all in one pack in this purpose, including all the needed additional elements: Apache and PHP.

1. On Linux: XAMP or LAMP.
2. On Windows: XAMP, WAMP, or EasyPHP.

Attention on Windows 10:

• The server IIS is launched by default, which forces Apache to change its port (888 instead of 80). To resolve this, just untick Internet Information Services in Programs and functionalities, Activate or deactivate the Windows functionalities. In the same way, the MySQL port can change from 3306 to 3388.
• Moreover, EasyPHP development server (alias Devserver, the red version) doesn't work properly (MSVCR110.dll is missing) but EasyPHP hosting server (alias Webserver, the blue one) yes. However, it launched automatically at each boot which slows the system significantly. To avoid this, execute services.msc, and toggle the three services below in manual start. Then to launch them on demand (as an administrator), create a script called MySQL.cmd, containing the following lines:

net start ews-dbserver
net start ews-httpserver
net start ews-dashboard
pause
net stop ews-dashboard
net stop ews-httpserver
net stop ews-dbserver

Single installation

This guide is written from the perspective of using the Linux Shell with Ubuntu and apt-get[1].

If you want to solely use the Terminal:

Make sure you have the MySQL Client and Server installed. To install the client and the server under apt-get distributions (for example Debian and Ubuntu), Execute:

apt-get install mysql-client mysql-client-5.0 mysql-server mysql-server-5.0

About the MySQL package:

[2]

Having a secure installation:

If all your answers are "yes" to what follows, this cleans up your installation, forces you to set a root password, asks you to test for anonymous users and makes your database internal.

Just be careful. Be sure that you are configuring MySQL to the specifications you want.

Here's the code:

mysql_secure_installation

Creating your own MySQL account and database:

Now that MySQL is installed, you wouldn't necessarily have your own account, so you have to log in as root.

To do this type:

sudo mysql -u root -p

(This means that you're logging on as the user "root" (-u root) and that you're requesting the password for "root" (-p) )

Once you've managed to log in, your command-line should look like this: mysql>

By the way, if your command-line ends up looking like this: -> theres an explanation behind it.

In MySQL each command you do has to end with ; . This way it knows that everything behind ; is a command.

So to get out of there, simply type ; There will be more on this later.

Now you can check what databases (if any) are available to your user (in this case "root" ):

show databases;

Let's get straight to the chase and create our own database. Let's call it people. While we're doing this we can also create our own user account. Two birds with one stone.

So first create the database:

create database people; 

(NOTE: in this particular case, you have to be "root" to create new databases.)

Now we want to grant ( GRANT ) all user rights ( ALL ) from ( ON ) the entire ( * ) people database to ( TO ) your account ( yourusername@localhost ) with your user password being stuffedpoodle ( IDENTIFIED BY "stuffedpoodle" ).

So we'd input this as:

GRANT ALL ON people.* TO yourusername@localhost IDENTIFIED BY "stuffedpoodle";

Tada! You now have your own user account. Let's say you chose ted as your username. You've configured MySQL to say that ted can play around with the people database in whatever ways he wishes.

Now get out of MySQL by typing

exit

To start working with the people database, you can now login as ted:

mysql -u ted -p

Creating tables with information in your database:

In MySQL information is stored in tables. Tables contain columns and rows.

Ted has now created a people database. So we want now to enter some information into a table.

Login as ted.

Firstly, we need to make sure we're working with the people database. So typing:

 select database(); 

will show you what database you're currently using. You should see a NULL , meaning that you're working with nothing at the moment.

So to start using the people database, type:

\u people

(NOTICE: Typing: USE people OR logging in as mysql people -u ted -p is also acceptable.)

So how to create a table.

Keep in mind that we need to set all the column values (like surname, age etc.).

Now, remember that annoying -> symbol? MySQL reads your command as just one command, not a series. So, -> enables you to enter your inputs in a nicer way than just writing everything on one line. (NOTE: The problem with this method is that if you screw up on a line and press ENTER to go to the next line, you can't go back and fix your mistake. That's why a nice way to do this is using something like SciTE Text Editor (set language to SQL) to write your code and just copy/paste that into the shell.)

Another thing is that you must separate your lines with , at the end of each line except when you've written your two last lines. On the second to last line, don't add , and the last line always ends with ; .

First I have to explain a few things so you're not blown away by an unfamiliar bunch of code.

If you don't know, we use brackets () to encapsulate code. (Often called parenthesis).

The first thing we will be writing after the CREATE TABLE tableName and the first bracket will be the database ID number(we use integers [3]) of each person, mainly known as the Primary Key. It's kinda like a passport ID number. Each number is unique to its owner and it has to be to prevent duplication and imposters.

Now, any variable in SQL is created as

 variableNAME variableTYPE otherVariableAttributes

. So in order to define the Primary Key variable, we need to type for example:

peopleID(variableNAME) int(variableTYPE - short for "integer") unsigned(means we want our integer value to always be a positive number) not null(we want each row to have a value, so obviously the value can't be empty(NULL) ) auto_increment(this ensures that each new row that is created will be a unique value) primary key(we are saying that this particular variable will be our Primary Key for this Table.), (a reminder that the , symbol indicates the end of this line so MySQL knows to go to the next line)

You already know about the int variable. There is another which is kinda like String (for example: if you've programmed in Java before). It's called varchar which stands for variable characters. You set the amount of characters someone is able to input into a varchar variable. Like this: nameOfFattestMooseAlive varchar(30) So nameOfFattestMooseAlive can have a maximum of 30 characters.

Okay, so let's see an example of how to create a table relating to the people database:

CREATE TABLE peopleInfo

(

peopleID int unsigned not null auto_increment primary key,

firstName varchar(30),

lastName varchar(30),

age int,

gender varchar(13)

);

Just a note that I set the maximum value of gender to 13 because "hermaphrodite" has 13 characters. :)

Now you can type: CREATE TABLE peopleInfo and press ENTER if you'd like to start -> and write the rest of the code or you can use SCITE and copy/paste it into your shell.

Great. We now completed our first Table.

Now comes the part when we have to get some actual people into our peopleInfo Table.

Since your already using the people database, you can type

show tables;

to see what tables are currently in your database. To see the properties of your table type:

describe peopleInfo;

So, how to fill in our peopleInfo table with people...

This is done by telling MySQL what rows you are filling in and the actual information/data you want to fill in.

So we want to insert into our table (specifying the rows) and inputting the values(actual data) that we want. (NOTE: We are not filling in the primary key.)

To create our first person you would type this:

INSERT INTO peopleInfo

(firstName, lastName, age, gender)

values

("Bill", "Harper", 17, "male");

Great. Now if you want to printout to the screen all the information about your table, type:

select * from peopleInfo;

and there you have it. Your table now has one person stored in it.

Inserting lots of information into your table:

A brief point that shall be covered later, MySQL backs-up itself in .sql files. The reason this is smart is because it backs-up the actual code inside the text file.

Keeping this in mind, let's say we want to add 10 other people into your peopleInfo table. It would be one hell of a hassle typing each person into existence. What if there were a 1000?

So I've graciously typed out the code of filling in 10 other people to a database. :) Create a blank .txt file and copy/paste this information into it, saving it as tenPeople.sql .

INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Mary", "Jones", 21, "female");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Jill", "Harrington", 19, "female");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Bob", "Mill", 26, "male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Alfred", "Jinks", 23, "male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Sandra", "Tussel", 31, "female");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Mike", "Habraha", 45, "male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("John", "Murry", 22, "male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Jake", "Mechowsky", 34, "male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Hobrah", "Hinbrah", 24, "hermaphrodite");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Laura", "Smith", 17, "female");

Excellent. Now we want to get all these people in our table. exit MySQL and go to the directory where you saved the tenPeople.sql file.

Once there, to get all the data into your database, type:

mysql -u ted -p people <tenPeople.sql

and enter your password.

Now log into MySQL and remember to select the database your using. \u people

Now check again what information you have. There ya go.

Manipulating your database:

Now that we have a database full of people. We can display that information anyway we want.

A brief example would be

select firstName, lastName, gender from peopleInfo;

This would display to the screen only people's names, surnames, and genders. You've not specified that you want people's Database IDs, Numbers, or Ages to be displayed. And the great thing is you can choose whatever you want from the database to be displayed. Now, if you want to delete your table, simply type:

drop table peopleInfo;

Extra conditions:

You can also you extra conditions (filters) through when displaying data.

select * from peopleInfo where gender = 'female';

will display everyone who is female.

(NOTE: letters are enclosed with ' while numbers are plain.)

You can also compare numbers. For example:

select * from peopleInfo where age > 17;

will show everyone in your table who is older than 17.

Little index here:

>   greater than
<   less than
>=  greater or equal to
<=  less than or equal to
<>  not equal to

Let's say we wanted to display all people whose first names began with the letter "j". We would use the LIKE condition. (Makes sense, is your name LIKE the letter "j", well it start with j so yes. :) )

About the LIKE condition.

[4]

select * from peopleInfo where firstName LIKE "j%";

(NOTE: LIKE 's evil opposite cousin is NOT LIKE)

Backing up and restoring your MySQL database:

There is a function called mysqldump. This is a way to backup your database.

Remember how you managed to get information into your database from tenPeople.sql? Well that's how you restore information to a database.

(In this particular case you gotta make sure that in your database you have a table called "peopleInfo")

Now...

To backup your database (in this case backup the people database):

We first have to create the .txt file that we will be backing it up to. Open a blank .txt file and save it as backupfile.sql .

Now we can type:

mysqldump -u ted -p people > backupfile.sql

Congratulations. You have now backuped your people database.

WARNING! mysqldump is one of the worst ways to backup production databases for the following reasons:

• it will take quite a lot of time to dump data
• even more time to restore. Depends on datasize, it can be counted in days!
• locking problem with MyISAM tables or mixed environment

Better solutions are based on binary copy. It allows you to perform non-locking, consistent backups.

For MyISAM or mixed environment:

• LVM snapshots

For InnoDB:

• LVM snapshots
• ZFS snapshots (for Solaris systems)
• InnoDB Hot Backup
• XtraBackup (similar to InnoDB Hot Backup but free)

phpMyAdmin

This graphic interface allows the generation of SQL code by selecting some options with the mouse. This software has its own wiki on http://wiki.cihar.com/pma/Welcome_to_phpMyAdmin_Wiki.

Hello world

To enter the SQL commands:

• Launch MySQL in shell:
  • Linux: mysql -h localhost -u root MyDB
  • Windows: "C:\Program Files (x86)\EasyPHP\binaries\mysql\bin\mysql.exe" -h localhost -u root MyDB
• Or open an SQL window in PhpMyAdmin (eg: http://localhost/modules/phpmyadmin/#PMAURL-1:server_sql.php?server=1).

select "hello world";
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)