Chinaunix首页 | 论坛 | 博客
  • 博客访问: 582577
  • 博文数量: 57
  • 博客积分: 877
  • 博客等级: 准尉
  • 技术积分: 1275
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-24 16:16
文章分类

全部博文(57)

文章存档

2014年(2)

2013年(15)

2012年(20)

2011年(20)

我的朋友

分类: Mysql/postgreSQL

2013-02-27 15:53:44

                                    

接着上一次的讲,接下来的是


9:mysqlindexcheck
作用:它可以读取一个或多个表的索引和识别重复的和潜在的冗余索引
语法: mysqlindexcheck.exe --server=user:pass@host:port:socket db1.table1 db2 db3.table2

10:mysqlmetagrep
作用:对输入的对象找到匹配的元数据内容
语法:mysqlmetagrep.exe --server=user:pass@host:port:socket [options] pattern
例如:
C:\Program Files\MySQL\MySQL Workbench 5.2 CE\utilities>mysqlmetagrep --server=root:sbcenter@172.72.15.41 --database=test_new  --pattern="%test%"
+---------------------------+--------------+--------------+-----------+-------------+-----------+
| Connection                | Object Type  | Object Name  | Database  | Field Type  | Matches   |
+---------------------------+--------------+--------------+-----------+-------------+-----------+
| root:*@172.72.15.41:3306  | SCHEMA       | test_new     | test_new  | DATABASE    | test_new  |
| root:*@172.72.15.41:3306  | TABLE        | test1        | test_new  | TABLE       | test1     |
+---------------------------+--------------+--------------+-----------+-------------+-----------+


在 test_new数据库中找出 匹配 %test% 的元数据

11:mysqlprocgrep

作用:扫描进程列表,根据输入找出匹配的进程项
语法:mysqlprocgrep.exe --server=user:pass@host:port:socket [options]
【options】 可用mysqlprocgrep --help 获得

12:和复制相关的一些 utility (mysqlfailover,mysqlreplicate,mysqlrpladmin,mysqlrplcheck,mysqlrplshow
通过一个实验一起讲解

(1)正确配置我们所需的mysql 服务器组,这里配置了5个mysql 服务
my1.cnf
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
datadir=/home/billy/mysql/data1
server-id=1
log-bin=util11-bin.log
report-host=utils1
report-port=3306
socket=/home/billy/mysql/sock1
port=3306

my2.cnf
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
datadir=/home/billy/mysql/data2
server-id=2
log-bin=util12-bin.log
report-host=utils1
report-port=3307
socket=/home/billy/mysql/sock2
port=3307


my3.cnf
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
datadir=/home/billy/mysql/data3
server-id=3
log-bin=util2-bin.log
report-host=utils2
report-port=3306
socket=/home/billy/mysql/sock3
port=3306

my4.cnf
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
master-info-repository=TABLE
relay-log-info-repository=TABLE
master-info-file=/home/billy/mysql/master4.info
datadir=/home/billy/mysql/data4
server-id=4
log-bin=util4-bin.log
report-host=utils2
report-port=3307
socket=/home/billy/mysql/sock4
port=3307

my5.cnf
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
datadir=/home/billy/mysql/data5
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
#master-info-file=/home/billy/mysql/master5.info
server-id=5
log-bin=util5-bin.log
report-host=utils2
report-port=3308
socket=/home/billy/mysql/sock5
port=3308


(2)授予远程用户权限
[billy@utils1 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "grant all on *.* to root@'%' with grant option;"
[billy@utils1 ~]$ mysql -h 127.0.0.1 -P3307 -u root -e "grant all on *.* to root@'%' with grant option;"
[billy@utils2 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "grant all on *.* to root@'%' with grant option;"
[billy@utils2 ~]$ mysql -h 127.0.0.1 -P3307 -u root -e "grant all on *.* to root@'%' with grant option;"
[billy@utils2 ~]$ mysql -h 127.0.0.1 -P3308 -u root -e "grant all on *.* to root@'%' with grant option;"


(3)用 mysqlreplicate  工具进行复制,这里 utils1 里的 3306端口为master,其余都为slave

mysqlreplicate --master=root@utils1:3306 --slave=root@utils1:3307
# master on utils1: ... connected.
# slave on utils1: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

mysqlreplicate --master=root@utils1:3306 --slave=root@utils2:3306
# master on utils1: ... connected.
# slave on utils2: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

mysqlreplicate --master=root@utils1:3306 --slave=root@utils2:3307
# master on utils1: ... connected.
# slave on utils2: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

mysqlreplicate --master=root@utils1:3306 --slave=root@utils2:3308
# master on utils1: ... connected.
# slave on utils2: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.



(4)复制配置完成之后,我们可以用mysqlrplshow   查看master-slave 的拓扑结构
mysqlrplshow --master=root@utils1;
# master on utils1: ... connected.
# Finding slaves for master: utils1:3306

# Replication Topology Graph
utils1:3306 (MASTER)
  |
  +--- utils1:3307 - (SLAVE)
  |
  +--- utils2:3306 - (SLAVE)
  |
  +--- utils2:3307 - (SLAVE)
  |
  +--- utils2:3308 - (SLAVE)


也可以用mysqlrplcheck 检查复制是否正确配置了
mysqlrplcheck --master=root@utils1 --slave=root@utils2:3307
# master on utils1: ... connected.
# slave on utils2: ... connected.
Test Description                                                     Status
---------------------------------------------------------------------------
Checking for binary logging on master                                [pass]
Are there binlog exceptions?                                         [pass]
Replication user exists?                                             [pass]
Checking server_id values                                            [pass]
Is slave connected to master?                                        [pass]
Check master information file                                        [pass]
Checking InnoDB compatibility                                        [pass]
Checking storage engines compatibility                               [pass]
Checking lower_case_table_names settings                             [pass]
Checking slave delay (seconds behind master)                         [pass]
# ...done.


(5): replicate 的监控和故障转移
mysqlfailover --master=root@utils1:3306 --discover-slaves-login=root --rediscover

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Aug 15 13:19:30 2012

Master Information
------------------
Binary Log File    Position  Binlog_Do_DB  Binlog_Ignore_DB
util11-bin.000001  2586

Replication Health Status
+---------+-------+---------+--------+------------+---------+
| host    | port  | role    | state  | gtid_mode  | health  |
+---------+-------+---------+--------+------------+---------+
| utils1  | 3306  | MASTER  | UP     | ON         | OK      |
| utils1  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3306  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3308  | SLAVE   | UP     | ON         | OK      |
+---------+-------+---------+--------+------------+---------+


Q-quit R-refresh H-health G-GTID Lists U-UUIDs



这里Failover Mode = auto 表示故障转移为自动,当master 出现故障的时候,就会从slave池中找到最为先进的slave作为master,当然你也可以根据实际情况绑定自己的故障转移脚本

现在把 master server 停掉
mysqladmin -h utils1 -P3306 -u root shutdown


然后刷新一下
MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Aug 15 13:21:13 2012

Master Information
------------------
Binary Log File    Position  Binlog_Do_DB  Binlog_Ignore_DB
util12-bin.000001  7131

Replication Health Status
+---------+-------+---------+--------+------------+---------+
| host    | port  | role    | state  | gtid_mode  | health  |
+---------+-------+---------+--------+------------+---------+
| utils1  | 3307  | MASTER  | UP     | ON         | OK      |
| utils2  | 3306  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3308  | SLAVE   | UP     | ON         | OK      |
+---------+-------+---------+--------+------------+---------+


Q-quit R-refresh H-health G-GTID Lists U-UUIDs


可以看到 utils1 的 3307 已经上升为master 了


(6)故障恢复
由于现在的master 是utils1:3307 ,所以utils1:3306需要从utils1:3307哪里更新数据
mysqlreplicate --master=root@utils1:3307 --slave=root@utils1:3306
# master on utils1: ... connected.
# slave on utils1: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.


刷新看下 master-slave 状态
MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Aug 15 13:24:38 2012

Master Information
------------------
Binary Log File    Position  Binlog_Do_DB  Binlog_Ignore_DB
util12-bin.000001  7131

Replication Health Status
+---------+-------+---------+--------+------------+---------+
| host    | port  | role    | state  | gtid_mode  | health  |
+---------+-------+---------+--------+------------+---------+
| utils1  | 3307  | MASTER  | UP     | ON         | OK      |
| utils1  | 3306  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3306  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3308  | SLAVE   | UP     | ON         | OK      |
+---------+-------+---------+--------+------------+---------+


发现utils1:3306 已经回到master-slave 组中了

(7)master-slave 主从转换
现在需要把 utils:3306 恢复到master 上,utils:3307回归到slave 上
可用mysqlrpladmin utility
mysqlrpladmin --master=root@utils1:3307 --new-master=root@utils1:3306 --demote-master
 --discover-slaves-login=root switchover


# Discovering slaves for master at utils1:3307
# Checking privileges.
# Performing switchover from master at utils1:3307 to slave at utils1:3306.
# Checking candidate slave prerequisites.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+---------+-------+---------+--------+------------+---------+
| host    | port  | role    | state  | gtid_mode  | health  |
+---------+-------+---------+--------+------------+---------+
| utils1  | 3306  | MASTER  | UP     | ON         | OK      |
| utils1  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3306  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3308  | SLAVE   | UP     | ON         | OK      |
+---------+-------+---------+--------+------------+---------+
# ...done.


完毕!!

参考文章:http://dev.mysql.com/doc/workbench/en/mysql-utils-man-overview.html


阅读(5632) | 评论(1) | 转发(1) |
给主人留下些什么吧!~~