MariaDB [mysql]> stop slave; 


한 뒤,


MariaDB [mysql]> SET GLOBAL sql_slave_skip_counter = 1;


하고



MariaDB [mysql]> start slave;



한다.




에러가 계속 발생하면,


위 단계를 반복한다.


블로그 이미지

엘로드넷

댓글을 달아 주세요

start slave 시 아래와 같은 메시지가 나온다.


MariaDB [mysql]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log
MariaDB [mysql]> MariaDB [mysql]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log
MariaDB [mysql]> 
MariaDB [mysql]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log
MariaDB [mysql]> 
MariaDB [mysql]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log
MariaDB [mysql]> 

MariaDB [mysql]> start slave;

ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log

MariaDB [mysql]> 




해결방법 : slave를 reset한 후 start 한다.




MariaDB [mysql]> reset slave;

Query OK, 0 rows affected (0.014 sec)


MariaDB [mysql]> start slave;

Query OK, 0 rows affected (0.015 sec)





블로그 이미지

엘로드넷

댓글을 달아 주세요

replication을 유지하면서 mysql-bin 로그 삭제



[마스터에서 수행]


1. 로그 목록 확인 [마스터에서 수행]


MariaDB [mysql]> show binary logs;

+------------------+------------+

| Log_name         | File_size  |

+------------------+------------+

| mysql-bin.000001 | 1073742334 |

| mysql-bin.000002 | 1073742066 |

| mysql-bin.000003 | 1073742021 |

| mysql-bin.000004 | 1073742090 |

| mysql-bin.000005 | 1074091311 |

| mysql-bin.000006 | 1074186164 |

| mysql-bin.000007 | 1074181534 |

| mysql-bin.000008 | 1073741968 |

| mysql-bin.000009 | 1073744583 |

| mysql-bin.000010 | 1073741939 |

| mysql-bin.000011 | 1000547863 |

+------------------+------------+

11 rows in set (0.00 sec)





1. 로그전체 삭제 --> replication 이 깨지므로 slave에서 replication을 다시 시작해줘야 한다.


MariaDB [mysql]>RESET MASTER;



2. replication유지하며 과거 로그 삭제


아래 명령어로 현재 slave가 사용중인 로그 확인[slave에서 수행]



MariaDB [mysql]> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.1

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000011

          Read_Master_Log_Pos: 1002605164

               Relay_Log_File: mysql-relay-bin.000034

                Relay_Log_Pos: 185226372

        Relay_Master_Log_File: mysql-bin.000011

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: 

           Replicate_Do_Table: 

       Replicate_Ignore_Table: 

      Replicate_Wild_Do_Table: 

  Replicate_Wild_Ignore_Table: 

                   Last_Errno: 0

                   Last_Error: 

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 1002605164

              Relay_Log_Space: 1002606071

              Until_Condition: None

               Until_Log_File: 

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File: 

           Master_SSL_CA_Path: 

              Master_SSL_Cert: 

            Master_SSL_Cipher: 

               Master_SSL_Key: 

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 1

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

                   Using_Gtid: No

                  Gtid_IO_Pos: 

      Replicate_Do_Domain_Ids: 

  Replicate_Ignore_Domain_Ids: 

                Parallel_Mode: conservative

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

1 row in set (0.00 sec)


ERROR: No query specified


MariaDB [mysql]> 


현재 slave가 사용중인 master 의 로그 파일은 mysql-bin.000011 이다.


그러므로 이것 이전 로그파일은  삭제해도 replication이 깨지지 않는다.



로그를 삭제하기 위해 PURGE BINARY LOGS 명령어를 사용한다.[마스터에서 수행]



첫번째 방법 :  PURGE BINARY LOGS TO 'mysql-bin.00003';


mysql-bin.00003 보다 낮은 번호의 로그들이 한꺼번에 삭제된다.


(mysql-bin.000001, mysql-bin.000002이 삭제됨)




두번째 방법 : PURGE BINARY LOGS BEFORE '2019-01-15 00:00:00';

2019년 1월 15일 0시 이전에 생성된 로그 파일들이 모두 삭제된다.




끝.






블로그 이미지

엘로드넷

댓글을 달아 주세요

replication

MySQL, MariaDB 2018. 6. 26. 10:06

1. A의 모든 DB를 B로 복제



A : 

mysqldump -u root -p --all-databases > all.sql


B : 


mysql -u root -p < all.sql




2. 마스터와 슬레이브 설정



A. 마스터 설정


설정파일  : /etc/my.cnf.d/server.cnf


서버아이디는 1로 설정


로그파일은 mysql-bin.000001 과 같은 형태로 생성됨




[mysqld]


server_id = 1


log-bin=mysql-bin

expire_logs_days=7





replication 사용자 등록


마스터 호스트 : 192.168.0.1

사용자 : repl

비밀번호 : 12345



MariaDB [mysql]> grant select, reload, lock tables, replication slave, replication client on *.* to 'repl@'192.168.0.1' identified by '12345';ant select, reload, lock tables, replication slave, replication client on *.* to 'id'@'slave ip address' identified by 'password';

MariaDB [mysql]> flush privileges;







B. 슬레이브 설정



설정파일 : /etc/my.cnf.d/server.cnf


서버아이디 : 2 로 설정


로그파일은  mysql-relay-bin.000001 과 같은 형식으로 생성됨


[mysqld]



server_id = 2

relay_log = mysql-relay-bin







3. replication


마스터 정보 확인


A : 


MariaDB [mysql]> show master status \G;

*************************** 1. row ***************************

            File: mysql-bin.000001

        Position: 104060378

    Binlog_Do_DB: 

Binlog_Ignore_DB: 

1 row in set (0.00 sec)


ERROR: No query specified






마스터의 로그파일 : mysql-bin.000001

로그 포지션 : 104060378




B : 설정



MariaDB [mysql]> stop slave;


MariaDB [mysql]> change master to 

master_host = '192.168.0.1',

master_user = 'repl',

master_password = '12345',

master_log_file = 'mysql-bin.000001',

master_log_pos = 104060378

;


MariaDB [mysql]> start slave;





슬레이브 상태 확인


MariaDB [mysql]> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.1

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 662570599

               Relay_Log_File: mysql-relay-bin.000001

                Relay_Log_Pos: 42800785

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: 

           Replicate_Do_Table: 

       Replicate_Ignore_Table: 

      Replicate_Wild_Do_Table: 

  Replicate_Wild_Ignore_Table: 

                   Last_Errno: 0

                   Last_Error: 

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 43002550

              Relay_Log_Space: 662370967

              Until_Condition: None

               Until_Log_File: 

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File: 

           Master_SSL_CA_Path: 

              Master_SSL_Cert: 

            Master_SSL_Cipher: 

               Master_SSL_Key: 

        Seconds_Behind_Master: 416430

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 1

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

                   Using_Gtid: No

                  Gtid_IO_Pos: 

      Replicate_Do_Domain_Ids: 

  Replicate_Ignore_Domain_Ids: 

                Parallel_Mode: conservative

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: updating

1 row in set (0.00 sec)


ERROR: No query specified


MariaDB [mysql]> 






mysql 디비 디렉토리에 가 보면,

아래와 같이 로그파일이 생성되어 있다.



-rw-rw----. 1 mysql mysql  47888824  6월 26 10:03 mysql-bin.000001

-rw-rw----. 1 mysql mysql        19  6월  7 11:22 mysql-bin.index

-rw-rw----. 1 mysql mysql       309  6월 21 09:02 mysql-relay-bin.000001

-rw-rw----. 1 mysql mysql       150  6월 26 10:03 mysql-relay-bin.index






끝.















블로그 이미지

엘로드넷

댓글을 달아 주세요

1. nginx 설치


[root@localhost yum.repos.d]# vi /etc/yum.repos.d/nginx.repo 


[nginx]

name=nginx repo

baseurl=http://nginx.org/packages/centos/7/$basearch/

gpgcheck=0

enabled=1

~                                                                                                                                         

[root@localhost yum.repos.d]# yum install nginx


Loaded plugins: fastestmirror, replace

http://nginx.org/packages/OS/OSRELEASE/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found

Trying other mirror.

To address this issue please refer to the below knowledge base article 


https://access.redhat.com/articles/1320623


If above article doesn't help to resolve this issue please create a bug on https://bugs.centos.org/




 One of the configured repositories failed (nginx repo),

 and yum doesn't have enough cached data to continue. At this point the only

 safe thing yum can do is fail. There are a few ways to work "fix" this:


     1. Contact the upstream for the repository and get them to fix the problem.


     2. Reconfigure the baseurl/etc. for the repository, to point to a working

        upstream. This is most often useful if you are using a newer

        distribution release than is supported by the repository (and the

        packages for the previous distribution release still work).


     3. Run the command with the repository temporarily disabled

            yum --disablerepo=nginx ...


     4. Disable the repository permanently, so yum won't use it by default. Yum

        will then just ignore the repository until you permanently enable it

        again or use --enablerepo for temporary usage:


            yum-config-manager --disable nginx

        or

            subscription-manager repos --disable=nginx


     5. Configure the failing repository to be skipped, if it is unavailable.

        Note that yum will try to contact the repo. when it runs most commands,

        so will have to try and fail each time (and thus. yum will be be much

        slower). If it is a very temporary problem though, this is often a nice

        compromise:


            yum-config-manager --save --setopt=nginx.skip_if_unavailable=true


failure: repodata/repomd.xml from nginx: [Errno 256] No more mirrors to try.

http://nginx.org/packages/OS/OSRELEASE/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found

[root@localhost yum.repos.d]# vi /etc/yum.repos.d/nginx.repo

[root@localhost yum.repos.d]# yum install nginx

Loaded plugins: fastestmirror, replace

base                                                                                                               | 3.6 kB  00:00:00     

epel/x86_64/metalink                                                                                               | 6.2 kB  00:00:00     

extras                                                                                                             | 3.4 kB  00:00:00     

nginx                                                                                                              | 2.9 kB  00:00:00     

updates                                                                                                            | 3.4 kB  00:00:00     

webtatic                                                                                                           | 3.6 kB  00:00:00     

nginx/x86_64/primary_db                                                                                            |  18 kB  00:00:00     

Loading mirror speeds from cached hostfile

 * base: mirror.oasis.onnetcorp.com

 * epel: mirror.premi.st

 * extras: mirror.oasis.onnetcorp.com

 * updates: mirror.oasis.onnetcorp.com

 * webtatic: sp.repo.webtatic.com

Resolving Dependencies

--> Running transaction check

---> Package nginx.x86_64 1:1.10.2-1.el7.ngx will be installed

--> Finished Dependency Resolution


Dependencies Resolved


==========================================================================================================================================

 Package                      Arch                          Version                                    Repository                    Size

==========================================================================================================================================

Installing:

 nginx                        x86_64                        1:1.10.2-1.el7.ngx                         nginx                        643 k


Transaction Summary

==========================================================================================================================================

Install  1 Package


Total download size: 643 k

Installed size: 2.2 M

Is this ok [y/d/N]: y

Downloading packages:

nginx-1.10.2-1.el7.ngx.x86_64.rpm                                                                                  | 643 kB  00:00:02     

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : 1:nginx-1.10.2-1.el7.ngx.x86_64                                                                                        1/1 

----------------------------------------------------------------------


Thanks for using nginx!


Please find the official documentation for nginx here:

* http://nginx.org/en/docs/


Commercial subscriptions for nginx are available on:

* http://nginx.com/products/


----------------------------------------------------------------------

  Verifying  : 1:nginx-1.10.2-1.el7.ngx.x86_64                                                                                        1/1 


Installed:

  nginx.x86_64 1:1.10.2-1.el7.ngx                                                                                                         


Complete!




[root@localhost yum.repos.d]# service nginx start

Redirecting to /bin/systemctl start  nginx.service






2. MariaDb 10 설치



[root@localhost yum.repos.d]# vi /etc/yum.repo.d/MariaDB.repo



[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.2/rhel7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1




[root@localhost yum.repos.d]# yum install -y mariadb mariadb-server




[root@localhost etc]# systemctl enable mariadb

[root@localhost etc]# systemctl start mariadb




설정파일 변경


2.1 /etc/my.cnf.d/mysql-clients.cnf


[mysql] 아래에

default-character-set=utf8 추가


[root@localhost etc]# vi /etc/my.cnf.d/mysql-clients.cnf 


[mysql]


default-character-set=utf8





2. /etc/my.cnf.d/server.cnf


[mysqld] 아래에 아래와 같이 추가하고

[mysqld_safe] 를 만들고 아래와 같이 추가


[root@localhost etc]# vi /etc/my.cnf.d/server.cnf 



[mysqld]



max_connections = 512

wait_timeout = 30

character-set-server = utf8



datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock


symbolic-links=0



[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid






3. 기본 보안 설정 


root 비밀번호 설정 등


Enter current password for root (enter for none): 그냥엔터(아직 root 비번을 설정하지 않았으므로) 



새로운 루트비밀번호 설정


Set 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] Y

 ... Success!


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!




재시작

[root@localhost run]# systemctl restart mariadb.service



[root@localhost run]# systemctl status mariadb.service

mariadb.service - MariaDB database server

   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)

  Drop-In: /etc/systemd/system/mariadb.service.d

           └─migrated-from-my.cnf-settings.conf

   Active: active (running) since 목 2017-01-05 03:12:14 KST; 19min ago

  Process: 1230 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

  Process: 1163 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)

  Process: 1160 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

 Main PID: 1198 (mysqld)

   Status: "Taking your SQL requests now..."

   CGroup: /system.slice/mariadb.service

           └─1198 /usr/sbin/mysqld


 1월 05 03:12:14 localhost.localdomain mysqld[1198]: 2017-01-05  3:12:14 139739293591744 [Note] InnoDB: 32 non-redo rollback seg...ctive.

 1월 05 03:12:14 localhost.localdomain mysqld[1198]: 2017-01-05  3:12:14 139739293591744 [Note] InnoDB: Waiting for purge to start

 1월 05 03:12:14 localhost.localdomain mysqld[1198]: 2017-01-05  3:12:14 139739293591744 [Note] InnoDB: 5.7.14 started; log sequ...226662

 1월 05 03:12:14 localhost.localdomain mysqld[1198]: 2017-01-05  3:12:14 139738111538944 [Note] InnoDB: Loading buffer pool(s) f...r_pool

 1월 05 03:12:14 localhost.localdomain mysqld[1198]: 2017-01-05  3:12:14 139739293591744 [Note] Plugin 'FEEDBACK' is disabled.

 1월 05 03:12:14 localhost.localdomain mysqld[1198]: 2017-01-05  3:12:14 139738111538944 [Note] InnoDB: Buffer pool(s) load comp...:12:14

 1월 05 03:12:14 localhost.localdomain mysqld[1198]: 2017-01-05  3:12:14 139739293591744 [Note] Server socket created on IP: '::'.

 1월 05 03:12:14 localhost.localdomain mysqld[1198]: 2017-01-05  3:12:14 139739293591744 [Note] /usr/sbin/mysqld: ready for connections.

 1월 05 03:12:14 localhost.localdomain mysqld[1198]: Version: '10.2.3-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306 ...Server

 1월 05 03:12:14 localhost.localdomain systemd[1]: Started MariaDB database server.

Hint: Some lines were ellipsized, use -l to show in full.


접속해 본다.

[root@localhost run]# mysql -u root -p mysql

Enter password: 

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A


Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.2.3-MariaDB MariaDB Server


Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [mysql]> 

MariaDB [mysql]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)


MariaDB [mysql]> 


캐릭터셋 확인

MariaDB [mysql]> show variables like 'char%';

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client     | utf8                       |

| character_set_connection | utf8                       |

| character_set_database   | utf8                       |

| character_set_filesystem | binary                     |

| character_set_results    | utf8                       |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)


MariaDB [mysql]> 



wait_timeout

MariaDB [mysql]> show global variables like 'wait%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| wait_timeout  | 30    |

+---------------+-------+

1 row in set (0.00 sec)


MariaDB [mysql]> 





3. PHP-FPM 7.0 설치

#yum update
#rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
#rpm -Uvh https://mirror.webtatic.com/yum/el7/webtatic-release.rpm


[root@localhost src]# yum update

Loaded plugins: fastestmirror, replace

Loading mirror speeds from cached hostfile

 * base: mirror.oasis.onnetcorp.com

 * epel: mirror.premi.st

 * extras: mirror.oasis.onnetcorp.com

 * updates: mirror.oasis.onnetcorp.com

 * webtatic: sp.repo.webtatic.com

No packages marked for update



[root@localhost src]# rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm(을)를 복구합니다

준비 중...                         ################################# [100%]

epel-release-7-8.noarch 패키지는 이미 설치되어 있습니다


https://mirror.webtatic.com/yum/el7/webtatic-release.rpm(을)를 복구합니다

준비 중...                         ################################# [100%]

webtatic-release-7-3.noarch 패키지는 이미 설치되어 있습니다

[root@localhost src]# 




php70w-fpm php70w-opcache 설치

[root@localhost src]# yum install php70w-fpm php70w-opcache



추가 설치

[root@localhost src]# yum install php70w-mysql php70w-gd php70w-curl php70w-mbstring php70w-mcrypt php70w-gettext php70w-pear





설치 확인

[root@localhost src]# php -v

PHP 7.0.14 (cli) (built: Dec 10 2016 11:35:27) ( NTS )

Copyright (c) 1997-2016 The PHP Group

Zend Engine v3.0.0, Copyright (c) 1998-2016 Zend Technologies

    with Zend OPcache v7.0.14, Copyright (c) 1999-2016, by Zend Technologies

[root@localhost src]# 



설정파일 변경

/etc/php.ini 파일을 열어서 아래 항목들 변경

cgi.fix_pathinfo = 0
allow_url_fopen = Off
expose_php = Off
display_errors = Off
upload_max_filesize = 10M
date.timezone = Asia/Seoul

/etc/php-fpm.d/www.conf 열어서 아래 항목들 변경

user = nginx
group = nginx
listen.owner = nginx 
listen.group = nginx 
listen.mode = 0664
listen = /var/run/php70w-fpm.sock






시작

[root@localhost php-fpm.d]# systemctl start php-fpm



확인

[root@localhost run]# ls /var/run/php70*

/var/run/php70w-fpm.sock

[root@localhost run]# 


[root@localhost php-fpm.d]# systemctl status php-fpm

php-fpm.service - The PHP FastCGI Process Manager

   Loaded: loaded (/usr/lib/systemd/system/php-fpm.service; disabled; vendor preset: disabled)

   Active: active (running) since 목 2017-01-05 03:32:06 KST; 20s ago

 Main PID: 11345 (php-fpm)

   Status: "Processes active: 0, idle: 5, Requests: 0, slow: 0, Traffic: 0req/sec"

   CGroup: /system.slice/php-fpm.service

           ├─11345 php-fpm: master process (/etc/php-fpm.conf)

           ├─11346 php-fpm: pool www

           ├─11347 php-fpm: pool www

           ├─11348 php-fpm: pool www

           ├─11349 php-fpm: pool www

           └─11350 php-fpm: pool www


 1월 05 03:32:05 localhost.localdomain systemd[1]: Starting The PHP FastCGI Process Manager...

 1월 05 03:32:06 localhost.localdomain systemd[1]: Started The PHP FastCGI Process Manager.

[root@localhost php-fpm.d]# 




4. nginx-php 연동

/etc/nginx/conf.d/default.conf 를 열어서 아래와 같이 변경

server {

    listen       80;

    server_name  192.168.33.252;


    #charset koi8-r;

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



    #location / {

        root   /usr/share/nginx/html;


        index  index.php index.html index.htm;

    #}


    location / {

        try_files $uri $uri/ =404;

    }



    #error_page  404              /404.html;


    # redirect server error pages to the static page /50x.html

    #

    error_page   500 502 503 504  /50x.html;

    location = /50x.html {

        root   /usr/share/nginx/html;

    }


    # proxy the PHP scripts to Apache listening on 127.0.0.1:80

    #

    #location ~ \.php$ {

    #    proxy_pass   http://127.0.0.1;

    #}


    # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000

    #

    location ~ \.php$ {

    #    root           html;

    #    fastcgi_pass   192.168.33.252:9000;

        try_files $uri =404;

        fastcgi_split_path_info ^(.+\.php)(/.+)$;

        fastcgi_pass   unix:/var/run/php70w-fpm.sock;

        fastcgi_index  index.php;

        fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;

        include        fastcgi_params;

    }


    # deny access to .htaccess files, if Apache's document root

    # concurs with nginx's one

    #

    #location ~ /\.ht {

    #    deny  all;

    #}

}





phpinfo()  테스트

/usr/share/nginx/html 에 index.php 파일을 만든다.

내용은
<?php

phpinfo()


?>





아래와 같이 나온다.



블로그 이미지

엘로드넷

댓글을 달아 주세요



SELECT 
concat(table_schema,'.',table_name), 
concat(round(table_rows/1000000,2),'M') rows, 
concat(round(data_length/(1024*1024*1024),2),'G') DATA, 
concat(round(index_length/(1024*1024*1024),2),'G') idx, 
concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size, 
round(index_length/data_length,2) idxfrac 
FROM information_schema.TABLES
where table_schema='데이터베이스명'
order by data desc;




블로그 이미지

엘로드넷

댓글을 달아 주세요

1. 디비툴이 있다면 쿼리편집기에서 아래 명령어를 쿼리날린다.



  SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 

  FROM INFORMATION_SCHEMA.TABLES 

  WHERE TABLE_NAME LIKE 'prefix_테이블명%' 

  AND TABLE_SCHEMA = '데이터베이스명';



테이블이 많다면 아래와 같이 결과가 나온다.


DROP TABLE 데이터베이스명.prefix_테이블명xx ;

DROP TABLE 데이터베이스명.prefix_테이블명xx ;

DROP TABLE 데이터베이스명.prefix_테이블명xx ;

DROP TABLE 데이터베이스명.prefix_테이블명xx ;

DROP TABLE 데이터베이스명.prefix_테이블명xx ;



검색결과를 복사해서 다시한번 쿼리 편집기에 넣어 쿼리를 날린다.





2. 쿼리결과를 파일로 저장해야할 경우


SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 

FROM INFORMATION_SCHEMA.TABLES 

WHERE TABLE_NAME LIKE 'prefix_테이블명%' 

AND TABLE_SCHEMA = '데이터베이스명' 

INTO OUTFILE '/tmp/저장할파일명.sql';



아래와 같이,

MariaDB [mysql]> SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 

    -> FROM INFORMATION_SCHEMA.TABLES 

    -> WHERE TABLE_NAME LIKE 'prefix_테이블명%' 

    -> AND TABLE_SCHEMA = '테이터베이스명' 

    -> INTO OUTFILE '/tmp/저장할파일명.sql';



그 후,


MariaDB [mysql]> source /tmp/저장할파일명.sql



삭제가 될 것이다.



블로그 이미지

엘로드넷

댓글을 달아 주세요

1. upgrade 스크립트 파일을 만든다.



ELLORDNET-MPR:~ ellord$ vi upgrademysql.sh



#!/bin/sh


wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.16-osx10.11-x86_64.tar.gz

tar xfvz mysql-5.7.16-osx10.11-x86_64.tar.gz



echo "stopping mamp"

sudo /Applications/MAMP/bin/stop.sh

sudo killall httpd mysqld


echo "creating backup"

sudo rsync -arv --progress /Applications/MAMP ~/Desktop/MAMP-Backup


echo "copy bin"

sudo rsync -arv --progress mysql-5.7.*/bin/* /Applications/MAMP/Library/bin/ --exclude=mysqld_multi --exclude=mysqld_safe


echo "copy share"

sudo rsync -arv --progress mysql-5.7.*/share/* /Applications/MAMP/Library/share/


echo "fixing access (workaround)"

sudo chmod -R o+rw  /Applications/MAMP/db/mysql/

sudo chmod -R o+rw  /Applications/MAMP/tmp/mysql/


echo "starting mamp"

sudo /Applications/MAMP/bin/start.sh


echo "migrate to new version"

sudo chmod -R 777 /Applications/MAMP/db/mysql/

/Applications/MAMP/Library/bin/mysql_upgrade --user=root --password=root --host=localhost --port=3306



2. 스크립트를 실행한다.


ELLORDNET-MPR:~ ellord$ ./upgrademysql.sh



-2016-11-17 10:22:55--  http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.16-osx10.11-x86_64.tar.gz

Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11

Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:80... connected.

HTTP request sent, awaiting response... 302 Found

Location: http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.16-osx10.11-x86_64.tar.gz [following]

--2016-11-17 10:22:56--  http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.16-osx10.11-x86_64.tar.gz

Resolving cdn.mysql.com (cdn.mysql.com)... 104.76.99.248

Connecting to cdn.mysql.com (cdn.mysql.com)|104.76.99.248|:80... connected.

HTTP request sent, awaiting response... 200 OK

Length: 331502302 (316M) [application/x-tar-gz]

Saving to: ‘mysql-5.7.16-osx10.11-x86_64.tar.gz.2’


 9% [========>                                                                                        ] 32,386,761  4.60MB/s  eta 64s    



아래와 같이 비밀번호를 입력하라고 하면.


stopping mamp

Password:



맥의 비밀번호를 입력해 준다.




마지막에 아래와 같은 에러가 나면.


migrate to new version

chmod: /Applications/MAMP/db/mysql/: No such file or directory

mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.

mysql_upgrade: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) while connecting to the MySQL server

Upgrade process encountered error and will not continue.

ELLORDNET-MPR:Documents ellord$ 161117 10:24:48 mysqld_safe Starting mysqld daemon with databases from /Applications/MAMP/db/mysql56

161117 10:24:49 mysqld_safe mysqld from pid file /Applications/MAMP/tmp/mysql/mysql.pid ended



mysql_upgrade 명령을 수동으로 실행한다.


ELLORDNET-MPR:bin ellord$ cd /Application/MAMP/Library/bin

ELLORDNET-MPR:bin ellord$ ./mysql_upgrade -u root 

mysql_upgrade: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) while connecting to the MySQL server

Upgrade process encountered error and will not continue.

ELLORDNET-MPR:bin ellord$ ./mysql_upgrade -u root --password=didckswn

mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

mysql_upgrade: [ERROR] 1146: Table 'mysql.innodb_table_stats' doesn't exist



그래도 에러가 난다면.



mysql 접속.


ELLORDNET-MPR:bin ellord$ ./mysql -u root -p mysql

Enter password: 



아래 5개 테이블 삭제.


innodb_index_stats
innodb_table_stats
slave_master_info
slave_relay_log_info
slave_worker_info


mysql> show tables;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| columns_priv              |

| db                        |

| engine_cost               |

| event                     |

| func                      |

| general_log               |

| gtid_executed             |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| innodb_index_stats        |

| innodb_table_stats        |

| ndb_binlog_index          |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| server_cost               |

| servers                   |

| slave_master_info         |

| slave_relay_log_info      |

| slave_worker_info         |

| slow_log                  |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

31 rows in set (0.01 sec)


mysql> drop table innodb_index_stats;



아래 5개 파일도 삭제.

innodb_index_stats.ibd
innodb_table_stats.ibd
slave_master_info.ibd
slave_relay_log_info.ibd
slave_worker_info.ibd

ELLORDNET-MPR:mysql ellord$ pwd

/Applications/MAMP/db/mysql56/mysql

ELLORDNET-MPR:mysql ellord$ rm -rf innodb_index_stats.ibd



5개 테이블 새로 만듬.


mysql> 


CREATE TABLE `innodb_index_stats` (

  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `stat_value` bigint(20) unsigned NOT NULL,

  `sample_size` bigint(20) unsigned DEFAULT NULL,

  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;


CREATE TABLE `innodb_table_stats` (

  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `n_rows` bigint(20) unsigned NOT NULL,

  `clustered_index_size` bigint(20) unsigned NOT NULL,

  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,

  PRIMARY KEY (`database_name`,`table_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;


CREATE TABLE `slave_master_info` (

  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',

  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',

  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',

  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',

  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',

  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',

  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',

  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',

  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',

  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',

  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',

  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',

  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',

  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',

  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',

  `Heartbeat` float NOT NULL,

  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',

  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',

  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',

  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',

  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',

  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',

  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',

  PRIMARY KEY (`Host`,`Port`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';


CREATE TABLE `slave_relay_log_info` (

  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',

  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',

  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',

  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',

  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',

  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',

  `Number_of_workers` int(10) unsigned NOT NULL,

  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',

  PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';


CREATE TABLE `slave_worker_info` (

  `Id` int(10) unsigned NOT NULL,

  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Relay_log_pos` bigint(20) unsigned NOT NULL,

  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Master_log_pos` bigint(20) unsigned NOT NULL,

  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,

  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,

  `Checkpoint_seqno` int(10) unsigned NOT NULL,

  `Checkpoint_group_size` int(10) unsigned NOT NULL,

  `Checkpoint_group_bitmap` blob NOT NULL,

  PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';






이제 다시 mysql_upgrade 를 시도해 본다.


ELLORDNET-MPR:bin ellord$ ./mysql_upgrade --user=root --password

Enter password:

mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Checking system database.

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.engine_cost                                  OK

mysql.event                                        OK

mysql.func                                         OK

mysql.general_log                                  OK

mysql.gtid_executed                                OK


sys.sys_config                                     OK

Upgrade process completed successfully.

Checking if update is needed.



위와 같이 정상적으로 업그레이드 되었다.


접속해 본다.

ELLORDNET-MPR:bin ellord$ ./mysql -u root -p mysql

Enter password: 

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.16 MySQL Community Server (GPL)


Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.


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> 

mysql> select @@version;

+-----------+

| @@version |

+-----------+

| 5.7.16    |

+-----------+

1 row in set (0.00 sec)


mysql> 




5.7.16 으로 업데이트 되었다.

















블로그 이미지

엘로드넷

댓글을 달아 주세요

MySQL CAST

MySQL, MariaDB 2016. 5. 12. 13:43

mysql 형변환 : CAST 함수 사용.

ex)
mysql> select cast('1234' as ​signed) as num from tb_sample;


signed 대신에 쓸 수 있는 것들은 아래와 같다.


binary
char
date
datetime
signed
time
unsigned


블로그 이미지

엘로드넷

댓글을 달아 주세요

mysqldump -u root -p 데이터베이스명 테이블명 --where="조건문" --no-create-info > test.sql                                 



--no-create-info 를 생략하면 백업시 테이블을 삭제하고 복구하니 주의하기 바람


'MySQL, MariaDB' 카테고리의 다른 글

MAMP mysql upgrade to 5.7.16 osx  (0) 2016.11.17
MySQL CAST  (0) 2016.05.12
특정 테이블 데이터 백업 및 복구  (0) 2016.03.19
order by field ()  (0) 2016.03.14
MySQL 테이블 사이즈 조회  (0) 2016.01.13
MySQL unauthenticated user login state 계속 발생시  (0) 2016.01.13
블로그 이미지

엘로드넷

댓글을 달아 주세요