Categories
MySql SQL SqLite

How to SQLite ? beginner

Download page

Download Page SQLITE

Basic Commands

GUI SQLITE

–> execute sqlite excutable from downladpage

sqlite

Import CSV data to SQlite

.mode csv
.import csvfilename databaseName

Quit SQlite

.quit

Categories
MySql SQL

How to resolve ERROR 1148 (42000): The used command is not allowed with this MySQL version ?

Solution

mysql> SHOW VARIABLES LIKE 'local_infile';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| local_infile  | OFF   |

+---------------+-------+

1 row in set (0.21 sec)
mysql> SET GLOBAL local_infile = 1;

Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'local_infile';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| local_infile  | ON    |

+---------------+-------+

1 row in set (0.00 sec)
mysql> load data local infile '/Users/davidr/Desktop/dob.csv' INTO TABLE dob FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
Query OK, 109093 rows affected (1.88 sec)
Records: 109093 Deleted: 0 Skipped: 0 Warnings: 0
Categories
MySql SQL Unix

Import CSV data to mysql

Fastest way to import data over time found to be csv format data

mysql> load data local infile 'Desktop/data.csv'
-> INTO TABLE cars
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> IGNORE 1 ROWS;
Query OK, 232880 rows affected, 65535 warnings (27.87 sec)
Records: 232880 Deleted: 0 Skipped: 0 Warnings: 232880

Categories
Database SQL

SQL HELPER

RDS AUTHENTICATION

mysql -h xxxxxxxxxta.us-east-1.rds.amazonaws.com  -u admin -p 

SELECT User,Host FROM mysql.user;
SHOW GRANTS FOR 'drupal'@'%';

SQL Client Recommendation

TablePlus(Mac)

DBeaver

Mysql Workbench

SequelPro

Import database

#1 mysql h xxxxx.com -u username -p dbname < dbexport.sql

OR 

#1 mysql h xxxxx.com -u username -p 
#2 use database-name;
#3 source file.sql

Export all Databases

    sudo mysqldump --all-databases -udxxxx -p > oct-16-2019-database.sql

Export Specific Database

mysqldump --databases xxxxxx-name -udxxxx -p > oct-16-2019-database-david-raleche.sql

NOT A ROOT USER

mysqldump –all-databases> database.sql

mysqldump -all-databases -uuser -ppassword> database.sql

mysqldump –all-databases –skip-lock-tables -u user-ppassword  > Sept2018database.sql

Long Queries

Explain 

SQL TABLE SIZE

select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;

CRON NACKUP DATABSECron Backup Database

mysqldump -uroot -p MyDatabase >/home/users/backup_MyDB/$(date +%F)_full_myDB.sql
Categories
General SQL

mysql 8

Create new User

– Full privileges