How to create MySql database using phpMyAdmin

Share me please

There is a moment in creating web applications and also other types of applications that you need to storage data.

You need to have a place in order to do this. You don’t have choice and preparing your own database instance is must be.

In this tutorial I will present your how to run database using Xampp environment and MySql database server.

Before you start you need to make sure that you have install xampp in your computer. You can use my xampp installation instructions if you don’t know how to start.

Running xampp

Xampp is a mix of well know packages like: MySql server, PHP server, Filezilla server, Merkury etc. For the purpose of this tutorial we will use MySql server.

The question is how to start ? Well we only need to go to apache installation folder. In many cases when you installed apache with default settings you should go to:

[code]

C:/xampp

[/code]

and run xampp-control.exe file. Depending on how your system is configured, running xamppa may require administrative privileges.

Run Apache and MySQL services by click Start buttons.

Creating MySQL database

In xampp environment you have out of the box configured system for mysql management online. It is named phpMyAdmin.

In most case you should be able to run in your web browser the address:

[code lang=’console’]
http://localhost/phpmyadmin/
[/code]

As you can see we have default databases installed in the MySQL server. You should see such databases like:

  • information_schema
  • mysql
  • performance_schema
  • phpmyadmin
  • test

Using phpMyAdmin panel we can build our database and table in two different ways: visually or typing sql commands.

In this tutorial we will select the first option and we create database using specialized sites in phpMyAdmin panel.

Let’s start from creating mysql database.

  1. Click Database link (localized in top menu).
  2. In Database name field type your database name for example my_first_database
  3. In drop down list select utf8-unicode-ci coding for your database.
  4. Click Create button.

When you do everything properly you should have defined your first mysql database named my_first_database.

Creating user and granting access to database

We have database but don’t have any user that we could use it. This step is very important because granting correct permission is very important from security reason. The main rule is to as low access as possible.

Adding new user with full access to newly created database my_first_database can be done using one sql script like below:

[code lang=”mysql”]
CREATE USER ‘testing_usr’@’localhost’ IDENTIFIED BY ‘testing_pass’;
GRANT ALL PRIVILEGES ON * . * TO ‘testing_usr’@’localhost’;
FLUSH PRIVILEGES;
[/code]

As I said it is good practice to assign only such privileges as needed for any user. In general we can use one of the available permission types in order to grant correct access.

  • CREATE – enable users to create databases/tables
  • SELECT – permit users to retrieve data
  • INSERT – let users add new entries in tables
  • UPDATE – allow users to modify existing entries in tables
  • DELETE – enable users to erase table entries
  • DROP – let users delete entire databases/tables
  • ALL PRIVILEGES – enable all permission mentioned above in one command

Putting this all together in our case we can try to assing CREATE, SELECT, INSERT and UPDATE permission for user testing_usr for the table my_table using this sql command:

[code lang=”mysql”]
GRANT CREATE, SELECT, INSERT, UPDATE ON my_first_database.my_table TO ‘testing_user’@’localhost’;
[/code]

If you want to grant all these permission to not only my_table table but for all tables you can change the my_table into star sign like below:

[code lang=”mysql”]
GRANT CREATE, SELECT, INSERT, UPDATE ON my_first_database.* TO ‘testing_user’@’localhost’;
[/code]

This knownledge is the minimal one that you need to start developing any IT standard project. For more information you can try to find online course in such portals like: Udemy, Coursera, Skillshare, Udacity or Lynda.

If there is something you wanted to ask then leave a comment, please.

Leave a Reply