歡迎您光臨本站 註冊首頁

MySQL 主從雙向複製實驗

←手機掃碼閱讀     火星人 @ 2014-03-09 , reply:0

今天說的實驗是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)

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

  1. # cd /usr/local/src/mysql
  2. # tar xzvf mysql-5.1.45.tar.gz
  3. # groupadd mysql
  4. # useradd -g mysql mysql
  5. # cd mysql-5.1.45
  6. #./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
  7. #make && make install



配置mysql

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


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

  1. [[email protected] ~]# egrep -v '^$|^#' /etc/my.cnf
  2. [client]
  3. port = 3306

  4. socket = /usr/local/mysql/tmp/mysql.sock
  5. [mysqld]
  6. port = 3306
  7. socket = /usr/local/mysql/tmp/mysql.sock
  8. skip-external-locking
  9. key_buffer_size = 16M
  10. max_allowed_packet = 1M
  11. table_open_cache = 64
  12. sort_buffer_size = 512K
  13. net_buffer_length = 8K
  14. read_buffer_size = 256K
  15. read_rnd_buffer_size = 512K
  16. myisam_sort_buffer_size = 8M
  17. server-id= 1
  18. master-host=192.168.0.214

  19. master-user=shanker
  20. master-password=1234
  21. master-port=3306
  22. master-connect-retry=60
  23. replicate-do-db=testdba
  24. log-bin
  25. binlog-do-db=testdba
  26. read-only=0
  27. binlog-ignore-db=mysql
  28. log-bin=mysql-bin
  29. binlog_format=mixed
  30. [mysqldump]
  31. quick
  32. max_allowed_packet = 16M
  33. [mysql]
  34. no-auto-rehash
  35. [myisamchk]
  36. key_buffer_size = 20M

  37. sort_buffer_size = 20M
  38. read_buffer = 2M
  39. write_buffer = 2M
  40. [mysqlhotcopy]
  41. interactive-timeout

slave的my.cnf配置內容

  1. [[email protected] ~]# egrep -v '^$|^#' /etc/my.cnf
  2. [client]
  3. port = 3306
  4. socket = /usr/local/mysql/tmp/mysql.sock
  5. [mysqld]
  6. port = 3306
  7. socket = /usr/local/mysql/tmp/mysql.sock
  8. skip-external-locking
  9. key_buffer_size = 16M
  10. max_allowed_packet = 1M
  11. table_open_cache = 64
  12. sort_buffer_size = 512K

  13. net_buffer_length = 8K
  14. read_buffer_size = 256K
  15. read_rnd_buffer_size = 512K
  16. myisam_sort_buffer_size = 8M
  17. server-id=2
  18. master-host=192.168.0.225
  19. master-user=shanker
  20. master-password=1234
  21. master-port=3306
  22. master-connect-retry=60
  23. replicate-do-db=testdba
  24. log-bin=/var/mysql/mysqllog
  25. log-bin=mysql-bin
  26. binlog_format

    =mixed
  27. [mysqldump]
  28. quick
  29. max_allowed_packet = 16M
  30. [mysql]
  31. no-auto-rehash
  32. [myisamchk]
  33. key_buffer_size = 20M
  34. sort_buffer_size = 20M
  35. read_buffer = 2M
  36. write_buffer = 2M
  37. [mysqlhotcopy]
  38. interactive-timeout
  39. server-id=1
  40. log-bin=/var/mysql/mysqllog
  41. binlog-do-db=testdb
  42. read-only=0
  43. binlog-ignore-db=mysql


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


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

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

  1. [[email protected] ~]# cat clearupvar
  2. cd /usr/local/mysql/var
  3. rm i* mysql-b* shanker* *.info

shanker是主機的名字

主機上:

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


從機上:

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


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

  1. # /usr/local/mysql/bin/mysql -uroot
  2. mysql>GRANT REPLICATION SLAVE ON *.* TO 'shanker'@'%' IDENTIFIED BY '1234';
  3. 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


[火星人 ] MySQL 主從雙向複製實驗已經有1073次圍觀

http://coctec.com/docs/linux/show-post-47659.html