Thursday, October 02, 2008

Some useful MySQL commands

Lately I have been getting quite intimate with the MySQL database and have been doing things beyond the standard queries and adds. Here is a list of some other useful commands that I have been working with lately. Please know that any options to these commands that are in [ ] are considered optional:

1. Delete a databse:

Format: drop {database | schema} [if exists] db_name;

This command drops all tables in the database and then deletes the database. You need to have 'drop' privilages to do this. *Note: As of MySQL 5.0.2 "schema" is a synonym for database.

2. Delete a table:

Format: drop [temporary] table [if exists] table_name[, tablename, ...] [restrict | cascade];

This command will delete one or more tables from a database. Again, you must have drop permissions for each table.

3. Back up a table to file (in case a recovery is needed after doing something like an alter on a table):

CLI Format: mysqldump --user=<username> --password -B <database_name> --tables <table_name> > filename.sql

This command should be issued from the command line, not from within mysql's interface, as the two previous commands would be. The '.sql' extension on the file tells you that the data inside is for sql. If you open the file, you will see a plethora of sql commands to recreate the table as it was.

4. Restore a table from a backup sql file:

CLI Format: mysql -u <username> -p <database_name> < filename.sql

This will restore the table to its original glory.

5. Delete all data from a table:

Format: truncate <table_name>;

This will erase all data from the table, leaving the defined schema in tact.

6. Grant permissions on database's to a specific user:

Format: grant {comma separated permissions list} on <db_name>.* to `username`@`localhost`;

This will grant whatever permissions needed to the specified user. Now, if the user account in MySQL does not exist, then after the username@localhost piece, you will need to specify:

identified by password `password`;

Now, the tic marks around `password` and `usrename`@`localhost` are all back tics. That is what I tend to use and have never had a problem.

One note of caution, BE CAREFUL using the above commands. They are commands that will cause serious changes to your database and tables. Practice on a test database before doing ANYTHING on a production database. Also, make sure to back up any tables or databases that you work on. As we all know, whatever can happen.... usually will.

No comments:

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 License.