อาทิตย์, 24 ก.ค. 2016
 
 

SMSKP-PayPal

Donate using PayPal
Amount:
Note:
Note:

PTT Oil Price

Gold Status

SM SKP ADS-1

สมุยสเก็ตอัพ คอมมิวนิตี้
Newsfeeds
Planet MySQL
Planet MySQL - http://www.planetmysql.org/

  • Working with CloudFlare DNS in python
    Last week I wrote about aDNS discovery feature in Etcd. As a step in the whole process we need to create DNS records in twindb.com zone. CloudFlare provides rich API to work with it. We wrapped it into a Python module twindb_cloudflare and opensourced it: Source code: https://github.com/twindb/twindb_cloudflare Documentation: https://twindb-cloudflare.readthedocs.io PyPi package: https://pypi.python.org/pypi/twindb_cloudflare In the post I will show how to use twindb_cloudflare module. CloudFlare API credentials First of all you need to get credentials to work with CloudFlare. Visit https://www.cloudflare.com/a/account/my-account and get “API Key”. CLOUDFLARE_EMAIL = "aleks@twindb.com" CLOUDFLARE_AUTH_KEY = "dbb4a7ae063347a306e9ad8c5bda58a7a3cfa" Installing twindb_cloudflare The module is available in PyPi. You can install it with pip: $ pip install twindb_cloudflare Collecting twindb_cloudflare Using cached twindb_cloudflare-0.1.1-py2.py3-none-any.whl Installing collected packages: twindb-cloudflare Successfully installed twindb-cloudflare-0.1.1 Creating A record To create an A record import socket import time from twindb_cloudflare.twindb_cloudflare import CloudFlare, CloudFlareException CLOUDFLARE_EMAIL = "aleks@twindb.com" CLOUDFLARE_AUTH_KEY = "dbb4a7ae063347a306e9ad8c5bda58a7a3cfa" cf = CloudFlare(CLOUDFLARE_EMAIL, CLOUDFLARE_AUTH_KEY) try: cf.create_dns_record('blogtest.twindb.com', 'twindb.com', '10.10.10.10') # The new record isn't available right away wait_until = time.time() + 600 while time.time() < wait_until: try: ip = socket.gethostbyname('blogtest.twindb.com') print(ip) exit(0) except socket.gaierror: time.sleep(1) print("New record isn't available after 600 seconds") exit(-1) except CloudFlareException as err: print(err) exit(-1) The script runs a minute or two: [16:41:51 aleks@Aleksandrs-MacBook-Pro mydns]$ python create.py 10.10.10.10 [16:43:59 aleks@Aleksandrs-MacBook-Pro mydns]$ Updating A record Updating a record is pretty straightforward either. In real applications you have to take into account that a DNS response may be cached and CloudFlare changes aren’t available momentarily. import socket import time from twindb_cloudflare.twindb_cloudflare import CloudFlare, CloudFlareException CLOUDFLARE_EMAIL = "aleks@twindb.com" CLOUDFLARE_AUTH_KEY = "dbb4a7ae063347a306e9ad8c5bda58a7a3cfa" cf = CloudFlare(CLOUDFLARE_EMAIL, CLOUDFLARE_AUTH_KEY) try: cf.update_dns_record('blogtest.twindb.com', 'twindb.com', '10.20.20.20') # The new record isn't available right away wait_until = time.time() + 600 while time.time() < wait_until: try: ip = socket.gethostbyname('blogtest.twindb.com') if ip == '10.20.20.20': print(ip) exit(0) else: time.sleep(1) except socket.gaierror: time.sleep(1) print("New record isn't updated after 600 seconds") exit(-1) except CloudFlareException as err: print(err) exit(-1) The change is visible after a minute [16:54:02 aleks@Aleksandrs-MacBook-Pro mydns]$ python myupdate.py 10.20.20.20 [16:55:03 aleks@Aleksandrs-MacBook-Pro mydns]$ Deleting A record And finally let’s delete the record we’ve created. Here I won’t wait until the change is propagated and will assume that if there was no exception then we are good. from twindb_cloudflare.twindb_cloudflare import CloudFlare, CloudFlareException CLOUDFLARE_EMAIL = "aleks@twindb.com" CLOUDFLARE_AUTH_KEY = "dbb4a7ae063347a306e9ad8c5bda58a7a3cfa" cf = CloudFlare(CLOUDFLARE_EMAIL, CLOUDFLARE_AUTH_KEY) try: cf.delete_dns_record('blogtest.twindb.com', 'twindb.com') except CloudFlareException as err: print(err) exit(-1) Final notes ClaudFlare API provides many more actions than the module implements. However I wanted to start with something small and incrementally implementing more feature as they are needed. I encourage you to file bugs and feature requests on https://github.com/twindb/twindb_cloudflare/issues. Also, pull requests are welcomed The post Working with CloudFlare DNS in python appeared first on Backup and Data Recovery for MySQL.

  • How I became a Data Engineer
    A reminiscence of a personal timeline of events; please, excuse minor errors and/or let me know if my memory has shuffled things around a bit. My first job was at a company that had their own CRM and they wanted a web version of some parts of the system. At that time, I only had “experience” with ASP and Microsoft Access (I know, tough). They wanted the web version in PHP, their reasoning, I think, being that they wanted the integration to run directly into the database. The web app would write directly into the DB. The CRM system was written using Delphi and Firebird. So I learned PHP and my first database, which wasn’t MySQL (I don’t count MS Access as a DB). After that I got a job in which MySQL was used. I was really fresh on MySQL, and I didn’t know about the engines and such, so it was a bit weird learning about MyISAM (which didn’t have foreign keys for instance). After that I got a job in a huge multinational where they had this project migrating every Excel spreadsheet to a PHP program. VBA was heavily used there, and they had entire programs running into that. What they didn't tell us was that it was cheaper for them to have a whole team of PHP developers doing an internal system than to have the features built into their ERP. For “security” reasons no business logic could be inside the PHP code, so I had to do tons of Stored Procedures. They also had integrations with MS SQL Server. The workflow system used it together with a PHP tool called Scriptcase. Another job I had was with a different multinational, I had to do a program to read from various sources and store in a DB for later reports and all. It would be a “scratch” of an ETL (Extract, Transform, Load), but at the time I wasn’t yet well versed with data warehouse techniques. For that job I used PostgreSQL. In the same company we later on did Magento white label stores for our clients (other big companies), and it had to have integration with our ERP (unlike my first job). This integration was through a Service Bus written in Java and the ERP had Oracle as the DB. One of my employers noticed my interest in database tasks, and how quickly I became familiarised with the schema and our data architecture. Our main relational database had millions and millions of records, terabytes and they created a Data Team to make it more efficient. We would create workers to sync data into Elasticsearch for instance. I was still a PHP Developer officially, but mainly curating the DB and doing workers with NodeJS (we needed the async and the indexing time was crucial for business). I could go on and on through every job I've had. Most of my jobs have been at big corporations, and that has put me into contact with many flavours of Relational Databases and NoSQL too (MongoDB, Dynamo and Cassandra being the main ones). In the end, this kind of exposure has made me the “DB” person among my fellow engineers, and everything considered more than a CRUD tends to fall in my lap, and I'm happy with that. That’s how I discovered that I wanted to work with data. I didn’t have any idea of what kind of title that would be, but I knew I didn’t want to be a DBA, since much of the infrastructure tasks involved in it didn’t sound fun to me. Once the opportunity to work officially as a Data Engineer appeared, I grabbed it with both hands and I knew it was right.

  • Avoiding MySQL ERROR 1784 when replicating from 5.7 to 5.6
    Recently I upgraded some MySQL databases from 5.6 to 5.7, but -- for boring reasons unique to my environment -- I had to leave one replica on version 5.6. I knew there was a chance that the 5.7 -> 5.6 replication wouldn't work, but I decided to try it out to see if (and why) it would fail. Once I upgraded the master, replication failed, so I checked the error log on the replica and found these messages: [ERROR] Slave I/O: Found a Gtid_log_event or Previous_gtids_log_event when @@GLOBAL.GTID_MODE = OFF. Error_code: 1784 [ERROR] Slave I/O: Relay log write failure: could not queue event from master, Error_code: 1595 The error surprised me a little bit since I'm not using GTIDs in that replication topology. I asked around a bit, and Kenny Gryp hypothesized that I might be experiencing MySQL bug #74683, which was fixed in version 5.6.23. Since my replica was on 5.6.22, I decided to do an incremental 5.6 upgrade to see if that resolved the issue. I upgraded to 5.6.31 and replication started working. YMMV and there are certainly bound to be 5.7 -> 5.6.31 replication scenarios that don't work, but this was a simple fix for me. In hindsight it makes sense that replicating from a new major version to an older major version is more likely to work with a more recent minor version, so if I have to do this in the future I'll make sure the replica is running the latest minor version before upgrading the master. P.S. Thanks Kenny!

  • Develop By Example – Document Store: working with collections using Node.js
    In the previous blog post we explained how to create schemas and collections. In this one we are going to explain how to work with collections: adding, updating and deleting documents. The following code demonstrates how to add a single document to an existing collection:var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var newDoc = { name: 'Test Name', description: 'Test Description' }; coll.add(newDoc).execute().then(function (added) { console.log('Document(s) added: ' + added.getAffectedItemsCount()); session.close(); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); });In the previous code, first we get the objects that represent the schema (schema) and the collection (coll) that we want to work with. An object using JSON (newDoc) is created and is passed as a parameter to the coll object’s add method. Calling the execute method the document is added to the collection. Once the execute method has finished, we receive an object (added) that contains information about the document added. To verify if the document was added, we can call the added object’s getAffectedItemsCount method, which will return how many documents were added. But, what if you want to add multiple documents? You can do it with almost no changes in your code. The following code adds two documents at the same time:var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var newDoc = { name: 'Test Name', description: 'Test Description' }; var newDoc2 = { name: 'Test Name 2', description: 'Test Description 2' }; coll.add(newDoc , newDoc2).execute().then(function (added) { console.log('Document(s) added: ' + added.getAffectedItemsCount()); session.close(); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); });As you can see, the previous code is almost identical to the first example. We just add an extra line to declare the new document (newDoc2), and we add the new document as a parameter in the coll object’s add method. At the end we call added object’s getAffectedItemsCount method to verify we added the two documents. Now we know how to add multiples documents to a collection using multiple variables and passing them as parameters, but we can also do the same using an Array object. In the following code example we create an array object to use it to add new documents to a collection.var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var newDocs = [{ name: 'Test Name', description: 'Test Description' }, { name: 'Test Name 2', description: 'Test Description 2' }]; coll.add(newDocs).execute().then(function (added) { console.log('Document(s) added: ' + added.getAffectedItemsCount()); session.close(); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); });The previous code is almost identical to the first example; the difference is that we pass an array object as a parameter instead of a JSON object. The rest of the code is the same. This could be useful if you receive an array of objects from the client or if you load the data from a JSON file, you just pass the whole array to upload it to the collection. Updating a field in a document is also very easy to do. The following code is an example of how to do it:var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$._id == 'f0a743ac-a052-d615-1f8c-ef65ebc4'"; coll.modify(query) .set('$.name', 'New Name') .set('$.description', 'New Description') .execute() .then(function (updated) { console.log('Document(s) updated: ' + updated.getAffectedItemsCount()); session.close(); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); });In the previous code, first we get the objects that represent the schema (schema) and the collection (coll) we want to work. Then we declare the query variable which contains the where clause for our update. Next, we call the coll object’s modify method that receives the query variable as a parameter. Chained to the modify method is the set method, which receives a pair of objects; the first one is the field to update and the second one the new value to be set. As we did in our previous examples we call the execute method to perform the action requested. When the execute method finishes we receive an object (updated) with information about the update. To know how many documents were updated we call the updated object’s getAffectedItemsCount method. Now that we know how to add and update documents in a collection, we are going to explain how to remove them. The following code demonstrates it.var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$._id == 'f0a743ac-a052-d615-1f8c-ef65ebc4'"; coll.remove(query).execute().then(function (deleted) { console.log('Document(s) deleted: ' + deleted.getAffectedItemsCount()); session.close(); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); });The previous code defines the objects that represent the schema (schema) and the collection (coll) where we want to work. Then, we define the query variable again to contain the where clause for our operation, the remove in this case. To remove a document we call the coll object’s remove method followed by the execute method. Once the execute method is completed, we receive an object (deleted) with the information about the operation that has finished. By calling the deleted object’s getAffectedItemsCount method, we know how many documents were removed from the collection. Now we are going to see how to get documents from a collection. In the following example, we are retrieving the document that match the _id that we want:var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$._id == 'f0a743ac-a052-d615-1f8c-ef65ebc4'"; coll.find(query).execute(function (doc) { console.log(doc); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); session.close(); }).catch(function (err) { console.log(err.message); console.log(err.stack); });The previous code defines the objects that represent the schema (schema) and the collection (coll) we want to work with. Then the query variable is defined and the where clause is set to it. Then we call the coll object’s execute method to perform the query. When the execute method completes, we receive the document that match our search criteria and is send to the console to view it. But, what if we want all the records from a collection? Well that is simple; we just need to remove the search criteria from the find method. The updated code would look like the following:var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); coll.find().execute(function (doc) { console.log(doc); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); session.close(); }).catch(function (err) { console.log(err.message); console.log(err.stack); });Now we know how to search a specific document and get all the documents from a collection. What if we want to get just some number of documents that match query criteria? The next example shows the code to do it:var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$.name like '%Test%'"; coll.find(query).limit(3).execute(function (doc) { console.log(doc); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); session.close(); }).catch(function (err) { console.log(err.message); console.log(err.stack); });The previous code looks very similar to the example that returns one document with a specific _id, the difference here is that our query is performing a like and we are adding a call to the coll object’s limit method. Note that the query statement is case sensitive; this means that if we have documents that have  ‘test’ in the field ‘name’, those documents will not be returned because we are searching for ‘Test’ names. See you in the next blog post.

  • Comparison of database encryption methods (for data at rest)
    I recently came across a project where we had to evaluate different techniques suited for encryption of PII data at rest. Database is MySQL community 5.6, Red Hat enterprise OS. We had to encrypt (mask) PII information of customers. As of now data is hosted in local cloud. But we may have future plans to move to a third party cloud like Amazon. We are talking about two threats, internal and external. Internal - we have support team accessing the database the data for fixes and reporting (slave) Also DBA or Linux root user who have special privileges. So PII needs to be masked from them. External - Mainly hackers, Amazon cloud admins if we move to their cloud environment. Finally we decided to have application layer to do the encryption/decryption. Here are the major factors that lead to the decision Encryption Type # File system Encryption Database level (TDE) Application level Column level privilege(with views) 1 Who is responsible OS MySQL EE Application DBA 2 who can access data MySQL user(s) MySQL users application Application, root, DBA 3 protects data from stolen disk, hackers file system hackers everything non Admin MySQL users 4 does not protect from DBA, OPS DBA, root user, OPS DBA, root, access during changes 5 what can be encrypted all required file systems database file system required fields required fields 6 performance penalty high low very low nothing 7 protection strength weak strong very strong medium 8 application change required No No Yes No 9 Is backup encrypted depends on the method (e.g. sqldump is not) depends on the method yes No 10 protects from internal threat no no yes yes 11 protects from external threat yes yes yes depends 12 duration to encrypt existing data long time long time depends which all fields no time OPS : support + dev team having mysql connectivity to the database column level privilege - create views excluding PII data for support folks, this can be a different schema as well with only views present in there This may not be very explanatory so let me know if you have any questions, I'll try my best to answer them..Praji