CentOS7 にmysql8をインストール

CentOS7にバンドルされているmariaDBとデータを削除。# yum remove mariadb-libs
# rm -rf /var/lib/mysql/

https://dev.mysql.com/downloads/repo/yum/

Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent), RPM Package

のダウンロードからリンクアドレスをコピー。（No thanks,just start my download)

# rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
# yum install mysql-community-devel
# yum install mysql-community-server

# yum install  mysql-community-client
# vi /etc/my.cnf

（追加）

log_timestamps=SYSTEM

skip-character-set-client-handshake

character-set-server=utf8mb4

explicit_defaults_for_timestamp=ON

#

# systemctl start mysqld

# systemctl enable mysqld

# mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# 次のコマンドで仮パスワードを検索して入力する。

# cat /var/log/mysqld.log | grep 'password is generated'

新しいパスワードに更新。
mysql -u root -p
初期パスワードを入力

mysql> set password= 'xxxxxxxxx';


CentOS7 safeモードで起動してパスワードをリセット
# systemctl stop mysqld
# systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
# systemctl start mysqld
# mysql -u root
mysql> use mysql
mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root';
mysql> flush privileges;
# systemctl stop mysqld
# systemctl unset-environment MYSQLD_OPTS
# systemctl start mysqld
# mysql_upgrade -u root -p                 
# mysql -u root
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> flush privileges;

※ upgradeしないと下記エラーがでる。
ERROR 1146 (42S02): Table 'mysql.role_edges' doesn't exist

■user追加( mysql_native_password で追加）
mysql> use mysql
mysql>  create user 'admin' identified with mysql_native_password by "No%3177%";
Query OK, 0 rows affected (0.03 sec)
mysql> grant all on *.* to admin with grant option;
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
mysql> select user, host, plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| admin            | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
6 rows in set (0.00 sec)

ユーザ削除
mysql> drop user 'admin';

■テストデータ作成
mysql> create database testDB;
Query OK, 1 row affected (0.17 sec)

mysql> create table testDB.mst001(
    -> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->     title VARCHAR(50),
    ->     content TEXT,
    ->     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.70 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testDB             |
+--------------------+
5 rows in set (0.01 sec)

mysql> describe testDB.mst001;
+------------+-------------+------+-----+-------------------+-------------------+
| Field      | Type        | Null | Key | Default           | Extra             |
+------------+-------------+------+-----+-------------------+-------------------+
| id         | int(11)     | NO   | PRI | NULL              | auto_increment    |
| title      | varchar(50) | YES  |     | NULL              |                   |
| content    | text        | YES  |     | NULL              |                   |
| created_at | timestamp   | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+-------------+------+-----+-------------------+-------------------+
4 rows in set (0.01 sec)

mysql> use testDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_testDB |
+------------------+
| mst001           |
+------------------+
1 row in set (0.00 sec)

mysql>insert into testDB.mst001 values(1,'title01','cont01',20181126);

mysql>insert into testDB.mst001 values(2,'title02','cont02',20181126);

mysql> select * from mst001;
+----+---------+---------+---------------------+
| id | title   | content | created_at          |
+----+---------+---------+---------------------+
|  1 | title01 | cont01  | 2018-11-26 00:00:00 |
|  2 | title02 | cont02  | 2018-11-26 00:00:00 |
+----+---------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> delete from testDB.mst001 where id=1;
Query OK, 1 row affected (0.15 sec)

mysql> select * from mst001;
+----+---------+---------+---------------------+
| id | title   | content | created_at          |
+----+---------+---------+---------------------+
|  2 | title02 | cont02  | 2018-11-26 00:00:00 |
+----+---------+---------+---------------------+
1 row in set (0.00 sec)

mysql>create database temp;
mysql>use temp;
mysql>create table g ( code int, goods varchar(16), price int );
mysql>create table t (id int, code int, qty int);
mysql>insert into g value(1,"りんご",150), (2,"みかん",50),(3,"ぶどう",500);
mysql>insert into t value(1, 1,10),(1, 2,15),(2, 3,18);
mysql>create view  v as select id, code, goods, qty, price, qty*price as amt from t inner join g using(code) where t.id=1;
mysql>select * from g;
mysql>select * from t;
mysql>select * from v;
mysql>drop view v;
mysql>drop table g, t;
mysql>drop database temp;