SSH Mysql command line

Ok so i had a huge amount of trouble trying to upload a sqldump file (large sql file) into the mysql database with phpmyadmin. It kept timing out, even when I increased the timeout and upload file size larger in the php.ini file. So logged in using Putty and used the following SSH commands.

Show how many current open connections there are to mysql
mysqladmin processlist |wc -l

MySQL Dump
mysqldump -u username -ppassword dbname > file.sql

so if my username = alice, password = madhatter database name = wonderlanddb and I wanted to write to a file called sqlbackup.sql and the folder http://www.lenatsu.co.uk/databackup/ I would type:
mysqldump -u alice -pmadhatter wonderlanddb > /var/www/vhosts/lenatsu.co.uk/httpdocs/databackup/sqlbackup.sql

If a file ends in .zip (for example, file.zip) type:
unzip file.zip

If a file ends in .tar (e.g., file.tar) type:
tar -zxvf file.tar.gz

If a file ends in .gz (e.g. file.gz) type:
gzip -d file.gz

If a file ends in .tar.gz (e.g. file.tar.gz) type:
gzip -d file.tar.gz
and then
tar -xvf file.tar

Importing MySQL database
mysql -uusername -ppassword database_name < file.sql
So i would type:
mysql -u alice -pmadhatter wonderlanddb < /var/www/vhosts/lenatsu.co.uk/httpdocs/databackup/sqlbackup.sql

Right if it comes up with some rubbish like
/*!40101 mysql

then it ismost likely you have put “mysqldump” at the beginning of the command instead of “mysql”, last time it happened to me it took a couple of hair pulling hours to figure out why.

SSH Commands

Common SSH Commands

passwd : Change your SSH account’s password, options follow after typing to change account password.

nano [option] [file]: friendly, easy to use file editor
nano w /home/aquhome/public_html/index.php : your now editing index.php with non wrapping of long lines

mkdir [directory_name] : Make a directory with proper default permissions
mkdir aquhome : Makes the directory aquhome in what ever directory your currently in

df : [attribute]
df -h[B] : will show how much disk space is available in human readable format (Megabytes and Gigabytes!)
cd : change directory
cd ~ : go to your home directory
cd  : go to the last directory you was viewing
cd ../ : go up a directory
cd [directory path]
cd /home/aqhome/public_html

ls: lists files & directories in a directory
ls: shows all files with detailed attributes

vi : an advanced editor, tons of features, but much harder to use then nano
vi /home/aquhome/public_html/index.php : now we’re editing that index.php file again!

ln : creates sys links between files and directories
ln  /usr/local/apache/conf/httpd.conf /etc/httpd.conf : Now you can edit the /etc/httpd.conf rather than the original, changes will effect the original instantly, however you can delete the link without deleting the original.

wall : broadcast message
wall [message]
wall so whens the server being restarted?

top : shows continuously updating system processes in a table.

w : shows who is currently logged in and there ip address.

ps : displays processes running. It’s similar to the top command, and it’s used to show currently running processes and their PID.

touch : creates a empty file.
touch [file]
touch index.html : creates a empty index.html

kill : terminate a system process
kill -9 [PID] : You can get a PID by using Top.
kill -9 100545

cp : copy a file
cp yourfile yourfile.copy : copies yourfile to yourfile.copy
cp a /home/aquhome/public_html/* /home/aquhome/public_ftp/ : copies all of the files in public_html to /public_ftp

du : shows disk usage
du sh : shows a summary, in human-readable form, of total disk space used in the currently directory, including subdirectories

netstat : shows all current network connections
netstat -rn : shows routing tables for ip’s.
netstat -an : shows all connections to the server.

chown : commands for system that changes the owner of a file
chown [attribute] newowner.newowner filenames
chown -R aquhome.aquhome /home/aquhome/public_html/index.php

chmod : [-r] permissions filenames
Permissions
u – User who owns the file.
g – Group that owns the file.
o – Other.
a – All.
r – Read the file.
w – Write or edit the file.
x – Execute or run the file as a program.
Numeric Permissions:
CHMOD can also to attributed by using Numeric Permissions:
400 read by owner
040 read by group
004 read by anybody (other)
200 write by owner
020 write by group
002 write by anybody
100 execute by owner
010 execute by group
001 execute by anybody
CHMOD 755 /home/aquhome/public_html/index.php

last : displays last logins to the system
last

rm : delete a file
rm filename.txt : deletes filename.txt, will more than likely ask if you really want to delete it
rm -f filename.txt : deletes filename.txt, will not ask for confirmation before deleting.
rm -rf tmp/ : recursively deletes the directory tmp, and all files in it, including subdirectories. BE VERY CAREFULL WITH THIS COMMAND!!

grep : looks for patterns in files
grep root /etc/passwd : shows all matches of root in /etc/passwd
grep -v root /etc/passwd : shows all lines that do not match root
wc : word count
wc -l filename.txt : tells how many lines are in filename.txt

mv : Moves a file.
mv -f /home/pen/ram.php /root/ Moves ram.php to the directory root

Basic Extracting

tar xvfz imagick-0.9.11.tgz (extracts the .tgz file)

bzip2 and bunzip: Files With .bz2 Extensions

bzip2 filename.txt : zips filename.txt to filename.txt.bz2

bunzip2 filename.txt.bz2 : unzips filename.txt.bz2 to filename.txt

[B]Important Service Commands

Restart apache:
-
service httpd restart

Restart MySQL:
-
service mysql restart

Restart exim:
-
service exim restart

Restart Cpanel:
-
service cpanel restart

Note: “restart” can be replaced with “stop” to quit the service and “start” to start a stopped service.

Restart apache:
service httpd restart
Restart MySQL:
service mysql restart
Restart exim:
service exim restart
Restart Cpanel:
service cpanel restart