Centosamoeba + mysql Master/Slave cluster Separation

Centosamoeba + mysql Master/Slave cluster Separation
It is recommended that you do not enter the database to be synchronized before configuring the master and slave databases. By default, all databases will be synchronized.
Mysql comes with java. 1. log on to the Master server, modify my. cnf, and add the following content;
Server-id = 1 // Database id. If it is set to 1, it is expressed as a Master. master_id must be a positive integer between 1 and 232-1;
Log-bin = mysql-bin // enable binary logs;
Binlog-do-db = data // name of the binary database to be synchronized; (it is recommended that you do not write it. All databases are synchronized by default)
Log =/usr/local/log/mysql. log location. You need to create a directory and change the chmod and chown commands.
{The master database configured above is enough. You can add some items listed below as needed.
Default-storage-engine = innodb-default storage engine, which is currently the main storage engine of Mysql
Innodb_data_home_dir =/data-innodb's data home directory
Innodb_data_file_path = ibdata1: 50 M: autoextend-innodb data storage file. When writing an absolute path, innodb_data_home_dir must be empty. Multiple storage files can be specified and separated by semicolons.
Server-id = 1-mysql server id: (1-2 ^ 32). The smaller the id number, the higher the priority.
Log-bin =/dblog/mysqld-bin-binary log
Max-binlog-size = 10 M-mysqlbinlog the maximum size of a single file is 10 M
Expire_logs_days = 10-Maximum log storage date
# Logs
Slow_query_log =/dblog/slow. log-the slow log must be enabled in the production environment.
Long_query_time = 2-defining a query that exceeds 2 seconds is a slow Query
Log-queries-not-using-indexes = 1
Log-error =/dblog/mysqld. err-error log
Do not enable the query log because of the large data size, which affects I/O.
Log-bin: a new file is generated every time Mysql is restarted.
# Mkdir/data-create a data storage directory
# Mkdir/dblog-create a log storage directory
# Chmod 700/data // dblog-Modify permissions
# Chown mysql. mysql/data/dblog/-R
# Mysql_install_db-datadir =/data-user = mysql-initialization
# Service mysql start-start the service. If no PID is found, check whether selinux and iptables are enabled.
}
2. Create the user to use for replication;
(First, log on. If you do not log on, you cannot authorize or create a user.) for the first time, you can log on directly without a blank password.
The command to set the password is as follows: mysqladmin-uroot-p (old password, do not write) password new password and press ENTER
The system will prompt you to enter the password. You don't need to enter the password. Press enter to set the password.
Create a copy USER command mysql> grant replication slave on *. * to 'slave '@' % 'identified BY '123'
3. Restart mysql;
Service mysqld restart
4. (If the synchronized Master database has data, back up the data. If not, skip this step.) back up the data on the Master database;
After the locks, I directly lock the tar.gz data library file;
Mysql> flush tables with read lock;
Cd/var/lib/mysql
Tar data.tar.gz data
Then the remote scp is directly executed;
Scp./data.tar.gz root @ IP Address:/var/lib/mysql
The command for unlocking the master database is mysql> unlock tables;
5. After authorization, check the status of the master database,
Log on to mysql and grant the logon permission to the remote ip address. mysql> grant all privileges on *. * to 'root' @ '%' identified by '000000' with grant option; the permission to remotely manage IP addresses is mysql> grant all privileges on *. * to 'root' @ 'remote ip address' identified by '000000' with grant option; grant local Permissions
All privileges on *. * to 'root' @ 'localhost' identified by 'root' with grant option)
View status: mysql> show master status \ G
Record the pos and the first file name. Slave Database Synchronization is required (same as step 1)
If you check the status of the master database, do not perform any operations on the master database. Otherwise, the file blocking will change.
6 slave settings
Log on to the Slave database server and modify my. cnf;
Server-id = 3 // 2 has been used on another server. If you need to add the Slave number and then count it in the future, it will be OK;
Log-bin = mysql-bin
Master-host = master database ip Address
Master-user = slave
Masters-password = 888888
Master-port = 3306
Master-connect-retry = 60 // the time difference between the reconnection if the master server is disconnected;
Replicate-do-db = data // the database to be backed up
7. decompress the file from the Master scp. You do not need to change the permission and owner here. The default value is not changed. You can modify the file based on the actual situation;


8. After completing the preceding steps, you can start slave and view the slave status;

mysql>slave start;mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.0.1Master_User: testMaster_Port: 3306Connect_Retry: 60Master_Log_File: updatelog.000001Read_Master_Log_Pos: 106Relay_Log_File: onlinevc-relay-bin.000013Relay_Log_Pos: 1069Relay_Master_Log_File: updatelog.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: dataReplicate_Ignore_DB: mysqlReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 106Relay_Log_Space: 1681Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:1 row in set (0.00 sec)

9. view the status of the Master;

mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| updatelog.000012 | 15016 | data | mysql |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

It can be seen that there is a problem with the File and Position of the two. You need to set Master_Log_File and Read_Master_Log_Pos for the master database on Slave; execute the following statement;
Mysql> slave stop;
Mysql> change master to MASTER_HOST = 'master database ip', MASTER_USER = 'slave ', MASTER_PASSWORD =' ****** ', MASTER_LOG_FILE = 'updatelog. 000012 ', MASTER_LOG_POS = 15016;
Make sure that Slave_IO_Running: Yes, Slave_ SQL _Running: Yes must be YES to prove that Slave's I/O and SQL are normal.


10. Some statements are frequently used to log on to mysql during this period. I will write them down.
Show databases;
Show tables;
Create database Library
Create table indicates (id int, name varchar (100 ));
Insert into values (a, tom)
Create table indicates (id int)
Insert into indicates values (1)
Drop table indicates
Delete from user where user = 'delete user'
Select * from user, host from user; view user and logon Permissions
Use Library


MySQL inserts 1 million records to test the Stored Procedure

BEGIN#Routine body goes here...DECLARE i INT DEFAULT 0;WHILE i < 1000000 DOINSERT INTO test values(i, concat('zhangsan',i));SET i = i + 1;END WHILE;END

Before installing amoeba, you must authorize the user name and password used to connect the amoeba to the database. The command is as follows:
Create an amoeab user authorization command: grant all on *. * to 'amoebauser' @ '%' identified by 'amoebapassword'
Note: grant permissions to the mysql master database.
10. Installation of Amoeba
Amoeba: http://sourceforge.net/projects/amoeba/
Mkdir/usr/local/amoeba
Wget http://softlayer.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz
Tar xzf amoeba-mysql-binary-2.2.0.tar.gz-C/usr/local/amoeba
Configure Global Environment Variables
Vi/etc/profile
Export PATH =/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/bin: $ PATH
Export JAVA_HOME =/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/
Export AMOEBA_HOME =/usr/local/amoeba
Export PATH =/usr/local/amoeba/bin: $ PATH
Let's talk about the java variables. You can use which java and whereis java to find them. If they cannot be found, use find/-name java to find them. Do not confuse them with the connected files. Make it clear.
Here I will use find/-name java to find it.
Then let the environment variables take effect
Source/etc/profile


11. Amoeba for mysql configuration and configuration the read/write splitting of Amoeba for mysql mainly involves two files:
1./usr/local/amoeba/conf/dbServers. xml
This file defines how the database of the Amoeba agent connects, such as the Basic Host IP address, port, and username and password used by Amoeba.
2./usr/local/amoeba/conf/amoeba. xml
This file defines the relevant configurations of the Amoeba agent.
Configure abstractServer in the dbServers. xml file:

<dbServer name="abstractServer" abstractive="true"><factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"><property name="manager">${defaultManager}</property><property name="sendBufferSize">64</property><property name="receiveBufferSize">128</property><!-- mysql port --><property name="port">3306</property><!-- mysql schema --><property name="schema">dbname</property><!-- mysql user --><property name="user">root</property><!-- mysql password --><property name="password">root</property></factoryConfig><poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool"><property name="maxActive">500</property><property name="maxIdle">500</property><property name="minIdle">10</property><property name="minEvictableIdleTimeMillis">600000</property><property name="timeBetweenEvictionRunsMillis">600000</property><property name="testOnBorrow">true</property><property name="testWhileIdle">true</property></poolConfig></dbServer>

This section defines the real mysql server port, database name, mysql user and password.

Master-slave database definition:

<dbServer name="Master" parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.0.1</property></factoryConfig></dbServer><dbServer name="Slave1" parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.0.2</property></factoryConfig></dbServer><dbServer name="Slave2" parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.0.3</property></factoryConfig></dbServer><dbServer name="virtualSlave" virtual="true"><poolConfig class="com.meidusa.amoeba.server.MultipleServerPool"><!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--><property name="loadbalance">1</property><!-- Separated by commas,such as: server1,server2,server1 --><property name="poolNames">Slave1,Slave2</property></poolConfig></dbServer>

This section defines the master server, slave server, and slave server connection pool. Here, only the database addresses are defined. Their users and passwords are set in the abstractServer above. Note that the user connecting to the real mysql server must have the remote connection permission.

Amoeba. xml configuration

Amoeba connection verification Configuration:

<property name="authenticator"><bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"><property name="user">root</property><property name="password">root</property><property name="filter"><bean class="com.meidusa.amoeba.server.IPAccessController"><property name="ipFile">${amoeba.home}/conf/access_list.conf</property></bean></property></bean></property>

The user and password used to verify the connection to amoeba are defined here.

Read/write splitting Configuration:

<property name="defaultPool">Master</property><property name="writePool">Master</property><property name="readPool">virtualSlave</property>

DefaultPool: the default database node is configured. Some statements except SELECTUPDATEINSERTDELETE are executed in the defaultPool.

WritePool: a database write database is configured. It is usually configured as a Master database. Here, it is configured as a previously defined Master database.

ReadPool: a database read database is configured. It is usually configured with a database pool composed of Slave or Slave. For example, the previous virtualSlave database pool is configured here.

Amoeba startup

Start command:

Amoeba start (foreground startup allows you to view the error message

 

Run:/usr/local/amoeba/bin/amoeba. the following error may occur:
The stack size specified is too small, Specify at least 160 k cocould not create the Java virtual machine.

Modify the amoeba file, vi/usr/local/amoeba/bin/amoeba, and find the following file:
DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss128k"
Modify it:
DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss256k

Run/usr/local/amoeba/bin/amoeba again. If amoeba start | stop appears, you can start amoeba.
#/Usr/local/amoeba/bin/amoeba start

This command is started in the previous mode and will output the startup information. After checking that there is no error message, it will be interrupted and run in the background:

Nohup amoeba start & (background startup command)

 

When performing mysql operations, check the nohup log and find that cocould not create a validated object, cause: ValidateObject failed

Unable to connect, mainly possible:

Error in Database Password Configuration in dbServer. xml

Database Name error scheme

I also listed some errors,

For example, if I want to change mysql from the database to the master database, after you change all the configuration files, you can view the connection status of the previous mysql> show master status on the master database. What should I do?

You need to log on to mysql and execute the previous command

Mysql> slave stop;

Mysql> change master to master_host = '';

 

After the mysql master-slave cluster is completed, a database data is accidentally deleted from the master database and the slave database does not have this database. In this case, Slave_ SQL _Running: NO will change to NO. There are two solutions:

Record the value corresponding to File and Position.

mysql> show master status;+------------------+-----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+-----------+--------------+------------------+| mysql-bin.000013 | 330748356| | |+------------------+-----------+--------------+------------------+1 row in set (0.00 sec)

Perform manual synchronization on the slave server:

mysql> slave stop;mysql> change master to> master_log_file='mysql-bin.000013',> master_log_pos=330748356;1 row in set (0.00 sec)mysql> slave start;1 row in set (0.00 sec)

View the slave status again and find:

Slave_IO_Running: YesSlave_SQL_Running: Yes...Seconds_Behind_Master: 0

Solution 2:

mysql> slave stop;mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;mysql> slave start;