emGee Software Solutions Custom Database Applications

Share this

Web Technologies

DoneJS 2.0 Released

Echo JS - Fri, 03/09/2018 - 00:33
Categories: Web Technologies

React Native Starter Kit

Echo JS - Fri, 03/09/2018 - 00:33
Categories: Web Technologies

Binlog Encryption with Percona Server for MySQL

Planet MySQL - Thu, 03/08/2018 - 14:11

In this blog post, we’ll look at how to turn on binlog encryption in Percona Server for MySQL.

Why do I need this?

As you probably know, Percona Server for MySQL’s binlog contains sensitive information. Replication uses the binlog to copy events between servers. They contain all the information from one server so that it can be applied on another. In other words, if somebody has access to a binlog, it means they have access to all the data in the server. Moreover, said person (or, “Hacker”) could create a clone copy of our server by just making a replica of it. In the end, they have access to our binlog. This shows how important protecting a binlog really is – leakage of binlogs not only make a particular table/tablespace or a group of tables accessible to a hacker, but literally the whole server is at risk. The same situation is true with relay log – a relay log is really a copy of binlog on the slave server.

But have no fear – a new feature to the rescue – binary log encryption. Since Percona Server for MySQL version 5.7.20-19 (beta version) it is possible to enable binlog encryption for all the binlogs and relay logs produced by the server.

How do you turn it on?

To start binlog encryption, you need to start the server with –encrypt-binlog=1. This, in turn, requires –master_verify_checksum and –binlog_checksum both to be ON. Also, you need to install one of the keyring plugins.

From now on all the binlogs and relay logs produced by the server get encrypted. However, for the replication to be safe as a whole the connection between servers also has to be encrypted. See https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-encrypted-connections.html for details on how to do this.

Please note that this does not mean that all binlogs in our replication schema get encrypted. Remember you need to turn on encrypt-binlog on slave servers too, even if they do not produce binlog files. Slave servers still produce relay logs when replicating from a master server. Turn on encrypt-binlog on slave servers so that their relay logs also get encrypted.

How does this work in the big picture?

The master encrypts the event before writing it into the binlog. The slave connects to master and ask for events. The master decrypts the events from the binary log and sends them over to slave.

Note that events send between the master and slave servers are not encrypted! This is why the connection between the master and slave needs to use a secure channel, i.e., TLS.

The slave receives events from the master, encrypts them and writes them down into the relay log.

That is why we need to enable encrypt-binlog on a slave. The relay log has to get encrypted too.

Next, the slave decrypts events from relay log and applies them. After applying the event the slave encrypts it and writes it down into its binlog file (given binlog is enabled on the slave).

In summary to make our replication secure, we need:

  • Turn on encrypt-binlog on the master
  • Turn on encrypt-binlog on the slave
  • The connection between master and slave needs to use TLS.

It’s worth noting that servers in replication have no idea if other servers are encrypted or not.

Why do master_verify_checksum and binlog_checksum need to be turned ON?

This is needed for “authenticate encryption”. Simply put, this is how we make sure that what we decrypt has not been changed by a third party. Also, it checks if the key that was used to decrypt the event was the correct one.

Digging deeper with mysqlbinlog

Mysqlbinlog is a standalone application that lets you read binlog files. As I write this blog post, it is not capable of decrypting binary logs – at least not by itself. However, it still can read encrypted binlog files when using a running Percona Server for MySQL. Use option –read-from-remote-server to read binary log produced by a given server.

Let’s see what happens when we try to read an encrypted binlog with mysqlbinlog without read-from-remote-server enabled. You will get something like this:

As you can see it is only possible to read binary log till event type 9f gets read. This event is the Start_encryption_event. After this event the rest of the binlog is encrypted. One thing to note is that Start_encryption_event is never propagated in replication. For instance, the master server is run with –encryt_binlog. This means that the server writes Start_encryption_event to its binary logs. However it is never sent to the slave server (the slave has no idea whether the master is encrypted).

Another option you can use with mysqlbinlog is –force option. It forces mysqlbinlog to read all the events from the binlog, even if they are encrypted. You will see something like this in the output:

As you can see, it is only possible to read two first events – until the Start_encryption_event. However, this time we can see that there are other events that follow, which are encrypted.

Running mysqlbinlog (without –read-from-remote) on encrypted binary logs may only make sense if we want to see if a given binary log is encrypted. For point-in-time recovery, and for other purposes that might require reading encrypted binlog, we would use mysqlbinlog with –read-from-remote option.

For instance, if we want to read binlog master-bin.000001, and Percona Server for MySQL is running on, port 3033, with user:robert, password:hard_password, we would use mysqlbinlog like this:

mysqlbinlog –read-from-remote-server –protocol=tcp –host= –port=3033 –user=robert –password=hard_password master-bing.000001.

When you look at the output of this command, you see something like this:

You can now see the decrypted binlog. One interesting thing to note here is that we do not see our Start_encryption_event (type 9f). This proves my point – Start_encryption_event never leaves the server (we are reading from the server now as we use –read-from-remote-server).

For more information how to use mysqlbinlog for point-in-time recovery see https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html.

However, for more modern approaches for point-in-time recovery that do not use mysqlbinlog and make use of parallel appliers, see here:

Have fun with binlog encryption!

Categories: Web Technologies

Screen Recording Utilities for macOS

CSS-Tricks - Thu, 03/08/2018 - 12:25

I record quite a few short little videos. Sometimes for use demonstrating bugs or weirdnesses. Sometimes right here for the blog. A lot of times for Instagram or other social media.

Allow me to get SUPER NITPICKY about what I like.

  • Multiple formats. Sometimes you need a GIF. Sometimes you need an MP4. Sometimes you need both. It's ideal if the software can export as either or both.
  • Easily resizeable recording area. If you need to record the entire screen, fine, but I feel like that's the job for more full-blown screencasting apps. More often, I need to record a smaller bit of the screen. Ideally, I can drag over the portion I want, but the more control the better.
  • Aspect ratios and saved sizes. Speaking of control, it's likely I might want a square recording (like if it's going to Instagram) or I might want a 16:9, a common aspect ratio for TV's and web video. Ideally, the software helps me get there quickly.
  • Cursor/clicking, or not. Sometimes the point of a video is to demonstrate something, which might require showing the cursor and interactions like clicks. Ideally, that is available but turn-off-able.
  • Editing after recording. The chances of getting a perfect take are rare. More commonly, I'd like to adjust the start and end time of the recording. Since it's likely the GIF or video is meant to repeat, playing the recording as this is happening is ideal.
  • Configurable shortcuts. I'd ideally like to hit a keyboard command to fire up the app, select a recording area, and go.
  • Audio or no audio. It should be possible to record sound, clear if I am or not, and configurable.
  • Cost. This is just informational as it's typically a factor. Generally I like to pay for things as it can be a good indicator of quality and support. But as we all known, open source can be incredible, and incentivized companies can do well making free products, too.
  • Retains history. Maybe I need to re-cut it. Maybe I lost my export somehow. Maybe the app weirdly quit. Ideally, I'd like some history so I can go back to some older recordings and export another copy.
GIPHY Capture The green box there is the area of the screen GIPHY Capture records.

This is a great idea for a company like GIPHY to build, and they've done a fine job here. Best of all, I've watched it evolve over time to get more and more useful. As a cool bonus feature, you can add captions at specific points in the recording.

My main gripe is the two-window system. The green-box window has recording and history, then a second window for editing and exporting. That alone is no big deal, but when you are editing, you often want to be gone with the green box. But closing the green box means quitting the whole app.

Multiple formats GIF, MP4, or "Batch" which outputs a folder with both. Easily resizeable recording area Position and size the green box over the area you want to record. Aspect ratios and saved sizes No aspect ratios, but you can specifiy pixel width/height and it will save your recently used ones. A bit hidden, you have to click the pixel dimensions in the lower right to access it. Cursor/clicking Recording the cursor is on/off setting. If on, it adds a circle around the cursor when you click. Editing after recording Handles at the beginning and end of the timeline allow you to drag them inward to crop the clip. Very nicely handled (get it?) — I think this might be the best take on editing. File size control You can choose from a handful of options for both pixel size and frame rate to control size.

Not as fine-grained as you might want but likely fits most needs. Configurable shortcuts When the app is open, you can set a letter or number as a key command to start/stop recording. Audio or no audio No audio recording at all Cost Free Retains history I'm not sure how far back the history goes (it's a bit hard to navigate beyond what you can see) but the lower bar of the green recording window gives history access to the last few very easily. Batch exporting is a clever feature. Sometimes I really do need both types (GIF and video), and that need is likely to increase. Kap

I think Kap is my favorite one. At least it is today. It's quite polished, and also open source, perhaps as a bit of marketing for the agency it comes from.

Kap is at version 2.0 right now, and I quite like it. I had 1.0 and aborted pretty quickly. I can't remember why exactly, but it didn't measure up to other options. Version 2.0 is perhaps best-of-breed. I'm a fan of the fact that it's a menu bar app, so it is ready all the time instead of something I need to launch.

Its fancy bonus feature is installable export locations, like uploading to Cloudinary or S3.

The keyboard shortcut is one thing that (and this is weirdly unique to me) really bugs me. It actually keeps me from having it open all the time, because Command-Shift-5 is CodePen's command for re-running, which I use all the time. Configurability, please!

Multiple formats The most formats! GIF and MP4, but also WebM and APNG. Cool, but you can only export one at a time. Easily resizeable recording area Clicking the record button gives you little black-white dashed lines you position and size over the recording area. If GIPHY Capture's green screen is papa bear (too much), this is mama bear (too little). There is probably a just right baby bear in there somewhere. Aspect ratios and saved sizes Sizing is a first-class citizen here, giving you a dropdown for aspect ratio or controls for exact sizes (that it remembers). Cursor/clicking Under preferences, you can flip cursor recording on and off (and separately from click highlighting). Editing after recording Drag handles from the start or end inward to edit. File size control It's a big strange. There is an FPS control buried in settings to adjust the frame rate, but then on the editing screen before you export, you only get to pick between 30 and 15, so it's not clear what happens if you've adjusted it in settings to something other than those. Configurable shortcuts Weirdly, it's Command-Shift-5, which it doesn't tell you, allow you to turn off, or configure. Audio or no audio One click to turn on and off right before you record or after you record. Cost Free and open source. Retains history No history, but warns you before you close an editing window so you don't accidently lose recordings. LICEcap

Old school! LICEcap is the app that opened my eyes to the idea that these apps were even a thing. Perhaps the first of its kind.

Multiple formats GIF only Easily resizeable recording area The empty frame window might be the most clear UI out of all of them. Aspect ratios and saved sizes Neither, but it is easy to manually resize or type in pixel dimensions manually Cursor/clicking You decide if you want it right before you record. Editing after recording None File size control You can choose the FPS as you record, so you have good control, but it can't be changed after recording. So if it ends up too large or too small, you have to re-record. Configurable shortcuts None Audio or no audio As it's GIF only, there is no audio. Cost Free Retains history No Droplr Menu bar app

Droplr absolutely has the power to record quick screencasts, but it's much more limited than these others. What it does offer is a very quick way to get your screencasts up onto the web in a permanent and shareable way very quickly. If that's the most important thing to you, you'd be in good hands.

Multiple formats GIF or MOV Easily resizeable recording area Every time you record you have to drag over the area you want to record. Aspect ratios and saved sizes Easy to select a recording area, but it doesn't save sizes, tell you the dimensions of your selected area, or help with aspect ratios. Cursor/clicking Automatically includes cursor and click highlighting. Editing after recording None. You just choose GIF or MOV and it auto-uploads it. The ability to at least save locally before uploading would be nice. File size control None Configurable shortcuts You can pick a custom keyboard command for screencasts, along with different key commands for everythinge else Droplr does. Audio or no audio As you upload, it gives the impression that videos automatically include sound. But, you can turn off audio recording in preferences. Cost Freemium. If you need unlimited length screen recordings, it's $8.29/month. Retains history Yep, through the Droplr service, you'll have a complete history of all recordings. CloudApp

Like Droplr, CloudApp will help you record a screencast, but it's all about getting that screencast uploaded to their service so you can share it from there. That can be awfully handy, but also get in the way when you just want to work locally. You can set a preference to save the GIF and movie record locally, but it's a PRO feature.

Multiple formats GIF or MOV, which you pick before you record. Easily resizeable recording area Drag over the area you want. Aspect ratios and saved sizes Also like Droplr, you drag over the area you want, but it doesn't tell you the dimensions as you are doing it, allow to specify or adjust that size with numbers or help with aspect ratios. Cursor/clicking Option in preferences. Editing after recording No File size control In preferences, you can configure GIF FPS at 3, 6, or 12. Configurable shortcuts Lots of options for all the types of screenshots or recordings you can do. Audio or no audio Button to turn on or off (and select source) right before you record. Cost Freemium. Pro plans start at $8/month. Very heavy on the upgrade triggers. Retains history On their service. Quicktime Player

It's worth knowing that macOS has a built-in way to record the screen, and it's not half bad. Pop it open and do File > New Screen Recording and you have a decent little tool for video recordings.

Multiple formats Just video. Easily resizeable recording area Drag over the area you want to record (or just click to record the whole screen). Aspect ratios and saved sizes No help with saved sizes, specifying the size you want, previously used sizes, or aspect ratios. Cursor/clicking You automatically get the cursor, but no click highlighting. Editing after recording Command-T gives you a Trim dialog for editing the start and end points. File size control You can export the video in 4K, 1080p, 720p, etc. If the video isn't big enough, the unavailable options are grayed out. Configurable shortcuts Not configurable, but it does have default shortcuts for when the app is open and active. Audio or no audio Beside the red button that starts the recording there is a dropdown to select an audio source (or none). Cost Free, as in comes with macOS. Retains history Only if you save the files. It will prompt you to save before closing them. Gifox

Gifox was unknown to me before I started looking around for this post. It's pretty great! Very modern. Lots of options. Fairly priced. Plus a few pretty neat features.

Multiple formats Only GIF, which is unfortunate as it might be this app's only weakness. Easily resizeable recording area Drag to record an area (helps you with coordinates and sizing) or record specific windows (nice touch). Aspect ratios and saved sizes No aspect ratios, but it does allow you to lock the size so that subsequent recordings open up at exactly the same size. Cursor/clicking Option in settings. Editing after recording None File size control In settings you can control recording and playback FPS. Configurable shortcuts Very helpful with shortcuts. Little stuff like the spacebar to start/stop recording, but also a bunch of configurable ones in settings. Audio or no audio GIF only, so no audio. Cost Free version, but $4.99 for the licensed version which you'll probably want because it watermarks the recording otherwise. Retains history Automatically saves all recordings, so you can't lose them unless you trash them. Also has integrations with Dropbox, Google Drive, and Imgur so it can push directly there. Screenflow

Screenflow is really beefy screencasting software. All the stuff we've looked at so far is for little tiny quicky stuff. Screenflow is for long-form, edited, fancy screencasts. You can use it for little stuff, but it would be overkill and all the control would probably get in the way more than help. But if you need lots of control, it's fantastic.

Multiple formats Just video. Easily resizeable recording area Screenflow turns this on it's head. Generally you record the entire screen, then during editing, you crop down to what you need. Newer versions let you scope down the recording area before you record, but the old paradigm is still there and probably a smart way to work in general. Aspect ratios and saved sizes Lots of control. It defaults to resizing as an aspect ratio when you crop after recording, but you can change it with hard pixel values if you wish, or choose from presets. Cursor/clicking Loads of control here. As you're editing, you can add action points that allow you to focus on the cursor with various effects, like graying out the rest of the screen. Editing after recording This is the main point of Screenflow. File size control Lots of exporting control for size, speed, and quality. Configurable shortcuts Massive set of configurable keyboard commands. Audio or no audio You choose what audio sources you want to record when you record. You can always remove those tracks during editing, or edit the audio just as you do the video. Cost Starts at $129.99. Retains history Only what you save. The Graveyard?
  • Screeny. Looks pretty nice, but also looks like it hasn't been touched in five years and I didn't wanna spend $14.99 when there seems to be a lot of good modern alternatives.
  • Recordit. Looks pretty similar to some of these others — notably CloudApp and Droplr — as it has a hosted service. But also sorta looks limited and abandoned.
  • GifGrabber. Looks pretty good and it's free! Just also feels a bit abandoned and only does GIF.

The post Screen Recording Utilities for macOS appeared first on CSS-Tricks.

Categories: Web Technologies

Migrating MySQL Users to Amazon RDS

Planet MySQL - Thu, 03/08/2018 - 11:49

In this blog post, we’ll look at what is needed when migrating MySQL users to Amazon RDS. We’ll discuss how we can transform MySQL user grants and make them compatible with Amazon RDS.

In order to deliver a managed service experience, Amazon RDS does not provide shell access to the underlying operating system. It also restricts access to certain procedures that require advanced privileges.

Every MySQL instance has some users with ALL PRIVILEGES, and you can’t directly migrate these users to Amazon RDS because it does not support following privileges for regular users.

  • SUPER – Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global.
  • SHUTDOWN – Enable use of mysqladmin shutdown. Level: Global.
  • FILE – Enable the user to cause the server to read or write files. Level: Global.
  • CREATE TABLESPACE – Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global.

The RDS parameter groups manage changes to the MySQL configuration (dynamic and non-dynamic variables). Amazon RDS also provides stored procedures to perform various administrative tasks that require SUPER privileges.

For example, we’ve got this user in MySQL instance running on Amazon EC2.

db01 (none)> show grants for percona@'%'; +-----------------------------------------------------------------------------------------------------------------------------------+ | Grants for percona@% | +-----------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'percona'@'%' IDENTIFIED BY PASSWORD '*497030855D20D6B22E65436D0DFC75AA347B32F0' WITH GRANT OPTION | +-----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

If we try to run the same grants in RDS, it will fail.

[RDS] (none)> GRANT ALL PRIVILEGES ON *.* TO 'percona'@'%' IDENTIFIED BY PASSWORD '*497030855D20D6B22E65436D0DFC75AA347B32F0' WITH GRANT OPTION; ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

We’ll follow these steps for migrating users to RDS.

  1. Identify users with privileges that aren’t supported by RDS.
  2. Export their grants using pt-show-grants.
  3. Import grants in a separate clean MySQL instance running the same version.
  4. Remove the forbidden privileges using the REVOKE statement.
  5. Export grants again using pt-show-grants and load them to RDS.
Identify users having privileges that aren’t supported by RDS

First, we’ll find the users with privileges that aren’t supported by Amazon RDS. I’ve excluded the localhost users because there is no direct shell access in RDS and you shouldn’t migrate these users.

db01 (none)> select concat("'",user,"'@'",host,"'") as 'user', CONCAT("REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM '",user,"'@'",host,"';") as 'query' from mysql.user where host not in ('localhost','') and (Super_Priv='Y' OR Shutdown_priv='Y' OR File_priv='Y' OR Create_tablespace_priv='Y'); +---------------+----------------------------------------------------------------------------+ | user | query | +---------------+----------------------------------------------------------------------------+ | 'appuser'@'%' | REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'appuser'@'%'; | | 'percona'@'%' | REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'percona'@'%'; | +---------------+----------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

We’ve two users with incompatible grants. Let’s transform their grants to make them compatible with RDS. We’ll use the query in second column output later in this process.

Export grants using pt-show-grants

The next step is exporting these two users’ grants using pt-show-grants:

[root@db01 ~]# pt-show-grants --only='appuser'@'%','percona'@'%' -- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 5.6.38-83.0 at 2018-02-24 10:02:21 -- Grants for 'appuser'@'%' GRANT FILE ON *.* TO 'appuser'@'%' IDENTIFIED BY PASSWORD '*46BDE570B30DFEDC739A339B0AFA17DB62C54213'; GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE ON `sakila`.* TO 'appuser'@'%'; -- Grants for 'percona'@'%' GRANT ALL PRIVILEGES ON *.* TO 'percona'@'%' IDENTIFIED BY PASSWORD '*497030855D20D6B22E65436D0DFC75AA347B32F0' WITH GRANT OPTION;

As we can see from above output, both users have at least one privilege that isn’t supported by RDS. Now, all we need to do is to import these users into a separate clean MySQL instance running the same version, and REVOKE the privileges that aren’t supported by RDS.

Import users in a separate MySQL instance running the same version

I’m going to import grants in a separate VM where I’ve just installed Percona Server for MySQL 5.6. Let’s call this instance as db02:

[root@db02 ~]# pt-show-grants --host=db01 --only='appuser'@'%','percona'@'%' --user=percona --ask-pass | mysql Enter password:

Remove the forbidden privileges using the REVOKE statement

In this step, we will use REVOKE statement from Step 1 to remove the privileges that aren’t supported by Amazon RDS:

db02 (none)> REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'appuser'@'%'; Query OK, 0 rows affected (0.00 sec) db02 (none)> REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'percona'@'%'; Query OK, 0 rows affected (0.00 sec)

Export grants again using pt-show-grants and load them to RDS

At this point, db02 has the grants that are compatible with RDS. Let’s take a look at them:

[root@db02 ~]# pt-show-grants --only='appuser'@'%','percona'@'%' -- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 5.6.39-83.1 at 2018-02-24 10:10:38 -- Grants for 'appuser'@'%' GRANT USAGE ON *.* TO 'appuser'@'%' IDENTIFIED BY PASSWORD '*46BDE570B30DFEDC739A339B0AFA17DB62C54213'; GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE ON `sakila`.* TO 'appuser'@'%'; -- Grants for 'percona'@'%' GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE ON *.* TO 'percona'@'%' IDENTIFIED BY PASSWORD '*497030855D20D6B22E65436D0DFC75AA347B32F0' WITH GRANT OPTION;

These grants look good, these can be safely migrated to RDS now. Let’s do it:


We have successfully migrated users to Amazon RDS, which would have failed in direct migration.

What about rest of the users that don’t have SUPER/SHUTDOWN/FILE/CREATE TABLESPACE privileges? Well, it’s easy. We can migrate them directly using pt-show-grants. They don’t need any transformation before migration.

List them using the following query:

db01 (none)> select concat("'",user,"'@'",host,"'") as 'user' from mysql.user where host not in ('localhost','') and (Super_Priv<>'Y' AND Shutdown_priv<>'Y' AND File_priv<>'Y' AND Create_tablespace_priv<>'Y'); +-----------------------+ | user | +-----------------------+ | 'readonly'@'%' | | 'repl'@'' | +-----------------------+ 2 rows in set (0.01 sec)

Export them using pt-show grants and load to RDS.

[root@db01 ~]# pt-show-grants --only='readonly'@'%','repl'@'' | mysql --host=<rds.endpoint> --user=percona -p Enter password:


Amazon RDS is a great platform for hosting your MySQL databases. When migrating MySQL users to Amazon RDS, some grants might fail because of having privileges that aren’t supported by RDS. Using pt-show-grants from Percona Toolkit and a separate clean MySQL instance, we can easily transform grants and migrate MySQL users to Amazon RDS without any hassle.

Categories: Web Technologies

Creating a Parking Game With the HTML Drag and Drop API

CSS-Tricks - Thu, 03/08/2018 - 06:15

Among the many JavaScript APIs added in HTML5 was Drag and Drop (we’ll refer to it as DnD in this article) which brought native DnD support to the browser, making it easier for developers to implement this interactive feature into applications. The amazing thing that happens when features become easier to implement is that people start making all kinds of silly, impractical things with it, like the one we’re making today: a parking game!

DnD requires only a few things to work:

  • Something to drag
  • Somewhere to drop
  • JavaScript event handlers on the target to tell the browser it can drop

We’re going to start by creating our draggables.


Both <img> and <a>(with the href attribute set) elements are draggable by default. If you want to drag a different element, you'll need to set the draggable attribute to true.

We’ll start with the HTML that sets up the images for our four vehicles: fire truck, ambulance, car and bicycle.

<ul class="vehicles"> <li> <!-- Fire Truck --> <!-- <code>img<code> elements don't need a <code>draggable<code> attribute like other elements --> <img id="fire-truck" alt="fire truck" src="https://cdn.glitch.com/20f985bd-431d-4807-857b-e966e015c91b%2Ftruck-clip-art-fire-truck4.png?1519011787956"/> </li> <li> <!-- Ambulance --> <img id="ambulance" alt="ambulance" src="https://cdn.glitch.com/20f985bd-431d-4807-857b-e966e015c91b%2Fambulance5.png?1519011787610"> </li> <li> <!-- Car --> <img id="car" alt="car" src="https://cdn.glitch.com/20f985bd-431d-4807-857b-e966e015c91b%2Fcar-20clip-20art-1311497037_Vector_Clipart.png?1519011788408"> </li> <li> <!-- Bike --> <img id="bike" alt="bicycle" src="https://cdn.glitch.com/20f985bd-431d-4807-857b-e966e015c91b%2Fbicycle-20clip-20art-bicycle3.png?1519011787816"> </li> </ul>

Since images are draggable by default, you’ll see dragging any one of them creates a ghost image.

Just adding a draggable attribute to an element that’s not an image or link is really all you need to make an element draggable in most browsers. To make elements draggable in all browsers, you need to define some event handlers. They are also useful for adding extra functionality like a border if an element is being dragged around or a sound if it stops being dragged. For these, you’re going to need some drag event handlers, so let’s look at those.

Drag Events

There are three drag-related events you can listen for but we’re only going to use two: dragstart and dragend.

  • dragstart - Triggered as soon as we start dragging. This is where we can define the drag data and the drag effect.
  • dragend - Triggered when a draggable element is dropped. This event is generally fired right after the drop zone’s drop event.

We’ll cover what the drag data and the drag effect is shortly.

let dragged; // Keeps track of what's being dragged - we'll use this later! function onDragStart(event) { let target = event.target; if (target && target.nodeName === 'IMG') { // If target is an image dragged = target; event.dataTransfer.setData('text', target.id); event.dataTransfer.dropEffect = 'move'; // Make it half transparent when it's being dragged event.target.style.opacity = .3; } } function onDragEnd(event) { if (event.target && event.target.nodeName === 'IMG') { // Reset the transparency event.target.style.opacity = ''; // Reset opacity when dragging ends dragged = null; } } // Adding event listeners const vehicles = document.querySelector('.vehicles'); vehicles.addEventListener('dragstart', onDragStart); vehicles.addEventListener('dragend', onDragEnd);

There are a couple of things happening in this code:

  • We are defining the drag data. Each drag event has a property called dataTransfer that stores the event's data. You can use the setData(type, data) method to add a dragged item to the drag data. We’re storing the dragged image’s ID as type 'text' in line 7.
  • We’re storing the element being dragged in a global variable. I know, I know. Global is dangerous for scoping but here’s why we do it: although you can store the dragged item using setData, you can’t retrieve it using event.dataTransfer.getData() in all browsers (except Firefox) because the drag data is protected mode. You can read more about it here. I wanted to mention defining the drag data just so you know about it.
  • We’re setting the dropEffect to move. The dropEffect property is used to control the feedback the user is given during a drag and drop operation. For example, it changes which cursor the browser displays while dragging. There are three effects: copy, move and link.
    • copy - Indicates that the data being dragged will be copied from its source to the drop location.
    • move - Indicates that the data being dragged will be moved.
    • link - Indicates that some form of relationship will be created between the source and drop locations.

Now we have draggable vehicles but nowhere to drop them:

See the Pen 1 - Can you park here? by Omayeli Arenyeka (@yelly) on CodePen.


By default, when you drag an element, only form elements such as <input> will be able to accept it as a drop. We’re going to contain our “dropzone” in a <section> element, so we need to add drop event handlers so it can accept drops just like a form element.

First, since it’s an empty element we’re going to need to set a width, height and background color on it so we can see it on screen.

These are the parameters we have available for drop events:

  • dragenter - Triggered at the moment a draggable item enters a droppable area. At least 50% of the draggable element has to be inside the drop zone.
  • dragover - The same as dragenter but it is called repeatedly while the draggable item is within the drop zone.
  • dragleave - Triggered once a draggable item has moved away from a drop zone.
  • drop - Triggered when the draggable item has been released and the drop area agrees to accept the drop.
function onDragOver(event) { // Prevent default to allow drop event.preventDefault(); } function onDragLeave(event) { event.target.style.background = ''; } function onDragEnter(event) { const target = event.target; if (target) { event.preventDefault(); // Set the dropEffect to move event.dataTransfer.dropEffect = 'move' target.style.background = '#1f904e'; } } function onDrop(event) { const target = event.target; if ( target) { target.style.backgroundColor = ''; event.preventDefault(); // Get the id of the target and add the moved element to the target's DOM dragged.parentNode.removeChild(dragged); dragged.style.opacity = ''; target.appendChild(dragged); } } const dropZone = document.querySelector('.drop-zone'); dropZone.addEventListener('drop', onDrop); dropZone.addEventListener('dragenter', onDragEnter); dropZone.addEventListener('dragleave', onDragLeave); dropZone.addEventListener('dragover', onDragOver);

If you’re wondering why we keep calling event.preventDefault() it’s because by default the browser assumes any target is not a valid drop target. This isn’t true all the time for all browsers but it’s better to be safe than sorry! Calling preventDefault() on the dragenter, dragover and drop events, informs the browser that the current target is a valid drop target.

Now, we have a simple drag and drop application!

See the Pen 2 - Can you park here? by Omayeli Arenyeka (@yelly) on CodePen.

It’s fun, but not quite as frustrating as parking. We have to create some rules to make that happen.

Rules and Validation

I came up with some random parking rules, and I’d encourage you to create some of your own. Parking signs usually have days and times you can park as well as what types of vehicles are allowed to park at that moment in time. When we were creating our draggable objects, we had four vehicles: an ambulance, a fire truck, a regular car and a bicycle. So, we’re going to create rules for them.

  1. Ambulance parking only: Monday through Friday, 9pm to 3am.
  2. Fire truck parking only: All day during the weekend.
  3. Regular car parking: Monday through Friday, 3am to 3pm.
  4. Bicycle parking: Monday through Friday, 3pm to 9pm.

Now, we translate these rules to code. We’re going to be using two libraries to handle time and ranges: Moment and Moment-range.

The scripts are already available in Codepen to add to any new demo, but if you are developing outside of Codepen you can copy or link them up from here:

<script defer src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.js"></script> <script defer src="https://cdnjs.cloudflare.com/ajax/libs/moment-range/3.1.1/moment-range.js"></script>

Then, we create an object to store all the parking rules.

window['moment-range'].extendMoment(moment); // The array of weekdays const weekdays = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']; const parkingRules = { ambulance: { // The ambulance can only park on weekdays... days: weekdays, // ...from 9pm to 3am (the next day) times: createRange(moment().set('hour', 21), moment().add(1, 'day').set('hour', 3)) }, 'fire truck': { // The fire truck can obnly park on Saturdays and Sundays, but all day days: ['Saturday', 'Sunday'] }, car: { // The car can only park on weekdays... days: weekdays, // ...from 3am - 3pm (the same day) times: createRange(moment().set('hour', 3), moment().set('hour', 15)) }, bicycle: { // The car can only park on weekdays... days: weekdays, // ...from 3pm - 9pm (the same day) times: createRange(moment().set('hour', 15), moment().set('hour', 21)) } }; function createRange(start, end) { if (start && end) { return moment.range(start, end); } }

Each vehicle in the parkingRules object has a days property with an array of days it can park and a times property that is a time range. To get the current time using Moment, call moment(). To create a range using Moment-range, pass a start and end time to the moment.range function.

Now, in the onDragEnter and onDrop event handlers we defined earlier, we add some checks to make sure a vehicle can park. Our alt attribute on the img tag is storing the type of vehicle so we pass that to a canPark method which will return if the car can be parked. We also added visual cues (change in background) to tell the user whether a vehicle can be parked or not.

function onDragEnter(event) { const target = event.target; if (dragged && target) { const vehicleType = dragged.alt; // e.g bicycle, ambulance if (canPark(vehicleType)) { event.preventDefault(); // Set the dropEffect to move event.dataTransfer.dropEffect = 'move'; /* Change color to green to show it can be dropped /* target.style.background = '#1f904e'; } else { /* Change color to red to show it can't be dropped. Notice we * don't call event.preventDefault() here so the browser won't * allow a drop by default */ target.style.backgroundColor = '#d51c00'; } } } function onDrop(event) { const target = event.target; if (target) { const data = event.dataTransfer.getData('text'); const dragged = document.getElementById(data); const vehicleType = dragged.alt; target.style.backgroundColor = ''; if (canPark(vehicleType)) { event.preventDefault(); // Get the ID of the target and add the moved element to the target's DOM dragged.style.opacity = ''; target.appendChild(dragged); } } }

Then, we create the canPark method.

function getDay() { return moment().format('dddd'); // format as 'monday' not 1 } function getHours() { return moment().hour(); } function canPark(vehicle) { /* Check the time and the type of vehicle being dragged * to see if it can park at this time */ if (vehicle && parkingRules[vehicle]) { const rules = parkingRules[vehicle]; const validDays = rules.days; const validTimes = rules.times; const curDay = getDay(); if (validDays) { /* If the current day is included on the parking days for the vehicle * And if the current time is within the range */ return validDays.includes(curDay) && (validTimes ? validTimes.contains(moment()) : true); /* Moment.range has a contains function that checks * to see if your range contains a moment. https://github.com/rotaready/moment-range#contains */ } } return false; }

Now, only cars that are allowed to park can park. Lastly, we add the rules to the screen and style it.

Here’s the final result:

See the Pen 3 - Can you park here? by Omayeli Arenyeka (@yelly) on CodePen.

There are lots of ways this could be improved:

  • Auto-generate the HTML for the rules list from the parkingRules object!
  • Add some sound effects!
  • Add ability to drag back vehicles to original point without a page refresh.
  • All those pesky global variables.

But I’ll let you handle that.

If you’re interested in learning more about the DnD API and some critiques of it, here’s some good reading:

The post Creating a Parking Game With the HTML Drag and Drop API appeared first on CSS-Tricks.

Categories: Web Technologies

​What do you think about headless CMS?

CSS-Tricks - Thu, 03/08/2018 - 06:14

(This is a sponsored post.)

Headless CMS is the new kid on the technology block. Some say it’s the only way forward, while others call it a fad without a future. So we decided to conduct a study to see what people think about headless CMS and why they want to use it. Has headless got a future?

Share with us your opinion on the headless CMS and get a chance to win $50 Amazon gift card.

Start the survey

Direct Link to ArticlePermalink

The post ​What do you think about headless CMS? appeared first on CSS-Tricks.

Categories: Web Technologies