[Mac,MySQL] mariaDB 설치, 가동 및 삭제, 계정생성, 권한주기

Mariadb 설치

brew install mariadb

 

MySQL is configured to only allow connections from localhost by default
To have launchd start mariadb now and restart at login:
  brew services start mariadb
Or, if you don't want/need a background service you can just run:
  mysql.server start
leejongwon@ijong-won-ui-MacBookPro ~ % 

 

Mariadb 실행한다.

brew services start mariadb

 

최초 root 비번이 없이 mariadb 접속하기

Mysql -uroot

 

그런데, 만약

Mysql -uroot -p 하는데, 

Enter password: 

ERROR 1698 (28000): Access denied for user 'root'@'localhost' 

 

위와 같이 에러가 날때에는

sudo mysql -uroot   하고, 맥북 접속 비번을 넣으면 된다.

 

Password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.5.9-MariaDB Homebrew
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)]>

 

위와 같이 기본 접속가능하다.

 

root 비번변경해주도록 하자.

sudo mariadb-secure-installation

 

 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
 
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
 
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
 
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
 
You already have your root account protected, so you can safely answer 'n'.
 
Switch to unix_socket authentication [Y/n] Y
Enabled successfully!
Reloading privilege tables..
 ... Success!
 
 
You already have your root account protected, so you can safely answer 'n'.
 
Change the root password? [Y/n] Y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
 
 
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] 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? [Y/n] N
 ... skipping.
 
By default, MariaDB 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? [Y/n] 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? [Y/n] Y
 ... Success!
 
Cleaning up...
 
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
 
Thanks for using MariaDB!
leejongwon@ijong-won-ui-MacBookPro ~ % mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.5.9-MariaDB Homebrew
 
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)]>

 

Homebrew 로 설치되어 있는 프로그램 확인

brew services list

 

Name    Status  User    File

mariadb started jongwon ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist

 

 

 

Mariadb 실행

mysql.server start

 

실행 확인한다.

mysql.server status

 

 

Mariadb 삭제하기

 

1. 설치확인

brew services list  해서 mariadb 확인한다.

 

2. Mariadb 가동 중단

brew services stop mariadb

 

3. Mariadb 가동

brew services start mariadb

 

4. 삭제하기

brew uninstall mariadb

 

4. Mysql 관련 파일 깨끗이 삭제하기

rm -rf /usr/local/var/mysql

 

만약 화면창에서

ERROR! MariaDB is not running, but PID file exists

와 같은 에러가 난다면,  homebrew 로 mariadb 를 설치했기 때문인데, 이와 같은경우

상기 brew services start mariadb 등의 명령어를 이용하면 정상 동작한다.

 

https://datacode.tistory.com/36

[Mac OS] :: ERROR! MariaDB is not running, but PID file exists

 

 

 

spring 이라는 일반계정 생성

localhost 와 % (외부ip 접속)으로 각각 추가

 

mysql> create user spring@localhost identified by '25802580’;
mysql> create user spring@'%' identified by '25802580’;
mysql> show databases;
mysql> use mysql;
mysql> select user, host from user;

MariaDB [mysql]> select user,host from user;
+-------------+-------------------------------+
| User        | Host                          |
+-------------+-------------------------------+
| spring      | %                             |
|             | ijong-won-ui-macbookpro.local |
|             | localhost                     |
| leejongwon  | localhost                     |
| mariadb.sys | localhost                     |
| root        | localhost                     |
| spring      | localhost                     |
+-------------+-------------------------------+
7 rows in set (0.001 sec)


springdb 데이터베이스 생성

spring 계정으로 springdb 디비 권한부여

mysql> create database springdb;
mysql> grant all privileges on springdb.* to 'spring'@'%';
mysql> grant all privileges on springdb.* to 'spring’@localhost;
mysql> select user, host from user;
mysql> flush privileges;
mysql> show grants for 'spring'@'%';


MariaDB [mysql]> show grants for 'spring'@localhost;
+---------------------------------------------------------------------------------------------------------------+
| Grants for spring@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `spring`@`localhost` IDENTIFIED BY PASSWORD '*434FE9D06B6CE8180F187E670AD435DB38AE1726' |
| GRANT ALL PRIVILEGES ON `springdb`.* TO `spring`@`localhost`                                                  |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

 

[mysql 외부접근 설정]

Mariadb 의  my.cnf 파일 위치 확인

$ mysql --help | grep my.cnf
/usr/local/etc/my.cnf ~/.my.cnf 
                    order of preference, my.cnf, $MYSQL_TCP_PORT,

/usr/local/etc/my.cnf
/usr/local/Cellar/mariadb/10.5.9/.bottle/etc/my.cnf

 

mysqld.cnf 수정 - 0.0.0.0 해서 외부접속 허용

~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address  = 0.0.0.0


Mariadb 일 경우에는 아래의 my.cnf 를 열고 mysql 에 해당 소스 추가후 저장 Ctrl + o
sudo nano /usr/local/etc/my.cnf 

[mysqld]
bind-address=0.0.0.0 #특정 IP 주소만 접근 허용시, IP기술
port=3306 #서버 연결 통신 포트

mysql 재시작
brew services stop mariadb
brew services start mariadb

 

 

계정 생성 - dev_test
create user 'dev_test'@'%' identified by '비밀번호';

권한 주기
grant all privileges on 디비.* to 'dev_test'@'%';

flush privileges;

 

 

참고사이트 : 

https://valentine92.tistory.com/entry/ubuntu-mariadb-%EC%84%A4%EC%B9%98-%EB%B0%8F-%EC%99%B8%EB%B6%80%EC%A0%91%EC%86%8D-%EA%B8%B0%EB%B3%B8-%EC%84%A4%EC%A0%95

https://velog.io/@oyeon/MySQL-%EC%99%B8%EB%B6%80-%EC%A0%91%EC%86%8D

https://solbel.tistory.com/663

https://linked2ev.github.io/database/2021/06/19/MariaDB-6.-MySQL-%EC%84%A4%EC%A0%95-%ED%8C%8C%EC%9D%BC-my.cnf-%EC%9C%84%EC%B9%98/

https://jiwontip.tistory.com/62