Developping linux mysql application under ubuntu

apt-get this:

apt-get install libmysqlcppconn5 libmysqlcppconn-dev libmysqlclient-dev -y

Note here that you need to check the version of libmysqlcppconn. It might be higher then 5.

Include this library in your linker code:libmysqlcppconn.so.

To create a connection:

  1. create a driver
  2. call the drivers connect method (it might throw an error, so use a try catch block).

includes and name spaces:

#include<mysql_connection.h>
#include<mysql_driver.h>
#include<cppconn/statement.h>
using namespace sql::mysql;

You don’t have to use name spaces obviously, but this will usually make things easier.

create the driver:MySQL_Driver * p_driver = sql::mysql::get_driver_instance();
Then you can use it to connect with the connect method. Sample connection strings can be found here: http://dev.mysql.com/doc/refman/5.1/en/connector-cpp-examples-query.html.

creating the statement object:

p_stmt=p_connection->createStatement(); The important thing here is to remember to include the right headers. many of them are under cppconn, including the statement (<cppconn/statement.h>) and RecordSet(<cppconn/resultset.h>).

using the statement object:

ResultSet * set = p_stmt->executeQuery(“select username from users_tbl”);

The statement has many more options to look at. also, you should be looking at the prepare statement object(cppconn/prepared_statement.h).

Getting values from the record set

The first value from the record set can be fetched with the first() method.

To get the rest of the data use the next() method. Each of these return boolean that state the result.

To get a value from Result set use one of the getXXXXX(index column). methods. Note that the column index starts with 1.

 

JDBC java and mysql

first you must include the sql package:

import java.sql.*;

In order to word with JDBC driver for mysql, you first need to load the driver:

String driver = “com.mysql.jdbc.Driver”;
Class.forName(driver).newInstance();

Next there are minimum of item you must declare:

Connection conn = null;
Statement st = null;

Now, lets connect:

String url = “jdbc:mysql://” + server_name + “:3306/”;

try
        {
            conn = DriverManager.getConnection(url + db_name, username, password);
        } catch (SQLException ex)
        {
            Logger.getLogger(MySQLtest.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("fail to open mysql connection");
            return;
        }

where password, username, server_name and db_name are all strings you get from user input/file and so on.

next you need to create a statement:

try
        {

            st = conn.createStatement();
        } catch (SQLException ex)
        {
            Logger.getLogger(MySQLtest.class.getName()).log(Level.SEVERE, null, ex);
            return;
        }

to view results or record sets, you can call the Execute XXX method, where XXX stands for the specific flavour you need (create, update, count or select).

more info on the statement class here.

 

 

Backup and restore mysql server

backup command:

mysqldump -h localhost -u username -p database_name > backup_db.sql

restore:

mysql [--verbose] --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL

 

The [–verbose] option will out all the info on the screen. it is NOT recommended!

MySQL common commands

Some mySQL commands you want to keep under you hand.

Connecting to a mySQL server:
mysql -h host_address -u user -p

Viewing all the databases:
show databases;

create a database:

create database my_db;

Select a database to use:
use mydatabase;

show db tables (db must be selected):
show tables;

describe the table content:
describe new_table;

Selecting all the users in the system:

select host, user, password from mysql.user;

Creating a user:
CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';

where you can omit theIDENTIFIED BY if password is not required (but it usually is).

Deleting a user:
DROP USER user;

Assigning a password:

UPDATE mysql.user SET Password=PASSWORD('newpass')
  WHERE User='bob' AND Host='%.loc.gov';
FLUSH PRIVILEGES;

or

GRANT USAGE ON *.* TO 'bob'@'%.loc.gov' IDENTIFIED BY 'newpass';

grant privileges:
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
where you can replace ALL PRIVILEGES with any privilege that you like.

Revoking privileges:

revoke all privileges  on databasename.* from user;
again, you can replace ALL PRIVILEGES with any privilege that you like.

Show user privileges:

show grants for 'user'@'host';

Clean table – this will remove all the records in the table. unlike the Delete command, this will also reset any counters on the table.

TRUNCATE TABLE tablename;