Knowledgebase & Downloads
Intro to MySQL
About MySQL
MySQL, a free and open-source database, is one of the most popular databases in the world.
This tutorial will discuss a basic MySQL installation on a Linux virtual server, and some of the basic MySQL commands.
Full documentation can be found on the MySQL.com website here
1) Installing MySQL
cPanel/WHM will come with MySQL pre-installed. However if you are not using cPanel you will need to install MySQL from the command line.
For users who are unfamiliar with the Linux command line we highly recommend using cPanel/WHM, which will install Apache, PHP and MySQL by default, and allow you to manage your databases, tables and mysql users from a webpage with ease.
For advanced users:
SSH In to your server as root or a user with sudo capabilities.
Then run the appropriate command below for your version of Linux.
CentOS Linux
yum install mysql-server
or
sudo yum install mysql-server
Debian/Ubuntu Linux
apt-get install mysql-server
or
sudo apt-get install mysql-server
2) Log into the MySQL Command Line
mysql -u root -p
mysql is the command for accessing the mysql shell.
The -u flag allows you to specify which mysql user you are logging in as
And, the -p flag tells mysql you are going to provide a password (even if it's a blank password).
If this is your first time logging in after you installed MySQL the root user's password should be blank, simply hit the enter key at the password prompt.
3) Change the root user's password
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new password');
new password will be the new password you want for the user.
4) Basic MySQL Commands
The commands below are a brief list that will get you started with creating a database, adding tables to your database, and inserting rows.
For a more comprehensive list of commands please visit the main MySQL documentation at http://dev.mysql.com/doc/
Creating a MySQL database
CREATE DATABASE test_db;
Selecting a MySQL database to use
USE test_db;
Creating a new table
For our example we are going to create a table for tracking books we've lent out to friends.
CREATE TABLE books (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, title VARCHAR(32), lent_to VARCHAR(32), lent_on INT);
Let's break that command down into smaller pieces:
CREATE TABLE books
CREATE TABLE is the command for creating the table, and books is the name of the table.
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
- Add a column named "id" to this table, with data type of INT (integer)
- Its value cannot be NULL
- It is this table's PRIMARY KEY column
- It automatically increments every time a new row is inserted into the table.
title VARCHAR(32), lent_to VARCHAR(32), lent_on INT
The remaining columns for the table are;
- title of the book, which has a data type of VARCHAR, or Variable Character. and a character length of 32
- lent_to, also a VARCHAR with a length of 32
- lent_on with a type of INT -- for storing unix timestamps.
Inserting a row into your MySQL table
INSERT INTO books (title, lent_to, lent_on) VALUES ('Learning MySQL','Monty Widenius','1412886424');
This will insert a new row of data into the books table.
You will notice that we did not specify an id for the row. This is because we set that column up to be our auto_incrementing primary key -- so MySQL will automatically populate this column with the next integer.
If we were to select this row from our table later on we would see that we lent the book Learning MySQL to Monty Widenius, on October 9th, 2014 at 8:27PM UTC