Configure MySQL Master/Slave replication
In this document the package install of MySQL version 5.1.35 is used on Solaris 10 (sparc)
Master Server IP: 192.168.128.101
Slave Server IP: 192.168.128.102
Configure Master Server
Start the MySQL server
# /etc/init.d/mysql start
Allow "root" user access from anywhere
# /opt/mysql/mysql/bin/mysql -uroot -p<password>
GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'password';
Create the "webspace" database
# create database webspace;
Create/Modify the /etc/my.cnf file with following entries. This installation assumes that the MySQL is installed under /opt/mysql/mysql
[mysqld]
server-id=1
basedir=/opt/mysql/mysql
datadir=/var/lib/mysql
old_passwords=1
log-bin=olas-bin # Name the log bin with host name
binlog-do-db=webspace # Enable replication on webspace db
binlog-ignore-db=mysql # Disable replication of mysql db
binlog-ignore-db=test # Disable replication of mysql db
Stop MySQL Server
# /etc/init.d/mysql stop
Start MySQL Server
# /etc/init.d/mysql start
Create replication user "slaveuser" on Master
mysql> grant replication slave on *.* to slaveuser@'192.168.128.102' identified by 'password';
Dump data from the Master
/opt/mysql/mysql/bin/mysqldump -u root --all-databases --single-transaction --master-data=1 > master.sql
Configure Slave Server
Start the MySQL server
# /etc/init.d/mysql start
Allow "root" user access from anywhere
# /opt/mysql/mysql/bin/mysql -uroot -p<password>
GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'password';
Create/Modify the /etc/my.cnf file with following entries. This installation assumes that the MySQL is installed under /opt/mysql/mysql
[mysqld]
# changes made to do slave
server-id=2
basedir=/opt/mysql/mysql
datadir=/var/lib/mysql
old_passwords=1
log-bin=ps-eng40-bin
binlog-do-db=webspace
binlog-ignore-db=mysql
binlog-ignore-db=test
Stop MySQL Server
# /etc/init.d/mysql stop
Start MySQL Server
# /etc/init.d/mysql start
Dump the data into Slave from Master
Copy the file master.sql created during the master configuration to the slave server and run mysql to import into the slave server.
# /opt/mysql/mysql/bin/mysql -uroot -ppassword < master.sql
![]() | Workaround As a workaround for replication to start and work properly, drop the "webspace" database from the slave before enabling the replication.
|
Configure the slave server to its master server
mysql> CHANGE MASTER TO MASTER_HOST='192.168.128.101', MASTER_USER='slaveuser', MASTER_PASSWORD='password';
Start the slave
mysql>start slave;
Check the slave status
mysql>show slave status \G
Check master status. Connect to the master server using mysql tool and run
mysql>show master status;
Configure Web Space Server with Database Read/Write Split
In portal-ext.properties, configure the following parameters (please replace the hostname,user name password applicable to your installation)
spring.configs=\
META-INF/base-spring.xml,\
\
META-INF/hibernate-spring.xml,\
META-INF/infrastructure-spring.xml,\
META-INF/management-spring.xml,\
\
META-INF/util-spring.xml,\
\
META-INF/jcr-spring.xml,\
META-INF/messaging-spring.xml,\
META-INF/scheduler-spring.xml,\
META-INF/search-spring.xml,\
\
META-INF/counter-spring.xml,\
META-INF/document-library-spring.xml,\
META-INF/lock-spring.xml,\
META-INF/mail-spring.xml,\
META-INF/portal-spring.xml,\
META-INF/portlet-container-spring.xml,\
META-INF/wsrp-spring.xml,\
\
META-INF/mirage-spring.xml,\
\
META-INF/ext-spring.xml, \
META-INF/dynamic-data-source-spring.xml
#
# Split jdbc operations
#
jdbc.write.driverClassName=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
jdbc.write.url=jdbc:mysql://master:3306/webspace?emulateLocators=true&useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.write.username=root
jdbc.write.password=password
jdbc.read.driverClassName=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
jdbc.read.url=jdbc:mysql://slave:3306/webspace?emulateLocators=true&useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.read.username=root
jdbc.read.password=password