MySQL 主從雙向複製實驗

今天說的實驗是MySQL主從雙向複製,實驗環境為Master（Red Hat Enterprise Linux Server release 5.5 IP: 192.168.0.225）Slave（CentOS release 6.2 (Final) IP：192.168.0.214）

廢話不多說,直接寫如何實驗過程.

# cd /usr/local/src/mysql  
# tar xzvf mysql-5.1.45.tar.gz  
# groupadd mysql  
# useradd -g mysql mysql  
# cd mysql-5.1.45  
#./configure --prefix=/usr/local/mysql --enable-local-infile --with-charset=utf8 --with-extra-charsets=all --enable-thread-safe-client --enable-assembler --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock --with-plugins=innobase  
#make && make install 

配置mysql

# cp support-files/my-medium.cnf /etc/my.cnf  
# 修改 /etc.my.cnf 將skip-locking替換成skip-external-locking  
# cd /usr/local/mysql  
# bin/mysql_install_db --user=mysql  
# mkdir tmp  
# chown -R root .  
# chown -R mysql var  
# chown -R mysql tmp  
# chgrp -R mysql .  
# bin/mysqld_safe --user=mysql & //以mysql用戶啟動資料庫.  

同樣步驟在slave機器上安裝完畢mysql
———————————————————————————————————————————————————————————————————————————————————
master my.cnf配置文件

[root@defcon ~]# egrep -v '^$|^#' /etc/my.cnf  
[client]  
port = 3306        
                   
socket = /usr/local/mysql/tmp/mysql.sock  
[mysqld]  
port = 3306  
socket = /usr/local/mysql/tmp/mysql.sock  
skip-external-locking  
key_buffer_size = 16M  
max_allowed_packet = 1M  
table_open_cache = 64  
sort_buffer_size = 512K  
net_buffer_length = 8K  
read_buffer_size = 256K  
read_rnd_buffer_size = 512K  
myisam_sort_buffer_size = 8M  
server-id= 1  
master-host=192.168.0.214          
                 
master-user=shanker  
master-password=1234  
master-port=3306  
master-connect-retry=60  
replicate-do-db=testdba  
log-bin  
binlog-do-db=testdba  
read-only=0  
binlog-ignore-db=mysql  
 
log-bin=mysql-bin  
binlog_format=mixed  
[mysqldump]  
quick  
max_allowed_packet = 16M  
[mysql]  
no-auto-rehash  
[myisamchk]  
key_buffer_size = 20M        
                   
sort_buffer_size = 20M  
read_buffer = 2M  
write_buffer = 2M  
[mysqlhotcopy]  
interactive-timeout 

slave的my.cnf配置內容

[root@shanker ~]# egrep -v '^$|^#' /etc/my.cnf  
[client]  
port = 3306  
socket = /usr/local/mysql/tmp/mysql.sock  
[mysqld]  
port = 3306  
socket = /usr/local/mysql/tmp/mysql.sock  
skip-external-locking  
key_buffer_size = 16M  
max_allowed_packet = 1M  
table_open_cache = 64  
sort_buffer_size = 512K        
                   
net_buffer_length = 8K  
read_buffer_size = 256K  
read_rnd_buffer_size = 512K  
myisam_sort_buffer_size = 8M  
server-id=2  
master-host=192.168.0.225  
master-user=shanker  
master-password=1234  
master-port=3306  
master-connect-retry=60  
replicate-do-db=testdba  
log-bin=/var/mysql/mysqllog  
log-bin=mysql-bin  
binlog_format        
                 =mixed  
[mysqldump]  
quick  
max_allowed_packet = 16M  
[mysql]  
no-auto-rehash  
[myisamchk]  
key_buffer_size = 20M  
sort_buffer_size = 20M  
read_buffer = 2M  
write_buffer = 2M  
[mysqlhotcopy]  
interactive-timeout  
server-id=1  
log-bin=/var/mysql/mysqllog  
binlog-do-db=testdb  
read-only=0  
binlog-ignore-db=mysql  

配置文件修改完畢保存該配置並且刪掉 mysql 的 var 目錄下除資料庫以外的文件.

主要是 *.00000* 類似的文件以及 master.info ,為避免出錯,一定要先刪除.

為了方便我寫了個腳本,用來刪除那些文件

[root@shanker ~]# cat clearupvar  
cd /usr/local/mysql/var  
rm i* mysql-b* shanker* *.info 

shanker是主機的名字

主機上：

# /usr/local/mysql/bin/mysql -uroot -p  
mysql>create database testdb;  
mysql>use testdb;  
mysql>CREATE TABLE students(  
sn INT,  
name VARCHAR(20)  
)  
mysql> insert into students values(1,'shanker');  

從機上：

/usr/local/mysql/bin/mysql -uroot  -p
        
                 mysql>create database testdb;  
mysql>CREATE TABLE students(  
sn INT,  
name VARCHAR(20)  
)  
mysql> insert into students values(2,'defcon'); 

創建用於同步的用戶並賦予其操作許可權(主機從機都要執行)

# /usr/local/mysql/bin/mysql -uroot 
 
mysql>GRANT REPLICATION SLAVE ON *.* TO 'shanker'@'%' IDENTIFIED BY '1234'; 
 
 
mysql>flush privileges 

主機 master ：

mysql > show master status;

記住欄位 file 和 position 的值,在 slave 狀態中需要檢驗的.
從機 slave ：

mysql > show slave status\G;

特別提示：檢查參數 Slave_IO_State 、 Slave_IO_Running 、 Slave_SQL_Running

Slave_IO_State 的狀態值是由 Slave_IO_Running 的值決定的,如果

Slave_IO_Running 和 Slave_SQL_Running 的值都為 Yes ,並且

Slave_IO_State 的值為 :Waiting for master to send event ,那麼說

明配置是沒問題的可以進行下一步同步嘗試了.

看截圖

done successfully ～～

本文出自 「天涯海閣」 博客,請務必保留此出處http://shanker.blog.51cto.com/1189689/806095

Tags: linux system 系統