One of the most important data ecosystem technologies, MySQL is currently used extensively and productively in many sectors of the economy. People who work in general IT or with business data should consider learning about MySQL. Well, it enables even non relational system experts to swiftly build dependable, quick, and secure data storage systems.
hence, MySQL‘s programmatic interfaces and syntax are a great starting place for learning other well known query languages and structured data storage.
Shall we start with MySQL Create Database – and User Command (How To).
What is MySQL?
First of all, MySQL is a key component of many of the most widely used software stacks for creating and sustaining, from robust, data driven B2B services to customer facing web applications. Internet critical enterprises have benefited from its open source nature, security, and broad feature set combined with persistent development and assistance from Oracle.
A database is a type of organised data collection that maintain enormous amounts of data in a corporate network. An RDBMS is a collection of software tools used to create, manage, and query this database. All in all, MySQL is RDBMS (Relational database management system) developed by Oracle based on structured query language (SQL).
The GNU General Public License (GPL) utilised the open source MySQL database programme. It is important to note that it is available in two different editions: the patented technology MySQL Enterprise edition and other commercial products, which require an annual subscription and include professional support as well as many other advantages, and the open source MySQL Community Server, which you download, access the source code for and use for free.
MySQL and its Working Mechanism
Importantly, MySQL is utilised in a networked setting and has a client server architecture like the majority of management systems for databases out there. One or even more devices (clients) interact with a server over a specific network. As long as the request is understood by both ends, each client submits a request using the graphical user interface (GUI) displayed on their displays. The server then responds with the expected outcomes.
- MySQL defines the relationship between each table as it constructs a database for storing and managing data.
- The server is accessed by various client programmes (including MySQL tools or another programming language).
- Requests from clients are handled by the server, which then sends the responses back to the client.
- The server programme responds with the requested data, which the client then accesses.
- It’s important to note that clients cannot connect to the MySQL server until it operates.
We are now at the centre of the article title MySQL Create Database – and User Command (How To).
MySQL Create Database - and User Command (How To)
Prerequisites
- A root user or a user with sudo privileges.
Verify MySQL Installation
systemctl status mysql
You will get the following output.
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2023-02-02 05:03:38 UTC; 10s ago
Process: 8741 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 8749 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 4579)
Memory: 365.3M
CPU: 1.080s
CGroup: /system.slice/mysql.service
└─8749 /usr/sbin/mysqld
Feb 02 05:03:37 linux systemd[1]: Starting MySQL Community Server...
Feb 02 05:03:38 linux systemd[1]: Started MySQL Community Server.
Now, you also verify the MySQL version using the following command.
mysqld --version
This gives you the version of MySQL in the following output.
/usr/sbin/mysqld Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
Create a Database in MySQL
The basic syntax to create a database in MySQL is shown below:
CREATE DATABASE [database name]
Let’s create a new database named mydb using the following command.
CREATE DATABASE mydb;
You get an error if the mydb database is exists in your system. So it is recommended to use the IF NOT EXISTS clause to prevent errors.
CREATE DATABASE IF NOT EXISTS mydb;
The MySQL database also used to store data other than English. In this case, you need to define the character set when creating a database. For example, create a database with ascii character set, run the following command.
CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET ascii COLLATE ascii_general_ci;
Please verify the created database using the following command.
SHOW DATABASES;
This shows you your database in the following screen.
If you want to see database with character set, and collation of the database, run the following command.
SHOW CREATE DATABASE mydb;
You should see the following screen.
Create a Table in MySQL
Tables in MySQL are a database objects that contain all the data in a database. The data is organized in a row and column format. The basic syntax to create a table in MySQL is shown below.
CREATE TABLE [IF NOT EXISTS] `TableName` (`fieldname` dataType [optional parameters]) ENGINE = storage Engine;
To create a table, first you need to change the database to the database where you want to create a table.
USE mydb;
Next, create a table named tasks with the following command.
CREATE TABLE IF NOT EXISTS tasks (
task_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
start_date DATE,
due_date DATE,
status TINYINT NOT NULL,
priority TINYINT NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB;
Now verify the created table using the following command.
SHOW tables;
If you want to see the detailed information of table, run the following command.
DESCRIBE tasks;
You should see the following screen.
Create a User in MySQL
CREATE USER [user-name] IDENTIFIED BY [password];
To create a user named myuser for local system, run the following command.
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'yourpassword';
CREATE USER 'myuser'@'remote_ip_address' IDENTIFIED BY 'yourpassword';
Also create a user that connects from any remote system.
CREATE USER 'myuser'@'%' IDENTIFIED BY 'yourpassword';
To verify your created user, run the following command.
SELECT user from mysql.user;
You should see the user information on the following screen.
To see the information of currently login user, run the following command.
Select current_user();
You will see the following output.
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
Grant Permissions In MySQL
After creating user in MySQL, you also need to grant user permission to access the database. The basic syntax to grant permission is shown below.
GRANT [permission type] ON [database name] TO 'user'@'localhost';
For example, if you want to grant all permission to MySQL user on all databases, run the following command.
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost';
To grant all permission to MySQL user on a specific databases, run the following command.
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
If you want to grant only INSERT permission to MySQL user, run the following command.
GRANT INSERT ON *.* TO 'myuser'@'localhost';
To see the granted permissions, run the following command.
SHOW GRANTS FOR myuser@'localhost';
You should see the folowing screen.
If you want to revoke privileges from a specific user, run the following command.
REVOKE ALL ON mydb.* TO 'myuser'@'localhost';
That is the end of the article MySQL Create Database – and User Command (How To). Let’s conclude it now.
MySQL Create Database - and User Command (How To) Conclusion
In this post, we explained how to create a database and user in MySQL. Last of all, MySQL was built to be interoperable with other systems. Users transfer their data to a SQL Server database utilising database migration tool. When measured against important criteria like the number of occurrences in search results, the number of professional accounts, and the frequency of technical conversations on online forums, MySQL comes in second only to Oracle Database. The position is further cemented by the fact that numerous significant tech behemoths depend on it.
Why don’t you explore more of MySQL by navigating to this section of the blog.
