MySQL Fabric é uma ferramenta que está inclusa no MySQL Utilities que ajuda a gerenciar servidores MySQL.
Ele funciona basicamente adicionando uma nova camada entre a aplicação e os servidores MySQL, que auxilia no processo de sharding e alta disponibilidade.
Para instalar nosso ambiente com MySQL Fabric, vamos precisar de 4 servidores, eu utilizei os seguintes nomes e IPs:
fabric1 (192.168.0.200) - fabric mysql1 (192.168.0.201) - mysql master mysql2 (192.168.0.202) - mysql slave mysql3 (192.168.0.203) - mysql slave
Obs.: Estou rodando CentOS 6.5 em todos os servidores.
1. Adicione o repositório mysql nos 4 servidores, leia Instalar a versão mais recente do MySQL via yum para mais informações:
rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum update
2. Instale os pacotes mysql mysql-server mysql-utilities:
yum install mysql mysql-server mysql-utilities
chkconfig mysqld on
/etc/init.d/mysqld start
3. Nos servidores mysql1,mysql2,mysql3 adicione as seguintes configurações no arquivo my.cnf:
[mysqld] ... binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 report-host=192.168.0.201 report-port=3306 server-id=1 log-bin=mysql1-bin.log
4. No servidor mysql1, adicione os usuários para a replicação (um para cada servidor mysql):
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd';
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd'; " [root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd'; " [root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd'; "
5. No servidor mysql1 adicione o usuário MySQL para o servidor fabric:
GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';
[root@mysql1 ~]# mysql -u root -e "GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';"
6. Nos servidores mysql2 and mysql3, configure a replicação:
CHANGE MASTER TO MASTER_HOST='192.168.0.201', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;
7. No servidor fabric1 adicione o usuário fabric no MySQL:
GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';
[root@fabric1 ~]# mysql -u root -e "GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';"
8. No servidor fabric1, configure o usuário e password no grupos [storage] and [servers] no arquivo /etc/mysql/fabric.cfg :
[storage] ... password = fabricpwd ... [servers] password = reppwd user = replication
9. No servidor fabric1, configure o banco de dados que será utilizado pelo MySQL Fabric, será perguntado para configurar uma senha, esta senha será utilizada nas próximas vezes que utilizarmos o mysqlfabric:
mysqlfabric manage setup
[root@fabric1 ~]# mysqlfabric manage setup [INFO] 1406131468.176740 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric). Finishing initial setup ======================= Password for admin user is not yet set. Password for admin/xmlrpc: Repeat Password: Password set.
10. No servidor fabric1, inicie o fabric:
mysqlfabric manage start &
11. No servidor fabric1, adicione um grupo:
mysqlfabric group create GLOBAL1
[root@fabric1 ~]# mysqlfabric group create GLOBAL1 Password for admin: Procedure : { uuid = 5e4a6bdb-60f0-4e34-87ba-4c56b7616b35, finished = True, success = True, return = True, activities = }
12. No servidor fabric1, adicione mysql1, mysql2 e mysql3 ao grupo GLOBAL1:
mysqlfabric group add GLOBAL1 192.168.0.201
mysqlfabric group add GLOBAL1 192.168.0.202
mysqlfabric group add GLOBAL1 192.168.0.203
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.201 Password for admin: Procedure : { uuid = 39efb9c4-6195-4c41-aa02-0bfdc228bfe2, finished = True, success = True, return = True, activities = } [root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.202 Password for admin: Procedure : { uuid = c8babfb9-d836-44c0-b4fd-015cd1df8298, finished = True, success = True, return = True, activities = } [root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.203 Password for admin: Procedure : { uuid = c86bba70-69ac-4923-9c54-1a8aaab6d97e, finished = True, success = True, return = True, activities = }
13. No servidor fabric1, pegue o uuid do servidor master:
mysqlfabric group lookup_servers GLOBAL1
[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1 Password for admin: Command : { success = True return = [{'status': 'SECONDARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}] activities = }
14. No servidor fabric1, adicione o seu master como master do grupo GLOBAL1:
mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'
[root@fabric1 ~]# mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242' Password for admin: [WARNING] 1406131951.712366 - Executor-2 - Error () trying to process transactions in the relay log for candidate (('Command (START SLAVE SQL_THREAD, ()) failed: 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO', 1200)). [INFO] 1406131951.824763 - Executor-2 - Master has changed from None to 2e157d1e-1281-11e4-80dc-080027aa0242. Procedure : { uuid = 733ae69d-fb12-447b-b86b-041703491315, finished = True, success = True, return = True, activities = } [root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1 Password for admin: Command : { success = True return = [{'status': 'PRIMARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}] activities = } [root@fabric1 ~]#
Agora temos nosso ambiente MySQL Fabric funcionando .
Não perca os próximos post para aprender mais sobre essa nova ferramenta.