by xuejianxinokok@163.com 2021年3月25日 周四 15:06:43
====================================
1. 下载地址
https://dev.mysql.com/downloads/mysql/
2. 下载文件名称为: mysql-8.0.23-winx64.zip 到
3. 安装准备
4. 初始化种子库
4.1 在D:\soft\mysql\mysql8023\bin 目录下执行以下命令
mysqld --initialize-insecure
4.2 执行成功后 生成数据目录
D:\soft\mysql\mysql8023\data
5. 创建服务
创建 D:\soft\mysql\mysql8023\conf\my8023.conf 内容如下
------------------------------------------------------ --------------------------
[mysqld]
port=8023
default_authentication_plugin=mysql_native_password
basedir=D:/soft/mysql/mysql8023/soft
datadir=D:/soft/mysql/mysql8023/data/8023
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#复制用
server-id = 8023
log-bin = mysql-bin
-----------------------------------------------------------------------------------
总体结构如下
安装服务
mysqld --install mysql8023 --defaults-file="D:/soft/mysql/mysql8023/conf/my8023.conf"
如果登录 会有以下错误
需要输入
D:\soft\mysql\mysql8023\soft\bin\mysql -P8023 -uroot
执行如下sql
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
update mysql.user set host='%' where user='root';
flush privileges;
复制data/8023 目录
其中 8024,8025,8026 为链式复制 8024=复制到=》8025=复制到=》8026
其中 8027,8028,8029 为双主复制 8027=复制到=》8029 8028=复制到=》8029
复制my8023.conf 为以下内容
需要修改conf 文件中的端口和 data 路径 如下图所示
删除服务
sc delete mysql8024
sc delete mysql8025
sc delete mysql8026
sc delete mysql8027
sc delete mysql8028
sc delete mysql8029
创建服务
mysqld --install mysql8024 --defaults-file="D:/soft/mysql/mysql8023/conf/my8024.conf"
mysqld --install mysql8025 --defaults-file="D:/soft/mysql/mysql8023/conf/my8025.conf"
mysqld --install mysql8026 --defaults-file="D:/soft/mysql/mysql8023/conf/my8026.conf"
mysqld --install mysql8027 --defaults-file="D:/soft/mysql/mysql8023/conf/my8027.conf"
mysqld --install mysql8028 --defaults-file="D:/soft/mysql/mysql8023/conf/my8028.conf"
mysqld --install mysql8029 --defaults-file="D:/soft/mysql/mysql8023/conf/my8029.conf"
把所有的服务有设置为手动
启动 8024,8025,8026 配置链式复制 8024=复制到=》8025=复制到=》8026
配置主库 8024
show master status;
获取日志文件坐标:（文件，便宜）
FILE: mysql-bin.000001
POSITON: 156
登录从库8025 执行
-- 停止复制
-- STOP REPLICA;
--配置复制 这里会弹出错误不需要理他,注意主库的ip 地址要正确
CHANGE REPLICATION SOURCE TO SOURCE_HOST='localhost',SOURCE_PORT=8024,SOURCE_USER='root',SOURCE_PASSWORD='123456',SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=156;
[注:,SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=156;要和主库上面SHOW MASTER STATUS，后复制红框中的值mysql-bin.000001 156的时候得到的值一致]
--开始复制
START REPLICA
--停止复制
-- STOP REPLICA
--确认是否开始复制
在从库上执行
SHOW REPLICA STATUS
结果为
Waiting for master to send event 说明复制成功
在主库8024上创建库
CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use test;
create table user (
id int primary key,
name varchar(50)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
;
insert into user values (1,'aName');
select * from user;
在从库8025 上确认
由于 8025是复制库，所以有中级日志，如果没有配置relay_log 默认为 机器名-relay-bin
所以最好设置
#relay_log =/a/b/c/relay-bin 中继日志位置和名称
relay_log =relay-bin
#允许备库将其重放的时间也记录到自身的二进制文件中，默认的情况下mysql是关闭的
#从库做为其他从库的主库时 log-slave-updates参数是必须要添加的，因为从库要作为其他从库的主库，
#必须添加该参数。该参数就是为了让从库从主库复制数据时可以写入到binlog日志
#从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，
#然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。
#所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数
#该参数默认打开
log_slave_updates=1
#sync_binlog保证每次事物提交前会八二进制日志同步到磁盘，保证数据不丢失
sync_binlog=1
在 从库8026 上执行，注意8026 从8025 复制
CHANGE REPLICATION SOURCE TO SOURCE_HOST='localhost',SOURCE_PORT=8025,SOURCE_USER='root',SOURCE_PASSWORD='123456',SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=156;
START REPLICA
-- STOP REPLICA
验证级联复制
-- 在8024 上插入，验证8025 和8026 是否存在
insert into user values (2,'xuejianxin');
启动 mysql8027,mysql8028,mysql8029
8027上查询
SHOW MASTER STATUS
8028上查询
SHOW MASTER STATUS
8029上执行
CHANGE REPLICATION SOURCE TO SOURCE_HOST='localhost',SOURCE_PORT=8027,SOURCE_USER='root',SOURCE_PASSWORD='123456',SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=156 for channel '8027';
START REPLICA for channel '8027';
CHANGE REPLICATION SOURCE TO SOURCE_HOST='localhost',SOURCE_PORT=8028,SOURCE_USER='root',SOURCE_PASSWORD='123456',SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=156 for channel '8028';
START REPLICA for channel '8028';
SHOW REPLICA STATUS
-- stop REPLICA for channel '8027';
-- stop REPLICA for channel '8028';
在主库8027上创建库
CREATE DATABASE test8027 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use test8027;
create table user (
id int primary key,
name varchar(50)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
;
insert into user values (1,'aName');
select * from user;
在主库8028上创建库
CREATE DATABASE test8028 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use test8028;
create table user (
id int primary key,
name varchar(50)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
;
insert into user values (1,'aName');
select * from user;
解压 mysql-router-8.0.23-winx64.zip 到 mysqlrouter8023
全路径 D:\soft\mysql\mysqlrouter8023
sc delete MySQLRouter
D:\soft\mysql\mysqlrouter8023\bin\mysqlrouter.exe --install-service --config D:/soft/mysql/mysqlrouter8023/mysqlrouter.conf
配置文件内容
[DEFAULT]
base_dir = D:/soft/mysql/mysqlrouter8023
logging_folder={base_dir}/data/log
runtime_folder={base_dir}/data/run
data_folder ={base_dir}/data/data
[logger]
#level = INFO
level = DEBUG
[routing:db1]
bind_address=0.0.0.0
bind_port=8020
destinations=127.0.0.1:8024
mode=read-write
client_connect_timeout=6
connect_timeout=3
max_connections=2048
[routing:db2]
bind_address=0.0.0.0
bind_port=8021
destinations=127.0.0.1:8025,127.0.0.1:8026
mode=read-only
client_connect_timeout=6
connect_timeout=3
max_connections=1024
------------------------------------------------------------------
测试负载均衡
D:\soft\mysql\mysql8023\soft\bin\mysql -P8020 -uroot -p123456 -e "use test; create table t1 (a int); insert into t1 values (1);"
第一次执行
D:\soft\mysql\mysql8023\soft\bin\mysql -P8021 -uroot -p123456 -e "show variables like 'server_id';select * from test.t1;"
第二次执行
D:\soft\mysql\mysql8023\soft\bin\mysql -P8021 -uroot -p123456 -e "show variables like 'server_id';select * from test.t1;"
配置文件路径 D:\soft\nginx\nginx-win32\conf
stream {
upstream tcp8021 {
hash $remote_addr consistent; #负载方法
server localhost:8025 max_fails=5 fail_timeout=30s;
server localhost:8026 max_fails=5 fail_timeout=30s;
}
server {
listen 8021;
proxy_connect_timeout 60;
proxy_timeout 300s;
proxy_pass tcp8021;
}
server {
listen 8021;
proxy_connect_timeout 60;
proxy_timeout 300s;
proxy_pass tcp8021;
}
}
参考 /bwiev2Admin/src/main/resources/config/applicationContext-datasource.xml 配置和实现
源码如下： com.common.spring.db.DynamicDataSource
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DsHolder.get();
}
}
3. 定义切面
<bean id="dataSourceAdvice" class="com.common.spring.db.DataSourceAdvice"></bean>
<!-- 动态切换数据源 方法拦截器
1. 注解形式切换数据源
1.1此拦截器只会拦截以*DAO 结尾的类
1.2. 在DAO 中用以下方法切换,方法签名如下
@DataSource("read")
public List<TSSysUserinf> queryAllBybm();
1.3. mapper 文件的写法没有变化
1.4. 目前不支持分布式事务
2. 如何用代码切换数据源:
List<TSSysUserinf> result=DsHolder.execute("read", new IChangeDsCallback<List<TSSysUserinf>>() {
@Override
public List<TSSysUserinf> execute(String dsName) {
return service.queryByAll(params);
}
} );
-->
<bean id="dataSourceAdviceBeanNameAutoProxyCreator" class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator" >
<property name="proxyTargetClass" value="true"></property>
<property name="beanNames" value="*Service,I*Service" />
<property name="interceptorNames">
<list>
<value>dataSourceAdvice</value>
</list>
</property>
<property name="order" value="99"/>
</bean>
参考文档 https://shardingsphere.apache.org/document/current/cn/overview/#shardingsphere-proxy
https://dbaplus.cn/news-11-1854-1.html 对比总结，分析的很好
支持复制的jdbc
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-source-replica-replication-connection.html
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-j2ee-concepts-managing-load-balanced-connections.html
https://blog.csdn.net/li_xiang_996/article/details/106195812
https://blog.51cto.com/xsunday/2049682 使用样例
https://zhuanlan.zhihu.com/p/62279901 参数解释
1、readFromMasterWhenNoSlaves readFromSourceWhenNoReplicas 当所有的salve死掉后，此参数用来控制主库是否参与读。如果从库的流量很大，配置此参数对主库有很大风险；但如果你关掉，请求则会快速失败。
2、loadBalanceStrategy 策略用来指定从库的轮询规则。有轮询，也有权重，也可以指定具体的策略实现。当你维护或者迁移某个实例时，先置空流量，这会非常有用。或许，你会给某DB一个预热的可能。
3、allowMasterDownConnections allowSourceDownConnections如果主机当机，当连接池获取新的连接时，会失败。但如果打开此参数，则虚拟连接只会创建Slave连接组，整个连接会降级为只读，不论你设置了什么注解。
4、allowSlavesDownConnections allowReplicasDownConnections=true如果没有只读库了，是否允许创建新的连接。在这种情况下，此参数开启，读操作有很大可能会失败。
5、retriesAllDown 当所有的hosts都无法连接时重试的最大次数（依次循环重试），默认为120。重试次数达到阈值仍然无法获取有效链接，将会抛出SQLException。
6、autoReconnect 实例既然有下线、就有上线。上线以后要能够继续服务，此参数用来控制断线情况下自动重连而不抛出异常。这会破坏事务的完整性，但还是默认开启。
Seamless Reconnection
Although not recommended, you can make the driver perform failovers without invalidating the active Statement or ResultSet instances by setting either the parameter autoReconnect or autoReconnectForPools to true. This allows the client to continue using the same object instances after a failover event, without taking any exceptional measures. This, however, may lead to unexpected results: for example, if the driver is connected to the primary host with read/write access mode and it fails-over to a secondary host in real-only mode, further attempts to issue data-changing queries will result in errors, and the client will not be aware of that. This limitation is particularly relevant when using data streaming: after the failover, the ResultSet looks to be alright, but the underlying connection may have changed already, and no backing cursor is available anymore.
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-source-replica-replication-connection.html 新参数配置
1. 添加jdbc
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
Connector/J supports multi-source replication topographies.
The connection URL for replication discussed earlier (i.e., in the format of jdbc:mysql:replication://source,replica1,replica2,replica3/test) assumes that the first (and only the first) host is the source host. Supporting deployments with an arbitrary number of sources and replicas requires the "address-equals" URL syntax for multiple host connection discussed in Section 6.2, “Connection URL Syntax”, with the property type=[source|replica]; for example:
jdbc:mysql:replication://address=(type=source)(host=source1host),address=(type=source)(host=source2host),address=
url : jdbc:mysql:replication://localhost:8024,localhost:8025,localhost:8026,localhost:8024/test?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=GMT%2B8&autoReconnect=false&failOverReadOnly=true&roundRobinLoadBalance=true&readFromSourceWhenNoReplicas=true&allowSourceDownConnections=true&allowReplicasDownConnections=true&retriesAllDown=3&connectionLifecycleInterceptors=com.app.common.core.MySqlConnectionLifecycleInterceptor
allowSourceDownConnections=true to allow Connection objects to be created even though no source hosts are reachable. Such Connection objects report they are read-only, and isSourceConnection() returns false for them. The Connection tests for available source hosts when Connection.setReadOnly(false) is called, throwing an SQLException if it cannot establish a connection to a source, or switching to a source connection if the host is available
allowReplicasDownConnections=true to allow Connection objects to be created even though no replica hosts are reachable. A Connection then, at runtime, tests for available replica hosts when Connection.setReadOnly(true) is called (see explanation for the method below), throwing an SQLException if it cannot establish a connection to a replica, unless the property readFromSourceWhenNoReplicas is set to be “true” (see below for a description of the property)
重要:
if you want to allow connection to a source when no replicas are available, set the property readFromSourceWhenNoReplicas to “true.”
Notice that the source host will be used in read-only state in those cases, as if it is a replica host. Also notice that setting readFromSourceWhenNoReplicas=true might result in an extra load for the source host in a transparent manner.
readFromSourceWhenNoReplicas 需要把master 放到读库列表中，一般放到最后
协议的第一个连接，表示主库Master
后面的一堆连接，表示从库Slave，当然可以有多个
当你把Master的连接也放在后面的一堆里，那么它也拥有了“读库“的属性了
2. 测试方法
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Properties props = new Properties();
props.put("autoReconnect", "true");
props.put("roundRobinLoadBalance", "true");
props.put("user", "test");
props.put("password", "123456");
String url = "jdbc:mysql:replication://localhost:8024,localhost:8025,localhost:8026/test";
int i = 1;
while (i < 100) {
// 每次获取新的连接,测试是否负载均衡
try (Connection connection = DriverManager.getConnection(url, props)) {
if ((i % 5) == 0) {
PreparedStatement rwStmt = connection.prepareStatement("insert into demo values(?,?)");
connection.setReadOnly(false);// 非只读
connection.setAutoCommit(false);// 只读
rwStmt.setString(1, String.valueOf(i));
rwStmt.setString(2, LocalDateTime.now().toString());
rwStmt.execute();
connection.commit();
rwStmt.close();
} else {
connection.setReadOnly(true);// 只读
}
Statement roStmt = connection.createStatement();
ResultSet rs = roStmt.executeQuery("select @@server_id server_id, @@hostname hostname ");
if (rs.next()) {
String output = LocalDateTime.now().toString() + ": server_id=" + rs.getString("server_id")
+ ", hostname=" + rs.getString("hostname");
output += " ( ro=" + connection.isReadOnly() + " )";
System.out.println(output);
}
rs.close();
roStmt.close();
Thread.sleep(1000);
i++;
} // end try
} // end while
}
注意事项: 复制库对于超级用户root 来说是可写入的,在应用中需要创建一个普通用户，防止从库被写入
需要访问普通用户
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
--授权 test 数据库给 'test'@'%'
GRANT ALL ON test.* TO 'test'@'%';
在mysql 配置文件中从库需要配置
#对普通用户只读,对超级用户root没用
read_only=ON
如果是注解式事物添加注解 @Transactional(readOnly = true)
@Bean("myTransactionInterceptor")
public TransactionInterceptor myTransactionInterceptor() {
NameMatchTransactionAttributeSource source = new NameMatchTransactionAttributeSource();
// 只读事务，不做更新操作
RuleBasedTransactionAttribute readOnlyTx = new RuleBasedTransactionAttribute();
readOnlyTx.setReadOnly(true);
//PROPAGATION_SUPPORTS 如果当前有事务则加入，如果没有则不用事务
//readOnlyTx.setPropagationBehavior(TransactionDefinition.PROPAGATION_SUPPORTS);
//支持当前事务，如果当前有事务， 那么加入事务， 如果当前没有事务则新建一个(默认情况)
readOnlyTx.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); //需要重新设置事物的传播属性
//支持当前事务，如果当前有事务， 那么加入事务， 如果当前没有事务则新建一个(默认情况)
RuleBasedTransactionAttribute requiredTx = new RuleBasedTransactionAttribute(
TransactionDefinition.PROPAGATION_REQUIRED,
Collections.singletonList(new RollbackRuleAttribute(Exception.class)));
// requiredTx.setTimeout(5);//设置超时
Map<String, TransactionAttribute> txMap = new HashMap<>();
txMap.put("*", readOnlyTx);// 只读事务
txMap.put("save*", requiredTx);
txMap.put("insert*", requiredTx);
txMap.put("update*", requiredTx);
txMap.put("delete*", requiredTx);
source.setNameMap(txMap);
return new TransactionInterceptor(transactionManager, source);
}
生命周期管理 connectionLifecycleInterceptors
接口 com.mysql.cj.jdbc.interceptors.ConnectionLifecycleInterceptor
样例 &connectionLifecycleInterceptors=com.app.common.core.MySqlConnectionLifecycleInterceptor
A comma-delimited list of classes that implement "com.mysql.cj.jdbc.interceptors.ConnectionLifecycleInterceptor" that should notified of connection lifecycle events
(creation, destruction, commit, rollback, setting the current database and changing the autocommit mode) and potentially alter the execution of these commands.
ConnectionLifecycleInterceptors are "stackable", more than one interceptor may be specified via the configuration property as a comma-delimited list,
with the interceptors executed in order from left to right
查询拦截 queryInterceptors
queryInterceptors, where you specify the fully qualified names of classes that implement the com.mysql.cj.interceptors.QueryInterceptor interface. In these kinds of interceptor classes, you might change or augment the processing done by certain kinds of statements, such as automatically checking for queried data in a memcached server, rewriting slow queries, logging information about statement execution, or route requests to remote servers
A comma-delimited list of classes that implement "com.mysql.cj.interceptors.QueryInterceptor" that should be placed "in between" query execution to influence the results. QueryInterceptors are "chainable", the results returned by the "current" interceptor will be passed on to the next in in the chain, from left-to-right order, as specified in this property.
连接验证
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html
specify a validation query in your connection pool that starts with /* ping */. Note that the syntax must be exactly as specified. This will cause the driver send a ping to the server and return a dummy lightweight result set. When using a ReplicationConnection or LoadBalancedConnection, the ping will be sent across all active connections.
