MySQL 笔记

最新推荐文章于 2024-07-30 19:26:47 发布

weixin_34248023

最新推荐文章于 2024-07-30 19:26:47 发布

阅读量65

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/colben/blog/1610056

版权

为什么80%的码农都做不了架构师？>>>

MySQL 5.7 配置文件 my.cnf

[mysqld]
datadir                        = /db/mysql
socket                         = /var/lib/mysql/mysql.sock
symbolic-links                 = 0
log_timestamps                 = SYSTEM
slow_query_log                 = 1
slow_query_log_file            = /var/log/mysqld/slow.log
long_query_time                = 8
#log_queries_not_using_indexes  = 1
log_error                      = /var/log/mysqld/error.log
pid-file                       = /var/run/mysqld/mysqld.pid
max_connections                = 1000
max_connect_errors             = 1000
max_user_connections           = 600
interactive_timeout            = 3600
wait_timeout                   = 3600
skip-name-resolve              = 1
character-set-server           = utf8mb4
sql_mode                       = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
bind-address                   = 0.0.0.0
table_open_cache               = 2048
default_storage_engine         = innodb
innodb_autoinc_lock_mode       = 2
innodb_flush_log_at_trx_commit = 0
# 建议物理内存一半
innodb_buffer_pool_size        = 8G
innodb_buffer_pool_instances   = 8
max_allowed_packet             = 512M
query_cache_size               = 0
query_cache_type               = 0
# 建议点分 ip 的最后一个数字
server_id                      = 123
# bin log
#binlog_format                  = ROW
#log-bin                        = /var/lib/mysql/mysql-bin
#expire_logs_days               = 3
# relay log
#read-only                      = 1
#replicate-wild-do-table        = db1.%
#relay_log                      = /var/lib/mysql/mysql-relay-bin
#slave_parallel_type            = logical_clock

复制表结构

create table db1.t1 like db2.t2;
create table db1.t1 select db2.t2 where 1=2;

复制表结构及其数据

create table db1.t1 select db2.t2 [where ...]

复制表数据

insert into db2.t2(column1, column2 ...) select column1, column2 ... from db1.t1 [where ...]

通过复制表文件来复制表数据

在db2中创建同结构表
```
create table db2.t1 like db1.t1;
```
丢弃表空间
```
alter table db2.t1 discard tablespace;
```

复制 t1 的表数据文件

#关闭数据库
systemctl stop mysqld
cd /var/lib/mysql
scp db1/t1.idb db2/t1.idb
chown mysql.mysql db2/t1.idb
#启动数据库
systemctl start mysqld

导入表空间
```
alter table db2.t1 import tablespace;
```

设置一个表的空列自增

-- 删除可能存在的主键
alter table 表名 drop primary key;
alter table 表名 modify 列名 auto_increment primary key;

查看数据库中每个表的全部列名

select table_name, column_name from information_schema.columns where table_schema = '数据库名';

查看数据库中每个表的行数

select table_name, table_rows from information_schema.tables where table_schema = '数据库名';

查看数据库中每个表的索引

select table_name, column_name, index_name from INFORMATION_SCHEMA.STATISTICS where table_schema = '数据库名';

表的部分列数据到另一个表

update db2.t2(column1, column2 ...) = (select column1, column2 from db1.t1 where db1.t1.id = db2.t2.id);

把语句执行结果写到文件

mysql -uroot -p -hsever_ip -Ddb_name -Ne "select ... from table_name;" > file_name

表分区

查看表的分区情况

select table_schema, table_name, partition_name, table_rows from information_schema.partitions where table_name = 'table_name';

建表时指定

create table table_name() partition by range columns(column_name) (partition part_name values less than(some_value));

修改成分区表

alter table table_name partition by range(column_name) (partition part_name values less than(som_value));

增加分区

alter table table_name add partition (partition part_name values less than(som_value));

删除分区

alter table table_name drop partition part_name;

合并/拆分分区

alter table table_name reorganize part_old_1, part_old_2, part_old_3 into (partition part_new_1 values less than(value_1), partition part_new_2 values less than(value_2));

重建分区，整理分区碎片

alter table table_name rebuild partition part_name_1, part_name_2;

优化分区，回收空间，整理碎片

alter table table_name optimize partition part_name_1, part_name_2;

分析分区，读取并保存分区的健分布

alter table table_name analyze partition part_name_1, part_name_2;

修复分区

alter table table_name repair partition part_name_1, part_name_2;

检查分区

alter table table_name check partition part_name_1, part_name_2;

MySQL 5.7 从库多线程同步

stop slave;
set global slave_parallel_type='logical_clock';
set global slave_parallel_workers=4;
start slave;
show processlist;

MySQL 5.7 提示密码复杂度不够

set global validate_password_policy=0;

MySQL 5.7 从库复制失败跳过指定数量的事务

stop slave;
-- 跳过一个事务
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
-- 修改 my.cnf
slave-skip-errors=1062,1053,1146,1032 #跳过指定error no类型的错误
slave-skip-errors=all #跳过所有错误

MySQL 5.7 查看全部任务

-- 分号换成 \G 显示完整 sql
show processlist;
show full processlist;
SELECT command FROM information_schema.processlist;

MySQL 5.7 ssl 连接

--ssl-mode=REQUIRED

MariaDB 10.1 修改密码

UPDATE user SET password=password('newpassword') WHERE user='root';

MySQL 5.7 编码

查看
```
SHOW VARIABLES LIKE 'character_set%';
```
数据库连接参数中，characterEncoding=utf8 会被自动识别为 utf8mb4，但是 autoReconnect=true 必须指定

更改数据库编码

ALTER DATABASE db_name CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;

更改表编码

ALTER TABLE table_name CONVERT TO CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;

MySQL 5.7 升级数据库管理表结构

mysql_upgrade -u root -p

MySQL 5.7 日志配置

创建可读写日志文件

mkdir -p /var/log/mysqld
cd /var/log/mysqld
touch general.log slow.log error.log
chown -R mysql.mysql ../mysqld/

修改 my.cnf

# 日志中的时间格式
log_timestamps=SYSTEM
# 记录全部操作
general_log=1
general_log_file=/var/log/mysqld/general.log
# 记录慢查询，包括长时间查询和无索引查询
slow_query_log=1
slow_query_log_file=/var/log/mysqld/slow.log
long_query_time=10
log_queries_not_using_indexes=1
# 记录服务启停日志
log_error=/var/log/mysqld/error.log

MySQL 5.7 误删 root 后恢复 root 账户

停止 mysql 服务
```
systemctl stop mysqld
```
修改 my.cnf
```
# 添加如下一行
skip-grant-tables
```
启动 mysql 服务
```
systemctl start mysqld
```

重建 root 账户，并授权

insert into user set user='root', ssl_cipher='', x509_issuer='', x509_subject='';
update user set Host='localhost', select_priv='y', insert_priv='y', update_priv='y', Alter_priv='y', delete_priv='y', create_priv='y', drop_priv='y', reload_priv='y', shutdown_priv='y', Process_priv='y', file_priv='y', grant_priv='y', References_priv='y', index_priv='y', create_user_priv='y', show_db_priv='y', super_priv='y', create_tmp_table_priv='y', Lock_tables_priv='y', execute_priv='y', repl_slave_priv='y', repl_client_priv='y', create_view_priv='y', show_view_priv='y', create_routine_priv='y', alter_routine_priv='y', create_user_priv='y', event_priv='y', trigger_priv='y', create_tablespace_priv='y' where user='root';
flush privileges;

停止 mysql 服务
```
systemctl stop mysqld
```

修改 my.cnf

# 删除刚添加的如下一行
#skip-grant-tables

启动 mysql 服务，root 账户正常可用
```
systemctl start mysqld
```

通过EXPLAIN分析SQL的执行计划

使用
```
explain sql
```
select_type 查询类型
- SIMPLE 简单表，没有表连接或子查询
- PRIMARY 最外层的查询
- UNION union语句的后置查询
- SUBQUERY 第一个子查询
table 表/别名
type 访问类型
- ALL 全表扫描
- index 全索引扫描
- range 索引范围扫描
- ref 非唯一索引扫描
- eq_ref 唯一索引扫描
- const,system 单表最多一个匹配行
- NULL 不需要扫描表或索引
possible_keys 查询可能使用的索引
key 实际使用的索引
key_len 使用的索引字段的长度
ref 其他匹配字段
rows 扫描行的数量
filtered 满足查询条件的记录占存储引擎返回记录的比例
Extra 执行情况说明
- Using Index 全部使用索引，没有回表查询
- Using Where 有回表查询
- Using Index Condition ICP优化，直接在存储引擎完成条件过滤
- Using Flesort 依靠索引顺序达不到排序效果，需额外排序

统计 insert、delete、update 和 select 次数

show global status where Variable_name in ('com_insert', 'com_delete', 'com_update', 'com_select');

csv 文件

导出

select * from t1 into outfile '/var/lib/mysql-files/t1.csv' fields terminated by ',' enclosed by '\"' escaped by '\\\' lines terminated by '\n'

导入

load data infile '/var/lib/mysql-files/t1.csv' into table t1 fields terminated by ',' enclosed by '\"' escaped by '\\\' lines terminated by '\n'

转载于:https://my.oschina.net/colben/blog/1610056

weixin_34248023

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 笔记

为什么80%的码农都做不了架构师？>>> ...
复制链接

扫一扫