测试MySQL Fabric用于replication的实验

        今天测试了MySQL Fabric用于replication的实验,感觉MySQL的官方文档说的不是很清楚,特别是Fabric的配置文件解释的不清楚。习惯了Oracle的官方文档的条理清晰。测试过程如下:


== centos 6.3_x86 ==

node1  192.168.201.11    backing store (mysql fabric + mysql-server-5.6.19 + mysql-connector-python-1.2.2 )
node2  192.168.201.12     primary(mysql-server-5.6.19)
node4  192.168.201.14    secondary(mysql-server-5.6.19)
node5  192.168.201.15    secondary(mysql-server-5.6.19)

== node1 ==
[root@node1 ~]# rpm -ivh  http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
[root@node1 ~]# yum install mysql-community-server.i686 mysql-community-libs.i686 mysql-community-client.i686 mysql-utilities.noarch
[root@node1 ~]# vim /etc/my.cnf
[root@node1 ~]# mysql_install_db --defaults-file=/etc/my.cnf --user=mysql
[root@node1 ~]# service mysqld start
[root@node1 ~]# mysql_secure_installation
[root@node1 ~]# mysql -u root -p -h 127.0.0.1 -P 63306
mysql> create user fabric@'localhost' identified by 'fabric';
mysql> grant all on fabric.* to fabric@'localhost';
mysql> flush privileges;

== node2 ==
[root@node2 ~]# rpm -ivh  http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
[root@node2 ~]# yum install mysql-community-server.i686 mysql-community-libs.i686 mysql-community-client.i686 
[root@node2 ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
port=63307
character-set-server=utf8

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.

log_bin
gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=20112

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@node2 ~]# mysql_install_db --defaults-file=/etc/my.cnf --user=mysql
[root@node2 ~]# service mysqld start
[root@node2 ~]# mysql_secure_installation
mysql> create user fabric@'192.168.201.%' identified by 'fabric';
mysql> grant all on *.* to fabric@'192.168.201.%';
mysql> flush privileges;
[root@node2 ~]# mysqldump -u root -p -h 127.0.0.1 -P63307 < a.sql

== node4 == 
[root@node4 ~]# rpm -ivh  http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
[root@node4 ~]# yum install mysql-community-server.i686 mysql-community-libs.i686 mysql-community-client.i686 
[root@node4 ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
port=63308
character-set-server=utf8

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.

<span style="color:#FF0000;">log_bin
gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1</span>

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=20114

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@node4 ~]# mysql_install_db --defaults-file=/etc/my.cnf --user=mysql
[root@node4 ~]# service mysqld start
[root@node4 ~]# mysql_secure_installation
[root@node4 ~]# mysql -u root -p -h 127.0.0.1 -P63308 < a.sql
[root@node4 ~]# mysql -u root -p -h 127.0.0.1 -P63308  -e "flush privileges"

== node5 ==
[root@node5 ~]# rpm -ivh  http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
[root@node5 ~]# yum install mysql-community-server.i686 mysql-community-libs.i686 mysql-community-client.i686 
[root@node5 ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
port=63309
character-set-server=utf8

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.

<span style="color:#FF0000;">log_bin
gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1</span>

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=20115

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@node5 ~]# mysql_install_db --defaults-file=/etc/my.cnf --user=mysql
[root@node5 ~]# service mysqld start
[root@node5 ~]# mysql_secure_installation
[root@node5 ~]# mysql -u root -p -h 127.0.0.1 -P63309  < a.sql
[root@node5 ~]# mysql -u root -p -h 127.0.0.1 -P63309  -e "flush privileges"

== node1 ==
[root@node1 ~]# vim /etc/mysql/fabric.cfg
[DEFAULT]
prefix = 
sysconfdir = /etc
logdir = /var/log

[statistics]
prune_time = 3600

[logging]
url = file:///var/log/fabric.log
level = INFO

[storage]
auth_plugin = mysql_native_password
<span style="color:#FF0000;">database = fabric
user = fabric
address = localhost:63306</span>
connection_delay = 1
connection_timeout = 6
password = fabric
connection_attempts = 6

[failure_tracking]
notification_interval = 60
notification_clients = 50
detection_timeout = 1
detection_interval = 6
notifications = 300
detections = 3
<span style="color:#FF0000;">failover_interval = 0</span>
prune_time = 3600

[servers]
password = fabric
user = fabric

[connector]
ttl = 1

[client]
password = 

[protocol.xmlrpc]
disable_authentication = no
ssl_cert = 
realm = MySQL Fabric
ssl_key = 
ssl_ca = 
threads = 5
<span style="color:#FF0000;">user = admin</span>
<span style="color:#FF0000;">address = localhost:32274
password = admin</span>

[executor]
executors = 5

[sharding]
mysqldump_program = /usr/bin/mysqldump
mysqlclient_program = /usr/bin/mysql

[root@node1 ~]# mysqlfabric  help commands    --------> 查看所有命令帮助
[root@node1 ~]# mysqlfabric manage setup              ------------>使用fabric@'localhost'用户连接node1上63306端口的mysql实例, 该实例存储组及节点信息
[root@node1 ~]#  mysqlfabric manage start --daemonize  --------> 后台运行
[root@node1 ~]#  mysqlfabric  group create mycluster        ------------>创建一个HA组, 组名mycluster
[root@node1 ~]#  mysqlfabric  group add mycluster  192.168.201.12:63307   ------------>添加节点到组
[root@node1 ~]#  mysqlfabric  group add mycluster  192.168.201.14:63308   ------------>添加节点到组
[root@node1 ~]#  mysqlfabric  group add mycluster  192.168.201.15:63309   ------------>添加节点到组
[root@node1 ~]#  mysqlfabric group promote  mycluster    ----------->自动提升一个节点为primary
[root@node1 ~]#  mysqlfabric group activate mycluster    ---------------->激活监控, 自动failover(必须failover_interval=1)
[root@node1 ~]#  mysqlfabric group health mycluster    ----------->查看组健康状态
[root@node1 ~]#  mysqlfabric group lookup_servers mycluster   --------------->查看组成员信息

== node1 测试 node1做Fabric aware connector ==
[root@node1 ~]#  cat test_fabric_query.py
import mysql.connector
from   mysql.connector import fabric

def add_employee(conn, emp_no, first_name, last_name):
    conn.set_property(group="<span style="color:#FF0000;">mycluster</span>", mode=fabric.MODE_READWRITE)
    cur = conn.cursor()
    cur.execute("USE employees")
    cur.execute(
        "INSERT INTO employees VALUES (%s, %s, %s)",
        (emp_no, first_name, last_name)
        )
    # We need to keep track of what we have executed in order to,
    # at least, read our own updates from a slave.
    cur.execute("SELECT @@global.gtid_executed")
    for row in cur:
        print "Transactions executed on the master", row[0]
        return row[0]

def find_employee(conn, emp_no, gtid_executed):
    conn.set_property(group="<span style="color:#FF0000;">mycluster</span>", mode=fabric.MODE_READONLY)
    cur = conn.cursor()
    # Guarantee that a slave has applied our own updates before
    # reading anything.
    cur.execute(
        "SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)" %
        (gtid_executed, )
    )
    for row in cur:
        print "Had to synchronize", row, "transactions."
    cur.execute("USE employees")
    cur.execute(
        "SELECT first_name, last_name FROM employees "
        "WHERE emp_no = %s", (emp_no, )
        )
    for row in cur:
        print "Retrieved", row

# Address of the Fabric, not the host we are going to connect to.
<span style="color:#FF0000;">conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274,
            "username": "admin", "password" : "admin"
           },
    user="fabric", password="fabric", autocommit=True
    )</span>

conn.set_property(group="<span style="color:#FF0000;">mycluster</span>", mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS employees")
cur.execute("USE employees")
cur.execute("DROP TABLE IF EXISTS employees")
cur.execute(
    "CREATE TABLE employees ("
    "   emp_no INT, "
    "   first_name CHAR(40), "
    "   last_name CHAR(40)"
    ")"
    )

gtid_executed = add_employee(conn, 12, "John", "Doe")
find_employee(conn, 12, gtid_executed)


[root@node1 ~]# python ./test_fabric_query.py
<span style="color:#000099;">Transactions executed on the master 2c1d171d-f962-11e3-9d0c-000c29ccf58f:1-5,
557be7db-63f1-11e3-8e92-000c29add0bd:1-11
Had to synchronize (2,) transactions.
Retrieved (u'John', u'Doe')</span>











  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值