Useful MySQL commands

Nugget post (useful nuggets of information pertaining to MySQL)

– Ubuntu mysql configuration (including username and password) is stored in: /etc/mysql/debian.cnf

– Import data from csv file:

LOAD DATA LOCAL INFILE ‘/importfile.csv’

INTO TABLE test_table

FIELDS TERMINATED BY ‘,’

LINES TERMINATED BY ‘\n’

(field1, filed2, field3);

– Database manipulation:

show databases

create database [dbname]

drop database [dbname] : delete a database

– Backup the database to an sql file:

mysqldump -u [uname] -p [dbname] > [backupfile.sql]

mysqldump -u root -p –all-databases > [backupfile.sql]

mysqldump -u root –password=xxxxxx –all-databases > [backupfile.sql]

– Restore database:

mysql -u [username] -p [database_to_restore] < [backupfile]

– Creating a database called “testdave” and granting all access rights to user “dave”, connecting from any host (@”%”):

create testdave;

grant all on testdave.* to dave@”%”

– Update the password for a user (ivan):

use mysql;

update user set password=PASSWORD(“palestra”) where User=’ivan’;

– Export results of a query into /tmp/result.text in CSV format.

SELECT a,b,a+b INTO OUTFILE ‘/tmp/result.text’     # this line selects the fields to insert into the file, and where to create the file

FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘    #this line determines how to write fields into the file. In this case, seperated by a comma

LINES TERMINATED BY ‘\n’    # this line determines how to terminate each line in the file (with a newline)

FROM test_table;    # the table we are extracting information from

– If a row already exists it will be deleted before the insert is done, but only if the row has a duplicate primary key:

Replace into tablename(field1, field2) values (value1, value2)

– Altering a table’s primary key field:

ALTER TABLE table_name ADD PRIMARY KEY (column name);

– Conditional Insert:

INSERT INTO table_name (columnA, columnB, columnC, columnD, columnE, columnPK)
 SELECT columnA, columnB, columnC, columnD, columnE, columnPK FROM table_to_copy_from
        WHERE table_to_copy_from.parameter='1';

The above is a very interesting insert statement. It will insert into “table_name” columns A,B,C,D etc the contents of the same columns from table_to_copy_from but only if parameter = 1.

Side note, instead of using INSERT INTO, you can use INSERT INTO IGNORE which will not insert the information if the record already exists. Note this is different from the replace into command that I showed before, which will delete a record then insert the new one.

More information from : http://www.danielschneller.com/2007/01/conditional-insert-with-mysql.html

Special thanks to Fabien M for this 🙂

– Recreating mysql system tables (Ubuntu):

1. find out the datadir and tempdir from the my.cnf

2. change as necessary

3. delete the contents of the datadir

4. chmod 777 the temp directory

5. mysql_install_db –user=mysql

– Recreating the privileges on all mysql tables:

mysqld_safe –skip_grant_tables &

mysql_fix_privilege_tables

– Reconfigure mysql on Ubuntu:

dpkg-reconfigure mysql-server-5.0