mysql数据库作为我们程序开发中，最容易遇到瓶颈的一个环节，其有很多可优化的地方，此处我们先从见效最快、技术要求较低的地方 -- 数据库主从优化
主数据库开启二进制日志
- 修改my.cnf并且将以下参数加入其中,重启mysql实例
- [mysqld]
# 其下还有很多其他部分配置，此处不再一一列举，此处只是说明该配置项需要放置到mysqld分组下
# 由于bug,所以需要设置该参数.否则无法启动mysql实例
# server-id表示数据库的唯一标识符，主库的server-id值必须比从库大(程序员思路1为最大，常人理解时记住主库一般配置为1~10即可，从库从20往下进行递增)
server-id=11
# 其中mysql-bin代表的是basename就是生成二进制日志文件的前缀部分,默认的位置在datadir目录下,也可以设置为其他的路径
log-bin = mysql-bin
# 以下配置项为可选配置项，根据需求进行特殊定制即可
# 设置二进制的日志格式
# 二进制日志的格式有三种：基于语句的格式（STATEMENNT）、基于行的格式（ROW）和混合模式（MIXED）
# 在MySQL 5.7.7之前，默认格式是 STATEMENT。在MySQL 5.7.7及更高版本中，默认值是ROW
binlog_format=row
# 选择部分数据库进行记录
# 如果有多个数据库需要配置，直接复制该条记录重新指定数据库名称即可(默认记录全部数据库)
binlog-do-db = test
# 也可以选择不记录某些数据库的二进制日志
# 如果有多个数据库需要配置，直接复制该条记录重新指定数据库名称即可(默认忽略数据库为空)
# 此配置项优先级低于binlog-do-db，且为互斥关系，即一旦指定binlog-do-db，则此配置项将不再生效
binlog-ignore-db = test
binlog-ignore-db = mysql
# 设置二进制日志分片大小，即二进制日志大小到达多少后自动创建并启用新的日志进行记录
max_binlog_size = 500M
# 设置日志的过期天数，即生成日志后多少天以后，自动删除该二进制日志
# 此配置项单位为天，默认0表示永不过期
expire_logs_days = 7
# 除了二进制日志外，我们还可以记录查询日志，但因为查询太过于吃性能且对原数据并无变更要求，所以一般情况下，我们并未启用查询日志
# 此处仅为记录查询日志的记录方式，请酌情使用
# 启用/关闭查询日志，1-开启 0-关闭
general-log = 1
# 存储方式，可选值有table、file、none
# file表示记录到文件中，table表示记录到表中(此选项很少有人选，所以此处暂时忽略)
log-output = file
# 查询日志路径
general-log-file = "/home/chenyubo/www/logs/mysql.general.log"
# 慢查询日志
# 相较于查询日志，慢查询日志对于我们来说更加重要
# 管理语句的慢查询并不会记录其中
# 管理语句包括：alter table，analyze talbe，check table，create table，create index，drop index，optimize table和repaire table。
# 慢查询日志开关 0-关闭 1-开启
slow_query_log = 1
# 慢查询阀值，单位：秒，默认10秒
long_query_time = 3
# 设置慢查询日志路径
# 如果慢查询日志很多，可使用mysqldumpslow工具对慢查询日志进行分类汇总
slow_query_log_file = "/var/log/mysql/mysql.slow.log" - 查看二进制日志的相关配置
- -- 查看数据库二进制日志进度
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
-- 查看二进制日志列表
MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.000 sec)
-- 查看binlog相关系统参数值
MariaDB [(none)]> show variables where variable_name in ('log_bin','log_bin_basename','log_bin_index');
+------------------+--------------------------------+
| Variable_name | Value |
+------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
+------------------+--------------------------------+
3 rows in set (0.001 sec)
-- 可选SQL，用于管理二进制日志
-- 这些SQL将不会在复制其执行结果，仅留存SQL用于选择使用
-- 删除所有二进制日志
MariaDB [(none)]> reset master;
-- 删除编号在xxxxxx之前的日志
MariaDB [(none)]> purge master logs to 'mysql-bin.000006';
-- 删除日期在'yyyy-mm-dd hh:mm:ss'之前的日志
MariaDB [(none)]> purge master logs before '2020-07-10 12:12:12'; - PS：在my.cnf中设置好log-bin之后，log_bin会自动设置为ON，basename设置为mysql-bin，index文件也自动生成。index文件记录的就是所有的二进制文件的名称及位置信息。
- 在主数据库里创建一个同步账号授权给从数据库使用（grant创建，replication复制）
- -- 创建同步账号，用于从数据库读取二进制日志
-- 账户：repl
-- 登录域：任意
-- 密码：123456
MariaDB [(none)]> create user 'repl'@'%' identified by '123456';
-- 对该账户进行授权
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%';
-- 刷新授权
MariaDB [(none)]> flush privileges;
从库配置
从库配置和主库稍微有些区别，区别为从库需要配置主库的相关信息
[mysqld]
# 其下还有很多其他部分配置，此处不再一一列举，此处只是说明该配置项需要放置到mysqld分组下
# 由于bug,所以需要设置该参数.否则无法启动mysql实例
# server-id表示数据库的唯一标识符，主库的server-id值必须比从库大(程序员思路1为最大，常人理解时记住主库一般配置为1~10即可，从库从20往下进行递增)
server-id = 21
# 启用中继日志
# 一般情况下它在MySQL主从同步读写分离集群的从节点才开启。主节点一般不需要这个日志
# 此处存储的为中继日志的文件名称，生成格式和log-bin相同
relay-log = mysql-relay
# replicate-do-db 设定需要复制的数据库(多数据库逗号，隔开)
# replicate-do-table 设定需要复制的表
# replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符
# replicate-ignore-db 设定忽略的复制数据库 (多数据库逗号，隔开)
# replicate-ignore-table 设定需要忽略的复制表
# replicate-wild-ignore-table 同replication-ignore-table功能一样，可以加通配符
比如，忽略mysql库的所有表从库配置完成后，进行重启服务 systemctl restart mariadb ，然后进入mysql命令行，进行配置主数据库节点
-- 因配置项只有返回是否OK的结果，所以此处不再复制结果信息
MariaDB [(none)]> change master to \
master_host='192.168.86.100', -- 地址填主数据库的地址
master_port=3306, -- 指定主服务器的端口信息
master_user='repl' , -- 填主数据库的使用者
master_password='123456' , -- 访问主数据库密码
master_log_file='mysql_bin_0.000001' , -- 主数据库的文件名
master_log_pos=134; -- 同步主数据库从什么位置开始，填数字就可以，无需引号，但是最后需要分号
-- 开启主从同步
MariaDB [(none)]> start salve;
-- 查看结果
-- 此结果中，需要看到【Slave_IO_Running:yes】和【Slave_SQL_Running:yes】才表示从库配置完成
MariaDB [(none)]> show slave status;Docker 从库配置
我们建议将数据库直接以原生的方式搭建的宿主机上，但是从库并没有那么多要求，虽然也建议直接搭建到宿主机上，但是考虑到有时我们只有一台服务器，如果想做主从的话，就要考虑到Docker和宿主机之间的通讯了。
而且因为是主从，Docker服务器需要联通宿主机，而宿主机只考虑到使用端口链接Docker即可
PS：考虑到有些Docker已经启动过了，所以我们建议进入到Docker容器中，执行命令：ip addr进行查看容器IP
PS：如果没有设置专门的网关的话，可以直接在宿主机执行ip addr show docker0查看IP
# Docker 拉取数据库镜像
$ docker image pull mariadb
# 启动容器
# 运行mariadb
# 本地配置：/home/mysql/data MySQL数据存储目录
# 绑定端口：3307 开放本机端口
# 密码配置：root123 配置MySQL连接密码
# 20.10版本以上
$ docker run --name mysql01 --add-host="host.docker.internal:host-gateway" -v /home/xiaoyutab/Documents/mysqls:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root123 -p 3307:3306 -d mariadb
# 20.10版本以下
# 然后在宿主机使用docker0的IP进行访问
$ docker run --name mysql01 -v /home/xiaoyutab/Documents/mysqls:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root123 -p 3307:3306 -d mariadb
# 进入Docker虚拟机
$ docker exec -it mysql01 /bin/bash
# 修改配置文件，此处使用cp的形式进行复制-修改
[Docker]$ cd /etc/mysql/mariadb.conf.d
[Docker]$ cp 50-server.cnf /var/lib/mysql/
# 在宿主机修改此文件配置即可【因此文件复制出来为root权限，所以需要使用sodu进行执行】
$ sudo vim /home/xiaoyutab/Documents/mysqls/50-server.cnf
# 复制回来
[Docker]$ rm 50-server.cnf ; cp /var/lib/mysql/50-server.cnf ./
# 重启容器内MySQL
$ docker stop mysql01
$ docker start mysql01
# 再次进入容器，进行数据复制
$ docker exec -it mysql01 /bin/bash
[Docker]$ mysql -uroot -proot123
[Docker]MariaDB [(none)]> change master to master_host='172.17.0.1',master_port=3306,master_user='admin',master_password='admin',master_log_file='mysql-bin.000001',master_log_pos=1;
Query OK, 0 rows affected (0.020 sec)
[Docker]MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.000 sec)
[Docker]MariaDB [(none)]> show slave status\G;
# 此结果中，需要看到【Slave_IO_Running:yes】和【Slave_SQL_Running:yes】才表示从库配置完成错误处理
部分时候我们会遇到同步错误，这时候就需要使用以下方法进行解决处理了
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
原因：主从同步遇到同步日志紊乱
解决办法：
-- 主mysql
-- 查看新的binlog日志
> show binary logs;
-- 从mysql
-- 停止同步
> stop slave;
-- 修改同步信息
-- 如果是服务器变更，也可以重新执行change语句进行配置，类似上文的配置方式
> change master to master_log_file='mysql-bin.000001',master_log_pos=1;
-- 开启同步
> start slave;
-- 检查状态
> show slave status \G;Error 'Can't find any matching row in the user table' on query. Default database: ''. Query: 'grant replication slave on .to 'admin'@'localhost''
原因：主库账户表的数据跟从库不同步导致，主库要更新的记录而从库中不存在
解决方案：忽略掉不需要同步的表
PS：主要忽略的库有：mysql、information_schema、performance_schema，其中后两个为虚拟库，默认忽略，所以主要配置忽略的库为mysql