Posted By

chrisaiv on 03/23/08


Tagged

mysql sql


Versions (?)

Who likes this?

20 people have marked this snippet as a favorite

sergiomco
basicmagic
melling
uisluu
heinz1959
mitchkramez
snucko
lfatr
tgunr
yosemite610
Nix
umang_nine
Gr33d
Pixelpower
JimBarrows
colingardom
wirenaught
jeremydouglass
ringo380
rentnil


MySQL Commands I wish I could remember


 / Published in: SQL
 

  1. Load Data from a csv file\r\n2. Backing up a complete database\r\n3. Backing up a specific table\r\n4. Backing up multiple databases\r\n5. Restoring a database\r\n6. Setting up foreign keys between two databases
  1. #Loading Data CSV into a general table
  2.  
  3. LOAD DATA INFILE "/data.csv"
  4. INTO TABLE alldata
  5. FIELDS TERMINATED BY ",";
  6.  
  7. #Dump DB data to CSV file
  8. SELECT *
  9. INTO OUTFILE '/tmp/products.csv'
  10. FIELDS TERMINATED BY ','
  11. ENCLOSED BY '"'
  12. ESCAPED BY '\\'
  13. LINES TERMINATED BY '\n'
  14. FROM products
  15.  
  16.  
  17. #Backing up the complete database
  18.  
  19. mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]
  20.  
  21.  
  22. #Back Up specific tables. *Note: Multiple tables are seperated by a space.
  23.  
  24. mysqldump -u [username] -p [password] [databasename] [table1 table2 etc]
  25.  
  26.  
  27. #Backing up multiple databases
  28.  
  29. mysqldump -u [username] -p [password] --databases [databasename] > [backupfile.sql]
  30.  
  31.  
  32. #Restoring a Database
  33.  
  34. mysql -u [username] -p [password] [database_to_restore] < [backupfile.sql]
  35.  
  36. #Creating a username and password instead of using root
  37.  
  38. GRANT ALL PRIVILEGES ON database_development.* TO 'chrisaiv'@'localhost'
  39. IDENTIFIED BY 'password' WITH GRANT OPTION;
  40.  
  41. #Setting a foreign key between two databases
  42.  
  43. UPDATE albums SET artist_id = 3 WHERE id = 6;
  44.  
  45. #Set a Password for Root
  46. SET PASSWORD FOR root@localhost=PASSWORD('secretpassword');

Report this snippet  

You need to login to post a comment.