Mysql: Change account password

Through the mysql client: update mysql.user set password=password('NEW_PASSWORD') where user='USERNAME' and host='HOSTNAME'; flush privileges; Through the command line: mysqladmin -u USERNAME -p CURRENT_PWD password NEW_PWD Replace USERNAME, CURRENT_PWD and NEW_PWD with appropriate values.

Mysql: Delete orphan records

Finding records that do not match between two tables. CREATE TABLE bookreport ( b\_id int(11) NOT NULL auto\_increment, s\_id int(11) NOT NULL, report varchar(50), PRIMARY KEY (b\_id) ); CREATE TABLE student ( s\_id int(11) NOT NULL auto\_increment, name varchar(15), PRIMARY KEY (s\_id) ); insert into student (name) values ('bob'); insert into bookreport (s\_id,report) values ( last\_insert\_id(),'A Death in the Family'); insert into student (name) values ('sue'); insert into bookreport (s\_id,report) values ( last\_insert\_id(),'Go Tell It On the Mountain'); insert into student (name) values ('doug'); insert into bookreport (s\_id,report) values ( last\_insert\_id(),'The Red Badge of Courage'); insert into student (name) values ('tom'); To find the sudents where are missing reports: select s.

Mysql: Dump data in XML or HTML

Assume you have the table “exams” in the database “test”.Then, the following will give you XML output if executed from the shell prompt with the “-X” option. For html output use the “-H” option. mysql -X -e "select \* from exams" test

Mysql: Dumping data to a file

To dump data into a comma separated file use this: SELECT * INTO OUTFILE 'tablename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM tablename; Replace tablename with the tablename of the table you which to dump to a file.

Mysql: Loading data from file

Loading Data into Tables from Text Files. Assume you have the following table. CREATE TABLE loadtest ( pkey int(11) NOT NULL auto\_increment, name varchar(20), exam int, score int, timeEnter timestamp(14), PRIMARY KEY (pkey) ); And you have the following formatted text file as shown below with the unix “tail” command: $ tail /tmp/out.txt 'name22999990',2,94 'name22999991',3,93 'name22999992',0,91 'name22999993',1,93 'name22999994',2,90 'name22999995',3,93 'name22999996',0,93 'name22999997',1,89 'name22999998',2,85 'name22999999',3,88 NOTE: loadtest contains the "pkey" and "timeEnter" fields which are not present in the "/tmp/out.