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]
- 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]
- 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
