MySQL Notes

The following is a list of commands for setting up and using a MySQL database.
  1. Add C:\mysql\bin to your PATH
  2. Logging into the database

    To log in as the mySQL administrator do (by default 'root' has no password): 

    
    mysql -h localhost -u root
  3. Changing the root password 

    
    mysql> set password for root@localhost=PASSWORD('new_password');

    or from a shell prompt run: 

    
    mysqladmin -u root password new_password
  4. To log in as a regular user do: 

    
    mysql -h localhost -u username -p (you'll be prompted for the password)

    mysql -h localhost -u username -p[password] (no space before password)

    mysql -h localhost -u username -p[password] [db_name]
  5. Determining the mySQL version: 

    
    mysql> select version(), current_date;
  6. Showing the current logged in user: 

    
    mysql> select user(), current_date;
  7. Showing all users: 

    
    mysql> select host, user from mysql.user;
  8. Listing the databases: 

    
    mysql> show databases;
  9. To use a database (no semi-colon needed at end): 

    
    mysql> use [db_name]
  10. Creating Databases: 

    
    mysql> create database attensity;
  11. To list all tables: 

    
    mysql> show tables;
  12. To describe a table: 

    
    mysql> describe [table_name];
  13. To add new users to mySQL: 

    
    mysql> grant all privileges
        -> on attensity.*
        -> to platform@localhost
        -> identified by 'pLat4orm';

    mysql> flush privileges;
  14. To see grant privileges for a user: 

    
    mysql> show grants for userid;
  15. To login as the 'platform' user do: 

    
    mysql -h localhost -u platform -p attensity

    or 

    
    mysql -h localhost -u platform -ppLat4orm attensity
  16. To run a sql script from the command prompt: 

    
    mysql -h localhost -u platform -p attensity < CreateAllTables.mysql.sql
  17. Out of space problems. Modify c:\my.ini (or my.cnf)

    According to the MySQL documentation, the solution to this problem is to add the following line to your \Windows\my.ini file:

    innodb_data_file_path = ibdata1:20M:autoextend

  18. To delete the anonymous user: 

    
    mysql -u root -ppassword
    use mysql
    delete from User where User = "";
1