my설치
-30d89c39-4efc-11eb-9f39-eec956d5f988:255
- MySQL, MariaDB 테스트 서버
[컨테이너 정리하고 CentOS 7 기반으로 재생성]
docker stop MySQL
docker stop MariaDB
docker rm MySQL
docker rm MariaDB
docker create --name="MySQL" -h MySQL -p 3307:3306 --restart unless-stopped -i -t docker.io/centos:7 /bin/bash --init-file /root/start.sh
docker create --name="MariaDB" -h MariaDB -p 3306:3306 --restart unless-stopped -i -t docker.io/centos:7 /bin/bash --init-file /root/start.sh
docker start MySQL
docker start MariaDB
[MySQL 설치]
1. 컨테이너 접속
docker exec -ti MySQL /bin/bash -l
2. OS 패키지 설치
yum install net-tools wget rsync openssl098e lsof which perl perl-Data-Dumper libaio* numactl-libs sudo -y
yum update -y
3. 로케일 설정
rm -f /etc/localtime
localedef -v -c -i en_US -f UTF-8 en_US.UTF-8
localedef -v -c -i ko_KR -f UTF-8 ko_KR.UTF-8
4. root 계정 암호 변경 / mysql 계정 생성 및 암호 변경
passwd
useradd mysql -d /mysql
passwd mysql
5. 디렉토리 생성
export DBNM=testdb
mkdir -p /mysql/$DBNM
mkdir -p /mysql/$DBNM/scripts
mkdir -p /data/$DBNM/
mkdir -p /data/$DBNM/datadir
mkdir -p /data/$DBNM/tmpdir
mkdir -p /data/$DBNM/logdir
mkdir -p /data/$DBNM/logdir/oldlog
mkdir -p /data/$DBNM/binlog
mkdir -p /data/$DBNM/relaylog
chown -R mysql:mysql /mysql
chown -R mysql:mysql /data
mkdir -p /backup/$DBNM/binary_backup
mkdir -p /backup/$DBNM/data_backup
mkdir -p /backup/$DBNM/data_backup/backup_log
mkdir -p /backup/$DBNM/data_backup/binlog_backup
chown -R mysql:mysql /backup
6. MySQL 설치파일 준비
cd /mysql/$DBNM
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.0/binary/tarball/percona-toolkit-3.5.0_x86_64.tar.gz
wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.30-23/binary/tarball/percona-xtrabackup-8.0.30-23-Linux-x86_64.glibc2.17.tar.gz
chown mysql:mysql -R /mysql/$DBNM
7. mysql 계정으로 변경
su - mysql
export DBNM=testdb
cd $DBNM
8. 설치 파일 압축 풀기
tar xvf *xz
tar xvfz *xtrabackup*tar.gz
tar xvfz *toolkit*tar.gz
9. 설치 파일 삭제
rm -f *.xz
rm -f *.tar.gz
10. 디렉토리 명칭 심볼릭 링크 생성
ln -s mysql* instance
ln -s *xtrabackup* xtrabackup
ln -s *toolkit* toolkit
11. /data/$DBNM/my.cnf 설정 편집
vi /data/$DBNM/my.cnf
[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
server-id = 1
basedir = /mysql/testdb/instance
datadir = /data/testdb/datadir
tmpdir = /data/testdb/tmpdir
character-set-server = UTF8MB4
collation-server = UTF8MB4_BIN
default_storage_engine = InnoDB
event-scheduler = 0
sysdate-is-now
performance_schema = on
back_log = 100
max_connections = 500
max_connect_errors = 100
thread_cache_size = 10
table_open_cache = 2048
wait_timeout = 86400
max_allowed_packet = 32M
max_heap_table_size = 128M
tmp_table_size = 128M
sort_buffer_size = 256K
join_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
transaction_isolation = READ-COMMITTED
plugin-load = auth_socket=auth_socket.so
thread_stack = 240K
mysqlx=0
### InnoDB Specific options ###
innodb_data_file_path = ibdata1:100M:autoextend
innodb_file_per_table = 1
innodb_buffer_pool_size = 512M
innodb_log_buffer_size = 16M
innodb_log_file_size = 16M
innodb_log_files_in_group = 4
innodb_open_files = 1024
innodb_autoextend_increment = 256
innodb_flush_log_at_trx_commit = 1
innodb_flush_method=O_DIRECT
innodb_change_buffering='all'
innodb_lock_wait_timeout = 120
### MyISAM Specific options ###
key_buffer_size = 64M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 4G
### Logging options ###
log_bin = /data/testdb/binlog/binlog
binlog_expire_logs_seconds = 604800
general_log = 0
slow_query_log = 1
long_query_time = 2
log_error = /data/testdb/logdir/error.log
slow_query_log_file = /data/testdb/logdir/slow.log
general_log_file = /data/testdb/logdir/general.log
12. mysql 계정의 .bash_profile 수정
echo '' >> ~/.bash_profile
echo export DBNM=$DBNM >> ~/.bash_profile
echo export PATH=/mysql/$DBNM/instance/bin:/mysql/$DBNM/xtrabackup/bin:/mysql/$DBNM/toolkit/bin:'$PATH' >> ~/.bash_profile
echo alias cdmy="'cd /mysql/$DBNM/instance'" >> ~/.bash_profile
echo alias cdsp="'cd /mysql/$DBNM/scripts'" >> ~/.bash_profile
echo alias cddt="'cd /data/$DBNM/datadir'" >> ~/.bash_profile
echo alias cdlog="'cd /data/$DBNM/logdir'" >> ~/.bash_profile
echo alias showerr="'vi /data/$DBNM/logdir/error.log'" >> ~/.bash_profile
echo alias tailerr="'tail -f /data/$DBNM/logdir/error.log'" >> ~/.bash_profile
source ~/.bash_profile
13. 데이터베이스 초기화
cdmy
./bin/mysqld --defaults-file=/data/$DBNM/my.cnf --initialize
14. 초기 설정 및 운영 스크립트 추가
#1.데이터베이스 기동 스크립트 작성
cat > /mysql/$DBNM/scripts/startup.sh << EOF
#!/bin/sh
source ~mysql/.bash_profile
cd /mysql/$DBNM/instance
./bin/mysqld_safe --defaults-file=/data/$DBNM/my.cnf &
EOF
#2. 데이터베이스 중단 스크립트 작성
cat > /mysql/$DBNM/scripts/shutdown.sh << EOF
#!/bin/sh
source ~mysql/.bash_profile
cd /mysql/$DBNM/instance
./bin/mysqladmin shutdown -u root -p
EOF
#3. 데이터베이스 접속 스크립트 작성
cat > /mysql/$DBNM/scripts/conn.sh << EOF
#!/bin/sh
source ~mysql/.bash_profile
cd /mysql/$DBNM/instance
./bin/mysql -h localhost -u root -p
EOF
#4. 로그파일 보관 주기 관리용 스크립트 작성
cat > /mysql/$DBNM/scripts/logrotate.sh << EOF
#!/bin/sh
source ~mysql/.bash_profile
cd /mysql/$DBNM/instance
DATE=\`date -d '-1 days' +'%Y%m%d'\`
mv /data/$DBNM/logdir/error.log /data/$DBNM/logdir/oldlog/error.\${DATE}.log
mv /data/$DBNM/logdir/slow.log /data/$DBNM/logdir/oldlog/slow.\${DATE}.log
./bin/mysql -h localhost -u mysql -e "flush logs"
find /data/$DBNM/logdir/oldlog -name '*.log' -ctime +180 -exec rm -f {} ';'
EOF
15. 데이터베이스 기동
cdsp
sh startup.sh
ps -ef | grep mysqld
tailerr
16. root계정 암호 초기화 // socket 인증 계정 추가
export PASS=`cat /data/$DBNM/logdir/error.log | grep tempo | grep pass | awk {'print $NF'}`
cdmy
./bin/mysql -h localhost -u root -p$PASS --connect-expired-password << EOF
alter user 'root'@'localhost' identified by 'a14231414';
exit
EOF
./bin/mysql -h localhost -u root -pa14231414 << EOF
create user 'root'@'%' identified by 'a14231414';
grant all privileges on *.* to 'root'@'%' with grant option;
grant proxy on ''@'' to 'root'@'%' with grant option;
create user 'mysql'@'localhost' identified with auth_socket;
grant all privileges on *.* to 'mysql'@'localhost';
exit
EOF
17. 접속 테스트
- 위에 설정한 계정 정보로 로그인 잘 되는지 확인.
<<OS 계정 mysql에서 DB계정 mysql로 암호 없이 접속>>
./bin/mysql -h localhost -u mysql
exit
<<root 계정에 암호 안넣으면 로그인 실패>>
./bin/mysql -h localhost -u root
<<root 계정에 암호 넣고 접속 확인. 초기 암호는 new1234!>>
./bin/mysql -h localhost -u root -p
<<DB 접속된 상태에서 계정 정보와 스키마 정보 확인후 DB 접속 종료>>
select user,host from mysql.user;
show databases;
exit
18. 자동기동되게 스크립트 수정
exit
vi /root/start.sh
sudo -u mysql /bin/bash /mysql/testdb/scripts/startup.sh
19. 히스토리 파일 정리
su - mysql
history -c
exit
history -c
exit