Deployment and use of Fabric for MySQL sharded high-availability clusters

Deployment and use of Fabric for MySQL sharded high-availability clusters

Fabric is a MySQL cluster management tool developed by Oracle, which has both sharding and read/write splitting. Although I think the current version has many defects, it should be gradually improved, it will be a good tool in the future.

Step 1: download and install Fabric

Fabric can be downloaded from the MySQL official website, she belongs to MySQL Utilites, the official is http://dev.mysql.com/downloads/utilities/

I download the source code version, mysql-utilities-1.5.6.zip, because it is written in Python, so the installation is different from the C language:

$ Unzip mysql-utilities-1.5.6.zip

$ Cd mysql-utilities-1.5.6/

$ Python setup. py build

$ Sudo python setup. py install

After installation, the original python script removes the py suffix and is stored in the/usr/local/bin/directory by default, which can be directly executed.

Step 1: deploy multiple instances of MySQL5.6

Before Running Fabric, we need to prepare several databases and configure different databases as needed. First, we need to deploy the basic master-slave mode of read/write splitting, fabric needs to use the GTID feature of MySQL for master-slave replication. Therefore, MySQL 5.6 or later is required. In addition, due to the GTID feature of MariaDB, unlike MySQL, Fabric does not support MariaDB. If MariaDB is used, an error is reported. Therefore, only MySQL and later versions can be used.

We need to deploy at least three MySQL instances to see the Fabric effect,

Function	Address	Port	Data File Path	Configuration File Path
Fabric metabase	Localhost	10000	/Dev/shm/data/fa00	Fabric/fa00.cnf
Business Database 1	Localhost	10011	/Dev/shm/data/fa11	Fabric/fa11.cnf
	Localhost	10012	/Dev/shm/data/fa12	Fabric/fa12.cnf

TIP: I started a lot of databases on a computer, so I put all the data files in the memory hard disk. By default, some of my machines are/dev/shm, other machines may vary, such as/run/shm. The path selection is irrelevant as long as the parent directory is created in advance. Because the memory hard disk is used, MySQL's memory requirements are reduced. I reduced the memory usage required by MySQL, which is also based on my own situation. Note: The production environment cannot be configured like this.

For details about master-slave database deployment, refer to: and

Next we will modify the configuration file first. The content to be modified is as follows: (for example, fa00.cnf)

# Adjust the numbers in the first six rows by yourself. Each database cannot be the same. Here, the numbers corresponding to the last two 00 are changed.

[Client]

Port = 10000

Socket =/tmp/fa00.sock

 

[Mysqld]

Port = 10000

Socket =/tmp/fa00.sock

Datadir =/dev/shm/data/fa00

Servers-id = 10000

User = lyw

 

# Master-slave Replication

Log-bin = mysql-bin

Gtid-mode = on

Log-slave-updates = true

Enforce-gtid-consistency = true

# File and memory size, saving memory.

Innodb_buffer_pool_size = 32 M

Innodb_log_file_size = 5 M

After the configuration files fa00.cnf, fa11.cnf, and fa12.cnf are modified, data is initialized and started. This time, we use batch operations to reduce the workload. Create the following script init_start.sh and initialize the database file fabric. SQL In the mysql directory, and execute init_start.sh to create and start all databases.

Fabric. SQL content:

Use mysql;

Delete from user where user = '';

Flush privileges;

Grant all on *. * to 'fabric '@' % 'identified by '123 ';

Create database lyw;

Reset master;

The content of init_start.sh is:

#! /Bin/bash

Mkdir-p/dev/shm/data

For cnf in 'ls fabric/*. cnf'

Do

Scripts/mysql_install_db -- defaults-file = $ cnf

Bin/mysqld -- defaults-file = $ cnf &

Done

# Wait Until mysqld is started,

Sleep 3

 

For cnf in 'ls fabric/*. cnf'

Do

Bin/mysql -- defaults-file = $ cnf-uroot <fabric. SQL

Done

After the script is ready, execute init_start.sh to initialize and start all databases. You can start the mysql client to check whether all databases are initialized. Fabric does not need to actively execute the change master to SQL statement to enable the master and slave nodes. Instead, it is executed by Fabric itself. To prepare the database, start Fabric configuration.

Step 3: Fabric read/write splitting Master/Slave Configuration

The default configuration file path of fabric is/usr/local/etc/mysql/fabric. cfg. Other installation methods are/etc/mysql/fabric. cfg (other systems are configured according to their own circumstances). Therefore, to facilitate subsequent operations, we still use this configuration file. Of course, you can also use the -- config parameter to specify the configuration file.

The contents of fabric. cfg are as follows:

[DEFAULT]

Prefix =/usr/local

Sysconfdir =/usr/local/etc

Logdir =/var/log

# Storage is configured with a database for fabric metadata storage.

[Storage]

Address = localhost: 10000

User = fabric

Password = 123456

Database = fabric

Auth_plugin = mysql_native_password

Connection_timeout = 6

Connection_attempts = 6

Connection_delay = 1

 

[Servers]

User = fabric

Password = 123456

Backup_user = fabric_backup

Backup_password = secret

Restore_user = fabric_restore

Restore_password = secret

Unreachable_timeout = 5

 

# Fabric external protocol. Here is the xmlrpc protocol.

[Protocol. xmlrpc]

Address = localhost: 32274

Threads = 5

User = admin

Password = 123456

Disable_authentication = yes

Realm = MySQL Fabric

Ssl_ca =

Ssl_cert =

Ssl_key =

 

# Fabric external protocol. Here is the mysql protocol, which can be connected with mysql, but cannot be operated the same as that of a common database.

[Protocol. mysql]

Address = localhost: 32275

User = admin

Password = 123456

Disable_authentication = yes

Ssl_ca =

Ssl_cert =

Ssl_key =

 

[Executor]

Executors = 5

 

[Logging]

Level = INFO

Url = file: // var/log/fabric. log

 

[Sharding]

Mysqldump_program =/usr/bin/mysqldump

Mysqlclient_program =/usr/bin/mysql

 

[Statistics]

Prune_time = 3600.

 

[Failure_tracking]

Communications = 300

Icationication_clients = 50

Notification_interval = 60

Failover_interval = 0

Detections = 3

Detection_interval = 6

Detection_timeout = 1

Prune_time = 3600.

 

[Connector]

Ttl = 1

After configuration, You need to initialize fabric metadata
 

$ Mysqlfabric manage setup

......

Finishing initial setup

======================================

Password for admin user is not yet set.

Password for admin/xmlrpc: Set the admin Password here.

Repeat Password: enter the Password again

Password set.

Password set.

At this time, the database fa00 already has fabric metadata, which can be viewed on the mysql client.

Then start

$ Mysqlfabric manage start

Although fabric has been started since then, it has not been associated with the last two business databases configured previously. Next, we need to use the command line to establish a connection. Let's take a look at the help of mysqlfabric grouping first,

$ Mysqlfabric help group

Commands available in group 'group' are:

Group activate group_id [-- synchronous]

Group description group_id [-- description = NONE] [-- synchronous]

Group deactivate group_id [-- synchronous]

Group create group_id [-- description = NONE] [-- synchronous]

Group remove group_id server_id [-- synchronous]

Group add group_id address [-- timeout = NONE] [-- update_only] [-- synchronous]

Group health group_id

Group lookup_servers group_id [-- server_id = NONE] [-- status = NONE] [-- mode = NONE]

Group destroy group_id [-- synchronous]

Group demote group_id [-- update_only] [-- synchronous]

Group promote group_id [-- slave_id = NONE] [-- update_only] [-- synchronous]

Group lookup_groups [-- group_id = NONE]

First, we need to create a group-1:

$ Mysqlfabric group create group-1

Then, put the two business databases 10011 and 10012 into this group:

$ Mysqlfabric group add group-1 fig: 10011

$ Mysqlfabric group add group-1 fig: 10012

Then you can view the status of the group-1.

$ Mysqlfabric group lookup_servers group-1

Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e

Time-To-Live: 1

 

Server_uuid address status mode weight

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

D4919ca2-754a-11e5-8a5e-34238703623c 127.0.0.1: 10011 seconds READ_ONLY 1.0

D6597f06-754a-11e5-8a5e-34238703623c 127.0.0.1: 10012 seconds READ_ONLY 1.0

From the status and mode fields, we can see that the two newly added servers have not yet taken effect. They are both read-only identities of slave databases, and there is no substantive connection between the two databases, we need to promote a database to a writable master:

$ Mysqlfabric group promote group-1

 

$ Mysqlfabric group lookup_servers group-1

Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e

Time-To-Live: 1

Server_uuid address status mode weight

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

D4919ca2-754a-11e5-8a5e-34238703623c 127.0.0.1: 10011 seconds READ_ONLY 1.0

D6597f06-754a-11e5-8a5e-34238703623c 127.0.0.1: 10012 PRIMARY READ_WRITE 1.0

After you use the promote command to upgrade, one of them will be promoted to the master database, and the others will be upgraded from the slave database. The master and slave databases can also be viewed through the mysql client. The following command is displayed when the slave database executes the following command.

Mysql> show slave status \ G;

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: fabric

Master_Port: 10012

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 151

Relay_Log_File: lyw-hp-relay-bin.000002

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

......

At this point, the master-slave mode is basically available, and we can't wait to try how to use the client. Python and java are officially provided. Therefore, if Fabric is used, it is best to use these two business languages.

For more details, please continue to read the highlights on the next page:

1 2 Next Page