This describes the basic steps for installing the MySQL Database Server, create a database, table and perform CRUD operations.
Steps:
(A) Install MySQL server 8
(B) Create a database, table and perform CRUD (Create, Read, Update, Delete) operations.
I had used the following method on my Windows computer (you can use any suitable method based upon your preference and operating system).
Visit the MySQL website and navigate to -> MySQL Community Downloads
- Download MySQL Community Server (Windows (x86, 64-bit), ZIP Archive)
- Unzip into any folder (this is the installation directory).
Start the server, the first time, as follows:
The mysql-installation-dir\data would be the default data directory. But, the data directory can be configured as required (see MySQL Reference Manual topic Initializing the Data Directory).
From the installation directory (of Windows command prompt):
> bin\mysqld --initialize --console
2022-09-06T12:40:28.038720Z 0 [System] [MY-013169] [Server] D:\mysql8\mysql-8.0.30-winx64\bin\mysqld.exe
(mysqld 8.0.30) initializing of server in progress as process 4864
2022-09-06T12:40:28.080720Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-09-06T12:40:32.293961Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-09-06T12:40:37.248246Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: JoEbJkuj,5HJ
With the data ditectory initialization, a user account 'root'@'localhost' (referred as root user) is created by the MySQL.
This user is to be used for administrative purposes, and has all the privileges to perform any operation on the server.
Note the temporary password on the last line of the log output, above, for this user. This temporary password is to be changed at a later stage, and is described below (Step (4)).
> bin\mysqld --console
2022-09-06T12:47:28.645777Z 0 [System] [MY-010116] [Server] D:\mysql8\mysql-8.0.30-winx64\bin\mysqld.exe
(mysqld 8.0.30) starting as process 15272
2022-09-06T12:47:28.677777Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-09-06T12:47:31.827957Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-09-06T12:47:34.585115Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-09-06T12:47:34.594115Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS.
Encrypted connections are now supported for this channel.
2022-09-06T12:47:34.937137Z 0 [System] [MY-011323] [Server] X Plugin ready for connections.
Bind-address: '::' port: 33060
2022-09-06T12:47:34.939135Z 0 [System] [MY-010931] [Server] D:\mysql8\mysql-8.0.30-winx64\bin\mysqld.exe: ready for connections.
Version: '8.0.30' socket: '' port: 3306 MySQL Community Server - GPL.
In another terminal window, start the mysql client program and change the initial temporary password (noted above earlier at
Initialize data directory, step (2)).
> bin\mysql -u root -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_changed_password';
Exit mysql client.
> bin\mysqladmin -u root -p shutdown
Before we can add some data to the database and the table, these need to be created.
You can use any available tools to create the database and table - the mysql client or the MySQL Workbench.
Here we use the mysql client. Here are some commonly used commands. and the commands to create the database, tables, some sample data and query the data.
First, connect to the MySQL server using the mysql client (note the server should be up and running).
> bin\mysql -u root -p
mysql> SELECT USER(), VERSION(), CURRENT_DATE; -- show details about the user, server version and date
mysql> SHOW DATABASES; -- list all the databases on the server
mysql> CREATE DATABASE test_db; -- create a new database
mysql> USE test_db; -- connect to the database to work with
mysql> SELECT DATABASE(); -- show the database you are connected to (the current database)
-- create a table and query it
mysql> CREATE TABLE items (name VARCHAR(20) NOT NULL PRIMARY KEY, quantity INTEGER NOT NULL);
mysql> SHOW TABLES; -- list all tables in the current database
mysql> DESCRIBE items; -- show a table definition
mysql> INSERT INTO items VALUES ('Coca cola', 6); -- add data to the table
mysql> SELECT * FROM items; -- query (or read) the table
mysql> UPDATE items SET quantity=4 WHERE name='Coca cola'; -- update data in the table
mysql> DELETE FROM items WHERE name='Coca cola'; -- delete data from the table
-- DONE.