Mysql JSP Connection

Download mysql jar and add in your project library. The jar file can be download from below url:
http://mirrors.ibiblio.org/pub/mirrors/maven2/mysql/mysql-connector-java/3.1.12/mysql-connector-java-3.1.12.jar

<%@ page language="java" import="java.sql.*"%>
<% /*Database connection*/ out.println("MySQL Connect Example.
");
String url = "jdbc:mysql://localhost:3306/";
String dbName = "test";
String driver = "com.mysql.jdbc.Driver"; //or String driver = "org.gjt.mm.mysql.Driver";
String userName = "user";
String password = "pass";

Connection con=null;
ResultSet rst=null;
Statement stmt=null;

try {
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url+dbName,userName,password);
out.println("Connected to the database
");

stmt=con.createStatement();
rst=stmt.executeQuery("Query");
while(rst.next()) {
out.println("

---

"+rst.getString("field"));
}

con.close();
out.println("

---

Disconnected from database");
} catch (Exception e) {
e.printStackTrace();
out.println(e);
}
%>

MySQL User Privileges

Create User:
CREATE USER username IDENTIFIED BY 'password';

ex:
- CREATE USER myuser IDENTIFIED BY 'mypass';
- CREATE USER myuser;


Assign Permission:
GRANT PRIVILEGES_LIST|ALL ON DATABASE[.TABLES|.*] TO 'username'@'HOST';

ex:
- GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON mydb TO 'myuser'@'localhost';

List Permission:
SHOW GRANTS FOR 'user'@'host';

ex:
SHOW GRANTS FOR 'admin'@'localhost';

MySql Database backup command - mysqldump

Syntax:
mysqldump [options] -h hostname -u username -p password database|--all-databases [tables] > backupfile.sql

Example:

• mysqldump -h localhost -u root -p test > backup.sql
• mysqldump -h localhost -u root -p test mytable > backup.sql
• mysqldump -h localhost -u root -p --all-databases > backup.sql