MySQL tricks

MySQL tricks

Login as someuser (type password at prompt):

  mysql -u someuser -p

(To not be promted for the password, you would use 'mysql -u someuser -ppassword')

Load a database from a dump file:

  mysql -u someuser -p dbname < somedatabase.mysql

Dump a database to a file:

  mysqldump -u someuser -p somedatabase >somedatabase.sql

Dump specified tables of a database to a file:

  mysqldump -u someuser -p somedatabase --tables specific_table >somedatabasetable.sql

Output a SQL query to a file:

  echo "select * from title where new = \"No\"" | mysql -u someusername -psomepassword somedatabase >./outputfile.txt;

From within mysql:

Grant privs to someuser on the local box:

  GRANT ALL PRIVILEGES ON dbname.* TO someuser@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;


Grant select only privs to remoteuser on another box:

  GRANT SELECT ON dbname.* TO remoteuser@remotehost IDENTIFIED BY 'password';


Adding a column to a table:

  ALTER TABLE foo ADD COLUMN fubar varchar(255) AFTER baz;

Adding an index to a table:

  ALTER TABLE px_items ADD INDEX (fubar);

Copying a table:

  CREATE TABLE new_table SELECT * FROM orig_table;

Fixing the 'old client' problem:

  UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd') WHERE Host = 'some_host' AND User = 'some_user';

  FLUSH PRIVILEGES;