MySQL Replication Master-Slave筆記

Master(my.cnf)192.168.1.203
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

binlog_format = MIXED
要同步的DB
binlog_do_db = test
binlog_do_db = test2
innodb_flush_log_at_trx_commit=1
sync_binlog=1

mysql -u root -p
新增帳號給SLAVE同步使用
mysql>GRANT REPLICATION SLAVE ON *.* TO 'rep' IDENTIFIED BY 'pass';
or
mysql -u root -p -e "GRANT REPLICATION SLAVE ON *.* TO 'rep' IDENTIFIED BY 'pass';"
先鎖定資料庫
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"
mysql -u root -p -e "SHOW MASTER STATUS\G;"
+------------------+----------+--------------+------------------+
| File Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
記下File與Position，等下會用到
quit

匯出要同步的那個資料庫
mysqldump -uroot -p --flush-logs --opt --master-data test > test.sql

解除鎖定

mysql -u root -p -e "UNLOCK TABLES;"

此時MASTER上的工作結束。

Slave(my.cnf)192.168.1.204

server-id               = 2

要同步的DB
replicate_wild_do_table = test.%

mysql -uroot -p -e "STOP SLAVE; RESET SLAVE;"

mysql -uroot -p -e "CHANGE MASTER TO MASTER_HOST='192.168.1.204',MASTER_USER='rep',MASTER_PASSWORD='pass',
MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=107;"

mysql -uroot -p -e "START SLAVE;"

檢視MASTER的狀態
mysql -uroot -p -e "SHOW MASTER STATUS;"
+------------------+----------+--------------+------------------+
| File|Position| Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

檢視SLAVE的狀態
mysql -uroot -p -e "SHOW SLAVE STATUS\G;"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.204
Master_User: rep
Master_Port: 3306
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......

運作正常，此時可到MASTER上新增資料看是否立即同步至SLAVE

Windows下於tomcat內架設JavaBridge

下載tomcat可攜版,假設tomcat放在c:\tomcat下

1.設定tomcat可正常執行

設定系統環境變數
CATALINA_HOME=c:\tomcat
Path=......;%CATALINA_HOME%\lib;%CATALINA_HOME%\lib\servlet-api.jar;%CATALINA_HOME%\lib\jsp-api.jar

編輯tomcat/bin/startup.bat加入以下
set "JAVA_HOME=C:\Program Files (x86)\Java\jre7"
set "CATALINA_HOME=c:\tomcat"

2.複製JavaBridge.war至tomcat/weapps/下，移除webapps下其他目錄

tomcat會建立tomcat/webapps/JabaBridge目錄,此時JavaBridge.war可移除

從C:\tomcat\Webapps\JavaBridge\WEB-INF\lib複製JavaBridge.jar, php-servlet.jar and php-script.jar
到C:\tomcat\lib\

3.編輯web.xml加入以下

<listener><listener-class>php.java.servlet.ContextLoaderListener</listener-class></listener>
<servlet><servlet-name>PhpJavaServlet</servlet-name><servlet-class>php.java.servlet.PhpJavaServlet</servlet-class>
</servlet>
<servlet><servlet-name>PhpCGIServlet</servlet-name><servlet-class>php.java.servlet.fastcgi.FastCGIServlet</servlet-class>
<init-param><param-name>prefer_system_php_exec</param-name><param-value>On</param-value></init-param>
<init-param><param-name>php_include_java</param-name><param-value>Off</param-value></init-param>
</servlet>
<servlet-mapping><servlet-name>PhpJavaServlet</servlet-name><url-pattern>*.phpjavabridge</url-pattern> </servlet-mapping>
<servlet-mapping><servlet-name>PhpCGIServlet</servlet-name><url-pattern>*.php</url-pattern></servlet-mapping>

4.測試

執行tomcat/bin/startup.bat來啟動tomcat

於apache下之javatest.php
require_once("http://127.0.0.1:8080/JavaBridge/java/Java.inc");
$j = new Java('java.lang.system');
echo $j->getProperty('java.version');