MoonPoint Support Logo


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals

Advanced Search
Sun Mon Tue Wed Thu Fri Sat

Sun, Jun 29, 2014 8:58 pm

Showing permissions for MySQL databases

Any of the following commands can be used to view privileges on MySQL databases for the currently logged in user:


E.g., if logged into MySQL as the user joe:

mysql> show grants;
| Grants for joe@localhost                                                                           |
| GRANT USAGE ON *.* TO 'joe'@'localhost' IDENTIFIED BY PASSWORD '75ac044c66d44642'                  |
| GRANT ALL PRIVILEGES ON `family`.* TO 'joe'@'localhost'                                            |
| GRANT ALL PRIVILEGES ON `partslist`.* TO 'joe'@'localhost'                                         |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `tokyo`.`tokyo` TO 'joe'@'localhost'               |
4 rows in set (0.00 sec)

To show databases for which the user has access, the show databases command can be used:

mysql> show databases;
| Database           |
| information_schema |
| family             |
| partslist          |
| tokyo              |
4 rows in set (0.00 sec)

To see which users have access to a particular database, you use the command select user from mysql.db where db='dbname';, where dbname is the name of the relevant database, if you are logged into an account with administrator privileges, e.g., as root. E.g. for a database named partslist:

mysql> select user from mysql.db where db='partslist';
| user |
| joe  |
1 row in set (0.01 sec)

[/software/database/mysql] permanent link

Sun, Jun 29, 2014 7:40 pm

Dropping tables with a similar prefix from a MySQL database

I wanted to remove all of the tables from a MySQL database where the tables all had names starting with a similar prefix. Rather than deleting the tables one by one, I wanted to reduce the time and typing on my part to drop all of the tables. One way to do so is to use the mysqldump command to create a file that contains all of the needed DROP commands. You can issue a mysqldump command similar to the following where user is a user name with permissions to access the database and database is the name of the database:

mysqldump -u user -p database > dump.sql

The -p option will prompt you for the password for the user. E.g., supposing the user name is jdoe and the database is named jdoe_db:

$ mysqldump -u jdoe -p jdoe_db > dump.sql
Enter password:

Then, on a Unix/Linux/ system, you can use the grep command to search for lines in the dump.sql file produced by the mysqldump command for "DROP TABLE IF EXISTS" and then pipe that output into another grep command that searches only for lines containing the particular string in the table name that you want to key on for dropping tables. E.g., suppose you want to drop all tables that have example as part of the table name:

$ grep "DROP TABLE IF EXISTS" dump.sql | grep example > drop.sql

You can check the drop.sql file output by the above commands to verify that only the particular tables you wish to drop will be removed from the database.

$ more drop.sql

DROP TABLE IF EXISTS `example_settings`;
DROP TABLE IF EXISTS `example_smileys`;
DROP TABLE IF EXISTS `example_spiders`;

If you are satisfied that only the tables you want removed from the database will be deleted from the database, you can then issue the command:

mysql -u user -p database < drop.sql

E.g., for the example above, you could use:

$ mysql -u jdoe -p joe_db < drop.sql
Enter password:

The dump.sql and drop.sql files can then be deleted.

[/software/database/mysql] permanent link

Sat, Jun 21, 2014 12:23 pm

Prepending a character to all values in a column

I needed to prepend a "/" value to a directory field in a MySQL table named files for every entry in the table. With a column named Directory in the table, that can be done using the UPDATE command. E.g.:

UPDATE fles SET Directory = Concat('/', Directory);

[/software/database/mysql] permanent link

Tue, Jun 17, 2014 11:24 am

Creating a business category in Quicken 2014

I wanted to create a new business expense category in Quicken 2014 named Hardware. But when I created a new expense category, the category always defaulted to be a personal expense. I could not locate a means to specify that I wanted the new category to be in the business rather than personal group and I didn't see any way to change the classification to business after the category was created. I could put the new business expense category as a subcategory beneath an existing business category, but that is not what I wanted to do. There is a way to put the new category as a top-level category in the business group rather than have it be a subcategory, though, by first creating it as a subcategory and then changing it.
  1. Click on Tools.
  2. Select Category List.
  3. Click on the Add Category button.
  4. Type a name for the new category in the Category Name field.
  5. Select the Subcategory of radio button and pick a business category, such as "Supplies (Business)". Add a description if you like.
  6. Click on the Tax Reporting tab and make any changes, if any are needed, there.
  7. Click on OK.
  8. Right-click on the new category in the category list, then select the Expense radio button, rather than the Subcategory of one.
  9. Click on OK.
  10. Click on Done to close the Category List window.

The category will remain as a business category after the change.

[/financial] permanent link

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo