Additional menu

How to Install and Basic MySQL Command Examples

How to Install and Basic MySQL Command Examples

Geek Flare Blog post is sponsored by Netsparker Web Application Security Scanner.

MySQL is one of the most popular database management systems. It is most often used for web-based application, and it is also one of the main components of the LAMP (Linux, Apache, MySQL, and PHP) open-source web application stack.

In this tutorial, we will take a look at how to install MySQL on Ubuntu and CentOS with some basic commands.

We will be using the command line to execute MySQL commands, so we assume you have shell access to your webserver. Alternatively, you can use phpMyAdmin or SQL client to handle the administration of MySQL over the web.

Installation

Before we begin, make sure you already have MySQL (or MariaDB) installed on your system. You can get a MySQL VPS and experiment with MySQL commands on your server.

If you don’t have it installed, while logged in as a root user, you can easily do it by executing the following command:

For Ubuntu:

apt-get install mysql-server

For CentOS:

yum install mysql-server

Once installed, you can log in as the root user (using the password you have entered during the installation), with the following command:

mysql -u root -p

If you have successfully logged in you should see the following prompt line:

mysql>

Basic MySQL commands

From here we can start executing MySQL commands. We will take a look at some of the most basic commands.

To see a list of all databases:

SHOW DATABASES;

Your output should look something like this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)

To create a new database, type the following command:

CREATE DATABASE DatabaseName;

To delete a database, execute the following:

DROP DATABASE DatabaseName;

To set a database as the default database, we want to use:

USE DatabaseName;

Working with tables

We can now add a new table to our database. To create a new table, run the following command:

CREATE TABLE test_table ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, column1 VARCHAR(40), column2 VARCHAR (50) );

With this command, we have created a new table called test_table, with three columns. In this example, the id column shows the number of each row. The column1 and column2 have a character limit of 40 and 50 characters each, respectively

The following command shows all the tables we have just created in our database:

SHOW TABLES;

Your output should look like this:

+------------------------+
| Tables_in_DatabaseName |
+------------------------+
| test_table             |
+------------------------+
1 row in set (0.00 sec)

To see the table’s structure:

DESCRIBE test_table;

You should see the following:

+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| column1 | varchar(40) | YES  |     | NULL    |                |
| column2 | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Once we have created a table we can now insert new rows into it (we can insert as many rows as we want):

INSERT INTO test_table (id, column1, column2) VALUES (NULL, 'name1', 'name2');

To show all the data we have inserted in a table:

SELECT * FROM test_table;

We will get the following output:

+----+---------+---------+
| id | column1 | column2 |
+----+---------+---------+
| 1  | name1   | name2   |
+----+---------+---------+
1 row in set (0.00 sec)

If something goes wrong and we want to start over again, we can delete all existing data we have entered with the following command:

DELETE FROM test_table;

And if we want to remove the whole table, execute the following command:

DROP TABLE test_table;

Finally, to exit MySQL, we just type:

exit

Creating new user

In MySQL, you can also create new users and grant them access to any information they would need. Here is an example for creating a new MySQL database user and granting database permissions to that user.

First, we create a new database:

CREATE DATABASE DatabaseName;

Next, we create a new user with the following command:

CREATE USER 'DatabaseUser'@'localhost' IDENTIFIED BY 'password';

To grant all privileges to this user so that he can have full control over this database, we do the following:

GRANT ALL ON DatabaseName.* TO 'DatabaseUser'@"localhost";

And finally, to save all the changes we have made, we need to reload the privileges:

FLUSH PRIVILEGES;

We can now exit MySQL and log back in as that user by executing the following command:

mysql -u DatabaseUser -p

Backup and restore

It is critical to regularly backup your databases, so you don’t lose any important data you need. Here, we will show you how to make a backup and restore your databases using the command line.

To dump a database execute the following command:

mysqldump -u DatabaseUser -p DatabaseName > BackupDatabase.sql

With this command, we connect to the MySQL server and create a SQL dump file which contains all the SQL statements needed to recreate the database. In this particular example, we make a backup of the database named ‘DatabaseName’ with username ‘DatabaseUser’ and no password, into a file called ‘BackupDatabase.sql.’

And to restore a database, execute the following:

mysql -u DatabaseUser -p DatabaseName < BackupDatabase.sql

With this, we used the ‘BackupDatabase.sql’ file to recreate the database named ‘DatabaseName.’

You should get pretty familiar with MySQL and how it’s used with these basic command examples. To learn more about MySQL and MySQL commands, visit their official documentation.

Article by Jeff from RoseHosting.com

Reader Interactions

Chandan Kumar
About Chandan
Chandan Kumar is the founder of Geek Flare. Learn more here and connect with him on Twitter.

Leave a Reply

Your email address will not be published. Required fields are marked *

10 Shares
Share
Tweet
Stumble
Share