MySQL是开源的关系型数据库系统。复制(Replication)是从一台MySQL数据库服务器（主服务器master）复制数据到另一个服务器（从服务器slave）的一个进程
实验环境
CentOS6.8_x64
192.168.1.109 mysql_master/amoeba
192.168.1.111 mysql_slave
实验软件
epel-release-6-8.noarch.rpm
jdk-7u80-linux-x64.tar.gz
amoeba-mysql-3.0.5-RC-distribution.zip
软件安装
rpm -ivh epel-release-6-8.noarch.rpm
sed -i 's/^mirrorlist=https/mirrorlist=http/' /etc/yum.repos.d/epel.repo
wget -C http://mirrors.163.com/.help/CentOS6-Base-163.repo /etc/yum.repos.d
yum clean all
yum makecache -y
yum install -y ld-linux.so.2* glibc-* nss-* unzip*
yum install -y mysql mysql-devel mysql-server
unzip amoeba-mysql-3.0.5-RC-distribution.zip
mv amoeba-mysql-3.0.5-RC /usr/local/amoeba
tar zxvf jdk-7u80-linux-x64.tar.gz
mv jdk1.7.0_80 /usr/local/java
vim /etc/profile
export JAVA_HOME="/usr/local/java"
export CLASS_PATH="$JAVA_HOME/lib:$JAVA_HOME/jre/lib"
export PATH=".:$PATH:$JAVA_HOME/bin"
export CATALINA_HOME="/usr/local/tomcat"
export JAVA_HOME CATALINA_HOME 最后一行写入
source /etc/profile
java -version
java version "1.7.0_80"
Java(TM) SE Runtime Environment (build 1.7.0_80-b15)
Java HotSpot(TM) 64-Bit Server VM (build 24.80-b11, mixed mode)
You have new mail in /var/spool/mail/root 说明jdk安装成功
/etc/rc.d/init.d/mysqld restart
mysqladmin -uroot password 数据库密码
ps -e | grep mysqld
3045 pts/0 00:00:00 mysqld_safe
3147 pts/0 00:00:00 mysqld
netstat -tuplna | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3147/mysqld
mysql --version
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
mysql -uroot -p 数据库密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution
mysql> 以上操作master slave端都是一致
cp /etc/my.cnf /etc/my.cnf.bak
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=1
log-bin=mysql-bin master端添加2行配置
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
看到这个选项证明配置没有问题
cp /etc/my.cnf /etc/my.cnf.bak
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server_id = 2 slave添加几行配置
log-bin = mysql-bin
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
看到这个提示系统文件配置没问题
mysql -uroot -p
Enter password:
mysql> grant replication slave on *.* to 'root'@'192.168.1.111' identified by '123456';
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 323 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) master端操作
mysql -uroot -p slave端操作
Enter password:
mysql> stop slave;
mysql> reset slave;
mysql> change master to
-> master_host='192.168.1.109', master端ip
-> master_user='root', master端数据库用户名
-> master_password='123456', master端数据库密码
-> master_log_file='mysql-bin.000001',
-> master_log_pos=323; 这两个文件按照master端实际情况而定,次操作为slave端
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.109
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 323
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 468
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes 这两行一定要是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: 323
Relay_Log_Space: 624
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.00 sec)
netstat -tuplna | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 22233/mysqld
tcp 0 0 192.168.1.109:3306 192.168.1.111:41406 ESTABLISHED 22233/mysqld
测试主从同步
在master端执行{create inster}等操作查看数据库slave端是否同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.06 sec) master端
mysql> create database test_1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| test_1 |
+--------------------+
4 rows in set (0.00 sec) master端
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec) slave端
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| test_1 |
+--------------------+
4 rows in set (0.00 sec) slave端 看到此提示证明master已经可以和slave同步数据了
cp /usr/local/amoeba/conf/dbServers.xml /usr/local/amoeba/conf/dbServers.xml.bak
cp /usr/local/amoeba/conf/amoeba.xml /usr/local/amoeba/conf/amoeba.xml.bak
vim /usr/local/amoeba/conf/dbServers.xml
<!-- mysql port -->
<property name="port">3306</property>
<!-- mysql schema -->
<property name="schema">test</property> 初始化默认库
<!-- mysql user -->
<property name="user">test1</property> 创建用户连接test数据库
<property name="password">123456</property> 授权用户密码
</factoryConfig>
<poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">
<property name="maxActive">500</property> 最大连接数
<property name="maxIdle">500</property> 最大空闲连接数
<property name="minIdle">1</property> 最新空闲连接数
<dbServer name="writedb" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.109</property>
</factoryConfig>
</dbServer> master端ip_数据库读
<dbServer name="slave" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.111</property>
</factoryConfig>
</dbServer>
<dbServer name="myslave" virtual="true"> 定义虚拟数据库服务器
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
算法，1表示复制均衡，2表示权重，3表示HA
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="slave">server1</property>
</poolConfig>
</dbServer>
</amoeba:dbServers>
vim /usr/local/amoeba/conf/amoeba.xml
<property name="port">8066</property> 服务端口
<!-- bind ipAddress -->
<!--
<property name="ipAddress">127.0.0.1</property>
-->
<property name="user">root</property>
<property name="password">123456</property> 客户端连接amoeba 账号密码
/usr/local/amoeba/bin/launcher & 启动服务
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:329)
at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:239)
at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:409)
at org.codehaus.classworlds.Launcher.mainWithExitCode(Launcher.java:127)
at org.codehaus.classworlds.Launcher.main(Launcher.java:110)
2017-07-25 18:39:07 [INFO] Project Name=Amoeba-MySQL, PID=2510 , System shutdown ....
2017-07-25 18:39:31 [INFO] Project Name=Amoeba-MySQL, PID=2535 , starting...
log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2017-07-25 18:39:37,691 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA
netstat -unlpt | grep java
tcp 0 0 :::8066 :::* LISTEN 1602/java
实验中用root用户用于实验，生产环境不可做此操作。如果有3-5台mysql可以使用MHA mysql高可用解决方案
grant replication slave on *.* to 'root'@'192.168.1.111' identified by '123456';
192.168.1.111 为slave 数据库ip
123456 为slave 数据库密码
本文出自 “hades” 博客，谢绝转载！