Fedora mySql

fix Table 'information_schema.SCHEMATA' doesn't exist

$sudo mysql_upgrade -u root -p --force

Fedora mySQL 8.0

mysql_upgrade -u root -p --force

mySQL ODBC Driver error 5.2

Error 1918. Error installing ODBC driver MySQL ODBC
 5.3 ANSI Driver, ODBC error 13: The setup routines for
 the MySQL ODBC 5.3 ANSI Driver ODBC driver could
 not be loaded due to system error code 126: The

Solution:
MySQL Connector/ODBC requires Microsoft Visual C++ 2010 redistributable so you have to install it first.

Resources:
  [1]  MySQL Connector/ODBC 32-bit version, you have to download and install Microsoft Visual C++ 2010 Redistributable Package (x86).
  [2]  MySQL Connector/ODBC 64-bit version, you have to download and install Microsoft Visual C++ 2010 Redistributable Package (x64).

mySQL try to Optimize

References:

[1] https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html

[1] connection options https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-flags
[2] autocommit problem http://www.mysqlab.net/knowledge/kb/detail/topic/innodb/id/5934
[3] 101 tips https://www.monitis.com/blog/101-tips-to-mysql-tuning-and-optimization/
[4] Create table  https://dev.mysql.com/doc/refman/8.0/en/create-table.html

mySql Optimize date queries and inserts

High Level of use



DROP INDEX VENTAS_fecven_idx ON VENTAS

select * from VENTAS where date(fecven)='2019-03-09' #~2.81

select * from VENTAS where fecven >='2019-03-09 00:00:00' and fecven<'2019-03-10 00:00:00'  #~2.9


select * from VENTAS where fecven Between '2019-03-09 00:00:00' and '2019-03-10 00:00:00'  #~3.6


CREATE INDEX VENTAS_fecven_idx ON VENTAS (fecven)


select * from VENTAS where date(fecven)='2019-03-09' #~2.794

select * from VENTAS where fecven >='2019-03-09 00:00:00' and fecven<'2019-03-10 00:00:00'  #~0.265

select * from VENTAS where fecven Between '2019-03-09 00:00:00' and '2019-03-10 00:00:00'  #~0.261

CREATE INDEX MOVPRO_fecmov_idx ON MOVPRO (fecmov)
#change date(fecmov) between f1 and f2
#to         fecmov between f1 and f2  #implicit add 00:00:00

DROP INDEX MOVPRO_fecmov_idx ON MOVPRO


Setting mysql

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 128;
[mysqld]
bulk_insert_buffer_size=128M

References
[1] Insert https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html
[2] Amazon Best Practices https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_BestPractices.html

mySql Optimization parameters or commands

Query

SHOW GLOBAL STATUS

SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';

Settings

SET GLOBAL join_buffer_size = 1024 * 1024 * 128 #128M

or

[mysqld]
join_buffer_size = 128M  #default ~256K

 

References:

[0] Compute memory http://mysqlcalculator.com/ 

[1] join_buffer_size https://dba.stackexchange.com/questions/74693/how-to-break-table-into-two-without-losing-performance 

[2] https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

[3] Example tunning https://dba.stackexchange.com/questions/127862/tuning-mysql-variables-to-accommodate-high-load

[4] Query variables https://dev.mysql.com/doc/refman/8.0/en/show-variables.html

mySql Common Commands

CREATE TABLE foo LIKE bar;

CREATE TABLE IF NOT EXISTS offices_bk

SELECT * FROM

    offices;

==

CREATE TABLE IF NOT EXISTS new_table LIKE existing_table;

INSERT new_table

SELECT * FROM existing_table;

ALTER TABLE <tablename> CHANGE COLUMN <colname> <colname> VARCHAR(65536);

ALTER TABLE emp MODIFY COLUMN name VARCHAR(100);

Or use CHANGE, but that means you have to give the column name twice (because CHANGE allows you to change the name of the column too).
ALTER TABLE emp CHANGE COLUMN name name VARCHAR(100);

mySql change port problem

Can't start server: Bind on TCP/IP port: Permission denied
Do you already have another mysqld server running on port: 13306 ?


Common solution is change my.cnf adding port=newport, but in what section?

Discover who is the principal executable
$locate mysqld.service

/etc/systemd/system/multi-user.target.wants/mysqld.service
/usr/lib/systemd/system/mysqld.service

$vi /usr/lib/systemd/system/mysqld.service #for watch settings
In my case, was this:
ExecStart=/usr/bin/mysqld_safe --basedir=/usr

then i know what section i need to change

$vi /etc/my.cnf  # add new port in section [mysqld_safe]

Aditional commands during process:

tail -30 /var/log/mysql/error.log

sudo lsof -i TCP:3306
netstat -lp | grep 3306
 

$systemctl status iptables.service

$service iptables status

Ubuntu mySQL Backup/Restore

1)Create


2) Restore


$mysql -u root -p mysql
> create database mydb; mysql
> use mydb; mysql
> source db_backup.dump;


3) Another way, you  need to run:

$mysql -p -u[user] [database] < db_backup.dump

If the dump contains multiple databases you should omit the database name:

$mysql -p -u[user] < db_backup.dump

4) Restore specific database

$mysql -u onepoint -p --one-database maxx2016 < back_20190226.sql

mySQL Develop Optimization

Select * from MOVPRO where fecmov>='2018-11-25' and tipmov='S'
EXPLAIN Select * from MOVPRO where fecmov>='2018-11-25' and tipmov='S'

CREATE INDEX MOVPRO_fecmov_tipmov_index ON MOVPRO (fecmov,tipmov)


References:
[1] faster using indexes https://blog.nodeswat.com/making-slow-queries-fast-with-composite-indexes-in-mysql-eb452a8d6e46

python3 using mysql connection

$pip3 install mysql-connector-python --user

from mysql.connector import (connection)


cnx=connection.MySQLConnection(user='user', password='*******',
             host='ip', database='db')

cnx.close()


Resources:
[1] https://dev.mysql.com/doc/connector-python/en/connector-python-installation.html

mySQL Clients - Ubuntu

Resources:

1. DBeaver(Multi Driver)  http://dbeaver.jkiss.org/download/
2. Valentina Studio(Multi Driver) https://www.valentina-db.com/en/all-downloads

Replicate mySQL Database

References:
[1] http://top-performance.blogspot.com.br/2012/03/how-to-setup-mysql-replication-in-11.html
[2] http://www.rackspace.com/knowledge_center/article/mysql-replication-masterslave

mySQL Store procedures

CREATE DEFINER = 'root'@'%' PROCEDURE `queryf2`(
        IN id varchar(8)
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN 
   START TRANSACTION; 
   SELECT * FROM PRODUCTOS_STOCK ps where ps.IdSucursal=id; 
   COMMIT; 
END;

references:


[1] Stored Procedure that returns random rows from a table
      http://www.it-iss.com/mysql/mysql-stored-procedure-that-returns-random-rows-from-a-table/

mySql Innodb recovery

InnoDB (.ibdata, .ibd and .frm) 
MyISAM (.myd, .myi and .frm) 


[1] https://launchpad.net/percona-data-recovery-tool-for-innodb
[2] http://blog.shiraj.com/2012/10/extract-data-from-mysql-ibdata1-data-file/
[3] http://linuxrnd.blogspot.com/2011/12/mysql-recover-data-using-ibdata1.html
[4] http://getasysadmin.com/2012/08/recover-mysql-innodb-database-from-ibdata1-and-frm/

mySQL Restore tables from files/deleted

http://www.chriscalender.com/?p=49


http://stackoverflow.com/questions/5745221/import-frm-and-opt-files-to-mysql


http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

http://82.157.70.109/mirrorbooks/mysqlguide4.1-5.0/0672326736/ch13lev1sec6.html

Información de host

show variables like 'host%'

mySQL grant

Viualizar Privilegios otorgados
show grants


Grant para habilitar acceso a servidor mySQL

http://stackoverflow.com/questions/2433543/how-to-make-a-remote-connection-to-a-mysql-database-server

mySQL Comandos Fechas

Trabajo con fechas:

mysql> SELECT DATE_ADD('2009-03-20', INTERVAL 31 DAY);
mysql> SELECT DATE_ADD('2009-03-20', INTERVAL 1 MONTH);
mysql> SELECT ADDDATE(CURDATE(), INTERVAL 31 DAY);
mysql> SELECT SUBDATE(CURDATE(), INTERVAL 2 MONTH);

La palabra clave INTERVAL y el especificador type no son sensibles a mayúsculas.

La siguiente tabla muestra cómo se relacionan los argumentos type y expr :

type Value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

Referencias:
http://dev.mysql.com/doc/refman/5.0/es/date-and-time-functions.html

mySql: Comandos de administración

mysql> SHOW FULL PROCESSLIST;
Para listar PCs q se conectan al mySQL Server



mysql> SHOW GRANTS;
Lista de usuarios y privilegios.