MySQL 5.7 配置文件 my.cnf

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[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
lower-case-table-names         = 1
default-time-zone              = '+08:00'
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

复制表结构

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

复制表结构及其数据

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

复制表数据

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

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

  • 在db2中创建同结构表

    1
    
    create table db2.t1 like db1.t1;
  • 丢弃表空间

    1
    
    alter table db2.t1 discard tablespace;
  • 复制 t1 的表数据文件

    1
    2
    3
    4
    5
    6
    7
    
    #关闭数据库
    systemctl stop mysqld
    cd /var/lib/mysql
    scp db1/t1.idb db2/t1.idb
    chown mysql.mysql db2/t1.idb
    #启动数据库
    systemctl start mysqld
  • 导入表空间

    1
    
    alter table db2.t1 import tablespace;

设置一个表的空列自增

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

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

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

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

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

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

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

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

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

把语句执行结果写到文件

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

表分区

  • 查看表的分区情况

    1
    2
    3
    
    select table_schema, table_name, partition_name, table_rows
        from information_schema.partitions
        where table_name = 'table_name';
  • 建表时指定

    1
    2
    3
    
    create table table_name(...)
        partition by range columns(column_name)
        (partition part_name values less than(some_value));
  • 修改成分区表

    1
    2
    3
    
    alter table table_name
        partition by range(column_name)
        (partition part_name values less than(som_value));
  • 增加分区

    1
    2
    
    alter table table_name add partition
        (partition part_name values less than(som_value));
  • 删除分区

    1
    
    alter table table_name drop partition part_name;
  • 合并/拆分分区

    1
    2
    3
    4
    
    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));
  • 重建分区,整理分区碎片

    1
    2
    
    alter table table_name
        rebuild partition part_name_1, part_name_2;
  • 优化分区,回收空间,整理碎片

    1
    2
    
    alter table table_name
        optimize partition part_name_1, part_name_2;
  • 分析分区,读取并保存分区的健分布

    1
    2
    
    alter table table_name
         analyze partition part_name_1, part_name_2;
  • 修复分区

    1
    2
    
    alter table table_name
        repair partition part_name_1, part_name_2;
  • 检查分区

    1
    2
    
    alter table table_name
        check partition part_name_1, part_name_2;

MySQL 5.7 从库多线程同步

1
2
3
4
5
stop slave;
set global slave_parallel_type='logical_clock';
set global slave_parallel_workers=4;
start slave;
show processlist;

MySQL 5.7 提示密码复杂度不够

1
set global validate_password_policy=0;

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

1
2
3
4
5
6
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 查看全部任务

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

MySQL 5.7 ssl 连接

1
--ssl-mode=REQUIRED

MariaDB 10.1 修改密码

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

MySQL 5.7 编码

  • 查看

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

  • 更改数据库编码

    1
    2
    
    ALTER DATABASE db_name
        CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;
  • 更改表编码

    1
    2
    
    ALTER TABLE table_name
        CONVERT TO CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;

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

1
mysql_upgrade -u root -p

MySQL 5.7 误删 root 后恢复 root 账户

  • 停止 mysql 服务

    1
    
    systemctl stop mysqld
  • 修改 my.cnf

    1
    2
    
    # 添加如下一行
    skip-grant-tables
  • 启动 mysql 服务

    1
    
    systemctl start mysqld
  • 重建 root 账户,并授权

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    
    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 服务

    1
    
    systemctl stop mysqld
  • 修改 my.cnf

    1
    2
    
    # 删除刚添加的如下一行
    #skip-grant-tables
  • 启动 mysql 服务,root 账户正常可用

    1
    
    systemctl start mysqld

通过EXPLAIN分析SQL的执行计划

  • 使用

    1
    
    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 次数

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

csv 文件

  • 导出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    
    # Shell 终端
    # mysql -e "select * from t1
        into outfile '/var/lib/mysql-files/t1.csv'
        fields terminated by ','
        enclosed by '\"'
        escaped by '\\\'
        lines terminated by '\n'"
    # MySQL 终端
    # MySQL> select * from t1
                 into outfile '/var/lib/mysql-files/t1.csv'
                 fields terminated by ','
                 enclosed by '\"'
                 escaped by '\\'
                 lines terminated by '\n';
  • 导入

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    
    # Shell 终端
    # mysql -e "load data infile '/var/lib/mysql-files/t1.csv'
        into table t1
        fields terminated by ','
        enclosed by '\"'
        escaped by '\\\'
        lines terminated by '\n'"
    # MySQL 终端
    # MySQL> load data infile '/var/lib/mysql-files/t1.csv'
                 into table t1
                 fields terminated by ','
                 enclosed by '\"'
                 escaped by '\\'
                 lines terminated by '\n';