MySQL

Security

Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server http://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server

Just perform the following steps: 1) Connect to mysql

mysql -uroot -p

2) Create user

CREATE USER 'user'@'%' IDENTIFIED BY 'password';

3) Grant permissions

GRANT ALL PRIVILEGES ON \*.\* TO 'user'@'%' WITH GRANT OPTION;

4) Flush priviledges

FLUSH PRIVILEGES;

Connect from outside localhost

# For maria-db
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf 
# For mysql
sudo vim /etc/my.cnf

[mysqld]
bind-address		= 0.0.0.0

Importing from csv

# For maria-db
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf 
# For mysql
sudo vim /etc/my.cnf

[mysqld]
secure-file-priv=/mysqlfiles

sudo mkdir /mysqlfiles
sudo chown -R mysql:mysql  /mysqlfiles/

sudo systemctl restart mysql.service 
sudo systemctl restart mariadb.service 

code snipps

clear and update tables from csv

A nice "easy" script to clear and update tables from csv.

TRUNCATE TABLE lookups.cbot_months;
LOAD DATA LOCAL INFILE 'C:/Data/MySQL/Lookups/cBOT_months.csv'
       INTO TABLE lookups.cbot_months
       FIELDS TERMINATED BY ',' ENCLOSED BY '"' lines terminated by '\r\n'
IGNORE 1 LINES;

Select rows from a table that are not in another

You need to do the subselect based on a column name. For example, if you had an id field common to both tables, you could do:

SELECT * FROM Table1 WHERE id NOT IN (SELECT id FROM Table2)

Using AND, OR, and NOT together

You can combine the three logical operators in a compound condition. Your DBMS uses SQL’s precedence rules to determine which operators to evaluate first. Precedence is covered in “Determining the Order of Evaluation” in Chapter 5, but for now you need know only that when you use multiple logical operators in a compound condition, NOT is evaluated first, then AND, and finally OR. You can override this order with parentheses: Everything in parentheses is evaluated first. When parenthesized conditions are nested, the innermost condition is evaluated first. Under the default precedence rules, the condition x AND NOT y OR z is equivalent to (x AND (NOT y)) OR z. It’s wise to use parentheses, rather than rely on the default evaluation order, to make the evaluation order clear.

MySQL count occurrences greater than 2, find duplicate lines/entries

SELECT count(DOC_NUMBER), DOC_NUMBER as count 
FROM jde_dw.ap_invoice_list
GROUP BY DOC_NUMBER
HAVING count >= 2;

And now how to delete them!

CREATE TABLE web_apps.localpnl_exposure_history AS SELECT * FROM web_apps.localpnl_exposure_history_back LIMIT 0;
SELECT * FROM web_apps.localpnl_exposure_history_back;
SELECT * FROM web_apps.localpnl_exposure_history;
ALTER TABLE web_apps.localpnl_exposure_history_back ADD PRIMARY KEY (JDCS,Exposure_Date(20));

create a new temporary table with same structure

An alternative way would be to create a new temporary table with same structure.

CREATE TABLE temp_table AS SELECT * FROM original_table LIMIT 0

Then create the primary key in the table.

ALTER TABLE temp_table ADD PRIMARY KEY (primary-key-field)

Finally copy all records from the original table while ignoring the duplicate records.

INSERT IGNORE INTO temp_table AS SELECT * FROM original_table

Now you can delete the original table and rename the new table.

DROP TABLE original_table
RENAME TABLE temp_table TO original_table

CAST a string into a number while removing/replacing commas

SELECT SUM(replace( ifnull( Sell_Qty, 0 ), ',',  )) FROM mbs_dw.futures_cbot;

mysqldump

backup

mysqldump -u [uname] -p[pass] db_name > db_backup.sql
mysqldump  db_name > db_backup.sql

restore

C:\> mysql -u root -p
mysql> create database mydb;
mysql> use mydb;
mysql> source db_backup.dump;