How to: Setup Mysql Database Master and Slave with HyperDB on WordPress

This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

1 Configure the Master

Edit /etc/mysql/my.cnf:

Under the section [mysqld] add the following lines or comment out if already present

#skip-networking
#bind-address = 127.0.0.1

And again in /etc/mysql/my.cnf under [mysqld] section add

server-id=1
skip-name-resolve
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=wpdb

Replace wpdb with your database name

Restart the mysql server

root@master# /etc/init.d/mysql restart

Then we log into the MySQL shell as root and create a user with replication privileges:

root@master# mysql -u root -p
Enter password:

GRANT REPLICATION SLAVE ON wpdb.* TO 'slave_user'@'%' IDENTIFIED BY 'some_password';
FLUSH PRIVILEGES;

Replace wpdb, slave_user, some_password with your data.. also % can be replaced with your slave server ip address

Next, on same MySQL shell

USE wpdb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
quit;

It will show something like this:

Write down this information, you will need it later on the slave (in this case the info are mysql-bin.006 and 183)!

Now it is time to dump the wpdb database, on the linux shell prompt run the following command:

root@master# mysqldump -u root -p wpdb >wpdb.sql
Enter password:

Upload your database (wpdb.sql) on the slave server

Now go back to the MySQL shell

root@master# mysql -u root -p
Enter password:

And type

UNLOCK TABLES;
quit;

2 Configure The Slave

On the slave we first need to create the database wpdb:

root@slave# mysql -u root -p
Enter password:

CREATE DATABASE wpdb;
quit;

Import the database dump you made on the master into the newly created wpdb on the slave:

root@slave# mysql -u root -p wpdb 
Now edit /etc/mysql/my.cnf on the slave server

Add the following lines under [mysqld] section

server-id=2
skip-name-resolve
replicate-do-db=wpdb
relay-log=/var/log/mysql/mysql-relay-bin.log
log_bin=/var/log/mysql/mysql-bin.log
master-host=master_ip_address
master-user=slave_user
master-password=some_password
master-connect-retry=60

Replace master_ip_address, slave_user, some_password with your data
In case you have MySQL 5.5+ The following options have been removed, just add to my.cnf the first 5 lines above and skip the master-* ones


–master-host

–master-user

–master-password

–master-port

-master-connect-retry

Restart the MySQL server

root@slave# /etc/init.d/mysql restart

Enter againg the MySQL shell

root@slave# mysql -u root -p
Enter password:


SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='master_ip_address', MASTER_USER='slave_user', MASTER_PASSWORD='some_password', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183, MASTER_PORT=3306;
START SLAVE;
quit;

MASTER_HOST is the IP address or hostname of the master server.

MASTER_USER is the user we granted replication privileges on the master server.

MASTER_PASSWORD is the password of MASTER_USER on the master server.

MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master ( in this case mysql-bin.006 to be replaced with your value ) .

MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master ( In this case 183 to be replaced with your value ).

MASTER_PORT to set which port the master server is listening, optional if set to default 3306, be noted that master_port should not be quoted.
Restart MySQL on both master and slave server
Usefull MySQL commands
Details of slave replication:
SHOW SLAVE STATUS\G
List of MySQL user accounts:
select User,Host from mysql.user;
Change password for a MySQL user:
update user set password=PASSWORD("NEWPASSWORD") where User='your user';
flush privileges;
Add a read-only MySQL user:

CREATE USER 'read_only_username'@'%' IDENTIFIED BY 'password'; 
Safest way to quickly rename a MySQL database:

$ mysqldump -u root -p olddb >~/olddb.sql
$ mysql -u root -p
mysql> create database newdb;
mysql> use newdb
mysql> source ~/olddb.sql
mysql> drop database olddb;

- Setting up HyperDB on WordPress
1 On the Slave server edit /etc/mysql/my.cnf under [mysqld] and change as below:

#bind-address = 127.0.0.1
#skip-networking

Restart MySQL server

root@slave# /etc/init.d/mysql restart

Add a MySQL user

root@slave# mysql -uroot -p
Enter password:
mysql> CREATE USER mysql_user@'wordpress_server_ip_address' IDENTIFIED BY 'mysql_user_password';
mysql> GRANT ALL PRIVILEGES ON wordpress_database_name.* TO mysql_user@'wordpress_server_ip_address';
mysql> flush privileges;
mysql> quit;

Replace mysql_user, wordpress_server_ip_address, mysql_user_password, wordpress_database_name with your data
2 On the WordPress server
Download hyperdb here,unzip and edit hyperdb/db-config.php

root@wordpress# wget https://downloads.wordpress.org/plugin/hyperdb.zip
root@wordpress# unzip hyperdb.zip
root@wordpress# vi hyperdb/db-config.php

Go to line 228

$wpdb->add_database(array(
 'host' => SLAVE_IP_ADDRESS:PORT, // If port is other than 3306, use host:port.
 'user' => mysql_user,
 'password' => mysql_user_password,
 'name' => wordpress_database_name,
 'write' => 2,
 'read' => 1,
 'dataset' => 'global',
 'timeout' => 0.2,
));

Replace SLAVE_IP_ADDRESS:PORT, mysql_user, mysql_user_password, wordpress_database_name with your data
Save and quit the editor
Copy the hyperdb files into the wordpress directories, db-config.php into same directory where wp-config.php is and db.php in the wp-content/ directory

root@wordpress# cp hyperdb/db-config.php /var/www/html/wordpress/
root@wordpress# cp hyperdb/db.php /var/www/html/wordpress/wp-content/

Replace /var/www/html/wordpress/ and /var/www/html/wordpress/wp-content/ with your wordpress paths

Once db.php is in place, hyperdb will be active. To disable remove db.php from wp-content/ directory
How to check if hyperdb is working

root@wordpress# mysql -h SLAVE_IP_ADDRESS -u mysql_user -P 3306 -p
Enter password:

If you can log into MySQL remotely then authentication is working
Shutdown the wordpress MySQL server and from a browser refresh your wordpress website.

If the website opens as usual it means the slave MySQL is working.

To be sure everything is ok restart wordpress MySQL and shutdown Slave MySQL server, refresh the Website and final

check shutdown both MySQL servers to get the usual error.

If between the above tests you are redirected to the initial wordpress installation wizard then add the following code to db-config.php, at the near end of the file, see below

 shm_detach( $segment );
 sem_release( $sem_id );

 return $row[ 'lag' ];
}

*/
function wpdb_connection_error( $host, $port, $op, $tbl, $ds, $dbhname, $wpdb ) 
 dead_db();

$wpdb->add_callback( 'wpdb_connection_error', 'db_connection_error' );
// The ending PHP tag is omitted. This is actually safer than including it.

Reference links
https://dev.mysql.com/doc/index.html
http://MariaDB.org
http://httpd.apache.org
http://nginx.org
https://wordpress.org
https://wordpress.org/plugins/hyperdb/
 
How to: Setup Mysql Database Master and Slave with HyperDB on WordPress