LSP Database Management

You'll need to be granted SSH access by sharing your public key with a server admin.

1. At the console, login to MySQL as lsp

   mysql -u lsp -p

2. Select the lsp database

   use lsp;

3. Determine which table you'd like to touch

   show tables;

   Example:

   # +---------------+
   # | Tables_in_lsp |
   # +---------------+
   # | categories    |
   # | comments      |
   # | files         |
   # | filetypes     |
   # | licenses      |
   # | ratings       |
   # | subcategories |
   # | users         |
   # +---------------+
   # 8 rows in set (0.00 sec)

4. Determine the table layout by selecting the first record:

   select * from comments limit 1

   Example:

   # mysql> select * from comments limit 1;
   # +----+---------+---------+---------------------+---------------------------------------------+
   # | id | user_id | file_id | date                | text                                        |
   # +----+---------+---------+---------------------+---------------------------------------------+
   # |  1 |     555 |     555| 2011-02-15 05:22:91  | What a nice song, thanks for sharing!       |
   # +----+---------+---------+---------------------+---------------------------------------------+
   # 1 row in set (0.01 sec)

5. Delete by id or a very unique pattern... Don't forget the semicolon at the end!

   -- CAREFUL -- CAREFUL --- CAREFUL
   delete from comments where id = 1;

6. Alternately, you may delete by other fields...

   select * from comments where user_id = 555 and file_id = 555;
   (1 row returned)
   -- CAREFUL -- CAREFUL -- CAREFUL
   delete from comments where user_id = 555 and file_id = 555;
   (1 row affected)

7. Or by pattern...

   select * from comments where text like '%very bad word%';

8. Reset a user's password:

   update users set password = LOWER(SHA1('some_new_password')) where login = 'some_valid_user_name';

9. Ctrl+D to logout of MySQL

10. Ctrl+D again to logout of SSH