10 stupid tips for MySQL

Você pode ler este post em português também.

Hello!

The April’s post brings 10 stupid tips for day-by-day work with MySQL. Let’s see them:

  1. Use a .my.cnf file in your homedir with user/password data:
    [client]
    user=root
    password=my_p@ss-w0rd
  2. End your commands with G instead of ";": This will bring a “form” view instead the normal tabular view. This is necessary a lot of times;
  3. Materialized views: When the regular views (created with CREATE VIEW) doesn’t offer the expected performance, use triggers to feed tables that could work like the regular views. Being carefull, it helps a lot!
  4. Take care of the parameter ENGINE of your databases and tables: Choose the best considering the R/W relation of the tables;
  5. Adjust your MySQL prompt to your best fit at your .my.cnf in your homedir. The example below shows "mysql:db_name>". There are also the u (to show the username) and h (to show the hostname):
    prompt='mysql:d>'
  6. At your mysql prompt, CTRL+A brings you to the begin of the line. CTRL+E brings you to the end;
  7. To create a t2 table with the same structure of t1 table:
    CREATE TABLE t2 LIKE t1;
  8. To create a user and give its access in one line:
    GRANT ALL ON db_name.* TO 'username'@'host' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
  9. To generate a CSV file from the shell (better than OUTFILE ’cause this below shows the column names):
    mysql -AD dbname -e "Your SELECT here;" | tr "t" ";" > resultset.csv
  10. And the last, but not least (stupid). To show the queries in execution (the FULL parameter is optional):

    SHOW [FULL] PROCESSLIST;

Memorize them and your day-by-day with MySQL will be easier.

See you!

About Adriano Laranjeira

Software engineer & developer See more at http://about.me/arglbr
This entry was posted in English and tagged , , , , , . Bookmark the permalink.

1 Response to 10 stupid tips for MySQL

  1. Pingback: Adriano Laranjeira » Blog Archive » 10 dicas estúpidas para o MySQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s