[Ubuntu] mariadb root 비번 재설정 , mariadb 삭제하기, mariadb 재설치
$ sudo systemctl stop mariadb
$ sudo systemctl stop mysql
백그라운드에서 안전모드로 가동
$ sudo mysqld_safe --skip-grant-tables &
$ mysql -u root
그러면 mariadb 진입에 성공한다.
여기서, 아래와 같이 비밀번호 재설정한다.
$ mysql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD('PASSWORD');
그런데, 아래와 같이 에러가 난다면
The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
다음줄로 해결하도록 하자.
$ mysql > flush privileges;
$ mysql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD('PASSWORD');
$ mysql > flush privileges;
완료되었으면, 이제 mysql_safe 를 종료하는데, 현재 진행중인 내용을 찍어보면,
$ ps aux | grep "mysqld_safe"
root 301807 0.0 0.0 11496 5680 pts/0 S 07:28 0:00 sudo mysqld_safe --skip-grant-tables
root 301808 0.0 0.0 11496 880 pts/1 Ss+ 07:28 0:00 sudo mysqld_safe --skip-grant-tables
root 301809 0.0 0.0 2888 1892 pts/1 S 07:28 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
dev 302108 0.0 0.0 6608 2256 pts/0 S+ 07:47 0:00 grep --color=auto mysqld_safe
$ sudo mysqladmin shutdown
[1]+ Done sudo mysqld_safe --skip-grant-tables
이제 kill 되었다. (괜히 강제 shutdown 했다.)
그런데, 재가동이 안되었다. 어디서 에러가 났다고 한다.
mariadb.service - MariaDB 10.6.12 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Sat 2023-12-02 08:03:14 KST; 13s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 1159 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 1160 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 1162 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set->
Process: 1188 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
Main PID: 1188 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"
CPU: 87ms
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [Note] InnoDB: Starting shutdown...
Dec 02 08:03:14 dev mariadbd[1188]: 2023-12-02 8:03:14 0 [ERROR] Plugin 'InnoDB' init function returned error.
Dec 02 08:03:14 dev mariadbd[1188]: 2023-12-02 8:03:14 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Dec 02 08:03:14 dev mariadbd[1188]: 2023-12-02 8:03:14 0 [Note] Plugin 'FEEDBACK' is disabled.
Dec 02 08:03:14 dev mariadbd[1188]: 2023-12-02 8:03:14 0 [ERROR] Unknown/unsupported storage engine: InnoDB
Dec 02 08:03:14 dev mariadbd[1188]: 2023-12-02 8:03:14 0 [ERROR] Aborting
Dec 02 08:03:14 dev systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Dec 02 08:03:14 dev systemd[1]: mariadb.service: Failed with result 'exit-code'.
Dec 02 08:03:14 dev systemd[1]: Failed to start MariaDB 10.6.12 database server.
journalctl 로 그 프로세서를 확인해보았다.
dev@dev:~$ sudo journalctl _PID=1188
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [Note] Starting MariaDB 10.6.12-MariaDB-0ubuntu0.22.04.1 source revision as process 1188
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [Note] InnoDB: Number of pools: 1
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [Note] InnoDB: Completed initialization of buffer pool
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=42526,42526
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT at 42526 between the checkpoint 42526 and the end 42550.
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
Dec 02 08:03:13 dev mariadbd[1188]: 2023-12-02 8:03:13 0 [Note] InnoDB: Starting shutdown...
Dec 02 08:03:14 dev mariadbd[1188]: 2023-12-02 8:03:14 0 [ERROR] Plugin 'InnoDB' init function returned error.
Dec 02 08:03:14 dev mariadbd[1188]: 2023-12-02 8:03:14 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Dec 02 08:03:14 dev mariadbd[1188]: 2023-12-02 8:03:14 0 [Note] Plugin 'FEEDBACK' is disabled.
Dec 02 08:03:14 dev mariadbd[1188]: 2023-12-02 8:03:14 0 [ERROR] Unknown/unsupported storage engine: InnoDB
Dec 02 08:03:14 dev mariadbd[1188]: 2023-12-02 8:03:14 0 [ERROR] Aborting
-----------> 해결이 안됨
https://yooloo.tistory.com/127
Ubuntu 20.04에 MariaDB 삭제하기
우분투를 사용하는 경우 apt-get 명령을 사용하여 데이터베이스 패키지를 제거합니다. 함께 설치된 mariadb 패키지를 확인한 후 관련 패키지 모두를 삭제합니다. $ sudo dpkg -l | grep mariadb ii mariadb-client
yooloo.tistory.com
Ubuntu 20.04에 MariaDB 삭제하기
먼저 설치된 mariadb 확인하기
dev@dev:~$ sudo dpkg -l | grep mariadb
ii libmariadb3:amd64 1:10.6.12-0ubuntu0.22.04.1 amd64 MariaDB database client library
ii mariadb-client 1:10.6.12-0ubuntu0.22.04.1 all MariaDB database client (metapackage depending on the latest version)
ii mariadb-client-10.6 1:10.6.12-0ubuntu0.22.04.1 amd64 MariaDB database client binaries
ii mariadb-client-core-10.6 1:10.6.12-0ubuntu0.22.04.1 amd64 MariaDB database core client binaries
ii mariadb-common 1:10.6.12-0ubuntu0.22.04.1 all MariaDB common configuration files
ii mariadb-server 1:10.6.12-0ubuntu0.22.04.1 all MariaDB database server (metapackage depending on the latest version)
ii mariadb-server-10.6 1:10.6.12-0ubuntu0.22.04.1 amd64 MariaDB database server binaries
ii mariadb-server-core-10.6 1:10.6.12-0ubuntu0.22.04.1 amd64 MariaDB database core server files
dev@dev:~$ sudo apt-get purge mariadb-server
dev@dev:~$ sudo apt-get purge mariadb-server-core-10.6
dev@dev:~$ sudo apt-get purge mariadb-common
dev@dev:~$ sudo apt-get purge mariadb-client
등등 삭제하기
ubutn mariadb 새로 설치
mariaDB 설치 및 초기 설정 (Ubuntu)
Ubuntu에서 mariaDB를 설치하고 초기 설정을 해봅니다.
velog.io
sudo apt-get install mariadb-server
mysql -uroot -p
비번 묻는데 접속이 안된다.
suto mysql 하면 접속이 되고,
use mysql 한다.
dev@dev:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
dev@dev:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
dev@dev:~$ sudo mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04
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
[ Timezone 설정]
Database changed
MariaDB [mysql]> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.000 sec)
MariaDB [mysql]> exit
Bye
서울시간대로 설정하기
dev@dev:~$ sudo timedatectl set-timezone 'Asia/Seoul'
date
Sat Dec 2 08:45:46 AM KST 2023
mariadb 가동중인것 확인
dev@dev:~$ sudo systemctl status mariadb
● mariadb.service - MariaDB 10.6.12 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2023-12-02 08:46:50 KST; 1min 18s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 2773 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 2774 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 2776 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl >
Process: 2819 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 2821 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 2806 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 8 (limit: 9339)
Memory: 60.6M
CPU: 313ms
CGroup: /system.slice/mariadb.service
└─2806 /usr/sbin/mariadbd
Dec 02 08:46:50 dev mariadbd[2806]: 2023-12-02 8:46:50 0 [Note] Server socket created on IP: '127.0.0.1'.
Dec 02 08:46:50 dev mariadbd[2806]: 2023-12-02 8:46:50 0 [Note] InnoDB: Buffer pool(s) load completed at 231202 8:46:50
Dec 02 08:46:50 dev mariadbd[2806]: 2023-12-02 8:46:50 0 [Note] /usr/sbin/mariadbd: ready for connections.
Dec 02 08:46:50 dev mariadbd[2806]: Version: '10.6.12-MariaDB-0ubuntu0.22.04.1' socket: '/run/mysqld/mysqld.sock' port: 3306 Ubuntu 22.04
Dec 02 08:46:50 dev systemd[1]: Started MariaDB 10.6.12 database server.
Dec 02 08:46:50 dev /etc/mysql/debian-start[2826]: Looking for 'mariadb' as: /usr/bin/mariadb
Dec 02 08:46:50 dev /etc/mysql/debian-start[2826]: Looking for 'mariadb-check' as: /usr/bin/mariadb-check
Dec 02 08:46:50 dev /etc/mysql/debian-start[2826]: This installation of MariaDB is already upgraded to 10.6.12-MariaDB.
Dec 02 08:46:50 dev /etc/mysql/debian-start[2826]: There is no need to run mysql_upgrade again for 10.6.12-MariaDB.
Dec 02 08:46:50 dev /etc/mysql/debian-start[2826]: You can use --force if you still want to run mysql_upgrade
[ root 비밀번호 변경하기 ]
sudo mysql 하고 들어가서
use mysql 데이터베이스 사용체크하고
mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
mysql> flush privileges;
mysql> quit;
하면 localhost 의 root 가 빈값으로 리셋된다.
MariaDB [mysql]> alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password as PASSWORD('비밀번호');
Query OK, 0 rows affected (0.006 sec)
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
(mariadb)
alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password as PASSWORD('비밀번호');
(mysql)
ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY '비밀번호';
자 ~ 다시 exit 로 빠져나와서
mysql -uroot -p 로 접속해보자 ~ 성공
참고 : https://velog.io/@shin6949/mariaDB-%EC%84%A4%EC%B9%98-%EB%B0%8F-%EC%B4%88%EA%B8%B0-%EC%84%A4%EC%A0%95-Ubuntu
참고 : https://dev-whoan.xyz/48
참고 : https://stackoverflow.com/questions/53086514/how-to-stop-mysql-after-running-it-using-mysqld-safe
참고 : https://stackoverflow.com/questions/66485096/what-is-the-correct-syntax-for-updating-users-mysql-password-while-specifying-au