MySQL运维

数据库
数据库
发布日期:   2022-02-14

一、安装 MySQL

1、安装

删除之前的 mysql

rpm -qa | grep mysql
rpm -e mysql　　// 普通删除模式
rpm -e --nodeps mysql　　// 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除

安装

wget https://repo.mysql.com/mysql57-community-release-el7.rpm
yum -y install mysql57-community-release-el7.rpm
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum -y install mysql-community-server
systemctl start  mysqld.service

找出默认密码

grep "password" /var/log/mysqld.log

登录

mysql -uroot -p

修改密码：默认root密码不能过于简单，否则会出现ERROR，需要设置两个参数使其可以设置简单密码

alter user 'root'@'localhost' identified by '密码';
set global validate_password_policy=0;
set global validate_password_length=1;

无法安装解决方法

使用yum -y install mysql-community-server 安装mysql时候提示：

The GPG keys listed for the “MySQL 5.7 Community Server” repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.

Failing package is: mysql-community-libs-compat-5.7.37-1.el7.x86_64
GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

原因是Mysql的GPG升级了，需要重新获取，使用以下命令即可

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

再执行

yum install mysql-server

2、启动关闭

启动

service mysqld start

关闭

service mysqld stop

重启

service mysqld restart

开机自启

systemctl enable mysqld
systemctl daemon-reload

3、忘记密码

vim /etc/my.cnf

在[mysqld]下添加以下语句

skip-grant-tables

重启

service mysqld restart

登录mysql，无需密码

mysql -uroot -p

修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

如果执行修改密码命令报错

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

则需要先执行以下命令

flush privileges;

在重启

4、允许root用户远程访问

grant all on *.* to root@'%' identified by 'your_password';

如果授权失败，让重置密码，则是当前密码不符合密码策略

以下命令查看密码策略

SHOW VARIABLES LIKE 'validate_password%';

设置密码策略

set global validate_password_policy=LOW;

设置密码（不重新设置也行）

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

5、忽略大小写

修改/etc/my.cnf

# 忽略表名大小写
lower_case_table_names=1

# group by 查询条件可有分组外的字段
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

二、日志

1、错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，错误日志 无法被禁止，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置：

show variables like '%log_error%'

默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为 mysqld.log （Linux系统）或 hostname.err （mac系统）。如果需要制定文件名，则需要在 my.cnf 或者 my.ini 中做如下配置：

[mysqld]
log-error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名

修改配置项后，需要重启MySQL服务以生效。

（1）查询错误日志基本信息

查询错误日志文件信息（存储路径、文件名等）：

SHOW VARIABLES LIKE 'log_err%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
4 rows in set (0.01 sec)

（2）删除\刷新日志

对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除， 以保证MySQL服务器上的 硬盘空间 。MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以 直接删除 。

mysqladmin -uroot -p flush-logs

可能出现以下错误：

mysqladmin: refresh failed; error: 'Could not open file '/var/log/mysqld.log' for
error logging.'

如果出现以上错误，需要执行以下命令

install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

2、二进制日志

（1）介绍

binlog 即 binary log，二进制日志文件，也叫作变更日志（update log）。

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

每次重启会创建新的 binlog 文件

作用：

  • 数据恢复

  • MySQL的主从复制。

在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下：

# 查看是否开启
show variables like '%log_bin%';

# binlog详细信息
show global variables like "binlog%";

#查看所有binlog日志列表
show master logs;

#查看最新一个binlog日志的编号名称，及其最后一个操作事件结束点 
show master status;

#当前的二进制日志文件列表及大小
SHOW BINARY LOGS;

#刷新log日志，立刻产生一个新编号的binlog日志文件，跟重启一个效果 
flush logs;

#清空所有binlog日志 
reset master;

开启 bin_log，编辑 /etc/my.cnf

# 服务唯一ID,开启 binlog 时，必须设置，mysql集群唯一id
server_id=1
# 开启bin_log,默认在/var/lib/mysql目录下
log-bin=mysql_bin
# bin_log格式,默认ROW
binlog-format=ROW

# 可选参数
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
#binlog每个日志文件大小，最大值和默认值都是1G,该设置无法严格控制binlog大小，比如到达临界值时，存在事务未执行完，则不会切换日志文件；重启mysql也会切换日志文件。一般默认值即可
max_binlog_size = 512M
#设置binlog清理时间
expire_logs_days = 7
#监听同步的库, 多个库使用英文逗号“,”拼接
replicate-do-db=test

（2）日志格式

MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

日志格式 含义
STATEMENT 基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。
ROW 基于行的日志记录，记录的是每一行的数据变更。（默认）
MIXED 混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。 
show variables like '%binlog_format%';
  • Statement

每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

缺点：遇到函数可能会出错，比如 now()，由于备份恢复或主从同步的时间不一致导致数据不一致

  • Row

5.1.5版本的MySQL才开始支持row level 的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修 改。

优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下 的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

缺点：binlog日志量较大

  • Mixed

是Statement与Row的结合。

（3）日志查看（备份）

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看，具体语法：

mysqlbinlog [参数选项] logfilename

参数选项：

  • -d：指定数据库名称，只列出指定的数据库的相关操作
  • -o：忽略掉日志中的前n行命令
  • -v：将行事件（数据变更）重构为 SQL 语句
  • -vv：将行事件（数据变更）重构为 SQL 语句，并输出注释信息

如果命令找不到，可先执行以下命令查找可执行文件位置

find / -name mysqlbinlog

解码文件命令使用案例（阿里云 RDS binlog 日志为例）

mysqlbinlog -vv --base64-output=decode-rows /data/mysql-bin.000145 -v > /data/log1.txt
[root@VM-20-13-centos mysql]# mysqlbinlog -v  --base64-output=DECODE-ROWS bin_log.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240727 19:23:22 server id 1  end_log_pos 123 CRC32 0xb1b987fe     Start: binlog v 4, server v 5.7.39-log created 240727 19:23:22 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#240727 19:23:22 server id 1  end_log_pos 154 CRC32 0xaad2a790     Previous-GTIDs
# [empty]
# at 154
#240727 19:28:48 server id 1  end_log_pos 219 CRC32 0xf15f16fb     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#240727 19:28:48 server id 1  end_log_pos 299 CRC32 0x76620282     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1722079728/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 299
#240727 19:28:48 server id 1  end_log_pos 373 CRC32 0x9752c677     Table_map: `rewind_admin`.`open_chat` mapped to number 102
# at 373
#240727 19:28:48 server id 1  end_log_pos 504 CRC32 0x71aa0616     Delete_rows: table id 102 flags: STMT_END_F
### DELETE FROM `rewind_admin`.`open_chat`
### WHERE
###   @1=1719270456389132289
###   @2=1719269648633290753
###   @3=1718798271180263426
###   @4='太阳翻译成英文'
###   @5='太阳的英文是"Sun"。'
###   @6=7
###   @7=7
###   @8=14
###   @9='2023-10-31 16:29:54'
###   @10=0
# at 504
#240727 19:28:48 server id 1  end_log_pos 535 CRC32 0x6a2eca5b     Xid = 104
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

（4）使用日志恢复数据

mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename|mysql –uuser -ppass;

这个命令可以这样理解：

使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容 恢复到数据库中。

  • filename ：是日志文件名。

  • option ：可选项，比较重要的两对option参数是–start-date、–stop-date 和 –start-position、– stop-position。

    • –start-date 和 –stop-date ：可以指定恢复数据库的起始时间点和结束时间点。
    • –start-position和–stop-position ：可以指定恢复数据的开始位置和结束位置。

注意：使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复

  • 导出备份 sql 文件
mysqlbinlog --start-datetime="2023-03-01 00:00:00" --stop-datetime="2023-03-02 00:00:00" --database=your_database_name /path/to/mysql-bin.* > output.sql
  • 直接还原
mysqlbinlog --start-datetime="2023-03-01 00:00:00" --stop-datetime="2023-03-02 00:00:00" --database=your_database_name /path/to/mysql-bin.* | mysql -uroot -p

（5）日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

指令 含义
reset master 删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001重新开始
purge master logs to ‘binlog.编号’ 删除指定编号之前的所有日志
purge master logs before ‘yyyy-mm-dd hh24:mi:ss’ 删除日志为 “yyyy-mm-dd hh24:mi:ss” 之前产生的所有日志

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。

show variables like '%binlog_expire_logs_seconds%';

（6）写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再 把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一 次性写入，所以系统会给每个线程分配一个块内存作为 binlog cache。

image-20240727213407625

write和fsync的时机，可以由参数 sync_binlog 控制，默认是 0 。为0的时候，表示每次提交事务都只 write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的 binglog 会丢失。如下图：

3、通用查询日志

通用查询日志用来 记录用户的所有操作 ，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止 时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志， 还原操作时的具体场景，可以帮助我们准确定位问题。

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下， 查询日志是未开启的。

（1）查询日志开启状态

查看查询日志信息（是否开启、存储路径）

show variables like '%general%';

（2）开启、关闭

永久开启

修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：

# 该选项用来开启查询日志，可选值：0 或 1；0代表关闭，1代表开启
general_log=1

# 设置日志的文件名，如果没有指定，默认的文件名为 host_name.log
general_log_file=mysql_query.log

修改 my.cnf 或者 my.ini 文件，把[mysqld]组下的 general_log 值设置为 OFF 或者把general_log一项 注释掉。修改保存后，再 重启MySQL服务 ，即可生效。

临时开启

SET GLOBAL general_log=on; # 开启通用查询日志
SET GLOBAL general_log_file=’path/filename’; # 设置日志文件保存位置

SET GLOBAL general_log=off; # 关闭通用查询日志

（3）通用查询日志内容


/usr/sbin/mysqld, Version: 5.7.39 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2024-07-27T10:48:11.227667Z     77470 Query     SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
2024-07-27T10:48:11.260005Z     77470 Query     SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.000811*100,3), '') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=2 GROUP BY SEQ, STATE ORDER BY SEQ
2024-07-27T10:49:55.665539Z     77471 Connect   root@112.51.42.110 on  using TCP/IP
2024-07-27T10:49:55.697079Z     77471 Query     SET NAMES utf8mb4
2024-07-27T10:49:55.728309Z     77471 Init DB   rewind_admin
2024-07-27T10:49:55.763236Z     77471 Query     SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'rewind_admin' UNION SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'rewind_admin' UNION SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'rewind_admin'
2024-07-27T10:49:55.797941Z     77471 Query     SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'rewind_admin' ORDER BY TABLE_SCHEMA, TABLE_TYPE
2024-07-27T10:49:55.829589Z     77471 Query     SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'rewind_admin' ORDER BY TABLE_SCHEMA, TABLE_NAME
2024-07-27T10:49:55.906513Z     77471 Query     SELECT DISTINCT ROUTINE_SCHEMA, ROUTINE_NAME, PARAMS.PARAMETER FROM information_schema.ROUTINES LEFT JOIN ( SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, GROUP_CONCAT(CONCAT(DATA_TYPE, ' ', PARAMETER_NAME) ORDER BY ORDINAL_POSITION SEPARATOR ', ') PARAMETER, ROUTINE_TYPE FROM information_schema.PARAMETERS GROUP BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE ) PARAMS ON ROUTINES.ROUTINE_SCHEMA = PARAMS.SPECIFIC_SCHEMA AND ROUTINES.ROUTINE_NAME = PARAMS.SPECIFIC_NAME AND ROUTINES.ROUTINE_TYPE = PARAMS.ROUTINE_TYPE WHERE ROUTINE_SCHEMA = 'rewind_admin' ORDER BY ROUTINE_SCHEMA
2024-07-27T10:49:57.044082Z     77471 Init DB   rewind_admin
2024-07-27T10:49:57.074564Z     77471 Query     SHOW FULL TABLES WHERE Table_type != 'VIEW'
2024-07-27T10:49:57.107028Z     77471 Init DB   rewind_admin
2024-07-27T10:49:57.137054Z     77471 Query     SHOW TABLE STATUS
2024-07-27T10:49:59.668107Z     77471 Init DB   rewind_admin
2024-07-27T10:49:59.698818Z     77471 Query     SELECT * FROM `rewind_admin`.`config_info` LIMIT 0,1000
2024-07-27T10:49:59.731793Z     77471 Query     SHOW TABLE STATUS LIKE 'config_info'
2024-07-27T10:49:59.762014Z     77471 Init DB   rewind_admin
2024-07-27T10:49:59.856787Z     77472 Connect   root@127.0.0.1 on  using TCP/IP
2024-07-27T10:49:59.887159Z     77472 Query     SET NAMES utf8mb4
2024-07-27T10:49:59.917685Z     77472 Init DB   rewind_admin

（4）删除、刷新日志

如果数据的使用非常频繁，那么通用查询日志会占用服务器非常大的磁盘空间。数据管理员可以删除很 长时间之前的查询日志，以保证MySQL服务器上的硬盘空间。

手动删除通用查询日志 mysql_query.log。 使用如下命令重新生成查询日志文件，具体命令如下。刷新MySQL数据目录，发现创建了新的日志文 件。前提一定要开启通用日志。

mysqladmin -uroot -p flush-logs

4、慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。

# 慢查询日志
slow_query_log=1
# 执行时间参数
long_query_time=2

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements 和更改此行为 log_queries_not_using_indexes，如下所述。

# 记录执行较慢的管理语句
log_slow_admin_statements=1
# 记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1

三、主从复制

1、概述

主从复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL 复制的有点主要包含以下三个方面：

  1. 主库出现问题，可以快速切换到从库提供服务。
  2. 实现读写分离，降低主库的访问压力。
  3. 可以在从库中执行备份，以避免备份期间影响主库服务。

主从复制原理（基于二进制日志）

image-20220216211803090

基本原理：

slave会从master读取binlog来进行数据同步

具体步骤：

  • Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
  • 当slave上执行 start slave 命令之后，slave会创建一个 IO 线程用来连接master，请求master中的binlog。
  • 当slave连接master时，master会创建一个 log dump 线程，用于发送 binlog 的内容。在读取 binlog 的内容的操作中，会对主节点上的 binlog 加锁，当读取完成并发送给从服务器后解锁。
  • IO 线程接收主节点 binlog dump 进程发来的更新之后，保存到 中继日志（relay log） 中。
  • slave的SQL线程，读取relay log日志，并解析成具体操作，从而实现主从操作一致，最终数据一致。

2、主库配置

（0）主库备份

mysqldump -uroot -p --routines --single-transaction --set-gtid-purged=off --master-data=2 my_database > my_database.sql
mysqldump --single-transaction -h主机地址 -u用户名 -p密码 --routines --single-transaction --set-gtid-purged=off --master-data=2 数据库名 > 存储的全路径

参数说明：

  • –routines：导出存储过程和函数

  • –single_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务。

  • –master-data：默认等于1，将dump起始(change master to)binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释。

  • -B：备份时添加建库语句

  • -L：数据库表添加只读锁

  • -F：刷新bin-log日志文件，相当于执行flush logs指令，生成新的bin-log日志文件，此后日志都放在新的bin-log日志文件中，利于查找。

  • –set-gtid-purged=off：避免将gtid信息导出，如果不加，会在从库恢复时报错

-–master-data 需要用户有以下权限

报错：mysqldump: Couldn’t execute ‘FLUSH /*!40101 LOCAL */ TABLES’: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

GRANT reload ON *.* TO '用户名'@'%';

（1）修改配置文件

vim /etc/my.cnf

# mysql 服务id，保证整个集群环境中唯一，取值范围：1 到 2的31次方减1，默认为1
server-id=1
# 是否只读，1：只读，0：读写
read-only=0
# 忽略的数据库，指不需要同步的数据库
# binlog-ignore-db=mysql
# 指定要同步的数据库
# binlog-db=mysql

修改后重启 mysql 服务器

systemctl restart mysqld

查询数据库 server-id（阿里云的rds无需进行以上配置）

show variables like '%server_id%';

（2）创建从库账号

登录 mysql ，创建远程连接的账号，并授予主从赋值权限

# 创建 rewind 用户，并设置密码，%表示该用户可在任意主机连接mysql服务
create user 'rewind'@'%' identified with mysql_native_password by '123456';
# 为 rewind 用户分匹配主从复制权限
grant replication slave on *.* to 'rewind'@'%';

（3）查看二进制日志坐标

主库仍在执行业务

从上面主库备份的sql文件中的大约20几行可以获得

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000988', MASTER_LOG_POS=394481;

  • MASTER_LOG_FILE=’mysql-bin.000988’

  • MASTER_LOG_POS=394481

下面为主库停止业务时，可以这么干

show master status;

字段含义说明：

  • file : 从哪个日志文件开始推送日志文件
  • position ： 从哪个位置开始推送日志
  • binlog_ignore_db : 指定不需要同步的数据库

3、RDS作为主库配置

  1. 登录 RDS 控制台，选择目标实例。
  2. 配置从实例读取数据使用的只读账号和授权数据库。
  3. 将从实例的 IP 地址加入主实例的 IP 白名单中。
  4. 查询主实例的 server-id。show variables like '%server_id%';

4、从库配置

（0）恢复数据

方法一 进入命令行

mysql –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】，示例：

mysql –uroot –p123456 -Dtest < /home/test.sql

注意：

如果在sql脚本文件中使用了use 数据库，则-D数据库选项可以忽略

方法二 进入mysql的控制台后，使用source命令执行

Mysql>source 【sql脚本文件的路径全名】 或 Mysql>. 【sql脚本文件的路径全名】，示例：

source /home/test.sql

（1）修改配置文件

/etc/my.cnf

# mysql 服务id，保证整个集群环境唯一，取值范围：1 到 2的31次方减1，必须和主库不一样
server-id=2
# 是否只读，1代表只读，0代表读写
read-only=1

#GTID全局事务ID:主库从库必须保持一致
gtid_mode=on
enforce_gtid_consistency=on

#  上面为必填，下面选填
log_bin=/var/log/mysql/mysql-bin.log
# 避免两台服务器同时做更新时自增长字段的值之间发生冲突
auto-increment-offset=1          
expire_logs_days=10
max_binlog_size=100M
replicate-do-db=exampledb                #需要同步的数据库
replicate-ignore-db=mysql                  #不需要同步的数据库
replicate-ignore-db=information_schema     #不需要同步的数据库
replicate-ignore-db=performance_schema     #不需要同步的数据库

重启 MySQL 服务

systemctl restart mysqld

（2）设置主库

登录从库mysql，mysql -uroot -p，设置主库配置

mysql 8.0.23 版本

change replication source to source_host='xxx.xxx',sourcec_user='xxx',source_password='xxx',source_log_file='xxx',source_log_pos=xxx;

mysql 8.0.23 之前版本

CHANGE MASTER TO MASTER_HOST='XXX.XXX',MASTER_USER='XXX',MASTER_PASSWORD='XXX',MASTER_LOG_FILE='mysql-bin.000988',MASTER_LOG_POS=386021;
参数名 含义 8.0.23 之前
SOURCE_HOST 主库 ip MASTER_HOST
SOURCE_USER 连接主库用户名 MASTER_USER
SOURCE_PASSWORD 连接主库密码 MASTER_PASSWORD
SOURCE_LOG_FILE binlog日志文件名 MASTER_LOG_FILE
SOURCE_LOG_POS binlog日志文件位置 MASTER_LOG_POS

binlog日志文件名、binlog日志文件位置：从上面的主库配置的查看二进制日志坐标处获取

（3）开启同步

开启同步操作

# 8.0.22 之后
start replica;
# 8.0.22 之前
start slave;

停止同步

stop slave;

查看同步状态

# 8.0.22 之后
show replica status\G
# 8.0.22 之前
show slave status\G

查看系统返回信息中 Slave_IO_Running 和 Slave_SQL_Running 的状态是否为 Yes。

到这里主从复制就完成了

6、错误

（1）主从 GTID 不同

The replication receiver thread cannot start because the master has GTID_MOD

错误原因：主库启用 GTID_MOD ，从库未启用

#GTID全局事务ID:主库从库必须保持一致
gtid_mode=on
enforce_gtid_consistency=on

（2）时区表缺失

Error ‘Unknown or incorrect time zone: ‘Asia/Shanghai’’ on query. Default database: ‘’. Query: ‘flush privileges’

找不到时区相关的表

执行下面sql语句是报错的

SET GLOBAL time_zone = 'Asia/Shanghai';

解决方法：到https://dev.mysql.com/downloads/timezones.html下载版本对应的时区文件

image-20220217213912706

导入到 mysql 数据库中

use mysql;
source 路径\timezone_posix.sql

（3）position > file size

-- 在从机停止slave
SLAVE STOP;

-- 在主机查看mater状态
SHOW MASTER STATUS;
-- 在主机刷新日志
FLUSH LOGS;
-- 再次在主机查看mater状态（会发现File和Position发生了变化）
SHOW MASTER STATUS;
-- 修改从机连接主机的SQL，并重新连接即可

四、Docker 配置主从

1、主节点

（1）创建配置文件

默认情况下MySQL的binlog日志是自动开启的，可以通过如下配置（修改my.cnf）定义一些可选配置

创建配置文件

vim /data/mysql/master/conf/my.cnf
[mysqld]
# 服务器唯一id，默认值1
server-id=1
# 设置日志格式，默认值ROW
binlog_format=ROW
# 二进制日志名，默认binlog
# log-bin=binlog
# 设置需要复制的数据库，默认复制全部数据库
#binlog-do-db=mytestdb
# 设置不需要复制的数据库
#binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema

binlog格式说明：

  • binlog_format=STATEMENT（5.7以下默认）：日志记录的是主机数据库的写指令，性能高，但是now()之类的函数以及获取系统参数的操作会出现主从数据不同步的问题。
  • binlog_format=ROW（5.7及以上默认）：日志记录的是主机数据库的写后的数据，批量操作时性能较差，解决now()或者 user()或者 @@hostname 等操作在主从机器上不一致的问题。
  • binlog_format=MIXED：是以上两种level的混合使用，有函数用ROW，没函数用STATEMENT，但是无法识别系统变量

（2）启动容器

docker run -d \
-p 3306:3306 \
-v /data/mysql/master/conf:/etc/mysql/conf.d \
-v /data/mysql/master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql-master \
mysql:8.0.29

修改密码校验方式

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码
docker exec -it mysql-master env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码校验方式,Mysql8.0以上需要修改，为了向下兼容
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

查看 server-id

show variables like '%server_id%';

（3）创建slave用户

-- 创建slave用户
CREATE USER 'rewind_slave'@'%';
-- 设置密码
ALTER USER 'rewind_slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 授予rewind_slave用户复制权限
GRANT REPLICATION SLAVE ON *.* TO 'rewind_slave'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

（4）查询Master状态

SHOW MASTER STATUS;

记下File和Position的值。执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |     1876 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

字段含义说明：

  • file : 当前正在使用的binlog日志文件
  • position ： 从哪个位置开始推送日志
  • binlog_ignore_db : 指定不需要同步的数据库

2、从节点

（1）创建配置文件

创建挂载配置文件 my.cnf，[mysqld]不能漏了

[mysqld]
# 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
server-id=2
# 中继日志名，默认xxxxxxxxxxxx-relay-bin
#relay-log=relay-bin

（2）启动容器

docker run -d \
-p 3307:3306 \
-v /data/docker/mysql/slave1/conf:/etc/mysql/conf.d \
-v /data/docker/mysql/slave1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql-slave1 \
mysql:8.0.29

修改密码检验方式

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码
docker exec -it mysql-slave1 env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码校验方式,Mysql8.0以上需要修改，为了向下兼容
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

（3）配置主从关系

登录从机数据库，执行以下命令

CHANGE MASTER TO MASTER_HOST='主机ip', 
MASTER_USER='rewind_slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1357;
  • MASTER_USER：主机配置的 slave 用户
  • MASTER_PASSWORD：slave 用户密码
  • MASTER_PORT：主机 Mysql 端口
  • MASTER_LOG_FILE：主机当前 binlog 日志文件
  • MASTER_LOG_POS：主机 binlog 日志开始同步位置

（4）启动主从同步

启动从机的复制功能，执行SQL：

START SLAVE;
-- 查看状态（不需要分号）
SHOW SLAVE STATUS\G

查看系统返回信息中 Slave_IO_Running 和 Slave_SQL_Running 的状态是否为 Yes。

到这里主从复制就完成了

3、停止和重置主从复制

-- 在从机上执行。功能说明：停止I/O 线程和SQL线程的操作。
stop slave; 

-- 在从机上执行。功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。
reset slave;

-- 在主机上执行。功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。
-- 用于第一次进行搭建主从库时，进行主库binlog初始化工作；
reset master;

五、分库分表

1、分库分表类型

垂直拆分：垂直分库、垂直分表

水平拆分：水平分库、水平分表

（1）垂直拆分（结构拆分）

image-20220219224440627

（2）水平拆分（数据拆分）

image-20220219224515877

2、分库分表下实现查询

image-20220219225056229
  • shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持 java语言，性能较高。
  • MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。可让代码直接访问mycat即可，代码无需担心分库分表的影响

七、备份与还原

1、mysqldump详解

mysqldump 是 MySQL 自带的逻辑备份工具。

它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

脚本名以 .db 或 .sql 为后缀

mysqldump [选项] 数据库名 [表名] > 脚本名

或

mysqldump [选项] --数据库名 [选项 表名] > 脚本名

或

mysqldump [选项] --all-databases [选项]  > 脚本名
参数名 缩写 含义
–host -h 服务器IP地址
–port -P 服务器端口号
–user -u MySQL 用户名
–pasword -p MySQL 密码
–databases 指定要备份的数据库
–all-databases 备份mysql服务器上的所有数据库
–compact 压缩模式，产生更少的输出
–comments 添加注释信息
–complete-insert 输出完成的插入语句
–lock-tables 备份前，锁定所有数据库表
–no-create-db/–no-create-info 禁止生成创建数据库语句
–force 当出现错误时仍然继续备份操作
–default-character-set 指定默认字符集
–add-locks 备份数据库表时锁定数据库表

实例

备份所有数据库：

mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

备份指定数据库：

mysqldump -uroot -p test > /backup/mysqldump/test.db

备份指定数据库指定表(多个表以空格间隔)

mysqldump -uroot -p  mysql db event > /backup/mysqldump/2table.db

备份指定数据库排除某些表

mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db

常见选项：
–all-databases, -A： 备份所有数据库
–databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。

–force, -f：即使发现sql错误，仍然继续备份
–host=host_name, -h host_name：备份主机名，默认为localhost
–no-data, -d：只导出表结构
–password[=password], -p[password]：密码
–port=port_num, -P port_num：制定TCP/IP连接时的端口号
–quick, -q：快速导出
–tables：覆盖 –databases or -B选项，后面所跟参数被视作表名
–user=user_name, -u user_name：用户名
–xml, -X：导出为xml文件

详细参数:

–all-databases , -A
导出全部数据库。

–all-tablespaces , -Y
导出全部表空间。

–no-tablespaces , -y
不导出任何表空间信息。

–add-drop-database
每个数据库创建之前添加drop数据库语句。

–add-drop-table
每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用–skip-add-drop-table取消选项)

–add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用–skip-add-locks取消选项)

–allow-keywords
允许创建是关键词的列名字。这由表名前缀于每个列名做到。

–apply-slave-statements
在’CHANGE MASTER’前添加’STOP SLAVE’，并且在导出的最后添加’START SLAVE’。

–character-sets-dir
字符集文件的目录

–comments
附加注释信息。默认为打开，可以用–skip-comments取消

–compatible
导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，
要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容。

–compact
导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：–skip-add-drop-table –skip-add-locks –skip-comments –skip-disable-keys

–complete-insert, -c
使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。

–compress, -C
在客户端和服务器之间启用压缩传递所有信息

–create-options, -a
在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)

–databases, -B
导出几个数据库。参数后面所有名字参量都被看作数据库名。

–debug
输出debug信息，用于调试。默认值为：d:t:o,/tmp/mysqldump.trace

–debug-check
检查内存和打开文件使用说明并退出。

–debug-info
输出调试信息并退出

–default-character-set
设置默认字符集，默认值为utf8

–delayed-insert
采用延时插入方式（INSERT DELAYED）导出数据

–delete-master-logs
master备份后删除日志. 这个参数将自动激活–master-data。

–disable-keys
对于每个表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS /;和/!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件，因为它是在插入所有行后创建索引的。该选项只适合MyISAM表，默认为打开状态。

–dump-slave
该选项将导致主的binlog位置和文件名追加到导出数据的文件中。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，在命令前增加说明信息。该选项将会打开–lock-all-tables，除非–single-transaction被指定。该选项会自动关闭–lock-tables选项。默认值为0。

–events, -E
导出事件。

–extended-insert, -e
使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用–skip-extended-insert取消选项。

–fields-terminated-by
导出文件中忽略给定字段。与–tab选项一起使用，不能用于–databases和–all-databases选项

–fields-enclosed-by
输出文件中的各个字段用给定字符包裹。与–tab选项一起使用，不能用于–databases和–all-databases选项

–fields-optionally-enclosed-by
输出文件中的各个字段用给定字符选择性包裹。与–tab选项一起使用，不能用于–databases和–all-databases选项

–fields-escaped-by
输出文件中的各个字段忽略给定字符。与–tab选项一起使用，不能用于–databases和–all-databases选项

–flush-logs
开始导出之前刷新日志。
请注意：假如一次导出多个数据库(使用选项–databases或者–all-databases)，将会逐个数据库刷新日志。除使用–lock-all-tables或者–master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用–lock-all-tables 或者–master-data 和–flush-logs。

–flush-privileges
在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。

–force
在导出过程中忽略出现的SQL错误。

–help
显示帮助信息并退出。

–hex-blob
使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。

–host, -h
需要导出的主机信息

–ignore-table
不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：–ignore-table=database.table1 –ignore-table=database.table2 ……

–include-master-host-port
在–dump-slave产生的’CHANGE MASTER TO..’语句中增加 'MASTER_HOST=<host>，MASTER_PORT=<port>'

–insert-ignore
在插入行时使用INSERT IGNORE语句.

–lines-terminated-by
输出文件的每行用给定字符串划分。与–tab选项一起使用，不能用于–databases和–all-databases选项。

–lock-all-tables, -x
提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭–single-transaction 和–lock-tables 选项。

–lock-tables, -l
开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，–single-transaction是一个更好的选择，因为它根本不需要锁定表。
请注意当导出多个数据库时，–lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

–log-error
附加警告和错误信息到给定文件

–master-data
该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开–lock-all-tables 选项，除非–single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的–single-transaction选项）。该选项自动关闭–lock-tables选项。

–max_allowed_packet
服务器发送和接受的最大包长度。

–net_buffer_length
TCP/IP和socket连接的缓存大小。

–no-autocommit
使用autocommit/commit 语句包裹表。

–no-create-db, -n
只导出数据，而不添加CREATE DATABASE 语句。

–no-create-info, -t
只导出数据，而不添加CREATE TABLE 语句。

–no-data, -d
不导出任何数据，只导出数据库表结构。

–no-set-names, -N
等同于–skip-set-charset

–opt
等同于–add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, –disable-keys 该选项默认开启, 可以用–skip-opt禁用.

–order-by-primary
如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。

–password, -p
连接数据库密码

–pipe(windows系统可用)
使用命名管道连接mysql

–port, -P
连接数据库端口号

–protocol
使用的连接协议，包括：tcp, socket, pipe, memory.

–quick, -q
不缓冲查询，直接导出到标准输出。默认为打开状态，使用–skip-quick取消该选项。

–quote-names,-Q
使用（`）引起表和列名。默认为打开状态，使用–skip-quote-names取消该选项。

–replace
使用REPLACE INTO 取代INSERT INTO.

–result-file, -r
直接输出到指定文件中。该选项应该用在使用回车换行对（\r\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。

–routines, -R
导出存储过程以及自定义函数。

–set-charset
添加’SET NAMES default_character_set’到输出文件。默认为打开状态，使用–skip-set-charset关闭选项。

–single-transaction
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和–lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用–quick 选项。

–dump-date
将导出时间添加到输出文件中。默认为打开状态，使用–skip-dump-date关闭选项。

–skip-opt
禁用–opt选项.

–socket,-S
指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock

–tab,-T
为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。

–tables
覆盖–databases (-B)参数，指定需要导出的表名。

–triggers
导出触发器。该选项默认启用，用–skip-triggers禁用它。

–tz-utc
在导出顶部设置时区TIME_ZONE=’+00:00’ ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。

–user, -u
指定连接的用户名。

–verbose, –v
输出多种平台信息。

–version, -V
输出mysqldump版本信息并退出

–where, -w
只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。

–xml, -X
导出XML格式.

–plugin_dir
客户端插件的目录，用于兼容不同的插件版本。

–default_auth
客户端插件默认使用权限。

2、备份案例

1.备份全部数据库的数据和结构

mysqldump -uroot -p123456 -A >F:\all.sql

2.备份全部数据库的结构（加 -d 参数）

mysqldump -uroot -p123456 -A-d>F:\all_struct.sql

3.备份全部数据库的数据(加 -t 参数)

mysqldump -uroot -p123456 -A-t>F:\all_data.sql

4.备份单个数据库的数据和结构(,数据库名mydb)

mysqldump -uroot-p123456 mydb>F:\mydb.sql

5.备份单个数据库的结构

mysqldump -uroot -p123456 mydb-d>F:\mydb.sql

6.备份单个数据库的数据

mysqldump -uroot -p123456 mydb-t>F:\mydb.sql

7.备份多个表的数据和结构（数据，结构的单独备份方法与上同）

mysqldump -uroot -p123456 mydb t1 t2>f:\multables.sql

8.一次备份多个数据库

mysqldump -uroot -p123456 --databases db1 db2>f:\muldbs.sql

3、还原案例

还原部分分（1）mysql命令行source方法 和 （2）系统命令行方法

1.还原全部数据库:

(1) mysql命令行：

source f:\all.sql

(2) 系统命令行： 

mysql -uroot -p123456

2.还原单个数据库(需指定数据库)

(1) mysql命令行

mysql>use mydb
mysql>source f:\mydb.sql

(2) 系统命令行

mysql -uroot -p123456 mydb

3.还原单个数据库的多个表(需指定数据库)

(1) mysql命令行

mysql>use mydb
mysql>source f:\multables.sql

(2) 系统命令行

mysql -uroot -p123456 mydb

4.还原多个数据库，（一个备份文件里有多个数据库的备份，此时不需要指定数据库）

(1) mysql命令行：

mysql>source f:\muldbs.sql

(2) 系统命令行： 

mysql -uroot -p123456

4、数据归档

八、MySQL误删数据还原

数据库
  目录
Copyright © 2020-2024 2020 Rewind | Powered by Hexo | Theme Matery
|  总访问量:  次 |  总访问人数:  人
载入运行时间...
闽ICP备2020023126号-1