Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

mysql



Multiple mysql instances in windows

====================================
Centos 7 - https://www.linode.com/docs/databases/mysql/how-to-install-mysql-on-centos-7
====================================
Each instance runs in a different port and behaves as a dedicated standalone server.

multiple mysql in one machine
Why multiple instances..?
If you are a developer its for you. Sometimes you need to test your application with different type of data (like QA, Production) to replicate some error and etc., At that time no need to drop your old database and put the new one. Just use this and change the db port in your application configuration. This will be helpful for system administrators also to provide database service to his users.
Requirement:
  • mysql installed windows machine
  • Administrative privileges for that machine
For me mysql installed location is e:\softs\mysql\
Steps:
  1. Create [instances\production] with in mysql installed directory (you can create anywhere). Better   do everything in command prompt. 
o     
e:
cd \softs\mysql\
md instances\production
o     
  1. Copy your data and share folder to production folder
o     
copy data instances\production\
copy share instances\production\
  1. To create your custom ini file copy my.ini file and edit
o     
copy my.ini instances\production.ini
  1. Open production.ini and change port as 3307 under [client] and [mysqld] section
  2. Change basedir and datadir in that ini file. Clickhere for my example file
  3. Now add your custom ini in system starup
o     
mysqld --install mysqldproduction --defaults-file="E:\softs\mysql\instances\production.ini"
  1. Now goto Control Panel → Administrative tools → Services. Then search for ‘mysqldproduction’ and start service. OR
o     
net start mysqldproduction
You may think all is done. But after this only I faced problems.
Cannot find the file specified:
When I try to start that service “System error has occurred.The system cannot find the file specified.”
Open your registry editor at:
  1. Start → Run
  2. type ‘regedit’ and enter.
  3. Next, browse to the registry key named: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\mysqldproduction\ImagePath
  4. Now you can see there is no double quotes in that –defaults-file so change ImagePath to
o     
"E:\softs\mysql\bin\mysqld" --defaults-file="E:\softs\mysql\instances\production.ini" mysqldproduction
  1. Now start that service.
Keep watching Event Logger. That is the best place to get debug information. Again error while starting server

Event viewer for mysql
Can’t find messagefile:
The error is
System error 1067 has occurred.


The process terminated unexpectedly.


Can't find messagefile 'E:\softs\mysql\instances\production\share\errmsg.sys'
Fix:
Just copy errmsg.sys file from [MYSQL_BASE]\share\english\ to that instances\production\share folder
Now everything is fine. Service will start successfully.

========================================================
####### Install Mysql in Windows ########

1. Install using the GUI provided. It will ask for port ect. and then install as a service.

### If you get any error "Taking long time to start" ######

1. Cancel the installation

2. Go to the installed directory in C:

3. Copy the my-medium.ini (or large or small) as my.ini

4. Change "port" "datadir" "socket"

5. Try starting from console.

C:\mysql-5.5.41\bin\mysqld --console

6. It will start correctly.

7. Then install the service(Change the service name as needed. here "mysqld2").

C:\mysql-5.5.41\bin\mysqld --install mysqld2


########## Delete Service ##########

sc delete ServiceName
 
=====================================================
MySQL Installation
===================================
mysql community server
http://dev.mysql.com/downloads/file/?id=461109
download my sql msi all in  one package


Next Next finish
=================================================================
Multiple instances
===========================================

cd C:\ProgramData\MySQL\
md instances\prod
copy all 4 files including data file from C:\ProgramData\MySQL\MySQL Server 5.7/
to  instances\prod
copy share folder C:\Program Files\MySQL\MySQL Server 5.7\share 
to  instances\prod 
rename ini file as prod.ini
prod.ini - change port  as 3307 under [client] and [mysqld] section
prod.ini  - change all file paths according to new folder paths including base dir data dir

cd C:\Program Files\MySQL\MySQL Server 5.7\bin   //must use this otherwise get an error when start the service in the services, if get the error use "sc delete mysqldproduction" then go to bin path and execute following command.
 
 "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" --defaults-file="C:\ProgramData\MySQL\instances\prod\prod.ini" mysqldproduction



then start
mysqldproduction service in the service

cmd-> mysql --port=3307 -u root -p
(to use mysql command need to set path env. variable->
"C:\Program Files\MySQL\MySQL Server 5.7\bin")

 ====================================================================

--------------------------
After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

Login as root

mysql -u root -p

+++++++++++User only allowed from localhost++++++++++++++++++++++

mysql> CREATE USER 'mysqluser'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;

FLUSH PRIVILEGES;


+++++++++++User only allowed any host++++++++++++++++++++++

mysql> CREATE USER 'mysqluser'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysqluser'@'%'
    ->     WITH GRANT OPTION;

FLUSH PRIVILEGES;

///ex:
CREATE USER superadmin@'%'
IDENTIFIED BY 'Secured';///
++++++++++ Grant previlleges to a specific DB +++++++++++++

CREATE DATABASE roundcubemail;
GRANT ALL PRIVILEGES ON roundcubemail.* TO username@localhost IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

   

The accounts created by these statements have the following properties:

Two of the accounts have a user name of monty and a password of some_pass.
Both accounts are superuser accounts with full privileges to do anything.
The 'monty'@'localhost' account can be used only when connecting from the local host.
The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.


==================== Commands ============================

+++++++++++++++ Create DB +++++++++++++++++++++++++++++++++++++++

mysqladmin -u root -p create DB_NAME

+++++++++++++++ User Accounts +++++++++++++++++++++++++++++++++++

SELECT User FROM mysql.user; ### list all users

DROP USER 'jeffrey'@'localhost'; ##### delete user

UPDATE mysql.user SET Password=PASSWORD('foobar') WHERE User='tom' AND Host='localhost';  #### Update Password of user

+++++++++++++++ DB and Query  +++++++++++++++++++++++++++++++++++

mysql -u root -p ###### login as root

show databases; ###### Show all data bases

use icescrum; ######## Go into database

show tables; ######## Show all tables in above db

select * from icescrum2_user; ###### list all entries in a table

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

===============================================================

connection to jboss.
//sample code
package com.howtodoinjava.jdbc.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionDemo {
    public static void main(String[] argv) {
        System.out.println("-------- MySQL JDBC Connection Demo ------------");
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
        }
        catch (ClassNotFoundException e) {
            System.out.println("MySQL JDBC Driver not found !!");
            return;
        }
        System.out.println("MySQL JDBC Driver Registered!");
        Connection connection = null;
        try {
            connection = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root", "password");
            System.out.println("SQL Connection to database established!");
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            return;
        } finally {
            try
            {
                if(connection != null)
                    connection.close();
                System.out.println("Connection closed !!");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
Output:
-------- MySQL JDBC Connection Demo ------------
MySQL JDBC Driver Registered!
SQL Connection to database established!
Connection closed !!


===========================
My sample login
===========================
ADD jdbc jar file
http://dev.mysql.com/downloads/file/?id=460363
      --->Right click the project->debug->debug configurations->jboss4->class path-> add external jars->inport the mysql jar jdbc file    

  
====================
Appone.java

protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
         if(request.getParameter("firstname") == null ||request.getParameter("firstname") == null){
                getServletContext().getRequestDispatcher("/index.jsp").forward(request, response);
                return;
            }
       
            //PrintWriter out = response.getWriter();
           String fname=request.getParameter("firstname");
           String lname=request.getParameter("lastname");
            //out.println("Hello " +fname+ " " + lname);
       
       
       
       
       
            Connection c = null;
           Statement stmt = null;
            try {
            //Class.forName("org.postgresql.Driver");
              //c = DriverManager.getConnection("jdbc:postgresql://192.168.0.95:5432/PGRS1","postgres","123456");
                Class.forName("com.mysql.jdbc.Driver");
                c = DriverManager.getConnection("jdbc:mysql://localhost:3306/PGRS1", "root", "123456");
            //c = DriverManager.getConnection("jdbc:mysql://localhost/pgrs1" + "user=root&password=123456");
          
                  
           
              c.setAutoCommit(false);
              System.out.println("Opened database successfully");

              stmt = c.createStatement();
              ResultSet rs = stmt.executeQuery( "SELECT * from ss2.tt1;" );
              while ( rs.next() ) {
             
                 fname = rs.getString("id");
                 lname = rs.getString("title");
             
             
                System.out.println( "FNAME = " + fname );
                System.out.println( "LNAME = " + lname );
                        System.out.println();
             }
              rs.close();
              stmt.close();
         
              c.close();
            } catch ( Exception e ) {
              //System.err.println( e.getClass().getName()+": "+ e.getMessage() );
             // System.exit(0);
             System.out.println("not Opened database ");
            }
       
       
       
       
       
       
       
            request.setAttribute("fname", fname);
            request.setAttribute("lname", lname);
       
            getServletContext().getRequestDispatcher("/output.jsp").forward(request, response);
    }

===================================
index.jsp
===================================
<body>
 <form action="appone" method="post">
<table border="0">
<tr>
<td>First Name : </td> <td> <input type="text" name="firstname" /> </td>
</tr>
<tr>
<td>Last Name: </td> <td> <input type="text" name="lastname" /> </td>
</tr>
<tr>
<td colspan="2"> <input type="submit" value="Submit" /> </td>
</tr>
</table>
</form>
</body>
==================================
output.jsp
==================================
<body>
    <h1>Your name is </h1>
    <% 
   
   
   
    String fname=(String) request.getAttribute("fname");
    String lname=(String) request.getAttribute("lname");
    out.print(fname+" "+lname);    
     %>
</body>
================================== 

MySQL install in CENTOS 
==================================

su
yum install mysql mysql-server
chkconfig msqld on
chkconfig msqld --list
service mysqld start
/usr/bin/mysql_secure_installation
[enter]
123456[pw] Y Y Y Y

http://articles.slicehost.com/2011/3/10/configuring-mysql-server-on-centos



mysql -u root -p =to login
---------------------

vi /etc/my.cnf
[mysqld]
bind-address = 192.168.0.95

--------------
grant all privileges on pgrs1.* to root@"PROGRAM IP" identified by '123456'; 
flush privileges;
 
GRANT SELECT, INSERT ON *.* TO 'root'@'192.168.1.93';
 flush privileges;
 
Note:Database is a collection of schemas and schema is a collection of tables. 
But in MySQL they use it the same way. 
 
CREATE SCHEMA ss1;

CREATE TABLE ss1.tt1(id INT NOT NULL,title text,PRIMARY KEY (id));

INSERT INTO ss1.tt1 (id,title) VALUES (22,'aaaaaa');

=============================================== 

multiple 
///////////http://dev.mysql.com/doc/refman/5.1/en/multiple-servers.html
http://www.ducea.com/2009/01/19/running-multiple-instances-of-mysql-on-the-same-machine/

 [root@localhost ~]# mkdir  /var/lib/mysql2
[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql2
[root@localhost ~]# mkdir /var/log/mysql2
[root@localhost ~]# chown -R mysql.mysql /var/log/mysql2
[root@localhost ~]# cp -rf /etc/my.cnf /etc/my2.cnf

 vi /etc/my2.cnf
[mysqld]
datadir=/var/lib/mysql2
socket=/var/lib/mysql2/mysql.sock
user=mysql
port=3307
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

 [mysqld_safe]
log-error=/var/log/mysql2/mysqld.log
pid-file=/var/run/mysqld/mysqld2.pid
port=3307

mysql_install_db --user=mysql --datadir=/var/lib/mysql2/

 start -----> mysqld_safe --defaults-file=/etc/my2.cnf &

 to connect 3307
mysql -h 127.0.0.1 -P 3307

 (now you dont want passwod to connect, so use this for set the password after first login)
mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
now connect-->mysql -h 127.0.0.1 -P 3307 -p
for external, GRANT ALL PRIVILEGES ON *.* TO root@192.168.137.11 IDENTIFIED BY '123456';
now local host and extenl (12) askin the password when connecting

 its updating mysql database users
SELECT * FROM mysql.user;

//////////to connect 3306

///////////mysql -p -h 127.0.0.1 -P 3306
stop 3307---->
mysqladmin -S /var/lib/mysql2/mysql.sock shutdown

==============================

 not complete
replication

 vi /etc/m.cnf
bind-address=192.168.137.11
server-id=1
log_bin=/var/lib/mysql/mysql-bin.log
create user 'repl'@'%' identified by 'slavepassword';
grant replication slave on *.* to 'repl'@'%';

 create database pgrs1;
use pgrs1
create table pgrs1.t1(name varchar(20)) ;
insert into pgrs1.t1 values('aaa');
select * from pgrs1.t1;
 mysqldump -uroot --all-databases --master-data > masterdump.sql -p
 grep CHANGE *sql |head -1

 -------------------
bind-address=192.168.137.12
server-id=2
log_bin=/var/lib/mysql/mysql-bin.log

 mysql -h 192.168.137.12 -P 3308 -p 

 mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.137.11',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='slavepassword';

 mysql -h 192.168.137.12 -P 3308 -p < masterdump.sql

mysql>start slave;

show slave status/G;

 https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)