MySQL read-Write separation Amoeba implementation

with Amoeba Mates MySQL Master-Slave construction MySQL read/write separation

1 MySQL master-Slave configuration

MySQL master192.168.11.177

MySQL slave192.168.11.178

Amoeba host 192.168.11.179

On both virtual machines

Yum Install MYSQLMYSQL-SEREVR mysql-devel ( No use to prevent future use)

/etc/init.d/mysqldstart

Mysqladmin-u Rootpassword ' 123456 '

Chkconfig mysqld on

on MySQL Master

(1)

mysql-uroot-p123456

Create Database TB;

Use TB;

Create tabletbtable (id int (4), name nvarchar (20));

Quit

( Libraries and tables for testing)

(2)

Vim/etc/my.cnf

server-id=161 ( server unique ID, default is 1, usually take IP last paragraph, integer 1 to 2 of 32 times)

Log-bin=mysql-bin # Enable Logging

BINLOG-DO-DB=TB # indicates that the databases are synchronized

replicate-ignore-db=test # indicates that the databases are not synchronized

Replicate-ignore-db=mysql

Replicate-ignore-db=information_schema

port=3306

(3)

log in to the main library to give permissions from the library account, allowing users to read the log on the main library ( user name: slave Password:123456)

mysql-uroot-p123456

Grant Replicationslave on * * to ' tbslave ' @ ' 192.168.11.135 ' identified by ' 123456 ';

To verify the account, we can use the command on the machine to test the following

Mysql-utbslave-p-H 192.168.11.161

( 4 )

Show Main Library information

Show master status;

mysql> showmaster status;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/45/86/wKioL1PolALABHnfAAFJkLZ0yaE102.jpg "title=" Image001.png "alt=" Wkiol1polalabhnfaafjklz0yae102.jpg "/>

On the MySQL slave

Vim/etc/my.cnf

[Mysqld]

Establish the databases to synchronize from the library; #tbfltest

server-id=177

Mysql> Slavestop

mysql> changemaster tomaster_host= ' 192.168.11.161 ', master_user= ' tbslave ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000003 ', master_log_pos=264;

Query OK, 0 rows affected (0.02 sec)

Mysql> Slavestart;

Query OK, 0 rowsaffected (0.00 sec)

Show Slavestatus\g;

1. Row ***************************

Slave_io_state:waiting for Master to Sendevent

master_host:192.168.11.161

Master_user:tbslave

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000003

read_master_log_pos:264

relay_log_file:mysqld-relay-bin.000002

relay_log_pos:251

relay_master_log_file:mysql-bin.000003

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:0

Last_error:

skip_counter:0

exec_master_log_pos:264

relay_log_space:407

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

1 row in Set (0.01SEC)

ERROR:

No query specified

Mysql>

Test

Create a new table on the main library to see if it will sync immediately

On the main library:

mysql-uroot-p123456

Use TB;

Create tabletbtest (id int (+), name nvarchar, passwd nvarchar (100));

Switch to ON-machine

mysql-uroot-p123456

Use TB;

Show tables;

If you can see the table established in the main library then it means that the synchronization is successful (after the master MySQL does not restart, restart the MySQL master status will change, slave will have a problem, to re-execute changes Master Tomaster_host= ' 192.168.11.161 ', master_user= ' tbslave ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000003 ', master_log_pos=264; )

Perform the operation in MySQL master,

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/45/85/wKiom1PokxTQx6sdAAA-MYtFOi4485.jpg "title=" Image002.png "alt=" Wkiom1pokxtqx6sdaaa-mytfoi4485.jpg "/>

Changed,

2 configuring amoebafor mysql(amoeba host 192.168.11.179)

2.1 Installing java, configuring Java Environment variables

SH jdk-6u32-linux-x64.bin

MV jdk1.6.0_32//usr/local/java

Vim ~/.bash_profile

Path= $PATH: $HOME/bin:/usr/local/amoeba/bin

Java_home=/usr/local/java

Export Java_home

Export PATH

2.2 Configuration Amoeba

Cd/usr/local/amoeba/conf

Vim Dbservers.xml

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/45/86/wKioL1PolD-hutNtAAPWfsKm1z4561.jpg "title=" Image003.png "alt=" Wkiol1pold-hutntaapwfskm1z4561.jpg "/>

This section defines the port of the real MySQL server, the database name,the MySQL user, and the password.
Master-slave Database definition:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/45/85/wKiom1PokznQcsylAAPYWpC497E417.jpg "title=" Image004.png "alt=" Wkiom1pokznqcsylaapywpc497e417.jpg "/>

This section defines the primary server, from the server, and from the server connection pool. Only the database addresses are defined here, and their users and passwords are the settings in the abstractserver above. Note the user that is used to connect to the real MySQL server must have remote connection permissions.

Amoeba.xml Configuration

Amoeba Connection Verification Configuration:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/45/86/wKioL1PolHKzsUFZAAWvAwHDevo815.jpg "title=" Image005.png "alt=" Wkiol1polhkzsufzaawvawhdevo815.jpg "/>

This defines the user and password used to authenticate when connecting to amoeba.
Read/write Separation configuration:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/45/86/wKioL1PolJLy57KoAAEhmFoBhoI066.jpg "title=" Image006.png "alt=" Wkiol1poljly57koaaehmfobhoi066.jpg "/>

on the MySQL master/slave host, perform the respective

Grant all on * * to [e-mail protected] '% ' identified by ' atest ';

AmoebaStart

Start command: Amoeba start

This command starts at the foreground, outputs information at startup, checks for no error messages, interrupts, and runs in the background:

Amoeba Start &