MySQL使用技巧整理

最新推荐文章于 2024-07-23 13:22:23 发布

TerrenceTian

最新推荐文章于 2024-07-23 13:22:23 发布

阅读量328

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/xiaoxing598/article/details/128887657

版权

title: MySQL使用技巧整理
date: 2021-04-11 00:00:00
tags:

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
使用语句alter table T engine=InnoDB替换alter table T drop index k; alter table T add index(k);。

假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：
1. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
2. 执行主备切换；
3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。
平时在做变更的时候，你应该考虑类似 gh-ost 这样的方案，更加稳妥。但是在需要紧急处理时，上面这个方案的效率是最高的。

重启数据库，并使用–skip-grant-tables 参数启动。
在 MySQL 8.0 版本里，如果你启用–skip-grant-tables 参数，MySQL 会默认把 --skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接。

在my.cnf中增加下面配置

slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 0 #确保每个语句都会被记录入慢查询日志

现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的理由有很多，我来给你举一个可以直接看出来的好处：恢复数据。
MariaDB 的Flashback工具就是基于上面介绍的原理来回滚数据的。
用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;，这个命令的意思是，将 master.000001 文件里面从第 2738 字节到第 2973 字节中间这段内容解析出来，放到 MySQL 去执行。

业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（建议把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。
那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。这个要怎么解决呢？
1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

命令 mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql。
–single-transaction 在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；
–add-locks=0 表示在输出的文件结果里，不增加"LOCK TABLES t WRITE;"；
–no-create-info 不需要导出表结构；
–set-gtid-purged=off 不输出跟 GTID 相关的信息；
如果你希望生成的文件中一条 INSERT 语句只插入一行数据的话，可以在执行 mysqldump 命令时，加上参数 –skip-extended-insert。

假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：
1. 执行 create table r like t，创建一个相同表结构的空表；
2. 执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
3. 执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
4. 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；
5. 执行 unlock tables，这时候 t.cfg 文件会被删除；
6. 执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。
复制时的注意点
1. 在第 3 步执行完 flsuh table 命令之后，db1.t 整个表处于只读状态，直到执行 unlock tables 命令后才释放读锁；
2. 在执行 import tablespace 的时候，为了让文件里的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件（比如 TB 级别），每个数据页都需要修改，所以你会看到这个 import 语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。

create user 'ua'@'%' identified by 'pa'; 创建一个用户；
grant all privileges on *.* to 'ua'@'%' with grant option; 全局权限，作用于整个MySQL实例，这些权限信息保存在 mysql库的user表里；
revoke all privileges on *.* from 'ua'@'%'; 回收上面的grant语句赋予的权限；
grant all privileges on db1.* to 'ua'@'%' with grant option; 除了全局权限，MySQL 也支持库级别的权限定义。
grant all privileges on db1.t1 to 'ua'@'%' with grant option; 表权限定义存放在表 mysql.tables_priv 中；
grant SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option; 列权限定义存放在表 mysql.columns_priv 中；
不建议的写法 grant super on *.* to 'ua'@'%' identified by 'pa';，因为这种写法很容易就会不慎把密码给改了。