Segfault > IT > Lookups > MySQL commands

 

  • Syntax to execute a series of commands contained in a text file:
    mysql [-p -u myuser] mydatabase < sql2execute.txt
  • Subqueries are available from version 4.1 onwards.
  • Add additional users (power-mode):
    GRANT ALL PRIVILEGES ON [db or *].* TO 'newuser'@'localhost' IDENTIFIED BY 'newuserpasswd' [WITH GRANT OPTION];
  • Drop a user:
    DROP USER [username]
  • List all existing users:
    select host, user from mysql.user
  • List privileges granted to a user:
    show grants for [username]@[hostname]
  • To change the password type:
    /usr/bin/mysqladmin -u root -p password [new-password]
    You will be prompted for the old password
  • To write the output of a query/sql to a file use:
    mysql -u [youruserid] -p --tee output.txt
    Omit "-p" if you didn't set a password.
  • To grant all rights for a specific DB to a user:
    grant all on [dbname].* to [username]@localhost identified by '[userpassword]';
  • Run a SQL-script:
    • From the command shell:
      mysql -u [myuser] -p [ [myscript.sql]
    • From within MySql:
      mysql > source [filename]
      or
      mysql > \. [filename]
  • Generate insert statements for a single table:
    mysqldump --skip-opt --no-create-db --no-create-info -p -u MYUSER MYDB MYTABLE | grep -i "insert into" > MYTABLE.sql && echo "commit;" >> MYTABLE.sql
  • To transfer a database:
    • Extract from the source server:
      mysqldump -u "YOURUSER" -p --lock-tables "YOURDB" > YOURDB_dump.sql
    • Import into the target server:
      mysql -u YOURUSER -p "YOURDB" < YOURDB_dump.sql