Wper主从搭建
主服务器 192.168.192.91
从服务器192.168.192.54
master:
my.cnf中添加
server-id=1
log-bin=mysql-bin
重启服务
加锁并备份数据:
- mysql>flush tables with read lock
- mysql> show master status;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000001 | 482745 | | |
- +------------------+----------+--------------+------------------+
- #mysqldump --databases bbs_weand_com > /root/bbs_weand_com.sql //这条命令需要另开一个终端执行
- mysql>unlock tables;
- #scp /root/bbs_weand_com.sql 192.168.192.54:/disk2
1 row in set (0.00 sec)
授权:
- mysql>grant replication client,replication slave on *.* to backup@'192.168.192.%' identifided by 'XXXX';
- mysql>flush privileges;
- mysql>show grants for backup@'192.168.192.%';
slave:
配置文件添加
- [mysqld7]
- socket = /disk2/mysql5.0/data/mysql7_wper/mysql.sock
- port = 3307
- pid-file = /disk2/mysql5.0/data/mysql7_wper/mysql.pid
- datadir = /disk2/mysql5.0/data/mysql7_wper/
- user = mysql
- server-id = 30
- log-bin = mysql-bin
- expire_logs_days = 7
- master-host=192.168.192.91
- master-port=3306
- master-user=backup
- master-password=weiphone
- replicate_do_db=bbs_weand_com
开启服务
- #mysql -uroot -h127.0.0.1 -P3307 </disk2/bbs_weand_com.sql
- #mysql -uroot -h127.0.0.1 -P3307
- mysql>stop slave;
- mysql> CHANGE MASTER TO MASTER_HOST='192.168.192.91',MASTER_USER='backup',MASTER_PASSWORD='XXXX',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=482745;
- mysql>start slave;
依据脚本格式为monitor授权:
- mysql> GRANT SUPER, REPLICATION SLAVE ON *.* TO 'monitor'@'192.168.192.%' IDENTIFIED BY PASSWORD '*330BC159EEB68E28E5BB0EACD81C6735A8677136';
为mysql用户授权关机:
- mysql> grant shutdown on *.* to mysql@localhost identified by 'BBBBXXX';
- mysql>flush privileges;
添加监控脚本内容如下:
- <?php
- #include('db_config.php');
- $_CONFIG = array(
- ('mysql_host_w'=>'192.168.192.54:3307','mysql_user_w'=>'monitor','mysql_pwd_w'=>'AAAXXX'),
- );
- foreach($_CONFIG as $k => $v){
- $err_no = 1;
- $conn = mysql_connect($v['mysql_host_w'],$v['mysql_user_w'],$v['mysql_pwd_w']);
- $result = mysql_query("show slave status") or print_r(mysql_error());
- $row = mysql_fetch_array($result);
- mysql_close($conn);
- //print_r($row);
- switch ($row['Slave_SQL_Running']){
- case 'Yes' :
- $err_no2 = 0; break;
- case 'No' :
- $err_no2 = 1; break;
- default :
- $err_no2 = 2; break;
- }
- switch ($row['Slave_IO_Running']){
- case 'Yes' :
- $err_no = 0; break;
- case 'No' :
- $err_no = 3; break;
- default :
- $err_no = 4; break;
- }
- //$err_no |= $row['Slave_SQL_Running'] =='Yes'?0:1;
- //$err_no |= $row['Slave_IO_Running'] == 'Yes'?0:2;
- //$row['c'] = ($row['Slave_SQL_Running'] =='Yes' && $row['Slave_IO_Running'] == 'Yes')?1:0;
- //echo 'TIME=' .date('Y-m-d H:i:s') .'NUM=' .$err_no;
- if($err_no == 0 & $err_no2 == 0 )
- echo '0|mysql slave successful '.$v['mysql_host_w']."<br>
- ";
- elseif($err_no == 1 | $err_no2 == 1)
- echo '1|mysql slave fail error_no:'.$err_no.','.$v['mysql_host_w']." | Slave_SQL_Running:".$row['Slave_SQL_Running']."<br>
- ";
- elseif($err_no == 3 | $err_no2 ==3 )
- echo '3|mysql slave fail error_no:'.$err_no.','.$v['mysql_host_w']." | Slave_IO_Running:".$row['Slave_IO_Running']."<br>
- ";
- else
- echo '4|mysql slave error_no:'.$err_no.','.$v['mysql_host_w']." | Slave_SQL_Running:".$row['Slave_SQL_Running']."<br>
- ";
- }
转载于:https://blog.51cto.com/leezqang/945670