MySQL(5.x, 8.x) 기초 설치 Ubuntu(16.x), 테이블 용량확인

1. MySQL 5.x

Install

sudo apt-get install mysql-server-5.7

설치확인

/etc/init.d/mysql status

Port확인

sudo netstat -nltp|grep mysql

mysql root 로그인

mysql -uroot -p

mysql 한글설정

vim /etc/mysql/conf.d/mysql.cnf
#이하내용작성 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
character-set-server=utf8
collation-server=utf8_general_ci
init_connect = set collation_connection = utf8_general_ci
init_connect = set names utf8
 
[mysql]
default-character-set=utf8
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-character-set=utf8
 
[client]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

mysql restart

sudo /etc/init.d/mysql restart

mysql database 생성 및 유저생성

create database test_db;
create user 'test_user'@'%' identified by '1234';
grant all privileges on test_db.* to 'test_user'@'%' identified by '1234';
#등록확인
use mysql
select host, user from user

mysql Binding IP 변경

jdbc:mysql://ip:port/TestDB?characterEncoding=UTF-8&serverTimezone=serverTimezone=Asia/Seoul  //UTC

 

2. MySQL 8.x

install

wget -c https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb 
sudo dpkg –i mysql-apt-config_0.8.10-1_all.deb
sudo apt-get update
sudo apt-get install mysql-server

status

$ sudo systemctl status mysql

restart

$ sudo systemctl restart mysql

 

3. 테이블 용량확인

select round(data_length / (1024 * 1024), 2)  as 'DATA_SIZE(MB)',
       round(index_length / (1024 * 1024), 2) as 'INDEX_SIZE(MB)'

from information_schema.TABLES
where table_name = '테이블명'