MySQL import and export commands

MySQL import and export commands

mysqldump  

MySQL is an open-source relational database management system (RDBMS). World's second most widely used open-source client–server RDBMS. The MySQL development project has made its source code available under the terms of the GNU General Public License and owned by Oracle Corporation.

Now lets see simple command line examples for exporting and importing MySQL database(s). All examples are shown under Windows machines. So when we try in different OS only path(s) will change rather than commands shown below.


Export Commands:

Open command prompt and goto MySQL installed bin folder as shown below location. NOTE: Installation directory path may change from system to system.

C:\Program Files\MySQL\MySQL Server 5.6\bin>


1. Export all databases

> mysqldump -u{username} -p{password} -h{host} --all-databases > C:\Backup\all-databases.sql

* username - MySQL username
* password - MySQL password
* host - MySQL server host. If MySQL running in local then please mention as 'localhost' else give remote MySQL server IP address. When we give remote server IP, need to make sure that we have access to remote MySQL server.


2. Export single database

> mysqldump -u{username} -p{password} -h{host} {database_name} > C:\Backup\database.sql

* database_name - MySQL database name to export to file.


3. Export more than 1 database

> mysqldump -u{username} -p{password} -h{host} --databases {database_name_1} {database_name_2} > C:\Backup\databases_1_2.sql


4. Export only specific tables from database

> mysqldump -u{username} -p{password} -h{host} {database_name} {table_name_1} {table_name_2} > C:\Backup\database_table_1_2.sql


mysqldump supports the various options, which can be specified on the command. For more informations on options please refer to MySQL site link under Table 4:11


Import Command:

Similar to mysqldump command please goto MySQL installed bin folder [C:\Program Files\MySQL\MySQL Server 5.6\bin>].

1. Import all databases

> mysql -u{username} -p{password} -h{host} < C:\Backup\all-databases.sql


2. Import particular database

> mysql -u{username} -p{password} -h{host} {database_name} < C:\Backup\database.sql

* NOTE: Database with name mentioned must exist already, else create empty database with the name mentioned before importing.


3. Import tables into particular database

> mysql -u{username} -p{password} -h{host} {database_name} < C:\Backup\database_table_1_2.sql

* NOTE: Database with name mentioned must exist already, else create empty database with the name mentioned before importing.

Increase MySql database time wait

Increase MySql database time wait

MySql Timewait  

In one of our earlier tutorials we have see about basic MySql commands and MySql connection sample code. In this tutorial we will see about increasing MySql default time wait of 8 hours.
For example in a rare condition we have an application which running socket connection between 2 server and communicates each other's data. But communication times are inconsistent and it can happen once in 1 hour or 2 hour once or even after 8 hours once. Lets assume in that case we having a open MySql database connection and whenever request comes it needs to preform its appropriate database activity. 
In that case if time wait goes more than 8 hours then MySql will disconnect all those database connections. So we need of more time wait from 8 hours to 10 or 15 hours or 1 days etc.,
So lets see how to change default MySql time wait option.

Before changing default time wait in MySql lets query for the current time wait which set in server.

mysql> show variables LIKE '%timeout%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| connect_timeout                 | 10    |
| delayed_insert_timeout       | 300   |
| innodb_lock_wait_timeout    | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout             | 28800 |
| net_read_timeout               | 30    |
| net_write_timeout              | 60    |
| slave_net_timeout              | 3600  |
| table_lock_wait_timeout     | 50    |
| wait_timeout                     | 28800 |
+----------------------------------+-------+

By above query we can get current MySql "wait_timeout" and "interactive_timeout" values as 28800(seconds equal to 8 hrs). Next we will see about changing those values from 8 hrs to 24 hrs. 

Under /etc edit my.cnf file with below details

# vi /etc/my.cnf 

[mysqld]

wait_timeout=86400

interactive_timeout=86400

NOTE: Suppose if my.cnf file not exist please create it. 

Once changed please restart MySql and check for the parameters has been updated or not.

mysql> show variables LIKE '%timeout%';

+------------------------------------+--------+

| Variable_name                      | Value  |

+------------------------------------+--------+

| connect_timeout                    | 10     | 

| delayed_insert_timeout          | 300    | 

| innodb_lock_wait_timeout       | 50     | 

| innodb_rollback_on_timeout    | OFF    | 

| interactive_timeout                | 86400  | 

| net_read_timeout                  | 30     | 

| net_write_timeout                 | 60     | 

| slave_net_timeout                 | 3600   | 

| table_lock_wait_timeout         | 50     | 

| wait_timeout                        | 86400  | 

+------------------------------------+--------+

From above table we can see timeout value got changed from 8 hrs to 24 hrs. 

Useful MySQL commands and queries

Useful MySQL commands and queries

MySql commands and query  

We all know that MySQL database is widely used open source database. So lets see few simple examples like how to start and stop MySQL service, creating database and few simple query for create, select, update, delete and alter tables. First lets see how to start and stop MySQL server along with few other commands in Unix machines. 


START MYSQL:
# /etc/init.d/mysqld start


STOP MYSQL:
# /etc/init.d/mysqld stop


CHECK MYSQL STATUS:
# /etc/init.d/mysqld status


RESTART MYSQL:
# /etc/init.d/mysqld restart


Login to MySQL database:
# mysql -u root -p <ENTER>
Enter password: <ENTER ROOT PASSWORD>
mysql>


LIST AVAILABLE DATABASES:
mysql>show databases;


CHANGE ROOT USER PASSWORD:
mysql> use mysql;
Database changed
mysql> update user set password=PASSWORD("mypassword") where User='root';
mysql> flush privileges;

NOTE: Once we change password need to flush privileges or need to restart MySQL database.


CREATE DATABASE:
mysql> create database <DATABASE_NAME>;


DROP DATABASE:
drop database <DATABASE_NAME>;


SELECT DATABASE:
mysql> use <DATABASE_NAME>;
Database changed
mysql> 


LIST AVAILABLE TABLES:
mysql>show tables;


SHOW TABLE STRUCTURE:
mysql> desc <TABLE_NAME>;


CREATE TABLE (showing sample tables):
CREATE TABLE tbl_master (
        user_id int not null primary key AUTO_INCREMENT,
user_name varchar(100) not null                
);

CREATE TABLE tbl_details (
id int not null primary key AUTO_INCREMENT,
user_id int not null,
project_name varchar(100) not null,
project_start datetime,
project_end datetime,
FOREIGN KEY (user_id) REFERENCES tbl_user_master(user_id)
);  


INSERT QUERY:
INSERT INTO tbl_master (user_name) VALUES("Rex");
INSERT INTO tbl_details (user_id, designation, date_of_birth) VALUES(1, "APAC-Mono", "2012-10-25", "2013-01-30");


SELECT QUERY:
SELECT * FROM tbl_master;
SELECT user_name, project_name, project_start, project_end FROM tbl_details as D, tbl_master as M WHERE D.user_id = M.user_id;   


UPDATE QUERY:
UPDATE tbl_details SET project_name = "APAC-Teli" WHERE project_name = "APAC-Mono";


ALTER QUERY:
ALTER TABLE tbl_master DROP PRIMARY KEY, ADD PRIMARY KEY(user_id, user_name);
ALTER TABLE tbl_master MODIFY user_name VARCHAR(150);
ALTER TABLE tbl_master DROP COLUMN user_name;
ALTER TABLE tbl_master ADD COLUMN user_name VARCHAR(150);


DELETE TABLE:
DELETE TABLE tbl_details WHERE user_id = 1;
DELETE TABLE tbl_details;


TRUNCATE TABLE:
TRUNCATE tbl_details;


DROP TABLE:
DROP TABLE tbl_details;

MySQL JDBC Connection Example

MySQL JDBC Connection Example

MySQL  

In one of our earlier tutorial we have seen about PostgreSQL database connection with simple JSP login page example. In this tutorial we will see about MySQL JDBC connection with example. To test this code we assume already MySQL database has been installed properly and its in running state. Also we have create a test database to verify the code. 
Any Java IDE like Eclipse, IntelliJ  etc., can be used or even we can test from command prompt. 
Next we need MySQL JDBC jar file or MySQL connector jar file. If you are using any IDE then downloaded jar file can be added to your libraries.

In below example we will see how to create MySQL DB connection, how to use simple select query and finally closing the connection. 

public class TestConnection {
 
 public static void main(String[] args) {
  
  // Creating DB connection
  Connection conn = createConnection();
  
  // Simple fetch query
  fetchEmailId(conn);
  
  // Closing connection
  closeConnection(conn);
 }

 public static Connection createConnection(){
  
  Connection conn = null;
   
  try {
   Class.forName("com.mysql.jdbc.Driver");
   System.out.println("MySQL JDBC Driver Registered successfully :)");
   conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname","username", "password");
  
  } catch (SQLException e) {
   System.err.println("Connection failed !!!");
   e.printStackTrace();
   return null;
  
  } catch (ClassNotFoundException e) {
   System.err.println("Please configure MySQL driver file !!!");
   e.printStackTrace();
  }
  return conn;
 }
 
 public static void fetchEmailId(Connection conn){
  Statement st = null;
  if(conn == null){
   System.err.println("Connection failed !!!");
   return;
  }
  
  System.out.println("Database connection is successfull :)");
  
  //Querying simple select query
  try {
   String qry = "SELECT email_id FROM tbl_email_master";
   st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
   ResultSet rs = st.executeQuery(qry);
   while(rs != null && rs.next()){
    System.out.println(rs.getString("email_id"));
   }
   
  } catch (SQLException e) {
   e.printStackTrace();
  
  } finally{
   try{
    if(st != null) st.close();
   }catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
 
 public static void closeConnection(Connection conn){
  if(conn != null){
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
}