Linux环境下部署MySQL + Atlas --- 实现读写分离

最新推荐文章于 2023-02-01 10:23:42 发布

pekeka

最新推荐文章于 2023-02-01 10:23:42 发布

阅读量349

点赞数

文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/p12361465/article/details/128243214

版权

ip	配置	主机名
192.168.88.67	rhel7	server67（Atlas代理服务）
192.168.88.69	rhel7	server69（主MySQL数据库）
192.168.88.70	rhel7	server70（从MySQL数据库）

在server69上配置mysql5.7数据库

[root@server69 ~]# ll
总用量 50484
-rw-------.  1 root root      1455 11月 22 04:51 anaconda-ks.cfg
drwxr-xr-x  35 7161 31415     4096 6月  22 2017 mysql-5.7.19
-rw-r--r--   1 root root  51686763 9月   4 10:54 mysql-5.7.19.tar.gz
[root@server69 ~]# cd mysql-5.7.19
[root@server69 mysql-5.7.19]# ll   ###准备好这些包并进行解压
-rw-r--r--  1 root root   25090196 9月   4 10:54 mysql-community-client-5.7.20-1.el7.x86_64.rpm
-rw-r--r--  1 root root     278300 9月   4 10:54 mysql-community-common-5.7.20-1.el7.x86_64.rpm
-rw-r--r--  1 root root    3840412 9月   7 09:46 mysql-community-devel-5.7.24-1.el7.x86_64.rpm
-rw-r--r--  1 root root    2238604 9月   4 10:54 mysql-community-libs-5.7.20-1.el7.x86_64.rpm
-rw-r--r--  1 root root  171597916 9月   4 10:59 mysql-community-server-5.7.20-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]# rpm -ivh mysql-community-common-5.7.24-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]# rpm -ivh mysql-community-libs-5.7.24-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]# rpm -ivh mysql-community-client-5.7.24-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]#rpm -ivh mysql-community-devel-5.7.24-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]# rpm -ivh mysql-community-server-5.7.24-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]# systemctl enable mysqld
[root@server69 mysql-5.7.19]# systemctl start mysqld
[root@server69 mysql-5.7.19]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 四 2022-12-08 17:25:46 CST; 4ms ago
     Docs: man:mysqld(8)
[root@server69 mysql-5.7.19]# cat /var/log/mysqld.log | grep "password"   ###查找初始密码
2022-12-08T09:25:44.126928Z 1 [Note] A temporary password is generated for root@localhost: qvB.wjoyx2qu
[root@server69 mysql-5.7.19]# mysql -uroot -p
Enter password: qvB.wjoyx2qu
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20
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> ALTER USER 'root'@'localhost' identified by "GuangZhou_123";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to pekeka@'%' identified by "GuangZhou_123";  ###增加用户并为其添加权限
mysql> use mysql
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>  select user, host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| pekeka        | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
mysql> create database pekeka;  ##创建一个数据库用于测试读写分离
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
[root@server69 mysql-5.7.19]# > /etc/my.cnf
[root@server69 mysql-5.7.19]# vim /etc/my.cnf  
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
#主从复制配置 
sync_binlog=1
#需要备份的数据库 
binlog-do-db=pekeka
#不需要备份的数据库 
binlog-ignore-db=mysql
#启动二进制文件 
log-bin=mysql-bin
#服务器ID 
server-id=1
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@server69 mysql-5.7.19]# systemctl restart mysqld
[root@server69 mysql-5.7.19]# mysql -uroot -pGuangZhou_123  ##配置主从设置
mysql> grant replication slave on *.* to 'pekeka'@'%' identified by 'GuangZhou_123';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      591 | pekeka       | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在server70上配置mysql5.7数据库

按如上步骤进行安装mysql5.7后
[root@server70 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server-id=2
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-name-resolve
[root@server70 ~]# mysql -uroot -pGuangZhou_123
mysql> change master to master_host='192.168.88.69',
    ->      master_user='pekeka',
    ->      master_password='GuangZhou_123',
    ->      master_port=3306,
    ->      master_log_file='mysql-bin.000001',
    ->      master_log_pos=591,
    ->      master_connect_retry=10;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.88.69
                  Master_User: pekeka
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 591
               Relay_Log_File: server70-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: 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: 591
              Relay_Log_Space: 530
              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: 1
                  Master_UUID: 4a623bc9-76da-11ed-813d-00505632aa47
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

在server67中进行Atlas配置

[root@server67 ~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
准备中...                          ################################# [100%]
正在升级/安装...
   1:Atlas-2.2.1-1                    ################################# [100%]
[root@server67 ~]# cd /usr/local/mysql-proxy/
[root@server67 mysql-proxy]# ll
总用量 0
drwxr-xr-x 2 root root  75 12月  8 19:07 bin   
drwxr-xr-x 2 root root  22 12月  8 19:07 conf
drwxr-xr-x 3 root root 331 12月  8 19:07 lib
drwxr-xr-x 2 root root   6 12月 17 2014 log
[root@server67 bin]# ll
总用量 44
-rwxr-xr-x 1 root root  9696 12月 17 2014 encrypt  #encrypt”是用来对MySQL密码进行加密的，在配置的时候会用到
-rwxr-xr-x 1 root root 23564 12月 17 2014 mysql-proxy  # mysql-proxy是MySQL自己的读写分离代理
-rwxr-xr-x 1 root root  1552 12月 17 2014 mysql-proxyd #用于服务的启动、重启、停止，都是用他来执行的
-rw-r--r-- 1 root root     6 12月 17 2014 VERSION

[root@server67 bin]# cd ..;cd conf
[root@server67 conf]# ll
总用量 4
-rw-r--r-- 1 root root 2810 12月 17 2014 test.cnf # test.cnf只有一个文件，用来配置代理的
[root@server67 mysql-proxy]# cd bin/
[root@server67 bin]# ll
总用量 44
-rwxr-xr-x 1 root root  9696 12月 17 2014 encrypt
-rwxr-xr-x 1 root root 23564 12月 17 2014 mysql-proxy
-rwxr-xr-x 1 root root  1552 12月 17 2014 mysql-proxyd
-rw-r--r-- 1 root root     6 12月 17 2014 VERSION
[root@server67 bin]# ./encrypt GuangZhou_123   #对密码进行加密
5/1uUDUoAX5yNgxLEkQlUQ==
[root@server67 bin]# cd /usr/local/mysql-proxy/conf/
[root@server67 conf]# ll
总用量 4
-rw-r--r-- 1 root root 2810 12月 17 2014 test.cnf
[root@server67 conf]# vim test.cnf ###修改一下三处地方
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 192.168.88.69:3306

#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
proxy-read-only-backend-addresses = 192.168.88.70:3306@1

#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户
名和加密密码！
pwds = pekeka:5/1uUDUoAX5yNgxLEkQlUQ==
[root@server67 bin]# ./mysql-proxyd test start
OK: MySQL-Proxy of test is started
[root@server67 bin]# ./mysql-proxyd test status
MySQL-Proxy of test is running (12602)
MySQL-Proxy of test is running (12603)
[root@server67 bin]# netstat -pantul | grep 1234
tcp        0      0 0.0.0.0:1234            0.0.0.0:*               LISTEN      12603/mysql-proxy   
[root@server67 bin]# netstat -pantul | grep 2345
tcp        0      0 0.0.0.0:2345            0.0.0.0:*               LISTEN      12603/mysql-proxy 
[root@server67 bin]# yum install mariadb  ###需要有mysql命令既可以
[root@server67 bin]# which mysql
/usr/bin/mysql
[root@server67 bin]# mysql -h127.0.0.1 -P1234 -upekeka -pGuangZhou_123  #利用工作端口来连接数据库

读写分离测试

在MySQL主库和从库开启general_log日志

mysql>  show variables like '%general_log%';
+------------------+-----------------------------+
| Variable_name    | Value                       |
+------------------+-----------------------------+
| general_log      | OFF                         |
| general_log_file | /var/lib/mysql/server69.log |
+------------------+-----------------------------+
2 rows in set (0.01 sec)

mysql> set global general_log = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%general_log%';
+------------------+-----------------------------+
| Variable_name    | Value                       |
+------------------+-----------------------------+
| general_log      | ON                          |
| general_log_file | /var/lib/mysql/server69.log |
+------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql>



[root@server67 bin]# mysql -h127.0.0.1 -P1234 -upekeka -pGuangZhou_123
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.81-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 |
| mysql              |
| pekeka             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

MySQL [(none)]> use pekeka;
MySQL [pekeka]> select * from book;  ##进行读操作
+------------------+--------+-------+
| name             | price  | pages |
+------------------+--------+-------+
| Linux            |     66 |   666 |
| pekeka           |     77 |   666 |
| Linux            |     30 |   666 |
| Cloud Computing  |     60 |   666 |
| Operation System |     80 |   666 |
| Linux            |     66 |   666 |
| pekeka           |     77 |   666 |
| Linux            |     30 |   666 |
| Cloud Computing  |     60 |   666 |
| Operation System |     80 |   666 |
| Artifgence       | 111111 |     1 |
| nginx            |     66 |   666 |
+------------------+--------+-------+
12 rows in set (0.00 sec)
MySQL [pekeka]> INSERT INTO book(name,price,pages) VALUES('pekeka','66','666');    ###进行写操作
Query OK, 1 row affected (0.01 sec)

分别对69和70两天机器进行查看，证明读写分离完成
[root@server69 ~]# tail -f /var/lib/mysql/server69.log
2022-12-08T12:10:32.838228Z    28 Query SET CHARACTER_SET_CLIENT=utf8
2022-12-08T12:10:32.838403Z    28 Init DB   pekeka
2022-12-08T12:10:32.838640Z    28 Query INSERT INTO book(name,price,pages) VALUES('nginx','66','666')
2022-12-08T12:19:18.671904Z    29 Connect   pekeka@192.168.88.67 on  using TCP/IP
2022-12-08T12:19:18.672520Z    29 Query SET CHARACTER_SET_CONNECTION=utf8
2022-12-08T12:19:18.672784Z    29 Query SET CHARACTER_SET_RESULTS=utf8
2022-12-08T12:19:18.673055Z    29 Query SET CHARACTER_SET_CLIENT=utf8
2022-12-08T12:19:18.673363Z    29 Init DB   pekeka
2022-12-08T12:19:18.673678Z    29 Query INSERT INTO book(name,price,pages) VALUES('pekeka','66','666')
2022-12-08T12:19:20.725085Z    29 Init DB   pekeka

[root@server70 ~]# tail -f /var/lib/mysql/server70.log
2022-12-08T12:19:12.298394Z     9 Query SELECT DATABASE()
2022-12-08T12:19:12.298975Z     9 Init DB   pekeka
2022-12-08T12:19:12.300163Z     9 Init DB   pekeka
2022-12-08T12:19:12.300434Z     9 Query show databases
2022-12-08T12:19:12.301863Z     9 Init DB   pekeka
2022-12-08T12:19:12.302712Z     9 Query show tables
2022-12-08T12:19:12.305699Z     9 Init DB   pekeka
2022-12-08T12:19:12.306784Z     9 Field List    book 
2022-12-08T12:19:18.682850Z     6 Query BEGIN
2022-12-08T12:19:18.683224Z     6 Query COMMIT /* implicit, from Xid_log_event */
2022-12-08T12:22:07.833753Z     9 Init DB   pekeka
2022-12-08T12:22:07.835236Z     9 Query select * from book