Configuration of Mycat Read and write separation under Linux

Why to configure read and write separation, I think I do not need to repeat, then how to read and write separation under the Mycat configuration, the actual efficiency after the configuration? I am in the morning according to the documentation and test a bit, here to do a record:

In the beginning, we would like to configure the master-slave replication of MySQL itself.

　　First, configure the primary server:

1. Edit the database configuration file,/etc/my.cnf

Add the code under [mysqld]:

log-bin=mysql-binserver-id=1innodb_flush_log_at_trx_commit=1sync_binlog=1binlog-do-db=db1binlog_ignore_db= Mysql

The Server-id is used to differentiate between the respective servers and needs to be globally unique.

BINLOG-IGNORE_DB is a library that is ignored, that is, the library for which the item setting is not copied.

BINLOG-DO-DB for databases that need to be replicated, and if not, to replicate all libraries except the binlog-ignore_db set

2. Restart MySQL:

Service mysqld Restart

3. Login Mysql:mysql-uroot-p

A separate account can be created for the slave end, which facilitates the management of rights and then assigns the account's permissions to the slave side:

' User ' ' x.x.x.x ' ' XXXXXX ' ; MySQL ' User ' @'x.x.x.x'XXXXXX';

x.x.x.x is the IP address of the slave end

4. Prevent data from synchronizing when new data is written to the database during replication:

Mysql>flush TABLES with READ LOCK;

5. Export and transfer the database to the slave server side:

#mysqldump-u root-pxxxxxx--all-databases  --lock-tables=false  -->/root/all.sql#  SCP /root/all.sql [email protected]:/root

x.x.x.x the IP address from the server.

6. Go to the master server database to view and record the status, which will be used later:

Mysql>show MASTER Status\g;

Record file and position

7. Unlocking the Data sheet

Mysql>unlock TABLES;

　　Then configure the slave server:

1. Log in from the server and import the primary server's database:

#mysql-U root-p123456 </root/all.sql

2. Edit the/etc/my.cnf, and under [Mysqld], add:

server-id=11        //service ID, note to be different from Master's Server-id Log-bin=mysql-binrelay-log-index=slave-relay-bin.indexrelay-log =slave-relay-binsync_master_info=1sync_relay_log=1sync_relay_log_info=1

3. Restart the database and log in, and execute the following command

Mysql>change Master tomaster_host= ' x.x.x.x ',            //IP address of the primary server master_user= ' USER ',               //account number and password created just now on the primary server Master_ Password= ' XXXXXX ', master_port=3306,master_log_file= ' mysql-bin.000001 ',//filemaster_log_pos=120 of the main server just now,        // Positionmaster_connect_retry=10 of the primary server;

4. Start the slave process:

Mysql>start SLAVE;

If there is no error, it should be set up.

　　A small optimization solution:

Although the MyISAM engine does not support transaction processing and row-level locks, its query efficiency is much better than InnoDB, and as a slave database, transaction processing and row-level locks are not required, so it is recommended that the client table be re-deleted and built as InnoDB.

　　Start Mycat Read and write separation:

to Configure the Schema.xml file:　

<Datahostname= "Localhost1"Maxcon= "+"Mincon= "Ten"Balance= "0"Writetype= "0"DbType= "MySQL"Dbdriver= "Native">    <Heartbeat>Select User ()</Heartbeat>    <!--can have multi write hosts -    <WritehostHost= "HostM1"URL= "localhost:3306"User= "root"Password= "XXXXXX">        <!--can have multi read hosts -        <ReadhostHost= "HostS1"URL= "x.x.x.x:3306"User= "User"Password= "XXXXXX" />    </Writehost></Datahost>

　　

Configuration of Mycat Read and write separation under Linux