Ozzie.eu

Working on a mixed environment, with Windows and Linux boxes, you might want to share some stuff from a linux machine with a user that doesn’t have an SSH client with file tranfer support or even installed. Or even send some files to download to a non-techie team mate. Something everybody is used to is downloading files from the browser, why not use that?

On your linux box, go to the directory where the files are stored and run the following command:

python -m SimpleHTTPServer

For example, I shared a folder called “vmware-tools-distrib”:

Afterwards, the target user only has to open the browser and navigate to the address of the linux box on port 8000.

Easy and handy.

At Yahoo, we manage a massive number of MySQL databases spread across multiple data centers.
We have thousands of databases and each database has many partitioned tables. In order to efficiently create and maintain partitions we developed a partition manager which automatically manages these for you with minimal pre configuration.

So, I was going through the documentation for SQL Server 2016 Always Encrypted feature and I read this paragraph:

The driver encrypts the data in sensitive columns before passing the data to SQL Server, and automatically rewrites queries so that the semantics to the application are preserved.

We’ll still be able to get the execution plan for the query from the server, but regarding optimization, similar issues to the ones that arise from ORM frameworks might pop-out.

Anyway, going further down the same documentation, you get the feature constraints, like:

• Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations
• Indexing columns encrypted using randomized encryption is not supported.
• Query parameters that map to encrypted columns must be passed as driver-level parameters.
  

Also, you have some characteristics that disallow the use of encryption, like:

• Columns that are referenced by statistics
  
• Columns using alias type
  
• Partitioning columns
  
• Columns with default constraints
  

The case is clear that it’s a wonderful

security-wise

feature. Before adopting it on your business driven development, make sure it won’t become a source for technical debt for your current code base and future developments.

mysql> select distinct title from titles;
+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+
7 rows in set (0.38 sec)

mysql> alter table titles add fulltext index `title` (`title`);
Query OK, 0 rows affected (14.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select distinct title from titles;
Empty set (0.00 sec)

mysql> select count(distinct title) from titles;
+-----------------------+
| count(distinct title) |
+-----------------------+
|                     7 |
+-----------------------+
1 row in set (0.24 sec)

mysql> select distinct(ucase(title)) from titles;
+--------------------+
| (ucase(title))     |
+--------------------+
| SENIOR ENGINEER    |
| STAFF              |
| ENGINEER           |
| SENIOR STAFF       |
| ASSISTANT ENGINEER |
| TECHNIQUE LEADER   |
| MANAGER            |
+--------------------+
7 rows in set (0.44 sec)

This bug is already reported at MySQL Bugs. It appears to be an upstream issue, reproducible on current 5.6 and 5.7.

Seems I’m not the only one keeping away from JSON on the relational DB:

I would shy away from the NoSQL add-ons that some relational vendors have added, to compete with their newer database cousins. This starts to feel like a fashion contest after a while.

You can also get my point of view on this matter:

• JSON on MySQL: still not fond of it.
  
• JSON on the database? No thanks!
  

dbareactions:

Being onduty during maintenance weekends … @nosqltillcoffee

Have a nice weekend!

• The host name, what operating system it runs on, the MySQL version installed, default collation of the instance, the installation directory and the data directory;
• How many user databases it hosts, what they're called and the collation used;
• The size of each database as a whole and broken down by storage engine;
• All the tables and their space used ;
• The creation and last modification on each database.

Landing on an enterprise with ongoing projects mean that servers are often handed to IT staff without complete knowledge of  what’s inside.
I’ve built a simple script, scraping from here and there, to gather a summary of relevant information.

Earlier, I posted here about my dislike of allowing developers to push the JSON workload into the database server.

Reading this entry on a nice blog about MySQL, what caught my eye was the sentence:

Funny. It all started with the idea to be schema-free. Now people seem to need schema enforcement.

I’ve also seen this article on DZone which sarcastically illustrates the mislead of developers caused by the hype on JSON:

That’s it, then; let’s use JSON and manifest the death of XML!

I got reassurance on Twitter that MySQL performance won’t be compromised for using the new JSON datatype and operations:

@mjlmo I would like to see it be inc. in the standards (where it is currently being discussed). Overhead is well amortized by IO on insert.

— morgo (@morgo) September 10, 2015

Not wanting to flog a dead horse here, by coming back to this topic, but it’s really not about the features or how they perform well on the engine. It’s what developers come up with to make things work. 

Apparently, already there are bright minds that would like to cripple the flexibility of JSON with a sort of schema validation. Who knows what else will come up? And since the door is open to shove all that bright ideas inside the database engine, might as well make developers at home and put it up to them to solve the upcoming performance issues later on.

• Replication: the binary log on a master replication server provides a record of the data changes to be sent to slave servers.
• Point in Time recovery: allow to recover a database from a full backup and them replaying the subsequent events saved on the binary log, up to a given instant.

#Enabling the binary log
log-bin=binlog
max_binlog_size=500M
expire_logs_days=7
server_id=1

shell> sudo service mysql restart

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       177 |
| binlog.000002 |       315 |
+---------------+-----------+
2 rows in set (0.00 sec)

shell> mysql -u root -p < sakila-schema.sql

mysql> flush binary logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       177 |
| binlog.000002 |       359 |
| binlog.000003 |       154 |
+---------------+-----------+
3 rows in set (0.00 sec)

shell> mysql -u root -p < sakila-data.sql

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       177 |
| binlog.000002 |       359 |
| binlog.000003 |   1359101 |
+---------------+-----------+
3 rows in set (0.01 sec)

shell> sudo mysqlbinlog --verbose /var/lib/mysql/binlog.000003

### INSERT INTO `sakila`.`country`
### SET
###   @1=1
###   @2='Afghanistan'
###   @3=1139978640

MariaDB Enterprise Cluster with Galera technology is a multi-master cluster that achieves high availability and scalability through features such as synchronous replication, read and write to any cluster nodes without slave lag, automatic membership control of new nodes joining the cluster and failed nodes dropping from the cluster.

thecodinglove:

/* by
Stefan Ray */

:)

• Security permissions were not exhaustively identified for all database objects. To solve things quickly, you turn your database authorization management into Swiss cheese;
• The new system has issues, until the bugs are fixed, manual correction scripts have to be executed on working hours, maiming overall business activity;
• As the data volume grows, there is performance degradation due to inefficient indexing, bad user experience design or poor database coding skills;
• Technical support provided by the database vendor performs an audit on the workload, does some tuning on the server instance, and shifts responsibility on the remaining lack of performance over to the development team;
• The development team struggles adopting the database vendor recommendations as it has great impact on the source code;
• Management wants high availability, but it won't commit the infrastructure resources and budget to set it up properly;
• You do not have a remote site so that a disaster recovery plan can be made, you don't have a lab where you regularly restore backups and perform automated integrity checks;
• You are understaffed and with no one possessing deep skills on the specific data platform you own;

So I’ve read the article above and thought:

• Will managers realize they have to document a formal data security policy?
  
• Will DBA staff be accountable for wrong permissions: granting too much or too little access to data?
  

Overall, I think it’s definitely a good security enhancement but security lies on the weakest link, right?

Oracle today announced the general availability of MySQL 5.7, the latest version of the world’s most popular open source database. The new version delivers greater performance, scalability and manageability, plus enhanced NoSQL capabilities with JSON support and MySQL Router, which makes it easy to connect applications to multiple MySQL databases.

When tackling a new enterprise project to support a given business, you face the challenge of choosing and committing to a database platform. The choice should be the one most adequate, given the needs and requirements of the new information system and data to be hosted and managed.

Typically, a number of factors should be taken into consideration like security features, storage requirements, reliability, high availability, backups, disaster recovery, data compression, technical support and last but definitely not least, the cost of the solution. Added to that there is also performance, scalability and ease of administration to think about.

With the result of this analysis, most of the time, the verdict is this: data platforms available as community editions or free open source fall short on the given requirements fulfillment. So, the advice is almost always to acquire commercial licenses or expand the licensing already owned.

And this should give you peace of mind for a while. At least until the first release of the system goes live. After that, some of the common pitfalls are:

• Security permissions were not exhaustively identified for all database objects. To solve things quickly, you turn your database authorization management into Swiss cheese;
  
• The new system has issues, until the bugs are fixed, manual correction scripts have to be executed on working hours, maiming overall business activity;
  
• As the data volume grows, there is performance degradation due to inefficient indexing, bad user experience design or poor database coding skills;
  
• Technical support provided by the database vendor performs an audit on the workload, does some tuning on the server instance, and shifts responsibility on the remaining lack of performance over to the development team;
  
• The development team struggles adopting the database vendor recommendations as it has great impact on the source code;
  
• Management wants high availability, but it won’t commit the infrastructure resources and budget to set it up properly;
  
• You do not have a remote site so that a disaster recovery plan can be made, you don’t have a lab where you regularly restore backups and perform automated integrity checks;
  
• You are understaffed and with no one possessing deep skills on the specific data platform you own;
  

Even if just a third of these pitfalls sound familiar, what are you doing with your next project? Still thinking on recommending commercial software because people are the ones to blame here?

On a global organization, after you deploy the first release and spread it across the offices, the licensing and support costs will skyrocket. That money could be spent preventing some of the pitfalls mentioned here. If you cut back on licensing and support, you can spend on infrastructure and staff.

There are wonderful commercial databases out there, but on the business requirements phase, the pick should be done as a whole and not based on vendor promises because the final solution will be a result of development and available budget, not a sales brochure.

Vision and engineering are the keys to success. And I’m afraid that doesn’t come out of the box.

So this is a really nice shortlist for any DBA. Without being too detailed, it gives a good starting point to perform a security audit on your Microsoft SQL Server database instances.

The  aspects covered are:

• Physical Security
  
• Operating System Configuration
  
• Database Instance Configurations
  

At the end it has some links to other related checklists. It’s on the Technet wiki and should be updated regularly.

Check it out here: http://social.technet.microsoft.com/wiki/contents/articles/1256.database-engine-security-checklist-database-engine-security-configuration.aspx