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.sql
Code language: CSS (css)
To load a mysql db dump into an existing empty database:
mysql -u userName -p -h localhost databaseName < /path/to/mysqldbdump.sql
Code 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)
Basic DELETE query:

First log in to mysql. Then run:

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

Leave a Reply

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