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.
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.
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).