NoChecksum's questions

What is the safest way to clean up?

MySQL server 5.5.62-0 on Debian 8 with no replication.

I made a mistake and created a new column on a 26GB table. SHOW PROCESSLIST showed MySQL was copying the data to a tmp table at 100% CPU.

+-----------+------+-----------+--------+---------+------+-------------------+------------------+
| Id        | User | Host      | db     | Command | Time | State             | Info             |
+-----------+------+-----------+--------+---------+------+-------------------+------------------+
| 145904211 | root | localhost | huge   | Query   |  160 | copy to tmp table | ALTER TABLE ...  |
| 145905739 | root | localhost | NULL   | Query   |    0 | NULL              | show processlist |
+-----------+------+-----------+--------+---------+------+-------------------+------------------+

A few minutes later the main partition was full and CPU dropped to 0. I used systemctl stop mysql hoping it would clean up temporary files. The service wouldn't restart either.

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/root        79G   75G 1000K 100% /

$ sudo systemctl start mysql
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.

I shut down the VPS and expanded the disk. The server restarted fine and I was able to start the MySQL process and connect to it. Everything seems to be operating.

However disk usage has not reduced since the incident 20 minutes ago.

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/root       158G   75G   75G  50% /

Will housekeeping kick in or should I manually clean up the mess I made? What is the safest way to do that?

A script was automatically creating an SSH tunnel in the background with a simple command like this, which works:

ssh -R 19000:localhost:22 [email protected] -i ~/.ssh/keyfile &

This answer describes how to set up dynamically assigned ports and redirect the output of SSH such that the port can be captured by my script. It works too:

ssh -R 0:localhost:2222 [email protected] -i ~/.ssh/keyfile 3>&1 1>&2 2>&3 | sed -n "s/^Allocated port \([0-9]*\) for/port \0/p" > portfile.txt &

Separately, this article suggests a method to have tunnels auto-closing after a specific time, which also works on its own:

ssh -R 19000:localhost:22 [email protected] -i ~/.ssh/keyfile sleep 10;

Now I wish to combine them into one line but the tunnel collapses before my sleep command can execute:

(ssh -R 0:localhost:22 [email protected] -i ~/.ssh/keyfile 3>&1 1>&2 2>&3 "sleep 1" | sed -n "s/^Allocated port \([0-9]*\) for remote forward to localhost:2222/\1/p" > portfile.txt); sleep 10 &

Output is redirected and the port number is written to the file as expected, but immediately the tunnel closes and the sleep command runs.

Please can someone explain what I'm doing / doing wrong in this command, and why it doesn't work?

I created some custom 4xx and 5xx HTML error pages for most errors listed on the Wikipedia page. I'd like to install them on any nginx server I set up, without impacting performance too much.

Following the documentation I came up with this configuration directive for an error:

error_page 404 /nginx/404.html;
location = /nginx/404.html {
        alias /usr/share/nginx/errors/404.html;
        internal;
}

I put this in /etc/nginx/snippets/errors.conf and can include the snippet in all my server blocks.

This works great, however...

It prepends nearly 50 location directives to every server block on the box.

Is this excessive, and is there a more performance-friendly way to achieve the same thing? I avoided server-side includes, if statements, and rewrites thinking it would require more server resources.

I followed the official install guide to build nginx with the latest stable pagespeed module. It works in some regards so I'd like to debug further with the admin tools. They are enabled in nginx as follows;

http {
    pagespeed FileCachePath "/mnt/ramdisk/ngx_pagespeed_cache";
    pagespeed StatisticsPath /ngx_pagespeed_statistics;
    pagespeed GlobalStatisticsPath /ngx_pagespeed_global_statistics;
    pagespeed MessagesPath /ngx_pagespeed_message;
    pagespeed ConsolePath /pagespeed_console;
    pagespeed AdminPath /pagespeed_admin;
    pagespeed GlobalAdminPath /pagespeed_global_admin;
}

server {
    location /ngx_pagespeed_statistics { allow 12.34.56.78; deny all; }
    location /ngx_pagespeed_global_statistics { allow 12.34.56.78; deny all; }
    location /ngx_pagespeed_message { allow 12.34.56.78; deny all; }
    location /pagespeed_console { allow 12.34.56.78; deny all; }
    location ~ ^/pagespeed_admin { allow 12.34.56.78; deny all; }
    location ~ ^/pagespeed_global_admin { allow 12.34.56.78; deny all; }

    pagespeed on;

    location ~ "\.pagespeed\.([a-z]\.)?[a-z]{2}\.[^.]{10}\.[^.]+" {
        add_header "" "";
    }
    location ~ "^/pagespeed_static/" { }
    location ~ "^/ngx_pagespeed_beacon$" { }
}

(Where 12.34.56.78 is my IP address)

When I visit https://example.org/ngx_pagespeed_statistics I see a brief glimpse of statistics before it says "Sorry, failed to update the statistics. Please wait and try again later."

In the browser inspector I see it tries to load /ngx_pagespeed_statistics/stats_json which returns a 404.

Did I miss a step in configuration? What could be the cause?

I have a linux server with a lot of unused files, but it is low on disk space. I'd like to take a copy of the files before deleting them. There is not enough disk space to store the compressed version before downloading.

I've tried scp -C hostname:/path . which is streaming down the files with compression but there are lots of tiny files which is taking a long time to copy down.

Another thread suggested a command like ssh 10.0.0.4 "cat /tmp/backup.sql | gzip -c1" | gunzip -c > backup.sql but that only works for one file.

Are there other methods to achieve it?