注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

MySQL 官方 Auto-Failover 功能测试  

2016-01-11 16:28:28|  分类: MySQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

MySQL 官方 Auto-Failover 功能测试


参考资料:

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


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

 

[client]

port = 3306

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

default-character-set = utf8


3. 创建所需目录,在所有服务器上:

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

$ sudo chown mysql: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


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

$ mysql -uroot


1

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

2

mysql> quit;

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

$ mysql -uroot


1

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

2

mysql> quit;

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

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


1

mysql> use mysql;

2

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

3

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


1

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

2

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

3

# Checking for binary logging on master...

4

# set up replication...

5

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


1

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

2

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

3

# Checking for binary logging on master...

4

# set up replication...

5

# ...done.

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

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


1

mysql> create database testdb;

2

mysql> quit;

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


1

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

2

| Database          |

3

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

4

| information_schema|

5

| mysql             |

6

| performance_schema|

7

| test              |

8

| testdb            |

9

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

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


1

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

2

| Database          |

3

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

4

| information_schema|

5

| mysql             |

6

| performance_schema|

7

| test              |

8

| testdb            |

9

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

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


1

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

2

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

3

 

4

# Replication Topology Graph

5

demoenv-trial-1:3306 (MASTER)

6

  |

7

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

8

  |

9

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

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


01

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

02

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

03

Test Description                          Status

04

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

05

Checking for binary logging on master     [pass]

06

Are there binlog exceptions?              [WARN]

07

 

08

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

09

| server | do_db  | ignore_db |

10

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

11

| master | testdb |           |

12

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

13

 

14

Replication user exists?                  [pass]

15

Checking server_id values                 [pass]

16

Checking server_uuid values               [pass]

17

Is slave connected to master?             [pass]

18

Check master information file             [pass]

19

Checking InnoDB compatibility             [pass]

20

Checking storage engines compatibility    [pass]

21

Checking lower_case_table_names setting   [pass]

22

Checking slave delay (seconds behind master)                         [pass]

23

# ...done.  

9. 关闭 master 测试 auto-failover

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


01

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

02

# Discovering slave at demoenv-trial-2:3306

03

# Found slave: demoenv-trial-2:3306

04

# Discovering slave at demoenv-trial-3:3306

05

# Found slave: demoenv-trial-3:3306

06

# Checking privileges.

  评论这张
 
阅读(65)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017