반응형

OS: Ubuntu 14.04 (amd64)

에러 발생한 버전: 5.5 (Ubuntu 14.04에서 apt-get으로 기본 설치되는 버전)



아마존웹서비스(AWS) 서버에 있는 MySQL 데이터베이스를 dump를 떠서 Ubuntu 14.04를 쓰는 개인 피씨에 옮기려고 했는데, 백업이 복구가 되지 않고 아래와 같은 에러가 발생하였다:


$ mysql -uroot -p test < test_170728.sql

Enter password: 

ERROR 1293 (HY000) at line 25: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause



에러 메세지가 알려주는 것 그대로, 테이블 하나에 TIMESTAMP 타입의 column은 한개만 default로 CURRENT_TIMESTAMP를 지정할 수 있다는 내용이다.


참고로 이 제한사항은 5.6부터 해제되었기 때문에, 지금 쓰는 mysql-server-5.5를 mysql-server-5.6으로 업그레이드하면 문제가 해결된다.


$ sudo apt-get update

$ sudo apt-get install mysql-server-5.6


이미 5.5에서 관리하고 있던 데이터베이스가 있는 경우, 위와 같이 5.6을 설치하면 자동으로 데이터베이스가 이전되므로 별도로 dump를 떠서 옮길 필요는 없다. 다만 만약의 경우를 대비해서 5.6을 설치하기 전에 미리 백업을 해 두면 안전하긴 하다.





반응형
블로그 이미지

Bryan_

,
반응형

Tested OS: Ubuntu 14.04 또는 Ubuntu 16.04

MySQL version: 5.5~5.7



설정 파일(my.cnf)과 mysql 콘솔에서의 설정 두 가지를 적용하면 된다.


/etc/mysql/my.cnf 또는 /etc/mysql/mysql.conf.d/mysqld.cnf 파일에서

bind-address에 원래 127.0.0.1로 되어 있는 것을 0.0.0.0으로 바꾼다.


bind-address = 0.0.0.0



참고로, root 계정을 외부에서 접속할 수 있게 만드는 것은 그다지 좋은 방법이 아니다. 따라서 외부 접속용 계정을 따로 만들고, 그 계정이 특정 데이터베이스에만 접근할 수 있도록 설정해 주는 것이 좋다.


*DB 생성


mysql> create database testdb;


*사용자 생성


mysql> create user 'testuser'@'%' identified by 'testpassword';



*생성한 사용자의 접근 권한 설정


 - 로컬 접속:


mysql> grant all privileges on testdb.* to 'testuser'@localhost identified by 'testpassword';


 - 외부 접속:


mysql> grant all privileges on testdb.* to 'testuser'@'%' identified by 'testpassword';



MySQL 서비스 재시작:


$ sudo service mysql restart


반응형
블로그 이미지

Bryan_

,
반응형

OS: Ubuntu 14.04 또는 16.04

MySQL: 5.5 또는 5.7


우분투(Ubuntu)에서 MySQL Server 설치를 하고 나면 기본 설정으로 

127.0.0.1 주소에 포트 번호 3306으로 접속하게 되어 있다.

접속하는 포트 번호를 다른 숫자로 변경하려면 설정 파일을 수정하면 된다.


수정할 파일은 /etc/mysql 에서 하위 디렉토리 구조에 따라서 다르다.

하위 폴더에 mysql.conf.d 디렉토리가 있는 경우에는 /etc/mysql/mysql.conf.d/mysqld.cnf 파일을 수정하고,

그런 폴더가 없으면 /etc/mysql/my.cnf 파일을 수정한다.


아니면 /etc/mysql/my.cnf 파일에 !includedir /etc/mysql/mysql.conf.d/ 이런 내용이 적혀 있는데 그 디렉토리에 설정파일이 있다.



내용 중에서, [mysqld] 로 시작하는 부분에서 port = 3306 으로 되어 있는 부분을 원하는 번호로 고친다.


[mysqld]

#

# * Basic Settings

#

user        = mysql

pid-file    = /var/run/mysqld/mysqld.pid

socket      = /var/run/mysqld/mysqld.sock

port        = 43306

basedir     = /usr

datadir     = /var/lib/mysql

tmpdir      = /tmp

lc-messages-dir = /usr/share/mysql

skip-external-locking 



설정 파일을 저장하고, 서비스를 재시작하면 적용된다.


$ sudo service mysql restart




반응형
블로그 이미지

Bryan_

,
반응형

파일(sql구문)로 DB 백업:
$ mysqldump -u[사용자] -p[암호] [백업할 데이터베이스 이름] > db091130.sql
참고로 -d 옵션을 주면 데이터 없이 스키마만 백업한다.

생성된 DB가 utf8을 사용하고 있고 데이터에 한글이 포함되었을 때는 가급적
구문을 추가해줄 것.


백업파일로부터 DB 복구:
$ mysql -u[사용자] -p[암호] [복구될 데이터베이스 이름] < db091130.sql

복구할 때 복구될 데이터베이스는 "create database [데이터베이스이름]"에 의해 미리 생성되어 있어야 한다.
반응형
블로그 이미지

Bryan_

,
반응형
만들어진 테이블들:
mysql> create table DEPARTMENT (
    -> DEPT_CD varchar(10) not null,
    -> NAME varchar(30) not null,
    -> PHONE int(11) not null,
    -> primary key(DEPT_CD)
    -> );

desc RESERVATION;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| RESERVATION_ID | varchar(10) | NO   | PRI | NULL    |       |
| PATIENT        | int(13)     | NO   | MUL | NULL    |       |
| DEPARTMENT     | varchar(10) | NO   | MUL | NULL    |       |
| DOCTOR         | varchar(5)  | NO   | MUL | NULL    |       |
| DATE           | varchar(10) | NO   |     | NULL    |       |
| TIME           | varchar(10) | NO   |     | NULL    |       |
| TYPE           | varchar(20) | NO   |     | NULL    |       |
| GRANTED        | varchar(10) | NO   |     | NO      |       |
+----------------+-------------+------+-----+---------+-------+



발생한 에러:
mysql> alter table RESERVATION drop column DEPARTMENT;
ERROR 1025 (HY000): Error on rename of '.\db_project\#sql-d0_2' to '.\db_project\reservation' (errno: 150)



또다른 시도:
mysql> alter table RESERVATION drop foreign key DEPARTMENT;
ERROR 1025 (HY000): Error on rename of '.\db_project\#sql-d0_2' to '.\db_project\reservation' (errno: 152)


이 두 경우에 대한 문제의 원인분석:
(1) 일단 foreign key constraint부터 지운 후에 drop column을 하는 것이 정상적인 순서이다.
(2) 그런데 foreign key constraint를 내가 테이블 생성할 때에 지정해 주지 않았다.
(3) 따라서 내부적으로 MySQL이 직접 정해준 foreign key constraint의 이름을 알아내야 한다.


마지막으로 foreign key와 관려하여 에러난 상황을 자세히 보고 싶을 때:
mysql> SHOW INNODB STATUS;


그러면 중간쯤에서 아래와 같은 자세한 설명을 볼 수 있고, 거기서 contraint name을 찾을 수 있다.
------------------------
LATEST FOREIGN KEY ERROR
------------------------
091130 21:10:55 Error in foreign key constraint of table db_project/reservation:

there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT "reservation_ibfk_2" FOREIGN KEY ("DEPARTMENT") REFERENCES "departm
ent" ("DEPT_CD") ON DELETE CASCADE


이제 foreign key constraint와 column name을 차례대로 지워주면 된다.
mysql> alter table reservation drop foreign key reservation_ibfk_2;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table reservation drop column DEPARTMENT;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

반응형
블로그 이미지

Bryan_

,