mysql - doubility-sky/daydayup GitHub Wiki

The MySQL™ software delivers a very fast, multithreaded, multi-user, and robust SQL (Structured Query Language) database server. MySQL Server is intended for mission-critical, heavy-load production systems as well as for embedding into mass-deployed software.

• MySQL Documentation
• MySQL 5.7 Reference Manual
• TiDB

• Complete list of new features in MySQL 5.7

• MySQL运维笔记
• Linux运维菜 - MySQL
• mysql中的慢查询日志
  • 慢查询日志分析工具（mysqldumpslow）
  • 使用Show Profile进行分析
• 值75亿美金的GitHub都不用分布式，你的数据库也不需要
  • Partitioning GitHub’s relational databases to handle scale

• apt update && apt install mysql-server

• CentOS 7 安装 Mysql5.5 或自定义版本 RPM 方式
• Install MySQL on CentOS 7 Operating System
• centos7 MySQL 数据库安装和配置
• 下载 mysql 的 repo 源，安装 mysql-community-release
  • wget https://dev.mysql.com/get/mysql57-community-release-el7.rpm
  • rpm -ivh mysql57-community-release-el7.rpm && yum install mysql-server
• 启动/停止/重启 service mysqld start/stop/restart
• 关闭强密码验证 vi /etc/my.cnf 添加 validate-password=OFF 至末尾，如已开启需重启
• 获取临时密码 grep "temporary password" /var/log/mysqld.log
• 连接 mysql -uroot -p 修改密码，并允许远程连接

  ALTER USER 'root'@'localhost' IDENTIFIED BY 'XXX_NEW_PASSWORD';
  -- 另建远程管理账号，而不是把 root@localhost 改掉；host 尽量限定网段而非 '%'
  CREATE USER 'admin'@'10.0.0.%' IDENTIFIED BY 'XXX_ADMIN_PASSWORD';
  GRANT ALL ON *.* TO 'admin'@'10.0.0.%' WITH GRANT OPTION;

• 使用 firewall-cmd 开放端口
  • firewall-cmd --zone=public --add-port=3306/tcp --permanent

• As a service（服务名不同：Oracle RPM（EL/Fedora）是 mysqld，Debian/Ubuntu 与 SLES 是 mysql，发行版自带的 MariaDB 是 mariadb）
  • RPM package platforms systemctl {start|stop|restart|status} mysqld
  • Debian package platforms systemctl {start|stop|restart|status} mysql
• Command line
  • Run mysqld/mysqld_safe &
  • Stop mysqladmin -uroot -p shutdown

• interactive_timeout 和 wait_timeout
• 开启binlog：配置文件中设置 log-bin=mysql-bin
• 查找配置文件路径 mysqld --verbose --help |grep -A 1 'Default options'

• 创建用户：

  CREATE USER 'user'@'host' IDENTIFIED BY 'pwd';
  CREATE USER 'user'@'%' IDENTIFIED BY 'pwd'; -- 全部主机
  CREATE USER 'user'@'localhost' IDENTIFIED BY 'pwd'; -- 本地登陆
  CREATE USER 'user'@'192.168.1.101' IDENTIFIED BY 'pwd'; -- 指定主机

• 删除用户：

  DROP USER 'user'@'%';
  DROP USER 'user'@'host';

• 修改密码: $ mysql -u root
  • MySQL 5.7 / 8.0 通用写法（PASSWORD() 函数已在 8.0 移除，不要再直接 UPDATE mysql.user）：

    ALTER USER USER() IDENTIFIED BY 'pwd';              -- 修改当前登录用户密码
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'pwd';  -- 修改指定用户密码

  • 8.0 默认认证插件是 caching_sha2_password，旧客户端连不上时可指定插件：

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pwd';

    • mysql_native_password 自 8.0.34 起 deprecated，8.4 起默认不加载（ERROR 1524 Plugin ... is not loaded），需在 [mysqld] 中显式 mysql_native_password=ON；9.0 已彻底移除
  • ⚠️ 不带 WITH 的 IDENTIFIED BY 会把该账号的认证插件重置为服务器默认插件。想保留原插件，用 SET PASSWORD FOR 'user'@'host' = 'pwd';，或显式写出 IDENTIFIED WITH
  • ALTER USER / SET PASSWORD 会立即生效，不需要 flush privileges;
• 使用 mysqladmin 修改密码: $ mysqladmin -u root -p password 会依次提示输入当前密码与新密码
  • 不要写成 mysqladmin -u root -p password 'NEW'，新密码会明文出现在 ps 和 shell history 中

• 权限列表：http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
• 查询权限：show grants for root@'localhost';
• 修改数据库权限（8.0 移除了 GRANT ... IDENTIFIED BY，必须先 CREATE USER 再 GRANT）

  -- GRANT 不再隐式建号，账号必须先存在
  CREATE USER 'user'@'host' IDENTIFIED BY 'pwd';
  -- 以下三条是三种粒度的示例，按需选一（授了 ALL 之后后两条基本没有意义，但 WITH GRANT OPTION 除外：全局 ALL 不含它）
  -- 全部权限
  GRANT ALL ON *.* TO 'user'@'host';
  -- 该用户可以给其他用户授权
  GRANT privileges ON db.tbl TO 'user'@'host' WITH GRANT OPTION;
  -- 特定权限
  GRANT SELECT,UPDATE,INSERT,DELETE ON testdb.* TO 'user'@'host';

• 取消权限

  REVOKE privilege ON databasename.tablename FROM 'user'@'host';
  REVOKE SELECT ON testdb.* FROM 'user'@'localhost'; 

• 允许远程访问：账号的可登录来源是 'user'@'host' 里的 host，不要直接改 mysql.user

  -- 推荐：保留本地账号，另建一个限定来源网段的远程账号
  CREATE USER 'XXX'@'10.0.0.%' IDENTIFIED BY 'pwd';
  GRANT ALL ON db.* TO 'XXX'@'10.0.0.%';
  -- 或者把已有账号整体改为可远程（注意这是「移动」，原 localhost 账号不再存在）
  RENAME USER 'XXX'@'localhost' TO 'XXX'@'10.0.0.%';

  • Debian/Ubuntu 的 root 默认用 auth_socket 认证，只能走 Unix socket。这类账号改完 host 仍然连不上，需要先给它换个带密码的插件：ALTER USER ... IDENTIFIED BY 'pwd';（只有为了迁就旧客户端才需要 IDENTIFIED WITH mysql_native_password，且 8.4+ 该插件默认不加载，见上文）
  • 8.0+ 新建的账号默认是 caching_sha2_password，首次认证（以及重启 / flush privileges 清空缓存后）需要 TLS 或服务端 RSA 公钥；命中缓存后普通连接即可
  • 8.0.22+ 若被改名的账号是任何存储对象（视图/触发器/事件/存储过程/函数）的 DEFINER，RENAME USER 会直接报错；有 SET_USER_ID 权限时改名会成功但只给一个 warning，那些存储对象的 DEFINER 就悬空了
• ⭐CREATE/ALTER/RENAME/DROP USER、GRANT、REVOKE、SET PASSWORD 都会立即重新加载权限表，不需要 flush privileges;；只有直接 INSERT/UPDATE/DELETE 授权表（mysql.user 等）后才必须执行 flush privileges;
  • 已连接会话的静态全局权限与密码要等下次连接才生效（动态权限立即生效），这与 flush privileges; 无关

• MySQL 性能优化总结
• MySQL Optimal Configuration Template
  • mysql-5.7 配置文件 参数优化
• MySQL 5.7 Performance Tuning After Installation - zh-CN

• 为什么你要用 InnoDB, 而不是 MyISAM ？
• MyISAM 和 InnoDB 区别和应用场景
• MyISAM
  • 读取性能极佳
  • 不支持行锁(只有表锁)，不支持事务，不支持外键，不支持崩溃后的安全恢复
  • 不推荐使用（除非需执行大量 SELECT 查询）
• InnoDB
  • 支持行锁，采用 MVCC 来支持高并发，但有可能死锁；
  • 支持事务；支持外键；支持崩溃后的安全恢复；支持全文索引（MySQL 5.6+）
  • 推荐使用（MySQL 5.5+ 默认）

• MySQL Performance: InnoDB Buffers & Directives
• What is a big innodb_log_file_size?
• 优化 MySQL：3 个简单的小调整 —— 调整关键配置(20%)，可得到 80% 性能提升，:star:Pareto principle
  1. 所有表使用 innodb 引擎
  2. 加大 innodb_buffer_pool_size，最大可使用物理机器的 70%
  3. 设置 innodb_buffer_pool_instances 来分割 innodb_buffer_pool_size，以提高并发性
• MySQL性能调优 – 你必须了解的15个重要变量
  • innodb_buffer_pool_size 最重要
• MySQL Innodb 并发涉及参数
  • 当并发用户线程数量小于 64，建议设置 innodb_thread_concurrency=0 (保持默认不变)
• Config example:

  # Ubuntu18.04LTS, CPU16核心, 32G内存为例，同时运行其他业务
  # vi /etc/mysql/mysql.conf.d/mysqld.cnf
  max_allowed_packet = 256M
  max_connections = 1024
  wait_timeout = 600
  # 慢查询日志
  slow_query_log = 1
  # default is /var/lib/mysql/xxx-slow.log
  slow_query_log_file = /var/log/mysql/mysql-slow.log
  log_queries_not_using_indexes = 1
  log_timestamps = system
  # InnoDB 相关
  innodb_buffer_pool_size = 16G
  innodb_buffer_pool_instances = 8
  innodb_read_io_threads = 10
  innodb_write_io_threads = 6
  innodb_log_file_size = 2G

• Configuring systemd for MySQL
• Migrating from mysqld_safe to systemd

• apt install libjemalloc-dev / apt install google-perftools

  • mkdir -p /etc/systemd/system/mysql.service.d / systemctl edit mysql
  • vi /etc/systemd/system/mysql.service.d/override.conf

    [Service]
    Environment="LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so"
    # OR 
    Environment="LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libtcmalloc.so.4"

  • systemctl daemon-reload
  • service mysql restart

• How to check the memory allocator used by my mysql 5.7.20

  • lsof -p $(pidof mysqld) | grep mem
  • lsof -n |grep jemalloc

• Compile and install jemalloc ❌ Not recommend!

  View details

  • 安装 jemalloc for mysql
  • git clone https://github.com/jemalloc/jemalloc
  • cd jemalloc && git checkout master
  • ./autogen.sh && ./configure && make && make install
  • cp /usr/local/lib/libjemalloc.* /usr/lib/
  • run mysqld with jemalloc
    • export LD_PRELOAD=/usr/lib/libjemalloc.so && mysqld &
  • FAQ
    • mkdir /var/run/mysqld && chown mysql:mysql /var/run/mysqld

• 解锁 MySQL 备份恢复的 4 种正确姿势

• Binlog 日志使用总结
• 利用 mysql 的 binlog 恢复数据
• 关于 binary log 那些事

• Mysqldump 备份说明及数据库备份脚本分享
• 基于 mysqldump 做备份恢复

• mysqlpump 使用说明

• Percona XtraBackup 2.4 Documentation
  • Percona XtraBackup is a set of following tools:
  • innobackupex is the symlink for xtrabackup. innobackupex still supports all features and syntax as 2.2 version did, but is now deprecated and will be removed in next major release.
  • xtrabackup a compiled C binary that provides functionality to backup a whole MySQL database instance with MyISAM, InnoDB, and XtraDB tables.
  • xbcrypt utility used for encrypting and decrypting backup files.
  • xbstream utility that allows streaming and extracting files to/from the xbstream format.
  • xbcloud utility used for downloading and uploading full or part of xbstream archive from/to cloud.
  • After Percona XtraBackup 2.3 release the recommend way to take the backup is using the xtrabackup script. More information on script options can be found in how to use xtrabackup.
• xtrabackup 备份 - 原理与应用
• 热备工具 Xtrabackup 简介
  • xtrabackup 安装及使用
• 使用 percona xtraback 实施物理备份
• Xtrabackup 备份还原
  • Xtrabackup 备份和恢复应用, shell 自动化
  • 从 Xtrabackup 完整备份中恢复单个表
• innobackupex - DEPRECATED
  • innobackupex 备份工具使用总结
  • innobackupex 备份恢复+增量备份与恢复

• Mysql 主从复制配置 单向/双向

• 搭建 MySQL 5.7.19 主从复制，以及复制实现细节分析
• MySQL 5.7 基于 GTID 的主从复制实践
• MySQL 主从备份配置
• MySQL 主从复制架构使用方法、
• MySQL 主从复制——主库已有数据的解决方案
• 3 分钟解决 MySQL 1032 主从错误

• MySQL双主（主主）架构方案
• MySQL主主复制架构使用方法

• system-design-primer - sharding
• 这四种情况下，才是考虑分库分表的时候！
• 三分钟理解分库分表

• mycli: A command line client for MySQL that can do auto-completion and syntax highlighting.
• phpMyAdmin: A web interface for MySQL and MariaDB.
• Sequel Ace is the "sequel" to longtime macOS tool Sequel Pro. Sequel Ace is a fast, easy-to-use Mac database management application for working with MySQL & MariaDB databases.
• Querious: THE BEST MYSQL TOOL FOR MACOS
• See also: database tools

• Lost connection to MySQL server at 'reading initial communication packet
• Ubuntu 开启 mysql 远程连接

  vi /etc/mysql/mysql.conf.d/mysqld.cnf
    #bind-address = 127.0.0.1
  service mysql restart

• Cenos7 MySQL ERROR 1044 (42000):Access denied for user ''@'localhost' to databa...
• ERROR 1698 (28000): Access denied for user 'root'@'localhost' at Ubuntu 18.04
  • Change user password in MySQL 5.7 with “plugin: auth_socket”
  • If you install 5.7 and don’t provide a password to the root user, it will use the auth_socket plugin.
• [Resolved] When I faced “#1273 – Unknown collation: ‘utf8mb4_0900_ai_ci'” Error
  • utf8mb4_0900_ai_ci --> utf8mb4_general_ci.
• Truncate Slow Query Log in MySQL
  • > /var/lib/mysql/XXX-slow.log
• Did your logging stop working after you set up logrotate? Then this post might be for you.
• MySQL 获取行数

  mysql -h$MYHOST -P$MYPORT -u$MYUSER -p$MYPWD -N -e "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$DBNAME'"

• awesome-mysql: A curated list of awesome MySQL software, libraries, tools and resources
• MySQL 资源大全中文版，分析工具、备份、性能测试、配置、部署、GUI 等