程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> mysql AB數據交互同步(雙機熱備)

mysql AB數據交互同步(雙機熱備)

編輯：關於MYSQL數據庫

MySQL AB復制實現數據交互同步(也叫雙機熱備)

環境：
rhel 5u3 server01: 192.168.1.31
rhel 5u3 server02: 192.168.1.32
MySQL工具包：
MySQL-max-5.0.24-Linux-i686.tar.gz

1、安裝MYSQL 兩台MySQL服務器做相同安裝操作
shell> tar -zxvf MySQL-max-5.0.24-Linux-i686.tar.gz
shell> mv mysql-max-5.0.24-Linux-i686-glibc23 /usr/local/MySQL-max-5.0.24
shell> ln -s /usr/local/mysql-max-5.0.24 /usr/local/MySQL
     shell> cd /usr/local/MySQL
     shell> groupadd MySQL
     shell> useradd -g mysql MySQL
     shell> scripts/mysql_install_db --user=MySQL
     shell> chown -R root .
     shell> chown -R mysql.MySQL data
     shell> chmod 777 data -R
     shell> bin/mysqld_safe --user=MySQL &
     shell> cp support-files/mysql.server /etc/init.d/MySQLd
     shell> cd
     shell> vi .bash_profile
     PATH=/usr/local/MySQL/bin:$PATH:$HOME/bin

2. 配置兩台MySQL服務器互為主從關系
2.1 在MySQL服務器1上
server01: 192.168.1.31
shell> cp /usr/local/MySQL/support-files/my-medium.cnf /etc/my.cnf
shell> vi /etc/my.cnf
/*
[MySQLd]
port            = 3306
socket          = /tmp/MySQL.sock
server-id=1
log-bin=binlog_name

master-host=192.168.1.32   |添加對方主機認證
master-user=slave          |
master-passWord=123456     |
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/MySQL/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/MySQL/data/
innodb_log_arch_dir = /usr/local/MySQL/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

*/
2.2 到MySQL服務器2上
   server02: 192.168.1.32
shell> cp /usr/local/MySQL/support-files/my-medium.cnf /etc/my.cnf
shell> vi /etc/my.cnf
/*
[MySQLd]
port            = 3306
socket          = /tmp/MySQL.sock
server-id=2
log-bin=binlog_name

master-host=192.168.1.31    |添加對方主機認證
master-user=slave      |
master-passWord=123456   |

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/MySQL/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/MySQL/data/
innodb_log_arch_dir = /usr/local/MySQL/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

*/
3. 添加數據庫交互的用戶，用於從庫讀取目標主庫日志。
   3.1在MySQL服務器1上測試狀態信息
   service MySQLd start
   MySQL -u root
MySQL> grant replication slave,reload,super on *.* to identifIEd by '123456'
MySQL> flush privileges; 或者
MySQL> FLUSH TABLES WITH READ LOCK;

    到MySQL服務器2上測試交互：
shell> MySQL -u slave -p -h 192.168.1.31
MySQL>
MySQL> SHOW GRANTS;
    Grants for [email protected]
MySQL> start slave;
MySQL> load data from master;
MySQL> show slave status\G；
           Slave_IO_Running: Yes   | OK了！
          Slave_SQL_Running: Yes
3.2 在MySQL服務器2上做3.1 同樣的測試操作
    service MySQLd start
    MySQL -u root
MySQL>grant replication slave,reload,super on *.* to identifIEd by '123456'；
     到MySQL服務器2上測試交互：
shell> MySQL -u slave -p -h 192.168.1.32
MySQL>
MySQL> SHOW GRANTS;
    Grants for [email protected]
MySQL> start slave;
MySQL> load data from master;
MySQL> show slave status\G；
           Slave_IO_Running: Yes   | OK了！
          Slave_SQL_Running: Yes
4. 測試數據同步
server01: 上
MySQL> create database test01;
MySQL> use test01;
MySQL> create table tb01 (name char(20),phone char(20));
MySQL> insert into tb01 values('laoli,'12345678');
MySQL> slect * from tb01;