Chinaunix首页 | 论坛 | 博客

四爷yueliangdao0608.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

yueliangdao0608

  • 博客访问： 4183971
  • 博文数量： 240
  • 博客积分： 11504
  • 博客等级： 上将
  • 技术积分： 4277
  • 用 户 组： 普通用户
  • 注册时间： 2006-12-28 14:24
文章分类

全部博文（240）

最近访客
推荐博文
相关博文
【原创】mysql-utilities工具体验

分类： Mysql/postgreSQL

2013-05-14 12:53:41

我主要体验了下几个关于REPLICATION的工具。 其他的可以参见手册。
先说下我的环境：
MASTER: 192.168.1.131
SLAVE: 192.168.1.132, 192.168.1.133
三台DB都有对外的ALL权限用户。
各个配置文件如下，



点击(此处)折叠或打开

  1. [root@mysql56-master home]# cat /etc/my.cnf
  2. [mysqld]
  3. user = ytt
  4. skip-name-resolve


  7. innodb_buffer_pool_size = 128M




  12. basedir = /usr/local/mysql
  13. datadir = /usr/local/mysql/data
  14. port = 3306
  15. server_id = 131
  16. socket = /tmp/mysql.sock






  23. explicit_defaults_for_timestamp
  24. log-bin=mysql56-master-bin
  25. binlog-ignore-db=mysql


  28. gtid-mode=on
  29. enforce-gtid-consistency
  30. log-slave-updates


  33. binlog-format=ROW
  34. sync-master-info=1
  35. report-host=192.168.1.131
  36. report-port=3306




  41. master_info_repository=table
  42. relay_log_info_repository=table



其他两台SERVER，除了SERVER-ID，都基本相同，我就不贴了。


1. MYSQLREPLICATE 搭建主从的脚本，这里我搭建了两台从机。


点击(此处)折叠或打开

  1. mysqlreplicate --master=root:root@192.168.1.131:3306 --slave=root:root@192.168.1.132:3306;...
  2. [root@mysql56-master home]# ./replicate_create
  3. # master on 192.168.1.131: ... connected.
  4. # slave on 192.168.1.132: ... connected.
  5. # Checking for binary logging on master...
  6. # Setting up replication...
  7. # ...done.
  8. # master on 192.168.1.131: ... connected.
  9. # slave on 192.168.1.133: ... connected.
  10. # Checking for binary logging on master...
  11. # Setting up replication...
  12. # ...done.




2. mysqlrplcheck  检查主从的运行情况。



点击(此处)折叠或打开

  1. [root@mysql56-master home]# mysqlrplcheck --master=root:root@192.168.1.131:3306 --slave=root:root@192.168.1.132:3306 -s
  2. # master on 192.168.1.131: ... connected.
  3. # slave on 192.168.1.132: ... connected.
  4. Test Description Status
  5. ---------------------------------------------------------------------------
  6. Checking for binary logging on master [pass]
  7. Are there binlog exceptions? [WARN]


  10. +---------+--------+------------+
  11. | server | do_db | ignore_db |
  12. +---------+--------+------------+
  13. | master | | mysql |
  14. | slave | | mysql |
  15. +---------+--------+------------+


  18. Replication user exists? [pass]
  19. Checking server_id values [pass]
  20. Checking server_uuid values [pass]
  21. Is slave connected to master? [pass]
  22. Check master information file [pass]
  23. Checking InnoDB compatibility [pass]
  24. Checking storage engines compatibility [pass]
  25. Checking lower_case_table_names settings [pass]
  26. Checking slave delay (seconds behind master) [pass]


  29. #
  30. # Slave status:
  31. #
  32.                 Slave_IO_State : Waiting for master to send event
  33.                    Master_Host : 192.168.1.131
  34.                    Master_User : rpl
  35.                    Master_Port : 3306
  36.                  Connect_Retry : 60
  37.                Master_Log_File : mysql56-master-bin.000002
  38.            Read_Master_Log_Pos : 151
  39.                 Relay_Log_File : mysql56-slave-relay-bin.000003
  40.                  Relay_Log_Pos : 379
  41.          Relay_Master_Log_File : mysql56-master-bin.000002
  42.               Slave_IO_Running : Yes
  43.              Slave_SQL_Running : Yes
  44.                Replicate_Do_DB :
  45.            Replicate_Ignore_DB : mysql
  46.             Replicate_Do_Table :
  47.         Replicate_Ignore_Table :
  48.        Replicate_Wild_Do_Table :
  49.    Replicate_Wild_Ignore_Table :
  50.                     Last_Errno : 0
  51.                     Last_Error :
  52.                   Skip_Counter : 0
  53.            Exec_Master_Log_Pos : 151
  54.                Relay_Log_Space : 819
  55.                Until_Condition : None
  56.                 Until_Log_File :
  57.                  Until_Log_Pos : 0
  58.             Master_SSL_Allowed : No
  59.             Master_SSL_CA_File :
  60.             Master_SSL_CA_Path :
  61.                Master_SSL_Cert :
  62.              Master_SSL_Cipher :
  63.                 Master_SSL_Key :
  64.          Seconds_Behind_Master : 0
  65.  Master_SSL_Verify_Server_Cert : No
  66.                  Last_IO_Errno : 0
  67.                  Last_IO_Error :
  68.                 Last_SQL_Errno : 0
  69.                 Last_SQL_Error :
  70.    Replicate_Ignore_Server_Ids :
  71.               Master_Server_Id : 131
  72.                    Master_UUID : 4d89ad1d-bc12-11e2-87e9-080027338857
  73.               Master_Info_File : mysql.slave_master_info
  74.                      SQL_Delay : 0
  75.            SQL_Remaining_Delay : None
  76.        Slave_SQL_Running_State : Slave has read all relay log; waiting for the slave I/O thread to update it
  77.             Master_Retry_Count : 86400
  78.                    Master_Bind :
  79.        Last_IO_Error_Timestamp :
  80.       Last_SQL_Error_Timestamp :
  81.                 Master_SSL_Crl :
  82.             Master_SSL_Crlpath :
  83.             Retrieved_Gtid_Set :
  84.              Executed_Gtid_Set :
  85.                  Auto_Position : 1
  86. # ...done.
  87. [root@mysql56-master home]




3. mysqlrplshow. 显示主从的架构。



点击(此处)折叠或打开

  1. [root@mysql56-master home]# mysqlrplshow --master=root:root@192.168.1.131:3306 --discover-slaves-login=root:root -v
  2. # master on 192.168.1.131: ... connected.
  3. # Finding slaves for master: 192.168.1.131:3306


  6. # Replication Topology Graph
  7. 192.168.1.131:3306 (MASTER)
  8.    |
  9.    +--- 192.168.1.132:3306 [IO running: Yes] - (SLAVE)
  10.    |
  11.    +--- 192.168.1.133:3306 [IO running: Yes] - (SLAVE)


  14. [root@mysql56-master home]





4. mysqlfailover. 监视主从健康状态。



点击(此处)折叠或打开

  1. [root@mysql56-master home]# mysqlfailover --master=root:root@192.168.1.131:3306 --discover-slaves-login=root:root
  2. # Discovering slaves for master at 192.168.1.131:3306
  3. # Discovering slave at 192.168.1.132:3306
  4. # Found slave: 192.168.1.132:3306
  5. # Discovering slave at 192.168.1.133:3306
  6. # Found slave: 192.168.1.133:3306
  7. # Checking privileges.


  10. MySQL Replication Failover Utility
  11. Failover Mode = auto Next Interval = Tue May 14 12:27:56 2013


  14. Master Information
  15. ------------------
  16. Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
  17. mysql56-master-bin.0 151 mysql


  20. GTID Executed Set
  21. None


  24. Replication Health Status
  25. +----------------+-------+---------+--------+------------+-------------------------------------------+
  26. | host | port | role | state | gtid_mode | health |
  27. +----------------+-------+---------+--------+------------+-------------------------------------------+
  28. | 192.168.1.131 | 3306 | MASTER | UP | ON | OK |
  29. | 192.168.1.132 | 3306 | SLAVE | UP | ON | OK |
  30. | 192.168.1.133 | 3306 | SLAVE | UP | ON | Binary log and Relay log filters differ. |
  31. +----------------+-------+---------+--------+------------+-------------------------------------------+






  38. Q-quit R-refresh H-health G-GTID Lists U-UUIDs
  39. [root@mysql56-master home]





5. mysqlrpladmin. 对主从进行管理。
 

点击(此处)折叠或打开

  1. 停止从机服务：
  2.  [root@mysql56-master home]# mysqlrpladmin --master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 stop
  3. # Checking privileges.
  4. # Performing STOP on all slaves.
  5. # Executing stop on slave 192.168.1.132:3306 Ok
  6. # Executing stop on slave 192.168.1.133:3306 Ok
  7. # ...done.
  8. [root@mysql56-master home]#
  9.  开启从机服务：
  10. [root@mysql56-master home]# mysqlrpladmin --master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 stop
  11. # Checking privileges.
  12. # Performing STOP on all slaves.
  13. # Executing stop on slave 192.168.1.132:3306 Ok
  14. # Executing stop on slave 192.168.1.133:3306 Ok
  15. # ...done.
  16. [root@mysql56-master home]#


  19. 选择最好的备机准备以后切换用.
  20. [root@mysql56-master home]# mysqlrpladmin --master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 elect
  21. # Checking privileges.
  22. # Electing candidate slave from known slaves.
  23. # Best slave found is located on 192.168.1.132:3306.
  24. # ...done.
  25. [root@mysql56-master home]#
  26. 进行主从切换。


  29. [root@mysql56-master home]# mysqlrpladmin --master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 --new-master=root:root@192.168.1.132:3306 --demote-master switchover
  30. # Checking privileges.
  31. # Performing switchover from master at 192.168.1.131:3306 to slave at 192.168.1.132:3306.
  32. # Checking candidate slave prerequisites.
  33. # Checking slaves configuration to master.
  34. # Waiting for slaves to catch up to old master.
  35. # Stopping slaves.
  36. # Performing STOP on all slaves.
  37. # Demoting old master to be a slave to the new master.
  38. # Switching slaves to new master.
  39. # Starting all slaves.
  40. # Performing START on all slaves.
  41. # Checking slaves for errors.
  42. # Switchover complete.
  43. #
  44. # Replication Topology Health:
  45. +----------------+-------+---------+--------+------------+-----------------------------+
  46. | host | port | role | state | gtid_mode | health |
  47. +----------------+-------+---------+--------+------------+-----------------------------+
  48. | 192.168.1.132 | 3306 | MASTER | UP | ON | OK |
  49. | 192.168.1.131 | 3306 | SLAVE | UP | ON | OK |
  50. | 192.168.1.133 | 3306 | SLAVE | UP | ON | OK |
  51. +----------------+-------+---------+--------+------------+-----------------------------+
  52. # ...done.
  53. [root@mysql56-master home]#
  54. 显示下新的主从架构：
  55. [root@mysql56-master home]# mysqlrplshow --master=root:root@192.168.1.132:3306 --discover-slaves-login=root:root -v
  56. # master on 192.168.1.132: ... connected.
  57. # Finding slaves for master: 192.168.1.132:3306


  60. # Replication Topology Graph
  61. 192.168.1.132:3306 (MASTER)
  62.    |
  63.    +--- 192.168.1.131:3306 [IO running: Yes] - (SLAVE)
  64.    |
  65.    +--- 192.168.1.133:3306 [IO running: Yes] - (SLAVE)


  68. [root@mysql56-master home]


阅读(4565) | 评论(0) | 转发(0) |
1

上一篇：【原创】MySQL 最新商业版备份工具使用方法

下一篇：【原创】MySQL里求给定的时间是所在月份的第几个礼拜

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6