MySQL 單向主從復制快速部署指南

環境準備

系統：RHEL 4.7，MySQL 5.1.34（源碼編譯安裝）。

編譯安裝 MySQL（兩台機器同步操作）

#groupadd mysql
#useradd -g mysql mysql
#tar zxvf mysql-5.1.34.tar.gz
#cd mysql-5.1.34
#./configure --prefix=/usr/local/mysql --localstatedir=/db/mysql --sysconfdir=/etc --without-debug --enable-assembler --enable-thread-safe-client --enable-profiling --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-charset=utf8 --with-collation=utf8_general_ci --with-extra-charsets=utf8,gbk --with-pthread --without-ndb-debug --with-mysqld-user=mysql --without-embedded-server --with-unix-socket-path=/usr/local/mysql/sock/mysql.sock
#make && make install
#cp support-files/my-huge.cnf /etc/my.cnf
#vi /etc/my.cnf  # 添加下列配置
datadir = /db/mysql
skip-name-resolve
table-cache = 4096
#mkdir -p /db/mysql
#chown mysql.mysql /db/mysql -R
#./scripts/mysql_install_db --user=mysql
#cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
#chmod +x /etc/rc.d/init.d/mysqld
#chkconfig --level 235 mysqld on
#chown -R root.mysql /usr/local/mysql
# 設置環境變量
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
#mysqladmin -u root password 'YourPASSWD'
#service mysqld start

Master 服務器配置

mysql -uroot -pYourPASSWD
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT, RELOAD, SUPER ON *.* TO 'repl'@'192.168.76.132' IDENTIFIED BY '111111';
mysql> FLUSH PRIVILEGES;
mysql> \q

#vi /etc/my.cnf  # 在 [mysqld] 區段添加
log-bin=mysql-bin
binlog-do-db=db2
server-id=1
binlog-ignore-db=mysql

# 重啟 MySQL
#service mysqld restart

# 創建測試庫表
mysql -uroot -pYourPASSWD
mysql> CREATE DATABASE db2;
mysql> USE db2;
mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (a)) ENGINE=MyISAM;

Slave 服務器配置

#vi /etc/my.cnf  # 在 [mysqld] 區段添加
server-id=2
master-host=192.168.76.131
master-user=repl
master-password=111111
master-port=3306
master-connect-retry=60
replicate-do-db=db2

# 重啟 MySQL
#service mysqld restart

驗證復制狀態

在 Master 執行 SHOW MASTER STATUS;，在 Slave 執行 SHOW SLAVE STATUS\G，確認 Slave_IO_Running 和 Slave_SQL_Running 均為 Yes。在 Master 的 db2 中插入數據，觀察 Slave 是否同步。