mysql主从复制详细配置

主从同步

主服务器20.0.0.100

yum -y install ntpvim /etc/ntp.conf ###添加两行
server 127.127.1.0
fudge 127.127.1.0 stratum 8
systemctl restart ntpd
systemctl enable ntpd

从服务器上配置ntp时间同步20.0.0.31 20.0.0.32

yum -y install ntpdate
ntpdate 20.0.0.100
添加计划任务，自动同步时间
crontab -e
*/2 * * * * /usr/sbin/ntpdate 20.0.0.100 >> /var/log/ntpdate.log

主从服务器安装mysql，省略。。。

配置主从同步

主mysql服务器20.0.0.100
vim /etc/my.cnf
server-id = 11 #修改为11
log-bin = master-bin #添加开启二进制日志
log-slave-updates = true #添加允许从服务器二进制日志同步

systemctl restart mysqld

登陆数据库授权从服务器
mysql -u root -p
mysql> grant replication slave on . to ‘myslave’@‘20.0.0.%’ identified by ‘abc123’; #授予复制权限
mysql> flush privileges; #刷新
mysql> show master status;
±------------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±------------------±---------±-------------±-----------------±------------------+
| master-bin.000001 | 604 | | | |
±------------------±---------±-------------±-----------------±------------------+
记住这里的Position值，后面配置从服务器需要用到

从mysql服务器20.0.0.31 20.0.0.32

vim /etc/my.cnf
server-id = 22 #多从台服务器，只需要保证server-id 不同即可，其它配置都一样
relay-log = relay-log-bin #开启中继日志，从主服务器上复制二进制日志文件到本地
relay-log-index = slave-relay-bin.index #定义relay-log的未知和文件名
systemctl restart mysqld

登陆数据库配置Slave同步
mysql -u root -p
mysql> change master to master_host=‘20.0.0.100’,master_user=‘myslave’,master_password=‘abc123’,master_log_file=‘master-bin.000001’,master_log_pos=604; #此处配置信息与主服务器授权时应一致#此处用到主服务器的Position值
mysql> start slave; #当再次开启同步时，必须要先停止stop slave;否则会发送错误
mysql> show slave status\G #\G以竖的格式显示

读写分离与负载均衡

前端amoeba服务器上 20.0.0.33 你可以把它理解成调度器

安装JDK 上传JDK到opt目录

cd /opt
tar xzvf jdk-8u144-linux-x64.tar.gz
cp -rv jdk1.8.0_144/ /usr/local/java
vim /etc/profile ###在后面加入
export JAVA_HOME=/usr/local/java #指定java目录
export JRE_HOME=/usr/local/java/jre #java虚拟机，简称JVM
export PATH=$PATH:/usr/local/java/bin
export CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/lib #类文件库

source /etc/profile
java -version

unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/
mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
chmod -R 755 /usr/local/amoeba/
vim /usr/local/amoeba/jvm.properties
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k" ###修改

制作amoeba管理脚本##

vi /etc/init.d/amoeba
#!/bin/bash
#chkconfig: 35 62 62

export JAVA_HOME=/usr/local/java
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
NAME=Amoeba
AMOEBA_BIN=/usr/local/amoeba/bin/launcher
SHUTDOWN_BIN=/usr/local/amoeba/bin/shutdown
PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid
SCRIPTNAME=/etc/init.d/amoeba

case “$1” in
start)
echo -n “Starting $NAME… "
$AMOEBA_BIN
echo " done”
;;
stop)
echo -n “Stoping $NAME… "
$SHUTDOWN_BIN
echo " done”
;;
restart)
$SHUTDOWN_BIN
sleep 1
$AMOEBA_BIN
;;
*)
echo “Usage: $SCRIPTNAME {start|stop|restart}”
exit 1
;;
esac

chmod +x /etc/init.d/amoeba
chkconfig --add amoeba
service amoeba start

在三台mysql数据库中为amoeba授权#

主服务器master20.0.0.100

mysql> create database test;
mysql> GRANT ALL ON *.* TO test@‘20.0.0.%’ IDENTIFIED BY ‘abc123’;
mysql> FLUSH PRIVILEGES;

amoeba服务器20.0.0.33

vi conf/amoeba.xml
<property name=“readPool”>slaves #85 读取池名称
<property name=“needParse”>true #86
vim conf/dbServers.xml #26-29的注释去掉
<property name=“schema”>test #####数据库中要有此处定义的数据库，否则客户端连接后会报错，默认我用test数据库，创建是test数据库不要弄错了
<property name=“user”>test #设置授权账号，与三台mysql服务器amoeba授权对应
<property name=“password”>abc123 #设置授权密码，与三台mysql服务器amoeba授权对应
<dbServer name=“master” parent=“abstractServer”> #####name后面改成 master
<factoryConfig>
<!-- mysql ip -->
<property name=“ipAddress”>192.168.100.42 ####ipAddress加上主mysql的IP地址
</factoryConfig>
</dbServer>

<dbServer name=“slave1” parent=“abstractServer”> #####name后面改成 slave1
<factoryConfig>
<!-- mysql ip -->
<property name=“ipAddress”>192.168.100.43 ###ipAddress加上从1mysql的IP地址
</factoryConfig>
</dbServer>

<dbServer name=“slave2” parent=“abstractServer”> #####name后面改成 slave2
<factoryConfig>
<!-- mysql ip -->
<property name=“ipAddress”>192.168.100.44 ###ipAddress加上从2mysql的IP地址
</factoryConfig>
</dbServer>

<dbServer name=“slaves” virtual=“true”> #####name后面改成 slaves，与amoeba.xml中读取池的名字对应
<poolConfig class=“com.meidusa.amoeba.server.MultipleServerPool”>
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA–>
<property name=“loadbalance”>1

<!-- Separated by commas,such as: server1,server2,server1 -->
<property name=“poolNames”>slave1,slave2 #####poolNames后面改成 slave1,slave2
</poolConfig>
</dbServer>

service amoeba restart
netstat -anpt | grep java

测试用客户端 20.0.0.99##

service firewalld stop
setenforce 0
yum install -y mysql
mysql -u amoeba -pabc123 -h 20.0.0.33 -P8066 ##### 登录20.0.0.33 amoeba服务端

在主mysql上创建数据库 20.0.0.100

[root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123
mysql> use test;
create table zhang1 (id int(10),name varchar(10),address varchar(20));
mysql> use test;
Database changed
mysql> create table zhang1 (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.05 sec）

###在从1 mysql关闭同步 20.0.0.31###
[root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> insert into zhang1 values(‘2’,‘zhang’,‘this_is_slave1’);
Query OK, 1 row affected (0.00 sec)

###在从2 mysql关闭同步 20.0.0.32###
[root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> insert into zhang1 values(‘3’,‘zhang’,‘this_is_slave2’);
Query OK, 1 row affected (0.00 sec)

###在主mysql上插入数据 20.0.0.31 ###
[root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123
mysql> use test;
Database changed
mysql> insert into zhang1 values(‘1’,‘zhang’,‘this_is_master’);
Query OK, 0 rows affected (0.05 sec）

######登录客户端 20.0.099#####
[root@localhost ~]# mysql -u amoeba -pabc123 -h 20.0.0.33 -P8066
MySQL [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| bdqn |
| db_test |
| mysql |
| performance_schema |
| sys |
| test | #####发现数据库test
±-------------------+
7 rows in set (0.01 sec)

MySQL [(none)]> use test; ####进入数据库test
Database changed
MySQL [test]> select * from zhang1; ###查看zhang信息 发现进入slave1 数据中的数据
| id | name | address |
±-----±------±---------------+
| 2 | zhang | this_is_slave1 |
±-----±------±---------------+
1 row in set (0.00 sec)

MySQL [test]> select * from zhang1; ###查看zhang信息 发现进入slave2 数据中的数据
±-----±------±---------------+
| id | name | address |
±-----±------±---------------+
| 3 | zhang | this_is_slave2 |
±-----±------±---------------+
1 row in set (0.01 sec)

#########上述实验，实现数据读取负载均衡#######

###在客户机上 20.0.0.99 继续写输数据，按照正常的规则应该是slave1 和slave2是看不见的
MySQL [(none)]> use test;
Database changed
MySQL [test]> insert into zhang1 values(‘4’,‘zhang’,‘write_test’);
Query OK, 1 row affected (0.01 sec)

######登录mastar 20.0.0.100 ###
[root@localhost ~]# mysql -u root -p
mysql> use test;
Database changed
mysql> select * from zhang1;
±-----±------±---------------+
| id | name | address |
±-----±------±---------------+
| 1 | zhang | this_is_master |
| 4 | zhang | write_test |
±-----±------±---------------+
2 rows in set (0.00 sec)

######登录slave1 20.0.0.31###
[root@localhost ~]# mysql -u root -p
mysql> use test;
Database changed
mysql> select * from zhang1;
±-----±------±---------------+
| id | name | address |
±-----±------±---------------+
| 2 | zhang | this_is_slave1 |
±-----±------±---------------+
1 row in set (0.00 sec)

######登录slave2 20.0.0.32 ###
[root@localhost ~]# mysql -u root -p
mysql> use test;
Database changed
mysql> select * from zhang1;
±-----±------±---------------+
| id | name | address |
±-----±------±---------------+
| 3 | zhang | this_is_slave2 |
±-----±------±---------------+
1 row in set (0.00 sec)