mysql 主从复制项目实战

最新推荐文章于 2024-04-30 01:53:50 发布

xueshanfeitian

最新推荐文章于 2024-04-30 01:53:50 发布

阅读量795

点赞数 2

分类专栏： mysql 服务器文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/xueshanfeitian/article/details/122913552

版权

服务器同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

mysql

9 篇文章 0 订阅

订阅专栏

引言

最近小猿需要按照领导们的要求将要上项目了，数据库需实现主从复制，关于数据库主从复制的必要性及原理在本文中就不做过多解释，有兴趣的童鞋可以参考网上的一些文章，小猿将针对对整个主从复制过程来展开本文。

主从复制架构

异步(asynchronous)备份

MySQL replication by default is asynchronous. The source writes events to its binary log and replicas request them when they are ready. The source does not know whether or when a replica has retrieved and processed the transactions, and there is no guarantee that any event ever reaches any replica. With asynchronous replication, if the source crashes, transactions that it has committed might not have been transmitted to any replica. Failover from source to replica in this case might result in failover to a server that is missing transactions relative to the source.

同步(synchronous)备份

With fully synchronous replication, when a source commits a transaction, all replicas have also committed the transaction before the source returns to the session that performed the transaction. Fully synchronous replication means failover from the source to any replica is possible at any time. The drawback of fully synchronous replication is that there might be a lot of delay to complete a transaction.

半同步(Semisynchronous)备份

Semisynchronous replication falls between asynchronous and fully synchronous replication. The source waits until at least one replica has received and logged the events (the required number of replicas is configurable), and then commits the transaction. The source does not wait for all replicas to acknowledge receipt, and it requires only an acknowledgement from the replicas, not that the events have been fully executed and committed on the replica side. Semisynchronous replication therefore guarantees that if the source crashes, all the transactions that it has committed have been transmitted to at least one replica.

Compared to asynchronous replication, semisynchronous replication provides improved data integrity, because when a commit returns successfully, it is known that the data exists in at least two places. Until a semisynchronous source receives acknowledgment from the required number of replicas, the transaction is on hold and not committed.

Compared to fully synchronous replication, semisynchronous replication is faster, because it can be configured to balance your requirements for data integrity (the number of replicas acknowledging receipt of the transaction) with the speed of commits, which are slower due to the need to wait for replicas.
more detail we can go the Semisynchronous Replication.

主从复制格式

statement

When using statement-based binary logging, the source writes SQL statements to the binary log. Replication of the source to the replica works by executing the SQL statements on the replica. This is called statement-based replication (which can be abbreviated as SBR), which corresponds to the MySQL statement-based binary logging format.

rowbase

When using row-based logging, the source writes events to the binary log that indicate how individual table rows are changed. Replication of the source to the replica works by copying the events representing the changes to the table rows to the replica. This is called row-based replication (which can be abbreviated as RBR).
Row-based logging is the default method.

mixed

You can also configure MySQL to use a mix of both statement-based and row-based logging, depending on which is most appropriate for the change to be logged. This is called mixed-format logging. When using mixed-format logging, a statement-based log is used by default. Depending on certain statements, and also the storage engine being used, the log is automatically switched to row-based in particular cases. Replication using the mixed format is referred to as mixed-based replication or mixed-format replication.

环境

主数据库： 172.16.101.40
从数据库： 172.16.101.41
mysql版本: 5.7.30
系统环境：centos7.6

安装

安装可以参考小猿的典型传统项目部署实战(jdk，tomcat，mysql，nginx实战)

主从备份

主数据库配置及相关操作

[mysqld]
...
...
# 主从复制设置
#skip_slave_start,这样复制进程就不会随着数据库的启动而启动
#skip-slave-start
# 开启mysql binlog功能
server-id = 1 #Mysql服务的唯一编号 每个mysql服务Id需唯一
log-bin = /usr/local/mysql/data/mysql-bin
sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
innodb_file_per_table=1 #每张表配置独立的空间
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量
slave-skip-errors = all #跳过从库错误
# binlog记录内容的方式，记录被操作的每一行
#binlog_format = ROW
# 对于binlog_format = ROW模式时，减少记录日志的内容，只记录受影响的列
#binlog_row_image = minimal

主数据库重点配置说明

server_id

主从复制需要配置server_id

Each replica must have a unique server ID, as specified by the server_id system variable. If you are setting up multiple replicas, each one must have a unique server_id value that differs from that of the source and from any of the other replicas. If the replica’s server ID is not already set, or the current value conflicts with the value that you have chosen for the source or another replica, you must change it.

log-bin

在这里插入图片描述

该文件为指定binlog日志文件

Specifies the base name to use for binary log files. With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication. The binary log is a sequence of files with a base name and numeric extension. The --log-bin option value is the base name for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the base name.
If you do not supply the --log-bin option, MySQL uses binlog as the default base name for the binary log files. For compatibility with earlier releases, if you supply the --log-bin option with no string or with an empty string, the base name defaults to host_name-bin, using the name of the host machine.

sync_binlog

在这里插入图片描述

Controls how often the MySQL server synchronizes the binary log to disk.
sync_binlog=0: Disables synchronization of the binary log to disk by the MySQL server. Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been synchronized to the binary log.
sync_binlog=1: Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash, transactions that are missing from the binary log are only in a prepared state. This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log.
sync_binlog=N, where N is a value other than 0 or 1: The binary log is synchronized to disk after N binary log commit groups have been collected. In the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been flushed to the binary log. This setting can have a negative impact on performance due to the increased number of disk writes. A higher value improves performance, but with an increased risk of data loss.

innodb_file_per_table

在这里插入图片描述

When a table that resides in a file-per-table tablespace is truncated or dropped, the freed space is returned to the operating system. Truncating or dropping a table that resides in the system tablespace only frees space in the system tablespace. Freed space in the system tablespace can be used again for InnoDB data but is not returned to the operating system, as system tablespace data files never shrink.

binlog-format

日志类型
在这里插入图片描述

Replication works because events written to the binary log are read from the source and then processed on the replica. The events are recorded within the binary log in different formats according to the type of event. The different replication formats used correspond to the binary logging format used when the events were recorded in the source’s binary log. The correlation between binary logging formats and the terms used during replication are:
When using statement-based binary logging, the source writes SQL statements to the binary log. Replication of the source to the replica works by executing the SQL statements on the replica. This is called statement-based replication (which can be abbreviated as SBR), which corresponds to the MySQL statement-based binary logging format.
When using row-based logging, the source writes events to the binary log that indicate how individual table rows are changed. Replication of the source to the replica works by copying the events representing the changes to the table rows to the replica. This is called row-based replication (which can be abbreviated as RBR).
Row-based logging is the default method.
You can also configure MySQL to use a mix of both statement-based and row-based logging, depending on which is most appropriate for the change to be logged. This is called mixed-format logging. When using mixed-format logging, a statement-based log is used by default. Depending on certain statements, and also the storage engine being used, the log is automatically switched to row-based in particular cases. Replication using the mixed format is referred to as mixed-based replication or mixed-format replication.

各模式优缺点

SBR 的优点：

技术成熟
binlog文件较小
binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
binlog可以用于实时的还原，而不仅仅用于复制
主从版本可以不一样，从服务器版本可以比主服务器版本高

SBR 的缺点：

不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。
调用具有不确定因素的 UDF 时复制也可能出问题
使用以下函数的语句也无法被复制：
LOAD_FILE()
UUID()
USER()
FOUND_ROWS()
SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
INSERT … SELECT 会产生比 RBR 更多的行级锁
复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响
存储函数(不是存储过程)在被调用的同时也会执行一次 NOW() 函数，这个可以说是坏事也可能是好事
确定了的 UDF 也需要在从服务器上执行
数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
执行复杂语句如果出错的话，会消耗更多资源

RBR 的优点：

任何情况都可以被复制，这对复制来说是最安全可靠的
和其他大多数数据库系统的复制技术一样
多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
复制以下几种语句时的行锁更少：
INSERT … SELECT
包含 AUTO_INCREMENT 字段的 INSERT
没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
执行 INSERT，UPDATE，DELETE 语句时锁更少
从服务器上采用多线程来执行复制成为可能

RBR 的缺点：

binlog 大了很多
复杂的回滚时 binlog 中会包含大量的数据
主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题
UDF 产生的大 BLOB 值会导致复制变慢
无法从 binlog 中看到都复制了写什么语句
当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生

expire-logs-days

在这里插入图片描述

Specifies the number of days before automatic removal of binary log files. expire_logs_days is deprecated, and you should expect it to be removed in a future release. Instead, use binlog_expire_logs_seconds, which sets the binary log expiration period in seconds. If you do not set a value for either system variable, the default expiration period is 30 days. Possible removals happen at startup and when the binary log is flushed.

max_binlog_size

在这里插入图片描述

If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one). The minimum value is 4096 bytes. The maximum and default value is 1GB. Encrypted binary log files have an additional 512-byte header, which is included in max_binlog_size.
A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.
If max_relay_log_size is 0, the value of max_binlog_size applies to relay logs as well.

binlog_cache_size

在这里插入图片描述

The size of the memory buffer to hold changes to the binary log during a transaction. The value must be a multiple of 4096.
When binary logging is enabled on the server (with the log_bin system variable set to ON), a binary log cache is allocated for each client if the server supports any transactional storage engines. If the data for the transaction exceeds the space in the memory buffer, the excess data is stored in a temporary file. When binary log encryption is active on the server, the memory buffer is not encrypted, but (from MySQL 8.0.17) any temporary file used to hold the binary log cache is encrypted. After each transaction is committed, the binary log cache is reset by clearing the memory buffer and truncating the temporary file if used.
If you often use large transactions, you can increase this cache size to get better performance by reducing or eliminating the need to write to temporary files. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable. See Section 5.4.4, “The Binary Log”.
binlog_cache_size sets the size for the transaction cache only; the size of the statement cache is governed by the binlog_stmt_cache_size system variable.

max_binlog_cache_size

在这里插入图片描述

If a transaction requires more than this many bytes of memory, the server generates a Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage error. The minimum value is 4096. The maximum possible value is 16EiB (exbibytes). The maximum recommended value is 4GB; this is due to the fact that MySQL currently cannot work with binary log positions greater than 4GB. The value must be a multiple of 4096.
max_binlog_cache_size sets the size for the transaction cache only; the upper limit for the statement cache is governed by the max_binlog_stmt_cache_size system variable.
The visibility to sessions of max_binlog_cache_size matches that of the binlog_cache_size system variable; in other words, changing its value affects only new sessions that are started after the value is changed.

binlog-ignore-db

在这里插入图片描述

The effects of this option depend on whether the statement-based or row-based logging format is in use, in the same way that the effects of --replicate-ignore-db depend on whether statement-based or row-based replication is in use. You should keep in mind that the format used to log a given statement may not necessarily be the same as that indicated by the value of binlog_format. For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements, without regard to the logging format in effect, so the following statement-based rules for --binlog-ignore-db always apply in determining whether or not the statement is logged.
Statement-based logging. Tells the server to not log any statement where the default database (that is, the one selected by USE) is db_name.
When there is no default database, no --binlog-ignore-db options are applied, and such statements are always logged. (Bug #11829838, Bug #60188)
Row-based format. Tells the server not to log updates to any tables in the database db_name. The current database has no effect.
When using statement-based logging, the following example does not work as you might expect. Suppose that the server is started with --binlog-ignore-db=sales and you issue the following statements:

auto_increment_offset&auto_increment_increment

在这里插入图片描述

auto_increment_increment and auto_increment_offset are intended for use with circular (source-to-source) replication, and can be used to control the operation of AUTO_INCREMENT columns. Both variables have global and session values, and each can assume an integer value between 1 and 65,535 inclusive. Setting the value of either of these two variables to 0 causes its value to be set to 1 instead. Attempting to set the value of either of these two variables to an integer greater than 65,535 or less than 0 causes its value to be set to 65,535 instead. Attempting to set the value of auto_increment_increment or auto_increment_offset to a noninteger value produces an error, and the actual value of the variable remains unchanged.
auto_increment_offset determines the starting point for the AUTO_INCREMENT column value. Consider the following, assuming that these statements are executed during the same session as the example given in the description for auto_increment_increment:

查看更多配置

更多的配置可以查看mysql的replication配置。

主库重启并查看binlog配置是否生效

service mysql restart

show variables like '%log_bin%';

在这里插入图片描述

show variables like '%server_id%';

在这里插入图片描述
其他的参数是否生效也可以参照上述方法来查询

创建从节点所需用户

grant replication slave on *.* to 'repl_user'@'172.16.101.%'  identified by 'repl_user123456';
flush privileges;

在这里插入图片描述

主库锁表并备份

锁表

flush table with read lock;

查看备份位置

show master status;

在这里插入图片描述

备份

主库建立备份文件夹

mkdir -p /usr/local/mysql/server/backup

为防止意外的错误，需要配置相关权限此处省略
在这里插入图片描述
执行备份命令

#备份并压缩
mysqldump -uroot -p  -A -B |gzip >/usr/local/mysql/server/backup/mysql_bak.$(date +%F).sql.gz

在这里插入图片描述
对上述参数有疑问可以参考下面的命令

mysqldump --help

传输备份文件

#用scp复制到从库上
scp /usr/local/mysql/server/backup/mysql_bak.2022-02-13.sql.gz root@172.16.101.41:/root/

在这里插入图片描述

解锁

unlock tables;

从库配置及操作

恢复备份数据库

mysql -uroot -p < mysql_bak.2022-02-13.sql

在这里插入图片描述

从库配置

my.cnf 修改

[mysqld]
...
...
server-id=2
relay-log = /usr/local/mysql/data/relay-log
relay-log-index = /usr/local/mysql/data/relay-log.index
sync_relay_log =1
sync_relay_log_info = 1
innodb_file_per_table=1
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

#不级联无需开启
#log-bin=/usr/local/mysql/data/mysql-bin
#replicate-do-db =lgry-vue

配置主库信息

CHANGE MASTER TO 
MASTER_HOST = '172.16.101.40',  
MASTER_USER = 'repl_user', 
MASTER_PASSWORD = 'repl_user123456',
MASTER_PORT = 28000,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1663899,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;

在这里插入图片描述

开启从库服务

start slave;

在这里插入图片描述

测试

初始状态

主库状态

在这里插入图片描述

从库状态

在这里插入图片描述

主库建立测试库

在这里插入图片描述

查看从库

在这里插入图片描述

附件

对于mysql生产环境而言，配置文件就是核心，所以小猿将自己在本项目中配置文件做一个记录，方便以后项目进行参考。

master配置文件

[client]    # 客户端设置，即客户端默认的连接参数
port = 28000  # 默认连接端口
socket = /usr/local/mysql/data/mysql.sock  # 用于本地连接的socket套接字，mysqld守护进程生成了这个文件
default-character-set=utf8mb4

#set client  security ssl transmition protocol
ssl-ca=/usr/local/mysql/data/cert/ca.pem
ssl-cert=/usr/local/mysql/data/cert/client-cert.pem
ssl-key=/usr/local/mysql/data/cert/client-key.pem

[mysql]
socket=/usr/local/mysql/data/mysql.sock
# set mysql client default chararter
default-character-set=utf8mb4

[mysqldump]
max_allowed_packet = 64M

[mysqld_safe]
open_files_limit = 8192 # You possibly have to adapt your O/S settings as well
user = mysql
log-error = /usr/local/mysql/logs/error.log

[mysqld]     # 服务端基本设置
# 基础设置
#user = root
#server-id = 1  # Mysql服务的唯一编号 每个mysql服务Id需唯一
port = 28000    # MySQL监听端口
basedir = /usr/local/mysql   # MySQL安装根目录
datadir = /usr/local/mysql/data  # MySQL数据文件所在位置
tmpdir  = /usr/local/mysql/tmp   # 临时目录，比如load data infile会用到
log-error = /usr/local/mysql/logs/error.log # 数据库错误日志文件
slow_query_log_file = /usr/local/mysql/logs/slow_query.log # 慢查询日志文件

socket =  /usr/local/mysql/data/mysql.sock  # 为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
pid-file =  /usr/local/mysql/data/localhost.pid # pid文件所在目录
skip_name_resolve = 1         # 只能用IP地址检查客户端的登录，不用主机名
character-set-server = utf8mb4   # 数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
transaction_isolation = READ-COMMITTED # 事务隔离级别，默认为可重复读，MySQL默认可重复读级别
collation-server = utf8mb4_general_ci  # 数据库字符集对应一些排序等规则，注意要和character-set-server对应
#init_connect='SET NAMES utf8mb4'  # 设置client连接mysql时的字符集,防止乱码
lower_case_table_names = 1        # 是否对sql语句大小写敏感，1表示不敏感
max_connections = 400             # 最大连接数
max_connect_errors = 1000         # 最大错误连接数
explicit_defaults_for_timestamp = true # TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
max_allowed_packet = 128M         # SQL数据包发送的大小，如果有BLOB对象建议修改成1G
interactive_timeout = 1880000        # MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
wait_timeout = 1880000               # MySQL默认的wait_timeout值为8个小时, interactive_timeout参数需要同时配置才能生效
tmp_table_size = 16M              # 内部内存临时表的最大值 ，设置成128M；比如大数据量的group by ,order by时可能用到临时表；超过了这个值将写入磁盘，系统IO压力增大
max_heap_table_size = 128M        # 定义了用户可以创建的内存表(memory table)的大小
query_cache_size = 0              # 禁用mysql的缓存查询结果集功能；后期根据业务情况测试决定是否开启；大部分情况下关闭下面两项
query_cache_type = 0

## 设置sqlmode  默认情况下mysql5.7.5以上的板本都有sqlmode，而ONLY_FULL_GROUP_BY则是默认的 如果不配置则会出现select后面查询的字段没有出现在group by中的错误
#nested exception is java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and
# contains nonaggregated column 'wlhy_xysk.s.vehicle_license_no' which is not functionally dependent on columns in GROUP BY clause;
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

#set mysql server  ssl  security transmition protocol
ssl-ca=/usr/local/mysql/data/cert/ca.pem
ssl-cert=/usr/local/mysql/data/cert/server-cert.pem
ssl-key=/usr/local/mysql/data/cert/server-key.pem

#Threads_created：创建过的线程数，
#  1G  ---> 8
#  2G  ---> 16
#  3G  ---> 32
thread_cache_size = 8
# 用户进程分配到的内存设置，每个session将会分配参数设置的内存大小
key_buffer_size = 256M         #key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度
read_buffer_size = 2M             # MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。
read_rnd_buffer_size = 8M         # MySQL的随机读缓冲区大小
sort_buffer_size = 8M             # MySQL执行排序使用的缓冲大小
binlog_cache_size = 1M            # 一个事务，在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size大小32K 
back_log = 130                    # 在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中；官方建议back_log = 50 + (max_connections / 5),封顶数为900
skip-external-locking     #跳过外部锁 如果是多态服务器希望打开external locking特征则直接注释，如果是单台服务器则直接打开即可
#skip-grant-tables


# 日志设置
#log_error = /usr/local/mysql/data/logs/error.log # 数据库错误日志文件
slow_query_log = 1                # 慢查询sql日志设置
long_query_time = 1               # 慢查询时间；超过1秒则为慢查询
#slow_query_log_file = /usr/local/mysql/data/logs/slow_query.log # 慢查询日志文件
log_queries_not_using_indexes = 1 # 检查未使用到索引的sql
log_throttle_queries_not_using_indexes = 5   # 用来表示每分钟允许记录到slow log的且未使用索引的SQL语句次数。该值默认为0，表示没有限制
min_examined_row_limit = 100                 # 检索的行数必须达到此值才可被记为慢查询，查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
expire_logs_days = 7              # MySQL binlog日志文件保存的过期时间，过期后自动删除


# 主从复制设置
#skip_slave_start,这样复制进程就不会随着数据库的启动而启动
skip-slave-start
# 开启mysql binlog功能
server-id = 1 #Mysql服务的唯一编号 每个mysql服务Id需唯一
log-bin = /usr/local/mysql/data/mysql-bin
sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
innodb_file_per_table=1 #每张表配置独立的空间
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量
slave-skip-errors = all #跳过从库错误
# binlog记录内容的方式，记录被操作的每一行
#binlog_format = ROW 
# 对于binlog_format = ROW模式时，减少记录日志的内容，只记录受影响的列
#binlog_row_image = minimal        
 
# Innodb设置
innodb_open_files = 500           # 限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300
innodb_buffer_pool_size = 64M     # InnoDB使用一个缓冲池来保存索引和原始数据，一般设置物理存储的60% ~ 70%；这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少
innodb_log_buffer_size = 2M       # 此参数确定写日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间
innodb_flush_method = O_DIRECT    # O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突
innodb_write_io_threads = 2     # CPU多核处理能力设置，根据读，写比例进行调整最好多少核的cpu配多少数
innodb_read_io_threads = 2
innodb_lock_wait_timeout = 120    # InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒
innodb_log_file_size = 32M        # 此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间

slave配置文件

[client]    # 客户端设置，即客户端默认的连接参数
port = 28000  # 默认连接端口
socket = /usr/local/mysql/data/mysql.sock  # 用于本地连接的socket套接字，mysqld守护进程生成了这个文件
default-character-set=utf8mb4

#set client  security ssl transmition protocol
ssl-ca=/usr/local/mysql/data/cert/ca.pem
ssl-cert=/usr/local/mysql/data/cert/client-cert.pem
ssl-key=/usr/local/mysql/data/cert/client-key.pem

[mysql]
socket=/usr/local/mysql/data/mysql.sock
# set mysql client default chararter
default-character-set=utf8mb4

[mysqldump]
max_allowed_packet = 64M

[mysqld_safe]
open_files_limit = 8192 # You possibly have to adapt your O/S settings as well
user = mysql
log-error = /usr/local/mysql/logs/error.log
 
[mysqld]     # 服务端基本设置
# 基础设置
#user = root
#server-id = 1  # Mysql服务的唯一编号 每个mysql服务Id需唯一
port = 28000    # MySQL监听端口
basedir = /usr/local/mysql   # MySQL安装根目录
datadir = /usr/local/mysql/data  # MySQL数据文件所在位置
tmpdir  = /usr/local/mysql/tmp   # 临时目录，比如load data infile会用到
socket =  /usr/local/mysql/data/mysql.sock  # 为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
pid-file =  /usr/local/mysql/data/localhost.pid # pid文件所在目录
skip_name_resolve = 1         # 只能用IP地址检查客户端的登录，不用主机名
character-set-server = utf8mb4   # 数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
transaction_isolation = READ-COMMITTED # 事务隔离级别，默认为可重复读，MySQL默认可重复读级别
collation-server = utf8mb4_general_ci  # 数据库字符集对应一些排序等规则，注意要和character-set-server对应
#init_connect='SET NAMES utf8mb4'  # 设置client连接mysql时的字符集,防止乱码
lower_case_table_names = 1        # 是否对sql语句大小写敏感，1表示不敏感
max_connections = 400             # 最大连接数
max_connect_errors = 1000         # 最大错误连接数
explicit_defaults_for_timestamp = true # TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
max_allowed_packet = 128M         # SQL数据包发送的大小，如果有BLOB对象建议修改成1G
interactive_timeout = 1880000        # MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
wait_timeout = 1880000               # MySQL默认的wait_timeout值为8个小时, interactive_timeout参数需要同时配置才能生效
tmp_table_size = 16M              # 内部内存临时表的最大值 ，设置成128M；比如大数据量的group by ,order by时可能用到临时表；超过了这个值将写入磁盘，系统IO压力增大
max_heap_table_size = 128M        # 定义了用户可以创建的内存表(memory table)的大小
query_cache_size = 0              # 禁用mysql的缓存查询结果集功能；后期根据业务情况测试决定是否开启；大部分情况下关闭下面两项
query_cache_type = 0


## 设置sqlmode  默认情况下mysql5.7.5以上的板本都有sqlmode，而ONLY_FULL_GROUP_BY则是默认的 如果不配置则会出现select后面查询的字段没有出现在group by中的错误
#nested exception is java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and
# contains nonaggregated column 'wlhy_xysk.s.vehicle_license_no' which is not functionally dependent on columns in GROUP BY clause;
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

#set mysql server  ssl  security transmition protocol
ssl-ca=/usr/local/mysql/data/cert/ca.pem
ssl-cert=/usr/local/mysql/data/cert/server-cert.pem
ssl-key=/usr/local/mysql/data/cert/server-key.pem

#Threads_created：创建过的线程数，
#  1G  ---> 8
#  2G  ---> 16
#  3G  ---> 32
thread_cache_size = 8
# 用户进程分配到的内存设置，每个session将会分配参数设置的内存大小
key_buffer_size = 256M         #key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度
read_buffer_size = 2M             # MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。
read_rnd_buffer_size = 8M         # MySQL的随机读缓冲区大小
sort_buffer_size = 8M             # MySQL执行排序使用的缓冲大小
binlog_cache_size = 1M            # 一个事务，在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size大小32K 
back_log = 130                    # 在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中；官方建议back_log = 50 + (max_connections / 5),封顶数为900
skip-external-locking     #跳过外部锁 如果是多态服务器希望打开external locking特征则直接注释，如果是单台服务器则直接打开即可
#skip-grant-tables

# 日志设置
log-error = /usr/local/mysql/logs/error.log # 数据库错误日志文件
slow_query_log = 1                # 慢查询sql日志设置
long_query_time = 1               # 慢查询时间；超过1秒则为慢查询
slow_query_log_file = /usr/local/mysql/logs/slow_query.log # 慢查询日志文件
log_queries_not_using_indexes = 1 # 检查未使用到索引的sql
log_throttle_queries_not_using_indexes = 5   # 用来表示每分钟允许记录到slow log的且未使用索引的SQL语句次数。该值默认为0，表示没有限制
min_examined_row_limit = 100                 # 检索的行数必须达到此值才可被记为慢查询，查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
expire_logs_days = 7              # MySQL binlog日志文件保存的过期时间，过期后自动删除

# 主从复制设置
server-id=2
relay-log = /usr/local/mysql/data/relay-log
relay-log-index = /usr/local/mysql/data/relay-log.index
sync_relay_log =1
sync_relay_log_info = 1
innodb_file_per_table=1
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

#不级联无需开启
#log-bin = /usr/local/mysql/data/mysql-bin
#master-host = 172.16.101.40
#master-user=repl_user
#master-pass=repl_user123456
#master-port = 28000
#MASTER_LOG_FILE='mysql-bin.000001',
#MASTER_LOG_POS=1663899;
#master-connect-retry=60
#replicate-do-db =lgry-vue

# Innodb设置
innodb_open_files = 500           # 限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300
innodb_buffer_pool_size = 64M     # InnoDB使用一个缓冲池来保存索引和原始数据，一般设置物理存储的60% ~ 70%；这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少
innodb_log_buffer_size = 2M       # 此参数确定写日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间
innodb_flush_method = O_DIRECT    # O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突
innodb_write_io_threads = 2     # CPU多核处理能力设置，根据读，写比例进行调整最好多少核的cpu配多少数
innodb_read_io_threads = 2
innodb_lock_wait_timeout = 120    # InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒
innodb_log_file_size = 32M        # 此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间