Linux——MySQL主从复制与读写分离

实验环境

虚拟机 3台 centos7.9

网卡NAT模式 数量 1

组件包mysql-5.6.36.tar.gz  cmake-2.8.6.tar.gz

设备	IP	备注
Centos01	192.168.223.123	Amoeba
Centos02	192.168.223.124	Master
Centos03	192.168.223.125	Slave

MySQL安装 

主从同时操作

安装所需要的组件包

[root@chicken ~]# yum -y install gcc gcc-c++ ntp vim net-tools ncurses-devel autoconf

上传mysql安装包mysql-5.6.36.tar.gz和cmake-2.8.6.tar.gz

解压，编译安装cmake

[root@chicken ~]# tar zxf cmake-2.8.6.tar.gz 
[root@chicken ~]# cd cmake-2.8.6/
[root@chicken cmake-2.8.6]# ./configure && gmake && gmake install

解压mysql并编译安装mysql

[root@chicken ~]# tar zxf mysql-5.6.36.tar.gz 
[root@chicken ~]# cd mysql-5.6.36/
[root@chicken mysql-5.6.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all
[root@chicken mysql-5.6.36]# make && make install

创建mysql用户和组

[root@chicken ~]# groupadd mysql
[root@chicken ~]# useradd -M -s /sbin/nologin mysql -g mysql

修改mysql安装目录权限，复制默认配置文件

[root@chicken ~]# chown -R mysql:mysql /usr/local/mysql
[root@chicken ~]# cp support-files/my-default.cnf /etc/my.cnf

MySQL数据库初始化

[root@chicken ~]# ./scripts/mysql_install_db --user=mysql  --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

添加mysql到系统服务

[root@chicken ~]# cp support-files/mysql.server /etc/init.d/mysqld
[root@chicken ~]# chmod +x /etc/init.d/mysqld
[root@chicken ~]# chkconfig --add mysqld
####启动MySQL服务#######
[root@chicken ~]# systemctl start mysqld

添加mysq操作指令关联系统

[root@chicken ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile && . /etc/profile
####用户登录测试#####
[root@chicken ~]# mysql -u root

MySql主从复制

主配置

[root@chicken mysql]# cat << EOF >> /etc/ntp.conf 
> server 127.127.1.0
> fudge 127.127.1.0 stratum 8
> EOF
[root@chicken mysql]# systemctl start ntpd

Ntp服务slave从配置 

[root@chicken ~]# yum -y install ntpdate
[root@chicken ~]# ntpdate 192.168.223.124  ##主服务的IP地址

主服务master配置

[root@chicken ~]# sed -i 's/# server_id = .*/server_id = 11/' /etc/my.cnf
[root@chicken ~]# sed -i '/server_id = 11/a\log_bin = master-bin' /etc/my.cnf
[root@chicken ~]# sed -i '/log_bin = master-bin/a\log-slave-updates = true' /etc/my.cnf
[root@chicken ~]# systemctl restart mysqld

创建授权用户myslave

[root@chicken ~]# mysql -u root
mysql> grant replication slave on *.* to 'root'@'192.168.223.%' identified by '123456';
mysql> flush privileges;
mysql> show master status;

 从服务器配置

[root@chicken ~]# sed -i 's/# server_id = .*/server_id = 22/' /etc/my.cnf
[root@chicken ~]# sed -i '/server_id = 22/a\relay-log = relay-log-bin' /etc/my.cnf
[root@chicken ~]# sed -i '/relay-log = relay-log-bin/a\relay-log-index = slave-relay-bin.index' /etc/my.cnf
####重启MySql服务######
[root@chicken ~]# systemctl restart mysqld

连接主服务器

[root@chicken ~]# mysql -u root
mysql> change master to master_host='192.168.160.51',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=120;
mysql> start slave;
mysql> show slave status\G;

MySQL读写分离

部署Java运行环境，上传安装包jdk-6u14-linux-x64.bin

[root@chicken ~]# chmod +x jdk-6u14-linux-x64.bin
[root@chicken ~]# ./jdk-6u14-linux-x64.bin
[root@chicken ~]# mv jdk1.6.0_14 /usr/local/jdk1.6

添加Java运行环境变量

[root@chicken ~]# vim /etc/profile
####在export下面提添加变量######
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:/$JAVA_HOME/jre/bin:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba/
[root@chicken ~]# java -version

部署amoeba，解压amoeba软件包并创建主目录

[root@chicken ~]# mkdir /usr/local/amoeba
[root@chicken ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba

编辑主配置文件

[root@chicken ~]# cd /usr/local/amoeba/
[root@chicken amoeba]# vim conf/amoeba.xml 
<property name="user">root</property>            客户端连接的用户
<property name="password">123456</property>      客户端连接密码

取消注释
<property name="writePool">server1</property>      写入数据池名称
<property name="readPool">server2</property>        读取数据池名称

编辑数据库连接文件

[root@chicken amoeba]# vim conf/dbServers.xml 
<property name="port">3306</property>
<property name="schema">test</property> 		默认库 名称
<property name="user">root</property>          链接数据库用户名
<property name="password">123456</property>  链接数据库密码  

修改ipaddress 127.0.0.1 为mysql 主和从的IP
  <dbServer name="server1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.223.124</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="server2"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.223.125</property>
                </factoryConfig>
        </dbServer>

启用amoeba

[root@chicken amoeba]# nohup ./bin/amoeba start 2>&1 &

查看端口