How To Optimize WordPress Performance With MySQL Replication On Ubuntu 14.04

Introduction

In this tutorial, we will teach you how to scale up your WordPress MySQL database server setup using master-slave database replication and the HyperDB plugin for WordPress. Adding more database servers to your environment in this manner allows your WordPress application to read from multiple database servers, increasing read performance.

MySQL replication reaps the most performance benefits for a system that processes frequent reads and infrequent writes, like most WordPress installations. By using a single-master with multiple-slave setup, you can add more slaves to scale your system, until you run out of network bandwidth or your master cannot handle the update load. If you wish, you can add more than one slaves by repeating the “slave” portions of the replication sections of this tutorial.

We are assuming that your setup includes two load balanced WordPress application servers that connect to a separate MySQL database server (see the prerequisites for a tutorial on how to set that up). It is not strictly necessary to have load balanced application servers to follow this tutorial, but your MySQL database server should be separate from your application servers.

Prerequisites

Before continuing with this tutorial, you should have completed two tutorials or have a similar environment:

After following those tutorials, to set up WordPress with two load balanced web application servers and a separate database server, you should have four VPSs. Because we will be dealing with several VPSs, for reference purposes, we will call your four existing VPSs the following:

  • haproxy-www: Your HAProxy server for layer 4 load balancing your WordPress web application servers. This is the entry point into your website
  • wordpress-1: Your first WordPress web application server
  • wordpress-2: Your second WordPress web application server
  • mysql-1: Your MySQL server for WordPress

That is, your environment should look something like this:

 

 

In addition to your current environment, we will require one additional VPS during this tutorial. We will call it:

  • mysql-2: Your slave MySQL database server

Our Goal

When we are finished with this tutorial, you will have two database servers will be replicating in a master-slave configuration. Your WordPress servers will selectively write to your master and read from both your master and slave databases, by use of the HyperDB WordPress plugin. Your final environment should look something like this:

 

 

Keep in mind that you do not need to have load balanced application servers (wordpress-1/wordpress-2) to follow this tutorial, and that you can add more slave databases if you want.

Set Up MySQL Master-Slave Replication

Before we can configure our WordPress application to read from multiple database servers, we need to set up our MySQL replication.

Create MySQL Slave VPS, mysql-2

You will want to create a new VPS that will act as the MySQL slave server–for reference purposes, we will call this server mysql-2. The slave will be configured to replicate all of the databases of your master MySQL server, including your WordPress database.

On mysql-2, install the MySQL software:

sudo apt-get update sudo apt-get install mysql-server

Enter a root password for your MySQL installation. Next we will configure on our MySQL master server.

Configure Existing MySQL Server as a Master

The first step is to set up your existing MySQL database server, mysql-1, with a replication master configuration.

On mysql-1, edit the MySQL configuration file:

sudo vi /etc/mysql/my.cnf

Search for the following three lines:

<pre>
bind-address = <span class=“highlight”>mysql1private_IP</span>

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

</pre>

  • bind-address: the IP address that MySQL will listen on. This should already be set to mysql-1’s private IP address from your original setup
  • server-id: the unique server ID. Since this is the master server, we will want to leave the value as “1” and uncomment this line
  • log_bin: the location of the binary log file. The binary log is used to send data changes from the master to its slave for replication. Uncomment this line

The three lines should look like this (be sure to substitute the highlighted with database server’s private IP address):

<pre>
bind-address = <span class=“highlight”>mysql1privateIP</span>
server-id = 1
logbin = /var/log/mysql/mysql-bin.log
</pre>

Optionally, if you want to restrict the replication to the wordpress database, specifically, add the following line to your configuration (substituting the highlighted with your desired database name):

<pre>
binlogdodb = <span class=“highlight”>wordpress</span>
</pre>

Save and quit. To put these changes into effect, restart mysql with the following command:

sudo service mysql restart

Connect to to the MySQL console with the following command, then enter the password when prompted:

mysql -u root -p

Create a user that will be used by our slave MySQL servers for replication purposes. We will call this user repl. Be sure to replace the repl_password with your own, strong password. The % specifies that the source IP for this user can be anything, but you may substitute the % with the private IP address of your slave MySQL server, mysql-2, to restrict connections with this user to that particular server:

<pre>
CREATE USER ‘repl’@’<span class=“highlight”>%</span>’ IDENTIFIED BY ’<span class=“highlight”>repl_password</span>’;
GRANT REPLICATION SLAVE ON . TO 'repl’@’%’;
</pre>

Do not exit the MySQL console yet!

Export a Backup Of MySQL Master

Next, we will want to export a backup of the MySQL master database, to import into our slave database so it will be identical before we start replication. We need to lock the database so we can do a data dump. In your MySQL console on mysql-1, run this:

FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON; EXIT

Now, from your command shell, run the following command to export a backup of the databases on your master MySQL server to a file called masterdump.sql:

mysqldump --lock-all-tables -u root -p --all-databases > masterdump.sql

Copy your masterdump.sql file to your slave server, mysql-2, using scp:

<pre>
scp masterdump.sql <span class=“highlight”>user</span>@<span class=“highlight”>mysql2private_IP</span>:/tmp
</pre>

Enter mysql-1’s MySQL console once again:

mysql -u root -p

At the MySQL prompt, unlock your database:

SET GLOBAL read_only = OFF; UNLOCK TABLES;

Now run this statement to print out information that you will need to configure your MySQL slave:

<pre>
SHOW MASTER STATUS;
</pre> <pre> Output: +——————+———-+————–+——————+ | File | Position | BinlogDoDB | BinlogIgnoreDB |
+——————+———-+————–+——————+
| <span class=“highlight”>mysql-bin.000001</span> | <span class=“highlight”>408</span> | | | +——————+———-+————–+——————+ 1 row in set (0.00 sec) </pre>

Take a note of the values of File and Position, as you will need them when configuring your slave server. Let’s switch over to mysql-2 now.

Configure MySQL Slave

Now we will want to import the master database into our slave to synchronize them in preparation for replication.

On mysql-2, run this command to import the masterdump.sql file:

mysql -u root -p < /tmp/masterdump.sql

Next, we will set up mysql-2 as a replication slave. On mysql-2, edit the MySQL configuration file:

sudo vi /etc/mysql/my.cnf

Search for the following two lines:

<pre>
bind-address = 127.0.0.1

server-id = 1

</pre>

  • bind-address: the IP address that MySQL will listen on. Set to mysql-2’s private IP address
  • server-id: the unique server ID. Since this is the master server, change this value to 2 and uncomment this line

The two lines should look like this (be sure to substitute the highlighted with database server’s private IP address):

<pre>
bind-address = <span class=“highlight”>mysql2private_IP</span>
server-id = 2
</pre>

Save and quit. Restart MySQL to put the changes into effect:

sudo service mysql restart

Enter the MySQL console:

mysql -u root -p

Next, we will connect the slave to the master. The five following values are required:

  • MASTER_HOST: set to mysql-1’s private IP
  • MASTER_USER: set to the replication user that we created on the master, repl
  • MASTER_PASSWORD: set to repl’s password, which should be substituted with your own password
  • MASTERLOGFILE: set to the “File” listed when you ran SHOW MASTER STATUS; on your master MySQL server
  • MASTERLOGPOS: set to the “Position” listed when you ran SHOW MASTER STATUS; on your master MySQL server

The following statement connects your slave to your master server, and it requires that you substitute all of the highlighted fields with the appropriate values:

<pre>
CHANGE MASTER TO
MASTERHOST=’<span class=“highlight”>mysql1privateIP</span>’,
MASTERUSER='repl’,
MASTERPASSWORD=’<span class=“highlight”>replpassword</span>’,
MASTERLOGFILE=’<span class=“highlight”>mysql-bin.000001</span>’,
MASTERLOG_POS=<span class=“highlight”>408</span>;
</pre>

If that statement ran properly, run this command to initiate the slave connection:

START SLAVE;

Your mysql-2 server should be connected as a slave now! Run the following command to check that the replication is working:

SHOW SLAVE STATUS\G

Revoke Write Privileges From Slave Users

This is optional because the HyperDB plugin can be configured to only read from your slave database server, but you may want revoke the write privileges from your wordpressuser database users on your slave database (because updates to your slave will not be replicated to your master, if you accidentally update your slave somehow).

On mysql-2, from your MySQL console run the following statement to list your database users:

<pre>
SELECT user,host FROM mysql.user;
</pre> <pre> Output: +——————+—————-+ | user | host | +——————+—————-+ | repl | % | | wordpressuser | <span class=“highlight”>wordpress1IP</span> |
| wordpressuser | <span class=“highlight”>wordpress2IP</span> |

</pre>

You should see output similar to the above code block. You may view privileges for each user with the following command:

<pre>
SHOW GRANTS FOR <span class=“highlight”>wordpressuser</span>@<span class=“highlight”>wordpress1IP</span>;
</pre>

In this example, we have one wordpressuser for each WordPress server, so we will revoke the insert, update, and delete privileges from each of them (“wordpress” is the name of our database in this example):

<pre>
REVOKE INSERT, UPDATE, DELETE ON <span class=“highlight”>wordpress</span>.* FROM ’<span class=“highlight”>wordpressuser</span>’@’<span class=“highlight”>wordpress1privateIP</span>’;
REVOKE INSERT, UPDATE, DELETE ON <span class=“highlight”>wordpress</span>.* FROM ’<span class=“highlight”>wordpressuser</span>’@’<span class=“highlight”>wordpress2privateIP</span>’;
FLUSH PRIVILEGES;
</pre>

Now your MySQL replication setup is complete. Let’s move on to setting up WordPress to use both database servers properly.

Install and Configure HyperDB

We will use HyperDB to determine where to send updates (your master database) and read requests (your master and slave). Let’s download it to your home directory from the WordPress Plugin Directory (also install zip/unzip to unarchive it):

cd ~; wget http://downloads.wordpress.org/plugin/hyperdb.zip sudo apt-get install zip unzip hyperdb.zip

It should be unarchived to a directory called “hyperdb”, in your home directory. Copy the sample configuration file to your WordPress installation (substitute the highlighted with your WordPress installation path), and open it for editing:

<pre>
cp ~/hyperdb/db-config.php <span class=“highlight”>/var/www/example.com</span>/
vi <span class=“highlight”>/var/www/example.com</span>/db-config.php </pre>

Look for the second occurrence of DB_HOST, which should be directly after some comments that describe setting up a slave and it should look exactly like the following:

<pre>
$wpdb->adddatabase(array(
'host’ => <span class=“highlight”>DBHOST</span>, // If port is other than 3306, use host:port.
'user’ => DBUSER,
'password’ => DBPASSWORD,
'name’ => DB_NAME,
'write’ => 0, 'read’ => 1, 'dataset’ => 'global’, 'timeout’ => 0.2, )); </pre>

The first occurrence of DBHOST defines the master database server, and the second occurrence defines the slave database server (denoted by the 'write' => 0,). Replace the second occurrence of `DBHOSTwithDBSLAVE1`:

<pre>
'host’ => <span class=“highlight”>DBSLAVE1</span>, // If port is other than 3306, use host:port.
</pre>

Save and exit. Next you will want to define DB_SLAVE_1 in your wp-config.php, which HyperDB will use as a slave database host. Open wp-config.php for editing:

<pre>
vi <span class=“highlight”>/var/www/example.com</span>/wp-config.php
</pre>

Find the line that defines DB_HOST and add the following line under it, substituting your slave’s private IP address (mysql-2):

<pre>
define('DBSLAVE1’, ’<span class=“highlight”>mysql2private_IP</span>’);
</pre>

Then save and exit.

Finish the HyperDB installation by copying the db.php file to the wp-content directory in your WordPress installation, then disabling write access to it:

<pre>
cp ~/hyperdb/db.php <span class=“highlight”>/var/www/example.com</span>/wp-content/</span>
sudo chmod a-w <span class=“highlight”>/var/www/example.com</span>/wp-content/db.php </pre>

Then update the ownership of your wordpress files to their appropriate values (in this tutorial, we have been using www-data for the user/group ownership):

<pre>
sudo chown -R www-data:www-data <span class=“highlight”>/var/www/example.com</span>/
</pre>

Now your WordPress read requests will be served by both your master and slave databases, while updates will be sent to your master (which will then be replicated to your slave).

Conclusion

Now that you have completed your MySQL replication and HyperDB setup, your database environment will be able to handle increased read traffic i.e. more concurrent users! Remember that you can add more MySQL slaves if you want to scale your database serving capacity even more.

<div class=“author”>By Mitchell Anicas</div>

 

 

 

https://www.digitalocean.com/community/tutorials/how-to-optimize-wordpress-performance-with-mysql-replication-on-ubuntu-14-04

블로그 이미지

remoted

Remoted's IT LAB & POST DATABASE

댓글을 달아 주세요

I have some bad data in my database table. I want to replace all &amp; or &amp;amp; or &amp;amp;amp or &amp;amp;amp;amp to & only.

In java it is working fine. how to do in SQL?

 

Java:

String[] names = new String[] { "Ravi Suthar", "Ravi &amp; Suthar", "Ravi &amp;amp; Suthar", "Ravi &amp;amp;amp; Suthar", "Ravi &amp;amp;amp;amp; Suthar" }; for (String name : names) { System.out.println(name.replaceAll("&amp;[amp;]*", "&")); }

 

SQL:

UPDATE tablename SET columnname=REPLACE(columnname,'&amp;[amp;]*','&');

 

 

 

 

 

Following sql will replace &amp; or &amp;amp; or &amp;amp;amp or &amp;amp;amp;amp or its sequence to &

UPDATE tablename SET columnname = REPLACE(REPLACE(columnname, '&amp;', '&'), 'amp;', '');

or

UPDATE tablename SET columnname = REPLACE(columnname , 'amp;', '')

블로그 이미지

remoted

Remoted's IT LAB & POST DATABASE

댓글을 달아 주세요

게시판 데이터에 첨부파일 경로가 변경이 되었거나 그 외 일괄 변경이 필요한 경우 REPLACE 함수를 이용해 쉽게 DB 데이터를 변경 할 수 있습니다.

소개해드리는 예제는 myTable이라는 테이블의 content_data 데이터 중 /upload1/aaa/ 문자를 /upload2/bbb/ 로 치환하는 과정입니다.

 

1. 변경할 데이터 조회 하기

: 우선 변경 될 대상의 레코드를 확인해보시기 바랍니다. 간혹 의도치 않은 데이터가 변경 되는 것을 방지하기 위한 확인 작업입니다.

 

 

 

 

 

MySQL

 

1

SELECT * FROM myTable WHERE content_data LIKE '%/upload1/aaa/%';

 

2. REPLACE로 치환 될 데이터 확인 (SELECT)

: 문자를 치환한 후 문제가 발생하는 것을 방지하기 위해 SELECT 문으로 치환 후 대상 값을 확인합니다. 이때 SElECT문으로 조회하는 것이기 때문에 REPLACE 함수로 인한 데이터 변경은 없습니다.

 

 

 

 

 

MySQL

 

1

SELECT REPLACE(content_data, '/upload1/aaa/', '/upload2/bbb/') AS 'replaced_data' FROM myTable;

 

3. 데이터 치환 실행하기 (UPDATE)

: 1번과 2번에서 모두 이상이 없다면 실제로 치환을 실행합니다.

 

 

 

 

 

MySQL

 

1

UPDATE myTable SET content_data = REPLACE(content_data, '/upload1/aaa/', '/upload2/bbb/');

 

* 조건을 추가하고 싶으시다면 1, 2, 3번 쿼리문 뒤에 WHERE 절을 추가해주시면 됩니다. 위 예제는 조건이 없는 전체 데이터를 대상으로한 내용입니다.

블로그 이미지

remoted

Remoted's IT LAB & POST DATABASE

댓글을 달아 주세요

innodb 설치 및 옵션

DB 2018. 5. 17. 02:10

1. 설치가 안되어 있을 때

1) 설치 여부 확인

mysql> show variables like 'have_innodb';

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

| Variable_name | Value |

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

| have_innodb   | NO   |

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

1 row in set (0.00 sec)

 

2) 2가지의 설치 방법

@ mysql 설치 시에 configure 추가 설치(or 재컴파일 설치)

configure 시에 option 추가( --with-plugins=innobase)

./configure --prefix=/usr/local/mysql --with-charset=euckr --with-extra-charsets=all --with-plugins=innobase                                       옵션 추가

설치된 mysql plugins 설치

my.cnf 파일 설정 추가

#vi /etc/my.cnf

[mysqld] [mysqld] 밑에 2줄 추가

ignore-builtin-innodb                                      

plugin-load=innodb=ha_innodb_plugin.so

mysql 재시작

 

2. 설치는 되어있지만 사용 설정이 없을 때

1) 설치 여부 확인

mysql> show variables like 'have_innodb';

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

| Variable_name | Value    |

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

| have_innodb   | DISABLED |

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

1 row in set (0.00 sec)


2) my.cnf 설정 변경

#vi /etc/my.cnf                                              내용 중에 skip-innodb검색 후 주석처리

#skip-innodb

Mysql 재시작

 

3. innodb default 설정

#vi /etc/my.cnf

[mysqld]

default-table-type=innodb                                추가 후 재시작

 

4. 발생할 수 있는 에러

1) InnoDB: Error: data file /usr/local/mysql/data/ibdata1 is of a different size (innodb 에러)

#vi /usr/local/mysql/var/[도메인].err                                          로그 확인 시

InnoDB: Error: data file /usr/local/mysql/data/ibdata1 is of a different size

InnoDB: 640 pages (rounded down to MB)

InnoDB: than specified in the .cnf file 128000 pages!

InnoDB: Could not open or create data files.

InnoDB: If you tried to add new data files, and it failed here,

InnoDB: you should now edit innodb_data_file_path in my.cnf back

InnoDB: to what it was, and remove the new ibdata files InnoDB created

InnoDB: in this failed attempt. InnoDB only wrote those files full of

InnoDB: zeros, but did not yet use them in any way. But be careful: do not

InnoDB: remove old data files which contain your precious data!

 

#vi /etc/my.cnf                                                                   설정 내역 확인

innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend          10M로 변경

2) InnoDB 실제 pages(640 Pages)과 my.cnf pages(128000 Pages)이 틀려 발생하는 에러

1 page = memory 16Kbyets

640 pages = 10Mbytes

128000 pages = 2000Mbytes

 

관련링크 : http://www.mail-archive.com/mysql@lists.mysql.com/msg76029.html

 

5. 옵션 설명

#vi /etc/my.cnf

 

[mysqld]

……

innodb_data_home_dir = /usr/local/mysql/data/innordb/

innodb_log_group_home_dir = /usr/local/mysql/data/innordb/

innodb_data_file_path = innordata1:256M:autoextend:max:2000M

innodb_autoextend_increment = 10M

innodb_file_per_table

innodb_buffer_pool_size = 2G

innodb_additional_mem_pool_size = 20M

innodb_flush_log_at_trx_commit = 2

innodb_log_file_size = 512M

innodb_log_buffer_size = 2M

innodb_lock_wait_timeout = 50

innodb_flush_method = O_DSYNC

max_connections = 500

 

A. innodb_data_home_dir

 : 테이블스페이스 파일의 생성 위치 설정

B. innodb_data_file_path

 : 테이블스페이스 파일 명 및 크기옵션 설정

innordata1:256M:autoextend:max:2000M

innordata1라는 파일명으로 생성되고 256MB의 고정크기로 최초 생성되며용량이 256MB가 넘을 경우 “autoextend”라는 옵션으로 자동으로 파일 크기가 확장되며최대 확장되는 크기는 MAX 옵션의 설정 값만큼 확장

C. innodb_autoextend_increment

 : autoextend 옵션으로 자동 확장되는 크기 지정디폴트는 8M

D. innodb_file_per_table

 : 공용 테이블스페이스 사용 대신에 테이블 별 테이블스페이스 사용 옵션

TableName.idb 파일 생성

E. innodb_log_group_home_dir

 : 로그 파일에 대한 디렉토리 경로 설정

F. innodb_buffer_pool_size

 : 자신의 테이블에 있는 데이터와 인덱스를 캐시하기 위해 사용하는 메모리 버퍼의 크기이 값을 크게 설정하면 할수록테이블에 있는 데이터를 접속하는데 필요한 I/O가 덜 생김전체 메모리의 60~80%로 설정

G. innodb_additional_mem_pool_size

: InnoDB가 데이터 디렉토리 정보와 다른 내부 데이터 구조를 저장하기 위해 사용하는 메모리 풀의 크기

H. innodb_flush_log_at_trx_commit

자신의 테이블에 있는 데이터와 인덱스를 캐시하기 위해 사용하는 메모리 버퍼의 크기이 값을 크게 설정하면 할수록테이블에 있는 데이터를 접속하는데 필요한 I/O가 덜 생김전체 메모리의 60~80%로 설정

I. innodb_flush_log_at_trx_commit

: commit 을 하였을때 그 즉시 commit 된 데이터를 log file 에 기록할지 안할지를 설정

0 - log buffer내용이 1초 간격으로 로그파일에 쓰여지고 flush, commit시 미동작

1 - log buffer내용이 commit할 때에 로그 파일에 쓰여지고 flush

2 - log buffer내용이 commit할 때에 로그 파일에 쓰여지고 flush 1초 간격으로 동작

J. innodb_log_buffer_size

: InnoDB가 로그 파일을 디스크에 쓰기 위해 사용하는 버퍼의 크기사용 가능한 크기는 1~8MB, 디폴트는1MB.

K. innodb_log_file_size

로그 파일의 크기 설정디폴트는 5MB, 로그 파일은 하나의 파일에 계속 저장되는 것이 아니고 순차적으로 파일이 일정한 크기와 용량으로 순환식으로 생성, innodb_buffer_pool_size 25% 정도로 설정.

L. innodb_lock_wait_timeout

트랜젝션의 타임아웃은 롤백이 진행되기 전에 락을 대기하는 시간디폴트는 50.

M. innodb_flush_method

: Flush 명령어 방식 설정디폴트는 fdatasync.

fdatasync - fsync()를 사용해서 데이터와 로그 파일을 flush

O_SYNC - 로그 파일을 열고 flush하지만데이터 파일을 flush하기 위해서는 fsync()를 사용

O_DIRECT - O_DIRECT를 사용해서 데이터 파일을 열고데이터 파일과 로그 파일을 flush (몇몇 GNU/Linux 버전에서 사용 가능함)

 Windows에서는 flush 방식은 항상 async_unbuffered 사용

N. innodb_thread_concurrency

: InnoDB 내부에 OS 쓰레드의 숫자 설정설정된 값과 같거나 적게 유지권장하는 값은 여러분이 사용하는 시스템의 프로세스와 디스크의 전체 합설정 값의 범위는 0~100.

 

3. InnoDB Table 생성(Default Engine InnoDB가 아닐 경우)

 . Table 생성시

  : InnoDB 테이블을 생성하기 위해서는, CREATE TABLE 명령문에서 ENGINE = InnoDB 옵션을 지정한다:

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

 . MyISAM 테이블을 InnoDB로 변환

: Alter문 사용하여 변경

 ALTER TABLE tablename ENGINE=INNODB;

블로그 이미지

remoted

Remoted's IT LAB & POST DATABASE

댓글을 달아 주세요

MySQL 오늘기준으로 최근 한달 동안 데이터 가져오기

 

긴 말이 필요 없습니다. 기준이 되는 데이터 칼럼만 있다면 BETWEEN 과 DATE_ADD() 함수를 이용해서

간단하게 가져올 수가 있습니다.

 

SELECT seq, title FROM TABLE WHERE DATE_COLUMN BETWEEN DATE_ADD(NOW(),INTERVAL -1 MONTH ) AND NOW();

 

그럼 오늘 기준으로 최근 일주일 동안 데이터를 가져올려면 어떻게 해야 할까요.

아래와 같이 MONTH를 WEEK로만 바꿔 주시면 됩니다.

 

SELECT seq, title FROM TABLE WHERE DATE_COLUMN BETWEEN DATE_ADD(NOW(),INTERVAL -1 WEEK ) AND NOW();

 

 

 

From : 높이뜬새(www.webmadang.net)

 


블로그 이미지

remoted

Remoted's IT LAB & POST DATABASE

댓글을 달아 주세요

등록되어있는 코드를 보려면 다음 명령을 실행한다.

[프로시져 목록 확인방법]
mysql> show procedure status 

[프로시져 스크림트 확인방법]
mysql> show create procedure uso_Get_Mini_Evaluation

[뷰 스크립트 확인방법]
mysql> show create view v_core_ability_examination



출처: http://sangmin.tistory.com/1553 [Pell's seer Blog]

출처: http://sangmin.tistory.com/1553 [Pell's seer Blog]

블로그 이미지

remoted

Remoted's IT LAB & POST DATABASE

댓글을 달아 주세요


개요

MySQL 의 default character set 은 MySQL 의 설정 파일에 설정할 수 있다.

배포판
위치
RHEL/CentOS /etc/my.cnf
Ubuntu /etc/mysql/mysql.conf.d/mysqld.cnf


명시적으로 설정하지 않으면 기본 캐릭터 셋은 latin1 이 된다. 현재 characterset 확인은 다음과 같이 mysql 클라이언트로 연결한 후에 status 명령어로 알수 있다.

또는 다음과 같이 mysql 콘솔에서 show variable 을 사용해도 된다.

mysql> show variables like 'char%';


redmine, gitlab 등 거의 모든 app 들은 charset 으로 UTF-8 을 권장하고 있고 실제로도 UTF-8 을 사용하는게 정신건강에 좋다.

MySQL 5.1 과 5.5 이상은 my.cnf 에 캐릭터 셋 설정하는 방식이 약간 다르다.


설정

MySQL 5.5 이상

MySQL 5.5 부터는 default-character-set 옵션이 제거되었고 character-set-server 만 설정해 주면 된다.

skip-character-set-client-handshake 가 빠지면 client 의 시스템 encoding 을 사용하는데 윈도의 경우 euckr 로 설정 될수 있으므로 필요하다.

MySQL 5.5
[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8
skip-character-set-client-handshake


이제 service mysqld restart 로 재구동후에 mysql client 에서 status 명령어로 보면 charset 이 utf-8 로 변경되었을 것이다.


MySQL 5.1

5.1 은 다음과 같이 설정해야 한다.

MySQL 5.1
[mysqld]
character-set-server = utf8
  
[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8

UTF8-MB 

Emoji 는 기존 utf-8 로는 표현을 못하므로 MySQL에서 이모지를 저장하려면 4바이트의 가변 길이를 갖는 utf8mb4 캐릭터 셋을 사용해야 한다. (MySQL 5.5.3 이상 필요)

[mysqld]
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4
skip-character-set-client-handshake


이미 기존에 만들어진 database 라면 alter database 명령어로 캐릭터 셋과 collate 를 변경할 수 있다.


character set 변경
ALTER DATABASE homestead CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


같이 보기

Ref

utf8mb4_unicode_ci 

블로그 이미지

remoted

Remoted's IT LAB & POST DATABASE

댓글을 달아 주세요

List stored procedures in MySQL

Posted in MySql - Last updated Oct. 05, 2011

This post shows how to get a complete list of stored procedures in a MySQL database and then to see what code is used in the stored procedure.

List MySQL stored procedures

Run the following SQL query either from the MySQL command line, or using a GUI tool like phpMyAdmin to get a complete list of stored procedures from all databases your login has access to:

SHOW PROCEDURE STATUS

To just list procedures from a particular database do this, where we want to query stored procedures from the "mydb_abc" database:

SHOW PROCEDURE STATUS WHERE Db = 'mydb'

The output from the above commands will look something like this:

+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name    | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mydb | mysproc | PROCEDURE | root@%  | 2011-08-18 20:29:53 | 2011-08-18 20:29:53 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

Show the SQL code in the stored procedure

Use the show create procedure query to get the SQL code from the query. To get it for the "mysproc" stored procedure in the example output above, do this:

SHOW CREATE PROCEDURE mysproc

This then gives you a few fields as a resultset and it's the "Create Procedure" column which has the procedure creation SQL.

블로그 이미지

remoted

Remoted's IT LAB & POST DATABASE

댓글을 달아 주세요