service firewalld status ## 查看防火墙状态
service firewalld stop ## 关闭防火墙 groupadd mysql ## 创建用户组
useradd -g mysql -d /app/mysql mysql ## 在用户组下创建mysql用户并授权相关目录
groupdel mysql ## 删除用户组名(若报已存在相关用户组)
userdel mysql ## 删除用户(若报已存在相关用户) 2019-07-01T02:05:52.681626Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2019-07-01T02:05:52.681694Z 0 [System] [MY-013169] [Server] /app/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.16) initializing of server in progress as process 1479
2019-07-01T02:05:52.681726Z 0 [ERROR] [MY-010338] [Server] Can't find error-message file '/app/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2019-07-01T02:05:55.713747Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: xa6(H>rK/r
2019-07-01T02:05:57.303240Z 0 [System] [MY-013170] [Server] /app/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.16) initializing of server has completed
注意,此时mysql会生成一个默认的临时密码,如上图所示,需要先保存下来然后修改
[mysqld]
port=3306
basedir=/app/mysql/mysql-8.0.16-linux-glibc2.12-x86_64
datadir=/app/mysql/data
socket=/tmp/mysql.sock
symbolic-links=0
[mysqld_safe]
log-error=/app/mysql/data/log/error.log
pid-file=/app/mysql/data/mysql.pid
user=mysql
tmpdir=/tmp
character_set_server=utf8
default-storage-engine=INNODB
init_connect='SET NAMES utf8'
!includedir /etc/my.cnf.d
如果报日志权限相关错误,请先建立对应日志文件,并给mysql用户授权
chown -R mysql:mysql /app/mysql/data/log/error.log mysql -uroot -p密码 ## 登录
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '000000'; use mysql;
update user set host='%' where user='root' limit 1;
flush privileges;
配置mysql主从同步(binlog)
复制原理
复制要求
配置步骤
主数据库(192.168.249.131) CREATE USER 'slave'@'192.168.249.129' IDENTIFIED WITH 'mysql_native_password' BY '000000';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'192.168.249.129';
FLUSH PRIVILEGES;
注意这里创建用户时需要选用mysql_native_password加密方式插件,否则默认会使用caching_sha2_password加密方式,这样在同步的时候需要使用SSL的身份进行验证,为了方便简单,我们直接采用mysql_native_password方式 [mysqld]
# 开启二进制日志功能
log-bin=mysql-bin
# 设置server_id,,注意在网段内要唯一
server-id=131
#(可选配置)要同步的数据库名,要同步多个数据库,就多加几个replicate-db-db=数据库名
binlog-do-db=mydb
#(可选配置)要忽略的数据库
binlog-ignore-db=mysql
注意看里面的参数,特别前面两个File和Position,在从服务器(Slave)配置主从关系会有用到的。
从数据库(192.168.249.129) [mysqld]
server-id=129
log-bin=mysql-bin
replicate-do-db=mydb
replicate-ignore-db=mysql stop slave;
change master to master_host='192.168.249.131',master_user='slave',master_password='000000',master_log_file='mysql-bin.000001',master_log_pos=155;
start slave;
参数说明: master_host='192.168.249.131' ## Master的IP地址
master_user='slave' ## 用于同步数据的用户(在Master中授权的用户)
master_password='000000' ## 同步数据用户的密码
master_port=3306 ## Master数据库服务的端口
masterlogfile='mysql-bin.000001' ##指定Slave从哪个日志文件开始读复制数据(Master上执行命令的结果的File字段)
masterlogpos=155 ## 从哪个POSITION号开始读(Master上执行命令的结果的Position字段)
masterconnectretry=30 ##当重新建立主从连接时,如果连接建立失败,间隔多久后重试。单位为秒,默认设置为60秒,同步延迟调优参数。
至此数据库层面主从配置完成。
SpringBoot中配置主从读写分离
在主从模式下请遵守如下规则:主数据库 只执行 INSERT,UPDATE,DELETE 操作 从数据库 只执行SELECT操作
我们这里使用开源项目[dynamic-datasource-spring-boot-starter](https://gitee.com/baomidou/dynamic-datasource-spring-boot-starter/wikis/)作为读写分离的工具包
使用方法 DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`position` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
org.springframework.boot
spring-boot-starter
org.springframework.boot
spring-boot-starter-web
org.mybatis.spring.boot
mybatis-spring-boot-starter
2.0.1
com.baomidou
dynamic-datasource-spring-boot-starter
2.5.5
mysql
mysql-connector-java
8.0.15
org.projectlombok
lombok
true
org.springframework.boot
spring-boot-starter-test
test
spring:
datasource:
dynamic:
primary: master #设置默认的数据源或者数据源组,默认值即为master
strict: false #设置严格模式,默认false不启动. 启动后再为匹配到指定数据源时候回抛出异常,不启动会使用默认数据源.
datasource:
master:
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://192.168.249.131:3306/mydb?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
username: root
password: '000000'
driver-class-name: com.mysql.cj.jdbc.Driver
slave_1:
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://192.168.249.129:3306/mydb?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
username: root
password: '000000'
driver-class-name: com.mysql.cj.jdbc.Driver @SpringBootApplication@MapperScan("com.jianzh5.dynamic.mapper")
public class DynamicDatsourceBootstrap{
public static void main(String[] args){
SpringApplication.run(DynamicDatsourceBootstrap.class, args);
}
} @Data
public class User{
private int id;
private String account;
private String name;
private String position;
}
6.建立mapper接口文件,新增两个方法addUser(User user),getById(int id) public interface UserDao{
@Insert("INSERT INTO user(account, name, position) VALUES(#{account}, #{name}, #{position})")
@Options(useGeneratedKeys = true,keyProperty = "id")
int addUser(User user);
@Select("SELECT * FROM user WHERE id = #{id}")
User getById(int id);
} public interface UserService{
int addUser(User user);
User getById(int id);
}
@Service
public class UserServiceImpl implements UserService{
@Resource
private UserDao userDao;
@Override
public int addUser(User user){
return userDao.addUser(user);
}
@DS("slave")
@Override
public User getById(int id){
return userDao.getById(id);
}
}
由于在数据源中配置了primary: master,默认操作都会从主库执行,使用注解@DS切换数据源,此注解也可直接用于类文件上,同时存在方法注解优先于类上注解。 public class UserServiceTest extends DynamicDatsourceBootstrapTests{
@Autowired
private UserService userService;
@Test
public void testAddUser(){
User user = new User();
user.setName("李四");
user.setAccount("sili");
user.setPosition("JAVA开发工程师");
int i = userService.addUser(user);
System.out.println(user);
}
@Test
public void testGetById(){
int id = 4;
User user = userService.getById(id);
Assert.assertEquals("sanzhang",user.getAccount());
}
}
朕已阅