MySQL: Commands I wish I could remember

Import CSV file

LOAD DATA INFILE "/data.csv" 
  INTO TABLE alldata 
  FIELDS TERMINATED BY ",";  

#Dump DB data to CSV file
SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products
Loading Data CSV into a general table


Backing up a complete database

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]
Backing up the complete database


Backing up a specific table

mysqldump -u [username] -p [password] [databasename] [table1 table2 etc]
Back Up specific tables. *Note: Multiple tables are seperated by a space.

Backing up multiple databases

mysqldump -u [username] -p [password] --databases [databasename] > [backupfile.sql]
Backing up multiple databases

Restoring a database

mysql -u [username] -p [password] [database_to_restore] < [backupfile.sql]
Restoring a Database

Setting up foreign keys between two databases.

update albums set artist_id = 3 where id = 6;
Setting a foreign key between two databases

Set a Password for Root

SET PASSWORD FOR root@localhost=PASSWORD('secretpassword');
Set a Password for Root