mysql autoextend_MySQL Auto-Failover 功能测试

最新推荐文章于 2022-06-01 09:47:00 发布
最新推荐文章于 2022-06-01 09:47:00 发布
阅读量84 收藏
点赞数
文章标签： mysql autoextend
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42521843/article/details/113276116
版权

参考资料：

http://www.clusterdb.com/mysql/replication-and-auto-failover-made-easy-with-mysql-utilities

环境介绍:

master: demoenv-trial-1

slaves: demoenv-trial-2 demoenv-trial-3

1. 安装 Percona Server，在所有服务器上：

$ sudo yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

$ sudo yum install Percona-Server-shared-compat

$ sudo yum install Percona-Server-server-56

$ sudo yum install http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

$ sudo yum install mysql-utilities

1

2

3

4

5

$sudoyuminstallhttp://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

$sudoyuminstallPercona-Server-shared-compat

$sudoyuminstallPercona-Server-server-56

$sudoyuminstallhttp://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

$sudoyuminstallmysql-utilities

2. 配置 /etc/my.cnf，在所有服务器上：

注意：确保 server-id 不同且 report-host 与自身主机名相同

$ sudo vim /etc/my.cnf

[mysqld]

# basic setting

datadir = /opt/mysql/data

tmpdir = /opt/mysql/tmp

socket = /opt/mysql/run/mysqld.sock

port = 3306

pid-file = /opt/mysql/run/mysqld.pid

# innodb setting

default-storage-engine = INNODB

innodb_file_per_table = 1

log-bin = /opt/mysql/binlogs/bin-log-mysqld

log-bin-index = /opt/mysql/binlogs/bin-log-mysqld.index

innodb_data_home_dir = /opt/mysql/data

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /opt/mysql/data

binlog-do-db = testdb

# server id

server-id=1

# gtids setting

binlog-format = ROW

log-slave-updates = true

gtid-mode = on

enforce-gtid-consistency = true

report-host = demoenv-trial-1

report-port = 3306

master-info-repository = TABLE

relay-log-info-repository = TABLE

sync-master-info = 1

# other settings

[mysqld_safe]

log-error = /opt/mysql/log/mysqld.log

pid-file = /opt/mysql/run/mysqld.pid

open-files-limit = 8192

[mysqlhotcopy]

interactive-timeout

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

$sudovim/etc/my.cnf

[mysqld]

# basic setting

datadir=/opt/mysql/data

tmpdir=/opt/mysql/tmp

socket=/opt/mysql/run/mysqld.sock

port=3306

pid-file=/opt/mysql/run/mysqld.pid

# innodb setting

default-storage-engine=INNODB

innodb_file_per_table=1

log-bin=/opt/mysql/binlogs/bin-log-mysqld

log-bin-index=/opt/mysql/binlogs/bin-log-mysqld.index

innodb_data_home_dir=/opt/mysql/data

innodb_data_file_path=ibdata1:10M:autoextend

innodb_log_group_home_dir=/opt/mysql/data

binlog-do-db=testdb

# server id

server-id=1

# gtids setting

binlog-format=ROW

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

report-host=demoenv-trial-1

report-port=3306

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

# other settings

[mysqld_safe]

log-error=/opt/mysql/log/mysqld.log

pid-file=/opt/mysql/run/mysqld.pid

open-files-limit=8192

[mysqlhotcopy]

interactive-timeout

3. 创建所需目录，在所有服务器上：

$ sudo mkdir -p /opt/mysql/{data,tmp,run,binlogs,log}

$ sudo chown mysql:mysql /opt/mysql/{data,tmp,run,binlogs,log}

1

2

$sudomkdir-p/opt/mysql/{data,tmp,run,binlogs,log}

$sudochownmysql:mysql/opt/mysql/{data,tmp,run,binlogs,log}

4. 初始化数据库，在所有服务器上：

$ sudo -i

# su - mysql

$ mysql_install_db --user=mysql --datadir=/opt/mysql/data/

$ exit

# exit

$ sudo /etc/init.d/mysql start

1

2

3

4

5

6

$sudo-i

# su - mysql

$mysql_install_db--user=mysql--datadir=/opt/mysql/data/

$exit

# exit

$sudo/etc/init.d/mysqlstart

5. 创建授权用户 root@'%' 以便通过 mysqlreplicate 来进行主从复制的配置，在所有服务器上：

$ mysql -uroot

mysql> grant all on *.* to root@'%' identified by 'pass' with grant option;

mysql> quit;

1

2

3

$mysql-uroot

mysql>grantallon *.*toroot@'%'identifiedby'pass'withgrantoption;

mysql>quit;

6. 创建复制所需的用户，在所有服务器上：

$ mysql -uroot

mysql> grant replication slave on *.* to 'rpl'@'%' identified by 'rpl';

mysql> quit;

1

2

3

$mysql-uroot

mysql>grantreplicationslaveon *.*to'rpl'@'%'identifiedby'rpl';

mysql>quit;

7. 配置主从复制，可选择任意一台服务器操作：

[dong.guo@demoenv-trial-1 ~]$ mysql -uroot

mysql> use mysql;

mysql> drop user root@'demoenv-trial-1';

mysql> quit;

[dong.guo@demoenv-trial-1 ~]$ mysqlreplicate --master=root:pass@'demoenv-trial-1':3306 --slave=root:pass@'demoenv-trial-2':3306 --rpl-user=rpl:rpl

# master on demoenv-trial-1: ... connected.

# slave on demoenv-trial-2: ... connected.

# Checking for binary logging on master...

# set up replication...

# ...done.

[dong.guo@demoenv-trial-1 ~]$ mysqlreplicate --master=root:pass@'demoenv-trial-1':3306 --slave=root:pass@'demoenv-trial-3':3306 --rpl-user=rpl:rpl

# master on demoenv-trial-1: ... connected.

# slave on demoenv-trial-3: ... connected.

# Checking for binary logging on master...

# set up replication...

# ...done.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

[dong.guo@demoenv-trial-1~]$mysql-uroot

mysql>usemysql;

mysql>dropuserroot@'demoenv-trial-1';

mysql>quit;

[dong.guo@demoenv-trial-1~]$mysqlreplicate--master=root:pass@'demoenv-trial-1':3306--slave=root:pass@'demoenv-trial-2':3306--rpl-user=rpl:rpl

# master on demoenv-trial-1: ... connected.

# slave on demoenv-trial-2: ... connected.

# Checking for binary logging on master...

# set up replication...

# ...done.

[dong.guo@demoenv-trial-1~]$mysqlreplicate--master=root:pass@'demoenv-trial-1':3306--slave=root:pass@'demoenv-trial-3':3306--rpl-user=rpl:rpl

# master on demoenv-trial-1: ... connected.

# slave on demoenv-trial-3: ... connected.

# Checking for binary logging on master...

# set up replication...

# ...done.

8. 验证主从复制的数据完整性

[dong.guo@demoenv-trial-1 ~]$ mysql -uroot

mysql> create database testdb;

mysql> quit;

[dong.guo@demoenv-trial-1 ~]$ mysql -uroot -ppass -h'demoenv-trial-2' -e 'show databases;'

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

| Database |

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

| information_schema|

| mysql |

| performance_schema|

| test |

| testdb |

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

[dong.guo@demoenv-trial-1 ~]$ mysql -uroot -ppass -h'demoenv-trial-3' -e 'show databases;'

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

| Database |

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

| information_schema|

| mysql |

| performance_schema|

| test |

| testdb |

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

[dong.guo@demoenv-trial-1 ~]$ mysqlrplshow --master=rpl:rpl@'demoenv-trial-1':3306 --discover-slaves-login=root:pass;

# master on demoenv-trial-1: ... connected.

# Finding slaves for master: demoenv-trial-1:3306

# Replication Topology Graph

demoenv-trial-1:3306 (MASTER)

|

+--- demoenv-trial-2:3306 - (SLAVE)

|

+--- demoenv-trial-3:3306 - (SLAVE)

[dong.guo@demoenv-trial-1 ~]$ mysqlrplcheck --master=root:pass@'demoenv-trial-1' --slave=root:pass@'demoenv-trial-2'

# master on demoenv-trial-1: ... connected.

# slave on demoenv-trial-2: ... connected.

Test Description Status

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

Checking for binary logging on master [pass]

Are there binlog exceptions? [WARN]

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

| server | do_db | ignore_db |

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

| master | testdb | |

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

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 setting [pass]

Checking slave delay (seconds behind master) [pass]

# ...done.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

[dong.guo@demoenv-trial-1~]$mysql-uroot

mysql>createdatabasetestdb;

mysql>quit;

[dong.guo@demoenv-trial-1~]$mysql-uroot-ppass-h'demoenv-trial-2'-e'show databases;'

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

|Database|

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

|information_schema|

|mysql|

|performance_schema|

|test|

|testdb|

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

[dong.guo@demoenv-trial-1~]$mysql-uroot-ppass-h'demoenv-trial-3'-e'show databases;'

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

|Database|

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

|information_schema|

|mysql|

|performance_schema|

|test|

|testdb|

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

[dong.guo@demoenv-trial-1~]$mysqlrplshow--master=rpl:rpl@'demoenv-trial-1':3306--discover-slaves-login=root:pass;

# master on demoenv-trial-1: ... connected.

# Finding slaves for master: demoenv-trial-1:3306

# Replication Topology Graph

demoenv-trial-1:3306(MASTER)

|

+---demoenv-trial-2:3306-(SLAVE)

|

+---demoenv-trial-3:3306-(SLAVE)

[dong.guo@demoenv-trial-1~]$mysqlrplcheck--master=root:pass@'demoenv-trial-1'--slave=root:pass@'demoenv-trial-2'

# master on demoenv-trial-1: ... connected.

# slave on demoenv-trial-2: ... connected.

TestDescriptionStatus

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

Checkingforbinaryloggingonmaster[pass]

Aretherebinlogexceptions?[WARN]

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

|server|do_db|ignore_db|

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

|master|testdb||

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

Replicationuserexists?[pass]

Checkingserver_idvalues[pass]

Checkingserver_uuidvalues[pass]

Isslaveconnectedtomaster?[pass]

Checkmasterinformationfile[pass]

CheckingInnoDBcompatibility[pass]

Checkingstorageenginescompatibility[pass]

Checkinglower_case_table_namessetting[pass]

Checkingslavedelay(secondsbehindmaster)[pass]

# ...done.

9. 关闭 master 测试 auto-failover

[dong.guo@demoenv-trial-1 ~]$ mysqlfailover --master=root:pass@'demoenv-trial-1':3306 --discover-slaves-login=root:pass --rediscover

# Discovering slaves for master at demoenv-trial-1:3306

# Discovering slave at demoenv-trial-2:3306

# Found slave: demoenv-trial-2:3306

# Discovering slave at demoenv-trial-3:3306

# Found slave: demoenv-trial-3:3306

# Checking privileges.

# Discovering slaves for master at demoenv-trial-1:3306

MySQL Replication Failover Utility

Failover Mode = auto Next Interval = Sun Oct 20 06:58:52 2013

Master Information

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

Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB

bin-log-mysqld.00000 299 testdb

GTID Executed Set

92df196b-3906-11e3-b6b6-000c290d14d7:1

Replication Health Status

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

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

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

| demoenv-trial-1 | 3306 | MASTER | UP | ON | OK |

| demoenv-trial-2 | 3306 | SLAVE | UP | ON | OK |

| demoenv-trial-3 | 3306 | SLAVE | UP | ON | OK |

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

[dong.guo@demoenv-trial-1~]$mysqlfailover--master=root:pass@'demoenv-trial-1':3306--discover-slaves-login=root:pass--rediscover

# Discovering slaves for master at demoenv-trial-1:3306

# Discovering slave at demoenv-trial-2:3306

# Found slave: demoenv-trial-2:3306

# Discovering slave at demoenv-trial-3:3306

# Found slave: demoenv-trial-3:3306

# Checking privileges.

# Discovering slaves for master at demoenv-trial-1:3306

MySQLReplicationFailoverUtility

FailoverMode=autoNextInterval=SunOct2006:58:522013

MasterInformation

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

BinaryLogFilePositionBinlog_Do_DBBinlog_Ignore_DB

bin-log-mysqld.00000299testdb

GTIDExecutedSet

92df196b-3906-11e3-b6b6-000c290d14d7:1

ReplicationHealthStatus

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

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

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

|demoenv-trial-1|3306|MASTER|UP|ON|OK|

|demoenv-trial-2|3306|SLAVE|UP|ON|OK|

|demoenv-trial-3|3306|SLAVE|UP|ON|OK|

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

Q-quitR-refreshH-healthG-GTIDListsU-UUIDs

然后，执行命令的终端就挂起了，需要另外开启一个终端来关闭master：

[dong.guo@demoenv-trial-1 ~]$ mysqladmin -uroot -ppass shutdown

1

[dong.guo@demoenv-trial-1~]$mysqladmin-uroot-ppassshutdown

接着，在刚刚挂起的终端上，可以看到：

Failover starting in 'auto' mode...

# Candidate slave demoenv-trial-2:3306 will become the new master.

# Checking slaves status (before failover).

# Preparing candidate for failover.

# Creating replication user if it does not exist.

# Stopping slaves.

# Performing STOP on all slaves.

# Switching slaves to new master.

# Disconnecting new master as slave.

# Starting slaves.

# Performing START on all slaves.

# Checking slaves for errors.

# Failover complete.

# Discovering slaves for master at demoenv-trial-2:3306

Failover console will restart in 5 seconds.

MySQL Replication Failover Utility

Failover Mode = auto Next Interval = Sun Oct 20 07:01:25 2013

Master Information

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

Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB

bin-log-mysqld.00000 299 testdb

GTID Executed Set

92df196b-3906-11e3-b6b6-000c290d14d7:1

Replication Health Status

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

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

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

| demoenv-trial-2 | 3306 | MASTER | UP | ON | OK |

| demoenv-trial-3 | 3306 | SLAVE | UP | ON | OK |

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

Failoverstartingin'auto'mode...

# Candidate slave demoenv-trial-2:3306 will become the new master.

# Checking slaves status (before failover).

# Preparing candidate for failover.

# Creating replication user if it does not exist.

# Stopping slaves.

# Performing STOP on all slaves.

# Switching slaves to new master.

# Disconnecting new master as slave.

# Starting slaves.

# Performing START on all slaves.

# Checking slaves for errors.

# Failover complete.

# Discovering slaves for master at demoenv-trial-2:3306

Failoverconsolewillrestartin5seconds.

MySQLReplicationFailoverUtility

FailoverMode=autoNextInterval=SunOct2007:01:252013

MasterInformation

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

BinaryLogFilePositionBinlog_Do_DBBinlog_Ignore_DB

bin-log-mysqld.00000299testdb

GTIDExecutedSet

92df196b-3906-11e3-b6b6-000c290d14d7:1

ReplicationHealthStatus

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

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

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

|demoenv-trial-2|3306|MASTER|UP|ON|OK|

|demoenv-trial-3|3306|SLAVE|UP|ON|OK|

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

Q-quitR-refreshH-healthG-GTIDListsU-UUIDs

服务器 'demoenv-trial-2' 变成了新的master。

10. 插入数据测试主从复制

[dong.guo@demoenv-trial-2 ~]$ mysql -uroot

mysql> use testdb;

Database changed

mysql> CREATE TABLE `hostgroup` (

-> `hostgroup_id` tinyint(4) NOT NULL AUTO_INCREMENT,

-> `hostgroup_name` char(20) DEFAULT NULL,

-> `hostgroup_next` tinyint(4) NOT NULL,

-> `colo_name` char(4) NOT NULL,

-> PRIMARY KEY (`hostgroup_id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Query OK, 0 rows affected (0.10 sec)

mysql> quit;

[dong.guo@demoenv-trial-2 binlogs]$ mysql -uroot -ppass -h'demoenv-trial-3' testdb -e 'show tables;'

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

| Tables_in_testdb|

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

| hostgroup |

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

[dong.guo@demoenv-trial-2~]$mysql-uroot

mysql>usetestdb;

Databasechanged

mysql>CREATETABLE`hostgroup`(

->`hostgroup_id`tinyint(4)NOTNULLAUTO_INCREMENT,

->`hostgroup_name`char(20)DEFAULTNULL,

->`hostgroup_next`tinyint(4)NOTNULL,

->`colo_name`char(4)NOTNULL,

->PRIMARYKEY(`hostgroup_id`)

->)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_unicode_ci;

QueryOK,0rowsaffected(0.10sec)

mysql>quit;

[dong.guo@demoenv-trial-2binlogs]$mysql-uroot-ppass-h'demoenv-trial-3'testdb-e'show tables;'

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

|Tables_in_testdb|

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

|hostgroup|

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

11. 尝试将旧的master恢复

[dong.guo@demoenv-trial-1 ~]$ sudo /etc/init.d/mysql start

[dong.guo@demoenv-trial-1 ~]$ mysqlreplicate --master=root:pass@'demoenv-trial-2':3306 --slave=root:pass@'demoenv-trial-1':3306

# master on demoenv-trial-2: ... connected.

# slave on demoenv-trial-1: ... connected.

# Checking for binary logging on master...

# setting up replication...

# ...done.

1

2

3

4

5

6

7

[dong.guo@demoenv-trial-1~]$sudo/etc/init.d/mysqlstart

[dong.guo@demoenv-trial-1~]$mysqlreplicate--master=root:pass@'demoenv-trial-2':3306--slave=root:pass@'demoenv-trial-1':3306

# master on demoenv-trial-2: ... connected.

# slave on demoenv-trial-1: ... connected.

# Checking for binary logging on master...

# setting up replication...

# ...done.

在刚刚挂起的终端上，可以看到：

Replication Health Status

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

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

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

| demoenv-trial-2 | 3306 | MASTER | UP | ON | OK |

| demoenv-trial-1 | 3306 | SLAVE | UP | ON | OK |

| demoenv-trial-3 | 3306 | SLAVE | UP | ON | OK |

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

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

1

2

3

4

5

6

7

8

9

ReplicationHealthStatus

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

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

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

|demoenv-trial-2|3306|MASTER|UP|ON|OK|

|demoenv-trial-1|3306|SLAVE|UP|ON|OK|

|demoenv-trial-3|3306|SLAVE|UP|ON|OK|

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

Q-quitR-refreshH-healthG-GTIDListsU-UUIDs

将旧的master恢复

[dong.guo@demoenv-trial-1 ~]$ mysqlrpladmin --master=root:pass@'demoenv-trial-2':3306 --new-master=root:pass@'demoenv-trial-1':3306 --demote-master --discover-slaves-login=root:pass switchover

# Discovering slaves for master at demoenv-trial-2:3306

# Discovering slave at demoenv-trial-1:3306

# Found slave: demoenv-trial-1:3306

# Discovering slave at demoenv-trial-3:3306

# Found slave: demoenv-trial-3:3306

# Checking privileges.

# Performing switchover from master at demoenv-trial-2:3306 to slave at demoenv-trial-1: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 |

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

| demoenv-trial-1 | 3306 | MASTER | UP | ON | OK |

| demoenv-trial-2 | 3306 | SLAVE | UP | ON | OK |

| demoenv-trial-3 | 3306 | SLAVE | UP | ON | OK |

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

# ...done.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

[dong.guo@demoenv-trial-1~]$mysqlrpladmin--master=root:pass@'demoenv-trial-2':3306--new-master=root:pass@'demoenv-trial-1':3306--demote-master--discover-slaves-login=root:passswitchover

# Discovering slaves for master at demoenv-trial-2:3306

# Discovering slave at demoenv-trial-1:3306

# Found slave: demoenv-trial-1:3306

# Discovering slave at demoenv-trial-3:3306

# Found slave: demoenv-trial-3:3306

# Checking privileges.

# Performing switchover from master at demoenv-trial-2:3306 to slave at demoenv-trial-1: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|

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

|demoenv-trial-1|3306|MASTER|UP|ON|OK|

|demoenv-trial-2|3306|SLAVE|UP|ON|OK|

|demoenv-trial-3|3306|SLAVE|UP|ON|OK|

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

# ...done.

在刚刚挂起的终端上，可以看到：

MySQL Replication Failover Utility

Failover Mode = auto Next Interval = Sun Oct 20 07:30:07 2013

Master Information

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

Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB

bin-log-mysqld.00000 710 testdb

GTID Executed Set

8a58172b-1efd-11e3-8cf1-000c2950fe0c:1 [...]

Replication Health Status

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

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

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

| demoenv-trial-2 | 3306 | MASTER | UP | ON | OK |

| demoenv-trial-1 | 3306 | SLAVE | UP | WARN | Slave is not connected to master.|

| demoenv-trial-3 | 3306 | SLAVE | UP | WARN | Slave is not connected to master.|

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

MySQLReplicationFailoverUtility

FailoverMode=autoNextInterval=SunOct2007:30:072013

MasterInformation

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

BinaryLogFilePositionBinlog_Do_DBBinlog_Ignore_DB

bin-log-mysqld.00000710testdb

GTIDExecutedSet

8a58172b-1efd-11e3-8cf1-000c2950fe0c:1[...]

ReplicationHealthStatus

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

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

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

|demoenv-trial-2|3306|MASTER|UP|ON|OK|

|demoenv-trial-1|3306|SLAVE|UP|WARN|Slaveisnotconnectedtomaster.|

|demoenv-trial-3|3306|SLAVE|UP|WARN|Slaveisnotconnectedtomaster.|

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

Q-quitR-refreshH-healthG-GTIDListsU-UUIDs

可以看到failover在手动恢复了旧的master之后已经停止了工作。

按下Q，然后重启auto-failover：

[dong.guo@demoenv-trial-1 ~]$ mysqlfailover --master=root:pass@'demoenv-trial-1':3306 --discover-slaves-login=root:pass --rediscover

Replication Health Status

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

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

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

| demoenv-trial-1 | 3306 | MASTER | UP | ON | OK |

| demoenv-trial-2 | 3306 | SLAVE | UP | ON | OK |

| demoenv-trial-3 | 3306 | SLAVE | UP | ON | OK |

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

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

1

2

3

4

5

6

7

8

9

10

[dong.guo@demoenv-trial-1~]$mysqlfailover--master=root:pass@'demoenv-trial-1':3306--discover-slaves-login=root:pass--rediscover

ReplicationHealthStatus

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

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

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

|demoenv-trial-1|3306|MASTER|UP|ON|OK|

|demoenv-trial-2|3306|SLAVE|UP|ON|OK|

|demoenv-trial-3|3306|SLAVE|UP|ON|OK|

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

Q-quitR-refreshH-healthG-GTIDListsU-UUIDs

它又可以工作了。

12. 对于 auto-failover 的简短总结：

它包含在mysql-utilities这个软件包中；

它只能工作在MySQL 5.6 版本上，因为需要GITDs的支持；

它可以自动选取一个slave作为新的master，当现有的master死掉以后；

但是它不能自动将旧的master恢复，并且在手动恢复了旧的master之后也会停止工作。

转自http://heylinux.com/archives/2776.html

王羽翊
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql mha配置文件详解_十四、MySQL MHA 高可用配置（一）
weixin_36269385的博客
01-27 457
MHA 高可用1.准备三台机器IP：10.0.0.51 主机名：db01 内存：2GIP：10.0.0.52 主机名：db02 内存：2GIP：10.0.0.53 主机名：db03 内存：2G2.优化一、MHA概述1.简介MHA能够在较短的时间内实现自动故障检测和故障转移，通常在10-30秒以内;在复制框架中，MHA能够很好地解决复制过程中的数据一致性问题，由于不需要在现有的repli...
centos7安装mysql8以及常规操作.txt
07-01
centos7安装mysql8以及常规操作 ------------------------------------- [root@...opt]# vi /etc/my.cnf [mysqld] socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/...
mysql failover_MySQL管理工具MySQL Utilities — mysqlfailover(40)
weixin_39754831的博客
01-19 174
mysqlfailover 工具对复制进行健康检测和实现故障自动转移。该工具在定期的时间内进行间隔或持续的健康检测。主要任务是监视主失败，并在发送故障时候，自动执行故障切换到当前最优秀的从服务器上。mysqlfailover工具被设计成专门为了支持全局事务标识符(GTIDs)服务器，gtid_mode=ON。因此MySQL Server版本需要5.6.5或更高来支持GTIDs。使用--inte...
mysql mysqlfailover_MySQL 官方 Auto-Failover 功能测试
weixin_33735910的博客
01-19 195
环境介绍:master: demoenv-trial-1slaves: demoenv-trial-2 demoenv-trial-31. 安装 Percona Server，在所有服务器上：$ sudo yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm$ su...
mysql黑盒测试_MySQL 官方 Auto-Failover 功能测试
weixin_34668160的博客
02-05 161
参考资料： http://www.clusterdb.com/mysql/replication-and-auto-failover-made-easy-with-mysql-utilities 环境介绍: master: demoenv-trial-1 slaves: demoenv-trial-2 demoenv-trial-3 1. 安装 Percona Server，在所有服务器上： $ ...
MySQL Auto-Failover 功能测试
weixin_34409741的博客
06-12 259
为什么80%的码农都做不了架构师？>>> ...
MySQL 官方 Auto-Failover 功能测试
weixin_33805557的博客
06-12 209
为什么80%的码农都做不了架构师？>>> ...
mysql auto failover_基于全局事物ID的MySQLfailover的故障自动切换
weixin_30539317的博客
02-17 94
三台机器：192.168.1.163(MASTER)192.168.1.162(SLAVE1)192.168.1.161(SLAVE2)三台机器的配置文件如下：[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockinnodb_data_file_path=ibdata1:10M:autoextendsymbolic-lin...
MYSQL--架构--MGR--部署--03--配置
zhou920786312的博客
06-01 984
loose 前缀的意义在于第一次启动时还没加载组复制的plugin,可以让mysql server忽略该参数,继续启动。尝试从供体池连接到供体时，加入该组的服务器尝试的次数是10启动时加载插件，这里配置的是组复制的插件。是否使用克隆插件。Mysql服务ID将master的连接信息写入表：mysql.salve_master_info将中继日志的信息写入表:mysql.slave_realy_log_info从服务器 从 主服务器 收到的更新 记入到 从服务器 自己的二进制日志文件中。以行的格式记录将MySQ
mysql拷贝文件迁移出错_mysql迁移时错误总结
weixin_39754603的博客
01-27 668
错误1：数据库做迁移时，往往会出现各种问题，比如performance_schema，数据库perfor这个出错，可以尝试用此命令修复：mysql_upgrade-uroot-pmysql_upgrade-uroot-p--force错误2：找不到指定的innodb目录。在my.cnf配置文件中添加如下参数：innodb_data_home_dir=/home/data/data...
mysql主从复制
11-10
5> innodb_data_file_path = ibdata1:10M:autoextend表空间的名字以8M增长，可以将其大小修改：例如50M 6> innodb_log_group_home_dir = /usr/local/mysql/var/ 7> innodb_buffer_pool_size = 128M共享表空间，表...
关于JDBC与MySQL临时表空间的深入解析
12-15
临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象，相信大家在开发中经常会遇到相关的需求，下面本文将给大家详细JDBC与MySQL临时表空间的相关内容，分享出来供大家参考学习，下面话不...
MySQL 5.7临时表空间如何玩才能不掉坑里详解
12-16
导读 MySQL 5.7的目标是成为发布以来最安全的MySQL服务器，其在SSL/TLS和全面安全开发方面...innodb_temp_data_file_path = ibtmp1:12M:autoextend 临时表空间的几点说明 临时表空间不像普通InnoDB表空间那样，不支
MySQL问答系列之如何避免ibdata1文件大小暴涨
12-16
0、导读 ibdata1文件是什么？...显然这个文件会越来越大，innodb_autoextend_increment选项则指定了该文件每次自动增长的步进，默认是8M. 是什么原因导致ibdata1文件会越来越大？ ibdata1存放数据，
ar-track二维码数字8，标准15cm宽度打印
06-15
ar_track二维码数字8，标准15cm宽度打印
关于C语言的基础代码.zip
06-15
C语言是一种广泛使用的编程语言，它具有高效、灵活、可移植性强等特点，被广泛应用于操作系统、嵌入式系统、数据库、编译器等领域的开发。C语言的基本语法包括变量、数据类型、运算符、控制结构（如if语句、循环语句等）、函数、指针等。下面详细介绍C语言的基本概念和语法。 1. 变量和数据类型 在C语言中，变量用于存储数据，数据类型用于定义变量的类型和范围。C语言支持多种数据类型，包括基本数据类型（如int、float、char等）和复合数据类型（如结构体、联合等）。 2. 运算符 C语言中常用的运算符包括算术运算符（如+、、、/等）、关系运算符（如==、!=、、=、<、<=等）、逻辑运算符（如&&、||、！等）。此外，还有位运算符（如&、|、^等）和指针运算符（如、等）。 3. 控制结构 C语言中常用的控制结构包括if语句、循环语句（如for、while等）和switch语句。通过这些控制结构，可以实现程序的分支、循环和多路选择等功能。 4. 函数 函数是C语言中用于封装代码的单元，可以实现代码的复用和模块化。C语言中定义函数使用关键字“void”或返回值类型（如int、float等），并通过“{”和“}”括起来的代码块来实现函数的功能。 5. 指针 指针是C语言中用于存储变量地址的变量。通过指针，可以实现对内存的间接访问和修改。C语言中定义指针使用星号（）符号，指向数组、字符串和结构体等数据结构时，还需要注意数组名和字符串常量的特殊性质。 6. 数组和字符串 数组是C语言中用于存储同类型数据的结构，可以通过索引访问和修改数组中的元素。字符串是C语言中用于存储文本数据的特殊类型，通常以字符串常量的形式出现，用双引号（"..."）括起来，末尾自动添加'\0'字符。 7. 结构体和联合 结构体和联合是C语言中用于存储不同类型数据的复合数据类型。结构体由多个成员组成，每个成员可以是不同的数据类型；联合由多个变量组成，它们共用同一块内存空间。通过结构体和联合，可以实现数据的封装和抽象。 8. 文件操作 C语言中通过文件操作函数（如fopen、fclose、fread、fwrite等）实现对文件的读写操作。文件操作函数通常返回文件指针，用于表示打开的文件。通过文件指针，可以进行文件的定位、读写等操作。 总之，C语言是一种功能强大、灵活高效的编程语言，广泛应用于各种领域。掌握C语言的基本语法和数据结构，可以为编程学习和实践打下坚实的基础。
基于SpringBoot 和 Vue3 开发的博客系统源码，适合新手学习，二次开发，课程设计和毕业设计等.zip
06-15
该资源内项目源码是个人的课程设计、毕业设计，代码都测试ok，都是运行成功后才上传资源，答辩评审平均分达到96分，放心下载使用！ ## 项目备注 1、该资源内项目代码都经过测试运行成功，功能ok的情况下才上传的，请放心下载使用！ 2、本项目适合计算机相关专业(如计科、人工智能、通信工程、自动化、电子信息等)的在校学生、老师或者企业员工下载学习，也适合小白学习进阶，当然也可作为毕设项目、课程设计、作业、项目初期立项演示等。 3、如果基础还行，也可在此代码基础上进行修改，以实现其他功能，也可用于毕设、课设、作业等。 下载后请首先打开README.md文件（如有），仅供学习参考, 切勿用于商业用途。 该资源内项目源码是个人的课程设计，代码都测试ok，都是运行成功后才上传资源，答辩评审平均分达到96分，放心下载使用！ ## 项目备注 1、该资源内项目代码都经过测试运行成功，功能ok的情况下才上传的，请放心下载使用！ 2、本项目适合计算机相关专业(如计科、人工智能、通信工程、自动化、电子信息等)的在校学生、老师或者企业员工下载学习，也适合小白学习进阶，当然也可作为毕设项目、课程设计、作业、项目初期立项演示等。 3、如果基础还行，也可在此代码基础上进行修改，以实现其他功能，也可用于毕设、课设、作业等。 下载后请首先打开README.md文件（如有），仅供学习参考, 切勿用于商业用途。
chromedriver-win64_120.0.6099.28.zip
最新发布
06-15
chromedriver-win64_120.0.6099.28.zip
mysql 报错 MY-012264
06-13
MY-012264 是 MySQL 数据库中的一个错误代码，表示 MySQL 实例在启动时无法创建 InnoDB 表空间。 这个错误通常是由于磁盘空间不足或者权限不足导致的。您可以通过检查 MySQL 错误日志文件来获取更多信息，例如在 Linux 系统上，这个文件通常位于 /var/log/mysql/error.log。 解决这个问题的方法包括： 1. 确保磁盘空间充足，并且 MySQL 数据目录有足够的空间。 2. 检查 MySQL 数据目录的权限，确保 MySQL 用户拥有足够的权限。 3. 在 MySQL 配置文件中增加或修改以下参数： innodb_data_file_path = ibdata1:10M:autoextend 这个参数指定了 InnoDB 表空间的大小和自动扩展策略。 您可以根据实际情况修改其中的参数值。 4. 如果您使用的是 MySQL 8.0 版本，可以尝试使用以下命令修复： mysql_upgrade --force 这个命令会检查并修复 MySQL 数据库中的问题。 如果以上方法无法解决问题，建议您向 MySQL 社区或者 MySQL 官方技术支持寻求帮助。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值