SBS: Spring MVC - Today in History 02 - Prepare the MySQL Database

Copy this link when reproducing:
http://www.casperlee.com/en/y/blog/90

 

Download, install MySQL, and create a database

1. Download "MySQL Community Server" from https://dev.mysql.com/downloads/

    Here is the .zip file that I have chosen: "mysql-5.7.19-winx64.zip".

2. Unzip "mysql-5.7.19-winx64.zip" to your favorite folder.

    Here is my favorite folder: "C:\Program Files\mysql"

3. Create a file named "my.ini" in the "C:\Program Files\mysql" folder, and put the following text in:

[mysqld]
# set basedir to your installation path
basedir=C:/Program Files/mysql
# set datadir to the location of your data directory
datadir=D:/Download/MyDocuments/mysql/data

4. Run "cmd.exe" in Administrator mode, enter the bin folder of mysql, execute the following command:

C:\Program Files\mysql\bin>mysqld --defaults-file="C:\Program Files\mysql\my.ini" --initialize-insecure

    Note: using "--initialize-insecure", the password of the root user will remain empty.

5. Execute the following command to install mysql as a Windows service:

c:\Program Files\mysql\bin>mysqld --install MySQL --defaults-file="C:\Program Files\mysql\my.ini"

FYI: the following command can uninstall the installed mysql service:

c:\Program Files\mysql\bin>mysqld --remove

6. Execute the following command to start the service:

c:\Program Files\mysql\bin>net start mysql

FYI: the following command can stop the service:

c:\Program Files\mysql\bin>net stop mysql

7. Execute the following command to test the service:

c:\Program Files\mysql\bin>mysqlshow.exe -u root

8. It works! Here is the result of the command in my computer:

C:\Program Files\mysql\bin>mysqlshow -u root
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

9. Since the root password remains empty, I'm gonna change it:

    I. Execute the following command to enter the MySQL monitor:

C:\Program Files\mysql\bin>mysql -u root -p

    II. Execute the following command to modify the root password:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '[YourPassword]';

    III. Execute the following command to quit the MySQL monitor:

mysql> quit

 

FYI: You can get the official installation guide from 

https://downloads.mysql.com/docs/mysql-installation-excerpt-5.7-en.pdf

 

10. Now, it's time to create a database.

    I. Execute the following command to enter the MySQL monitor:

C:\Program Files\mysql\bin>mysql -u root -p

    II. Execute the following command to create a database named "DayDB":

mysql> create database DayDB;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| daydb              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

    III: Create a table named "day" in the database:

mysql> create table day (
    -> dayId int(4) primary key not null auto_increment,
    -> dayKey varchar(10) not null,
    -> dayName varchar(60) not null,
    -> dayDescription varchar(2000) null
    -> );
Query OK, 0 rows affected (0.47 sec)

mysql> CREATE UNIQUE INDEX idx_dayKey on Day(dayKey);
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from day;
Empty set (0.01 sec)

    IV. Input some data into the table for test purpose:

mysql> insert into day (dayKey, dayName, dayDescription) values
    -> ('0914', 'Sep. 14', 'September 14 is the 257th day of the year (258th in leap years) in the Gregorian calendar.');
Query OK, 1 row affected (0.05 sec)

mysql> insert into day (dayKey, dayName, dayDescription) values
    -> ('0915', 'Sep. 15', 'September 15 is the 258th day of the year (259th in leap years) in the Gregorian calendar.');
Query OK, 1 row affected (0.03 sec)

mysql> insert into day (dayKey, dayName, dayDescription) values
    -> ('0916', 'Sep. 16', 'September 16 is the 258th day of the year (259th in leap years) in the Gregorian calendar.');
Query OK, 1 row affected (0.03 sec)

mysql> select * from day;
+-------+--------+---------+--------------------------------------------------------------------------------------------+
| dayId | dayKey | dayName | dayDescription                                                                             |
+-------+--------+---------+--------------------------------------------------------------------------------------------+
|     1 | 0914   | Sep. 14 | September 14 is the 257th day of the year (258th in leap years) in the Gregorian calendar. |
|     2 | 0915   | Sep. 15 | September 15 is the 258th day of the year (259th in leap years) in the Gregorian calendar. |
|     3 | 0916   | Sep. 16 | September 16 is the 259th day of the year (260th in leap years) in the Gregorian calendar. |
+-------+--------+---------+--------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)