Mysql¶

Install¶

Local¶

Windows

install and start
解压 d:\myproject\mysq
修改 my.ini

[mysqld]
# Set MySQL base (installed) directory
# @@ Change to your MySQL installed directory @@
basedir=d:/myProject/mysql

# Set MySQL data directory
# @@ Change to sub-directory "data" of your MySQL installed directory  @@
datadir=d:/myProject/mysql/data

# Run the server on this TCP port number
port=8888

[client]
# MySQL client connects to the server running on this TCP port number
port=8888

start server

# cd <mysql_home>\bin
cd \javaProject\mysql-5.6.17-winx64\bin
mysqld --console

run client

mysql -u root

mysql> select host, user, password from mysql.user;
UPDATE user SET Password = PASSWORD('mysql') WHERE user = 'root';  -- change password.

mysql>
create user 'myuser'@'localhost' identified by 'xxxx';
grant all on *.* to 'myuser'@'localhost';

grant all privileges  on *.* to root@'%' identified by "mysql";

flush privileges; --刷新系统权限表。

-- 如果开启远程连接database，需要修改host为%;
create user 'myuser'@'%' identified by 'xxxx';

ubuntu

Tips¶

content	help-sql
number of rows(`所有表行数`)	`select table_name, table_rows from INFORMATION_SCHEMA.tables where TABLE_SCHEMA = 'weather' order by table_rows desc;`
create db	`CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`
set root-pwd	`update mysql.user set password=PASSWORD('NEW PASSWORD') where user='root';`
search user/pwd	`select host, user, password from mysql.user;`
`是否独立表空间`	`show variables like '%per_table';`
size of table	SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "weather" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;
version	`select version();`

Performance¶

重要配置参数优化¶

[mysqld]
# 缓冲池配置
innodb_buffer_pool_size = 1G        # 建议设置为系统内存的50%-70%
innodb_buffer_pool_instances = 8    # CPU核心数量相同

# IO相关配置
innodb_file_per_table = 1          # 独立表空间
innodb_flush_log_at_trx_commit = 2 # 降低日志同步频率,提升性能
innodb_flush_method = O_DIRECT     # 避免双重缓冲

# 连接数配置
max_connections = 1000             # 最大连接数
thread_cache_size = 16             # 线程缓存数

# 查询缓存(MySQL 8.0已移除)
query_cache_size = 0               # 建议关闭查询缓存
query_cache_type = 0

# 临时表配置
tmp_table_size = 64M              # 内存临时表大小
max_heap_table_size = 64M         # 用户可创建的内存表大小

# 日志配置
slow_query_log = 1                # 开启慢查询日志
long_query_time = 2               # 超过2秒记录慢查询

关键优化建议¶

定期分析表和优化表:

ANALYZE TABLE table_name;
OPTIMIZE TABLE table_name;

合理设置索引:

-- 为经常查询的列添加索引
ALTER TABLE table_name ADD INDEX idx_name (column_name);

-- 删除不常用的索引
ALTER TABLE table_name DROP INDEX idx_name;

监控系统状态:

-- 查看系统状态变量
SHOW GLOBAL STATUS;

-- 查看当前连接/进程
SHOW PROCESSLIST;

Docker¶

prepare

# create volume for persistent data.
docker volume create mysql-data
docker volume ls
# check mountpoint
docker volume inspect mysql-data

custom conf

此处适配100并发/2H2G

[mysqld]
# 基础设置
default-time-zone = '+08:00'
user = mysql
port = 3306
bind-address = 0.0.0.0
default_authentication_plugin = mysql_native_password
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
skip-name-resolve
explicit_defaults_for_timestamp = true

# 连接相关
max_connections = 150
wait_timeout = 1800
interactive_timeout = 1800
max_connect_errors = 1000

# 缓存与内存控制（适配2GB内存）
table_open_cache = 200
thread_cache_size = 50

# InnoDB 引擎配置
innodb_buffer_pool_size = 512M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 4

# 性能提升建议
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 2M
read_buffer_size = 1M
join_buffer_size = 2M

# SQL 模式（更严格）
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

run container

docker run -d \
  --name mysql-prod \
  -e MYSQL_ROOT_PASSWORD=[yourStrongPassword] \
  -e MYSQL_DATABASE=[prod_db] \
  -e MYSQL_USER=[prod_user] \
  -e MYSQL_PASSWORD=[prod_pass] \
  -p 3306:3306 \
  -v $(pwd)/my.cnf:/etc/mysql/conf.d/my.cnf:ro \
  -v mysql-data:/var/lib/mysql \
  --restart always \
  mysql:8.0

docker exec -it mysql-prod mysql -uroot -p
# SELECT @@global.time_zone, @@session.time_zone;
# SELECT NOW();