I wanted to take a mysqldump of one database, then compress it into a .tar.gz file and all as quickly as possible and with one command.
It’s considerably quicker to import a mysql database from a .gz file, and so this line was very useful for me:
mysqldump my_db -u root | gzip -c | cat > my_db-$(date +%Y-%m-%.%M.%S).sql.gz
to kill a whole bunch of mysql queries in one go, I used something like this that you can adapt for your own use:
select concat('KILL ',id,';') from information_schema.processlist where Host like 'some-sexy-server-ws%';
select concat('KILL ',id,';') from information_schema.processlist where Host like 'some-sexy-server-ws%' into outfile '/tmp/a.txt';
source /tmp/a.txt
How to set a mysql processlist to display by Time of query to help locate long running queries:
mysql> \P awk -F\| '$2 ~ /[0-9]+/ && $6 ~ /Sleep/' | sort -t \| -k 7 -n -r PAGER set to 'awk -F\| '$2 ~ /[0-9]+/ && $6 ~ /Sleep/' | sort -t \| -k 7 -n -r' mysql> show processlist;
I’ve setup a CUPS (Common Unix Printing System) wireless printer sharing in our office, and occasionally the wireless printing randomly stops working. In addition, Samba also stops for file sharing. This kind of behaviour is normally coupled with the LAN/Wireless connection dropping, so you cannot even SSH into the machine itself.
There is no need to restart the computer, just restart the network-manager from the machine itself.
sudo service network-manager restart
Installing mySQL from a binary is very simple. Download the binary to your server.
tar zxvf mysql-binary.tar.gz
cd mysql-binary
mkdir /mysql/partition/here
./configure --prefix==/mysql/partition/here
make
make install
Alternatively, even easier:
tar zxvf mysql-binary.tar.gz
cd /mysql/partition/here
chown -R mysql .
chgrp -R mysql .
scripts/mysql_install_db --user=mysql;
cd .;
./bin/mysqladmin -uroot -pSoMePasSwOrdHeRe