hj_mysql主从配置
一主一从 mysql8.0.32版本;
podman pull mysql:8.0.32 # 创建对应目录,配置my.cnf文件.文件示例在后面~ # 然后启动容器 podman run -d --privileged=true \ --name mysql_8.0.32_3308 -p 3308:3308 \ -v /hj_files/mysql8.0.32_3308/data:/var/lib/mysql \ -v /hj_files/mysql8.0.32_3308/conf:/etc/mysql \ -e MYSQL_ROOT_PASSWORD=123456 21a7ac543605 # 进入容器 登录mysql,配置账户 podman exec -it 容器ID /bin/bash mysql -uroot -p # 创建账户(主从复制专用账号~) create user 'hjcast'@'%' identified with mysql_native_password by 'hjcast123456'; # 授予主从复制权限 grant replication slave on *.* to 'hjcast'@'%'; # 查看maser信息 show master status; # 看到的示例内容如下: +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 157 | hj_boot | mysql,lingshi | | +------------------+----------+--------------+------------------+-------------------+ # 配置从库my.cnf文件 启动容器 podman run -d --privileged=true \ --name mysql_8.0.32_3309 -p 3309:3309 \ -v /hj_files/mysql8.0.32_3309/data:/var/lib/mysql \ -v /hj_files/mysql8.0.32_3309/conf:/etc/mysql \ -e MYSQL_ROOT_PASSWORD=123456 21a7ac543605 # 进入容器,登录mysql,配置主库信息 change replication source to source_host='120.78.129.105',source_port=3308,source_user='hjcast',source_password='hjcast123456',source_log_file='mysql-bin.000004',source_log_pos=157; # 启动slave start slave; stop slave; #停止 show slave status\G; # 查看信息 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 120.78.129.105 Master_User: hjcast Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 157 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 326 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: ........
master下的my.cnf配置文件示例如下:
# 修改后的配置文件 [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8mb4 # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # 配置主从复制,当下这个作为master库 # 数据库唯一ID,确保唯一 server-id=1 # 是否只读,1代表只读 0代表读写 read-only=0 # 开启mysql的binlog日志功能 log-bin=mysql-bin #控制数据库的binlog刷到磁盘上去,0 不控制,性能最好,1 每次事物提交都会刷到日志文件中,性能最差,最安全 sync_binlog= 1 #binlog日志格式,mysql默认采用statement,建议使用mixed binlog_format= mixed #binlog过期清理时间 expire_logs_days= 7 #binlog每个日志文件大小 max_binlog_size= 100m #binlog缓存大小 binlog_cache_size= 4m #需要同步的数据库 这个可不配置 binlog-do-db= hj_boot #最大binlog缓存大 max_binlog_cache_size= 512m #不生成日志文件的数据库,多个忽略数据库可以用逗号拼接,或者 复制这句话,写多行 binlog-ignore-db=mysql #不需要同步的数据库 binlog-ignore-db=lingshi # 自增值的偏移量 auto-increment-offset= 1 # 自增值的自增量 auto-increment-increment= 1 #跳过从库错误 slave-skip-errors= all # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password # 服务端使用的字符集默认为8比特编码的latin1字符集,改为utf8mb4 character-set-server=utf8mb4 collation-server=utf8mb4_general_ci # 设置3308端口 默认是3306 port=3308 skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.sock secure-file-priv=/var/lib/mysql-files user=mysql pid-file=/var/run/mysqld/mysqld.pid # 允许最大连接数 max_connections=20 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [client] socket=/var/run/mysqld/mysqld.sock !includedir /etc/mysql/conf.d/
slave下的my.cnf配置文件示例如下:
# 修改后的配置文件 [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8mb4 # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # 这个是从库配置 server-id= 2 # 只读,这个超管还是可读可写的 super-read-only=1 就都只能读 read-only=1 log-bin=mysql-bin # 从主数据库同步的binlog会写入到该目录下 relay-log= mysql-relay-bin #如果主从数据库名称不同 #replication-rewrite-db= 主数据库名 -> 从数据库名 # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password # 服务端使用的字符集默认为8比特编码的latin1字符集,改为utf8mb4 character-set-server=utf8mb4 collation-server=utf8mb4_general_ci # 设置3309端口 默认是3306 port=3309 skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.sock secure-file-priv=/var/lib/mysql-files user=mysql pid-file=/var/run/mysqld/mysqld.pid # 允许最大连接数 max_connections=20 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [client] socket=/var/run/mysqld/mysqld.sock !includedir /etc/mysql/conf.d/
好吧~一主一从 8.0.32版本大致到这啦~
springboot+mybatisplus项目可如此连接
maven依赖 <!-- mybatis-plus --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>${mybatisplus.version}</version> </dependency> <!-- 多数据源支持 --> <!-- https://mvnrepository.com/artifact/com.baomidou/dynamic-datasource-spring-boot-starter --> <dependency> <groupId>com.baomidou</groupId> <artifactId>dynamic-datasource-spring-boot-starter</artifactId> <version>${mybatisplus-dynamic.version}</version> </dependency> yml配置 spring: # mysql 主从配置 datasource: dynamic: # 设置默认的数据源或者数据源组,默认值即为master primary: master datasource: master: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://120.78.129.105:3308/hj_boot?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true username: root password: 123456 slave_1: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://120.78.129.105:3309/hj_boot?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true username: root password: 123456 # ... @DS("slave_1") 配置在方法上,即可使用对应的库,不然使用默认的master
二主二从 mysql8.0.32版本~接着前面继续做..
# 创建目录 mkdir -p /hj_files/mysql8.0.32_3310/data mkdir -p /hj_files/mysql8.0.32_3310/conf mkdir -p /hj_files/mysql8.0.32_3311/data mkdir -p /hj_files/mysql8.0.32_3311/conf # 拷贝配置文件 cp -r /hj_files/mysql8.0.32_3308/conf/. /hj_files/mysql8.0.32_3310/conf/ cp -r /hj_files/mysql8.0.32_3309/conf/. /hj_files/mysql8.0.32_3311/conf/ # 修改配置文件 # 3308做主库 3309做3308的从库 # 3310 做主库 3311做3310的从库 # 4个配置文件后面附录~ #启动这俩新的容器 podman run -d --privileged=true \ --name mysql_8.0.32_3310 -p 3310:3310 \ -v /hj_files/mysql8.0.32_3310/data:/var/lib/mysql \ -v /hj_files/mysql8.0.32_3310/conf:/etc/mysql \ -e MYSQL_ROOT_PASSWORD=123456 412b8cc72e4a podman run -d --privileged=true \ --name mysql_8.0.32_3311 -p 3311:3311 \ -v /hj_files/mysql8.0.32_3311/data:/var/lib/mysql \ -v /hj_files/mysql8.0.32_3311/conf:/etc/mysql \ -e MYSQL_ROOT_PASSWORD=123456 412b8cc72e4a # 进入到 3310容器创建账号并授权 podman exec -it 47836ad47496 /bin/bash mysql -uroot -p create user 'hscast'@'%' identified with mysql_native_password by 'hscast123456'; grant replication slave on *.* to 'hscast'@'%'; show master status; # 进入到3311从库容器进行操作 podman exec -it f70474bd2627 /bin/bash mysql -uroot -p change replication source to source_host='120.78.129.105',source_port=3310,source_user='hscast',source_password='hscast123456',source_log_file='mysql-bin.000003',source_log_pos=664; mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show slave status\G; # 重启一下3308容器 因为修改了配置文件 podman restart 3308 # 进入3308容器 把自身挂载到3310做从 change master to master_host='120.78.129.105',master_port=3310,master_user='hscast',master_password='hscast123456',master_log_file='mysql-bin.000003',master_log_pos=664; start slave; show slave status\G; # 进入到3310容器,把自身挂载到3308做从 change master to master_host='120.78.129.105',master_port=3308,master_user='hjcast',master_password='hjcast123456',master_log_file='mysql-bin.000005',master_log_pos=157; start slave; show slave status\G; # 成功了 但是...hj_boot 原来就存在的库没能同步过来 ,这个是log_file,log_pos配置问题.删掉重建库就行了.
# 修改后的配置文件 [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8mb4 # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # 配置主从复制,当下这个作为master库 # 数据库唯一ID,确保唯一 server-id=1 # 是否只读,1代表只读 0代表读写 read-only=0 # 开启mysql的binlog日志功能 log-bin=mysql-bin log-slave-updates auto_increment_increment=2 auto_increment_offset=1 #控制数据库的binlog刷到磁盘上去,0 不控制,性能最好,1 每次事物提交都会刷到日志文件中,性能最差,最安全 sync_binlog= 1 #binlog日志格式,mysql默认采用statement,建议使用mixed binlog_format= mixed #binlog过期清理时间 expire_logs_days= 7 #binlog每个日志文件大小 max_binlog_size= 100m #binlog缓存大小 binlog_cache_size= 4m #需要同步的数据库 这个可不配置 binlog-do-db= hj_boot binlog-do-db= hj_two #最大binlog缓存大 max_binlog_cache_size= 512m #不生成日志文件的数据库,多个忽略数据库可以用逗号拼接,或者 复制这句话,写多行 binlog-ignore-db=mysql #不需要同步的数据库 binlog-ignore-db=lingshi # 自增值的偏移量 #auto-increment-offset= 1 # 自增值的自增量 #auto-increment-increment= 1 #跳过从库错误 slave-skip-errors= all # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password # 服务端使用的字符集默认为8比特编码的latin1字符集,改为utf8mb4 character-set-server=utf8mb4 collation-server=utf8mb4_general_ci # 设置3308端口 默认是3306 port=3308 skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.sock secure-file-priv=/var/lib/mysql-files user=mysql pid-file=/var/run/mysqld/mysqld.pid # 允许最大连接数 max_connections=20 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [client] socket=/var/run/mysqld/mysqld.sock !includedir /etc/mysql/conf.d/
# 修改后的配置文件 [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8mb4 # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # 这个是从库配置 server-id= 2 # 只读,这个超管还是可读可写的 super-read-only=1 就都只能读 read-only=1 log-bin=mysql-bin # 从主数据库同步的binlog会写入到该目录下 relay-log= mysql-relay-bin #跳过从库错误 slave-skip-errors= all #如果主从数据库名称不同 #replication-rewrite-db= 主数据库名 -> 从数据库名 # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password # 服务端使用的字符集默认为8比特编码的latin1字符集,改为utf8mb4 character-set-server=utf8mb4 collation-server=utf8mb4_general_ci # 设置3309端口 默认是3306 port=3309 skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.sock secure-file-priv=/var/lib/mysql-files user=mysql pid-file=/var/run/mysqld/mysqld.pid # 允许最大连接数 max_connections=20 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [client] socket=/var/run/mysqld/mysqld.sock !includedir /etc/mysql/conf.d/
# 修改后的配置文件 [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8mb4 # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # 配置主从复制,当下这个作为master库 # 数据库唯一ID,确保唯一 server-id=3310 # 是否只读,1代表只读 0代表读写 read-only=0 # 开启mysql的binlog日志功能 log-bin=mysql-bin log-slave-updates auto_increment_increment=2 auto_increment_offset=2 #控制数据库的binlog刷到磁盘上去,0 不控制,性能最好,1 每次事物提交都会刷到日志文件中,性能最差,最安全 sync_binlog= 1 #binlog日志格式,mysql默认采用statement,建议使用mixed binlog_format= mixed #binlog过期清理时间 expire_logs_days= 7 #binlog每个日志文件大小 max_binlog_size= 100m #binlog缓存大小 binlog_cache_size= 4m #需要同步的数据库 这个可不配置 binlog-do-db= hj_boot binlog-do-db= hj_two #最大binlog缓存大 max_binlog_cache_size= 512m #不生成日志文件的数据库,多个忽略数据库可以用逗号拼接,或者 复制这句话,写多行 binlog-ignore-db=mysql #不需要同步的数据库 binlog-ignore-db=lingshi # 自增值的偏移量 #auto-increment-offset= 1 # 自增值的自增量 #auto-increment-increment= 1 #跳过从库错误 slave-skip-errors= all # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password # 服务端使用的字符集默认为8比特编码的latin1字符集,改为utf8mb4 character-set-server=utf8mb4 collation-server=utf8mb4_general_ci # 设置3310端口 默认是3306 port=3310 skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.sock secure-file-priv=/var/lib/mysql-files user=mysql pid-file=/var/run/mysqld/mysqld.pid # 允许最大连接数 max_connections=20 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [client] socket=/var/run/mysqld/mysqld.sock !includedir /etc/mysql/conf.d/
# 修改后的配置文件 [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8mb4 # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # 这个是从库配置 server-id= 2 # 只读,这个超管还是可读可写的 super-read-only=1 就都只能读 read-only=1 log-bin=mysql-bin # 从主数据库同步的binlog会写入到该目录下 relay-log= mysql-relay-bin #跳过从库错误 slave-skip-errors= all #如果主从数据库名称不同 #replication-rewrite-db= 主数据库名 -> 从数据库名 # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password # 服务端使用的字符集默认为8比特编码的latin1字符集,改为utf8mb4 character-set-server=utf8mb4 collation-server=utf8mb4_general_ci # 设置3311端口 默认是3306 port=3311 skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.sock secure-file-priv=/var/lib/mysql-files user=mysql pid-file=/var/run/mysqld/mysqld.pid # 允许最大连接数 max_connections=20 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [client] socket=/var/run/mysqld/mysqld.sock !includedir /etc/mysql/conf.d/
springboot+mybatisplus项目可如此连接
maven依赖 <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> </dependency> <!-- 多数据源支持 配置了这个 就要配主数据库哦--> <!-- Error querying database. Cause: com.baomidou.dynamic.datasource.exception.CannotFindDataSourceException: dynamic-datasource can not find primary datasource--> <!-- https://mvnrepository.com/artifact/com.baomidou/dynamic-datasource-spring-boot-starter --> <dependency> <groupId>com.baomidou</groupId> <artifactId>dynamic-datasource-spring-boot-starter</artifactId> </dependency> yml spring: # mysql 主从配置 datasource: dynamic: # 设置默认的数据源或者数据源组,默认值即为master primary: master_1 datasource: master_1: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://120.78.129.105:3308/hj_boot?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true username: root password: 123456 slave_1: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://120.78.129.105:3309/hj_boot?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true username: root password: 123456 master_2: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://120.78.129.105:3310/hj_boot?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true username: root password: 123456 slave_2: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://120.78.129.105:3311/hj_boot?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true username: root password: 123456 @DS("slave_2") @DS("master_2") ....
后面在该项目基础上继续研究自动切换数据源~
