출처
이전달 마지막날 구하기
select LAST_DAY(NOW() - interval 1 month)첫째날 구하기 (이전달 마지막날 + 1일)
select LAST_DAY(NOW() - interval 1 month)+ interval 1 DAY자주 사용하는 경우 함수로 만들기
DELIMITER $$
DROP FUNCTION IF EXISTS FIRST_DAY$$
CREATE FUNCTION FIRST_DAY(p_src_day DATE) RETURNS DATE
BEGIN
RETURN LAST_DAY(p_src_day - interval 1 month)+ interval 1 DAY;
END$$
DELIMITER ;호출 예
select FIRST_DAY(now())출처
리눅스 정보 조회
출처 : 리눅스 종류 확인, 리눅스 버전 확인 - 제타위키
$ cat /etc/*-release
NAME="Amazon Linux"
VERSION="2"
ID="amzn"
ID_LIKE="centos rhel fedora"
VERSION_ID="2"
PRETTY_NAME="Amazon Linux 2"
ANSI_COLOR="0;33"
CPE_NAME="cpe:2.3:o:amazon:amazon_linux:2"
HOME_URL="https://amazonlinux.com/"
Amazon Linux release 2 (Karoo)관련패키지 설치
$ sudo yum update $ sudo yum install gcc gcc-c++ openssl curl autoconf ncurses-devel bison zlib curl libtermcap-devel bzip2-devel
MySQL 계정 만들기
$ sudo groupadd mysql
$ sudo useradd -g mysql -s /bin/bash -m mysqlcmake 설치
$ sudo yum install cmake
MySQL 소스 다운로드, 압축해제, 컴파일, 설치
$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.23.tar.gz
$ tar xvf mysql-boost-5.7.23.tar.gz
$ cd mysql-5.7.23
$ cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql57 \
-DMYSQL_DATADIR=/usr/local/mysql57/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql57/mysql.sock \
-DSYSCONFDIR=/usr/local/mysql57 \
-DMYSQL_TCP_PORT=5723 \
-DMYSQL_USER=mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DDOWNLOAD_BOOST=0 \
-DWITH_BOOST=./boost
$ make
$ sudo make install환경설정
$ sudo vi /usr/local/mysql57/my.cnf[mysqld] # port=3306 # basedir=/usr/local/mysql57 # datadir=/usr/local/mysql57/data # pid-file=/usr/local/mysql57/mysqld.pid # log_error=/usr/local/mysql57/mysql_error.log # lc-messages-dir=/usr/local/mysql57/share init_connect=SET collation_connection = utf8_general_ci init_connect=SET NAMES utf8 character-set-server=utf8 collation-server=utf8_general_ci # table_cache=1024 max_connections=2048 max_user_connections=500 max_connect_errors=10000 wait_timeout=300 query_cache_type = 1 query_cache_size = 128M query_cache_limit = 5M slow_query_log long_query_time=3 max_allowed_packet=16M sort_buffer_size = 2M # skip-innodb skip-name-resolve sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION [mysql] default-character-set=utf8 [client] default-character-set=utf8
MySQL 설치 디렉토리 mysql 계정으로 권한 수정
$ sudo chown -R mysql:mysql /usr/local/mysql57MySQL 데이터베이스 초기화 (mysql 계정으로 실행)
$ cd /usr/local/mysql57 $ bin/mysql_install_db --no-defaults --user=mysql --datadir=/usr/local/mysql57/data --basedir=/usr/local/mysql57 -v 2021-05-08 08:15:24 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize 2021-05-08 08:15:24 [NOTE] Creating data directory /usr/local/mysql57/data 2021-05-08 08:15:24 [NOTE] Generating random password to /root/.mysql_secret...done. 2021-05-08 08:15:24 [NOTE] Setting file ownership to mysql 2021-05-08 08:15:24 [NOTE] Executing /usr/local/mysql57/bin/mysqld --no-defaults --bootstrap --datadir=/usr/local/mysql57/data --lc-messages-dir=/usr/local/mysql57/share --lc-messages=en_US --basedir=/usr/local/mysql57 2021-05-08 08:15:24 [NOTE] Creating system tables...done. 2021-05-08 08:15:24 [NOTE] Filling system tables with data...done. 2021-05-08 08:15:25 [NOTE] Filling help table with data...done. 2021-05-08 08:15:25 [NOTE] Creating user for internal session service...done. 2021-05-08 08:15:25 [NOTE] Creating default user root@localhost 2021-05-08 08:15:25 [NOTE] Creating default proxy root@localhost 2021-05-08 08:15:25 [NOTE] Creating sys schema 2021-05-08 08:15:25 [NOTE] done. 2021-05-08 08:15:26 [WARNING] The bootstrap log isn't empty: 2021-05-08 08:15:26 [WARNING] 2021-05-08T08:15:24.294574Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead 2021-05-08T08:15:24.299965Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2021-05-08T08:15:24.299974Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 2021-05-08 08:15:26 [NOTE] Generating SSL Certificates
MySQL 서비스 등록
서비스 mysqld 파일 복사
$ sudo cp /usr/local/mysql57/support-files/mysql.server /etc/init.d/mysqld57mysqld 수정
$ sudo vi /etc/init.d/mysqld57mysqld파일을 열어서 basedir에 mysql이 설치된 디렉토리와 데이터 디렉토리(datadir)를 설정한다.
basedir=/usr/local/mysql57
datadir=/usr/local/mysql57/datamysqld 서비스 등록
$ sudo chkconfig --add mysqld57
$ sudo chmod +x /etc/init.d/mysqld57
$ chkconfig --list
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
mysqld57 0:off 1:off 2:on 3:on 4:on 5:on 6:off
netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off
network 0:off 1:off 2:on 3:on 4:on 5:on 6:off
tomcat 0:off 1:off 2:off 3:on 4:on 5:on 6:off
$ sudo chkconfig --level 345 mysqld57 onmysqld 서비스 실행
$ sudo service mysqld57 start
Starting MySQL. SUCCESS!root 암호 초기화
mysqld 실행
$ sudo bin/mysqld_safe --skip-grant-tables & [1] 11505 2021-05-08T08:56:04.765371Z mysqld_safe Logging to '/var/log/mysqld.log'. 2021-05-08T08:56:04.792894Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
root 암호 설정
$ bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23-log Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql
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> update user set authentication_string = password('manager') where user = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
ERROR 1820 (HY000) 오류 해결
mysql> use mysql
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD = PASSWORD('soY&Be9p/4ed!!!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> use mysql
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>
사용자 추가
mysql> create user 'user1'@'%' identified by 'userpw'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on *.* to 'user1'@'%' with grant option; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
사용자 추가 - 특정 DB에 권한 부여
mysql> create user 'terecal'@'%' identified by '****'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges ON terecal_db.* TO 'terecal'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
출처
MySQL 스케줄러 사용 여부 확인
show variables like 'event%';
mysql.ini 파일 수정
[mysqld] event_scheduler = ON
테이블 생성
CREATE TABLE `work_time` ( `work_time` DATETIME DEFAULT NULL )
초마다 질의문 실행
CREATE EVENT IF NOT EXISTS sch_work_time ON SCHEDULE EVERY 1 SECOND -- 초마다 실행 STARTS CURRENT_TIMESTAMP DO update work_time set work_time = now();
5분 마다 실행 프로시져 실행
CREATE EVENT IF NOT EXISTS arc_sch_work_time ON SCHEDULE EVERY 5 MINUTE -- 5분마다 실행 STARTS CURRENT_TIMESTAMP DO call STATISTICS();
출처
MariaDB 서비스 등록
C:\server\mysql\mariadb-10.3.12-winx64>bin\mysql_install_db --datadir=C:\server\mysql\mariadb-10.3.12-winx64\data --service="MariaDB 10312" --port=3306 --password=xxxx
Running bootstrap
2019-02-10 12:39:34 0 [Note] C:\server\mysql\mariadb-10.3.12-winx64\bin\mysqld.exe (mysqld 10.3.12-MariaDB) starting as process 3784 ...
Removing default user
Setting root password
Creating my.ini file
Registering service 'MariaDB 10312'
Creation of the database was successful
C:\server\mysql\mariadb-10.3.12-winx64>root 사용자 암호등록
C:\server\mysql\mariadb-10.3.12-winx64>bin\mysql -u root -p
Enter password: ******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10 Server version: 10.3.12-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use mysql;
Database changed
MariaDB [mysql]> select user, host from mysql.user;
+-------------+-----------------+
| User | Host |
+-------------+-----------------+
| root | 127.0.0.1 |
| root | ::1 |
| root | desktop-b09gsin |
| mariadb.sys | localhost |
| root | localhost |
+-------------+-----------------+
5 rows in set (0.002 sec)
MariaDB [mysql]> alter user 'root'@'localhost' identified by 'sqldba';
Query OK, 0 rows affected (0.004 sec)출처
리눅스 정보 조회
출처 : 리눅스 종류 확인, 리눅스 버전 확인 - 제타위키
$ cat /etc/*-release | uniq DISTRIB_ID=Ubuntu DISTRIB_RELEASE=18.04 DISTRIB_CODENAME=bionic DISTRIB_DESCRIPTION="Ubuntu 18.04.1 LTS" NAME="Ubuntu" VERSION="18.04.1 LTS (Bionic Beaver)" ID=ubuntu ID_LIKE=debian PRETTY_NAME="Ubuntu 18.04.1 LTS" VERSION_ID="18.04" HOME_URL="https://www.ubuntu.com/" SUPPORT_URL="https://help.ubuntu.com/" BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/" PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy" VERSION_CODENAME=bionic UBUNTU_CODENAME=bionic
리눅스 비트 확인
$ getconf LONG_BIT 64
관련패키지 설치
$ sudo apt-get update $ sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev
MySQL 계정 만들기
$ sudo groupadd mysql $ sudo useradd -g mysql -s /bin/bash -m mysql
cmake 설치
$ sudo apt-get install cmake
mariadb 소스 다운로드, 압축해제, 컴파일, 설치
$ wget https://downloads.mariadb.org/interstitial/mariadb-10.3.12/source/mariadb-10.3.12.tar.gz $ tar xvf mariadb-10.3.12.tar.gz $ cd mariadb-10.3.12/ $ sudo cmake \ -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb103 \ -DMYSQL_DATADIR=/usr/local/mariadb103/data \ -DMYSQL_UNIX_ADDR=/usr/local/mariadb103/mysql.sock \ -DSYSCONFDIR=/usr/local/mariadb103 \ -DMYSQL_TCP_PORT=3306 \ -DMYSQL_USER=mysql \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DDOWNLOAD_BOOST=0 -DWITH_BOOST=./boost $ sudo make $ sudo make install
환경설정
$ sudo vi /usr/local/mariadb103/my.cnf
[mysqld] # port=3306 # basedir=/usr/local/mysql57 # datadir=/usr/local/mysql57/data # pid-file=/usr/local/mysql57/mysqld.pid # log_error=/usr/local/mysql57/mysql_error.log # lc-messages-dir=/usr/local/mysql57/share init_connect=SET collation_connection = utf8_general_ci init_connect=SET NAMES utf8 character-set-server=utf8 collation-server=utf8_general_ci # table_cache=1024 max_connections=2048 max_user_connections=500 max_connect_errors=10000 wait_timeout=300 query_cache_type = 1 query_cache_size = 128M query_cache_limit = 5M slow_query_log long_query_time=3 max_allowed_packet=16M sort_buffer_size = 2M # skip-innodb skip-name-resolve sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION [mysql] default-character-set=utf8 [client] default-character-set=utf8
mariadb 설치 디렉토리 mysql 계정으로 권한 수정
$ sudo chown -R mysql:mysql /usr/local/mariadb103
mariadb 데이터베이스 초기화 (mysql 계정으로 실행)
$ cd /usr/local/mariadb103 $ scripts/mysql_install_db --no-defaults --user=mysql --datadir=/usr/local/mariadb103/data --basedir=/usr/local/mariadb103 -v Installing MariaDB/MySQL system tables in '/usr/local/mariadb103/data' ... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/usr/local/mariadb103/bin/mysqladmin' -u root password 'new-password' '/usr/local/mariadb103/bin/mysqladmin' -u root -h bluesanta-desktop password 'new-password' Alternatively you can run: '/usr/local/mariadb103/bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '/usr/local/mariadb103' ; /usr/local/mariadb103/bin/mysqld_safe --datadir='/usr/local/mariadb103/data' You can test the MariaDB daemon with mysql-test-run.pl cd '/usr/local/mariadb103/mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/
root 암호 초기화
$ bin/mysqld_safe --skip-grant-tables & [1] 11396 mysql@bluesanta-desktop:/usr/local/mariadb103$ 190122 23:11:49 mysqld_safe Logging to '/usr/local/mariadb103/data/bluesanta-desktop.err'. 190122 23:11:49 mysqld_safe Starting mysqld daemon with databases from /usr/local/mariadb103/data
mysql@bluesanta-desktop:/usr/local/mariadb103$ bin/mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.3.12-MariaDB-log Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use mysql 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 MariaDB [mysql]> update user set authentication_string = password('sqldba') where user = 'root'; Query OK, 4 rows affected (0.001 sec) Rows matched: 4 Changed: 4 Warnings: 0 MariaDB [mysql]> flush privileges; Query OK, 0 rows affected (0.000 sec) MariaDB [mysql]> quit Bye
mariadb 서비스 등록
서비스 mariadb 파일 복사
$ sudo cp /usr/local/mariadb103/support-files/mysql.server /etc/init.d/mariadb
mariadb 수정
$ sudo vi /etc/init.d/mariadb
mariadb파일을 열어서 basedir에 mariadb가 설치된 디렉토리와 데이터 디렉토리(datadir)를 설정한다.
basedir=/usr/local/mariadb103 datadir=/usr/local/mariadb103/data # Try to find basedir in /etc/my.cnf conf=/usr/local/mariadb103/my.cnf
mariadb 서비스 등록
$ sudo update-rc.d mariadb defaults
mariadb 서비스 실행
$ sudo systemctl start mariadb.service
$ sudo systemctl status mariadb.service
● mariadb.service - LSB: start and stop MariaDB
Loaded: loaded (/etc/init.d/mariadb; generated)
Active: active (running) since Tue 2019-01-22 22:59:51 KST; 11s ago
Docs: man:systemd-sysv-generator(8)
Process: 10984 ExecStart=/etc/init.d/mariadb start (code=exited, status=0/SUCCESS)
Tasks: 32 (limit: 4915)
CGroup: /system.slice/mariadb.service
├─11058 /bin/sh /usr/local/mariadb103/bin/mysqld_safe --datadir=/usr/local/mariadb103/data --pid-file=/us
└─11191 /usr/local/mariadb103/bin/mysqld --basedir=/usr/local/mariadb103 --datadir=/usr/local/mariadb103/
1월 22 22:59:50 bluesanta-desktop systemd[1]: Starting LSB: start and stop MariaDB...
1월 22 22:59:50 bluesanta-desktop mariadb[10984]: Starting MariaDB
1월 22 22:59:50 bluesanta-desktop mariadb[10984]: .190122 22:59:50 mysqld_safe Logging to '/usr/local/mariadb103/da
1월 22 22:59:50 bluesanta-desktop mariadb[10984]: 190122 22:59:50 mysqld_safe Starting mysqld daemon with databases
1월 22 22:59:51 bluesanta-desktop mariadb[10984]: *
1월 22 22:59:51 bluesanta-desktop systemd[1]: Started LSB: start and stop MariaDB.
mariadb 서비스 종료
$ sudo systemctl stop mariadb.service
사용자 추가
mysql> create user 'user1'@'%' identified by 'userpw'; mysql> grant all privileges on *.* to 'user1'@'%' with grant option; mysql> flush privileges;
DB 종속적 사용자 추가
create user 'terecal'@'%' identified by '****'; GRANT ALL privileges ON terecal_db.* TO 'terecal'@'%'; flush privileges;
출처
Ubuntu server에 mysql 5.5.x 설치하기 -pupustory@- - Tistory
[Linux/CentOS] Mysql을 컴파일해서 설치해보자 - AT BLOG
리눅스 정보 조회
출처 : 리눅스 종류 확인, 리눅스 버전 확인 - 제타위키
$ cat /etc/*-release | uniq DISTRIB_ID=Ubuntu DISTRIB_RELEASE=14.04 DISTRIB_CODENAME=trusty DISTRIB_DESCRIPTION="Ubuntu 14.04.3 LTS" NAME="Ubuntu" VERSION="14.04.3 LTS, Trusty Tahr" ID=ubuntu ID_LIKE=debian PRETTY_NAME="Ubuntu 14.04.3 LTS" VERSION_ID="14.04" HOME_URL="http://www.ubuntu.com/" SUPPORT_URL="http://help.ubuntu.com/" BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"
리눅스 비트 확인
$ getconf LONG_BIT 64
관련패키지 설치
$ sudo apt-get update $ sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev
MySQL 계정 만들기
$ sudo groupadd mysql $ sudo useradd -g mysql -s /bin/bash -m mysql
cmake 설치
$ sudo apt-get install cmake
MySQL 소스 다운로드, 압축해제, 컴파일, 설치
$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.23.tar.gz $ tar xvf mysql-boost-5.7.23.tar.gz $ cd mysql-5.7.23 $ sudo cmake \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql57 \ -DMYSQL_DATADIR=/usr/local/mysql57/data \ -DMYSQL_UNIX_ADDR=/usr/local/mysql57/mysql.sock \ -DSYSCONFDIR=/usr/local/mysql57 \ -DMYSQL_TCP_PORT=3306 \ -DMYSQL_USER=mysql \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DDOWNLOAD_BOOST=0 -DWITH_BOOST=./boost $ sudo make $ sudo make install
환경설정
$ sudo vi /usr/local/mysql57/my.cnf
[mysqld] # port=3306 # basedir=/usr/local/mysql57 # datadir=/usr/local/mysql57/data # pid-file=/usr/local/mysql57/mysqld.pid # log_error=/usr/local/mysql57/mysql_error.log # lc-messages-dir=/usr/local/mysql57/share group_concat_max_len = 15360 event_scheduler = ON init_connect=SET collation_connection = utf8_general_ci init_connect=SET NAMES utf8 character-set-server=utf8 collation-server=utf8_general_ci # table_cache=1024 max_connections=2048 max_user_connections=500 max_connect_errors=10000 wait_timeout=300 query_cache_type = 1 query_cache_size = 128M query_cache_limit = 5M slow_query_log long_query_time=3 max_allowed_packet=16M sort_buffer_size = 2M # skip-innodb skip-name-resolve sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION [mysql] default-character-set=utf8 [client] default-character-set=utf8
MySQL 설치 디렉토리 mysql 계정으로 권한 수정
$ sudo chown -R mysql:mysql /usr/local/mysql57
MySQL 데이터베이스 초기화 (mysql 계정으로 실행)
$ cd /usr/local/mysql57 $ bin/mysql_install_db --no-defaults --user=mysql --datadir=/usr/local/mysql57/data --basedir=/usr/local/mysql57 -v 2018-10-03 15:26:47 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize 2018-10-03 15:26:47 [NOTE] Creating data directory /usr/local/mysql57/data 2018-10-03 15:26:47 [NOTE] Generating random password to /home/mysql/.mysql_secret...done. 2018-10-03 15:26:47 [NOTE] Executing /usr/local/mysql57/bin/mysqld --no-defaults --bootstrap --datadir=/usr/local/mysql57/data --lc-messages-dir=/usr/local/mysql57/share --lc-messages=en_US --basedir=/usr/local/mysql57 2018-10-03 15:26:48 [NOTE] Creating system tables...done. 2018-10-03 15:26:48 [NOTE] Filling system tables with data...done. 2018-10-03 15:26:49 [NOTE] Filling help table with data...done. 2018-10-03 15:26:49 [NOTE] Creating user for internal session service...done. 2018-10-03 15:26:49 [NOTE] Creating default user root@localhost 2018-10-03 15:26:49 [NOTE] Creating default proxy root@localhost 2018-10-03 15:26:49 [NOTE] Creating sys schema 2018-10-03 15:26:49 [NOTE] done. 2018-10-03 15:26:51 [WARNING] The bootstrap log isn't empty: 2018-10-03 15:26:51 [WARNING] 2018-10-03T06:26:47.586650Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead 2018-10-03 15:26:51 [NOTE] Generating SSL Certificates
MySQL 서비스 등록
서비스 mysqld 파일 복사
$ sudo cp /usr/local/mysql57/support-files/mysql.server /etc/init.d/mysqld57
mysqld 수정
$ sudo vi /etc/init.d/mysqld57
mysqld파일을 열어서 basedir에 mysql이 설치된 디렉토리와 데이터 디렉토리(datadir)를 설정한다.
basedir=/usr/local/mysql57 datadir=/usr/local/mysql57/data
mysqld 서비스 등록
$ sudo update-rc.d mysqld57 defaults
mysqld 서비스 실행
$ sudo systemctl start mysqld57.service
$ sudo systemctl status mysqld57.service
● mysqld57.service - LSB: start and stop MySQL
Loaded: loaded (/etc/init.d/mysqld57; generated)
Active: active (running) since Wed 2018-10-03 15:37:07 KST; 6min ago
Docs: man:systemd-sysv-generator(8)
Process: 1042 ExecStart=/etc/init.d/mysqld57 start (code=exited, status=0/SUCCESS)
Tasks: 28 (limit: 4915)
CGroup: /system.slice/mysqld57.service
├─1071 /bin/sh /usr/local/mysql57/bin/mysqld_safe --datadir=/usr/local/mysql57/data --pid-file=/usr/local/mysql57/data/bluesa
└─1469 /usr/local/mysql57/bin/mysqld --basedir=/usr/local/mysql57 --datadir=/usr/local/mysql57/data --plugin-dir=/usr/local/m
10월 03 15:37:06 bluesanta-ubuntu systemd[1]: Starting LSB: start and stop MySQL...
10월 03 15:37:06 bluesanta-ubuntu mysqld57[1042]: Starting MySQL
10월 03 15:37:07 bluesanta-ubuntu mysqld57[1042]: . *
10월 03 15:37:07 bluesanta-ubuntu systemd[1]: Started LSB: start and stop MySQL.
$ sudo systemctl stop mysqld57.service
root 암호 초기화
$ bin/mysqld_safe --skip-grant-tables & [1] 21756 mysql@bluesanta-ubuntu:/usr/local/mysql57$ 2018-10-03T06:32:29.844112Z mysqld_safe Logging to '/usr/local/mysql57/data/bluesanta-ubuntu.err'. 2018-10-03T06:32:29.873395Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql57/data
mysql@bluesanta-ubuntu:/usr/local/mysql57$ bin/mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.23-log Source distribution Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mysql 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> update user set authentication_string = password('manager') where user = 'root'; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye
ERROR 1820 (HY000) 오류 해결
mysql> select 1; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> SET PASSWORD = PASSWORD('sql'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql$gt; select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql>
사용자 추가
mysql> create user 'user1'@'%' identified by 'userpw'; mysql> grant all privileges on *.* to 'user1'@'%' with grant option; mysql> flush privileges;
사용자 추가
create user 'terecal'@'%' identified by '****'; GRANT ALL privileges ON terecal_db.* TO 'terecal'@'%'; flush privileges;
출처
리눅스 정보 조회
출처 : 리눅스 종류 확인, 리눅스 버전 확인 - 제타위키
$ cat /etc/*-release | uniq DISTRIB_ID=Ubuntu DISTRIB_RELEASE=18.04 DISTRIB_CODENAME=bionic DISTRIB_DESCRIPTION="Ubuntu 18.04.1 LTS" NAME="Ubuntu" VERSION="18.04.1 LTS (Bionic Beaver)" ID=ubuntu ID_LIKE=debian PRETTY_NAME="Ubuntu 18.04.1 LTS" VERSION_ID="18.04" HOME_URL="https://www.ubuntu.com/" SUPPORT_URL="https://help.ubuntu.com/" BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/" PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy" VERSION_CODENAME=bionic UBUNTU_CODENAME=bionic
리눅스 비트 확인
$ getconf LONG_BIT 64
관련패키지 설치
$ sudo apt-get update $ sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev
cmake 설치
$ sudo apt-get install cmake
MySQL 계정 만들기
$ sudo groupadd mysql $ sudo useradd -g mysql -s /bin/bash -m mysql
MySQL 소스 다운로드
$ wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.12.tar.gz
MySQL 소스 압축풀기
$ tar xvf mysql-8.0.12.tar.gz
MySQL 컴파일 및 설치
$ cd mysql-8.0.12/ $ sudo cmake \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ -DSYSCONFDIR=/usr/local/mysql \ -DMYSQL_TCP_PORT=3306 \ -DMYSQL_USER=mysql \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/mysql/boost $ sudo make $ sudo make install
MySQL 설치 디렉토리 mysql 계정으로 권한 수정
$ sudo chown -R mysql:mysql /usr/local/mysql
환경설정
$ vi /usr/local/mysql/my.cnf
[mysqld] bind-address=0.0.0.0 port=3306 basedir=/usr/local/mysql datadir=/usr/local/mysql/data # pid-file=/usr/local/mysql/mysqld.pid # log_error=/usr/local/mysql/mysql_error.log # lc-messages-dir=/usr/local/mysql/share init_connect=SET collation_connection = utf8_general_ci init_connect=SET NAMES utf8 character-set-server=utf8 collation-server=utf8_general_ci # table_cache=1024 max_connections=2048 max_user_connections=500 max_connect_errors=10000 wait_timeout=300 # query_cache_type = 1 # query_cache_size = 128M # query_cache_limit = 5M slow_query_log long_query_time=3 max_allowed_packet=16M sort_buffer_size = 2M # skip-innodb skip-name-resolve # sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION [mysql] default-character-set=utf8 [client] default-character-set=utf8
MySQL 데이터베이스 초기화 (mysql 계정으로 실행)
$ cd /usr/local/mysql $ bin/mysqld --defaults-file=/usr/local/mysql/my.cnf --initialize --console --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql 2018-10-03T03:17:05.650419Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.12) initializing of server in progress as process 5644 2018-10-03T03:17:08.797874Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: q<chLu;Cy2yY 2018-10-03T03:17:10.426427Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.12) initializing of server has completed
MySQL 종료 (프로세스가 존재하는 경우)
$ bin/mysqld stop 2018-10-03T01:59:45.345444Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.12) starting as process 3662 2018-10-03T01:59:45.756978Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2018-10-03T01:59:45.761934Z 0 [ERROR] [MY-010147] [Server] Too many arguments (first extra is 'stop'). 2018-10-03T01:59:45.761967Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue. 2018-10-03T01:59:45.762056Z 0 [ERROR] [MY-010119] [Server] Aborting 2018-10-03T01:59:47.444146Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.12) Source distribution.
root 암호 초기화
$ bin/mysqld_safe --skip-grant-tables & [1] 3745 mysql@bluesanta-ubuntu:/usr/local/mysql$ 2018-10-03T03:20:12.286866Z mysqld_safe Logging to '/usr/local/mysql/data/bluesanta-ubuntu.err'. 2018-10-03T03:20:12.311924Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
mysql@bluesanta-ubuntu:/usr/local/mysql$ bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.12 Source distribution Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.03 sec) mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'sql'; Query OK, 0 rows affected (0.08 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> quit Bye
MySQL 서비스 등록
서비스 mysqld 파일 복사
$ sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
mysqld 수정
$ sudo vi /etc/init.d/mysqld
mysqld파일을 열어서 basedir에 mysql이 설치된 디렉토리와 데이터 디렉토리(datadir)를 설정한다.
basedir=/usr/local/mysql datadir=/usr/local/mysql/data
mysqld 서비스 등록
$ sudo update-rc.d mysqld defaults Adding system startup for /etc/init.d/mysqld ... /etc/rc0.d/K20mysqld -> ../init.d/mysqld /etc/rc1.d/K20mysqld -> ../init.d/mysqld /etc/rc6.d/K20mysqld -> ../init.d/mysqld /etc/rc2.d/S20mysqld -> ../init.d/mysqld /etc/rc3.d/S20mysqld -> ../init.d/mysqld /etc/rc4.d/S20mysqld -> ../init.d/mysqld /etc/rc5.d/S20mysqld -> ../init.d/mysqld
mysqld 서비스 실행
$ sudo systemctl start mysqld.service
$ sudo systemctl status mysqld.service
● mysqld.service - LSB: start and stop MySQL
Loaded: loaded (/etc/init.d/mysqld; generated)
Active: active (running) since Wed 2018-10-03 11:58:26 KST; 32s ago
Docs: man:systemd-sysv-generator(8)
Process: 4863 ExecStop=/etc/init.d/mysqld stop (code=exited, status=0/SUCCESS)
Process: 4887 ExecStart=/etc/init.d/mysqld start (code=exited, status=0/SUCCESS)
Tasks: 38 (limit: 4915)
CGroup: /system.slice/mysqld.service
├─4895 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/bluesanta-ubuntu.pid
└─4982 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysq
10월 03 11:58:25 bluesanta-ubuntu systemd[1]: Starting LSB: start and stop MySQL...
10월 03 11:58:25 bluesanta-ubuntu mysqld[4887]: Starting MySQL
10월 03 11:58:26 bluesanta-ubuntu mysqld[4887]: . *
10월 03 11:58:26 bluesanta-ubuntu systemd[1]: Started LSB: start and stop MySQL.
$ sudo systemctl stop mysqld.service
ERROR 1820 (HY000) 오류 해결
mysql> select 1; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> SET PASSWORD = PASSWORD('sql'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql$gt; select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql>
사용자 추가
mysql> create user 'user1'@'%' identified by 'userpw'; mysql> grant all privileges on *.* to 'user1'@'%' with grant option; mysql> flush privileges;
사용자 추가
create user 'terecal'@'%' identified by '****'; GRANT ALL privileges ON terecal_db.* TO 'terecal'@'%'; flush privileges;
Ubuntu 리눅스 서버에 MySQL 5.7.x 설치
출처 : Ubuntu server에 mysql 5.5.x 설치하기 -pupustory@- - Tistory
[Linux/CentOS] Mysql을 컴파일해서 설치해보자 - AT BLOG
리눅스 정보 조회
출처 : 리눅스 종류 확인, 리눅스 버전 확인 - 제타위키
$ cat /etc/*-release | uniq DISTRIB_ID=Ubuntu DISTRIB_RELEASE=14.04 DISTRIB_CODENAME=trusty DISTRIB_DESCRIPTION="Ubuntu 14.04.3 LTS" NAME="Ubuntu" VERSION="14.04.3 LTS, Trusty Tahr" ID=ubuntu ID_LIKE=debian PRETTY_NAME="Ubuntu 14.04.3 LTS" VERSION_ID="14.04" HOME_URL="http://www.ubuntu.com/" SUPPORT_URL="http://help.ubuntu.com/" BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"
리눅스 비트 확인
$ getconf LONG_BIT 64
관련패키지 설치
$ sudo apt-get update $ sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev
MySQL 계정 만들기
$ sudo groupadd mysql $ sudo useradd -g mysql -s /bin/bash -m mysql
cmake 설치
$ sudo apt-get install cmake
cmake 수동 설치(option)
$ wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz $ tar xvf cmake-2.8.4.tar.gz $ cd cmake-2.8.4 $ ./bootstrap $ make all $ sudo make install
MySQL 소스 다운로드
$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20.tar.gz
MySQL 소스 압축풀기
$ tar xvf mysql-5.7.20.tar.gz
MySQL 컴파일 및 설치
$ cd mysql-5.7.20 $ sudo cmake \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ -DSYSCONFDIR=/etc \ -DMYSQL_TCP_PORT=3306 \ -DMYSQL_USER=mysql \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/mysql/boost $ sudo make $ sudo make install
MySQL 데이터베이스 초기화
$ cd /usr/local/mysql $ echo "./bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data -v" $ sudo ./bin/mysql_install_db --no-defaults --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql -v 2017-09-20 20:17:58 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize 2017-09-20 20:17:58 [NOTE] Using existing directory /usr/local/mysql/data 2017-09-20 20:17:58 [NOTE] Generating random password to /root/.mysql_secret...done. 2017-09-20 20:17:58 [NOTE] Setting file ownership to mysql 2017-09-20 20:17:58 [NOTE] Executing /usr/local/mysql/bin/mysqld --no-defaults --bootstrap --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US --basedir=/usr/local/mysql 2017-09-20 20:18:00 [NOTE] Creating system tables...done. 2017-09-20 20:18:00 [NOTE] Filling system tables with data...done. 2017-09-20 20:18:01 [NOTE] Filling help table with data...done. 2017-09-20 20:18:01 [NOTE] Creating user for internal session service...done. 2017-09-20 20:18:01 [NOTE] Creating default user root@localhost 2017-09-20 20:18:01 [NOTE] Creating default proxy root@localhost 2017-09-20 20:18:01 [NOTE] Creating sys schema 2017-09-20 20:18:02 [NOTE] done. 2017-09-20 20:18:03 [WARNING] The bootstrap log isn't empty: 2017-09-20 20:18:03 [WARNING] 2017-09-20T11:17:58.125569Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead 2017-09-20T11:17:58.126616Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2017-09-20T11:17:58.126627Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 2017-09-20 20:18:03 [NOTE] Generating SSL Certificates
MySQL 설치 디렉토리 mysql 계정으로 권한 수정
$ sudo chown -R mysql:mysql /usr/local/mysql
환경설정
$ vi /usr/local/mysql/my.cnf
[mysqld] # port=3306 # basedir=/usr/local/mysql # datadir=/usr/local/mysql/data # pid-file=/usr/local/mysql/mysqld.pid # log_error=/usr/local/mysql/mysql_error.log # lc-messages-dir=/usr/local/mysql/share init_connect=SET collation_connection = utf8_general_ci init_connect=SET NAMES utf8 character-set-server=utf8 collation-server=utf8_general_ci # table_cache=1024 max_connections=2048 max_user_connections=500 max_connect_errors=10000 wait_timeout=300 query_cache_type = 1 query_cache_size = 128M query_cache_limit = 5M slow_query_log long_query_time=3 max_allowed_packet=16M sort_buffer_size = 2M # skip-innodb skip-name-resolve sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION [mysql] default-character-set=utf8 [client] default-character-set=utf8
MySQL 서비스 등록
서비스 mysqld 파일 복사
$ sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
mysqld 수정
$ sudo vi /etc/init.d/mysqld
mysqld파일을 열어서 basedir에 mysql이 설치된 디렉토리와 데이터 디렉토리(datadir)를 설정한다.
basedir=/usr/local/mysql datadir=/usr/local/mysql/data
mysqld 서비스 등록
$ sudo update-rc.d mysqld defaults Adding system startup for /etc/init.d/mysqld ... /etc/rc0.d/K20mysqld -> ../init.d/mysqld /etc/rc1.d/K20mysqld -> ../init.d/mysqld /etc/rc6.d/K20mysqld -> ../init.d/mysqld /etc/rc2.d/S20mysqld -> ../init.d/mysqld /etc/rc3.d/S20mysqld -> ../init.d/mysqld /etc/rc4.d/S20mysqld -> ../init.d/mysqld /etc/rc5.d/S20mysqld -> ../init.d/mysqld
mysqld 서비스 실행
$ sudo service mysqld start Starting MySQL . * $ sudo service mysqld status * MySQL running (29565) $ sudo service mysqld stop Shutting down MySQL . *
root 암호 초기화
$ sudo ./bin/mysqld_safe --skip-grant-tables & [1] 31008 mysql@localhost:/usr/local/mysql$ 2017-04-18T09:21:34.035860Z mysqld_safe Logging to '/usr/local/mysql/data/localhost.err'. 2017-04-18T09:21:34.050764Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
mysql@localhost:/usr/local/mysql$ ./bin/mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.18-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mysql 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> update user set authentication_string = password('sql') where user = 'root'; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye
ERROR 1820 (HY000) 오류 해결
mysql> select 1; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> SET PASSWORD = PASSWORD('sql'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql$gt; select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql>
사용자 추가
mysql> create user 'user1'@'%' identified by 'userpw'; mysql> grant all privileges on *.* to 'user1'@'%' with grant option; mysql> flush privileges;
사용자 추가
create user 'terecal'@'%' identified by '****'; GRANT ALL privileges ON terecal_db.* TO 'terecal'@'%'; flush privileges;