Quick MySql Commands and Examples

Here is a list of my most frecently used MySql Commands and examples

Login to MySQL console

mysql -u root -pmypassword

mysql -h[your.ip.to.mysqlhost] -u root -pmypassword

Send SQL statement without login to the DataBase

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

Create/ Drop the DataBase

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

Export/Import a DataBase

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

Export/Import a DataBase Table

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

Information Squema

select * from information_schema.columns
where table_schema = 'your_db'
order by table_name, ordinal_position;

Give permissions to the DataBase

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';

Create linked tables with a Foreign key

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)
    );

Check if the database is listening for new connections.

•  Linux example change port accordingly: sudo netstat -anltp|grep :3306 or sudo nmap -p 3306 localhost
• Windows your database port should be among the ones listed: netstat -an

* The port should be listening if not the problems is that the database installation is wrong or is blocked by a firewall.

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";//change accordingly
            String url = "jdbc:mysql://localhost:3306";//change accordingly
            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("Wating for next iteration...");
                Thread.sleep(PAUSE_MILLIS);                
            }            
        } catch (Exception e) {
            System.err.println("Cannot connect to database server");
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    System.out.println("Database Connection Terminated");
                } catch (Exception e) {
                }
            }
        }
    }
}

* Change the URL, PORT, PASSWORD, PAUSE_MILLIS accordingly

Telnet Data Base

Using telnet can be very use full to find out if you can reach the database. ex. in console type telnet youdatabaseip port. or  telnet youdatabaseip:port if you are using windows. If telnet is not installed in your windows google windows install telnet. search install telnet

Press Intro twice if you are getting this means that your database can receive connections.

Try different combinations such us.

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

* Change accordingly to your local network ip, external IP and database port.

* With this you can determine whether it is a network-related problem or not.

MySql Configuration files

• Linux MySQL ex: /etc/my.cnf
• Windows ex: MySQL: C:\Program Files\mysql\bin\my.ini
• Comment out or Change "bind-address" attribute and change it to Ips: bind-address="127.0.0.1" or bind-address="0.0.0.0"
• Comment out "skip-networking"

* If you are getting the exception every now than then perhaps adding the following properties will help you.

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

 MySQL TimeOut 

• Log to your MySQL Console Linux ex. mysql -u root -pmypassword
• From the MySQL Console show the time variables show global variables like '%time%' 

* The wait time out is in seconds and will close the the opened connections. You can update in the configuration files #configfiles or ex. for ten minutes  set global wait_timeout=600;

Show active connections

• In MySQL console show full processlist; or show full processlist\G; or SELECT host,count(host) FROM information_schema.processlist GROUP BY host;

* The amount of open connections and traffic can shutdown the database.

• Info shows the MySQL command being run
• use kill id_process to remove processes.

Pool Tomcat examples

Pool with validation query, 2 maxIdle connections

        <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 with no idle connections that removes all the unclosed connections

        <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 For Testing Purposes Only.

<%@ 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();
}
%>