Mysql-utilities tool experience _ MySQL

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.