Mysql-utilities tool experience _ MySQL

Use the mysql-utilities tool to experience bitsCN.com

Mysql-utilities tool experience

I mainly tried several REPLICATION tools.

Let's talk about my environment:

MASTER: 192.168.1.131

SLAVE: 192.168.1.132, 192.168.1.133

ALL three databases have ALL external users.

The configuration files are as follows,

[SQL]

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

[Mysqld]

User = ytt

Skip-name-resolve

Innodb_buffer_pool_size = 128 M

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

The other two servers except SERVER-ID are basically the same, so I will not post them.

1. create a master-slave script using MYSQLREPLICATE. here I have set up two slave servers.

[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.ted:... connected.

# Checking for binary logging on master...

# Setting up replication...

#... Done.

2. mysqlrplcheck checks the running status of the master and slave nodes.

[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. displays the master-slave architecture.

[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.20.: 3306 [IO running: Yes]-(SLAVE)

[Root @ mysql56-master home] #

4. mysqlfailover. monitor the master/slave health status.

[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.20.: 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.20.| 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. Manage the master and slave nodes.

[SQL]

Stop the slave service:

[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.

# Specify Ming 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] #

Enable the slave service:

[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.

# Specify Ming 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] #

Select the best backup machine to be used after preparation.

[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.

# The Best slave found is located on 192.168.1.132: 3306.

#... Done.

[Root @ mysql56-master home] #

Perform master-slave switchover.

[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.

# Login Ming 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.

# Specify Ming STOP on all slaves.

# Demoting old master to be a slave to the new master.

# Switching slaves to new master.

# Starting all slaves.

# Specify Ming 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] #

The following figure shows the new master-slave architecture:

[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.20.: 3306 [IO running: Yes]-(SLAVE)

[Root @ mysql56-master home] #

BitsCN.com