How to optimize all tables from all databases

The following command will optimize all of your databases’ tables within MySQL.
It is important to optimize tables to reduce data fragmentation.

mysqlcheck -Aop -uroot

If you are not using root replace it with your username.

-A : Check all tables in all databases. This is the same as using the –databases option and naming all the databases on the command line.
-o : optimize the tables.
-p : Prompts for a password to use when connecting to the MySQL server.
-u : The MySQL user name to use when connecting to the server.

Inspired by this article.

――

How to backup MySQL users except root on Ubuntu

When you want to backup your mysql databases, you usually do mysqldump … –all-databases or mysqldump … –databases mysql … but you end up with the whole mysql table which is a pain to insert back when you need it because it can mess up the root password or the debian-sys-maint user…

If you wish to just backup all the users and privileges other than root and debian-sys-maint, you can use this command:

mysqldump -nt -uroot -p -w"User NOT LIKE 'root' AND User NOT LIKE 'debian%'" mysql user db > users_privs.sql

Here’s an explanation of each of the options:

  • -nt: Do not add “drop table” and “create table”.
  • -uroot -p: Connect as root and ask for a password
  • -w…: Add a “WHERE” condition to each query. We exclude everything related to root and debian-sys-maint.
  • mysql user db: Dump the user and db tables from the mysql database.
  • > users_privs.sql: Store the sql dump into the users_privs.sql file.
――

How To use MySQL Administrator & Query Browser through SSH tunnel

I try to keep my VPS (Virtual Private Server) as light as possible. That’s also why I don’t have PHPMyAdmin installed and wouldn’t want to install it. The only way of accessing MySQL and executing commands is through the mysql CLI (Command Line Interface), but I don’t always remember the exact queries to create a user, database or to grant privileges.

I recently decided to look for some GUI (Graphical User Interface) tools to tweak and administrate a MySQL server. I found the quite well-known MySQL Administrator (and Query Browser).

MySQL Administrator

MySQL Administrator

The problem is that by default (and for obvious security reasons), my MySQL server is not accessible from the internet (only accessible by localhost itself). This is where SSH tunnels enter the action!
Read the rest of this entry »

――