什么是MySQL主從復制

MySQL主從復制是一種數據備份和讀寫分離的方式，主數據庫負責寫操作，從數據庫負責讀操作和備份。主庫將寫操作的數據同步到從庫后，從庫就可以處理讀操作，也可以用于數據備份和故障恢復。

MySQL主從復制配置步驟

以下是MySQL主從復制的配置步驟：

1. 配置主庫：在主庫的my.cnf配置文件中添加以下內容： server-id=1 log_bin=mysql-bin binlog-do-db=your_db_name binlog-ignore-db=mysql

2. 配置從庫：在從庫的my.cnf配置文件中添加以下內容： server-id=2 relay-log=mysql-relay-bin log_bin=mysql-bin binlog-do-db=your_db_name binlog-ignore-db=mysql

3. 在主庫創建同步用戶： CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

4. 在主庫創建備份用戶： CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password'; GRANT RELOAD,LOCK TABLES,SUPER ON *.* TO 'backup'@'localhost';

5. 在從庫設置主庫信息： CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=xxxx;

6. 開啟從庫同步： START SLAVE;

MySQL主從復制配置腳本

配置MySQL主從復制可以使用腳本，以下是一個簡單的腳本示例：

# 主庫配置 echo "[mysqld]" >>/etc/my.cnf echo "server-id=1" >>/etc/my.cnf echo "log-bin=mysql-bin" >>/etc/my.cnf echo "binlog-do-db=your_db_name" >>/etc/my.cnf echo "binlog-ignore-db=mysql" >>/etc/my.cnf # 從庫配置 echo "[mysqld]" >>/etc/my.cnf echo "server-id=2" >>/etc/my.cnf echo "relay-log=mysql-relay-bin" >>/etc/my.cnf echo "log_bin=mysql-bin" >>/etc/my.cnf echo "binlog-do-db=your_db_name" >>/etc/my.cnf echo "binlog-ignore-db=mysql" >>/etc/my.cnf # 創建同步用戶 mysql -uroot -p -e "CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';" # 創建備份用戶 mysql -uroot -p -e "CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password'; GRANT RELOAD,LOCK TABLES,SUPER ON *.* TO 'backup'@'localhost';" # 設置主庫信息和開啟從庫同步 mysql -uroot -p -e "CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=xxxx; START SLAVE;"