mysql day 1 (install mysql)

事前準備

檢查 hostname
```
hostname
hostname -f
```
update yum
```
sudo yum update
```
安裝 wget
```
yum install wget
```

安裝 mysql

wget http://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm

安裝 rpm

sudo rpm -ivh mysql80-community-release-el7-1.noarch.rpm

update
```
yum update
```

安裝 mysql community

sudo yum install mysql-community-server

啟動 mysql server
```
sudo systemctl start mysqld.service
```

使用 mysql

一開始會有一個 super user (root’@’localhost)，而密碼會產生在 log 裡，可以用下列指令找出 :
```
sudo grep 'temporary password' /var/log/mysqld.log
```
pwd : DE7vfBpIsd&z

如果要改密碼的話，可用下列指令(需符合一些密碼規則) :
```
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass1!';
```

一些 DB 的操作

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> show tables in mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| ...                       |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
33 rows in set (0.00 sec)

mysql> show tables from mysql like '%time%';
+---------------------------+
| Tables_in_mysql (%time%)  |
+---------------------------+
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+
5 rows in set (0.00 sec)

mysql> create database testdb;
Query OK, 1 row affected (0.04 sec)

mysql> create user 'daniel'@'localhost' identified by 'MyNewPass1!';
Query OK, 0 rows affected (0.13 sec)

mysql> grant all on testdb.* to 'daniel'@'localhost' ;
Query OK, 0 rows affected (0.01 sec)

查看目前有的 user

mysql> SELECT Host, User FROM mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | daniel           |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

離開 mysql
```
mysql> exit
Bye
```
改用剛剛建立的 daniel 登入
```
mysql -u daniel -p
```

可以看到的 db 就沒有 root 那麼多了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testdb             |
+--------------------+
2 rows in set (0.00 sec)

使用 testdb
```
mysql> use testdb;
Database changed
```

建立 table

mysql> create table customers (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name TEXT, tel TEXT);
Query OK, 0 rows affected (0.18 sec)

新增資料

mysql> insert into customers(name,tel) values('daniel','123456');
Query OK, 1 row affected (0.16 sec)

查詢資料

mysql> select * from customers ;
+----+--------+--------+
| id | name   | tel    |
+----+--------+--------+
|  1 | daniel | 123456 |
+----+--------+--------+
1 row in set (0.00 sec)

更新資料

mysql> update customers set tel='888' where id='1';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

刪除資料

mysql> delete from customers where id='1';
Query OK, 1 row affected (0.04 sec)

mysql> update mysql.user set host='%' where user='daniel';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on testdb.* to 'daniel'@'%';
Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

遇到外部連不進去 VM 的問題

關閉防火牆
```
systemctl stop firewalld
```
修改 /etc/my.cnf

用 root 登入 alter user

mysql> ALTER USER 'daniel'@'%' IDENTIFIED WITH mysql_native_password BY 'MyNewPass1!';
Query OK, 0 rows affected (0.05 sec)

重啟 mysql
```
sudo systemctl restart mysqld.service
```

這邊使用的版本是 mysql-connector-java 8.0.11

libraryDependencies += "mysql" % "mysql-connector-java" % "8.0.11"

參考網址
mysql-quick-guide