Handy MySQL Commands

Log into mysql:

(enter password on next prompt)

mysql -u username -p

 

Show all databases on server:
mysql -u root -p -e 'show databases;'

 

To reset an auto_increment field to 1:
ALTER TABLE tbl_name AUTO_INCREMENT = 1;

 

To load a local csv file into an empty existing table:

(This assumes you’ve already created your empty table with the same number of fields that your csv file has.)

LOAD DATA LOCAL INFILE '/completePathToLocalData.csv' INTO TABLE tableName FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

 

To dump a mysql db to a file:
mysqldump -u root -p databaseName > dumpName_20180711.sql

 

To load a mysql db dump into an existing empty database:
mysql -u userName -p -h localhost databaseName < /path/to/mysqldbdump.sql

You will have to enter your password on the next prompt.
 

Run a script from a file:

First log in to mysql. Then run:

SOURCE /fullpath/to/file.sql

 

Basic UPDATE query:

First log in to mysql. Then run:

UPDATE `table_name`
SET `column_name` = 'new_value'
WHERE `column_name` = 'old_value';

 

Basic SELECT query:

First log in to mysql. Then run:

SELECT `column_name`
FROM `table_name` 
WHERE `column_name` LIKE '%some\_string%';

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.