MYSQL COMMANDS

Login to the MYsql server

First we’ll login to the MySQL server from the command line with the following command:

mysql -u root -p

In this case, I’ve specified the user root with the -u flag, and then used the -p flag so MySQL prompts for a password. Enter your current password to complete the login.

mysql> SELECT DATABASE();

mysql> use databasename;

mysql> SHOW TABLES;

mysql> DESCRIBE cache_criteria;

Creating A Backup

If you want to back up a single database, you merely create the dump and send the output into a file, like so:

mysqldump database_name > database_name.sql

Multiple databases can be backed up at the same time:

mysqldump --databases database_one database_two > two_databases.sql

In the code above, database_one is the name of the first database to be backed up, and database_two is the name of the second.

It is also simple to back up all of the databases on a server:

mysqldump --all-databases > all_databases.sql

************************************************************************************

Restoring a Backup

Since the dump files are just SQL commands, you can restore the database backup by telling mysql to run the commands in it and put the data into the proper database.

mysql database_name < database_name.sql

In the code above, database_name is the name of the database you want to restore, and database_name.sql is the name of the backup file to be restored..

If you are trying to restore a single database from dump of all the databases, you have to let mysql know like this:

mysql --one-database database_name < all_databases.sql

---------------------------------------------------------------------------------------------------------

MySQL InnoDB not releasing disk space after deleting data rows from table

1. OPTIMIZE TABLE

You can use OPTIMIZE TABLE as you mentionned it but you should care about the innodb_file_per_table variable :