Raspberry PI 4 : MySQL 5.7.35 컴파일(수동설치)

출처

• Ubuntu server에 mysql 5.5.x 설치하기 -pupustory@- - Tistory
• [Linux/CentOS] Mysql을 컴파일해서 설치해보자 - AT BLOG

리눅스 정보 조회

출처 : 리눅스 종류 확인, 리눅스 버전 확인 - 제타위키

pi@raspberrypi:~$ cat /etc/*-release | uniq
PRETTY_NAME="Raspbian GNU/Linux 10 (buster)"
NAME="Raspbian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=raspbian
ID_LIKE=debian
HOME_URL="http://www.raspbian.org/"
SUPPORT_URL="http://www.raspbian.org/RaspbianForums"
BUG_REPORT_URL="http://www.raspbian.org/RaspbianBugs"

리눅스 비트 확인

출처 : 리눅스 32비트 64비트 확인 - 제타위키

pi@raspberrypi:~$ getconf LONG_BIT
32

관련패키지 설치

pi@raspberrypi:~$ sudo apt install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev

MySQL 계정 만들기

pi@raspberrypi:~$ sudo groupadd mysql
pi@raspberrypi:~$ sudo useradd -g mysql -s /bin/bash -m mysql

cmake 설치

pi@raspberrypi:~$ sudo apt-get install cmake

MySQL 소스 다운로드, 압축해제, 컴파일, 설치

pi@raspberrypi:~$ wget https://cdn.mysql.com/archives/mysql-5.7/mysql-boost-5.7.35.tar.gz 
pi@raspberrypi:~$ tar xvf mysql-boost-5.7.35.tar.gz 
pi@raspberrypi:~$ cd mysql-5.7.35
pi@raspberrypi:~/mysql-5.7.35$ sudo cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql57 \
-DMYSQL_DATADIR=/usr/local/mysql57/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql57/mysql.sock \
-DSYSCONFDIR=/usr/local/mysql57 \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_USER=mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DDOWNLOAD_BOOST=0 -DWITH_BOOST=./boost
pi@raspberrypi:~/mysql-5.7.35$ sudo make 
pi@raspberrypi:~/mysql-5.7.35$ sudo make install

환경설정

$ sudo vi /usr/local/mysql57/my.cnf

my.cnf

[mysqld]
port=8017
# basedir=/usr/local/mysql57
# datadir=/usr/local/mysql57/data
# pid-file=/usr/local/mysql57/mysqld.pid
# log_error=/usr/local/mysql57/mysql_error.log
# lc-messages-dir=/usr/local/mysql57/share

init_connect=SET collation_connection = utf8_general_ci
init_connect=SET NAMES utf8
character-set-server=utf8
collation-server=utf8_general_ci
# table_cache=1024
max_connections=2048
max_user_connections=500
max_connect_errors=10000
wait_timeout=300
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 5M
slow_query_log
long_query_time=3
max_allowed_packet=16M
sort_buffer_size = 2M
# skip-innodb
skip-name-resolve
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

skip_ssl
# disable_ssl

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

MySQL 설치 디렉토리 mysql 계정으로 권한 수정

pi@raspberrypi:~/mysql-5.7.35$ sudo chown -R mysql:mysql /usr/local/mysql57

MySQL 데이터베이스 초기화 (mysql 계정으로 실행)

pi@raspberrypi:~/mysql-5.7.35$ su mysql
Password: 
mysql@raspberrypi:/home/pi/mysql-5.7.35$ whoami
mysql
mysql@raspberrypi:/home/pi/mysql-5.7.35$ cd /usr/local/mysql57
mysql@raspberrypi:/usr/local/mysql57$ bin/mysql_install_db --no-defaults --user=mysql --datadir=/usr/local/mysql57/data --basedir=/usr/local/mysql57 -v
2021-12-19 09:54:25 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2021-12-19 09:54:25 [NOTE]    Creating data directory /usr/local/mysql57/data
2021-12-19 09:54:25 [NOTE]    Generating random password to /home/mysql/.mysql_secret...done.
2021-12-19 09:54:25 [NOTE]    Executing /usr/local/mysql57/bin/mysqld --no-defaults --bootstrap --datadir=/usr/local/mysql57/data --lc-messages-dir=/usr/local/mysql57/share --lc-messages=en_US --basedir=/usr/local/mysql57
2021-12-19 09:54:55 [NOTE]    Creating system tables...done.
2021-12-19 09:54:55 [NOTE]    Filling system tables with data...done.
2021-12-19 09:55:00 [NOTE]    Filling help table with data...done.
2021-12-19 09:55:00 [NOTE]    Creating user for internal session service...done.
2021-12-19 09:55:00 [NOTE]    Creating default user root@localhost
2021-12-19 09:55:00 [NOTE]    Creating default proxy root@localhost
2021-12-19 09:55:00 [NOTE]    Creating sys schema
2021-12-19 09:55:02 [NOTE]    done.
2021-12-19 09:55:03 [WARNING] The bootstrap log isn't empty:
2021-12-19 09:55:03 [WARNING] 2021-12-19T00:54:25.228532Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead

MySQL 서비스 등록

서비스 mysqld 파일 복사

mysql@raspberrypi:/usr/local/mysql57$ sudo cp /usr/local/mysql57/support-files/mysql.server /etc/init.d/mysqld57

mysqld 수정

mysql@raspberrypi:/usr/local/mysql57$ sudo vi /etc/init.d/mysqld57

mysqld파일을 열어서 basedir에 mysql이 설치된 디렉토리와 데이터 디렉토리(datadir)를 설정한다.

basedir=/usr/local/mysql57
datadir=/usr/local/mysql57/data

mysqld 서비스 등록

mysql@raspberrypi:/usr/local/mysql57$ sudo update-rc.d mysqld57 defaults

mysqld 서비스 실행

mysql@raspberrypi:/usr/local/mysql57$ sudo systemctl start mysqld57.service
mysql@raspberrypi:/usr/local/mysql57$ sudo systemctl status mysqld57.service
● mysqld57.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/init.d/mysqld57; generated)
   Active: active (running) since Sun 2021-12-19 10:27:04 KST; 12s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 15063 ExecStart=/etc/init.d/mysqld57 start (code=exited, status=0/SUCCESS)
    Tasks: 28 (limit: 4915)
   CGroup: /system.slice/mysqld57.service
           ├─15071 /bin/sh /usr/local/mysql57/bin/mysqld_safe --datadir=/usr/local/mysql57/data --pid-file=/usr/local/mysql57/data/raspb
           └─15419 /usr/local/mysql57/bin/mysqld --basedir=/usr/local/mysql57 --datadir=/usr/local/mysql57/data --plugin-dir=/usr/local/
 
Dec 19 10:27:01 raspberrypi systemd[1]: Starting LSB: start and stop MySQL...
Dec 19 10:27:01 raspberrypi mysqld57[15063]: Starting MySQL
Dec 19 10:27:02 raspberrypi mysqld57[15063]: .Logging to '/usr/local/mysql57/data/raspberrypi.err'.
Dec 19 10:27:04 raspberrypi mysqld57[15063]: ...
Dec 19 10:27:04 raspberrypi systemd[1]: Started LSB: start and stop MySQL.
mysql@raspberrypi:/usr/local/mysql57$ sudo systemctl stop mysqld57.service

root 암호 초기화

권한을 무시하고 mysql 서버 실행

mysql@raspberrypi:/usr/local/mysql57$ bin/mysqld_safe --skip-grant-tables &
[1] 15759
mysql@raspberrypi:/usr/local/mysql57$ 2021-12-19T01:32:53.052394Z mysqld_safe Logging to '/usr/local/mysql57/data/raspberrypi.err'.
2021-12-19T01:32:53.127375Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql57/data

root 암호 설정

mysql@raspberrypi:/usr/local/mysql57$ bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35-log Source distribution
 
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> 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
mysql> update user set authentication_string = password('manager01!') where user = 'root';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit
Bye

ERROR 1820 (HY000) 오류 해결

mysql> use mysql
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD = PASSWORD('sql');
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> 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

사용자 추가

mysql> create user 'user1'@'%' identified by 'userpw'; 
mysql> grant all privileges on *.* to 'user1'@'%' with grant option; 
mysql> flush privileges;

사용자 추가

mysql> create user 'terecal'@'%' identified by '****';
mysql> GRANT ALL privileges ON terecal_db.* TO 'terecal'@'%';
mysql> flush privileges;