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

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

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

#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');

#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;

#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;

#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;

#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;

#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;

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;

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

MySQL Commands I wish I could remember

mysql, sql

SQL