[DB]Mysql – AuctionPro

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() 처리

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 […]