Database reading and writing separation in Java environment

Many scenarios: Ali middleware Cobar, AOP annotations, Com.mysql.jdbc.ReplicationDriver read-Write separation drive
Synchronization of MySQL databases.

MySQL is an open-source relational database system. Master-Slave synchronous Replication (Replication) is a process that replicates data from one MySQL database server (master server master) to another server (from server slave).

Configuring the primary server(master)
1. Edit the database configuration file my.cnf or My.ini (Windows), typically in the/etc/directory.
#vi/etc/my.cnf
Add the following code below [mysqld]:
Log-bin=mysql-bin
Server-id=1
Innodb_flush_log_at_trx_commit=1
Sync_binlog=1
Binlog-do-db=wordpress
Binlog_ignore_db=mysql

The 1 in server-id=1 can be defined as long as it is unique.
Binlog-do-db=wordpress is to represent only the backup WordPress.
Binlog_ignore_db=mysql indicates that backup MySQL is ignored.
Without binlog-do-db and binlog_ignore_db, that means backing up all the databases.
Technical Resource Library Http://www.qi788.com/info
2. Then restart MySQL:
#service mysqld Restart

3. Log in to the MySQL server.
#mysql-uroot-p

Create a new user grant "REPLICATION SLAVE" permission on the primary server. You don't need to give other permissions. In the following command, replace the x.x.x.x with the IP from the server.
Mysql>create user ' user ' @ ' x.x.x.x ' identified by ' password ';
Mysql>grant REPLICATION SLAVE on * * to ' user ' @ ' x.x.x.x ' identified by ' password ';

4. Execute the following command to lock the database to prevent writing data.
Mysql>flush TABLES with READ LOCK;

5. Exit the MySQL command line and export the database
#mysqldump-u root-p123456--all-databases >/root/all.sql

6. Use the SCP command to transfer the database file All.sql to the slave server.
#scp/root/all.sql [Email protected]:/root

7. Connect the database again to the MySQL command line to view the master status.
Mysql>show MASTER STATUS;

Make a note of the information that is displayed, which is used by the configuration from the server.
+ —————— + ———-+ ———— –+ —————— +
| File | Position | binlog_do_db | binlog_ignore_db |
+ —————— + ———-+ ———— –+ —————— +
| Mysql-bin. 000001| 253| dbispconfig | mysql |
+ —————— + ———-+ ———— –+ —————— +
1 row in Set (0.00 sec)

    8, unlocking data sheet.
    Mysql>unlock TABLES;

Configure from Server (slave)

    Log on from the server.
    1, import the database of the primary server.
    #mysql-U root-p123456 </root/all.sql

    2, Edit Profile my.cnf, add under [mysqld]:
    server-id=2
    NOTE: 2 can be defined by itself, as long as it is guaranteed to be unique.

    3, save the file and restart Mysqld.
    #service mysqld restart

    4, log in to the MySQL server and execute the following command.
Mysql>change MASTER to
master_host= ' x.x.x.x ',
master_user= ' USER ',
master_password= ' PASSWORD ',
master_port=3306,
master_log_file= ' mysql-bin.000001,
master_log_pos= 253,
Master_connect_ retry=10;

  Note:
    master_host: The IP of the primary server.
    Master_user: User name established when configuring the primary server
    master_password: User password

Master_port: The primary server MySQL port, if not modified, by default.

Here Master_log_file and Master_log_pos are the results of the previous show master status.

5, start the slave process.

Mysql>start SLAVE;

Mysql>show SLAVE STATUS; If we can find a record, then the configuration is successful.

6, check the MySQL log, generally in the/var/log/directory, if the startup is successful, you should see a log similar to the following.
091104 8:42:02 [Note] Slave I/O thread:connected to master ' [e-mail protected]:3306?, replication started in log ' Mysql-bi N.000001? At position 98

Now the master server and the slave server are all configured. In addition, you may need to set the expiration time of the primary server's database binary log, which can be set using the parameter Expire_logs_days in the configuration file.

Java Spring transaction settings, method header settings

@Transactional (Readonly=true)

Java Connection Driver settings
Jdbc.connection.driver=com.mysql.jdbc.replicationdriver
jdbc.connection.url=jdbc:mysql:replication://192.168.202.190,192.168.202.190/job?useunicode=true& Characterencoding=utf-8

//------------------------------------------------------

After using amoeba and Cobar, and Dbware and other read-write separation components, today a good friend of mine told me that MySQL itself is also able to read and write separate, because they provide a new driver, called Com.mysql.jdbc.ReplicationDriver

Description Document: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-replication-connection.html

code example:

importjava.sql.Connection;importjava.sql.ResultSet;importjava.util.Properties;importcom.mysql.jdbc.ReplicationDriver;publicclassReplicationDriverDemo {  publicstatic voidmain(String[] args) throwsException {    ReplicationDriver driver = newReplicationDriver();    Properties props = newProperties();    // We want this for failover on the slaves    props.put("autoReconnect", "true");    // We want to load balance between the slaves    props.put("roundRobinLoadBalance", "true");     props.put("user", "foo");    props.put("password", "bar");    //    // Looks like a normal MySQL JDBC url, with a    // comma-separated list of hosts, the first    // being the ‘master‘, the rest being any number    // of slaves that the driver will load balance against    //    Connection conn =        driver.connect("jdbc:mysql:replication://master,slave1,slave2,slave3/test",            props);    //    // Perform read/write work on the master    // by setting the read-only flag to "false"    //    conn.setReadOnly(false);    conn.setAutoCommit(false);    conn.createStatement().executeUpdate("UPDATE some_table ....");    conn.commit();    //    // Now, do a query from a slave, the driver automatically picks one    // from the list    //    conn.setReadOnly(true);    ResultSet rs =      conn.createStatement().executeQuery("SELECT a,b FROM alt_table");     .......  }}

Database reading and writing separation in Java environment