Skip to content

Latest commit

 

History

History
299 lines (246 loc) · 16.2 KB

mysql.md

File metadata and controls

299 lines (246 loc) · 16.2 KB

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.

Learn

Practice

Installation

Ubuntu

  • apt update && apt install mysql-server

CentOS

  • 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 修改密码，并允许远程连接 
    use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'XXX_NEW_PASSWORD';
update user set host = '%' where user = 'root';
flush privileges;
  • 使用 firewall-cmd 开放端口
    • firewall-cmd --zone=public --add-port=3306/tcp --permanent

Run/Stop

  • As a service
    • RPM package platforms service mysql {start|stop|restart|status}
    • Debian package platforms systemctl {start|stop|restart|status} mysqld
  • Command line
    • Run mysqld/mysqld_safe &
    • Stop mysqladmin -uroot -p shutdown

configuration

User

  • 创建用户： 
    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 
    use mysql;
SET PASSWORD = PASSWORD('pwd');  -- 修改当前登陆用户密码
update user set plugin="mysql_native_password";  -- mysql5.7+ required
update user set authentication_string=password('pwd') where user='root';
flush privileges;
  • 使用 mysqladmin 修改密码: $ mysqladmin -u root password -p

Privileges

  • 权限列表：http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
  • 查询权限：show grants for root@'localhost';
  • 修改数据库权限 
    -- 全部权限
GRANT ALL ON *.* TO 'user'@'%' IDENTIFIED BY 'pwd';
-- 该用户可以给其他用户授权
GRANT privileges ON db.tbl TO 'user'@'host' IDENTIFIED BY 'pwd' WITH GRANT OPTION;
-- 特定权限
GRANT SELECT,UPDATE,INSERT,DELETE ON testdb.* TO 'user'@'host' IDENTIFIED BY 'pwd';
  • 取消权限 
    REVOKE privilege ON databasename.tablename FROM 'user'@'host';
REVOKE SELECT ON testdb.* FROM 'user'@'localhost';
  • 允许远程访问 
    use mysql;
update user set host = '%' where user = 'XXX';
  • ⭐以上均需刷新生效：flush privileges;

Optimization

Storage engine

InnoDB

  • 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

Alternative malloc library

jemalloc OR tcmalloc/gperftools

  • 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

Backup/Restore

bin-log

mysqldump

mysqlpump

xtrabackup

Replication

Master-slave replication

Master-master replication

Sharding

Tools

  • 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

FAQs

Resources