您好,登錄后才能下訂單哦!
在PHP中實(shí)現(xiàn)MySQL的讀寫(xiě)分離,通常涉及到主從復(fù)制(Master-Slave Replication)的概念。以下是一個(gè)基本的實(shí)現(xiàn)步驟和示例代碼:
首先,你需要配置MySQL的主從復(fù)制。假設(shè)你有一個(gè)主數(shù)據(jù)庫(kù)(Master)和一個(gè)從數(shù)據(jù)庫(kù)(Slave)。
編輯my.cnf
或my.ini
文件:
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=your_database_name
重啟MySQL服務(wù)使配置生效。
編輯my.cnf
或my.ini
文件:
[mysqld]
server-id=2
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1
重啟MySQL服務(wù)使配置生效。
連接到主數(shù)據(jù)庫(kù)并獲取當(dāng)前的復(fù)制狀態(tài):
<?php
$master_host = 'master_host_ip';
$master_user = 'master_username';
$master_password = 'master_password';
$master_db = 'your_database_name';
$conn = new mysqli($master_host, $master_user, $master_password, $master_db);
if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}
$sql = "SHOW SLAVE STATUS\G";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "Slave_IO_Running: " . $row['Slave_IO_Running'] . "\n";
echo "Slave_SQL_Running: " . $row['Slave_SQL_Running'] . "\n";
if ($row['Slave_IO_Running'] == 'Yes' && $row['Slave_SQL_Running'] == 'Yes') {
echo "Replication is running.\n";
} else {
echo "Replication is not running.\n";
}
}
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
根據(jù)讀取和寫(xiě)入操作選擇不同的數(shù)據(jù)庫(kù)連接。
<?php
$write_host = 'master_host_ip';
$write_user = 'master_username';
$write_password = 'master_password';
$write_db = 'your_database_name';
$write_conn = new mysqli($write_host, $write_user, $write_password, $write_db);
if ($write_conn->connect_error) {
die('Connection failed: ' . $write_conn->connect_error);
}
// 插入數(shù)據(jù)示例
$sql = "INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')";
if ($write_conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $write_conn->error;
}
$write_conn->close();
?>
<?php
$read_host = 'slave_host_ip';
$read_user = 'slave_username';
$read_password = 'slave_password';
$read_db = 'your_database_name';
$read_conn = new mysqli($read_host, $read_user, $read_password, $read_db);
if ($read_conn->connect_error) {
die('Connection failed: ' . $read_conn->connect_error);
}
// 查詢數(shù)據(jù)示例
$sql = "SELECT * FROM your_table";
$result = $read_conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
}
} else {
echo "0 results";
}
$read_conn->close();
?>
為了提高性能和穩(wěn)定性,可以考慮使用連接池來(lái)管理數(shù)據(jù)庫(kù)連接。PHP中有許多連接池庫(kù),如PDO
或HikariCP
。
以上是一個(gè)基本的MySQL讀寫(xiě)分離實(shí)現(xiàn)示例。實(shí)際應(yīng)用中可能需要更多的錯(cuò)誤處理和優(yōu)化,例如使用連接池、監(jiān)控復(fù)制狀態(tài)、自動(dòng)故障轉(zhuǎn)移等。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。