ERROR 2006 (HY000) at line 115: MySQL server has gone away

 

gcp  CloudSQL 이용시 ERROR 2006 (HY000) at line 115: MySQL server has gone away 해결 방법

데이터베이스 플래그 max_allowed_packet  를 추가  합니다.

 

1.증상

[root@test-instance ~]# mysql -h db01 -uroot -p test_db < test-db.sql 
Enter password: ERROR 2006 (HY000) at line 115: MySQL server has gone away 
[root@test-instance ~]#

 

 

2. 해결방법

저장소 -> SQL -> 수정 -> 인스턴스 수정 -> 데이터베이스 플래그로 이동합니다.

max_allowed_packet 1073741824 패킷값을 추가

참고페이지: https://cloud.google.com/sql/docs/mysql/flags

 

 

3. 설정후 mysql 복구

[root@test-instance ~]# mysql -h db01 -uroot -p test_db < test-db.sql 
Enter password: 
[root@test-instance ~]#

 

 

 

 

Nginx proxy https

 

Nginx Self-sign https : http://blog.crois.net/2019/09/23/centos7-nginx-self-signed-https/ 설정하였던 VM 을

이용하여 nginx proxy 를 테스트 합니다. 상단 nginx-proxy 에만 인증서가 있으면 되며 백단 서버 두대의 경우

별도의 인증서 및 설정이 필요 하지 않습니다.

nginx 설치및 php 설치의 경우 이전 포스트를 참고해 주세요.

Nginx-Proxy 서버에서 ssl 설정을 하고 Nginx-www1 / Nginx-www2 에서는 80 port 설정만 진행 합니다.

1.Nginx-proxy 설정

[root@test ~]# vi /etc/nginx/sites-enabled/test_com.conf
server {
    listen       80;
    server_name  www.test.com test.com;
    root   /var/www/html/test.com/public_html;
    index  index.php index.html index.htm;
    location / {
        return 301 https://test.com$request_uri;
    }
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
    }
}

server {
    listen       443 http2 ssl;
    server_name  www.test.com test.com;
    root   /var/www/html/test.com/public_html;
    index  index.php index.html index.htm;

    access_log /var/www/html/test.com/logs/access.log;
    error_log  /var/www/html/test.com/logs/error.log warn;


    ssl_certificate /etc/ssl/certs/nginx-selfsign.crt;
    ssl_certificate_key /etc/ssl/private/nginx-selfsign.key;
    ssl_dhparam /etc/ssl/certs/dhparam.pem;
    ssl_session_timeout 10m;
    ssl_session_cache shared:SSL:10m;
    ssl_session_tickets off;
    ssl_protocols TLSv1.2;
    ssl_ecdh_curve secp384r1;
    #ssl_ciphers  ECDH+AESGCM:!AES128:!RSA+AES:!aNULL:!MD5:!DSS:!DHE:!kEDH:HIGH:!eNULL:!EXPORT:!DES:!RC4:!PSK:!AECDH:!LOW:!SRP:!ADH:!RSA:TLS13-CHACHA20-POLY1305-SHA256:TLS13-AES-256-GCM-SHA384:!COMPLEMENTOFDEFAULT;
    ssl_ciphers AES256+EECDH:AES256+EDH:!aNULL;
    ssl_prefer_server_ciphers on;
    add_header Strict-Transport-Security "max-age=31536000; includeSubdomains";
#    add_header X-Frame-Options DENY;
    add_header X-Content-Type-Options nosniff;

    # OCSP Stapling ---
    # fetch OCSP records from URL in ssl_certificate and cache them
    ssl_stapling on;
    ssl_stapling_verify on;


    fastcgi_buffering               on;
    fastcgi_buffer_size             16k;
    fastcgi_buffers                 16 16k;

    # time-out settings
    fastcgi_connect_timeout         600s;
    fastcgi_send_timeout            600s;
    fastcgi_read_timeout            600s;

    # php performance settings
    sendfile                        on;
    tcp_nopush                      off;
    keepalive_requests              0;


    location / {
        rewrite ^/(/.*)$ $1 break;
        proxy_pass http://test.com;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_redirect off;
        try_files $uri $uri/ /index.php?$query_string;
    }
}
upstream test.com {
    server 10.10.10.93:80;
    server 10.10.10.94:80;
}
[root@test ~]# systemctl restart nginx

 

 

2. server 설정

[root@www1 ~]# vi /etc/nginx/sites-enabled/test_com.conf
server {
    listen       80;
    server_name  www.test.com test.com;
    root   /var/www/html/test.com/public_html;
    index  index.php index.html index.htm;
    location / {
        try_files $uri $uri/ /index.php?$query_string;
        autoindex on;
    }

    access_log  /var/www/html/test.com/logs/access.log;
    error_log  /var/www/html/test.com/logs/error.log warn;

    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
    }

    location ~ \.php$ {
        try_files $uri =404;
        fastcgi_pass 127.0.0.1:9000;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        include fastcgi_params;
    }
}

[root@www1 ~]# systemctl restart nginx ; systemctl restart php-fpm

 

 

3. web-site 확인

 

4. server log 확인

tail -f access.log 를 확인 합니다.

Nginx Self-signed https

 

nginx https 설정을 테스트 합니다.

nginx web-server 와 php71 까지 설치후 Test 도메인으로 접속을 테스트 합니다.

 

 

1. Nginx 설치

[root@test ~]# vi /etc/yum.repos.d/nginx.repo
[nginx]
name=nginx
baseurl=http://nginx.org/packages/centos/7/$basearch/
gpgcheck=0
enabled=1
[root@test ~]# yum clean all

[root@test ~]# yum install -y nginx
[root@test ~]# systemctl enable nginx ; systemctl start nginx

 

 

2. php71 설치

[root@test ~]# yum install -y epel-release yum-utils
[root@test ~]# rpm -Uvh http://ftp.riken.jp/Linux/remi/enterprise/remi-release-7.rpm
[root@test ~]# yum-config-manager --enable remi-php71
[root@test ~]# yum -y install php php-mysql php-fpm php-opcache php-gd php-ldap php-odbc php-pear php-xml php-xmlrpc php-mbstring php-soap curl curl-devel
[root@test ~]# vi /etc/php.ini
date.timezone = Asia/Seoul
[root@test ~]# vi /etc/php-fpm.d/www.conf

user = nginx
group = nginx

listen.owner = nginx
listen.group = nginx
listen.mode = 0660

[root@test ~]# systemctl restart php-fpm
[root@test ~]# systemctl enable php-fpm

 

 

3. Nginx 설정

[root@test ~]# mkdir /etc/nginx/sites-enabled
[root@test ~]# vi /etc/nginx/nginx.conf
user  nginx;
worker_processes  1;

error_log  /var/log/nginx/error.log warn;
pid        /var/run/nginx.pid;


events {
    worker_connections  1024;
}


http {
    include       /etc/nginx/mime.types;
    default_type  application/octet-stream;

    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  /var/log/nginx/access.log  main;

    sendfile        on;
    #tcp_nopush     on;

    keepalive_timeout  65;

    #gzip  on;

    include /etc/nginx/conf.d/*.conf;
    include /etc/nginx/sites-enabled/*.conf;
}

[root@test ~]# vi /etc/nginx/conf.d/default.conf
server {
    listen       80 default_server;
    server_name  localhost;

    charset UTF-8;

        root   /usr/share/nginx/html;
        index  index.php index.html index.htm;
    location / {
        try_files $uri $uri/ /index.php?$query_string;
    }

    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   /usr/share/nginx/html;
    }

    location ~ \.php$ {
        try_files $uri =404;
        fastcgi_pass 127.0.0.1:9000;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        #fastcgi_pass unix:/run/php-fpm/php-fpm.sock;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        include fastcgi_params;
    }

}

[root@test ~]# mkdir -p /var/www/html/test.com/{public_html,logs}
[root@test ~]# vi /etc/nginx/sites-enabled/test_com.conf
server {
    listen       80;
    server_name  www.test.com test.com;
    root   /var/www/html/test.com/public_html;
    index  index.php index.html index.htm;
    location / {
        try_files $uri $uri/ /index.php?$query_string;
        autoindex on;
    }

    access_log  /var/www/html/test.com/logs/access.log;
    error_log  /var/www/html/test.com/logs/error.log warn;

    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
    }

    location ~ \.php$ {
        try_files $uri =404;
        fastcgi_pass 127.0.0.1:9000;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        include fastcgi_params;
    }
}



[root@test ~]# chown -R nginx:nginx /var/www/html/test.com/
[root@test ~]# systemctl restart nginx

 

 

4. SSL Certificate 생성

[root@test ~]# mkdir /etc/ssl/private
[root@test ~]# chmod 700 /etc/ssl/private
[root@test ~]# openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout /etc/ssl/private/nginx-selfsign.key -out /etc/ssl/certs/nginx-selfsign.crt
Generating a 2048 bit RSA private key
.....................................................................+++
.........................................................................................................................+++
writing new private key to '/etc/ssl/private/nginx-selfsig.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:KR
State or Province Name (full name) []:Seoul
Locality Name (eg, city) [Default City]:GangNam Gu
Organization Name (eg, company) [Default Company Ltd]:test.com
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:test.com
Email Address []:admin@test.com
[root@test ~]#
[root@test ~]# openssl dhparam -out /etc/ssl/certs/dhparam.pem 2048
Generating DH parameters, 2048 bit long safe prime, generator 2
This is going to take a long time
.................................................................
~중략
[root@test ~]#

 

 

5. Nginx 설정 변경

[root@test ~]# vi /etc/nginx/sites-enabled/test_com.conf

server {
    listen       80;
    server_name  www.test.com test.com;
    root   /var/www/html/test.com/public_html;
    index  index.php index.html index.htm;
    location / {
        return 301 https://test.com$request_uri;
    }
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
    }
}

server {
    listen       443 http2 ssl;
    server_name  www.test.com test.com;
    root   /var/www/html/test.com/public_html;
    index  index.php index.html index.htm;

    access_log /var/www/html/test.com/logs/access.log;
    error_log  /var/www/html/test.com/logs/error.log warn;


    ssl_certificate /etc/ssl/certs/nginx-selfsign.crt;
    ssl_certificate_key /etc/ssl/private/nginx-selfsign.key;
    ssl_dhparam /etc/ssl/certs/dhparam.pem;
    ssl_session_timeout 10m;
    ssl_session_cache shared:SSL:10m;
    ssl_session_tickets off;
    ssl_protocols TLSv1.2;
    ssl_ecdh_curve secp384r1;
    #ssl_ciphers  ECDH+AESGCM:!AES128:!RSA+AES:!aNULL:!MD5:!DSS:!DHE:!kEDH:HIGH:!eNULL:!EXPORT:!DES:!RC4:!PSK:!AECDH:!LOW:!SRP:!ADH:!RSA:TLS13-CHACHA20-POLY1305-SHA256:TLS13-AES-256-GCM-SHA384:!COMPLEMENTOFDEFAULT;
    ssl_ciphers AES256+EECDH:AES256+EDH:!aNULL;
    ssl_prefer_server_ciphers on;
    add_header Strict-Transport-Security "max-age=31536000; includeSubdomains";
#    add_header X-Frame-Options DENY;
    add_header X-Content-Type-Options nosniff;

    # OCSP Stapling ---
    # fetch OCSP records from URL in ssl_certificate and cache them
    ssl_stapling on;
    ssl_stapling_verify on;


    fastcgi_buffering               on;
    fastcgi_buffer_size             16k;
    fastcgi_buffers                 16 16k;

    # time-out settings
    fastcgi_connect_timeout         600s;
    fastcgi_send_timeout            600s;
    fastcgi_read_timeout            600s;

    # php performance settings
    sendfile                        on;
    tcp_nopush                      off;
    keepalive_requests              0;


    location / {
        try_files $uri $uri/ /index.php?$query_string;
    }
    location ~ \.php$ {
        try_files $uri =404;
        fastcgi_pass 127.0.0.1:9000;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_read_timeout 600;
        include fastcgi_params;
    }
}
[root@test ~]#
[root@test ~]# systemctl restart nginx
[root@test ~]# vi /var/www/html/test.com/public_html/index.php
<?php phpinfo(); ?>

 

 

6. web-site 접속테스트

 

mysqldump-max_allowed_packet error

max_allowed_packet error 발생시 –max_allowed_packet=1024M 옵션을 사용 하면 됩니다.

[root@localhost ~]# mysqldump -uroot -p --all-databases > db-data.sql
Enter password:
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `wp_aiowps_global_meta` at row: 1
[root@localhost ~]# mysqldump -uroot -p --all-databases --max_allowed_packet=1024M > db-data.sql
Enter password:
[root@localhost ~]#