Return to Snippet

Revision: 80611
at March 23, 2020 05:06 by chrisaiv


Updated URL
https://www.chrisjmendez.com/2008/03/23/mysql-commands-i-wish-i-could-remember/

Updated Code
https://www.chrisjmendez.com/2008/03/23/mysql-commands-i-wish-i-could-remember/

Updated Description
https://www.chrisjmendez.com/2008/03/23/mysql-commands-i-wish-i-could-remember/

Revision: 5657
at January 18, 2011 09:07 by chrisaiv


Updated Code
#Loading Data CSV into a general table

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


#Backing up the complete database

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]


#Back Up specific tables. *Note: Multiple tables are seperated by a space.

mysqldump -u [username] -p [password] [databasename] [table1 table2 etc]


#Backing up multiple databases

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


#Restoring a Database

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

#Creating a username and password instead of using root

GRANT ALL PRIVILEGES ON database_development.* TO 'chrisaiv'@'localhost' 
  IDENTIFIED BY 'password' WITH GRANT OPTION; 

#Setting a foreign key between two databases

update albums set artist_id = 3 where id = 6;

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

Revision: 5656
at October 23, 2010 08:55 by chrisaiv


Updated Code
#Loading Data CSV into a general table

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


#Backing up the complete database

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]


#Back Up specific tables. *Note: Multiple tables are seperated by a space.

mysqldump -u [username] -p [password] [databasename] [table1 table2 etc]


#Backing up multiple databases

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


#Restoring a Database

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

#Creating a username and password instead of using root

GRANT ALL PRIVILEGES ON database_development.* TO 'chrisaiv'@'localhost' 
  IDENTIFIED BY 'password' WITH GRANT OPTION; 

#Setting a foreign key between two databases

update albums set artist_id = 3 where id = 6;

Revision: 5655
at March 30, 2008 19:37 by chrisaiv


Updated Code
#Loading Data CSV into a general table

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

#Backing up the complete database

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]


#Back Up specific tables. *Note: Multiple tables are seperated by a space.

mysqldump -u [username] -p [password] [databasename] [table1 table2 etc]


#Backing up multiple databases

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


#Restoring a Database

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

#Creating a username and password instead of using root

GRANT ALL PRIVILEGES ON database_development.* TO 'chrisaiv'@'localhost' 
  IDENTIFIED BY 'password' WITH GRANT OPTION; 

#Setting a foreign key between two databases

update albums set artist_id = 3 where id = 6;

Revision: 5654
at March 30, 2008 19:37 by chrisaiv


Updated Code
#Loading Data CSV into a general table

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

#Backing up the complete database

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]


#Back Up specific tables. *Note: Multiple tables are seperated by a space.

mysqldump -u [username] -p [password] [databasename] [table1 table2 etc]


#Backing up multiple databases

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


#Restoring a Database

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

#Creating a username and password instead of using root
GRANT ALL PRIVILEGES ON database_development.* TO 'chrisaiv'@'localhost' 
  IDENTIFIED BY 'password' WITH GRANT OPTION; 

#Setting a foreign key between two databases

update albums set artist_id = 3 where id = 6;

Revision: 5653
at March 23, 2008 09:48 by chrisaiv


Updated Code
#Loading Data CSV into a general table

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

#Backing up the complete database

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]


#Back Up specific tables. *Note: Multiple tables are seperated by a space.

mysqldump -u [username] -p [password] [databasename] [table1 table2 etc]


#Backing up multiple databases

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


#Restoring a Database

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


#Setting a foreign key between two databases

update albums set artist_id = 3 where id = 6;

Revision: 5652
at March 23, 2008 09:47 by chrisaiv


Updated Code
#Loading Data CSV into a general table

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


#Backing up the complete database

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]


#Back Up specific tables. *Note: Multiple tables are seperated by a space.

mysqldump -u [username] -p [password] [databasename] [table1 table2 etc]


#Backing up multiple databases

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


#Restoring a Database

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


#Setting a foreign key between two databases

update albums set artist_id = 3 where id = 6;

Revision: 5651
at March 23, 2008 09:45 by chrisaiv


Initial Code
Loading Data CSV into a general table

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


Backing up the complete database

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]


Back Up specific tables. *Note: Multiple tables are seperated by a space.

mysqldump -u [username] -p [password] [databasename] [table1 table2 etc]


Backing up multiple databases

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


Restoring a Database

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


Setting a foreign key between two databases

update albums set artist_id = 3 where id = 6;

Initial URL


Initial Description
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

Initial Title
MySQL Commands I wish I could remember

Initial Tags
mysql, sql

Initial Language
SQL