接着上一次的讲,接下来的是
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