Database

From My Wiki
Jump to: navigation, search

MySql reset root password

sudo /etc/init.d/mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;
update user set password=PASSWORD("mynewpassword") where User='root';
flush privileges;
quit

sudo /etc/init.d/mysql stop
...
sudo /etc/init.d/mysql start
mysql -u root -p

MySql Export / Import

mysqldump -u root -p1234567890 my_wiki > dump.sql
mysql -u root -p1234567890 my_wiki < dump.sql

MySql config found at

/etc/mysql/my.cnf

MySql default port

tcp:3306

MS SQL Backup Restore

USE [master]
GO
BACKUP DATABASE [DB01]
    TO DISK = '\\ServerB\Backup\DB01.bak';
GO
USE [master]
GO
RESTORE DATABASE [DB01]
    FROM DISK = 'D:\Backup\DB01.bak';
GO

MS SQL Notes

Installing: Feature Selection -> Select All, Use Windows Authentication. Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication. Can change this afterwards.

Windows Firewall Settings: (http://msdn.microsoft.com/en-us/library/ms175043.aspx)

Start > Run > 'WF.msc'
Inbound Rules > Allow TCP Port 1433

how to create new user?

Microsoft SQL Server Management Studio: 
- (SERVER-NAME)
 - Security
 - Login
 - right click - add login
    - "SQL Server authentication"
    - Securables - just Grant everything
    - Status - Permission:Grant, Login:Enabled

Allow SQL Server Authentication to be used

Microsoft SQL Server Management Studio: 
- (SERVER-NAME)
 - right click - properties
 - security - change server authentication

Using MSSQL from C# Application (worked example)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication2
{
    class Program
    {
        static string connectionString =
            "Data Source=88.198.54.214;User ID=bob;Password=robert45smith";
            //static string connectionString = "Data Source=OWNEROR-7G7BEUA;Initial Catalog=redJOHN;Integrated Security=True";
        static void Main(string[] args)
        {
            SqlConnection myConnection = new SqlConnection(connectionString);
            try { myConnection.Open(); }
            catch (Exception e) { Console.WriteLine(e.ToString()); }

            string sql = "if db_id('geo') is not null DROP DATABASE geo";

            SqlCommand sc = new SqlCommand(sql, myConnection);
            try { sc.ExecuteNonQuery(); }
            catch (Exception e) { Console.WriteLine(e.ToString()); }

            sql = "CREATE DATABASE geo";
            sc = new SqlCommand(sql, myConnection);
            try { sc.ExecuteNonQuery(); }
            catch (Exception e) { Console.WriteLine(e.ToString()); }

            sql = @"CREATE TABLE [geo].[dbo].[Persons]
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);";
            sc = new SqlCommand(sql, myConnection);
            try { sc.ExecuteNonQuery(); }
            catch (Exception e) { Console.WriteLine(e.ToString()); }

            sql = @"INSERT INTO [geo].[dbo].[Persons]([PersonID],[LastName],[FirstName],[Address],[City]) 
VALUES(@PersonID, @LastName, @FirstName, @Address, @City)";
            string[] lastname = { "Lee", "Huang", "Chan", "Zhang" };
            string[] firstname = { "Adam", "Bob", "Charlie", "David" };
            string[] address = { "123 fake street", "45 overview rd", "10 johnson ave", "100 alpha drive" };
            string[] city = { "nyc", "auckland", "tokyo", "beijing" };

            for (int i = 0; i < 4; i++)
            {
                sc = new SqlCommand(sql, myConnection);
                SqlParameter[] p = new SqlParameter[5];
                p[0] = new SqlParameter("@PersonID", SqlDbType.Int);
                p[0].Value = (i + 1);
                sc.Parameters.Add(p[0]);
                p[1] = new SqlParameter("@LastName", SqlDbType.VarChar);
                p[1].Value = lastname[i];
                sc.Parameters.Add(p[1]);
                p[2] = new SqlParameter("@FirstName", SqlDbType.VarChar);
                p[2].Value = firstname[i];
                sc.Parameters.Add(p[2]);
                p[3] = new SqlParameter("@Address", SqlDbType.VarChar);
                p[3].Value = address[i];
                sc.Parameters.Add(p[3]);
                p[4] = new SqlParameter("@City", SqlDbType.VarChar);
                p[4].Value = city[i];
                sc.Parameters.Add(p[4]);
                sc.ExecuteNonQuery();
            }

            sql = "SELECT * FROM [geo].[dbo].[Persons]";
            sc = new SqlCommand(sql, myConnection);
            try
            {
                SqlDataReader dr = sc.ExecuteReader();
                while (dr.Read())
                {
                    Console.Write(dr[0] + ") ");
                    Console.Write(dr["LastName"] + " ");
                    Console.Write(dr["FirstName"] + " ");
                    Console.Write(dr["Address"] + " ");
                    Console.WriteLine(dr["City"] + " ");
                }
                dr.Close();
            }
            catch (Exception e) { Console.WriteLine(e.ToString()); }

            sc = new SqlCommand(sql, myConnection);

            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = sc;
            DataSet ds = new DataSet();
            sda.Fill(ds);

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Console.WriteLine();
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Console.Write(row[col] + " ");
                }
            }

            try { myConnection.Close(); }
            catch (Exception e) { Console.WriteLine(e.ToString()); }

            Console.Read();
        }
    }
}

Using MSSQL from Java Application (worked example)

This uses jtds http://sourceforge.net/projects/jtds/files/jtds/1.3.1/

Put jtds-1.3.1.jar into class path. e.g. javac main.java && java -cp jtds-1.3.1.jar: main

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;

public class main {
	public static void main(String[] args){
		Connection conn;
		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			//conn = DriverManager.getConnection("jdbc:sqlserver://88.198.54.214;Database=redJOHN", "sa", "test1234post!");
			conn = DriverManager.getConnection("jdbc:jtds:sqlserver://88.198.54.214", "bob", "robert45smith");
		} catch (ClassNotFoundException | SQLException e) {
			System.out.println(e);
			return;
		}
		System.out.println("Hello World");

		String sql = "if db_id('geo') is not null DROP DATABASE geo";
		try{
			PreparedStatement prest = conn.prepareStatement(sql);
			prest.executeUpdate();
		}catch(SQLException e){
			System.out.println(e.toString());
		}

		sql = "CREATE DATABASE geo";
		try{
			PreparedStatement prest = conn.prepareStatement(sql);
			prest.executeUpdate();
		}catch(SQLException e){
			System.out.println(e.toString());
		}
		sql = "CREATE TABLE [geo].[dbo].[Persons](PersonID int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255));";

		try{
			PreparedStatement prest = conn.prepareStatement(sql);
			prest.executeUpdate();
		}catch(SQLException e){
			System.out.println(e.toString());
		}

		sql = "INSERT INTO [geo].[dbo].[Persons]([PersonID],[LastName],[FirstName],[Address],[City]) VALUES(?,?,?,?,?)";
		String[] lastname = new String []{ "Lee", "Huang", "Chan", "Zhang" };
		String[] firstname = new String []{ "Adam", "Bob", "Charlie", "David" };
		String[] address = new String []{ "123 fake street", "45 overview rd", "10 johnson ave", "100 alpha drive" };
		String[] city = new String []{ "nyc", "auckland", "tokyo", "beijing" };

		for(int i = 0; i<4; i++){
			try{
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, i+1);
				ps.setString(2, lastname[i]);
				ps.setString(3, firstname[i]);
				ps.setString(4, address[i]);
				ps.setString(5, city[i]);
				ps.executeUpdate();
			}catch(SQLException e){System.out.println(e.toString());}
		}

		try (PreparedStatement ps = conn.prepareStatement("select [PersonID],[LastName],[FirstName],[Address],[City] from [geo].[dbo].[Persons]"); ResultSet rs = ps.executeQuery()) {
			while (rs.next()) {
				int PersonID = rs.getInt(1);
				String LastName = rs.getString("LastName");
				String FirstName = rs.getString("FirstName");
				String Address = rs.getString("Address");
				String City = rs.getString("City");
				System.out.println(PersonID + " " + LastName + " " + FirstName + " " + Address + " " + City);
			}
		}
		catch(SQLException e){System.out.println(e.toString());}
	}
}

Using MSSQL from Python Application (Windows and Ubuntu Linux / DSN and DSN-less) (worked example)

A database is an organized collection of data.

Database management systems (DBMSs) are computer software applications designed to manage a database, a large set of structured data, and run operations on the data requested by numerous users. Examples are Microsoft SQL Server (used here) and MySQL.

Databases are queried using Structured Query Language (SQL) statements. All queries follow the same structure (SELECT A FROM B WHERE C=D) but different DBMS (eg MySQL, MSSQL) may extend SQL differently. Transact-SQL (T-SQL) is Microsoft's proprietary extention to SQL. Microsoft SQL Server is used by sending T-SQL statements to it.

Open Database Connectivity (ODBC) is an API for accessing database management systems (DBMS). This is developed by Microsoft and is included in Windows (odbc32.dll : http://support.microsoft.com/kb/110093). Free and Open Source implementations of this do exist (eg unixODBC - libodbc.so : http://www.unixodbc.org)

Rarely does a software application access a specific DBMS directly. Rather, they go through an ODBC. The software application just needs to implement ODBC correctly. There are libraries available for various programming languages that makes it easier for a programmer to do this. (eg python pyodbc https://code.google.com/p/pyodbc/)

A software application implements ODBC. From there ODBC can connect to different Data Sources. A Data Source is normally a DBMS. But it can also be a CSV, Microsoft Excel or Microsoft Access file. An ODBC driver enables an ODBC-compliant application to use a Data Source. Drivers are specific to each DBMS eg MySQL or MSSQL. Drivers are libraries that implement the functions in the ODBC API.

An ODBC Driver Manager (DM) is a library that manages communication between the ODBC-aware application and any drivers. It loads and unloads the drivers. And it also resolves Data Source Names (DSN).

Data Source Names (DSN) collect additional information needed to connect to a particular data source, as opposed to the DBMS itself. For instance, the same MySQL driver can be used to connect to any MySQL server. But the information needed to connect to a local private database server would be different from the information needed to connect to an internet-hosted public database server. Details are hidden and accesible by a name.


I will now provide a worked example of a python application running in Ubuntu Linux connecting to a Microsoft SQL Server.

  • Assume MSSQL database has been setup. Can connect to it remotely i.e. you have
  • a known ip/port and firewall set to allow connections through
  • a known user/pass and SQL server configured to allow this type of authentication

First we get connected...

Ubuntu Linux - FreeTDS

First step is to ensure Ubuntu Linux can connect to the Microsoft SQL Server.

Tabular Data Stream (TDS) is the protocol used for the communications between a Microsoft SQL Server and a client. A free and open source implementation of this protocol is called FreeTDS. This implementation is included in the tdsodbc package. This package can later be used with unixODBC.

sudo apt-get update; apt-get -y upgrade
sudo apt-get -y install freetds-dev freetds-bin tdsodbc

Then try to connect to the Microsoft SQL Server by doing:

tsql -H <hostname or IP> -p <port> -U <username> -P<password>

Note that there is no space between -P and password value because the program has no way to tell whether a following argument is supposed to be the password value or some other kind of argument.

Also note, passing in the password as an argument like this means the password is available to anyone who can do a ps aux on your running processes (because it displays the arguments) or anyone that can look through your ~/.bash_history

To avoid this happening do this instead:

tsql -H <hostname or IP> -p <port> -U <username>

and enter your password at the prompt.

Once connected, you'll want to run a basic command like:

select @@version
go
use DBname;
go
SELECT Distinct TABLE_NAME FROM information_schema.TABLES;
go

freetds.conf

Just as DNS defines hostnames for network addresses, freetds.conf uses a servername to define the properties of your server. Default location of this file can be found by doing:

tsql -C 

Properties of a server consists of:

  • Hostname or IP address of the server
  • Port number or Instance name (not both)
  • TDS protocol version

Append your server details to the file like so:

[sqlserver]
    host = 10.10.10.10
    port = 1433
    tds version = 7.0

Then you can connect to the SQL Server by doing:

tsql -S sqlserver -U <username> -P<password>

unixODBC

Now that you're sure that the OS can connect to the SQL Server, it's time to allow software applications to do the same. This is done through unixODBC.

Install:

sudo apt-get -y install unixodbc unixodbc-dev

Then run this to get location of configuration files:

odbcinst -j

Example:

unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

unixODBC - Drivers

Going by example, The system file odbcinst.ini contains information about ODBC drivers available to all users.

Edit this to point to the FreeTDS libraries installed previously. Name given to this Driver is 'SQL Server'.

[SQL Server]
Description = TDS driver (Sybase/MS SQL)
# Some installations may differ in the paths (32bit v 64bit)
#Driver = /usr/lib/odbc/libtdsodbc.so #32bit, or /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so ???
#Setup = /usr/lib/odbc/libtdsS.so #32bit, or /usr/lib/i386-linux-gnu/odbc/libtdsS.so ???
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

Now its time to connect an application to it!

Python - DSN-less

We'll use Python to write our application here.

sudo apt-get -y install python python-pyodbc

Here is a 'Hello World' type application in python

import pyodbc

print "hello world"

user = 'test'
password = 'test123'
database = 'testdb'

con_string = 'DRIVER={SQL Server};SERVER=10.10.10.10;PORT=1433;UID=%s;PWD=%s;DATABASE=%s;' % (user, password, database)
cnxn = pyodbc.connect(con_string)

cursor = cnxn.cursor()

cursor.execute("select @@version as p")
while 1:
    row = cursor.fetchone()
    if not row:
        break
    print row.p
	
cnxn.commit()

cursor.execute("if db_id('geo') is not null DROP DATABASE geo")
cnxn.commit()

cursor.execute("CREATE DATABASE geo")
cnxn.commit()

cursor.execute("""CREATE TABLE [geo].[dbo].[Persons]
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);""")
cnxn.commit()

lastname = ["Lee","Huang","Chan","Zhang"]
firstname = ["Adam", "Bob", "Charlie", "David"]
address = ["123 fake street", "45 overview rd", "10 johnson ave", "100 alpha drive"]
city = ["nyc", "auckland", "tokyo", "beijing"]

for i in range(0,4):
	cursor.execute("""INSERT INTO [geo].[dbo].[Persons]([PersonID],[LastName],[FirstName],[Address],[City]) 
	VALUES(?,?,?,?,?)""", i+1, lastname[i], firstname[i], address[i], city[i])

cursor.execute("select * FROM [geo].[dbo].[Persons]")
#cursor.execute("select * FROM [geo].[dbo].[Persons] where LastName = %s",("Huang"))
while 1:
    row = cursor.fetchone()
    if not row:
        break
    print "%d: %s %s %s %s"%(row.PersonID,row.LastName,row.FirstName,row.Address,row.City)
	
cnxn.commit()

Note here that in the connection string the Driver, Server, UserId, and Database are all provided by the application. This makes this a DSN-less connection.

DSN is a central store of settings for a connection that called from an application by name.


Instead of doing fetchone() can do fetchall() like so

    rows = cursor.fetchall()
    for row in rows:
        print row.line_id, row.user_id_str
        user_id_str = str(row.user_id_str).strip()
        line_id = str(row.line_id).strip()
        process(user_id_str, line_id)

unixODBC - Data Sources

Going back to the unixODBC configuration before, one location where Data Sources is stored is /root/.odbc.ini so lets edit that file like so.

[sqlserverdatasource]
Driver = SQL Server
Description = ODBC connection via FreeTDS / SQL Server
Trace = No
Servername = sqlserver
Database = testdb
  • Data Source Name (DSN) here is 'sqlserverdatasource'.
  • Driver name is 'SQL Server' as per /etc/odbcinst.ini
  • Servername is 'sqlserver' as per freetds.conf

try connection from command line (remember to bash escape special chars in password)

isql sqlserverdatasource <username> <password>
SQL>select @@version
SQL>SELECT Distinct TABLE_NAME FROM information_schema.TABLES;

Python - DSN

in the python application now, can use

dsn = 'sqlserverdatasource'
user = 'test'
password = 'test123'

con_string = 'DSN=%s;UID=%s;PWD=%s;' % (dsn, user, password)
cnxn = pyodbc.connect(con_string)

cursor = cnxn.cursor()

Python errors

pyodbc.Error: ('HY000', 'The driver did not supply an error!')

Fix: use

cxnn=pyodbc.connect('DSN=SERVER;UID=sa;PWD=password', autocommit=True)

instead.

pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (4004) (SQLExecDirectW)')

fix: use

TDS_VERSION=8.0 

in connection string

Windows - Install Python / pyodbc

now do this in windows! -

Install Python - https://www.python.org/downloads/

remember, add python to path C:\Python27 to system PATH (Control Panel\System and Security\System; Advanced System Settings > Advanced > Enviromental Variables) (there should be an option to do this during install)

Install pyodbc - https://code.google.com/p/pyodbc/downloads/list

Same python code should work when Drivers (and if using, DSN) setup.

Windows - ODBC - Drivers

Control Panel > Administrative Tools > Data Sources (ODBC) > Drivers 

"SQL Server" should be in list by default

Windows - ODBC - Data Sources (DSN)

Control Panel > Administrative Tools > Data Sources (ODBC) > User DSN 
Add > "SQL Server"
Name = sqlserverdatasource
Server = 10.10.10.10
Next
Use SQL Server authentication
Untick to not enter detail settings for User ID and Password (do it in application instead)
Next
Next
Finish
Ok

Using MySQL from Python application (Linux)

sudo apt-get install python-mysqldb

Running MySQL off a ram drive (Ubuntu)

sudo mkdir /var/ramfs
sudo mount -t ramfs -o size=100G ramfs /var/ramfs/

sudo /etc/init.d/mysql stop
sudo cp -R /var/lib/mysql /var/ramfs/
sudo chown -R mysql:mysql /var/ramfs/mysql

sudo cp /etc/mysql/my.cnf /etc/mysql/original-my.cnf
sudo vim /etc/mysql/my.cnf

set:
datadir = /var/ramfs/mysql

sudo vim /etc/apparmor.d/usr.sbin.mysqld
OR sudo vim /etc/apparmor.d/local/usr.sbin.mysqld ?

set:
# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
/var/tmpfs/mysql/ r,
/var/tmpfs/mysql/*.pid rw,
/var/tmpfs/mysql/** rwk,


sudo /etc/init.d/apparmor restart
sudo /etc/init.d/mysql start

source: http://icephoenix.us/stuff/how-to-move-mysql-storage-to-ramfs-or-tmpfs-partition/

MySQL big performance thingy

my config when i wanted to do lots of data with it

root@ns501337:/etc/mysql# cat my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
datadir = /var/tmpfs/mysql
connect_timeout = 28800
net_read_timeout = 28800
slave_net_timeout = 28800
max_allowed_packet = 1G
innodb_buffer_pool_size = 1G

MongoDB Notes

Install:

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10
echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list
sudo apt-get update
sudo apt-get install -y mongodb-org
logs: /var/log/mongodb/mongod.log
config: /etc/mongod.conf
service mongod start/stop/restart
shell: mongo (no auth needed from localhost)

Basic commands:

db # show current db
show dbs
use mydb
show collections
db.testData.find()

db.g.find({com: /chi/},{com:1})
is equiv to: 
SELECT com FROM ...
WHERE com lIKE '%chi%'

More complex commands:

this command to create it, with a new index as well (collection called twitter, expect an 'id' col) 

db.twitter.createIndex( { "id": 1 }, { unique: true } )

Authentication

use admin
db.createUser(
   {
     user: "alaning",
     pwd: "alaning01",
     roles:
       [
         { role: "readWrite", db: "junkyard" }
		]
   }
)

Edit config file: 
auth=true # add to force authentication
bind_ip   # comment this so it will listen on any ip

now login like this

mongo -u alaning -p alaning01 --authenticationDatabase admin

verbose

mongo --host localhost --port 27017 -u alaning -p alaning01 --authenticationDatabase admin

Using MongoDB from Java Application (worked example)

See here for worked example: http://git.alaning.me/root/4chanjsonintomongodb/

another example: http://git.alaning.me/root/Mongodb2Mysql