042. Raspberry Pi 라즈베리파이 - mysql(10.1.38-MariaDB-0+deb9u1) 재설치 (Raspberry pi reinstall mysql)

postgresql을 재설치를 했었는데…

mysql도 어쩌다보니 재설치를 하게됬습니다

다행히 작업한건 없어서 휴...

 

• mariadb(mysql) 삭제

sudo apt-get purge mysql* mariadb* -y

sudo apt-get autoremove -y

sudo apt-get autoclean -y

sudo rm -rf /usr/sbin/mysqld

sudo rm -rf /var/run/mysqld

sudo rm -rf /etc/mysql

pi@rasp-dev:~ $ sudo apt-get purge mysql* mariadb* -y && sudo apt-get autoremove -y && sudo apt-get autoclean -y 패키지 목록을 읽는 중입니다... 완료 의존성 트리를 만드는 중입니다        상태 정보를 읽는 중입니다... 완료 Note, selecting 'mysqltcl' for glob 'mysql*'   . . .   Processing triggers for libc-bin (2.24-11+deb9u4) … Processing triggers for man-db (2.7.6.1-2) ... pi@rasp-dev:~ $ sudo dpkg -l | grep mysql pi@rasp-dev:~ $ sudo dpkg -l | grep mariadb pi@rasp-dev:~ $ sudo rm -rf /usr/sbin/mysqld /var/run/mysqld /etc/mysql pi@rasp-dev:~ $ mysql -bash: /usr/bin/mysql: 그런 파일이나 디렉터리가 없습니다 pi@rasp-dev:~ $ sudo service mysql mysql: unrecognized service pi@rasp-dev:~ $

삭제를 진행했으니

다시 설치를 해줍니다

 

• mysql (mariadb) 설치

sudo apt-get update -y && sudo apt-get upgrade -y

sudo apt-get install mysql-client mysql-server -y

pi@rasp-dev:~ $ sudo apt-get update -y && sudo apt-get upgrade  기존:1 http://archive.raspberrypi.org/debian stretch InRelease                  받기:2 http://raspbian.raspberrypi.org/raspbian stretch InRelease [15.0 kB]       . . .   ssh (1:7.4p1-10+deb9u7) 설정하는 중입니다 … Processing triggers for initramfs-tools (0.130) ... Processing triggers for libc-bin (2.24-11+deb9u4) ...   pi@rasp-dev:~ $ sudo apt-get install mysql-client mysql-server -y 패키지 목록을 읽는 중입니다... 완료 의존성 트리를 만드는 중입니다        상태 정보를 읽는 중입니다... 완료   . . .   mysql-client (5.5.9999+default) 설정하는 중입니다 … Processing triggers for systemd (232-25+deb9u12) ... pi@rasp-dev:~ $

설치가 끝났으니 외부접속을 하기위해 root 패스워드 변경을 진행합니다

 

• password change

update user set password = password('변경할 패스워드') where user = 'root';

pi@rasp-dev:~ $ sudo mysql Welcome to the MariaDB monitor.  Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.1.38-MariaDB-0+deb9u1 Raspbian 9.0   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]> select version(); +--------------------------+ | version()                | +--------------------------+ | 10.1.38-MariaDB-0+deb9u1 | +--------------------------+ 1 row in set (0.00 sec)   MariaDB [mysql]> select host, user, password from user; +-----------+------+----------+ | host      | user | password | +-----------+------+----------+ | localhost | root |          | +-----------+------+----------+ 1 row in set (0.00 sec)   MariaDB [mysql]> update user set password = password('root패스워드') where user ='passwd'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0   MariaDB [mysql]> select host, user, password from user; +-----------+------+-------------------------------------------+ | host      | user | password                                  | +-----------+------+-------------------------------------------+ | localhost | root | *81F5E21E3540DDDD4A6CD4A731AEBFB6AF209E1B | +-----------+------+-------------------------------------------+ 1 row in set (0.00 sec)   MariaDB [mysql]>

패스워드를 변경했으니 다음단계로 넘어갑니다

 

• 외부접근 허용

• 접근 가능한 host 추가

grant all privileges on *.* to 'root'@'%' identified by 'root 패스워드';

MariaDB [mysql]> grant all privileges on *.* to 'root'@'%' identified by 'passwd';  Query OK, 0 rows affected (0.00 sec)   MariaDB [mysql]> select host, user, password from user; +-----------+------+-------------------------------------------+ | host      | user | password                                  | +-----------+------+-------------------------------------------+ | localhost | root | *81F5E21E3540DDDD4A6CD4A731AEBFB6AF209E1B | | %         | root | *81F5E21E3540DDDD4A6CD4A731AEBFB6AF209E1B | +-----------+------+-------------------------------------------+ 2 rows in set (0.00 sec)   MariaDB [mysql]> flush privileges; Query OK, 0 rows affected (0.00 sec)   MariaDB [mysql]> exit

grant 를 사용해서 권한을 추가했으니

설정파일을 수정해야 합니다

참고로 특정 IP만 접근허용하려면 %대신 IP를 넣으면 됩니다

 

• 50-server.cnf 수정

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf 

pi@rasp-dev:~ $ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf     13   14 #  15 # * Basic Settings  16 #  17 user            = mysql  18 pid-file        = /var/run/mysqld/mysqld.pid  19 socket          = /var/run/mysqld/mysqld.sock  20 port            = 3306  21 basedir         = /usr  22 datadir         = /var/lib/mysql  23 tmpdir          = /tmp  24 lc-messages-dir = /usr/share/mysql  25 skip-external-locking  26   27 # Instead of skip-networking the default is now to listen only on  28 # localhost which is more compatible and is not less secure.  29 #bind-address            = 127.0.0.1  30   31 #  32 # * Fine Tuning  33 #  34 key_buffer_size         = 16M  35 max_allowed_packet      = 16M                                                               28,1          10%

29번 라인에 bind-address를 주석처리해줍니다

특정 IP에서만 접근하게하려면 해당 IP를 넣으면 된다는거죠

다람쥐 SQL

다람쥐 SQL을 통해 접속을 해보니 잘되네요

pi@rasp-dev:~ $ mysql -u root -p Enter password:  ERROR 1698 (28000): Access denied for user 'root'@'localhost' pi@rasp-dev:~ $

근데 명령어로 접속하니 실패…

뭐지

 

찾아보니 plugin이 문제가 된다고 하니 확인해 봤습니다

 

• plugin 확인

select host, user, plugin from user;

pi@rasp-dev:~ $ sudo mysql Welcome to the MariaDB monitor.  Commands end with ; or \g. Your MariaDB connection id is 32 Server version: 10.1.38-MariaDB-0+deb9u1 Raspbian 9.0   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]> select host, user, plugin from user; +-----------+------+-------------+ | host      | user | plugin      | +-----------+------+-------------+ | localhost | root | unix_socket | | %         | root |             | +-----------+------+-------------+ 2 rows in set (0.01 sec)   MariaDB [mysql]> update user set plugin='mysql_native_password' where user = 'root'; Query OK, 2 rows affected (0.01 sec) Rows matched: 2  Changed: 2  Warnings: 0   MariaDB [mysql]> flush privileges; Query OK, 0 rows affected (0.00 sec)   MariaDB [mysql]>

sudo 를 이용해서 로그인하면 되긴하지만

맘에 안드니 플러그인을 unix_socket 을 mysql_native_password 로 변경해줄거에요

 

pi@rasp-dev:~ $ mysql -u root -p Enter password:  Welcome to the MariaDB monitor.  Commands end with ; or \g. Your MariaDB connection id is 7 Server version: 10.1.38-MariaDB-0+deb9u1 Raspbian 9.0   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)]>

plugin unix_socket 을 mysql_native_password 로 변경해주니 정상적으로 로그인이 되네요

 

---

관련글

[embedded/raspberrypi] - 012. Raspberry Pi 라즈베리 파이 - Apache2, PHP, Mysql APM설치

[programing/JAVA&JSP&SERVLET&SPRING] - JAVA 자바 Mysql Load Data Infile

[OS/Utils] - 강추)다람쥐 SQL SQuirreL SQL 만능 DB 접속툴

[embedded/raspberrypi] - Raspberry Pi Jessie 라즈베리파이 - WordPress 4.7 설치

[embedded/raspberrypi] - 030. Raspberry Pi 라즈베리 파이 - Mysql 외부접속 허용

[OS/MAC OS X] - [macOS High Sierra] Mysql 8.x brew install 설치

[OS/MAC OS X] - [macOS High Sierra] Mysql 8.x dmg install 설치

[OS/MAC OS X] - [macOS High Sierra] Mysql 8.x password change 변경

[embedded/raspberrypi] - Raspberry Pi Jessie 라즈베리파이 - install MediaWiKi 미디어위키 설치

[embedded/raspberrypi] - 042. Raspberry Pi 라즈베리파이 - mysql(10.1.38-MariaDB-0+deb9u1) 재설치 (Raspberry pi reinstall mysql)

[embedded/raspberrypi] - 043. Raspberry Pi 라즈베리파이 - mariadb(mysql) 데이터 파일 위치 변경 (move to mariadb data directory)

---

 

참고

https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost