Commands often used in MySQL operations

The following commands for working with MySQL are summarized.

・ Login to MySQL ・ Display database list ・ Display table list ・ Display column list

Log in to MySQL

mysql -u username-p

After entering, enter the password. Be careful not to make a mistake because the password is not displayed on the CLI.

Although deprecated, you can log in with a single shot by passing the password in the -p argument.

View database list

SHOW DATABASES;

The list is displayed like this.

mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myapp_development  |
| myapp_test         |
| mysql              |
| performance_schema |
| root               |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

• What is information_schema?
• [What is performance_schema](https://www.google.com/search?rlz=1C5CHFA_enJP911JP911&sxsrf=ALeKk02XCU4i5BPWgjO07tVp_fhww5I_uQ%3A1605346897077&ei=UaavX7-zBPaXr7wPyuG7 % A8% E3% 81% AF & gs_lcp = CgZwc3ktYWIQAzIECAAQHjIGCAAQBRAeOgQIABBHUMWIAlj1iQJg-o0CaABwAngAgAGlAogBnQeSAQUwLjEuM5gBAKABAaoBB2d3cy13aXrIAQHAAQE & sclient = psy-ab & ved = 0ahUKEwj_n6qc34HtAhX2y4sBHcrwDuMQ4dUDCA0 & uact = 5)
• [What is sys](https://www.google.com/search?q=mysql+sys%E3%81%A8%E3%81%AF&rlz=1C5CHFA_enJP911JP911&oq=mysql+sys%E3%81%A8%E3 % 81% AF & aqs = chrome..69i57j0i333l2.9536j0j7 & sourceid = chrome & ie = UTF-8)

Display table list

First, select the database you want to use.

use database name;
ex)use users;

• Be careful not to forget to add ";" at the end of the command. After selecting the database, execute the following command.

SHOW TABLES;

You can get a list of tables in the database.

mysql> SHOW TABLES;
+----------------------+
| Tables_in_myapp_test |
+----------------------+
| posts                |
| schema_migrations    |
| users                |
+----------------------+
3 rows in set (0.01 sec)

• Even if no database is selected

show tables from database name;

You can get it at.

Get the list of columns in the table.

Select the database as before. Execute the following command.

DESCRIBE table name;
show columns from table name;

ex)show columns from users;

You can get the column list in the table like this.

mysql> show columns from users;
+-----------------------------+--------------+------+-----+---------+----------------+
| Field                       | Type         | Null | Key | Default | Extra          |
+-----------------------------+--------------+------+-----+---------+----------------+
| id                          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| email                       | varchar(255) | NO   | UNI | NULL    |                |
| created_at                  | datetime     | NO   |     | NULL    |                |
| updated_at                  | datetime     | NO   |     | NULL    |                |
| name                        | varchar(255) | NO   |     | NULL    |                |
| image                       | varchar(255) | YES  |     | NULL    |                |
+-----------------------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

Connect with MySQL in Docker environment

-Launch the container with docker-compose up. -Docker-compose exec db bash -Connect to the DB container with mysql -u username -p

Connect to RDS from ECS / EC2 container

・ Launch a task ・ Connect to EC2 with ssh -Docker exec -it container ID sh · Mysql -h endpoint -u username -p to connect to RDS