这里实现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和主库的配置重复了，所以需要修改一下，然后重启即可。
您可以选择一种方式赞助本站
支付宝扫一扫赞助
微信钱包扫描赞助
赏