When failover or scaling is getting more important day by day as in today's web services, database replication will do some critical role, I suppose. So, I write some mysql-replication settings and testing to be helpful for me and somebody want it.
Purpose: replication on existing mysql db server's data(using mysqldump utility) and setup master-slave replication.
Check shall be done by using wordpress hyperdb plugin if master is write,read and slave's read-only works.
Prerequsities: Ubuntu server 14.04. Master's LAMP+wordpress(4.2.1) already should installed. And secondary slave server wieh same mysql-server version.
Env:
Master mysql host is running wordpress data on wordpress database.
To replicate mysql host master to slave mysql server, first should copy master data to mysql slave server.
And rest process is very similiar to basic fresh mysql install replication.
This test has been done on ubuntu 14.4 trusty mysql-server-5.5. Master server is running apache2+php5+mysql-server5.5.
10.0.0.7 is master,10.0.0.9 is slave.
MySQL-server-5.5 default engine type is Innodb.
mysql> show engines\G; command will show like below.
......others.showing.......
*************************** 9. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
9 rows in set (0.00 sec)
root@master:~# mysqld --version
mysqld Ver 5.5.43-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))
root@master:~#
root@slave:/var/lib/mysql# mysqld --version
mysqld Ver 5.5.43-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))
#Backup on master server
root@master:~# mysqld -u root -p
...
mysql>FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;
root@master:~# mysqldump -uroot -p --single-transaction --routines --triggers wordpress > wordpress_db.sql
#Compression and copying data to slave.
root@master:~# tar cvzfp wordpress_db.sql.tar.gz wordpress_db.sql
wordpress_db.sql
root@master:~# ls
local wordpress_db.sql wordpress_db.sql.tar.gz
root@master:~# scp wordpress_db.sql.tar.gz vagrant@10.0.0.9:/home/vagrant/
wordpress_db.sql.tar.gz 100% 89KB 89.5KB/s 00:00
root@slave:/home/vagrant# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.5.43-0ubuntu0.14.04.1 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database wordpress;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
#Recovery data to slave server.
root@slave:/home/vagrant# mysql -p wordpress < wordpress_db.sql
Enter password:
root@slave:/home/vagrant#
#Checking recovery.
root@slave:/home/vagrant# mysql -p wordpress
Enter password:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
+-----------------------+
| Tables_in_wordpress |
+-----------------------+
| wp_commentmeta |
| wp_commenmaster |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posmaster |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_terms |
| wp_usermeta |
| wp_users |
+-----------------------+
11 rows in set (0.00 sec)
mysql>
#Master server configuration insert to my.cnf configuration file.
root@master:~# vi /etc/mysql/my.cnf
bind-address = 10.0.0.7
server-id = 7
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = wordpress
sync_binlog=1
#Master status check.
root@master:~# mysql -p
Enter password:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 107 | wordpress | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#Granting replication to slave server 10.0.0.9.
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'10.0.0.9' IDENTIFIED BY 'pass';
Query OK, 0 rows affected (0.00 sec)
#Restart
root@slave:/home/vagrant# service mysql restart
mysql stop/waiting
mysql start/running, process 10589
#Slave configuration edit.
root@slave:/home/vagrant# vi /etc/mysql/my.cnf
#other setting goes on
bind-address = 10.0.0.9
#
#other basic settings
server-id = 9
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1
root@slave:/home/vagrant# mysql -u root -p
#Now implementation
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.7',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='pass',
-> MASTER_LOG_FILE='mysql-bin.000005',
-> MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.02 sec)
#Still, Slave_IO_Running: No,Slave_SQL_Running: No
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.7
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
ERROR:
No query specified
#Let's start slave. Must check if "Slave_IO_Running: Yes,Slave_SQL_Running: Yes" or not.
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.7
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 275
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 421
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 275
Relay_Log_Space: 577
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7
1 row in set (0.00 sec)
ERROR:
No query specified
#Now if you post new articles or comment wordpress site,you should see also that data on slave server.
#On master
mysql>SET GLOBAL read_only = OFF;
UNLOCK TABLES;
mysql> use wordpress;
Database changed
mysql> create table test_repl (
-> id int(3),
-> name varchar(30));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test_repl values(1,'OYJ')
-> ;
Query OK, 1 row affected (0.00 sec)
#On slave, let's check if replication is on.
mysql> use wordpress;
Database changed
mysql> desc test_repl;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from test_repl;
+------+------+
| id | name |
+------+------+
| 1 | OYJ |
+------+------+
1 row in set (0.00 sec)
#Now it is ok.
#To utilize this setting, developers should code update and insert job on master,and read implementaion on both servers.#
#Master is write and read, slave only read configuration.
/**There is good wordpress plugin to check this set-up=>hyperdb#
Now ...let's configure hyperdb. And check if it is working or not.*/
#Using hyper-db
#ON where apache and wordpress installed.
root@ts:~/hyperdb# cd ~; wget http://downloads.wordpress.org/plugin/hyperdb.zip
root@ts:~# unzip hyperdb
root@ts:~# cp hyperdb/db-config.php /var/www/html/wordpress/
#Slave configuration
root@ts:~# vi /var/www/html/wordpress/db-config.php
/**
Setting explanation goes on...
*/
//wp-config.php's default configuration applies below.
$wpdb->add_database(array( // master server setting.
'host' => DB_HOST, // If port is other than 3306, use host:port.
'user' => DB_USER,
'password' => DB_PASSWORD,
'name' => DB_NAME,
));
/**
* This adds the same server again, only this time it is configured as a slave.
* The last three parameters are set to the defaults but are shown for clarity.
* Change to slave server's ip etc.
*/
$wpdb->add_database(array( //slave setting.
''host' => DB_SLAVE_1, //DB_HOST, // If port is other than 3306, use host:port.
'user' => DB_USER, //DB_USER,
'password' => DB_PASSWORD, //DB_PASSWORD,
'write' => 0,
'read' => 1,
'dataset' => 'global',
'timeout' => 0.2,
));
#I have to edit default wp-config.php
root@ts:/var/www/html/wordpress/wp-content# vi /var/www/html/wordpress/wp-config.php
/** other settings..*/
/** MySQL hostname(existing) */
define('DB_HOST', 'localhost');
/** Slave replica1 should be added to slave read replica */
define('DB_SLAVE_1', '10.0.0.9');
root@ts:~/hyperdb# chmod a-w db.php; cp db.php /var/www/html/wordpress/wp-content/; chown -R www-data:www-data /var/www/html/wordpress/
root@ts:~/hyperdb#
#Now stop master server,and check wordpress should still read from slave. If not, there is a configuration problem and db priviliege check should be done #
#In my case, I set replicate wordpress db only, so grant permission had to be done on slave(10.0.0.9) by issuing "mysql> GRANT ALL PRIVILEGES ON wordpress.* to wordpress_db_user@'10.0.0.7' IDENTIFIED BY 'wordpresspass';" via mysql shell mysql>.#
#Strictly checking, post won't be possible when master is dead. But, if master is up, write(insert) and update must be possible(it takes very negligible minor time gap til master works again though)#
No comments:
Post a Comment