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;'Code language: JavaScript (javascript)

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;Code language: JavaScript (javascript)

To backup/dump a mysql db to a file:

mysqldump -u root -p databaseName > dumpName_20180711.sqlCode language: CSS (css)

To load a mysql db dump into an existing empty database:

mysql -u userName -p -h localhost databaseName < /path/to/mysqldbdump.sqlCode language: JavaScript (javascript)

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';
Code language: JavaScript (javascript)

Basic SELECT query:

First log in to mysql. Then run:

SELECT `column_name`
FROM `table_name` 
WHERE `column_name` LIKE '%some\_string%';
Code language: JavaScript (javascript)

DELETE FROM `table_name` 
WHERE `column_name` LIKE '%some\_string%' LIMIT 1;
Code language: JavaScript (javascript)