MySQL dumps from cloudSQL, transform to csv, then load infile locally

I got some databases I need to migrate from one server to another.  We opted for the good old fashion `mysqldump`

So far it is wicked slow to import one of the tables, while working through the solution I needed to transform the data from the dump format of insert statements per line to a csv file.  Yeah, I could have just done the export again into this new format but why bother with all that and the network egress fees again, lets just transform what we have.

I asked ChatGPT to come up with some script to do this for me... that script failed :(

I ended up basically just settling on the good old fashion grep & sed commands:

time grep "INSERT INTO" /path/to/file_src.sql | sed -e 's/^[^(]*[(]//' -e 's/),(/\n/g' -e 's/);$//' > /path/to/file_dest.csv

so with this one command you consider only the lines with INSERT commands and you remove the front part something like: "INSERT INTO `table` ("

You also "split" and make new lines on "),(" which is the "boundary" between all the rows

Then we trim the end ");"

So in this one-liner we do basically all we need... if you have hundreds of gigs of tables like me it's faster to run this than re-export it all over the net.

We then needed to import it so I manually took the create table part of the dump, added it into another file and wrapped it in a loop.  I experimented with additional MySQL tuning bits and bobs over the iterations and have no idea if it is helping or not.

Find more and the scripts in this gist https://gist.github.com/ivanlawrence/c3fdbdcab0a34df714f0361f5f55e721

Google Workspace Routing: gmail pro catch-all routing

 I like to do this dumb thing where I use unique email addresses per service.  It started to thwart organizations from selling my email address, but now everything has been breached it has a unique benefit that I don't have the same username at every site so searching through my password manager is fast and also having someone try to login as `microsoft@example.com` but on google has nothing to do with my real account.

Well, I've moved my DNS registrar and name servers around a bit now that Google Domains has been sold.  Up shot is CloudFlare Domain Name Registration is a little cheaper and their API is very easy to deal with if you wanna do a roll your own Dynamic DNS service or something.

When I moved DNS some of my emails started to bounce :(

Looks like there was a gmail default route for "Google Domains Email Forwarding" but I couldn't find anything about it in the docs Set up Default routing for your organization which I think I was using before but now was no longer able to route since I wasn't using Google Domains?

The changes I made were for All Recipients > Envelope recipient > Change envelope recipient > Recipient username > my_username

When tested this seemed to give the result I wanted where the previously bounced emails like `foo@example.com` now was getting sent to `my_username@example.com`.

I thought I would share it here in case I forget and need to do it again.  I have other default routes for other domains and one of them adds a recipient instead of changing the recipient and that kinda dirties the email headers (more than they are now) but it's also a way.

MixPanel: use mixpanel.people.set() and mixpanel.identify() together

Found at https://community.mixpanel.com/questions/585/how-to-implement-peopleset-in-js-api.html

Question

stephendb9 asked · Jan 22 at 11:45 AM

How to implement people.set() in JS API

What is the right way to implement tracking for People properties in JS? The documentation states:

In order to send profile updates, you must call mixpanel.identify. The Mixpanel library does not automatically create people profiles for any user that performs an event; you have to explicitly call mixpanel.identify, which empowers you to only create profiles for registered users.

But in the examples, identify() is called before people.set() and sometimes it's after. Or not at all.

https://developer.mixpanel.com/docs/javascript#section-setting-profile-properties

• What is correct sequence of calls?
• Does identify() have to be called every time you want to change a property? Or just once per Profile?
• If identify() was already called for this Profile, do you just call it again with the same id?
• What if this Profile was created with alias()?

apipeople profilespeople properties

 1

1 Answer

annecamacho answered · Mar 11 at 2:27 PM

@stephendb9 -

The mixpanel.identify() call when paired with a mixpanel.people.set() call is intended to flush the data to Mixpanel, and it is required anytime that you intend to create or update a Mixpanel people property.

What is correct sequence of calls?

• You just need to call identify in the same function/context that you call people.set — the actual sequence in your code is less critical.

Does identify() have to be called every time you want to change a property? Or just once per Profile?

• Identify needs to be called every time you make a people.set call — to either create or update a people profile within Mixpanel.

If identify() was already called for this Profile, do you just call it again with the same id?

• If you've already identified the user you do not need to pass anything through the identify function, as you are just using it to flush the data. My personal preference is to always have an empty identify call i.e. mixpanel.identify() vs mixpanel.identify(xyz) after every people.set to ensure the data is sent to Mixpanel and stored to the profile.

What if this Profile was created with alias()?

• If you are aliasing or identifying a user, then a profile will not be created, unless you are also doing a people.set. Alias and identify methods are intended to help manage the identities of your users throughout their interactions in your site or app, but they also serve a second purpose of flushing the data to Mixpanel. The alias method, similar to identify, has a flush mechanism, but should only be used for the initial people.set calls when users are registering or signing up for an account. Whenever you add/update future people properties with people.set, you would call identify() to flush the data.

For more details on the alias and identify methods, here is a great overview.

-Anne

gcloud ADC for local testing (and how I will never get that day back)

After spending a day fighting permissions errors while running some test code locally using an account that I know for sure has the correct permissions:
{ Error: 7 PERMISSION_DENIED: User not authorized to perform this action.
at Object.exports.createStatusError (/Users/ivan/app/node_modules/grpc/src/common.js:87:15)
at Object.onReceiveStatus (/Users/ivan/app/node_modules/grpc/src/client_interceptors.js:1188:28)
at InterceptingListener._callNext (/Users/ivan/app/node_modules/grpc/src/client_interceptors.js:564:42)
at InterceptingListener.onReceiveStatus (/Users/ivan/app/node_modules/grpc/src/client_interceptors.js:614:8)
at callback (/Users/ivan/app/node_modules/grpc/src/client_interceptors.js:841:24)
code: 7,
metadata: Metadata { _internal_repr: {} },
details: 'User not authorized to perform this action.',
note: 'Exception occurred in retry method that was not classified as transient' }

I was even able to use gcloud to post a message to the same topic my code was trying to use just doing it from the CLI!  no permission errors there!

So after rebuilding and reconfiguring my environment, not to mention reinstalling gcloud and changing the configs a ton of times... I found out that 'gcloud auth login' no longer creates ADC for you... you must use
“gcloud auth application-default login”

This takes you through a browser based auth for the 'Google Auth Library' and BLAM-O you are in business!

Dell iDRAC Reboot Guest OS and Restart DRAC commands

Send the following commands via SSH:

ssh <drac hostname or IP> 'racadm getsysinfo'

You can even redirect it to a file on your local system:

ssh <drac hostname or IP> 'racadm getsysinfo' | tee -a /path/to/file.txt

Reboot Guest OS (the OS installed on the hardware)

(from: https://www.devops.zone/tricks/connecting-ssh-drac-reboot-server/)

Connect to the Dell Remote Access Controller (Drac) IP address via SSH. Then execute:

racadm serveraction <action>

whereas you replace <action> with one of the following:

• powerdown — Powers down the managed system.
• powerup — Powers up the managed system.
• powercycle — Issues a power-cycle operation on the managed system. This action is similar to pressing the power button on the system’s front panel to power down and then power up the system.
• powerstatus — Displays the current power status of the server (“ON”, or “OFF”)
• hardreset — Performs a reset (reboot) operation on the managed system.

So, to power off and back on your server, you just type racadm serveraction powercycle.

If your Drac crashes for any reason, you may want to reset it: (more info)

racadm racreset soft

If you want to have information about your current server, type:

racadm getsysinfo

(from: https://frednotes.wordpress.com/2012/11/13/reset-dell-idrac-using-ssh/)

How to reboot an DELL idrac when web page refuse access:

Connect to idrac IP using ssh with the password refused by web page

$ ssh root@192.168.0.120
root@192.168.0.120's password:

The magic DELL tool is here : radadm

/admin1-> racadm
===============================================================================
 RACADM version 1.80 (Build 17)
 Copyright (c) 2003-2010 Dell, Inc.
 All Rights Reserved
 ===============================================================================
RACADM usage syntax:
racadm <subcommand> <options>
Examples:
racadm getsysinfo
 racadm getsysinfo -d
 racadm getniccfg
 racadm setniccfg -d
 racadm setniccfg -s 192.168.0.120 255.255.255.0 192.168.0.1
 racadm getconfig -g cfgLanNetworking
Display a list of available subcommands for the RAC:
racadm help
Display more detailed help for a specific subcommand:
racadm help <subcommand>
------------------------------------------------------------------------------

So to reboot :

/admin1-> racadm racreset soft
RAC reset operation initiated successfully. It may take up to a minute
for the RAC to come back online again.
/admin1-> Connection to 192.168.0.120 closed by remote host.
Connection to 192.168.0.120 closed.

I need to rsync from a root protected directory on one server to a root protected directory on another...

Thanks to: https://crashingdaily.wordpress.com/2007/06/29/rsync-and-sudo-over-ssh/

sudo rsync -av -e "ssh" --rsync-path="sudo rsync" username@srv01.prod.example.com:/tftpboot/ /tftpboot/

So here is the thing...

I SSH(ed) to the remoteA and forwarded my ssh keys (ForwardAgent yes) and ran this command:

Breakdown:

sudo rsync -av -e "ssh" 

^ On the host I'm running this on run it under sudo

--rsync-path="sudo rsync" username@srv01.prod.example.com:/tftpboot/ /tftpboot/

^ On the remote server run rsync server as sudo

SSH ProxyCommand to use a "gateway" ssh server for subsequent SSH connections

from: https://heipei.github.io/2015/02/26/SSH-Agent-Forwarding-considered-harmful/

ProxyCommand to the rescue

If you’re still reading this, chances are that you’re not aware of the awesome toolbox that is SSH (or at least OpenSSH, let’s be honest).

OpenSSH has an option called ProxyCommand. It works by specifying a ProxyCommand to connect to host B. The config would look like this for our simple example:

1 Host hosta
2  User userfoo
3  Hostname 123.123.123.123
4 
5 Host hostb
6  User userbar
7  Hostname 192.168.1.1
8  Port 2222
9  ProxyCommand ssh -q -W %h:%p hosta

This config tells your local SSH client to connect to host A via a direct connection. Now, if you type ssh hostb what it will do is connect to host A first and then forward your SSH client via the ProxyCommand to the host and port specified by the -W parameter, in this case I used 192.168.1.1 to underline that this is an internal host. Your local SSH client will then authenticate to host B as if you were connected to it directly.

Another observation here is that you don’t need to memorize anything about the host, neither it’s IP or obscure hostname, port or username when you can simply alias it with the Host line.