How to Import All CSV Files in Directory in MySQL via Bash

Create a file import.sh and make it executable Paste following code in file mysqluser='root' mysqlpass='password' mysqldb='database_name' tablename='mysql_table_name' DIRECTORY='input_directory' for csv in $DIRECTORY/*.csv; do echo "Loading $csv" mysql -u $mysqluser --password=$mysqlpass -D $mysqldb -e "LOAD DATA INFILE '$csv' IGNORE INTO TABLE $tablenameFIELDS TERMINATED BY '\t' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES;" done

November 11, 2021 · 1 min · Saqib Razzaq

How to quickly login to MySQL using Bash

I know this method isn’t the most secure way. But when I’m working on personal projects, it gets annoying having to enter credentials every time. Hence, I put together this little script to quickly log me in. I create file mysql.sh with following contents mysql -u username --password='password_here'

November 11, 2021 · 1 min · Saqib Razzaq

MySQL Cheatsheet

copy a table with schema, indexes and data First, copy the table with indexes CREATE TABLE new_name LIKE table_to_copy; Then copy the data by running INSERT INTO new_name SELECT * FROM table_to_copy; create BTREE Indexes CREATE INDEX index_name ON table_name (column1, column2, ...); create FULLTEXT indexes ALTER TABLE `TableName` ADD FULLTEXT INDEX `IndexName` (`ColumnName`); create new user and give all privilages CREATE USER 'user'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON * ....

September 1, 2021 · 2 min · Saqib Razzaq

How to use Mysql CLI in xampp

I love working with Linux and CLI. It helps boost speed to a great extent and it feels like Windows' xampp often limits that because I mostly tend to avoid using command line. I’m finally writing this to change that and be equally as proficient on Windows. MySQL Bin Location {$xampp}/mysql/bin/mysql.exe Mysqldump Bin Location {$xampp}/mysql/bin/mysqldump.exe How to import and export files Import files Login to mysql by running {$xampp}/mysql/bin/mysql....

December 11, 2020 · 1 min · Saqib Razzaq

How to run a command on multiple MySQL tables using PHP

$query = "alter table {table} drop column columnName;"; $tables = array('tableA', 'tableB'); foreach ($tables as $key => $source) { $currentQuery = str_replace('{table}', $source, $query); DB::query($currentQuery); }

November 23, 2020 · 1 min · Saqib Razzaq