[DB]Mysql – AuctionPro

mysql ENGINE=InnoDB;

2025년 03월 09일 golgol 0

ENGINE=InnoDB; 란 MySQL에서 테이블을 생성할 때 해당 테이블에 InnoDB 저장 엔진을 사용하도록 지정하는 옵션입니다. InnoDB는 다음과 같은 장점을 제공합니다: 따라서, ENGINE=InnoDB; 옵션을 사용하면 위와 같은 […]

Tables and views keep on fetching in MYSQL

2023년 10월 13일 golgol 0

Overview : fetching 오류 Solution : mysql restart

mysql datetime default

2023년 07월 25일 golgol 0

Overview : 현제 시간으로 now(), getdate() 처리 DATETIME ENUM(‘Y’,’N’,’X’) MySQL의 ENUM 타입은 이론적으로 최대 65,535개의 값을 지정할 수 있습니다. 다만 몇 가지 주의할 점이 있습니다: […]

ubuntu mysql pool_size

2023년 06월 09일 golgol 0

Overview : InnoDB Status Buffer 사이즈 늘리기 1.MySQL Workbench -Dashboard – mysql 5.0 이상 지원 2. mysql config 수정 3. 아래에 램용량을 확인하고 적당한 buffer […]

MySQL DataLog 최소화하기

2022년 10월 18일 golgol 0

Remark : 가상화 서버 사용시 MySQL Binary Log 가 차지하는 용량이 커서 디비오류가 나는 경우가 있다. Mysql 오류인지 착각 하여 프로그램을 새로 설치하는 경우가 있는데, […]

mysql delete 경고

2022년 06월 11일 golgol 0

원인 테이블에서 키값을 이용한 update나 delete만을 허용하도록 되어 있는데, 그렇지 않게 좀더 넓은 범위의 sql을 적용하려고 할때 workbench에서 경고를 주는것임.즉 하나의 레코드만을 update, delete하도록 설정되어 […]

Mysql crashed 복구

2022년 04월 18일 golgol 0

09:13:09 SELECT * FROM auctionpro.wp_options LIMIT 0, 5000 Error Code: 144. Table ‘./auctionpro/wp_options’ is marked as crashed and last (automatic?) repair failed 0.000 sec 해결 […]

[MySQL 8.0] The table ‘/tmp/#sql278b-2a008_2’ is full

2022년 02월 23일 golgol 0

Remark : 하드 용량이 없어 발생 / 해결 : 필요없는 파일 및 디렉토리 삭제

ubuntu 18.04 mysql 8.023 설치

2020년 06월 08일 golgol 0

sudo apt update sudo apt install mysql-server sudo mysql_secure_installation


sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

1

2

3

4

5

sudo apt update

sudo apt install mysql-server

sudo mysql_secure_installation


root@localhost:/etc/apache2# sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length &gt;= 8
MEDIUM Length &gt;= 8, numeric, mixed case, and special characters
STRONG Length &gt;= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
All done!

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

root@localhost:/etc/apache2# sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords

and improve security. It checks the strength of password

and allows the users to set only those passwords which are

secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW Length >= 8

MEDIUM Length >= 8, numeric, mixed case, and special characters

STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 50

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

By default, a MySQL installation has an anonymous user,

allowing anyone to log into MySQL without having to have

a user account created for them. This is intended only for

testing, and to make the installation go a bit smoother.

You should remove them before moving into a production

environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Success.

Normally, root should only be allowed to connect from

'localhost'. This ensures that someone cannot guess at

the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

... skipping.

By default, MySQL comes with a database named 'test' that

anyone can access. This is also intended only for testing,

and should be removed before moving into a production

environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

- Dropping test database...

Success.

- Removing privileges on test database...

Success.

Reloading the privilege tables will ensure that all changes

made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : n

... skipping.

All done!

Check authentication method for MySQL users host 가 % 일경우 #주석처리 #bind-address=127.0.0.0 […]

[mysql] Data Import ERROR 1067 (42000) at line 431

2019년 05월 08일 golgol 0


17:40:33 Restoring C:\Users\Administrator\Downloads\backup_2019-04-24-1154_C_test__8e4fc2ead977-db\Csharpdb
Running: mysql.exe --defaults-file="c:\users\admini~1\appdata\local\temp\tmpdtqlgo.cnf"  --protocol=tcp --host= user=dsfd --port= --default-character-set=utf8 --comments --database=ctset  < "C:\\Users\\Administrator\\Downloads\\backup_2019-04-24-1154_C_test__8e4fc2ead977-db\\testpdb"
ERROR 1067 (42000) at line 431: Invalid default value for 'user_registered'

Operation failed with exitcode 1
17:40:35 Import of C:\Users\Administrator\Downloads\backup_2019-04-24-1154_C_test__8e4fc2ead977-db\Csharpdb has finished with 1 errors

1

2

3

4

5

6

7

8

17:40:33 Restoring C:\Users\Administrator\Downloads\backup_2019-04-24-1154_C_test__8e4fc2ead977-db\Csharpdb

Running: mysql.exe --defaults-file="c:\users\admini~1\appdata\local\temp\tmpdtqlgo.cnf" --protocol=tcp --host= user=dsfd --port= --default-character-set=utf8 --comments --database=ctset < "C:\\Users\\Administrator\\Downloads\\backup_2019-04-24-1154_C_test__8e4fc2ead977-db\\testpdb"

ERROR 1067 (42000) at line 431: Invalid default value for 'user_registered'

Operation failed with exitcode 1

17:40:35 Import of C:\Users\Administrator\Downloads\backup_2019-04-24-1154_C_test__8e4fc2ead977-db\Csharpdb has finished with 1 errors

solution:


use dbtest;
SET GLOBAL sql_mode = '';

1

2

3

4

use dbtest;

SET GLOBAL sql_mode = '';

mysql Error -> mysql_upgrade

2018년 07월 25일 golgol 0

Error Saving Account Error creating account vpopmail@%: Column countof mysql.user is wrong, Expected 42… Please use myslq_upgrade to fix this error. mysql_upgrade -u root -p […]

mysql update error SQL_SAFE_UPDATES=0

2013년 08월 11일 golgol 0

MySQL 서버에 로그인한 후에는 다음 명령을 사용하여 safe update 모드를 활성화할 수 있습니다: SET SQL_SAFE_UPDATES = 1;이 명령을 실행하면 safe update 모드가 활성화됩니다. 이제 UPDATE나 […]

mysql dump 백업

2013년 05월 20일 golgol 0

필요에 따른 mysql dump 방법 1. database 전체 데이터베이스 dump


# mysqldump -u root -p –all-databases > db.sql

1

2

3

# mysqldump -u root -p –all-databases > db.sql

2. 특정 테이블만 dump


# mysqldump -u root -p db명 talbe1 table2 > table.sql

1

2

3

# mysqldump -u root -p db명 talbe1 table2 > table.sql

3. 스키마(테이블구조만) dump


# mysqldump –no-data db명 > db.sql

1

2

3

# mysqldump –no-data db명 > db.sql

4. 데이터만(테이블구조 제외) dump


# mysqldump -u root -p –no-create db명 > db.sql

1

2

3

# mysqldump -u root -p –no-create db명 > db.sql

[…]

[MYSQL] 계정생성 및 권한부여

2012년 12월 20일 golgol 0

MySQL 사용하기 MySQL에서 root 암호 설정 mysqladmin을 이용하여 root 암호 설정 MySQL을 처음 설치하면 root 암호가 설정되어 있지 않기 때문에 반드시 설치 후 바로 root […]