mysql主从复制读写分离
Centos01:主服务器
Centos02:从服务器
Centos03::从服务器
一.配置主从复制
1.配置ntp服务器
安装ntp
[root@centos ~]# yum -y install ntp
修改ntp配置文件
[root@centos ~]# vim /etc/ntp.conf
18 restrict 192.168.100.0 mask 255.255.255.0 nomodify notrap
19 server 127.127.1.0
20 fudge 127.127.1.0 stratum 8
启动服务设置开机自动启动
[root@centos ~]# systemctl start ntpd
[root@centos ~]# systemctl enable ntpd
其他服务器同步时间
[root@centos ~]# ntpdate 192.168.100.10
2.安装mysql(01,02,05)
安装依赖
[root@centos ~]# yum -y install ncurses-devel.x86_64 cmake.x86_64
创建管理mysql组,将创建的mysql用户加入到组
[root@centos ~]# groupadd mysql
[root@centos ~]# useradd -M -s /sbin/nologin -g mysql mysql
切换Linux光盘,解压文件
[root@centos ~]# tar zxvf /mnt/mysql-5.5.22.tar.gz -C /usr/src/
将文件拷贝到20.0和50.0
[root@centos ~]# scp /mnt/mysql-5.5.22.tar.gz root@192.168.100.20:/usr/src/
配置mysql
[root@centos ~]# cd /usr/src/mysql-5.5.22/
[root@centos mysql-5.5.22]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET_CHARSET=utf8 -DDEFAU
LT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
02,05:
[root@centos ~]# tar zxvf /usr/src/mysql-5.5.22.tar.gz -C /usr/src/
[root@centos05 ~]# cd /usr/src/mysql-5.5.22/
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
-DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
编译安装
[root@centos mysql-5.5.22]# make && make install
生成mysql主配置文件
[root@centos mysql-5.5.22]# cp support-files/my-medium.cnf /etc/my.cnf
生成mysql服务控制文件
[root@centos mysql-5.5.22]# cp support-files/mysql.server /etc/init.d/mysqld
[root@centos mysql-5.5.22]# chmod +x /etc/init.d/mysqld
[root@centos ~]# chkconfig --add mysqld
[root@centos ~]# chkconfig --level 35 mysqld on
优化命令
[root@centos mysql-5.5.22]# vim /etc/profile
PATH=$PATH:/usr/local/mysql/bin
[root@centos mysql-5.5.22]# source /etc/profile
初始化服务:
[root@centos ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/us
r/local/mysql/data
启动服务
[root@centos ~]# systemctl start mysqld
查看端口
[root@centos ~]# netstat -anptu | grep 3306
为root用户设置密码
[root@centos ~]# mysqladmin -uroot password ‘pwd@123’
登录
[root@centos ~]# mysql -uroot -ppwd@123
3.修改主mysql配置文件
修改配置文件
[root@centos ~]# vim /etc/my.cnf
重新启动MySQL服务加载配置文件
[root@centos ~]# systemctl restart mysqld
3.修改从mysql主配置文件
02:
修改从服务器主配置文件
[root@centos ~]# vim /etc/my.cnf
重启MySQL服务加载配置文件
[root@centos ~]# systemctl restart mysqld
05:
[root@centos05 ~]# scp root@192.168.100.20:/etc/my.cnf /etc/
[root@centos ~]# vim /etc/my.cnf
重启MySQL服务加载配置文件
[root@centos05 ~]# systemctl restart mysqld
4.配置主从复制
创建主从复制账户
[root@centos ~]# mysql -uroot -ppwd@123
mysql> grant replication slave on . to ‘slave’@‘192.168.100.%’ identified by ‘pwd@123’;
mysql> select user,host from mysql.user;
mysql> select user,host from mysql.user where user=‘slave’;
查看主MySQL状态
mysql> show master status;
配置从MySQL数据库复制账户
20:
[root@centos ~]# mysql -uroot -ppwd@123
mysql> change master to master_host=‘192.168.100.10’,master_user=‘slave’,master_password=‘pwd@123’,master_log_fi
le=‘mysql-bin.000005’,master_log_pos=263;
启动从数据库复制进程
mysql> start slave;
查看从MySQL复制状态
mysql> show slave status\G;
50:
[root@centos05 ~]# mysql -uroot -ppwd@123
mysql> change master to master_host=‘192.168.100.10’,master_user=‘slave’,master_password=‘pwd@123’,master_log_fi
le=‘mysql-bin.000005’,master_log_pos=263;
mysql> start slave;
mysql> show slave status\G;
验证:主服务器
mysql> show databases;
mysql> create database benet;
mysql> show databases;
20,50,查看
Centos01:主服务器
Centos02:读服务器
Centos05:读服务器
Centos07:amoeba
Centos08:客户端
二.配置读写分离
编写hosts文件
[root@centos05 ~]# vim /etc/hosts
拷贝给其他服务器
[root@centos05 ~]# scp /etc/hosts root@192.168.100.20:/etc/
1.安装jdk和amoeba(07,Linux盘)
解压jdk
[root@centos07 ~]# cp /mnt/jdk-6u14-linux-x64.bin ./
[root@centos07 ~]# ./jdk-6u14-linux-x64.bin
移动jdk位置
[root@centos07 ~]# mv jdk1.6.0_14/ /usr/local/java
创建amoba配置文件
[root@centos07 ~]# mkdir /usr/local/amoeba
[root@centos07 ~]# tar zxvf /mnt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
配置环境变量
[root@centos07 ~]# vim /etc/profile.d/java.sh
export JAVA_HOME=/usr/local/java
export CLASSPATH=
C
L
A
S
S
P
A
T
H
:
CLASSPATH:
CLASSPATH:JAVA_HOME/lib:
J
A
V
A
H
O
M
E
/
j
r
e
/
l
i
b
e
x
p
o
r
t
P
A
T
H
=
JAVA_HOME/jre/lib export PATH=
JAVAHOME/jre/libexportPATH=JAVA_HOME/lib/:
J
A
V
A
H
O
M
E
/
j
r
e
/
b
i
n
:
JAVA_HOME/jre/bin:
JAVAHOME/jre/bin:PATH:
H
O
M
E
/
b
i
n
/
e
x
p
o
r
t
A
M
O
E
B
A
=
/
u
s
r
/
l
o
c
a
l
/
a
m
o
e
b
a
e
x
p
o
r
t
P
A
T
H
=
HOME/bin/ export AMOEBA=/usr/local/amoeba export PATH=
HOME/bin/exportAMOEBA=/usr/local/amoebaexportPATH=PATH:AMOEBA/bin
[root@centos07 ~]# source /etc/profile.d/java.sh
查看jdk版本
[root@centos07 ~]# java -version
2.配置读写分离(01)
创建amoeba访问MySQL是数据库读写分离账户
[root@centos ~]# mysql -uroot -ppwd@123
mysql> grant all on . to ‘write’@‘192.168.100.%’ identified by ‘pwd@123’;
查看从服务器(02,05)
mysql> select user,host from mysql.user;
修改amoeba配置文件设置登录amoeba账户(07)
[root@centos07 ~]# cp /usr/local/amoeba/conf/amoeba.xml /usr/local/amoeba/conf/amoeba.xml.bak
[root@centos07 ~]# vim /usr/local/amoeba/conf/amoeba.xml
修改配置文件添加读写节点
[root@centos07 ~]# cp /usr/local/amoeba/conf/dbServers.xml /usr/local/amoeba/conf/dbServers.xml.bak
[root@centos07 ~]# vim /usr/local/amoeba/conf/dbServers.xml
启动amoeba
[root@centos07 ~]# /usr/local/amoeba/bin/amoeba start&
[root@centos07 ~]# netstat -anptu | grep 8066
3.配置MySQL客户端(08)
安装MySQL客户端
[root@centos08 ~]# yum -y install mariadb
登录amoeba
[root@centos08 ~]# mysql -h 192.168.100.70 -P8066 -uamoeba -ppwd@123
MySQL [(none)]> create database xsh;
100.20查看:
读写分离:
MySQL [(none)]> create table xsh.student (姓名 char(5),性别 char(3),电话号码 char(11));
MySQL [(none)]> insert into xsh.student values (‘bob’,‘男’,‘12345678901’);
MySQL [(none)]> select * from xsh.student;
从服务器服务停止
mysql> stop slave;
验证:
主服务器查看
从服务器服务启动,客户端可查看
mysql> start slave;
MySQL [(none)]> select * from xsh.student;