程序员的MySQL手册(一): 安装，基本配置

这是《程序员的MySQL手册》第一篇。这个系列主要是把作为开发，日常工作中需要用到MySQL的方方面面作一个 总结，除去了一部分运维工作，主要包括：

• 安装，基本配置
• benchmark，监控
• 数据类型，数据库设计
• 索引类型，区分度
• 查询，explain

互联网兴起之后，MySQL随之大范围流行，随着Oracle收购MySQL之后，社区为了防止Oracle把MySQL闭源（Oracle这种事情没少做）， 因此fork之后创建了MariaDB，它的目标是提供100%兼容的MySQL，随后多个知名网站都迁移到MariaDB，我们的例子 中，也会使用MariaDB，不过对于称呼上来说，不对MySQL和MariaDB进行区分。

我们选择的机器是一个2C4G的虚拟机，操作系统为Debian，它内置了MariaDB，删除了MySQL。安装很简单：

$ sudo apt update && sudo apt install -y mariadb-server
...
Created symlink /etc/systemd/system/mysql.service → /lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /lib/systemd/system/mariadb.service.
...

由日志可以看出来，systemd的service中，mysql.service, mysqld.service, mariadb.service 其实是同一个：

$ less /lib/systemd/system/mariadb.service
...
[Install]
WantedBy=multi-user.target
Alias=mysql.service
Alias=mysqld.service
...

MySQL默认使用的是自定义的一个utf8，其实是真正utf8的一个子集，它有一个坑在于无法显示emoji表情等超出它表示 范围的字符，为了方便，我们统一设置为 utf8mb4：

• 编辑 /etc/mysql/conf.d/mysql.cnf，添加：

  [mysql]
  default-character-set = utf8mb4
  

• 编辑 /etc/mysql/mariadb.conf.d/50-client.cnf，设置：

  [client]
  default-character-set = utf8mb4
  

• 编辑 /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf，设置：

  [mysql]
  default-character-set = utf8mb4
  

$ sudo systemctl restart mariadb.service

可以创建一个数据库看默认的字符集是否是utf8mb4:

MariaDB [(none)]> create database foo;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show create database foo;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| foo      | CREATE DATABASE `foo` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.000 sec)

执行 mysql_secure_installation 增强安全设置：

$ sudo mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] 
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] 
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] 
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] 
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Disallow root login remotely 这一步，就看个人需求，如果是本地测试数据库的话，保留root登录还是 挺方便的，因此可以选no，如果是生产环境用，那么应当禁用。

这样做之后，只能切换到Linux系统的root用户之后，执行mysql(不需要登录)，但是如果是普通用户通过mysql去连接，是无法登录的。

如果想要以普通用户登录MySQL的root帐号，那么得执行：

$ sudo su
# mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

之后，就可以用Linux系统的普通用户，去连接MySQL的root帐号。

客户端和备份设置

自带的mysql命令就是客户端，常见的参数是：

• -u 指定用户名
• -p 说明要密码
• -h 指定host
• -P 指定端口
• --help 打印帮助文档

如果想要免密登录，那么就在HOME下创建一个 .my.cnf:

要执行上面所说的 ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘new_password’;

[client]
user = <用户名>
password = <密码>

注意把文件的权限设置好：chmod 400 ~/.my.cnf，就可以免密登录了：

$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 10.3.22-MariaDB-0+deb10u1 Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

设置好这个之后，就可以设置定时备份，加一个crontab：

# 每天5点，备份mysql
0 5 * * * /usr/bin/mysqldump --single-transaction --quick --lock-tables=true --all-databases | gzip -c > /data/backup/mysql/full-backup-$(date +\%F).sql.gz

当然，推荐在一个单独的用于备份的帐号下或者root下来配置备份和免密，并且设置好权限。

设置slowlog

slowlog是MySQL里定位慢查询的一个有力工具，为了开启slowlog，要做如下配置，编辑 /etc/mysql/mariadb.conf.d/50-server.cnf， 在 [mariadb] 下加入：

slow_query_log
long_query_time=1.0

注意要touch这个文件并且确保权限正确：sudo touch /var/log/mysql-slow.log && sudo chown mysql:mysql /var/log/mysql-slow.log

然后重启MySQL。

还有一种方式就是设置一个全局变量：

mysql> SET GLOBAL slow_query_log=1;
mysql> SET GLOBAL long_query_time=1.0;

上述的 long_query_time 都是指，超过这个执行时间(单位是秒)的就记录到日志。如果再有慢日志，就可以在文件里看到SQL了。

这一篇我们总结了很多MySQL运维上的知识，这基本上也是开发所需要了解的运维知识，其余的例如高可用、replica则更多的是真正的 运维同事的工作职责，有兴趣的话，可以去了解一下，不过我们就不做概述啦。最后，常见的MySQL操作可以看看 这篇

系列目录：

---

---

Git HTTPS 如何保存密码

短链系统的实现

程序员修炼之道 阅读笔记

Python开发实践经验

Golang实现平滑重启(优雅重启)

traefik 教程

Nginx作为TCP/UDP的负载均衡

Web开发系列(十)：事务和锁

Web开发系列(十一）：数据库扩展

Web开发系列(九)：消息队列，异步任务

Web开发简介系列

Web开发系列(六)：关系型数据库，ORM

Web开发系列(七)：缓存，CDN

Web开发系列(八)：单点故障，负载均衡

Nginx 请求匹配规则

---