mysql 使用

mysql 连接使用localhost连接

ubuntu20使用apt安装mysql8后，navicat连接localhost报错，更改连接设置即可正常连接。(使用127.0.0.1无需修改配置)
编辑连接，高级，使用命名管道套接字，/var/run/mysqld/mysqld.sock
参见 mysql连接方式

显示行号

SELECT (@rowNum := @rowNum + 1) AS "rowNum", u.* 
FROM `user` u,  ( SELECT @rowNum := 0 ) r;

where不等于时结果少了NULL

-- IFNULL效率更好
-- SELECT `name` FROM `user` WHERE `name` != 'AAA' OR `name` IS NULL
SELECT `name` FROM `user` WHERE IFNULL(`name`,'') !='AAA'

查看表占用空间

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `used_size` AS
SELECT `information_schema`.`tables`.`TABLE_SCHEMA` AS `数据库`
    , sum(`information_schema`.`tables`.`TABLE_ROWS`) AS `记录数`
    , sum(truncate(`information_schema`.`tables`.`DATA_LENGTH` / 1024 / 1024, 2)) AS `数据容量(MB)`
    , sum(truncate(`information_schema`.`tables`.`INDEX_LENGTH` / 1024 / 1024, 2)) AS `索引容量(MB)`
FROM `information_schema`.`TABLES`
GROUP BY `information_schema`.`tables`.`TABLE_SCHEMA`
ORDER BY sum(`information_schema`.`tables`.`DATA_LENGTH`) DESC, sum(`information_schema`.`tables`.`INDEX_LENGTH`) DESC

SQL分析

EXPLAIN FORMAT=JSON
SELECT * FROM sys_user LIMIT 1;

SET profiling=1;
SELECT * FROM sys_user LIMIT 1
SHOW PROFILE;

SET profiling=1;
SELECT * FROM sys_user LIMIT 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
SHOW PROFILE ALL FOR QUERY 1;

查看优化后的SQL

EXPLAIN 
SELECT * FROM `user`;
SHOW WARNINGS;

mysql workbench

下载地址

Mysql主从搭建

先创建三个文件

docker-compose.yml

services:
  mysql:
    container_name: mysql
    image: mysql:8.0
    ports:
      - "3306:3306"
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    networks:
      - free
    # network_mode: "host"
    volumes:
      - ./mysql/var_lib_mysql:/var/lib/mysql
      - ./mysql/master.cnf:/etc/my.cnf
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_unicode_ci
    mem_limit: 1024m

  mysql2:
    container_name: mysql2
    image: mysql:8.0
    ports:
      - "3307:3306"
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    volumes:
      - ./mysql/slave.cnf:/etc/my.cnf
    networks:
      - free
    command:
      --character-set-server=utf8mb4 
      --collation-server=utf8mb4_unicode_ci
    mem_limit: 1024m
    depends_on:
      - mysql

master.cnf

[mysqld]
# 禁用主机名缓存
host_cache_size=0
# 跳过主机名解析
skip-name-resolve
# 主数据库端ID号
server_id = 101
# 开启GTID模式
gtid-mode = ON
enforce-gtid-consistency = 1
# 开启二进制日志
log-bin = mysql-bin
# 不需要复制的数据库名（mysql库一般不同步）
binlog-ignore-db = mysql
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
# 二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除
expire_logs_days = 7
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format = mixed
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors = 1062
# log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates = ON

# 将函数复制到slave
log_bin_trust_function_creators = 1

[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

slave.cnf

[mysqld]
# 禁用主机名缓存
host_cache_size=0
# 跳过主机名解析
skip-name-resolve
# 从数据库端ID号
server_id = 102
# 开启GTID模式
gtid-mode = ON
enforce-gtid-consistency = 1
# 开启二进制日志
log-bin = mysql-bin
# 不需要复制的数据库名（mysql库一般不同步）
binlog-ignore-db = mysql
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
# 二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除
expire_logs_days = 7
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format = mixed
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors = 1062
# log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates = ON

# 从服务器中继日志的位置
relay_log = mysql-relay-bin
# 设置为只读
read_only=1

[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

配置主从

启动默认配置的容器

docker compose up -d mysql mysql2

主服务器执行

CREATE USER 'slave' @'%' IDENTIFIED BY '123456';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave' @'%';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;

从服务器执行

CHANGE REPLICATION SOURCE TO 
MASTER_HOST='mysql'
,MASTER_USER='slave'
,MASTER_PASSWORD='123456'
,MASTER_PORT=3306
,GET_MASTER_PUBLIC_KEY=1
,MASTER_AUTO_POSITION=1;

START SLAVE;
SHOW SLAVE STATUS;

从服务重新配置需先停止同步

STOP SLAVE;
RESET MASTER;

参数优化

-- innodb_buffer_pool_size 缓冲池大小，可设置为总内存的 70% 到 80%，小内存可设置为50%或更少。
-- join_buffer_size sort_buffer_size read_rnd_buffer_size
-- 连接，排序，随机读的缓冲大小。建议为默认值（256K）的 2 倍至 8 倍之间。
-- 设为1G大小
-- SET PERSIST GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024;
-- SET PERSIST GLOBAL innodb_buffer_pool_size = 1G;
SELECT VARIABLE_NAME, CONCAT( ROUND( VARIABLE_VALUE / 1024 / 1024, 4 ), 'MB' ) AS size 
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
 'innodb_log_buffer_size', 'innodb_buffer_pool_size'
,'join_buffer_size', 'sort_buffer_size', 'read_rnd_buffer_size'
);

查看数据库状态

SHOW STATUS
SHOW ENGINE INNODB STATUS
SHOW PROCESSLIST