liferay多数据源

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. mysql>drop database webspace;

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