MySQL Cheat Sheet

发表于 |

安装

版本选择

关于 mysql 的版本选择，可以参考 如何选择 MySQL 版本 这篇文章。我们选择社区版的 5.6 版本。

安装步骤

为了方便用户安装，MySQl 官方提供了单独的 repository，对于Linux 发行版，有专门的 yum 源和 apt 源。将源添加好后，就可以很方便的通过 linux 的 yum 或者 apt 工具进行安装。这里我们以 yum 为例来讲述 MySQL 社区版本的安装过程。

注意下载的文件名虽然统一叫做 mysql80-community-release-<platform-and-version>.noarch.rpm，但是里面包含了 8.0，5.7，5.6，5.5 等各个版本以及工具。

  • 本地安装下载的源，我们以 centos7为例，下载的文件为 mysql80-community-release-el7-1.noarch.rpm

    1
    2
    $ sudo rpm -Uvh mysql80-community-release-el7-1.noarch.rpm
    $ rpm -qa | grep mysql

  • 查看启用的版本，默认是启用 8.0 版本

1
2
3
4
$ yum repolist enabled | grep mysql
mysql-connectors-community/x86_64 MySQL Connectors Community                  65
mysql-tools-community/x86_64      MySQL Tools Community                       69
mysql80-community/x86_64          MySQL 8.0 Community Server                 412
  • 修改启用版本为 5.6
1
2
$ sudo yum-config-manager --disable mysql80-community
$ sudo yum-config-manager --enable mysql56-community
  • 安装
1
2
$ sudo yum install mysql-community-server
$ mysql --version

如果安装时下载有问题，可以考虑修改 /etc/yum.conf 里的 proxy 代理配置为你的 http 代理地址 http://your_proxy_ip:port

配置

默认安装路径是 /var/lib/mysql, 可以自定义数据文件路径。一般来说云主机的 data 盘较大，可以将数据库的数据存放于此

1
2
3
4
5
6
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock

[client]
socket=/data/mysql/mysql.sock   # 供 mysql client 连接用

这里自定义的/data/mysql 路径需要设置为 mysql 用户拥有

1
$ sudo chown -R mysql:mysql /data/mysql

开启慢查询

1
2
3
4
[mysqld]
slow_query_log=1
long_query_time=1 # 1s
slow_query_log_file=/data/mysql/mysqld-slow.log

开启 binlog

1
2
[mysqld]
log-bin=mysql-bin

设置字符集(character)及校对规则(collation，字符串比较规则)，字符集默认是 latin1，我们设置为 utf8

1
2
3
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

配置完成后，启动 mysqld 服务器进程

1
$ sudo service mysqld start

安全性控制

运行 mysql_secure_installation 命令，来对新安装的 mysql 进行安全性设置，这包括 root 密码设置、限制 root 只能本机登录、删除 test 库、删除匿名用户等。对于生产环境，强烈建议设置一下。

注意 5.7 及以上版本已经不需要运行该命令

性能测试

使用 sysbench 工具来对 MySQL 进行测试(这里我们只测试 select primary key)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ sysbench /usr/share/sysbench/oltp_read_only.lua help
sysbench 1.0.9 (using system LuaJIT 2.0.4)

oltp_read_only.lua options:
  --distinct_ranges=N           Number of SELECT DISTINCT queries per transaction [1]
  --sum_ranges=N                Number of SELECT SUM() queries per transaction [1]
  --skip_trx[=on|off]           Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
  --secondary[=on|off]          Use a secondary index in place of the PRIMARY KEY [off]
  --create_secondary[=on|off]   Create a secondary index in addition to the PRIMARY KEY [on]
  --index_updates=N             Number of UPDATE index queries per transaction [1]
  --range_size=N                Range size for range SELECT queries [100]
  --auto_inc[=on|off]           Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
  --delete_inserts=N            Number of DELETE/INSERT combination per transaction [1]
  --tables=N                    Number of tables [1]
  --mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
  --non_index_updates=N         Number of UPDATE non-index queries per transaction [1]
  --table_size=N                Number of rows per table [10000]
  --pgsql_variant=STRING        Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
  --simple_ranges=N             Number of simple range SELECT queries per transaction [1]
  --order_ranges=N              Number of SELECT ORDER BY queries per transaction [1]
  --range_selects[=on|off]      Enable/disable all range SELECT queries [on]
  --point_selects=N             Number of point SELECT queries per transaction [10]

准备数据

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
$ sysbench /usr/share/sysbench/oltp_read_only.lua --threads=4  --mysql-user=root --mysql-password=xxxxx --mysql-db=db_test --db-driver=mysql --tables=10 --table_size=1000000 prepare

sysbench 1.0.9 (using system LuaJIT 2.0.4)


Creating table 'sbtest3'...
Creating table 'sbtest4'...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest3'
Inserting 1000000 records into 'sbtest2'
Inserting 1000000 records into 'sbtest4'
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest8'...
Inserting 1000000 records into 'sbtest8'
Creating table 'sbtest6'...
Inserting 1000000 records into 'sbtest6'
Creating table 'sbtest5'...
Inserting 1000000 records into 'sbtest5'
Creating table 'sbtest7'...
Inserting 1000000 records into 'sbtest7'
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest10'...
Inserting 1000000 records into 'sbtest10'
Creating table 'sbtest9'...
Inserting 1000000 records into 'sbtest9'
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest9'...

数据准备好之后，开始运行

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
$ sysbench /usr/share/sysbench/oltp_read_only.lua --threads=16 --events=0 --time=300 --mysql-db=db_test --mysql-user=root --mysql-password=xxxxx --db-driver=mysql --table_size=1000000 --range_selects=off --db-ps-mode=disable --report-interval=1 --histogram run

...
[ 298s ] thds: 16 tps: 4907.59 qps: 58910.03 (r/w/o: 49095.86/0.00/9814.17) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 16 tps: 4915.22 qps: 58992.64 (r/w/o: 49161.20/0.00/9831.44) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 16 tps: 4909.87 qps: 58891.44 (r/w/o: 49070.70/0.00/9820.74) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00

SQL statistics:
    queries performed:
        read:                            14678040
        write:                           0
        other:                           2935608
        total:                           17613648
    transactions:                        1467804 (4892.54 per sec.)
    queries:                             17613648 (58710.46 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0070s
    total number of events:              1467804

Latency (ms):
         min:                                  0.80
         avg:                                  3.27
         max:                                114.77
         95th percentile:                      3.96
         sum:                            4797566.33

Threads fairness:
    events (avg/stddev):           91737.7500/481.98
    execution time (avg/stddev):   299.8479/0.00

登录

通过 mysql_secure_installation 脚本设置好 root 密码后，就可以登录了

1
2
$ mysql -h [host] -u [user] -p
Enter password: ********

登录并使用某一数据库(例子中密码前面不能有空格)

1
$ mysql -h [host] -u [user] -p[passwd] [DB_NAME]

权限

创建用户

1
mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

删除用户

1
mysql> DROP USER 'user'@'host';

赋权限

1
2
3
mysql> GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT ON `db\_oss\_%`.* TO 'query'@'172.31.%' IDENTIFIED BY '123';
mysql> FLUSH PRIVILEGES;

更新密码

1
mysql> SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');

常用命令

查看当前正在 use 的库

1
2
3
4
5
6
7
8
mysql> use mysql;
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

查看建表语句

1
mysql> show create table t_xxx;

查看索引

1
mysql> show index from table t_xxx;

查看字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

查看连接

1
mysql> show processlist;

设置连接编码为 utf8

1
mysql> SET NAMES 'utf8';

修改表结构

1
2
mysql> ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);
mysql> ALTER TABLE [table] DROP COLUMN [column];

插入一条记录

1
mysql> INSERT INTO [table] ([column], [column]) VALUES ('[value]', '[value]');

解释执行

1
mysql> EXPLAIN SELECT * FROM [table] WHERE [column] LIKE '%[value]%';

更新数据

1
mysql> UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];

为一列或者多列添加命名索引

1
2
mysql> ALTER TABLE table ADD INDEX [index_name](column, ...);
mysql> CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table (column,...);

删除索引

1
mysql> DROP INDEX index_name;

使用 group by 和 having 来获取特定组数据

1
mysql> SELECT * FROM table GROUP BY column_1 HAVING condition;

设置所有 row 的某一列

1
mysql> UPDATE table SET column_1 = value_1, ...;

备份和恢复

物理备份

物理备份是拷贝数据库的文件，比较快，适用于数据迁移等场景。

逻辑备份

逻辑备份可以无视版本和架构对数据进行备份，比如进行 mysql 升级就可以使用逻辑备份来备份数据。逻辑备份通常结合全量备份和增量备份。

在 mysql 服务器本地，使用 mysqldump 进行全量备份

1
$ mysqldump --single-transaction --all-databases --flush-logs -u [username] -p > db_backup.sql

如果要备份特定的库和表，可以这样

1
$ mysqldump --single-transaction --flush-logs -u [username] -p [db_name] <table_name> > db_backup.sql

如果不需要导出建库，使用–no-create–db选项，不需要建表语句，使用 –no-create-info 选项；导出特定条件的行，使用 –where 选项。例如

1
$ mysqldump -h 10.137.213.82 -udbuser -ppassword --no-create-info db_hgame_1020 t_user --where="accid=776085" > /tmp/776085.sql

导入备份的 sql 语句

1
$ mysql -hhostname -uusername -ppassword databasename < backupfile.sql

或者使用 mysql 的 source 命令

1
mysql> source backup.sql;

如果备份的数据比较大，可以压缩一下

1
2
$ mysqldump -u [username] -p --default-character-set=utf8 [db_name] | gzip > backup.sql.gz
$ gunzip -c backup.sql.gz | mysql -u [username] -p [db_name] 1> load.log 2>err.log

如果想将一台服务器的数据直接导入另外一台机器，可以

1
$ mysqldump -u root -p database_name | mysql -h other-host database_name

mysqldump 需要 SELECT 权限，对于没有指定 –single-transaction 选项的还需要 LOCK TABLES 权限。

对于增量备份，可以使用 binlog。

查看所有的 binlog

1
2
3
4
5
6
7
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)

查看当前正在写的 binlog

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

导出特定时间的 binlog 以便根据 pos 来进行具体恢复(binlog 是二进制格式，利用 mysqlbinlog 工具导出成 sql)

1
$ mysqlbinlog --start-datetime="2018-05-20 9:58:00" --stop-datetime="2018-05-20 10:01:00" /data/mysql/mysql-bin.000001 > /tmp/mysql_restore.sql

通过 mysql_restore.sql 我们发现只需要恢复到 log_pos 为368312 的位置，则执行

1
$ mysqlbinlog --stop-position=368312 /data/mysql/mysql-bin.000001 | mysql -uroot -p

卸载

先查询安装了哪些 mysql 的文件，例如

1
2
3
4
5
6
7
$ rpm -qa | grep mysql
mysql-community-libs-5.7.21-1.el7.x86_64
mysql-community-devel-5.7.21-1.el7.x86_64
mysql-community-client-5.7.21-1.el7.x86_64
mysql-community-server-5.7.21-1.el7.x86_64
mysql-community-common-5.7.21-1.el7.x86_64
mysql-community-libs-compat-5.7.21-1.el7.x86_64

然后使用 yum remove 命令卸载

1
2
3
$ sudo yum remove mysql
$ sudo yum remove mysql-community-common
$ sudo yum -y remove mysql-libs

其他

如果已知旧密码，需要修改 root 密码为新密码，可以这样

1
2
3
4
$ which mysqladmin
/usr/bin/mysqladmin
$ /usr/bin/mysqladmin -u root -p password 'new-password'
Enter password:

如果忘记密码，在 my.cnf [mysqld] 部分最后添加 skip-grant-tables 选项

1
2
[mysqld]
skip-grant-tables

重启 mysqld

1
$ sudo service mysqld restart

使用 root 无需密码登录

1
$ mysql -uroot

更新密码

1
2
3
mysql> use mysql;
mysql> UPDATE user SET password=password('new-password') WHERE user = 'root' ;
mysql> flush privileges ;

退出，删除 skip-grant-tables 选项并重启 mysqld 即可

参考资料

如何选择 MySQL 版本

How to Benchmark Performance of MySQL & MariaDB using SysBench