Perform a head on mysqldump at the same time as piping to mysql

360
February 22, 2017, at 3:19 PM

On Ubuntu 14.04 terminal using bash, i'm able to dump from master mysql db to the slave:

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 --user=myuser --password=[password] --host=127.0.0.1 mydb | mysql --max_allowed_packet=128M -h 192.168.1.110 -u myuser -p[password] otherdb

But I simultaneously want to redirect the mysqldump output to head -30 > /tmp/pointintime.log so I can get the point in time info:

--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.049868', MASTER_LOG_POS=107;

How to do this? I tried using tee but this results in sql syntax errors at the slave import end (as though its breaking up the output and not sending full statements):

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 --user=myuser --password=[password] --host=127.0.0.1 mydb | tee >(head -30 >/tmp/pointintime.log) | mysql --max_allowed_packet=128M -h 192.168.1.110 -u myuser -p[password] otherdb

How to mysqldump import to slave mysql db and simultaneously retrieve the head -30 of the dump? I dont want to dump to a file first because the db is huge, its more efficient to pipe over the network.

I also tried using multiple tee redirects but had the same syntax error:

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 --user=myuser --password=[password] --host=127.0.0.1 mydb | tee >(mysql --max_allowed_packet=128M -h 192.168.1.110 -u myuser -p[password] otherdb) >(head -30 >/tmp/pointintime.log)

ERROR 1064 (42000) at line 47: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near..

Answer 1

You can't do this with tee and head because head closes its input handle once it has output the requested number of lines, breaking the pipe. Here's what you should see with the command you've written.

mysqldump: Got errno 32 on write

Error 32 is "Broken pipe."

The other error -- I suspect -- is because the broken pipe causes the input handle to mysql to be closed, with a partial line of output already written, and the end of file it receives when that happens is interpreted as a premature end of statement.

You need something in the middle that will not break the pipe. Suggestion: the swiss army chainsaw... Perl.

Replace this:

| tee >(head -30 >/tmp/pointintime.log) | 

With this:

| perl -p -e 'print STDERR $_ unless $. > 30' 2>/tmp/pointintime.log |

The -e switch tells Perl that instead of loading a Perl script from a file, that the script is inside the quoted string that follows. The -p switch causes this little one-line "program" to be run for each line of STDIN, after which each input line will be printed to STDOUT, with the input line transiently appearing in the variable $_ as each line passes through, and the variable $. indicating the running line counter. Of course STDERR is the second output stream, which we catch with 2> and redirect to your log file, and once $. > 30 is true, no more lines are written to your log... but they're all written to the output.

Simple as that.

Piping mysqldump through Perl in this way is completely safe -- it will not manipulate the data in any way, it will write it out exactly as it came in. I routinely do this when I need to modify a backup stream on the fly.

Rent Charter Buses Company
READ ALSO
How to set a foreign key to point to an autoincrement int?

How to set a foreign key to point to an autoincrement int?

I am trying to set up a MySQL database where a column is a foreign key pointing to another table that auto incrementsWhen I try to create the table with the foreign key, I get the following error:

319
How do I create a new instance in a table that has a relationship to another table using sequelize?

How do I create a new instance in a table that has a relationship to another table using sequelize?

I am trying to create a new entry on my Topics table that has one to many relation with a Circles table

288
ModuleNotFoundError: No module named 'mysql'

ModuleNotFoundError: No module named 'mysql'

Trying to store values in sql using Python Cgi script, I have downloaded the Connector/Python for MySQL successfullyI used the following code in Python's shell to test my connection:

831
Relationship one to one (1:1)

Relationship one to one (1:1)

I'm with some doubts about relationship one to oneIn my example, i have one company and the company has one address

415