For the purpose of building a Web system, check the construction and operation of a super-basic DB server. (MySQL is used as the DB server.)
--DB server - EC2：t2.micro - OS：Red Hat Enterprise Linux 8 (HVM), SSD Volume Type --Disk: General-purpose SSD (GP2) 10GB - MySQL：MySQL 8
The security group settings are nice.
ec2-Login as user
Switch to root user
$ sudo su -
Confirmation of existence of rpm
# yum info mysql*server
Check the version of installed MySQL
# mysqld --version
/usr/libexec/mysqld Ver 8.0.17 for Linux on x86_64 (Source distribution)
Confirm that MySQL service is stopped
# service mysqld status
Active: inactive (dead)Check the output of.
Start the MySQL service
# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
Set password for MySQL root user
# mysql -uroot
> use mysql;
> ALTER USER 'root'@'localhost' identified BY 'password';
(The PASSWORD function seems to be abolished in MySQL 8.)
Confirm that you can log in with the set password
# mysql -uroot -ppassword
In addition, since it is not actual MySQL, mysql_secure_installation is not set.
Perform only the following simple operations.
Check existing DB
> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
Create a new DB
> create database test;
Confirm that a new DB has been created
> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
Specify the DB to use
> use test;
Check existing table
> show tables;
Empty set
Create a table
> create table customer (
-> id int,
-> name varchar(255),
-> age int,
-> sex tinyint);
Check for the existence of the newly created table
> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customer |
+----------------+
Check the rows that exist in the newly created table
> select * from customer;
Empty set
Add a row
> insert into customer values (1,"Yamada Taro",30,1);
Check the newly added line
> select * from customer;
+------+--------------+------+------+
| id | name | age | sex |
+------+--------------+------+------+
| 1 |Yamada Taro| 30 | 1 |
+------+--------------+------+------+
Add a few lines for subsequent operations
> insert into customer values (2,"Hanako Tanaka",25,2);
> insert into customer values (3,"Daisuke Suzuki",43,1);
> insert into customer values (4,"Akiko Yosano",99,2);
> select * from customer;
+------+-----------------+------+------+
| id | name | age | sex |
+------+-----------------+------+------+
| 1 |Yamada Taro| 30 | 1 |
| 2 |Hanako Tanaka| 25 | 2 |
| 3 |Daisuke Suzuki| 43 | 1 |
| 4 |Akiko Yosano| 99 | 2 |
+------+-----------------+------+------+
Check the information of Akiko Yosano to be updated
> select * from customer where id=4;
+------+-----------------+------+------+
| id | name | age | sex |
+------+-----------------+------+------+
| 4 |Akiko Yosano| 99 | 2 |
+------+-----------------+------+------+
Change the age of Akiko Yosano from 99 to 141
> update customer set age=141 where id=4;
Make sure your age has changed
> select * from customer where id=4;
+------+-----------------+------+------+
| id | name | age | sex |
+------+-----------------+------+------+
| 4 |Akiko Yosano| 141 | 2 |
+------+-----------------+------+------+
Check the rows currently stored in the table
> select * from customer;
+------+-----------------+------+------+
| id | name | age | sex |
+------+-----------------+------+------+
| 1 |Yamada Taro| 30 | 1 |
| 2 |Hanako Tanaka| 25 | 2 |
| 3 |Daisuke Suzuki| 43 | 1 |
| 4 |Akiko Yosano| 141 | 2 |
+------+-----------------+------+------+
Delete the line of Akiko Yosano
> delete from customer where id=4;
Check the rows stored in the table after deletion
> select * from customer;
+------+--------------+------+------+
| id | name | age | sex |
+------+--------------+------+------+
| 1 |Yamada Taro| 30 | 1 |
| 2 |Hanako Tanaka| 25 | 2 |
| 3 |Daisuke Suzuki| 43 | 1 |
+------+--------------+------+------+
Create another table and add rows
> create table sex(
-> id tinyint,
-> sex varchar(10));
> insert into sex values (1,"male");
> insert into sex values (2,"Female");
> select * from sex;
+------+--------+
| id | sex |
+------+--------+
| 1 |male|
| 2 |Female|
+------+--------+
Join the customer table and sex table with the sex and sex table id of the customer table,
Search for Japanese names by name and gender
> select customer.name, sex.sex from customer, sex where customer.sex=sex.id;
+--------------+--------+
| name | sex |
+--------------+--------+
|Yamada Taro|male|
|Hanako Tanaka|Female|
|Daisuke Suzuki|male|
+--------------+--------+
Check for existing tables
> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customer |
| sex |
+----------------+
Check the rows stored in the customer table
> select * from customer;
+------+--------------+------+------+
| id | name | age | sex |
+------+--------------+------+------+
| 1 |Yamada Taro| 30 | 1 |
| 2 |Hanako Tanaka| 25 | 2 |
| 3 |Daisuke Suzuki| 43 | 1 |
+------+--------------+------+------+
Truncate customer table
> truncate table customer;
Make sure the customer table is truncated
> select * from customer;
Empty set
Check for existing tables
> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customer |
| sex |
+----------------+
Delete sex table
> drop table sex;
Make sure the sex table has been deleted
> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customer |
+----------------+
Check for existing DB
> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
Delete testDB
> drop database test;
Confirm that testDB has been deleted
> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
next time, Web system construction (super basic) ④: Web system construction is.
Recommended Posts