There are two basics ways to create a Database in MySQL. That is, using the command line or Workbench. In this article, we will guide on creating a database in MySQL using the command line, Workbench and via GUI for Websites.
Do note that if you want to create a database for your website and you have some sort of Control Panel installed like cPanel or Plesk, you can create a database without using the command line. This method requires you to access phpMyAdmin. We will explain this a bit later. Let's learn the command line method first.
How to create Database in MySQL?
Creating Database using Command Line
- SSH into your Server or Directly Login to MySQL if you are doing this on localhost.
- Create a New Database users using the following command "GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'localhost' IDENTIFIED BY 'Your_Password';"
- Make Sure to replace 'db_user' with the Database username you want and 'Your_Password' with your desired password
- You can use the command "SHOW DATABASES" to view all the databases which already exists.
- Enter Command "CREATE DATABASE db_name" and Press Enter
Do note that if you want your database name 'ABCD', you will have to enter "CREATE DATABASE ABCD"
MySQL is not case sensitive which means that it doesn't matter if you enter the command as "create database abcd" or "CREATE DATABASE ABCD".
- You can access the database using the command "USE db_name" where db_name is to be replaced by the name of the database you just created.
That's it! You have just learned about creating a database in MySQL using the command line.
Creating Database using Workbench
- Launch MySQL Workbench and click on the Plus Icon next to MySQL Connections. Also known as Setup New Connection button
- Type your connection name ('local' in most cases) and click on Test Connection
- Enter your Password, Check 'Save Password in Vault' and click on OK
- Double click the connection name "Local" in order to connect to the MySQL server.
- A new window will open
- Click on 'Create a new schema in the connected server' button in the toolbar. It looks like a Database sign.
Do note that Creating a New Schema means creating a new database.
- A new window will open where you will have to enter your schema name (database name).
- You can change the Charset (Character Set) and collation. Its better to leave the default as of now.
- Click on Apply
- A new window will open with the following script "CREATE SCHEMA db_name" where db_name represents your database name.
- Click on Apply.
- Once done, you will be able to see your database name mentioned under the schemas tab.
- Select and Right-click on the database you just created and click on 'Set as Default Schema'
That's it! You have just learned how to create a database/schema using Workbench.
Creating a Database using phpMyAdmin
As the name suggests, it is mostly for website owners having a control panel that offers the option to use phpMyAdmin. Most control panels like cPanel, Plesk and DirectAdmin will offer phpMyAdmin which makes working with databases a lot easier.
- Open phpMyAdmin
- Click on 'Databases' from the toolbar. The option is right next to SQL and Status.
- Enter the database name you want to create and click on OK.
- Click on Users on the same toolbar.
- Click on Add User
- Enter Your Desired Username and Password and click on 'Add User'
- Grant all the privileges and click on 'Add User'.
Do note that this will grant User Privileges for all Databases.
- You can also click on "Edit Privileges" to assign privileges for some specific database.
Also Read: How to start a successful blog/ Website
Now you can create databases in MySQL using command line, Workbench and even phpMyAdmin.
That's a lot to learn for today.
What are Databases?
An organized collection of data that is stored in a computer system and can be accessed using the same is called Database.
In case if all of that was completely out of your understanding, don't be too hard on yourself. There are tonnes of videos available online on YouTube which you can watch to understand databases and creating databases in MySQL.
Remember, MySQL is pronounced as -My-S-Q-L and -My-Sequal. I have seen a lot of beginners doing the mistake. While it sounds cool, it is not correct.