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:

• How To Set Up a Remote Database to Optimize Site Performance with MySQL
• Optionally, How To Use HAProxy As A Layer 4 Load Balancer for WordPress Application Servers on Ubuntu 14.04

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

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;', '')

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

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

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

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

MySQL

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

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

MySQL

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

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

MySQL

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

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;