跳到主要内容

MySQL FAQ

select version();

限制​

  • bind 参数 65535
  • max_prepared_stmt_count=16382
    • 最大 prepare 数量
  • 4096 列/表
  • 65,535 byte/row
    • blob 和 text 占用 9-12 byte
TypeMaximum length
TINYTEXT255 (2 8−1) bytes
TEXT65,535 (216−1) bytes = 64 KiB
MEDIUMTEXT16,777,215 (224−1) bytes = 16 MiB
LONGTEXT4,294,967,295 (232−1) bytes = 4 GiB
ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

debug connections​

-- 默认 151 - 150 普通用户, 1 super
show variables like 'max_connections';
show status where variable_name = 'threads_connected';
show processlist;

select id,
       user,
       host,
       db,
       command,
       time,
       state,
       info
from information_schema.processlist

修改密码​

select user,host from mysql.user;
select user,host,db from mysql.db;

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

-- 允许 root 远程
-- root@localhost -> root@%
UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='root';
FLUSH PRIVILEGES;

-- UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='username';
-- GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
-- GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';

估算表大小​

-- 行数
select table_rows, TABLE_SCHEMA, TABLE_NAME
from information_schema.tables
order by TABLE_ROWS desc;

select (DATA_LENGTH / AVG_ROW_LENGTH) as total_rows, TABLE_NAME
from INFORMATION_SCHEMA.TABLES
order by total_rows desc;

-- 数据量
select sys.format_bytes(DATA_LENGTH), TABLE_NAME
from INFORMATION_SCHEMA.TABLES
order by DATA_LENGTH desc;

-- DB 大小
SELECT table_schema                                            "DB Name",
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;

Backup​

Expression of generated column 'status' contains a disallowed function: curdate.​

MySQL virtual 列不能用 CONNECTION_ID(), CURRENT_USER(), NOW().

删除数据不会释放空间​

  • 除非使用 innodb_file_per_table
  • optimize table 会减小 .idb, 但不会减小 ibdata1

1418​

1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

dump​

mysqldump --single-transaction --routines --triggers --all-databases > MySQLData.sql