MySQL Utilities 1.4 Manual  /  ...  /  Providing Information to Choose a MySQL Server

8.8.3. Providing Information to Choose a MySQL Server

If you create a Fabric connection without providing any information about which data to access, the connection cannot function. To access a database, you must provide the driver with either of these types of information:

  • The name of a high-availability group known by the MySQL Fabric instance to which you've connected. In such a group, one server is the master (the primary) and the others are slaves (secondaries).

  • A shard table, and optionally a shard key, to guide Connector/Python in selecting a high-availability group.

The following discussion describes both ways of providing information. You do this by setting one or more properties of the Fabric connection object using its set_property() method, so the discussion begins by setting forth the sharding-related properties.. In the examples, fcnx represents the Fabric connection object, created as shown in Section 8.8.2, “Requesting a Fabric Connection”.

Note

set_property() does not connect. The connection is opened when a cursor is requested from the Fabric connection object or when its cmd_query() or cmd_query_iter() method is invoked.

These set_property() arguments are shard-related:

  • group: A high-availability group name

  • tables: The sharding table or tables

  • mode: Whether operations are read/write or read only

  • scope: Whether operations are local or global

  • key: The key that identifies which row to affect

group and tables are mutually exclusive, so you specify only one of them. Applicability of the remaining arguments depends on which of group or tables you use:

If you specify group:

  • mode is optional. The default is fabric.MODE_READWRITE if this property is omitted.

  • scope is inapplicable. Do not specify it.

  • key is inapplicable. Do not specify it.

If you specify tables:

  • mode is optional. The default is fabric.MODE_READWRITE if this property is omitted.

  • scope is optional. The default is fabric.SCOPE_LOCAL if this property is omitted.

  • key: If scope is fabric.SCOPE_LOCAL, key is required to indicate which row to affect. If scope is fabric.SCOPE_GLOBAL, key is inapplicable; do not specify it.

When the mode argument is applicable, these values are permitted:

  • fabric.MODE_READWRITE: Connect to a master server. This is the default.

  • fabric.MODE_READONLY: Connect to a slave if one is available, to the master otherwise. If there are multiple secondary MySQL servers, load balancing is used to obtain the server information.

When the scope argument is applicable, these values are permitted:

  • fabric.SCOPE_LOCAL: Local operation that affects the row with a given key. This is the default.

  • fabric.SCOPE_GLOBAL: Global operation that affects all rows.

Providing the name of a high-availability group specifies that we know exactly the set of database servers that with which to interact. To do this, set the group property using the set_property() method:

fcnx.set_property(group='myGroup')

Providing shard information avoids the need to choose a high-availability group manually and permits Connector/Python to do so based on information from the MySQL Fabric server.

Whether operations use RANGE or HASH is transparent to the user. The information is provided by Fabric and Connector/Python uses the correct mechanism automatically.

To specify shard tables and shard keys, use the tables and key attributes of the set_property() method.

The format of each shard table is usually given as 'db_name.tbl_name'. Because one or more tables can be specified, the tables argument to set_property() is specified as a tuple or list:

fcnx.set_property(tables=['employees.employees'], key=40)
cur = fcnx.cursor()
# do operations for employee with emp_no 40
fcnx.close()

By default, operations occur in local scope, or the scope property can be given to specify local or global scope explicitly. For local operations (as in the preceding example), the key argument must be specified to indicate which row to use. For global operations, do not specify the key attribute because the operation is performed on all rows in the table:

fcnx.set_property(tables=['employees.employees'], scope=fabric.SCOPE_GLOBAL)
cur = fcnx.cursor()
cur.execute("UPDATE employees SET last_name = UPPER(last_name)")
cnx.commit()
fcnx.close()

The default mode is read/write, so the driver connects to the master. The mode property can be given to specify read/write or read-only mode explicitly:

fcnx.set_property(group='myGroup', mode=fabric.MODE_READWRITE)
cur = fcnx.cursor()
cur.execute("UPDATE employees SET last_name = UPPER(last_name)")
cnx.commit()
fcnx.close()

Applications for which read-only mode is sufficient can specify a mode attribute of fabric.MODE_READONLY. In this case, a connection is established to a slave if one is available, or to the master otherwise.


User Comments
Sign Up Login You must be logged in to post a comment.