mysql读写分离atlas

avatar 2024年5月11日18:14:02 评论 195 次浏览

这里实现mysql的读写分离,利用atlas中间件实现,在没实现读写分离之前我们需要先把数据库的主从复制搭建好,最终实现的需求是写的时候在主库上实现,读的时候从从库上读,现在两个数据库已经搭建好了,下面我们先配置一下读写分离。我这里有两个数据库分别是mysql01,mysql02,我这里把mysql01当成主库,mysql02当成从库,需要现在主库上创建一个用户,方便从库读取。但是从库从主库读取的是binlog日志进行同步,我们需要先在主库上开启binlog。

 [root@mysql01 ~]# mysql -uroot -p
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 13
 Server version: 8.0.33 MySQL Community Server - GPL
 
 Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
 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 variables like 'log_%';
 +----------------------------------------+----------------------------------------+
 | Variable_name                          | Value                                  |
 +----------------------------------------+----------------------------------------+
 | log_bin                                | ON                                     |
 | log_bin_basename                       | /usr/local/mysql/var/mysql-bin         |
 | log_bin_index                          | /usr/local/mysql/var/mysql-bin.index   |
 | log_bin_trust_function_creators        | OFF                                    |
 | log_bin_use_v1_row_events              | OFF                                    |
 | log_error                              | ./mysql01.err                          |
 | log_error_services                     | log_filter_internal; log_sink_internal |
 | log_error_suppression_list             |                                        |
 | log_error_verbosity                    | 2                                      |
 | log_output                             | FILE                                   |
 | log_queries_not_using_indexes          | OFF                                    |
 | log_raw                                | OFF                                    |
 | log_replica_updates                    | ON                                     |
 | log_slave_updates                      | ON                                     |
 | log_slow_admin_statements              | OFF                                    |
 | log_slow_extra                         | OFF                                    |
 | log_slow_replica_statements            | OFF                                    |
 | log_slow_slave_statements              | OFF                                    |
 | log_statements_unsafe_for_binlog       | ON                                     |
 | log_throttle_queries_not_using_indexes | 0                                      |
 | log_timestamps                         | UTC                                    |
 +----------------------------------------+----------------------------------------+
 21 rows in set (0.00 sec)
 

我这里已经开启好了,如果没有开启binlog,需要在mysql的配置文件目录下增加一个log-bin=mysql-bin更多配置可以参考

 log-bin=mysql-bin
 binlog_format=mixed
 server-id   = 1
 binlog_expire_logs_seconds = 864000
 early-plugin-load = ""
 
 default_storage_engine = InnoDB
 innodb_file_per_table = 1
 innodb_data_home_dir = /usr/local/mysql/var
 innodb_data_file_path = ibdata1:10M:autoextend
 innodb_log_group_home_dir = /usr/local/mysql/var
 innodb_buffer_pool_size = 16M
 innodb_log_file_size = 5M
 innodb_log_buffer_size = 8M
 innodb_flush_log_at_trx_commit = 1
 innodb_lock_wait_timeout = 50

下面在主库上创建一个用户,这个用户的目的是为了从库能够通过这个用户从主库里读取主库的binlog日志内容,然后复制到从库上在写到从库里,这里可以配置从库的ip也。可以不限制,建议限制一下从库的ip

 mysql> CREATE USER 'mysql02'@'10.211.55.39' IDENTIFIED BY 'mysql02';
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysql02'@'10.211.55.39';
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> SELECT user, host FROM mysql.user WHERE user = 'mysql02';
 +---------+--------------+
 | user    | host         |
 +---------+--------------+
 | mysql02 | 10.211.55.39 |
 +---------+--------------+
 1 row in set (0.01 sec)

用户和密码都创建完了,我们需要找一下主库的binlog日志,然后指定从库是从那个地方开始读取从库的binlog日志内容。

 mysql> SHOW MASTER STATUS;
 +------------------+----------+--------------+------------------+-------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
 | mysql-bin.000006 |     1998 |              |                  |                   |
 +------------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

下面在从库上执行,注意如果有防火墙先把防火墙关闭,如果是线上环境针对耽搁ip开启端口,可以参考: https://www.wulaoer.org/?p=3077

 mysql> change master to master_host='10.211.55.40', master_user='mysql02', master_password='mysql02',  master_log_file='mysql-bin.000006', master_log_pos=1998;
 Query OK, 0 rows affected, 8 warnings (0.03 sec)
 mysql> START REPLICA;     #早期版本使用START SLAVE;如果关闭可以使用STOP REPLICA;或STOP SLAVE;
 Query OK, 0 rows affected (0.01 sec)
 mysql> SHOW SLAVE STATUS\G
 *************************** 1. row ***************************
                Slave_IO_State: Waiting for source to send event
                   Master_Host: 10.211.55.40
                   Master_User: mysql02
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000006
           Read_Master_Log_Pos: 1998
                Relay_Log_File: mysql02-relay-bin.000003
                 Relay_Log_Pos: 326
         Relay_Master_Log_File: mysql-bin.000006
              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: 1998
               Relay_Log_Space: 718
               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: 26194a7f-0da6-11ef-a7bb-001c42828201
              Master_Info_File: mysql.slave_master_info
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Replica 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:
        Master_public_key_path:
         Get_master_public_key: 0
             Network_Namespace:
 1 row in set, 1 warning (0.00 sec)

这说明我的主从复制集群做好了,下面验证一下,在主库中创建一个数据库,看一下从库是否会出现。

 #主库执行
 mysql> create database wulaoer;
 Query OK, 1 row affected (0.01 sec)
 #从库
 mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 | wulaoer            |
 +--------------------+
 5 rows in set (0.00 sec)
 

说明我们主从复制搭建好了,下面我们安装一下中间件,这里选择使用atlas作为读写分离的中间件使用,下面看一下atlas的配置和使用方法。

 [root@clinent ~]# wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
 [root@clinent ~]# rpm -i Atlas-2.2.1.el6.x86_64.rpm
 或者直接使用yum安装也是可以的
 [root@clinent ~]# yum install -y Atlas*

安装好之后在/usr/local/mysql-proxy下是atlas的跟目录,atlas的配置文件是在conf目录下,为了方便atlas能够对主从数据库进行读写的操作,这里需要在主库上创建一个用户,并授权给atlas,会自动同步到从库上,所以从库不需要创建了。

 mysql> CREATE USER 'atlas'@'10.211.55.38' IDENTIFIED BY 'atlas';
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> GRANT REPLICATION SLAVE ON *.* TO 'atlas'@'10.211.55.38';
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> SELECT user, host FROM mysql.user WHERE user = 'atlas';
 +-------+--------------+
 | user  | host         |
 +-------+--------------+
 | atlas | 10.211.55.38 |
 +-------+--------------+
 1 row in set (0.00 sec)

创建后我们看一下atlas的配置内容,注释都是中文的,我这里就不多解释了。

 [root@clinent ~]# /usr/local/mysql-proxy/bin/encrypt atlas #给atlas的密码进行加密
 KsWNCR6qyNk=
 [root@clinent ~]# cd /usr/local/mysql-proxy/conf/
 [root@clinent conf]# cp test.cnf test.cnf.back
 [root@clinent conf]# cat test.cnf
 [mysql-proxy]
 #带#号的为非必需的配置项目
 #管理接口的用户名
 admin-username = root
 #管理接口的密码
 admin-password = root
 #Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
 proxy-backend-addresses = 10.211.55.40:3306
 #Atlas后端连接的MySQL从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔
 proxy-read-only-backend-addresses = 10.211.55.39:3306@1
 #用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名和加密密码!
 pwds = atlas:KsWNCR6qyNk=
 #设置Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
 daemon = true
 #设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
 keepalive = true
 #工作线程数,对Atlas的性能有很大影响,可根据情况适当设置
 event-threads = 8
 #日志级别,分为message、warning、critical、error、debug五个级别
 log-level = message
 #日志存放的路径
 log-path = /usr/local/mysql-proxy/log
 #SQL日志的开关,可设置为OFF、ON、REALTIME,OFF代表不记录SQL日志,ON代表记录SQL日志,REALTIME代表记录SQL日志且实时写入磁盘,默认为OFF
 #sql-log = OFF
 #慢日志输出设置。当设置了该参数时,则日志只输出执行时间超过sql-log-slow(单位:ms)的日志记录。不设置该参数则输出全部日志。
 #sql-log-slow = 10
 #实例名称,用于同一台机器上多个Atlas实例间的区分
 #instance = test
 #Atlas监听的工作接口IP和端口
 proxy-address = 0.0.0.0:1234
 #Atlas监听的管理接口IP和端口
 admin-address = 0.0.0.0:2345
 #分表设置,此例中person为库名,mt为表名,id为分表字段,3为子表数量,可设置多项,以逗号分隔,若不分表则不需要设置该项
 #tables = person.mt.id.3
 #默认字符集,设置该项后客户端不再需要执行SET NAMES语句
 charset = utf8
 #允许连接Atlas的客户端的IP,可以是精确IP,也可以是IP段,以逗号分隔,若不设置该项则允许所有IP连接,否则只允许列表中的IP连接
 #client-ips = 127.0.0.1, 192.168.1
 #Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP),若有LVS且设置了client-ips则此项必须设置,否则可以不设置
 #lvs-ips = 192.168.1.1

启动atlas

 [root@clinent conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test restart
 [root@clinent ~]# ps -ef | grep proxy
 root       57571       1  0 17:09 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
 root       57572   57571  0 17:09 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
 root       57687   57644  0 17:27 pts/2    00:00:00 grep --color=auto proxy

下面测试使用atlas测试读写分离,前面创建了一个库wulaoer并赋予atlas用户权限,这里要注意,如果不给atlas权限,会导致读取不了库中的内容也就无法创建了。下面我使用客户端连接altas

 [wolf@wulaoer.org 🔥🔥🔥🔥 ~ ]$ mysql -h10.211.55.38 -P 1234 -uatlas -patlas
 mysql: [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.0.81-log MySQL Community Server - GPL
 
 Copyright (c) 2000, 2019, 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 databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | performance_schema |
 | wulaoer            |
 +--------------------+
 3 rows in set (0.00 sec)
 mysql> use wulaoer;
 Database changed
 mysql> show tables;
 Empty set (0.01 sec)
 
 mysql> CREATE TABLE IF NOT EXISTS employees (
     ->     id INT AUTO_INCREMENT PRIMARY KEY,
     ->     first_name VARCHAR(50) NOT NULL,
     ->     last_name VARCHAR(50) NOT NULL,
     ->     email VARCHAR(100),
     ->     hire_date DATE,
     ->     salary DECIMAL(10, 2)
     -> );
 Query OK, 0 rows affected (0.05 sec)
 
 mysql> INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');
 Query OK, 1 row affected (0.00 sec)
 mysql> select * from employees;
 +----+------------+-----------+-------+-----------+--------+
 | id | first_name | last_name | email | hire_date | salary |
 +----+------------+-----------+-------+-----------+--------+
 |  1 | John       | Doe       | NULL  | NULL      |   NULL |
 +----+------------+-----------+-------+-----------+--------+
 1 row in set (0.00 sec)

通过atlas创建了表,并查看了表的内容。说明使用atlas已经实现了读写操作,下面我们看看我把主库停掉,是否可以插入数据。

 mysql> INSERT INTO employees (first_name, last_name) VALUES ('wolf', 'wulaoer');
 ERROR 2006 (HY000): MySQL server has gone away
 No connection. Trying to reconnect...
 Connection id:    2
 Current database: wulaoer
 
 ERROR 2013 (HY000): Lost connection to MySQL server during query

在id2里无法写入,我们看一下id是那个实例。

 [wolf@wulaoer.org 🔥🔥🔥🔥 ~ ]$ mysql -h10.211.55.38 -P 2345 -uroot -proot
 mysql: [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.0.99-agent-admin
 
 Copyright (c) 2000, 2019, 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> SELECT * FROM backends;
 +-------------+-------------------+-------+------+
 | backend_ndx | address           | state | type |
 +-------------+-------------------+-------+------+
 |           1 | 10.211.55.40:3306 | up    | rw   |
 |           2 | 10.211.55.39:3306 | up    | ro   |
 +-------------+-------------------+-------+------+
 2 rows in set (0.00 sec)

2时从库,所以无法下入数据,这里要注意,在主库实例上停止mysql,atlas中并不一定会停止,如果要切,需要先在atlas中先停,后在停止主库。至此atlas测试完成了,没有了看看其他的吧。更多atlas命令可以参考:https://www.wulaoer.org/?p=3081

启动报错解决方法

 ./encrypt
 ./encrypt: error while loading shared libraries: libcrypto.so.10: cannot open shared object file: No such file or directory
 [root@clinent bin]# rpm -qa | grep libcrypto.so
 [root@clinent bin]# rpm -qa | grep libcrypt
 [root@clinent bin]# dnf install compat-openssl10

在查看主从复制时SHOW SLAVE STATUS\G在Last_IO_Error提示下面的错误

 Fatal error: The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always make sense; please check the manual before using it).

主要是因为在mysql的配置文件中的[mysqld]下的server-id和主库的配置重复了,所以需要修改一下,然后重启即可。

avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: