mysql-utilities工具体验_MySQL

mysql-utilities工具体验

我主要体验了下几个关于REPLICATION的工具。

先说下我的环境：

MASTER: 192.168.1.131

SLAVE: 192.168.1.132, 192.168.1.133

三台DB都有对外的ALL权限用户。

各个配置文件如下，

[sql]

[root@mysql56-master home]# cat /etc/my.cnf

[mysqld]

user = ytt

skip-name-resolve

innodb_buffer_pool_size = 128M

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

port = 3306

server_id = 131

socket = /tmp/mysql.sock

explicit_defaults_for_timestamp

log-bin=mysql56-master-bin

binlog-ignore-db=mysql

gtid-mode=on

enforce-gtid-consistency

log-slave-updates

binlog-format=ROW

sync-master-info=1

report-host=192.168.1.131

report-port=3306

master_info_repository=table

relay_log_info_repository=table

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

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

[sql]

mysqlreplicate --master=root:root@192.168.1.131:3306 --slave=root:root@192.168.1.132:3306;...

[root@mysql56-master home]# ./replicate_create

# master on 192.168.1.131: ... connected.

# slave on 192.168.1.132: ... connected.

# Checking for binary logging on master...

# Setting up replication...

# ...done.

# master on 192.168.1.131: ... connected.

# slave on 192.168.1.133: ... connected.

# Checking for binary logging on master...

# Setting up replication...

# ...done.

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

[sql]

[root@mysql56-master home]# mysqlrplcheck --master=root:root@192.168.1.131:3306 --slave=root:root@192.168.1.132:3306 -s

# master on 192.168.1.131: ... connected.

# slave on 192.168.1.132: ... connected.

Test Description Status

---------------------------------------------------------------------------

Checking for binary logging on master [pass]

Are there binlog exceptions? [WARN]

+---------+--------+------------+

| server | do_db | ignore_db |

+---------+--------+------------+

| master | | mysql |

| slave | | mysql |

+---------+--------+------------+

Replication user exists? [pass]

Checking server_id values [pass]

Checking server_uuid 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]

#

# Slave status:

#

Slave_IO_State : Waiting for master to send event

Master_Host : 192.168.1.131

Master_User : rpl

Master_Port : 3306

Connect_Retry : 60

Master_Log_File : mysql56-master-bin.000002

Read_Master_Log_Pos : 151

Relay_Log_File : mysql56-slave-relay-bin.000003

Relay_Log_Pos : 379

Relay_Master_Log_File : mysql56-master-bin.000002

Slave_IO_Running : Yes

Slave_SQL_Running : Yes

Replicate_Do_DB :

Replicate_Ignore_DB : mysql

Replicate_Do_Table :

Replicate_Ignore_Table :

Replicate_Wild_Do_Table :

Replicate_Wild_Ignore_Table :

Last_Errno : 0

Last_Error :

Skip_Counter : 0

Exec_Master_Log_Pos : 151

Relay_Log_Space : 819

Until_Condition : None

Until_Log_File :

Until_Log_Pos : 0

Master_SSL_Allowed : No

Master_SSL_CA_File :

Master_SSL_CA_Path :

Master_SSL_Cert :

Master_SSL_Cipher :

Master_SSL_Key :

Seconds_Behind_Master : 0

Master_SSL_Verify_Server_Cert : No

Last_IO_Errno : 0

Last_IO_Error :

Last_SQL_Errno : 0

Last_SQL_Error :

Replicate_Ignore_Server_Ids :

Master_Server_Id : 131

Master_UUID : 4d89ad1d-bc12-11e2-87e9-080027338857

Master_Info_File : mysql.slave_master_info

SQL_Delay : 0

SQL_Remaining_Delay : None

Slave_SQL_Running_State : Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count : 86400

Master_Bind :

Last_IO_Error_Timestamp :

Last_SQL_Error_Timestamp :

Master_SSL_Crl :

Master_SSL_Crlpath :

Retrieved_Gtid_Set :

Executed_Gtid_Set :

Auto_Position : 1

# ...done.

[root@mysql56-master home]#

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

[sql]

[root@mysql56-master home]# mysqlrplshow --master=root:root@192.168.1.131:3306 --discover-slaves-login=root:root -v

# master on 192.168.1.131: ... connected.

# Finding slaves for master: 192.168.1.131:3306

# Replication Topology Graph

192.168.1.131:3306 (MASTER)

|

+--- 192.168.1.132:3306 [IO running: Yes] - (SLAVE)

|

+--- 192.168.1.133:3306 [IO running: Yes] - (SLAVE)

[root@mysql56-master home]#

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

[sql]

[root@mysql56-master home]# mysqlfailover --master=root:root@192.168.1.131:3306 --discover-slaves-login=root:root

# Discovering slaves for master at 192.168.1.131:3306

# Discovering slave at 192.168.1.132:3306

# Found slave: 192.168.1.132:3306

# Discovering slave at 192.168.1.133:3306

# Found slave: 192.168.1.133:3306

# Checking privileges.

MySQL Replication Failover Utility

Failover Mode = auto Next Interval = Tue May 14 12:27:56 2013

Master Information

------------------

Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB

mysql56-master-bin.0 151 mysql

GTID Executed Set

None

Replication Health Status

+----------------+-------+---------+--------+------------+-------------------------------------------+

| host | port | role | state | gtid_mode | health |

+----------------+-------+---------+--------+------------+-------------------------------------------+

| 192.168.1.131 | 3306 | MASTER | UP | ON | OK |

| 192.168.1.132 | 3306 | SLAVE | UP | ON | OK |

| 192.168.1.133 | 3306 | SLAVE | UP | ON | Binary log and Relay log filters differ. |

+----------------+-------+---------+--------+------------+-------------------------------------------+

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

[root@mysql56-master home]#

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

[sql]

停止从机服务：

[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

# Checking privileges.

# Performing STOP on all slaves.

# Executing stop on slave 192.168.1.132:3306 Ok

# Executing stop on slave 192.168.1.133:3306 Ok

# ...done.

[root@mysql56-master home]#

开启从机服务：

[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

# Checking privileges.

# Performing STOP on all slaves.

# Executing stop on slave 192.168.1.132:3306 Ok

# Executing stop on slave 192.168.1.133:3306 Ok

# ...done.

[root@mysql56-master home]#

选择最好的备机准备以后切换用.

[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

# Checking privileges.

# Electing candidate slave from known slaves.

# Best slave found is located on 192.168.1.132:3306.

# ...done.

[root@mysql56-master home]#

进行主从切换。

[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

# Checking privileges.

# Performing switchover from master at 192.168.1.131:3306 to slave at 192.168.1.132:3306.

# Checking candidate slave prerequisites.

# Checking slaves configuration to master.

# 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 |

+----------------+-------+---------+--------+------------+-----------------------------+

| 192.168.1.132 | 3306 | MASTER | UP | ON | OK |

| 192.168.1.131 | 3306 | SLAVE | UP | ON | OK |

| 192.168.1.133 | 3306 | SLAVE | UP | ON | OK |

+----------------+-------+---------+--------+------------+-----------------------------+

# ...done.

[root@mysql56-master home]#

显示下新的主从架构：

[root@mysql56-master home]# mysqlrplshow --master=root:root@192.168.1.132:3306 --discover-slaves-login=root:root -v

# master on 192.168.1.132: ... connected.

# Finding slaves for master: 192.168.1.132:3306

# Replication Topology Graph

192.168.1.132:3306 (MASTER)

|

+--- 192.168.1.131:3306 [IO running: Yes] - (SLAVE)

|

+--- 192.168.1.133:3306 [IO running: Yes] - (SLAVE)

[root@mysql56-master home]#