优化MySQL之配置MySQL主从复制与读写分离

最新推荐文章于 2022-10-17 13:28:23 发布

礁之

最新推荐文章于 2022-10-17 13:28:23 发布

阅读量813

点赞数 2

分类专栏：数据库系列文章标签： mysql优化 mysql centos 数据库 amoeba

本文链接：https://blog.csdn.net/rzy1248873545/article/details/115090441

版权

数据库系列专栏收录该内容

19 篇文章 3 订阅

订阅专栏

一、为什么要使用MySQL的主从复制和读写分离

在实际环境中，如果数据库的读和写都在一个数据库服务器中，并发负载是无法满足实际要求的
故而引出了通过主从复制和读写分离来提高数据库的并发负载能力
架构图
MySQL主从复制原理（利用二进制日志进行备份数据）

二、实验环境

主机名	ip地址	扮演角色
master	192.168.100.1	mysql主服务器
slave	192.168.100.2	mysql从服务器
amoeba	192.168.100.3	代理服务器
app	192.168.100.4	应用服务器

三、实验步骤

（1）搭建时间服务器，为了防止时间错误无法备份

mysql主服务器

******（1）进行基础配置
[root@mysql ~]# hostnamectl set-hostname master
[root@mysql ~]# su
[root@master ~]# setenforce 0
setenforce: SELinux is disabled
[root@master ~]# systemctl stop firewalld
[root@master ~]# mount /dev/cdrom /media/cdrom/
mount: /dev/sr0 写保护，将以只读方式挂载
******（2）使用yum安装ntp时间服务
[root@master ~]# yum -y install ntp 
。。。。。。
完毕！
******（3）配置ntp时间服务，并且启动
[root@master ~]# cat <<a>> /etc/ntp.conf 
> server 127.127.1.0
> fudge 127.127.1.0 stratum 8
> a
[root@master ~]# systemctl restart ntpd
[root@master ~]# systemctl enable ntpd
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.
[root@master ~]# netstat -utpln | grep ntp （确认已经成功启动）
udp        0      0 192.168.100.1:123       0.0.0.0:*                           1728/ntpd           
udp        0      0 127.0.0.1:123           0.0.0.0:*                           1728/ntpd           
udp        0      0 0.0.0.0:123             0.0.0.0:*                           1728/ntpd           
udp6       0      0 fe80::7762:f351:dbf:123 :::*                                1728/ntpd           
udp6       0      0 ::1:123                 :::*                                1728/ntpd           
udp6       0      0 :::123                  :::*                                1728/ntpd

mysql从服务器

******（1）做基础配置
[root@centos7-011 ~]# hostnamectl set-hostname slave
[root@centos7-011 ~]# su
[root@slave ~]# systemctl stop firewalld
[root@slave ~]# setenforce 0
setenforce: SELinux is disabled
[root@slave ~]# mount /dev/cdrom /media/cdrom/
mount: /dev/sr0 写保护，将以只读方式挂载
******（2）使用yum安装ntp的客户端
[root@slave ~]# yum -y install ntpdate
。。。。。。
完毕！
[root@slave ~]# /usr/sbin/ntpdate 192.168.100.1 （与主服务器同步时间）

（2）在主服务器和从服务器上安装mysql

具体步骤请点击链接：mysql安装

（3）开启mysql，创建密码

主服务器和从服务器的操作相同

******（1）开启mysql
[root@master ~]# systemctl start mysqld
******（2）创建密码
[root@master ~]# mysqladmin -u root -p password 123.com

（4）配置MySQL主从复制

mysql主服务器

******（1）编辑mysql主配置文件
[root@master ~]# vim /etc/my.cnf
。。。。。。
server-id = 11 （这个是唯一的）
log-bin = master-bin （开启二进制日志）
log-slave-updates = true
保存退出
[root@master ~]# systemctl restart mysqld （重启mysql）
******（2）进入数据库配置
[root@master ~]# mysql -u root -p123.com
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  grant replication slave on *.* to 'myslave'@'%' identified by '123'; （赋权一个用户）
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges; （刷新权限）
Query OK, 0 rows affected (0.00 sec)

mysql> show master status; （查看主服务器的二进制日志，这里要记住file和position的值）
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      199 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> create database aaa; （创建测试数据库）
Query OK, 1 row affected (0.00 sec)

mysql> show databases; （查看是否创建成功）
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.39 sec)
mysql> quit  （退出数据库）
Bye

mysql从服务器

******（1）配置mysql配置文件
[root@slave ~]# vim /etc/my.cnf
。。。。。。
server-id = 22
relay-log = relay-log-bin （开启中继日志）
relay-log-index = slave-relay-bin.index
保存退出
[root@slave ~]# systemctl restart mysqld
******（2）进入数据库配置
[root@slave ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.36 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to master_host = '192.168.100.1',master_user = 'myslave',master_password = '123',master_log_file= 'master-bin.000001',master_log_pos = 199;  （199不能加引号）
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G; （查看无error即可）
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.1
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 491
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 576
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes  （这项必须为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: 491
              Relay_Log_Space: 747
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 11
                  Master_UUID: e65c9adc-3489-11eb-841f-000c2944addb
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show databases; （查看是否同步成功）
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.11 sec)

（5）配置代理服务器

******（1）做基础配置
[root@centos7-009 ~]# hostnamectl set-hostname amoeba
[root@centos7-009 ~]# su
[root@amoeba ~]# systemctl stop firewalld
[root@amoeba ~]# setenforce 0
setenforce: SELinux is disabled
[root@amoeba ~]# mount /dev/cdrom /media/cdrom/
mount: /dev/sr0 写保护，将以只读方式挂载
******（2）搭建java环境
[root@amoeba ~]# yum -y remove java （先检查是否安装了java，安装了的话得先删除）
已加载插件：fastestmirror
参数 java 没有匹配
不删除任何软件包
[root@amoeba ~]# ll （上传java脚本）
总用量 79936
-rw-------. 1 root root     1262 9月   3 2020 anaconda-ks.cfg
-rw-r--r--  1 root root 81849212 3月  23 16:46 jdk-6u14-linux-x64.bin
[root@amoeba ~]# chmod +x jdk-6u14-linux-x64.bin  （添加可执行权限）
[root@amoeba ~]# bash jdk-6u14-linux-x64.bin  （执行脚本）
。。。。。。 过程中输入一次 yes ， 最后输入回车
[root@amoeba ~]# ll
总用量 79936
-rw-------.  1 root root     1262 9月   3 2020 anaconda-ks.cfg
drwxr-xr-x  10 root root      321 3月  23 16:47 jdk1.6.0_14
-rwxr-xr-x   1 root root 81849212 3月  23 16:46 jdk-6u14-linux-x64.bin
[root@amoeba ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@amoeba ~]# cat <<a>> /etc/profile （在全局变量的文件下添加，优化环境变量，使linux内核可以识别java命令）
> 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
> export PATH=$PATH:$AMOEBA_HOME/bin
> a
[root@amoeba ~]# source /etc/profile （使刚写入的全局变量生效）
[root@amoeba ~]# java -version （查看java版本）
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)
******（3）编译安装amoeba
[root@amoeba ~]# mkdir /usr/local/amoeba （创建目录）
[root@amoeba ~]# ll （上传amoeba的源码包）
总用量 83024
-rw-r--r--  1 root root  3161433 3月  23 16:57 amoeba-mysql-binary-2.2.0.tar.gz
-rw-------. 1 root root     1262 9月   3 2020 anaconda-ks.cfg
-rwxr-xr-x  1 root root 81849212 3月  23 16:46 jdk-6u14-linux-x64.bin
[root@amoeba ~]# tar xf amoeba-mysql-binary-2.2.0.tar.gz  -C /usr/local/amoeba/ （解压）
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba （验证）
amoeba start|stop

（6）在mysql主服务器和从服务器赋权一个用户

******（1）在mysql主服务器上赋权一个用户
[root@master ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.36-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all on *.* to aaa@'192.168.100.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
******（2）查看mysql从服务器是否同步
[root@slave ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.36 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show grants for aaa@'192.168.100.%'; （发现已经成功同步）
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for aaa@192.168.100.%                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'aaa'@'192.168.100.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（7）修改amoeba的配置文件

[root@amoeba ~]# vim /usr/local/amoeba/conf/amoeba.xml 
。。。。。。
 30                                         <property name="user">amoeba</property>
 31 
 32                                         <property name="password">123456</property>
。。。。。。
 115                 <property name="defaultPool">master</property>
 116 
 117                 <property name="writePool">master</property> （注意删除<!--  -->的注释）
 118                 <property name="readPool">slaves</property>
。。。。。。
保存退出
[root@amoeba ~]# vim /usr/local/amoeba/conf/dbServers.xml 
。。。。。。
 25                         <!-- mysql user -->
 26                         <property name="user">aaa</property> （数据库赋权的用户）
 27 
 28                         <!--  mysql password -->  （把下面的--》注释移到这里）
 29                         <property name="password">123</property> （赋权用户的密码）
。。。。。。
 44 
 45         <dbServer name="master"  parent="abstractServer"> （修改为master）
 46                 <factoryConfig>
 47                         <!-- mysql ip --> 
 48                         <property name="ipAddress">192.168.100.1</property> （修改为mysql主服务器的ip）
。。。。。。
 51         （51——57为服务器的配置，如果有多个，可以由此格式写多台从服务器）
 52         <dbServer name="slave1"  parent="abstractServer"> （修改为slave1）
 53                 <factoryConfig>
 54                         <!-- mysql ip -->
 55                         <property name="ipAddress">192.168.100.2</property> （修改为mysql从服务器的地址）
 56                 </factoryConfig>
 57         </dbServer>
。。。。。。
 59         <dbServer name="slaves" virtual="true"> （修改为slaves）
。。。。。。
65                         <property name="poolNames">slave1</property> （修改为slave1，如果有多个使用,隔开）
保存退出
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba start & （启动amoeba在后台运行）
[3] 1954
[root@amoeba ~]# netstat  -utpln | grep 8066 （检查端口确认已经启动）
tcp6       0      0 :::8066                 :::*                    LISTEN      1954/java

（8）验证

******（1）打开第四台机器，先做基础配置
[root@centos7-010 ~]# hostnamectl set-hostname app
[root@centos7-010 ~]# su
[root@app ~]# systemctl stop firewalld
[root@app ~]# setenforce 0
setenforce: SELinux is disabled
[root@app ~]# mount /dev/cdrom /media/cdrom/
mount: /dev/sr0 写保护，将以只读方式挂载
******（2）使用yum安装mysql
[root@app ~]# yum -y install mysql （使用yum安装mysql）
。。。。。。
完毕！
******（3）远程连接amoeba代理服务器，创建测试数据库和表
[root@app ~]# mysql -u amoeba -p123456 -h 192.168.100.3 -P 8066 （代理服务器的用户使amoeba，但是代理服务器使用的是mysql主服务器赋权的用户aaa登录的）
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 906872446
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.02 sec)

MySQL [(none)]> create database bbb;
Query OK, 1 row affected (0.02 sec)

MySQL [(none)]> create table bbb.aaa(id int(10),name char(10));
Query OK, 0 rows affected (0.25 sec)
******（4）在mysql从服务器上查看结果，发现已经成功同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| bbb                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use bbb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_bbb |
+---------------+
| aaa           |
+---------------+
1 row in set (0.00 sec)

mysql> 
******（5）关闭从服务器的slave功能，分别在主服务器和从服务器上创建不同的数据
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
主服务器：
mysql> insert into bbb.aaa values(1,'aaa');
Query OK, 1 row affected (0.00 sec)
从服务器：
mysql> insert into bbb.aaa values(2,'bbb');
Query OK, 1 row affected (0.00 sec)
******（6)使用应用客户端查看
MySQL [(none)]> select * from bbb.aaa; （发现只有从服务器创建的数据）
+------+------+
| id   | name |
+------+------+
|    2 | bbb  |
+------+------+
1 row in set (0.05 sec)
******（7）使用应用客户端写入数据
MySQL [(none)]> insert into bbb.aaa values(3,'ccc');
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> select * from bbb.aaa; （查询的时候发现没有刚刚创建的数据）
+------+------+
| id   | name |
+------+------+
|    2 | bbb  |
+------+------+
1 row in set (0.00 sec)
******（8）使用mysql主服务器查询
mysql> select * from bbb.aaa; （发现没有从服务器创建的数据）
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    3 | ccc  |
+------+------+
2 rows in set (0.00 sec)

总结

当应用客户端写入数据时，amoeba代理服务器会到mysql主服务器上进行存储，当应用客户端读取数据时，amoeba代理服务器回到mysql从服务器上查询数据 （从服务器可以有多台） ，实现了读写分离，当mysql从服务器开启slave功能时，主服务器和从服务器可以保证数据的一致性

礁之

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
5
评论
优化MySQL之配置MySQL主从复制与读写分离

目录一、为什么要使用MySQL的主从复制和读写分离二、实验环境三、实验步骤（1）搭建时间服务器，为了防止时间错误无法备份（2）在主服务器和从服务器上安装mysql（3）开启mysql，创建密码（4）配置MySQL主从复制一、为什么要使用MySQL的主从复制和读写分离在实际环境中，如果数据库的读和写都在一个数据库服务器中，并发负载是无法满足实际要求的故而引出了通过主从复制和读写分离来提高数据库的并发负载能力MySQL主从复制原理（利用二进制日志进行备份数据）二、实验环境主机名ip地址
复制链接

扫一扫