java, c# , linux, program devloper: Mysql 카테고리 글 목록
http://blog1.phps.kr/
blog.visualp.com
2021-05-29T08:24:41+09:00
Textcube 1.10.7 : Tempo primo
mariadb, mysql unique index 설정시 소문자 대문자 구분 되도록 컬럼 설정
java, tomcat, c#
http://blog1.phps.kr/748
2018-09-28T18:12:16+09:00
2018-09-28T18:12:13+09:00
ALTER TABLE `tb_webstat_info`<br /> CHANGE COLUMN `wi_code` `wi_code` VARCHAR(10) <strong><span style="color: #ff2600;">binary</span></strong> DEFAULT NULL COMMENT '모바일명세표_코드' AFTER `wi_rnum`;<br /><br />VARCHAR(10) <strong><span style="color: #ff2600;">binary</span></strong> 형식으로 설정을 해주던지 <br />문자열 타입을 utf8_bin;으로 설정 한다<p><strong><a href="http://blog1.phps.kr/748?commentInput=true#entry748WriteComment">댓글 쓰기</a></strong></p>
galera cluster recover , 장애복구
java, tomcat, c#
http://blog1.phps.kr/740
2018-04-03T11:06:55+09:00
2018-04-03T11:06:52+09:00
mariadb galera cluster 설치는 유수한 자료들로 구글에 검색 해보면 <br />찾을 수 있습니다.<br />하지만 실제 운영을 하려고 하면 , DB에 발생된 장애 처리를 해야 합니다.<br />상황에 따라서 발생할 수 있는 장애들을 <br />아래 percona.com 자료에서 설명 하고 있습니다.<br />운영시 발생할 수 있는 상황에 대해서 잘 설명 해주고 있습니다.<br />우선 서버가 강제적으로 다운 되면 강제 다운 시켜야 될 경우 또는<br />테스트중 정전으로 서버 시작이 안될 경우<br />난감한 문제들이 발생 합니다.<br />우선 확인 해야 될 것이 갑작스런 정전인 경우<br /><br />[서버 강제종료 / 다운 후 시작이 안되는 경우]<br />grastate.dat<br />safe_to_bootstrap: 1 // 0으로 변경 한다.<br />위와 같이 변경을 하면 어느 정도 해결이 됩니다. <br /><br />기타 장애 복구 참고 사이트<br />
<p>[원문] <br />https://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/<br /><br />상황별 가레나클러스터 복구 방법<br /><br /></p>
<p><strong>Galera</strong> replication for MySQL brings not only the new, great features to our ecosystem, but also introduces completely new maintenance techniques. Are you concerned about adding such new complexity to your MySQL environment? Perhaps that concern is unnecessarily.</p>
<p>I am going to present here some simple tips that hopefully will let fresh Galera users prevent headaches when there is the need to recover part or a whole cluster in certain situations. I used <a href="https://www.percona.com/software/percona-xtradb-cluster" target="_blank">Percona XtraDB Cluster</a> (project based on Percona Server and Galera library + MySQL extensions from Codership) to prepare this post, but most if not all of the scenarios should also apply to any solution based on MySQL+Galera tandem you actually chose, whether these are <a href="http://galeracluster.com/downloads/#downloads" target="_blank" rel="nofollow">binaries from Codership</a>, MariaDB Galera Cluster or maybe your own builds.</p>
<p>Unlike standard MySQL replication, a PXC cluster acts like one logical entity, which takes care about each node status and consistency as well as cluster status as a whole. This allows to maintain much better data integrity then you may expect from traditional asynchronous replication while allowing safe writes on multiple nodes in the same time. This is though for the price of more possible scenarios where database service will be stopped with no node being able to serve requests.</p>
<p>Lets assume the simplest case cluster of nodes <strong>A</strong>, <strong>B</strong> and <strong>C</strong> and few possible scenarios where some or all nodes are out of service. What may happen and what we have to do, to bring them (or whole cluster) back up.</p>
<h2>Scenario 1</h2>
<p><strong><a href="https://www.percona.com/blog/wp-content/uploads/2014/08/g1.png" target="_blank" data-slb-active="1" data-slb-asset="1903420509" data-slb-internal="0" data-slb-group="25217"><img class="alignright wp-image-25349 size-full" src="https://www.percona.com/blog/wp-content/uploads/2014/08/g1.png" alt="g1" width="215" height="164" /></a>Node A is gracefully stopped</strong>. Likely for the purpose of maintenance, configuration change, etc.<br /> In this case the other nodes receive “good bye” message from that node, hence the cluster size is reduced and some properties like quorum calculation or auto increment are automatically changed. Once we start the A node again, it will join the cluster based on it’s wsrep_cluster_address setting in my.cnf. This process is much different from normal replication – the joiner node won’t serve any requests until it is again fully synchronized with the cluster, so connecting to it’s peers isn’t enough, state transfer must succeed first. If the writeset cache (<a href="https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-provider-index.html#gcache.size" target="_blank"><tt>gcache.size</tt></a>), on nodes B and/or C has still all the transactions there were executed during the time this node was down, joining will be possible via (usually fast and light) <a href="http://galeracluster.com/documentation-webpages/statetransfer.html#incremental-state-transfer-ist" target="_blank" rel="nofollow">IST</a>. Otherwise, full <a href="https://www.percona.com/doc/percona-xtradb-cluster/5.6/manual/state_snapshot_transfer.html" target="_blank">SST</a> will be needed, which in fact is full binary data snapshot copy. Hence it may be important here to determine the best donor, as shown in<a href="https://www.percona.com/blog/2014/01/08/finding-good-ist-donor-percona-xtradb-cluster-5-6/" target="_blank"> this article</a>. If IST is impossible due to missing transactions in donor’s gcache, the fallback decision is made by the donor and SST is started automatically instead.</p>
<h2>Scenario 2</h2>
<p><strong><a href="https://www.percona.com/blog/wp-content/uploads/2014/08/g2.png" target="_blank" data-slb-active="1" data-slb-asset="1132265727" data-slb-internal="0" data-slb-group="25217"><img class="size-full wp-image-25348 alignright" src="https://www.percona.com/blog/wp-content/uploads/2014/08/g2.png" alt="g2" width="211" height="160" /></a>Nodes A and B are gracefully stopped.</strong> Similar to previous case, cluster size is reduced to 1, hence even the single remaining node C forms a <a href="http://galeracluster.com/documentation-webpages/weightedquorum.html#primary-component" target="_blank" rel="nofollow">primary component</a> and is serving client requests. To get the nodes back into the cluster, you just need to start them. However, the node C will be switched to “Donor/Desynced” state as it will have to provide state transfer to at least first joining node. It is still possible to read/write to it during that process, but it may be much slower, depending how large state transfers it needs to send. Also some load balancers may consider the donor node as not operational and remove it from the pool. So it is best to avoid situation when only one node is up.</p>
<p>Note though, if you restart A and then B in that order, you may want to make sure B won’t use A as state transfer donor, as A may not have all the needed writesets in it’s gcache. So just specify the C node as donor this way (“nodeC” name is the one you specify with wsrep_node_name variable):</p>
<blockquote>
<p><code>service mysql start --wsrep_sst_donor=nodeC</code></p>
</blockquote>
<h2>Scenario 3</h2>
<p><strong><a href="https://www.percona.com/blog/wp-content/uploads/2014/08/g3.png" target="_blank" data-slb-active="1" data-slb-asset="59506577" data-slb-internal="0" data-slb-group="25217"><img class="size-full wp-image-25347 alignright" src="https://www.percona.com/blog/wp-content/uploads/2014/08/g3.png" alt="g3" width="211" height="157" /></a>All three nodes are gracefully stopped.</strong> Cluster is deformed. In this case, the problem is how to initialize it again. Here, it is important to know, that during clean shutdown, a PXC node writes it’s last executed position into the <tt>grastate.dat</tt> file. By comparing the seqno number inside, you will see which node is the most advanced one (most likely the last one stopped). Cluster must be bootstrapped using this node, otherwise nodes that had more advanced position will have to perform full SST to join cluster initialized from the less advanced one (and some transactions will be lost). To bootstrap the first node, invoke the startup script like this:</p>
<blockquote>
<p><code>/etc/init.d/mysql bootstrap-pxc</code></p>
</blockquote>
<p>or</p>
<blockquote>
<p><code>service mysql bootstrap-pxc</code></p>
</blockquote>
<p>or</p>
<blockquote>
<p><code>service mysql start --wsrep_new_cluster</code></p>
</blockquote>
<p>or</p>
<blockquote>
<p><code>service mysql start --wsrep-cluster-address="gcomm://"</code></p>
</blockquote>
<p>or in packages using <a href="http://www.freedesktop.org/wiki/Software/systemd/" target="_blank" rel="nofollow">systemd</a> service manager (Centos7 at the moment):</p>
<blockquote>
<p><code>systemctl start mysql@bootstrap.service</code></p>
</blockquote>
<p>In older PXC versions, to bootstrap cluster, you had to edit my.cnf and replace previous wsrep_cluster_address line with empty value like this: wsrep_cluster_address=gcomm:// and start mysql normally. More details to be found <a href="http://galeracluster.com/documentation-webpages/restartingcluster.html" target="_blank" rel="nofollow">here</a>.</p>
<p>Please note that even if you bootstrap from the most advanced node, so the other nodes have lower sequence number, they will have to still join via full-SST because the Galera Cache is not retained on restart. For that reason, it is recommended to stop writes to the cluster *before* it’s full shutdown, so that all nodes stop in the same position. Edit: This changes since Galera 3.19 thanks to <a href="http://galeracluster.com/documentation-webpages/galeraparameters.html#gcache-recover" target="_blank" rel="nofollow">gcache-recover</a> option.</p>
<h2><a href="https://www.percona.com/blog/wp-content/uploads/2014/08/g4.png" target="_blank" data-slb-active="1" data-slb-asset="217044113" data-slb-internal="0" data-slb-group="25217"><img class="size-full wp-image-25346 alignright" src="https://www.percona.com/blog/wp-content/uploads/2014/08/g4.png" alt="g4" width="232" height="167" /></a>Scenario 4</h2>
<p><strong>Node A disappears from the cluster.</strong> By disappear I mean power outage, hardware failure, kernel panic, mysqld crash, kill -9 on mysqld pid, OOMkiller, etc. Two remaining nodes notice the connection to A node is down and will be trying to re-connect to it. After some timeouts, both agree that node A is really down and remove it “officially” from the cluster. Quorum is saved ( 2 out of 3 nodes are up), so no service disruption happens. After restarting, A will join automatically the same way as in scenario 1.</p>
<h2>Scenario 5</h2>
<p><strong><a href="https://www.percona.com/blog/wp-content/uploads/2014/08/g51.png" target="_blank" data-slb-active="1" data-slb-asset="2004883596" data-slb-internal="0" data-slb-group="25217"><img class="wp-image-25364 size-full alignright" src="https://www.percona.com/blog/wp-content/uploads/2014/08/g51.png" alt="" width="244" height="171" /></a>Nodes A and B disappear.</strong> The node C is not able to form the quorum alone, so the cluster is switching into a non-primary mode, in which MySQL refuses to serve any SQL query. In this state, mysqld process on C will be still running, you can connect to it, but any statement related to data fails with:</p>
<blockquote>
<p><code>mysql> select * from test.t1;<br /> ERROR 1047 (08S01): Unknown command</code></p>
</blockquote>
<p>Actually reads will be possible for a moment until C decides that it cannot reach A and B, but immediately no new writes will be allowed thanks to the <a href="http://galeracluster.com/documentation-webpages/certificationbasedreplication.html" target="_blank" rel="nofollow">certification based replication</a> in Galera. This is what we are going to see in the remaining node’s log:</p>
<blockquote>
<p><code>140814 0:42:13 [Note] WSREP: commit failed for reason: 3<br /> 140814 0:42:13 [Note] WSREP: conflict state: 0<br /> 140814 0:42:13 [Note] WSREP: cluster conflict due to certification failure for threads:<br /> 140814 0:42:13 [Note] WSREP: Victim thread:<br /> THD: 7, mode: local, state: executing, conflict: cert failure, seqno: -1<br /> SQL: insert into t values (1)</code></p>
</blockquote>
<p>The single node C is then waiting for it’s peers to show up again, and in some cases if that happens, like when there was network outage and those nodes were up all the time, the cluster will be formed again automatically. Also if the nodes B and C were just network-severed from the first node, but they can still reach each other, they will keep functioning as they still form the quorum. If A and B were crashed ( due to data inconsistency, bug, etc. ) or off due to power outage, you need to do manual action to enable <a href="http://galeracluster.com/documentation-webpages/weightedquorum.html#primary-component" target="_blank" rel="nofollow">primary component</a> on the C node, before you can bring A and B back. This way, we tell the C node “Hey, you can now form a new cluster alone, forget A and B!”. The command to do this is:</p>
<blockquote>
<p><code>SET GLOBAL wsrep_provider_options='pc.bootstrap=true';</code></p>
</blockquote>
<p>However, you should double check in order to <strong>be very sure the other nodes are really down</strong> before doing that! Otherwise, you will most likely end up with two clusters having different data.</p>
<h2>Scenario 6</h2>
<p><strong><a href="https://www.percona.com/blog/wp-content/uploads/2014/08/g6.png" target="_blank" data-slb-active="1" data-slb-asset="1356569175" data-slb-internal="0" data-slb-group="25217"><img class="size-full wp-image-25344 alignright" src="https://www.percona.com/blog/wp-content/uploads/2014/08/g6.png" alt="g6" width="244" height="186" /></a>All nodes went down without proper shutdown procedure.</strong> Such situation may happen in case of datacenter power failure, hitting some MySQL or Galera bug leading to crash on all nodes, but also as a result of data consistency being compromised where cluster detects that each node has different data. In each of those cases, the grastate.dat file is not updated and does not contain valid sequence number (seqno). It may look like this:</p>
<blockquote>
<p><code>cat /var/lib/mysql/grastate.dat<br /> # GALERA saved state<br /> version: 2.1<br /> uuid: 220dcdcb-1629-11e4-add3-aec059ad3734<br /> seqno: -1<br /> cert_index:</code></p>
</blockquote>
<p>In this case, we are not sure if all nodes were consistent with each other, hence it is crucial to find the most advanced one in order to boostrap the cluster using it. Before starting mysql daemon on any node, you have to extract the last sequence number by checking it’s transactional state. You can do it this way:</p>
<blockquote>
<p><code>[root@percona3 ~]# <strong>mysqld_safe --wsrep-recover</strong><br /> 140821 15:57:15 mysqld_safe Logging to '/var/lib/mysql/percona3_error.log'.<br /> 140821 15:57:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql<br /> 140821 15:57:15 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.6bUIqM' --pid-file='/var/lib/mysql/percona3-recover.pid'<br /> 140821 15:57:17 mysqld_safe WSREP: Recovered position 4b83bbe6-28bb-11e4-a885-4fc539d5eb6a:2<br /> 140821 15:57:19 mysqld_safe mysqld from pid file /var/lib/mysql/percona3.pid ended</code></p>
</blockquote>
<p>So the last committed transaction sequence number on this node was 2. Now you just need to bootstrap from the latest node first and then start the others.</p>
<p>However, the above procedure won’t be needed in the recent Galera versions (3.6+?), available since <a href="https://www.percona.com/doc/percona-xtradb-cluster/5.6/release-notes/Percona-XtraDB-Cluster-5.6.19-25.6.html" target="_blank">PXC 5.6.19</a>. There is a new option – <strong><a title="pc.recovery" href="https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-provider-index.html#pc.recovery" target="_blank"><code>pc.recovery</code></a></strong> (enabled by default), which saves the cluster state into a file named <code>gvwstate.dat</code> on each member node. As the variable name says (pc – primary component), it saves only a cluster being in PRIMARY state. An example content of that file may look like this:</p>
<blockquote>
<p><code><strong>cat /var/lib/mysql/gvwstate.dat</strong><br /> my_uuid: 76de8ad9-2aac-11e4-8089-d27fd06893b9<br /> #vwbeg<br /> view_id: 3 6c821ecc-2aac-11e4-85a5-56fe513c651f 3<br /> bootstrap: 0<br /> member: 6c821ecc-2aac-11e4-85a5-56fe513c651f 0<br /> member: 6d80ec1b-2aac-11e4-8d1e-b2b2f6caf018 0<br /> member: 76de8ad9-2aac-11e4-8089-d27fd06893b9 0<br /> #vwend</code></p>
</blockquote>
<p>We can see three node cluster above with all members being up. Thanks to this new feature, in the case of power outage in our datacenter, after power is back, the nodes will read the last state on startup and will try to restore primary component once all the members again start to see each other. This makes the PXC cluster to automatically recover from being powered down without any manual intervention! In the logs we will see:</p>
<blockquote>
<p><code><span style="color: #0000ff;">140823 15:28:55 [Note] WSREP: restore pc from disk successfully</span><br /> (...)<br /> 140823 15:29:59 [Note] WSREP: declaring 6c821ecc at tcp://192.168.90.3:4567 stable<br /> 140823 15:29:59 [Note] WSREP: declaring 6d80ec1b at tcp://192.168.90.4:4567 stable<br /> 140823 15:29:59 [Warning] WSREP: no nodes coming from prim view, prim not possible<br /> 140823 15:29:59 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 2, memb_num = 3<br /> 140823 15:29:59 [Note] WSREP: Flow-control interval: [28, 28]<br /> 140823 15:29:59 [Note] WSREP: Received NON-PRIMARY.<br /> 140823 15:29:59 [Note] WSREP: New cluster view: global state: 4b83bbe6-28bb-11e4-a885-4fc539d5eb6a:11, view# -1: non-Primary, number of nodes: 3, my index: 2, protocol version -1<br /> 140823 15:29:59 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.<br /><span style="color: #0000ff;">140823 15:29:59 [Note] WSREP: promote to primary component</span><br /> 140823 15:29:59 [Note] WSREP: save pc into disk<br /> 140823 15:29:59 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = yes, my_idx = 2, memb_num = 3<br /> 140823 15:29:59 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.<br /> 140823 15:29:59 [Note] WSREP: clear restored view<br /> (...)<br /> 140823 15:29:59 [Note] WSREP: Bootstrapped primary 00000000-0000-0000-0000-000000000000 found: 3.<br /> 140823 15:29:59 [Note] WSREP: Quorum results:<br /> version = 3,<br /> component = PRIMARY,<br /> conf_id = -1,<br /> members = 3/3 (joined/total),<br /> act_id = 11,<br /> last_appl. = -1,<br /> protocols = 0/6/2 (gcs/repl/appl),<br /> group UUID = 4b83bbe6-28bb-11e4-a885-4fc539d5eb6a<br /> 140823 15:29:59 [Note] WSREP: Flow-control interval: [28, 28]<br /> 140823 15:29:59 [Note] WSREP: Restored state OPEN -> JOINED (11)<br /> 140823 15:29:59 [Note] WSREP: New cluster view: global state: 4b83bbe6-28bb-11e4-a885-4fc539d5eb6a:11, view# 0: Primary, number of nodes: 3, my index: 2, protocol version 2<br /> 140823 15:29:59 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.<br /> 140823 15:29:59 [Note] WSREP: REPL Protocols: 6 (3, 2)<br /> 140823 15:29:59 [Note] WSREP: Service thread queue flushed.<br /> 140823 15:29:59 [Note] WSREP: Assign initial position for certification: 11, protocol version: 3<br /> 140823 15:29:59 [Note] WSREP: Service thread queue flushed.<br /> 140823 15:29:59 [Note] WSREP: Member 1.0 (percona3) synced with group.<br /> 140823 15:29:59 [Note] WSREP: Member 2.0 (percona1) synced with group.<br /> 140823 15:29:59 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 11)<br /> 140823 15:29:59 [Note] WSREP: Synchronized with group, ready for connections</code></p>
</blockquote>
<h2>Scenario 7</h2>
<p><a href="https://www.percona.com/blog/wp-content/uploads/2014/08/g7.png" target="_blank" data-slb-active="1" data-slb-asset="265177414" data-slb-internal="0" data-slb-group="25217"><img class="alignright wp-image-25343 size-full" src="https://www.percona.com/blog/wp-content/uploads/2014/08/g7.png" alt="g7" width="508" height="167" /></a>Cluster lost it’s primary state due to <strong>split brain situation</strong>. For the purpose of this example, let’s assume we have the cluster formed from even number of nodes – six and three of them are in one location while another three in second location (datacenter) and network connectivity is broken between them. Of course the best practice is to avoid such topology: if you can’t have odd number of real nodes, at least you can use an additional <a href="http://galeracluster.com/documentation-webpages/arbitrator.html" target="_blank" rel="nofollow">arbitrator</a> (garbd) node or set higher <a href="https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-provider-index.html#pc.weight" target="_blank"><code>pc.weight</code></a> to some nodes. But when split brain happens any way, so none of the separated groups can maintain the quorum – all nodes must stop serving requests and both parts of the cluster are just continuously trying to re-connect. If you want to restore the service even before the network link is restored, you can make one of the groups primary again using the same command like in scenario 5:</p>
<blockquote>
<p><code>SET GLOBAL wsrep_provider_options='pc.bootstrap=true';</code></p>
</blockquote>
<p>After that, you are able to work on the manually restored part of the cluster, and the second half should be able to automatically re-join using <a href="http://galeracluster.com/documentation-webpages/statetransfer.html#incremental-state-transfer-ist" target="_blank" rel="nofollow">incremental state transfer</a> (IST) once the network link is restored. <strong>But beware:</strong> if you set the bootstrap option on both the separated parts, you will end up with two living cluster instances, with data likely diverging away from each other. Restoring network link in that case won’t make them to re-join until nodes are restarted and try to re-connect to members specified in configuration file. Then, as Galera replication model truly cares about data consistency – once the inconsistency will be detected, nodes that cannot execute row change statement due to different data – will perform emergency shutdown and the only way to bring them back to the cluster will be via full SST.</p>
<p>I hope I covered most of the possible failure scenarios of Galera-based clusters, and made the recovery procedures bit more clear.<br /><br /></p><p><strong><a href="http://blog1.phps.kr/740?commentInput=true#entry740WriteComment">댓글 쓰기</a></strong></p>
mysqlcheck , how to use mysqlcheck
java, tomcat, c#
http://blog1.phps.kr/739
2018-03-31T13:10:20+09:00
2018-03-31T13:10:17+09:00
<span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysqlcheck 유틸리티 사용법(analyze, optimize, repair)</span><br /><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysqlcheck 유틸리티 사용법</span><br /><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">cost 베이스 DB에서 DB전체를 한번에 analyze, optimize, repair 하기 위한 유틸리티 입니다.</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">파일위치 : mysql/bin</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">----------------------------------------------------</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">전체 체크 및 자동 복구(check and repair)</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysqlcheck -Aa --auto-repair -u root -p</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysqlcheck -Ao --auto-repair -u root -p</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">----------------------------------------------------</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">-A, --all-databases</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">-a, --analyze</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">-o, --optimize</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">-P 포트번호</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">----------------------------------------------------</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">특정 데이터베이스만 체크 및 자동 복구</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysqlcheck --auto-repair -u root -p 데이터베이스명</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">----------------------------------------------------</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">특정 테이블만 체크 및 자동 복구</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysqlcheck --auto-repair -u root -p 데이터베이스명 테이블명</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">----------------------------------------------------</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">명령어 수정하여 사용하기</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysqlrepair = mysqlcheck --repair</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysqlanalyze = mysqlcheck --analyze</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysqloptimize = mysqlcheck --optimize</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">----------------------------------------------------</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysql에 접속하여 개별적으로 하는 방법</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysql> use 데이터베이스명;</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysql> show tables;</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysql> check table 테이블명;</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysql> repair table 테이블명;</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysql> analyze table 테이블명;</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysql> optimize table 테이블명;</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">----------------------------------------------------</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">##########################</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">OPTIMIZED table script PHP</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">##########################</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">$db = 'database_name';</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">$tables = mysql_list_tables($db);</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">while (list($table)name) = mysql_fetch_array($tables)) {</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">$sql = "OPTIMIZE TABLE $table_name";</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysql_query($sql) or exit(mysql_error());</span><br /><span style="color: #333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: NanumGothic, 나눔고딕, ngttf, ngeot, dotum, 돋음, sans-serif; font-size: 11px; font-style: normal; font-weight: 400; word-spacing: 0px; float: none; display: inline !important; white-space: normal; orphans: 2; widows: 2; background-color: #ffffff; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">}</span><p><strong><a href="http://blog1.phps.kr/739?commentInput=true#entry739WriteComment">댓글 쓰기</a></strong></p>
mariadb 파티션 테이블 , 운영 mysql Maintenance of Partitions
java, tomcat, c#
http://blog1.phps.kr/731
2018-01-09T18:23:52+09:00
2018-01-09T18:23:50+09:00
<h3 class="title">METHOD<br /><a class="link" title="13.7.2.2 CHECK TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/check-table.html"><code class="literal">CHECK TABLE</code></a>, <a class="link" title="13.7.2.4 OPTIMIZE TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html"><code class="literal">OPTIMIZE TABLE</code></a>, <a class="link" title="13.7.2.1 ANALYZE TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html"><code class="literal">ANALYZE TABLE</code></a>, and <a class="link" title="13.7.2.5 REPAIR TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/repair-table.html"><code class="literal">REPAIR TABLE</code></a><br /><br />HOWTO<br /><br />ALTER TABLE tb_cycle_data CHECK PARTITION tb_cycle_data2013;<br /><br />ALTER TABLE {TABLE-NAME} {METHOD} PARTITION {PARTITION-TABLE-NAME};<br /><br /><br />22.3.4 Maintenance of Partitions</h3>
<a class="indexterm" name="idm140433615931680"></a><a class="indexterm" name="idm140433615930192"></a><a class="indexterm" name="idm140433615928704"></a><a class="indexterm" name="idm140433615927216"></a><a class="indexterm" name="idm140433615925728"></a><a class="indexterm" name="idm140433615924240"></a><a class="indexterm" name="idm140433615922752"></a><a class="indexterm" name="idm140433615921264"></a>
<p>A number of table and partition maintenance tasks can be carried out using SQL statements intended for such purposes on partitioned tables in MySQL 5.7.</p>
<p>Table maintenance of partitioned tables can be accomplished using the statements <a class="link" title="13.7.2.2 CHECK TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/check-table.html"><code class="literal">CHECK TABLE</code></a>, <a class="link" title="13.7.2.4 OPTIMIZE TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html"><code class="literal">OPTIMIZE TABLE</code></a>, <a class="link" title="13.7.2.1 ANALYZE TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html"><code class="literal">ANALYZE TABLE</code></a>, and <a class="link" title="13.7.2.5 REPAIR TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/repair-table.html"><code class="literal">REPAIR TABLE</code></a>, which are supported for partitioned tables.</p>
<p>You can use a number of extensions to <a class="link" title="13.1.8.1 ALTER TABLE Partition Operations" href="https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html"><code class="literal">ALTER TABLE</code></a> for performing operations of this type on one or more partitions directly, as described in the following list:</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc;">
<li class="listitem">
<p><strong>Rebuilding partitions. </strong> Rebuilds the partition; this has the same effect as dropping all records stored in the partition, then reinserting them. This can be useful for purposes of defragmentation.</p>
<p>Example:</p>
<div class="copytoclipboard-wrapper" style="position: relative;">
<div id="sa91702729" class="docs-select-all right" style="display: none;">
<div class="copy-help left" style="display: none;">Press CTRL+C to copy</div>
<div class="right"> </div>
</div>
<pre class="programlisting line-numbers one-line language-sql"><code class=" language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">REBUILD</span> <span class="token keyword">PARTITION</span> p0<span class="token punctuation">,</span> p1<span class="token punctuation">;</span></code></pre>
</div>
</li>
<li class="listitem">
<p><strong>Optimizing partitions. </strong> If you have deleted a large number of rows from a partition or if you have made many changes to a partitioned table with variable-length rows (that is, having <a class="link" title="11.4.1 The CHAR and VARCHAR Types" href="https://dev.mysql.com/doc/refman/5.7/en/char.html"><code class="literal">VARCHAR</code></a>, <a class="link" title="11.4.3 The BLOB and TEXT Types" href="https://dev.mysql.com/doc/refman/5.7/en/blob.html"><code class="literal">BLOB</code></a>, or <a class="link" title="11.4.3 The BLOB and TEXT Types" href="https://dev.mysql.com/doc/refman/5.7/en/blob.html"><code class="literal">TEXT</code></a> columns), you can use <a class="link" title="13.1.8.1 ALTER TABLE Partition Operations" href="https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html"><code class="literal">ALTER TABLE ... OPTIMIZE PARTITION</code></a> to reclaim any unused space and to defragment the partition data file.</p>
<p>Example:</p>
<div class="copytoclipboard-wrapper" style="position: relative;">
<div id="sa54948240" class="docs-select-all right" style="display: none;">
<div class="copy-help left" style="display: none;">Press CTRL+C to copy</div>
<div class="right"> </div>
</div>
<pre class="programlisting line-numbers one-line language-sql"><code class=" language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">OPTIMIZE</span> <span class="token keyword">PARTITION</span> p0<span class="token punctuation">,</span> p1<span class="token punctuation">;</span></code></pre>
</div>
<p>Using <code class="literal">OPTIMIZE PARTITION</code> on a given partition is equivalent to running <code class="literal">CHECK PARTITION</code>, <code class="literal">ANALYZE PARTITION</code>, and <code class="literal">REPAIR PARTITION</code> on that partition.</p>
<p>Some MySQL storage engines, including <a class="link" title="Chapter 14 The InnoDB Storage Engine" href="https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html"><code class="literal">InnoDB</code></a>, do not support per-partition optimization; in these cases, <a class="link" title="13.1.8.1 ALTER TABLE Partition Operations" href="https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html"><code class="literal">ALTER TABLE ... OPTIMIZE PARTITION</code></a> analyzes and rebuilds the entire table, and causes an appropriate warning to be issued. (Bug #11751825, Bug #42822) Use <code class="literal">ALTER TABLE ... REBUILD PARTITION</code> and <code class="literal">ALTER TABLE ... ANALYZE PARTITION</code> instead, to avoid this issue.</p>
</li>
<li class="listitem">
<p><strong>Analyzing partitions. </strong> This reads and stores the key distributions for partitions.</p>
<p>Example:</p>
<div class="copytoclipboard-wrapper" style="position: relative;">
<div id="sa80257983" class="docs-select-all right" style="display: none;">
<div class="copy-help left" style="display: none;">Press CTRL+C to copy</div>
<div class="right"> </div>
</div>
<pre class="programlisting line-numbers one-line language-sql"><code class=" language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ANALYZE</span> <span class="token keyword">PARTITION</span> p3<span class="token punctuation">;</span></code></pre>
</div>
</li>
<li class="listitem">
<p><strong>Repairing partitions. </strong> This repairs corrupted partitions.</p>
<p>Example:</p>
<div class="copytoclipboard-wrapper" style="position: relative;">
<div id="sa41775426" class="docs-select-all right">
<div class="copy-help left">Press CTRL+C to copy</div>
<div class="right"> </div>
</div>
<pre class="programlisting line-numbers one-line language-sql"><code class=" language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">REPAIR</span> <span class="token keyword">PARTITION</span> p0<span class="token punctuation">,</span>p1<span class="token punctuation">;</span></code></pre>
</div>
<p>Normally, <code class="literal">REPAIR PARTITION</code> fails when the partition contains duplicate key errors. In MySQL 5.7.2 and later, you can use <a class="link" title="13.1.8.1 ALTER TABLE Partition Operations" href="https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html"><code class="literal">ALTER IGNORE TABLE</code></a> with this option, in which case all rows that cannot be moved due to the presence of duplicate keys are removed from the partition (Bug #16900947).</p>
</li>
<li class="listitem">
<p><strong>Checking partitions. </strong> You can check partitions for errors in much the same way that you can use <code class="literal">CHECK TABLE</code> with nonpartitioned tables.</p>
<p>Example:</p>
<div class="copytoclipboard-wrapper" style="position: relative;">
<div id="sa90289882" class="docs-select-all right">
<div class="copy-help left">Press CTRL+C to copy</div>
<div class="right"> </div>
</div>
<pre class="programlisting line-numbers one-line language-sql"><code class=" language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> trb3 <span class="token keyword">CHECK</span> <span class="token keyword">PARTITION</span> p1<span class="token punctuation">;</span></code></pre>
</div>
<p>This command will tell you if the data or indexes in partition <code class="literal">p1</code> of table <code class="literal">t1</code> are corrupted. If this is the case, use <a class="link" title="13.1.8.1 ALTER TABLE Partition Operations" href="https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html"><code class="literal">ALTER TABLE ... REPAIR PARTITION</code></a> to repair the partition.</p>
<p>Normally, <code class="literal">CHECK PARTITION</code> fails when the partition contains duplicate key errors. In MySQL 5.7.2 and later, you can use <a class="link" title="13.1.8.1 ALTER TABLE Partition Operations" href="https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html"><code class="literal">ALTER IGNORE TABLE</code></a> with this option, in which case the statement returns the contents of each row in the partition where a duplicate key violation is found. Only the values for the columns in the partitioning expression for the table are reported. (Bug #16900947)</p>
</li>
</ul>
</div>
<p>Each of the statements in the list just shown also supports the keyword <code class="literal">ALL</code> in place of the list of partition names. Using <code class="literal">ALL</code> causes the statement to act on all partitions in the table.</p>
<p>The use of <a class="link" title="4.5.3 mysqlcheck — A Table Maintenance Program" href="https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html"><span class="command"><strong>mysqlcheck</strong></span></a> and <a class="link" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility" href="https://dev.mysql.com/doc/refman/5.7/en/myisamchk.html"><span class="command"><strong>myisamchk</strong></span></a> is not supported with partitioned tables.</p>
<p>In MySQL 5.7, you can also truncate partitions using <a class="link" title="13.1.8 ALTER TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/alter-table.html"><code class="literal">ALTER TABLE ... TRUNCATE PARTITION</code></a>. This statement can be used to delete all rows from one or more partitions in much the same way that <a class="link" title="13.1.34 TRUNCATE TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/truncate-table.html"><code class="literal">TRUNCATE TABLE</code></a> deletes all rows from a table.</p>
<p><a class="link" title="13.1.8 ALTER TABLE Syntax" href="https://dev.mysql.com/doc/refman/5.7/en/alter-table.html"><code class="literal">ALTER TABLE ... TRUNCATE PARTITION ALL</code></a> truncates all partitions in the table.</p>
<p>Prior to MySQL 5.7.2, <code class="literal">ANALYZE</code>, <code class="literal">CHECK</code>, <code class="literal">OPTIMIZE</code>, <code class="literal">REBUILD</code>, <code class="literal">REPAIR</code>, and <code class="literal">TRUNCATE</code> operations were not permitted on subpartitions (Bug #14028340, Bug #65184). <br /><br />[원문]<br /><a href="https://dev.mysql.com/doc/refman/5.7/en/partitioning-maintenance.html">https://dev.mysql.com/doc/refman/5.7/en/partitioning-maintenance.html</a></p><p><strong><a href="http://blog1.phps.kr/731?commentInput=true#entry731WriteComment">댓글 쓰기</a></strong></p>
mysql, mariadb query 세로로 , 쿼리 세로로
java, tomcat, c#
http://blog1.phps.kr/730
2018-01-09T10:57:23+09:00
2018-01-09T10:57:21+09:00
종 데이터를 횡 데이트로 만들기 (세로 - 가로)<br /><br />횡으로 구성되어있는 데이터를 종으로 만드는 방법에 대해 설명합니다. 먼저 전, 후의 데이터를 비교해보세요.
<h3 class="new">처리 전 데이터</h3>
<div>
<div id="highlighter_703534" class="syntaxhighlighter sql">
<table border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td class="gutter">
<div class="line number1 index0 alt2" style="height: 16px !important;">1</div>
<div class="line number2 index1 alt1" style="height: 16px !important;">2</div>
<div class="line number3 index2 alt2" style="height: 16px !important;">3</div>
<div class="line number4 index3 alt1" style="height: 16px !important;">4</div>
<div class="line number5 index4 alt2" style="height: 16px !important;">5</div>
<div class="line number6 index5 alt1" style="height: 16px !important;">6</div>
<div class="line number7 index6 alt2" style="height: 16px !important;">7</div>
<div class="line number8 index7 alt1" style="height: 16px !important;">8</div>
<div class="line number9 index8 alt2" style="height: 16px !important;">9</div>
<div class="line number10 index9 alt1" style="height: 16px !important;">10</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2"><code class="sql plain">+</code><code class="sql comments">-----+------+-------+-------+</code></div>
<div class="line number2 index1 alt1"><code class="sql plain">| idx | </code><code class="sql keyword">name</code> <code class="sql plain">| class | score |</code></div>
<div class="line number3 index2 alt2"><code class="sql plain">+</code><code class="sql comments">-----+------+-------+-------+</code></div>
<div class="line number4 index3 alt1"><code class="sql plain">| 7 | choi | kor | 90 | </code></div>
<div class="line number5 index4 alt2"><code class="sql plain">| 8 | choi | eng | 80 | </code></div>
<div class="line number6 index5 alt1"><code class="sql plain">| 9 | choi | math | 70 | </code></div>
<div class="line number7 index6 alt2"><code class="sql plain">| 10 | kim | kor | 60 | </code></div>
<div class="line number8 index7 alt1"><code class="sql plain">| 11 | kim | eng | 85 | </code></div>
<div class="line number9 index8 alt2"><code class="sql plain">| 12 | kim | math | 100 | </code></div>
<div class="line number10 index9 alt1"><code class="sql plain">+</code><code class="sql comments">-----+------+-------+-------+</code></div>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<h3>처리 후 데이터</h3>
<div>
<div id="highlighter_864384" class="syntaxhighlighter sql">
<table border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td class="gutter">
<div class="line number1 index0 alt2" style="height: 16px !important;">1</div>
<div class="line number2 index1 alt1" style="height: 16px !important;">2</div>
<div class="line number3 index2 alt2" style="height: 16px !important;">3</div>
<div class="line number4 index3 alt1" style="height: 16px !important;">4</div>
<div class="line number5 index4 alt2" style="height: 16px !important;">5</div>
<div class="line number6 index5 alt1" style="height: 16px !important;">6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2"><code class="sql plain">+</code><code class="sql comments">------+------+------+------+</code></div>
<div class="line number2 index1 alt1"><code class="sql plain">| </code><code class="sql keyword">name</code> <code class="sql plain">| kor | eng | math |</code></div>
<div class="line number3 index2 alt2"><code class="sql plain">+</code><code class="sql comments">------+------+------+------+</code></div>
<div class="line number4 index3 alt1"><code class="sql plain">| choi | 90 | 80 | 70 | </code></div>
<div class="line number5 index4 alt2"><code class="sql plain">| kim | 60 | 85 | 100 | </code></div>
<div class="line number6 index5 alt1"><code class="sql plain">+</code><code class="sql comments">------+------+------+------+</code></div>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<p>처리 후 데이터를 보면 name 기준으로 kor, eng, math항목이 횡으로 나열된 것을 볼 수 있습니다.</p>
<h2>종 데이터 샘플 생성</h2>
<p>종 데이터로 사용할 사용할 가상의 샘플 데이터를 생성해 보겠습니다.</p>
<div>
<div id="highlighter_413123" class="syntaxhighlighter sql">
<table border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td class="gutter">
<div class="line number1 index0 alt2" style="height: 16px !important;">1</div>
<div class="line number2 index1 alt1" style="height: 16px !important;">2</div>
<div class="line number3 index2 alt2" style="height: 16px !important;">3</div>
<div class="line number4 index3 alt1" style="height: 16px !important;">4</div>
<div class="line number5 index4 alt2" style="height: 16px !important;">5</div>
<div class="line number6 index5 alt1" style="height: 16px !important;">6</div>
<div class="line number7 index6 alt2" style="height: 16px !important;">7</div>
<div class="line number8 index7 alt1" style="height: 16px !important;">8</div>
<div class="line number9 index8 alt2" style="height: 16px !important;">9</div>
<div class="line number10 index9 alt1" style="height: 16px !important;">10</div>
<div class="line number11 index10 alt2" style="height: 16px !important;">11</div>
<div class="line number12 index11 alt1" style="height: 16px !important;">12</div>
<div class="line number13 index12 alt2" style="height: 16px !important;">13</div>
<div class="line number14 index13 alt1" style="height: 16px !important;">14</div>
<div class="line number15 index14 alt2" style="height: 16px !important;">15</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2"><code class="sql keyword">CREATE</code> <code class="sql keyword">TABLE</code> <code class="sql plain">`score` (</code></div>
<div class="line number2 index1 alt1"><code class="sql spaces"> </code><code class="sql plain">`idx` </code><code class="sql keyword">int</code><code class="sql plain">(11) </code><code class="sql color1">NOT</code> <code class="sql color1">NULL</code> <code class="sql plain">AUTO_INCREMENT,</code></div>
<div class="line number3 index2 alt2"><code class="sql spaces"> </code><code class="sql plain">`</code><code class="sql keyword">name</code><code class="sql plain">` </code><code class="sql keyword">varchar</code><code class="sql plain">(32) </code><code class="sql color1">NOT</code> <code class="sql color1">NULL</code><code class="sql plain">,</code></div>
<div class="line number4 index3 alt1"><code class="sql spaces"> </code><code class="sql plain">`class` </code><code class="sql keyword">varchar</code><code class="sql plain">(32) </code><code class="sql color1">NOT</code> <code class="sql color1">NULL</code><code class="sql plain">,</code></div>
<div class="line number5 index4 alt2"><code class="sql spaces"> </code><code class="sql plain">`score` </code><code class="sql keyword">int</code><code class="sql plain">(11) </code><code class="sql color1">NOT</code> <code class="sql color1">NULL</code><code class="sql plain">,</code></div>
<div class="line number6 index5 alt1"><code class="sql spaces"> </code><code class="sql keyword">PRIMARY</code> <code class="sql keyword">KEY</code> <code class="sql plain">(`idx`)</code></div>
<div class="line number7 index6 alt2"><code class="sql plain">);</code></div>
<div class="line number8 index7 alt1"> </div>
<div class="line number9 index8 alt2"><code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">score (</code><code class="sql keyword">name</code><code class="sql plain">, class, score) </code><code class="sql keyword">VALUES</code> <code class="sql plain">(</code><code class="sql string">'choi'</code><code class="sql plain">, </code><code class="sql string">'kor'</code><code class="sql plain">, 90);</code></div>
<div class="line number10 index9 alt1"><code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">score (</code><code class="sql keyword">name</code><code class="sql plain">, class, score) </code><code class="sql keyword">VALUES</code> <code class="sql plain">(</code><code class="sql string">'choi'</code><code class="sql plain">, </code><code class="sql string">'eng'</code><code class="sql plain">, 80);</code></div>
<div class="line number11 index10 alt2"><code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">score (</code><code class="sql keyword">name</code><code class="sql plain">, class, score) </code><code class="sql keyword">VALUES</code> <code class="sql plain">(</code><code class="sql string">'choi'</code><code class="sql plain">, </code><code class="sql string">'math'</code><code class="sql plain">, 70);</code></div>
<div class="line number12 index11 alt1"> </div>
<div class="line number13 index12 alt2"><code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">score (</code><code class="sql keyword">name</code><code class="sql plain">, class, score) </code><code class="sql keyword">VALUES</code> <code class="sql plain">(</code><code class="sql string">'kim'</code><code class="sql plain">, </code><code class="sql string">'kor'</code><code class="sql plain">, 60);</code></div>
<div class="line number14 index13 alt1"><code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">score (</code><code class="sql keyword">name</code><code class="sql plain">, class, score) </code><code class="sql keyword">VALUES</code> <code class="sql plain">(</code><code class="sql string">'kim'</code><code class="sql plain">, </code><code class="sql string">'eng'</code><code class="sql plain">, 85);</code></div>
<div class="line number15 index14 alt2"><code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">score (</code><code class="sql keyword">name</code><code class="sql plain">, class, score) </code><code class="sql keyword">VALUES</code> <code class="sql plain">(</code><code class="sql string">'kim'</code><code class="sql plain">, </code><code class="sql string">'math'</code><code class="sql plain">, 100);</code></div>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<p>종 데이터 생성이 완료 되었습니다.</p>
<div>
<div id="highlighter_483421" class="syntaxhighlighter sql">
<table border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td class="gutter">
<div class="line number1 index0 alt2" style="height: 16px !important;">1</div>
<div class="line number2 index1 alt1" style="height: 16px !important;">2</div>
<div class="line number3 index2 alt2" style="height: 16px !important;">3</div>
<div class="line number4 index3 alt1" style="height: 16px !important;">4</div>
<div class="line number5 index4 alt2" style="height: 16px !important;">5</div>
<div class="line number6 index5 alt1" style="height: 16px !important;">6</div>
<div class="line number7 index6 alt2" style="height: 16px !important;">7</div>
<div class="line number8 index7 alt1" style="height: 16px !important;">8</div>
<div class="line number9 index8 alt2" style="height: 16px !important;">9</div>
<div class="line number10 index9 alt1" style="height: 16px !important;">10</div>
<div class="line number11 index10 alt2" style="height: 16px !important;">11</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2"><code class="sql plain">mysql> </code><code class="sql keyword">SELECT</code> <code class="sql plain">* </code><code class="sql keyword">FROM</code> <code class="sql plain">score;</code></div>
<div class="line number2 index1 alt1"><code class="sql plain">+</code><code class="sql comments">-----+------+-------+-------+</code></div>
<div class="line number3 index2 alt2"><code class="sql plain">| idx | </code><code class="sql keyword">name</code> <code class="sql plain">| class | score |</code></div>
<div class="line number4 index3 alt1"><code class="sql plain">+</code><code class="sql comments">-----+------+-------+-------+</code></div>
<div class="line number5 index4 alt2"><code class="sql plain">| 7 | choi | kor | 90 | </code></div>
<div class="line number6 index5 alt1"><code class="sql plain">| 8 | choi | eng | 80 | </code></div>
<div class="line number7 index6 alt2"><code class="sql plain">| 9 | choi | math | 70 | </code></div>
<div class="line number8 index7 alt1"><code class="sql plain">| 10 | kim | kor | 60 | </code></div>
<div class="line number9 index8 alt2"><code class="sql plain">| 11 | kim | eng | 85 | </code></div>
<div class="line number10 index9 alt1"><code class="sql plain">| 12 | kim | math | 100 | </code></div>
<div class="line number11 index10 alt2"><code class="sql plain">+</code><code class="sql comments">-----+------+-------+-------+</code></div>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<h2>alias를 이용한 컬럼 생성</h2>
<p>종 데이터를 kor, eng, math로 구성된 횡 데이터로 만들기 위해 alias를 이용 K, E, M 컬럼을 생성하고 kor데이터는 K에 eng데이터는 E에 math데이터는 M에 넣습니다.</p>
<p>값이 저장되지 않은 컬럼은 NULL이 저장됩니다.</p>
<div>
<div id="highlighter_648938" class="syntaxhighlighter sql ">
<table border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td class="gutter">
<div class="line number1 index0 alt2" style="height: 16px !important;">1</div>
<div class="line number2 index1 alt1" style="height: 16px !important;">2</div>
<div class="line number3 index2 alt2" style="height: 16px !important;">3</div>
<div class="line number4 index3 alt1" style="height: 16px !important;">4</div>
<div class="line number5 index4 alt2" style="height: 16px !important;">5</div>
<div class="line number6 index5 alt1" style="height: 16px !important;">6</div>
<div class="line number7 index6 alt2" style="height: 16px !important;">7</div>
<div class="line number8 index7 alt1" style="height: 16px !important;">8</div>
<div class="line number9 index8 alt2" style="height: 16px !important;">9</div>
<div class="line number10 index9 alt1" style="height: 16px !important;">10</div>
<div class="line number11 index10 alt2" style="height: 16px !important;">11</div>
<div class="line number12 index11 alt1" style="height: 16px !important;">12</div>
<div class="line number13 index12 alt2" style="height: 16px !important;">13</div>
<div class="line number14 index13 alt1" style="height: 16px !important;">14</div>
<div class="line number15 index14 alt2" style="height: 16px !important;">15</div>
<div class="line number16 index15 alt1" style="height: 16px !important;">16</div>
<div class="line number17 index16 alt2" style="height: 16px !important;">17</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2"><code class="sql keyword">SELECT</code></div>
<div class="line number2 index1 alt1"><code class="sql spaces"> </code><code class="sql keyword">name</code><code class="sql plain">, </code></div>
<div class="line number3 index2 alt2"><code class="sql spaces"> </code><code class="sql color2">CASE</code> <code class="sql keyword">WHEN</code> <code class="sql plain">class = </code><code class="sql string">'kor'</code> <code class="sql keyword">THEN</code> <code class="sql plain">score </code><code class="sql keyword">END</code> <code class="sql keyword">AS</code> <code class="sql plain">K, </code></div>
<div class="line number4 index3 alt1"><code class="sql spaces"> </code><code class="sql color2">CASE</code> <code class="sql keyword">WHEN</code> <code class="sql plain">class = </code><code class="sql string">'eng'</code> <code class="sql keyword">THEN</code> <code class="sql plain">score </code><code class="sql keyword">END</code> <code class="sql keyword">AS</code> <code class="sql plain">E, </code></div>
<div class="line number5 index4 alt2"><code class="sql spaces"> </code><code class="sql color2">CASE</code> <code class="sql keyword">WHEN</code> <code class="sql plain">class = </code><code class="sql string">'math'</code> <code class="sql keyword">THEN</code> <code class="sql plain">score </code><code class="sql keyword">END</code> <code class="sql keyword">AS</code> <code class="sql plain">M </code></div>
<div class="line number6 index5 alt1"><code class="sql keyword">FROM</code> <code class="sql plain">score;</code></div>
<div class="line number7 index6 alt2"> </div>
<div class="line number8 index7 alt1"><code class="sql plain">+</code><code class="sql comments">------+------+------+------+</code></div>
<div class="line number9 index8 alt2"><code class="sql plain">| </code><code class="sql keyword">name</code> <code class="sql plain">| K | E | M |</code></div>
<div class="line number10 index9 alt1"><code class="sql plain">+</code><code class="sql comments">------+------+------+------+</code></div>
<div class="line number11 index10 alt2"><code class="sql plain">| choi | 90 | </code><code class="sql color1">NULL</code> <code class="sql plain">| </code><code class="sql color1">NULL</code> <code class="sql plain">| </code></div>
<div class="line number12 index11 alt1"><code class="sql plain">| choi | </code><code class="sql color1">NULL</code> <code class="sql plain">| 80 | </code><code class="sql color1">NULL</code> <code class="sql plain">| </code></div>
<div class="line number13 index12 alt2"><code class="sql plain">| choi | </code><code class="sql color1">NULL</code> <code class="sql plain">| </code><code class="sql color1">NULL</code> <code class="sql plain">| 70 | </code></div>
<div class="line number14 index13 alt1"><code class="sql plain">| kim | 60 | </code><code class="sql color1">NULL</code> <code class="sql plain">| </code><code class="sql color1">NULL</code> <code class="sql plain">| </code></div>
<div class="line number15 index14 alt2"><code class="sql plain">| kim | </code><code class="sql color1">NULL</code> <code class="sql plain">| 85 | </code><code class="sql color1">NULL</code> <code class="sql plain">| </code></div>
<div class="line number16 index15 alt1"><code class="sql plain">| kim | </code><code class="sql color1">NULL</code> <code class="sql plain">| </code><code class="sql color1">NULL</code> <code class="sql plain">| 100 | </code></div>
<div class="line number17 index16 alt2"><code class="sql plain">+</code><code class="sql comments">------+------+------+------+</code></div>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<p>아직까지는 종 데이터의 형식이지만 이제 횡데이터로 만들기 위한 준비는 되었습니다.</p>
<h2>횡 데이터 생성</h2>
<p>위에서 만들었던 쿼리를 이용해서 임의의 테이블을 만듭니다.<br />임의의 테이블을 group by하고 alias를 이용해서 생성한 K, E, M 컬럼을 이용 횡데이터로 만듭니다.<br />K, E, M컬럼은 SUM 함수를 이용해서 더해주고 alias를 이용 kor, eng, math로 컬럼명을 지정합니다. </p>
<div>
<div id="highlighter_141109" class="syntaxhighlighter sql">
<table border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td class="gutter">
<div class="line number1 index0 alt2" style="height: 16px !important;">1</div>
<div class="line number2 index1 alt1" style="height: 16px !important;">2</div>
<div class="line number3 index2 alt2" style="height: 16px !important;">3</div>
<div class="line number4 index3 alt1" style="height: 16px !important;">4</div>
<div class="line number5 index4 alt2" style="height: 16px !important;">5</div>
<div class="line number6 index5 alt1" style="height: 16px !important;">6</div>
<div class="line number7 index6 alt2" style="height: 16px !important;">7</div>
<div class="line number8 index7 alt1" style="height: 16px !important;">8</div>
<div class="line number9 index8 alt2" style="height: 16px !important;">9</div>
<div class="line number10 index9 alt1" style="height: 16px !important;">10</div>
<div class="line number11 index10 alt2" style="height: 16px !important;">11</div>
<div class="line number12 index11 alt1" style="height: 16px !important;">12</div>
<div class="line number13 index12 alt2" style="height: 16px !important;">13</div>
<div class="line number14 index13 alt1" style="height: 16px !important;">14</div>
<div class="line number15 index14 alt2" style="height: 16px !important;">15</div>
<div class="line number16 index15 alt1" style="height: 16px !important;">16</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2"><code class="sql keyword">SELECT</code> <code class="sql keyword">name</code><code class="sql plain">, </code><code class="sql color2">SUM</code><code class="sql plain">(K) </code><code class="sql keyword">as</code> <code class="sql plain">kor, </code><code class="sql color2">SUM</code><code class="sql plain">(E) </code><code class="sql keyword">as</code> <code class="sql plain">eng, </code><code class="sql color2">SUM</code><code class="sql plain">(M) </code><code class="sql keyword">as</code> <code class="sql plain">math </code><code class="sql keyword">FROM</code></div>
<div class="line number2 index1 alt1"><code class="sql plain">(</code></div>
<div class="line number3 index2 alt2"><code class="sql spaces"> </code><code class="sql keyword">SELECT</code></div>
<div class="line number4 index3 alt1"><code class="sql spaces"> </code><code class="sql keyword">name</code><code class="sql plain">, </code></div>
<div class="line number5 index4 alt2"><code class="sql spaces"> </code><code class="sql color2">CASE</code> <code class="sql keyword">WHEN</code> <code class="sql plain">class = </code><code class="sql string">'kor'</code> <code class="sql keyword">THEN</code> <code class="sql plain">score </code><code class="sql keyword">END</code> <code class="sql keyword">AS</code> <code class="sql plain">K, </code></div>
<div class="line number6 index5 alt1"><code class="sql spaces"> </code><code class="sql color2">CASE</code> <code class="sql keyword">WHEN</code> <code class="sql plain">class = </code><code class="sql string">'eng'</code> <code class="sql keyword">THEN</code> <code class="sql plain">score </code><code class="sql keyword">END</code> <code class="sql keyword">AS</code> <code class="sql plain">E, </code></div>
<div class="line number7 index6 alt2"><code class="sql spaces"> </code><code class="sql color2">CASE</code> <code class="sql keyword">WHEN</code> <code class="sql plain">class = </code><code class="sql string">'math'</code> <code class="sql keyword">THEN</code> <code class="sql plain">score </code><code class="sql keyword">END</code> <code class="sql keyword">AS</code> <code class="sql plain">M </code></div>
<div class="line number8 index7 alt1"><code class="sql spaces"> </code><code class="sql keyword">FROM</code> <code class="sql plain">score</code></div>
<div class="line number9 index8 alt2"><code class="sql plain">)</code><code class="sql keyword">AS</code> <code class="sql plain">T </code><code class="sql keyword">GROUP</code> <code class="sql keyword">BY</code> <code class="sql keyword">name</code><code class="sql plain">;</code></div>
<div class="line number10 index9 alt1"> </div>
<div class="line number11 index10 alt2"><code class="sql plain">+</code><code class="sql comments">------+------+------+------+</code></div>
<div class="line number12 index11 alt1"><code class="sql plain">| </code><code class="sql keyword">name</code> <code class="sql plain">| kor | eng | math |</code></div>
<div class="line number13 index12 alt2"><code class="sql plain">+</code><code class="sql comments">------+------+------+------+</code></div>
<div class="line number14 index13 alt1"><code class="sql plain">| choi | 90 | 80 | 70 | </code></div>
<div class="line number15 index14 alt2"><code class="sql plain">| kim | 60 | 85 | 100 | </code></div>
<div class="line number16 index15 alt1"><code class="sql plain">+</code><code class="sql comments">------+------+------+------+</code></div>
</div>
</td>
</tr>
</tbody>
</table>
<br /><br />[원문]http://blog.devez.net/307<br /><br />
<h2>참고 URL</h2>
<p><a href="http://stackoverflow.com/questions/1241178/mysql-rows-to-columns"> http://stackoverflow.com/questions/1241178/mysql-rows-to-columns </a></p>
</div>
</div><p><strong><a href="http://blog1.phps.kr/730?commentInput=true#entry730WriteComment">댓글 쓰기</a></strong></p>
mariadb partition management , mysql 파티셔닝 관리
java, tomcat, c#
http://blog1.phps.kr/729
2018-01-08T16:28:41+09:00
2018-01-08T15:31:11+09:00
<p><span style="color: #670000;"><span style="color: #980000;"><span style="color: #980000;"><strong>[참고] : </strong></span><span style="color: #980000;"><strong><a href="https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html">https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html<br /><br /></a></strong></span>========================================================================================================<br /><span style="color: #980000;"><strong>1. 파티션 지원 확인</strong></span></span></span></p>
<p>SHOW VARIABLES LIKE '%partition%';</p>
<p>SHOW PLUGINS;</p>
<p> </p>
<p><strong><span style="color: #980000;">2. 파티션 추가</span></strong></p>
<p>CREATE TABLE `테이블` (</p>
<p> ....</p>
<p>)</p>
<p>PARTITION BY RANGE(함수(`필드명`)) (</p>
<p> PARTITION `파티션명1` VALUES LESS THAN (값),</p>
<p> PARTITION `파티션명2` VALUES LESS THAN (값),</p>
<p> PARTITION `파티션명3` VALUES LESS THAN (MAXVALUE)</p>
<p>);</p>
<p> </p>
<p>CREATE TABLE 이후 추가</p>
<p>ALTER TABLE<span style="line-height: 1.5; font-size: 9pt;"> </span><span style="line-height: 1.5; font-size: 9pt;">`테이블`</span><span style="line-height: 1.5; font-size: 9pt;"> </span><span style="line-height: 1.5; font-size: 9pt;">PARTITION BY RANGE(함수(`필드명`)) (</span></p>
<p> PARTITION `파티션명1` VALUES LESS THAN (값),</p>
<p> PARTITION `파티션명2` VALUES LESS THAN (값),</p>
<p> PARTITION `파티션명3` VALUES LESS THAN (MAXVALUE)</p>
<p>);</p>
<p> </p>
<p># THAN (표현식) 에는 THAN (함수(값)) 형태로도 가능</p>
<p>#표현식에 사용할 수 있는 함수 종류 및 파티션의 종류는 아래 링크에서 참고</p>
<p><a class="tx-link" href="http://wyseburn.tistory.com/entry/MySQL-%ED%8C%8C%ED%8B%B0%EC%85%94%EB%8B%9D" target="_blank">http://wyseburn.tistory.com/entry/MySQL-%ED%8C%8C%ED%8B%B0%EC%85%94%EB%8B%9D</a></p>
<p> </p>
<p><strong><span style="color: #980000;">3. 파티션 분할</span></strong></p>
<p>ALTER TABLE <span style="line-height: 1.5; font-size: 9pt;">`테이블` REORGANIZE PARTITION </span><span style="line-height: 1.5; font-size: 9pt;">`파티션명3` INTO (</span></p>
<p><span style="line-height: 1.5; font-size: 9pt;"> PARTITION </span><span style="line-height: 1.5; font-size: 9pt;">`파티션명3` VALUES LESS THAN (값),</span></p>
<p><span style="line-height: 1.5; font-size: 9pt;"> PARTITION `파티션명4` VALUES LESS THAN (MAXVALUE)</span></p>
<p><span style="line-height: 1.5; font-size: 9pt;">);</span></p>
<p><span style="line-height: 1.5; font-size: 9pt;"> </span></p>
<p><strong><span style="color: #980000; line-height: 1.5; font-size: 9pt;">4. </span><span style="color: #980000; line-height: 1.5; font-size: 9pt;">파티션 삭제</span></strong></p>
<p>ALTER TABLE<span style="line-height: 1.5; font-size: 9pt;"> </span><span style="line-height: 1.5; font-size: 9pt;">`테이블` DROP PARTITION </span><span style="line-height: 1.5; font-size: 9pt;">`파티션명1`;</span></p>
<p> </p>
<p><strong><span style="color: #980000;">5. 파티션 관리</span></strong></p>
<p><span style="color: #565656; line-height: 16.79px; font-family: 굴림, Gulim, dotum, 돋움, sans-serif;">ALTER TABLE REBUILD PARTITION;</span><br style="color: #565656; line-height: 2em; font-family: 굴림, Gulim, dotum, 돋움, sans-serif;" /><span style="color: #565656; line-height: 16.79px; font-family: 굴림, Gulim, dotum, 돋움, sans-serif;">ALTER TABLE OPTIMIZE PARTITION;</span><br style="color: #565656; line-height: 2em; font-family: 굴림, Gulim, dotum, 돋움, sans-serif;" /><span style="color: #565656; line-height: 16.79px; font-family: 굴림, Gulim, dotum, 돋움, sans-serif;">ALTER TABLE ANALYZE PARTITION;</span><br style="color: #565656; line-height: 2em; font-family: 굴림, Gulim, dotum, 돋움, sans-serif;" /><span style="color: #565656; line-height: 16.79px; font-family: 굴림, Gulim, dotum, 돋움, sans-serif;">ALTER TABLE REPAIR PARTITION;</span><br style="color: #565656; line-height: 2em; font-family: 굴림, Gulim, dotum, 돋움, sans-serif;" /><span style="color: #565656; line-height: 16.79px; font-family: 굴림, Gulim, dotum, 돋움, sans-serif;">ALTER TABLE CHECK PARTITION;</span></p>
<p> </p>
<p><strong><span style="color: #980000;">6. 파티션 완전삭제</span></strong></p>
<p>ALTER TABLE `테이블` REMOVE PARTITIONING;</p>
<br /><br />출처: <a href="http://wyseburn.tistory.com/entry/MySql-%ED%8C%8C%ED%8B%B0%EC%85%94%EB%8B%9D-%EC%A0%95%EB%A6%AC">http://wyseburn.tistory.com/entry/MySql-파티셔닝-정리</a> [메모장입니다.]<p><strong><a href="http://blog1.phps.kr/729?commentInput=true#entry729WriteComment">댓글 쓰기</a></strong></p>
innodb_flush_log_at_trx_commit
java, tomcat, c#
http://blog1.phps.kr/723
2017-09-27T09:15:53+09:00
2017-09-27T09:15:50+09:00
<p><br />실 운영시 =2로 사용(안정성)<br />디비작업시(대량)인서트 = 잠시 0 설정 해줌</p>
<p>mariadb 콜솔에서 실행<br />set global innodb_flush_log_at_trx_commit = 0;<br /><br /><br />innodb_flush_log_at_trx_commit = 0<br />innodb_flush_log_at_trx_commit = 1<br />innodb_flush_log_at_trx_commit = 2<br /><br /></p>
<p>0 인 경우, MySQL 이나 OS가 갑자기 crash 된다면 최대 1초동안의 트랜잭션을 잃을 수 있다.</p>
<ul>
<li>1 인 경우, 안전하다.</li>
<li>2 인 경우, OS가 갑자기 crash 된다면 최대 1초동안의 트랜잭션을 잃을 수 있다. 하지만 MySQL 장애시에는 이미 OS 영역으로 데이터는 넘어갔기 때문에 안전할 수 있다.</li>
<li>각 값에 따라, 엄청난 성능을 보일 수 있다.
<ul>
<li>지난번에 엄청난 양의 log를 위하여 MySQL을 사용하는 팀이 있었는데, 해당값을 1에서 0으로 수정함에 따라 성능이 7배 빨라지기도 했다.</li>
<li>단순 select용의 slave나, 최대 1초정도의 트랜잭션은 무시할 수 있는 서비스 혹은 log를 저장할 서버라면 해당값을 1에서 0으로 변경할 수 있다.</li>
<li>MySQL을 가장 빠르고 쉽게 튜닝할 수 있는 parameter 중의 하나이다.</li>
</ul>
</li>
</ul><p><strong><a href="http://blog1.phps.kr/723?commentInput=true#entry723WriteComment">댓글 쓰기</a></strong></p>
wait_timeout 설정
java, tomcat, c#
http://blog1.phps.kr/722
2017-09-20T14:21:43+09:00
2017-09-20T14:21:39+09:00
<p>[원문]https://sites.google.com/site/dist777study/home/db/mysql/wait_timeout<br />SET @@GLOBAL.wait_timeout=300<br />my.cnf 에 <br />wait_timeout=300 <br />추가<br /><br /></p><p><strong><a href="http://blog1.phps.kr/722?commentInput=true#entry722WriteComment">댓글 쓰기</a></strong></p>
날짜 중복범위 검색
java, tomcat, c#
http://blog1.phps.kr/718
2017-07-18T13:38:57+09:00
2017-07-18T13:38:54+09:00
<p>날짜 중복확인:</p>
<p>(('사용자입력시작일' <= DB시작일 and DB시작일 <= '사용자입력종료일') or ('사용자입력시작일' <= DB종료일 and DB종료일 <= '사용자입력종료일') or (DB시작일 <= '사용자입력시작일' and '사용자입력종료일' <= DB종료일)) </p><p><strong><a href="http://blog1.phps.kr/718?commentInput=true#entry718WriteComment">댓글 쓰기</a></strong></p>
mariadb Connection 수 관리 명령어
java, tomcat, c#
http://blog1.phps.kr/713
2016-10-13T09:49:12+09:00
2016-10-13T09:45:48+09:00
<p><br />-- Maria DB max connection 수 확인</p>
<p>SHOW VARIABLES LIKE '%max_connection%'<br /><br /></p>
<p>-- Maria DB에 접속되어 있는 client 정보 리스트</p>
<p>SHOW PROCESSLIST;<br /><br /></p>
<p>-- Maria DB max connection 수 조절</p>
<p>SET GLOBAL max_connections = 10000;<br /><br />//status 관련 값<br />show status like '%CONNECT%';<br /><br />Max_used_connections <--현재 연결된 접속수<br /><br />Threads_connected <-- 연결되었던 최대 접속수</p><p><strong><a href="http://blog1.phps.kr/713?commentInput=true#entry713WriteComment">댓글 쓰기</a></strong></p>