Common Mysql Admin Tasks

creating/deleting databases

• creating databases

  • mysqladmin -u root -p dbname
  • you can also create a database while connected to the console with the command create database dbname

• dropping a database

  • mysqladmin -u root -p drop dbname
  • you can also drop a database while connected to the console with the command drop database [if exists] dbname .

Adding the options if exists clause allows to drop the database only if it exists to avoid errors while using scripts

Add a user to mysql

Here are a couple user creation scenarios for adding a mysql user to a database. Please note that when you create a remote user with a wildcard host %, it does not automatically grant access from localhost.

In otherwords, to connect, you have to have to use the external ip even if you are accessing localhost.

Also, make sure that mysql is configured to allow access from the ip you are using. Add a local user to mysql from the command line:

    mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* to 'myuser'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;"
    mysql -u root -p -e "FLUSH PRIVILEGES;"

Add a remote user to mysql:

    mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* to 'myuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;"
    mysql -p -u root -e "FLUSH PRIVILEGES;"