Ejemplos rápidos de MySQL

Aquí esta una lista de los comandos de MySQL que más utilizo.

Logarse a la consola MySQL

mysql -u root -pmypassword

Enviar comando SQL sin logarse

mysql -u root -pmypassword mydb -e "select now()"

Base de datos Create/ Drop

mysqladmin -u root -pmypassword drop mydb
mysqladmin -u root -pmypassword create mydb

Exportar/Importar la base de datos

mysqldump -u root -pmypassword --quick mydb | gzip > myzipfile.gz
gunzip < myzipfile.gz | mysql -u root -pmypassword mydb

usar si da error de Lock:

Dar permisos a la base de datos

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' WITH GRANT OPTION;
grant all privileges on mydb.* to myuser@'%' identified by 'mypasswd';
grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd';

Crear tablas enlazadas con un Foreign Key con clave numerica y autoincremento

CREATE TABLE CATEGORIES(
    id INT NOT NULL AUTO_INCREMENT,
    category VARCHAR(100) NOT NULL,
        PRIMARY KEY (id)
    );
    
CREATE TABLE ARTICLE(
    id INT NOT NULL AUTO_INCREMENT,
    lang VARCHAR(2) NOT NULL,
    url VARCHAR(100) NOT NULL,
    meta_keywords VARCHAR(100) NOT NULL,
    title VARCHAR(120) NOT NULL,
    meta_description VARCHAR(100) NOT NULL,
    intro VARCHAR(500) NOT NULL,
    status VARCHAR(1) NOT NULL,
    last_update date NOT NULL,
    id_categories INT NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY (id_categories) REFERENCES CATEGORIES(id)
    );

Comprueba si la base de datos está escuchando

• Linux cambia el puerto por el tuyo si lo necesitas: sudo netstat -anltp|grep :3306 or sudo nmap -p 3306 localhost
• Windows el puerto de tu base de datos debería estar entre las listadas con este comando: netstat -an

* El puerto debería estar escuchando, si no lo está puede que haya un firewall o que la instalación de la base de datos este mal.

Test Code Snippet

import java.sql.*;
public class Connect {
    private static final long PAUSE_MILLIS = 70000;
    public static void main(String[] args) {
        Connection conn = null;
        try {
            String userName = "root";
            String password = "mypassword";//cambia por el tuyo
            String url = "jdbc:mysql://localhost:3306";//cambia por el tuyo
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            PreparedStatement ps1;
            System.out.println("Database connection established");            
            while(true){            
                try {
                    conn.setAutoCommit(true);
                    ps1 = conn
                            .prepareStatement("SELECT CURRENT_TIMESTAMP() as Date");
                    ResultSet rs = ps1.executeQuery();
                    while (rs.next()) {
                        System.out.println(rs.getString("Date"));
                    }
                    rs.close();
                    ps1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                System.out.println("Espera al siguiente ciclo...");
                Thread.sleep(PAUSE_MILLIS);                
            }            
        } catch (Exception e) {
            System.err.println("No puedo conectar");
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    System.out.println("Se termina la conexión");
                } catch (Exception e) {
                }
            }
        }
    }
}

* Cambia el PUERTO, PASSWORD, PAUSE_MILLIS según lo vayas necesitando para encontrar la causa de la excepción.

Telnet a MySQL

Usar el telnet puede ser muy útil para averiguar si puedes llegar a la base de datos ej. en consola escribe telnet youdatabaseip port. o  telnet youdatabaseip:port Si estás usando windows y si el telnet no está instalado busca. search install telnet

Si ves este mensaje es que estás llegando correctamente. Pulsa Intro 2 veces para salir.

Prueba diferentes combinaciones como por ejemplo:

• telnet localhost 3306
• telnet 127.0.0.1 3306
• telnet 192.168.1.10 3306
• telnet 82.16.23.22 3306

* Prueba diferentes combinaciones, si alguna falla puede darte una pista de que el problema este relacionado con la conectividad en la red.

MySql Archivos de configuración

• Linux MySQL ej: /etc/my.cnf
• Windows ej: MySQL: C:\Program Files\mysql\bin\my.ini
• Comenta o cambia "bind-address" y cambia la IP a: bind-address="127.0.0.1" o bind-address="0.0.0.0"
• Comenta "skip-networking"

* Si el problema es ocasional quizá debas establecer las siguientes propiedades.

• wait_timeout = number
• interactive_timeout = number
• connect_timeout = number

 MySQL TimeOut 

• Entra en consola MySQL ej. mysql -u root -pmypassword
• Desde la consola muestra todas las variables relacionadas con el tiempo show global variables like '%time%' 

* El wait_timeout esta en segundos y cerrará todas las conexiones abiertas. Puedes actualizar tus archivos de configuración #configfiles or  restablecerlo desde consola ej. durante 10 minutos set global wait_timeout=600;

Mostrar conexiones activas

• En la Consla MySQL show full processlist; o show full processlist\G; o SELECT host,count(host) FROM information_schema.processlist GROUP BY host;

* Muestra la cantidad de conexiones activas en la base de datos.

• Info muestra el comando de MySQL en ejecución, NULL indica que esta abierta la conexión pero que no hay ningun comando ejecutándose.
• Usa el comando kill id_process para borrar procesos.

Ejemplos de POOL con Tomcat

Pool con query de validación, máximo 2 conexiones en IDLE (latentes)

        <Resource name="jdbc/mysql" auth="Container" type="javax.sql.DataSource"
        driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/blog"
        username="root" password="mypasswd" maxActive="10" maxIdle="2" validationQuery="SELECT 1"/>

Pool que no tiene conexiónes en IDLE y que las cierra trás 45 segundos de duración.

        <Resource name="jdbc/mysql" auth="Container" type="javax.sql.DataSource"
        driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/blog"
        username="root" password="mypasswd" maxActive="10" maxIdle="2" maxWait="45" 
        maxAge="45" removeAbandoned="true" removeAbandonedTimeout="45"/>

JSP Para pruebas. (No se debería usar como ejemplo puesto que una JSP no es el lugar para acceder a base de datos)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.NamingException" %>
<%@ page import="javax.sql.DataSource" %>    
<%
Context context = new InitialContext();
DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/mysql");
if(ds!=null){    
    Connection con = ds.getConnection();
    con.setAutoCommit(true);
    PreparedStatement ps1 = con
            .prepareStatement("SELECT CURRENT_TIMESTAMP() as Date");
    ResultSet rs = ps1.executeQuery();
    while (rs.next()) {
        out.println("Date From DataBase " +rs.getString("Date"));
    }
    rs.close();
    ps1.close();
    con.close();
}
%>