MySQL High availability-Fabric installation configuration

About Fabric

MySQL fabric is launched by Oracle, which simplifies the management of MySQL clusters and offers two main features:

1 high availability with fault detection and failover

2 scalability through automatic data fragmentation

Fabric Prerequisites

• MySQL server 5.6.10 or later for fabric MySQL servers (fabric is based on the new features of MySQL 5.6 gtid)

• MySQL server 5.6.x or later for the backing store.

• python 2 (2.6 or later) for the  mysqlfabric  utility.

• a Fabric-aware connector to Use the Fabric in applications. Permitted connectors and versions are:

  • Connector/python 1.2.1 or later

  • Connector/j 5.1.27 or later

The above is the MySQL fabric website introduced

Fabric Required Installation Package

Server node:

MYSQL-SERVER-5.6.21-1.EL6.X86_64 # MySQL Server pack, installing this package requires uninstalling Mysql-libs this package first

MYSQL-CLIENT-5.6.21-1.EL6.X86_64 # MySQL Client package, can not be installed, not installed there is no MySQL command line command

Mysql-shared-5.6.21-1.el6.x86_64

mysql-shared-compat-5.6.21-1.el6.x86_64 # Uninstalling the Mysql-libs package will have some dependency issues, install mysql-shared These two packages can solve the dependency

Client node:

MYSQL-CLIENT-5.6.21-1.EL6.X86_64 # MySQL Client package, can not be installed, not installed there is no MySQL command line command

Mysql-connector-python-2.0.1-1.el6.noarch # App connects Fabric driver, not the same as Mysql-python this package

Fabric node:

Mysql-connector-python-2.0.1-1.el6.noarch # App connects fabric driver, Fabric node can not install

Mysql-utilities-1.5.2-1.el6.noarch # Mysqlfabric included in this bag

MYSQL-SERVER-5.6.21-1.EL6.X86_64 # MySQL Server pack, installing this package requires uninstalling Mysql-libs this package first

MYSQL-CLIENT-5.6.21-1.EL6.X86_64 # MySQL Client package, can not be installed, not installed there is no MySQL command line command

Mysql-shared-5.6.21-1.el6.x86_64

mysql-shared-compat-5.6.21-1.el6.x86_64 # Uninstalling the Mysql-libs package will have some dependency issues, install mysql-shared These two packages can solve the dependency

Fabric Specific Configuration

My environment:

Linux system CentOS 6.5 x86_64

Controller1 192.168.141.110 server Node

Controller2 192.168.141.120 server Node

Controller3 192.168.141.130 Fabric Node

[[The Email protected] ~]# mysql -uroot -p   # fabric node needs to be in backing Create a fabric database on  store mysql server, where the fabric nodes and backing store mysql  Server mysql> create user  ' fabric ' @ ' localhost '  identified by  ' fabric ' on the same machine   #  Connect backing store mysql server Certified user mysql> grant all on  fabric.* to  ' fabric ' @ ' localhost ' [[email protected] ~]# mysqlfabric help  commands  #  displays all the Mysqlfabric commands [[email protected] ~]# cd /etc/mysql/    #  Edit fabric.cfg configuration file [[email protected] mysql]# cp fabric.cfg  fabric.cfg.bak  #  backup fabric.cfg configuration file [[email protected] mysql]# vim fabric.cfg [default]prefix = sysconfdir = /etc  #  Configuration file Directory Logdir = /var/log    #日志目录 [STATISTICS] &NBsp;# how often the internal event log is pruned, in seconds  and also the age of events in the event log that  is used to present statistics.prune_time = 3600[logging]url = file:/// var/log/fabric.loglevel = info  #  log Level [storage]auth_plugin = mysql_native_ passworddatabase = fabric  #连接backing  store mysql server Database name user =  fabric  #  user name to connect backing store mysql server fabric database address =  Localhost:3306  # backing store mysql server address and port, localhost description backing store  mysql server and fabric nodes on the same machine connection_delay = 1  connection_timeout =  6password = fabric #  Connection backing store mysql server  The password for the fabric database Connection_attempts = 6  [failure_tracking]notification_interval = 60notification_clients =  50detection_timeout = 1detection_interval = 6notifications = 300detections  = 3failover_interval = 0prune_time = 3600[servers]password = oracle  The   # fabric node connects to the server authentication password in Mysql hagroup user = oracle #  The user name of the server authentication in the Fabric node connection Mysql hagroup unreachable_timeout = 5[connector]ttl = 1[client ]  # this section is used by the mysql client when  called from MySQL Fabric and is not used MySQL  fabric.password = oracle [protocol.xmlrpc] # this section contains  information about how the client connects to a mysql fabric  Node and configuration parameters for the xml-rpc protocol on the serverdisable_authentication =  nossl_cert = realm = mysql fabricssl_key = ssl_ca = threads  = 5user = adminaddress = controller3:32274password = admin [executor]   # the executor executes procedures in a serial order,  which guarantees that requests do not conflict.executors = 5[ sharding]  # to perform operations such as moving and  splitting shards, mysql fabric relies on the mysqldump and  mysqlclient programs.prune_limit = 10000mysqldump_program = /usr/bin/mysqldump   mysqlclient_program = /usr/bin/mysql[protocol.mysql]  #  official documents I didn't find this explanation, I think it's similar to PROTOCOL.XMLRPC. Disable_authentication = nossl_cert = ssl_key = ssl_ca = user =  MY.CNF configuration file for the Adminaddress = localhost:32275password = admin# fabric node [[email  protected] mysql]# cat /etc/my.cnf[mysqld]bind_address = localhostdatadir=/var/lib/ mysqlcollation-server = utf8_general_ciinit-connect =  ' Set names utf8 ' character-set-server = utf8default-storage-engine = innodbinnodb_file_per_table =  1innodb_buffer_pool_size = 512Mlog_bin                        gtid_mode=on   enforce_gtid_consistency=on  log_slave_updates=1#  initializing the Fabric database [[email protected]  Mysql]# mysqlfabric manage setup --param=storage.user=fabric --param=storage.password =fabric   #如果报错见下面的Trouble  shooting#  successful execution will let you enter the password for the admin user [[email protected]] mysqlfabric manage start --daemonize  #  start the fabric management system [[Email protected]] mysqlfabric group create mysql_group  #  Create a management group mysql_group[[email protected]] mysqlfabric group lookup_groups   #  View Management Group information #  you need to create an authorized user on each mysql server before each mysql server is added to the administrative group Mysql_group [[email  protected] ~]# mysql -uroot -e  "Grant all on *.* to [email  protected] '% '  identified by  ' Oracle '; " [[email protected] ~]# mysql -uroot -e  "Grant all on *.* to  [email protected] '% '  identified by  ' Oracle '; " [[Email protected]] mysqlfabric group add mysql_group controller1:3306[[email  protected]] mysqlfabric group add mysql_group controller2:3306  #   Finished addingAfter the node, view the group information, two are secondary, remember to write controller1 in the/etc/hosts file, IP mapping from controller2 [[Email protected]] mysqlfabric  group lookup_servers mysql_group[[email protected]] mysqlfabric group  health mysql_group  #  You can view node state information in either of these ways [[Email protected]] mysqlfabric group After  promote mysql_group # promote, Fabric will elect one as Primary , demote is to cancel the primary election [[ email protected]] mysqlfabric group activate mysql_group  #   Activation failure detection [[Email protected]ntroller3 ~]# mysqlfabric group lookup_servers mysql_ groupfabric uuid:  5ca1ab1e-a007-feed-f00d-cab3fe13249etime-to-live: 1                           server_uuid          address     status       mode weight------------------------------------ ----------------  --------- ---------- ------086193ff-4f7f-11e4-8e93-525400788967 controller2:3306  Secondary  read_only    1.00c826bd0-4f8c-11e4-8ee8-5254003d38c9 controller1 : 3306   primary read_write    2.0

The client can connect through the fabric connector, but the code for this part of the client connection operation in OpenStack is not implemented, and the fabric node is also highly available, hoping that Oracle will improve as soon as possible.

Trouble Shooting

1 Initialize Fabric database mysqlfabric Manage setup--param=storage.user=fabric--param=storage.password=fabric execution may have the following error:

Error:command (CREATE TABLE machines (machine_uuid varchar) NOT NULL, provider_id varchar (in the) not NULL, Av_zone Varch AR (), addresses TEXT, INDEX idx_machine_provider_id (provider_id)), failed accessing (localhost:3306). 1071 (42000): Specified key was too long; Max key length is 767 bytes.

Reason: MySQL's varchar primary key only supports fields up to 768 bytes or 768/2=384 or 768/3=256 three bytes and GBK is double byte, UTF-8 is three bytes.

Reference Link: http://lsting.iteye.com/blog/707749

Solve:

[[email protected] mysql]# vim/etc/my.cnf # Edit my.cnf file, first comment out the following utf-8 configuration options, restart the MySQL service, and then go to initialize the fabric database, after the initialization is complete, and then remove the comment.

[Mysqld] #collation-server = Utf8_general_ci#init-connect = ' SET NAMES utf8 ' #character-set-server = UTF8

2 when a database node after a failure to recover, you need to turn the node into spare, and then switch to seconary, this really makes the egg ache, there is no automatic recovery mechanism

[[email protected]] Mysqlfabric server set_status 086193ff-4f7f-11e4-8e93-525400788967 spare[[email protected]] Mysqlfabric Server Set_status 086193ff-4f7f-11e4-8e93-525400788967 Secondary

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/2B/wKioL1Q3mEfAafBrAAEAip5FRhA219.jpg "title=" M9 ' YM} H%ghjmrt%v9_~}j12.png "alt=" Wkiol1q3mefaafbraaeaip5frha219.jpg "/>

This is the server state switch diagram of the fabric management system (http://mysqlmusings.blogspot.fr/2013/10/mysql-fabric-high-availability-groups.html), see this should all understand!

Reference links

Http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric.html

http://blog.itpub.net/25704976/viewspace-1180262

Http://www.luocs.com/archives/862.html

http://www.percona.com/blog/2014/05/15/high-availability-mysql-fabric-part/

This article is from the "The-way-to-cloud" blog, make sure to keep this source http://iceyao.blog.51cto.com/9426658/1562266

MySQL High availability-Fabric installation configuration