RDS 与 ECS 自建mysql 建议主从复制读写分离操作
一、RDS 作为主数据库，本实例使用的是mysql 8.0
-
登录 RDS 控制台，选择目标实例。
-
配置从实例读取数据使用的只读账号和授权数据库。
-
将从实例的 IP 地址加入主实例的 IP 白名单中
如果从实例是部署在同一地域的 ECS 上，仅需配置该 ECS 的内网地址即可。 如果不在一个地域，请输入ESC的外网IP地址 -
登录主实例，即RDS控制台
-
查询主实例的 server-id。在RDS控制台里面执行命令
show variables like '%server_id%';系统返回如下信息：+-------------------------+-----------------+ | Variable_name | Value | +-------------------------+-----------------+ | server_id | 2657086497 | | server_id_bits | 32 | +-------------------------+-----------------+ -
查询主实例的
GTID_SUBTRACT值show master status\G# 返回如下 File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set mysql-bin.000391 445434 96ac6c7d-11da-11ec-815e-0c42a1a88092:1-1034889
二、ECS 自建 mysql 5.7.34 从数据库，ECS系统 Centos 8.5.2111
-
安装 MySQL 从实例。（宝塔安装）
MySQL 安装包和安装方法请参见 MySQL 官方安装文档。
-
停止从实例 mysql 服务。
systemctl stop mysqld -
修改从实例 mysql 配置文件。
- 打开从实例 MySQL 配置文件。
vim /etc/my.cnf # 或者直接在宝塔软件管理界面进行图形界面配置mysql配置- 配置从实例的 server-id 和要同步的数据库。
# 服务 ID，主从实例 server-id 需不同。只要不和主实例 rds 的相同即可 server-id = 123456789 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M # 需要同步的数据库 replicate-do-db = exampledb # 不需要同步的数据库 replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema # GTID 配置 GTID 同步模式，**binlog** 格式为 **row**，以兼容主实例。 gtid_mode=on enforce_gtid_consistency=on # 设置 binlog 为 row binlog_format=row log-slave-updates=1 ####### 配置mysql主从 开始 #数据库主从核心配置，开启主从复制。读写分离 log_bin = slave_106_bin # 事务型GTID同步必须使用row binlog_format=row server-id = 106 #同步的数据库 binlog_do_db = db_name #操作日志 log-bin-index=mysql-bin.index relay-log=relay-log relay_log_index=relay-log.index #GTID配置,是否开启GTID功能 gtid_mode=on enforce_gtid_consistency=on #同步主库操作日志 log-slave-updates=1 #跳过所有错误,这个参数至关重要 slave-skip-errors=all #复制要同步的数据库,可以并列写多个 replicate-do-db = db_name ####### 配置mysql主从 结束- 执行如下命令，查看 binlog 配置结果。
# 登录mysql执行 show variables like '%binlog_format%';# 系统返回如下信息： +-------------------------+-----------------+ | Variable_name | Value | +-------------------------+-----------------+ | binlog_format | ROW | +-------------------------+-----------------+ -
将主实例数据全量同步到从实例。先手动将主从数据库的数据同步，保持完全一致
本例以 mysqldump 工具为例。
# mysqldump -uname -hmasterhost.mysql.rds.aliyuncs.com -p --databases exampledb > exampledb.sql # mysqldump -uname -p < exampledb.sql -
登录数据库，设置同步选项。
# 先启动，如果已经启动请忽略此命令 systemctl start mysqld # 建立同步 change master to master_host = 'rm-*******.mysql.rds.aliyuncs.com', master_port = 3306, master_user = 'master_user', master_password='master_pass',master_auto_position=1; # 下面我们需要重新设置GTID以跳过错误的信息,记得在第一步我们记录下来的Executed_Gtid_set吗? 没错执行它的时候粗错了,那么保守起见直接跳过这一条即可,在其ID上加1即可,代码如下: set global gtid_purged="96ac6c7d-11da-11ec-815e-0c42a1a88092:1-1213420";# 命令详解 systemctl restart mysqld # 登录mysql mysql -uroot -p # 查看binlog功能是否开放 show variables like 'log_bin'; # 查看server_id是不是刚刚设置好的 show variables like "server_id"; #配置连接主数据库服务 mysql -uroot -p mysql> CHANGE MASTER TO -> MASTER_HOST='', # master IP 主mysql的IP地址或者域名 -> MASTER_PORT=48164, # DB port 主mysql的端口 -> MASTER_USER='replication', # 授权账号 -> MASTER_PASSWORD='password', # 授权密码 -> MASTER_LOG_FILE='master_bin.000002', # Binlog id -> MASTER_LOG_POS=154; # Binlog 目前位置是多少 # 下面我们需要重新设置GTID以跳过错误的信息,记得在第一步我们记录下来的Executed_Gtid_set吗? 没错执行它的时候粗错了,那么保守起见直接跳过这一条即可,在其ID上加1即可,代码如下: set global gtid_purged='96ac6c7d-11da-11ec-815e-0c42a1a88092:1-1035852'; -
启动从实例。
#再执行命令查看从数据库运行状态 start slave; -
检查同步结果。
#查看运行状态 show slave status\G# 查看系统返回信息中 Slave_IO_Running 和 Slave_SQL_Running 的状态是否为 Yes，如下所示。 Slave_IO_Running: Yes Slave_SQL_Running: Yes
三、TP 项目设置
# database.php
'connections' => [
'mysql' => [
// 数据库类型
'type' => env('database.type', 'mysql'),
// 服务器地址
'hostname' => 'master_host,slave_host',
// 数据库名
'database' => 'master_host,slave_host',
// 用户名
'username' => 'master_user,slave_user',
// 密码
'password' => 'master_pass,slave_pass',
// 端口
'hostport' => 'master_port,slave_port',
// 数据库连接参数
'params' => [],
// 数据库编码默认采用utf8
'charset' => env('database.charset', 'utf8'),
// 数据库表前缀
'prefix' => env('database.prefix', ''),
// 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
'deploy' => 1,
// 数据库读写是否分离 主从式有效
'rw_separate' => true,
// 读写分离后 主服务器数量
'master_num' => 1,
// 指定从服务器序号
'slave_no' => 'slave_id',
// 是否严格检查字段是否存在
'fields_strict' => true,
// 是否需要断线重连
'break_reconnect' => false,
// 监听SQL
'trigger_sql' => env('app_debug', true),
// 开启字段缓存
'fields_cache' => false,
],
四、建立主从库，A库到B库的同步关系
1、主库配置 /etc/my.cnf 不同环境下位置可能不一样，请根据具体环境找到 mysql 主配置文件位置，里面配置项目酌情选择，并不是一次复制粘贴进去，请自行检查冲突配置项。
#在[mysqld]区块中加入以下
####### 配置mysql主从 开始
# binlog_format 如果是开启了gtid模式，这里面必须要使用row模式，如果是bin_log文件形式，一般使用mixed方式
binlog_format = row
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""
#数据库主从核心配置，开启主从复制。读写分离
log_bin = master_bin
#同步的数据库
binlog_do_db = db_1
#操作日志
log-bin-index=mysql-bin.index
relay-log=relay-log
relay_log_index=relay-log.index
#GTID配置
#是否开启GTID功能
gtid_mode=on
enforce_gtid_consistency=on
#同步主库操作日志
log-slave-updates=1
#跳过所有错误,这个参数至关重要，想要研究这个参数的作用，可以先不写，看看发生什么
slave-skip-errors=all
#复制要同步的数据库,可以并列写多个
replicate-do-db=db_1
####### 配置mysql主从 结束
2、保存主配置文件 ，进行数据库配置
systemctl restart mysqld
mysql -uroot -p
mysql> show variables like 'log_bin'; 查看binlog功能是否开放
mysql> show variables like "server_id"; 查看server_id是不是刚刚设置好的
#创建用于主从复制的账号，并授权
mysql> create user 'db_user'@'db_name' identified by '123456';
mysql> grant REPLICATION SLAVE,Replication client,reload on *.* to 'db_user'@'%' identified by '123456';
mysql> flush privileges;
mysql> show grants for db_user@'%'; 查看指定账号权限
mysql> show master status; 查看目前索引位置
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master_bin.000001 | 154 | db_1,db_2 | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 特别说明
# databaseName：数据库名称，可以用%，代表所有的数据库
create user 'userName'@'databaseName' identified by 'password';
# REPLICATION SLAVE则是一个必须而基本的权限，它直接授予slave服务器以该账户连接master后可以执行replicate操作的权利
# REPLICATION CLIENT 使得用户可以使用SHOW MASTER STATUS和SHOW SLAVE STATUS命令，也就是说这个权限是用于授予账户监视Replication状况的权力。
# reload 是 administrative 级的权限，即 server administration；
grant REPLICATION SLAVE,Replication client,reload on *.* to 'username'@'%' identified by 'password';
# 具体实例
create user 'db_user'@'db_name' identified by '123456';
grant REPLICATION SLAVE,Replication client,reload on *.* to 'db_user'@'%' identified by '123456';
# 至此，主库配置基本完成。请自行开放网络端口以及防火墙等各类参数。保证从库从网络上访问到主库没有任何问题。
# 基于centos 7 以上系统，可以使用以下命令，直接关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
3、从库配置，并不是所有配置项目都一并复制，需要按需选择修改并添加
# 服务 ID，主从实例 server-id 需不同。只要不和主实例 rds 的相同即可
server-id = 123456789
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
# 需要同步的数据库
replicate-do-db = "a_db"
# 不需要同步的数据库
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
# 主库UC库里面的所有表同步到从库ops_db库里面
replicate-rewrite-db = "a_db->b_db"
# 从A库到B库最重要，最核心的配置-----这里面好像有多少张表需要同步，就得写多少行这个，我实测了，用*号代替，把 b_db.a,b_db.b……这样，好像不生效。
replicate-wild-do-table = b_db.table_1
replicate-wild-do-table = b_db.table_2
replicate-wild-do-table = b_db.table_3
# GTID 配置 GTID 同步模式，**binlog** 格式为 **row**，以兼容主实例。
gtid_mode = on
enforce_gtid_consistency = on
# 设置 binlog 为 row
binlog_format = row
log-slave-updates = 1
# 查看从库连接状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.254
Master_User: slave_ops
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master_bin.000006
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000007
Relay_Log_Pos: 409
Relay_Master_Log_File: master_bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: ops_db.uc_CPFR,ops_db.uc_apps,ops_db.uc_collection_terms,ops_db.uc_collection_terms_detail,ops_db.uc_company,ops_db.uc_company_mapping,ops_db.uc_continent,ops_db.uc_country,ops_db.uc_customer,ops_db.uc_customer_address,ops_db.uc_customer_business_info,ops_db.uc_customer_contacts,ops_db.uc_customer_level,ops_db.uc_customer_mapping,ops_db.uc_customer_stock,ops_db.uc_customer_stock_business_type,ops_db.uc_customer_type,ops_db.uc_dept,ops_db.uc_dept_attr,ops_db.uc_dept_mapping,ops_db.uc_dingtalk_subscribe_logs,ops_db.uc_exchange_rate,ops_db.uc_invoice_type,ops_db.uc_menu,ops_db.uc_migrations,ops_db.uc_org,ops_db.uc_org_accountancy_org_type,ops_db.uc_org_business_org_type,ops_db.uc_org_mapping,ops_db.uc_org_mapping_erp,ops_db.uc_payment_terms,ops_db.uc_payment_terms_detail,ops_db.uc_remittance_type,ops_db.uc_role,ops_db.uc_role_rules,ops_db.uc_role_users,ops_db.uc_settlement_currency,ops_db.uc_settlement_currency_symbol,ops_db.uc_settlement_method,ops_db.uc_settlement_method_bill_type,ops_db.uc_settlement_method_business_type,ops_db.uc_settlement_method_payment,ops_db.uc_settlement_method_type,ops_db.uc_staff,ops_db.uc_staff_mapping,ops_db.uc_supplier,ops_db.uc_supplier_address,ops_db.uc_supplier_business_info,ops_db.uc_supplier_contacts,ops_db.uc_supplier_level,ops_db.uc_supplier_mapping,ops_db.uc_supplier_type,ops_db.uc_sys_operate_log,ops_db.uc_sys_region,ops_db.uc_tax_rate,ops_db.uc_taxpayer_type,ops_db.uc_unit,ops_db.uc_unit_category,ops_db.uc_users,ops_db.uc_users_dimission_records,ops_db.uc_warehouse,ops_db.uc_warehouse_position,ops_db.uc_warehouse_type
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 875
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: bb024046-a243-11eb-b4e3-000c2988126d
Master_Info_File: /www/server/data/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: bb024046-a243-11eb-b4e3-000c2988126d:199-210
Executed_Gtid_Set: bb024046-a243-11eb-b4e3-000c2988126d:1-210,
c30e32e0-66c9-11ec-89fe-fcaa149ad446:1-273
Auto_Position: 1
Replicate_Rewrite_DB: (orico_ucenter_v3,ops_db)
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
4、从库操作命令 全部在 mysql 命令行里面进行操作
# 从库连接进mysql里面进行主从建立,第一个dome是以文件同步
CHANGE MASTER TO MASTER_HOST='192.168.1.254',MASTER_PORT=3306,MASTER_USER='username',MASTER_PASSWORD='password',MASTER_LOG_FILE='master_bin.000001',MASTER_LOG_POS=887;
# 以gtid事务进行同步，需要设置2步操作，一个是建立连接。另外一步是建立gtid
CHANGE MASTER TO MASTER_HOST='192.168.1.243',MASTER_PORT=3306,MASTER_USER='slave_ops',MASTER_PASSWORD='orico2021',master_auto_position = 1;
# 从库如果开启了gtid模式，需要设置GTID，在主库mysql控制台查询得到。show master status\G 获取gtid
set global gtid_purged='d141e2f1-2a45-11ec-84d7-000c2995cda2:1';
# 全部搞完后，执行
start slave;
# 查看从库状态
show slave status\G
# 如果报错需要重置,
stop slave;
reset slave;
reset master;
show slave status\G
# 如果修改从库配置文件,需要重新关闭开启一下从库
stop slave;
start slave;
1001
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
