There are a lot of usefully MySQL commands that I either don’t run enough to remember or just end up using some GUI to accomplish. Nothing against Navicat, but what kind of sysadmin would I be if I couldn’t do it at the command line as well. Anyhow, I had to do a bunch of this kind of work lately, so I thought I would write the more common commands down here so I would have them to reference.
It is generally a good idea to firewall off your MySQL port (3306) unless you are on a secure network. The downside to this is that it prevents you from using GUI tools like Navicat to connect to your database server. No problem, just use this command to SSH tunnel a connection. After doing this, you can connect to your database on localhost port 8888:
ssh -fNg -L 8888:127.0.0.1:3306 login@mysql-server
Once you have installed a new instance of MySQL, you have to change the root password:
# mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'yourhostname' = PASSWORD('newpwd');
mysql> exit
Depending on the way you handle your MySQL installation, you will most likely have to run the mysql_upgrade script after upgrades to use all the new features:
mysql_upgrade -u root –password=yourpassword
Do a thorough check to see if your tables are corrupted:
mysqlcheck -u root -p --check --extended --all-databases
Repair any corruption if it exists:
mysqlcheck -u root -p --repair --all-databases
Look at all your tables and optomize them if necessary:
mysqlcheck -u root -p --optimize --all-databases
