MySQL Install Guide

MySQL / MariaDB 安装指引

官方源

Mac Windows
5.7.23 5.7.31
8.0.18 8.0.21
 

MySQL 下载 ：5.7，8.0，最新版本，Sample 数据库，在线文档

 

MariaDB 官方安装源生成器：Repository Generator

MySQL APT

# 安装官方 APT 源
wget -c https://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb

# 重配 APT 源关联版本
sudo dpkg-reconfigure mysql-apt-config

# 安装 MySQL
sudo apt install mysql-community-server

MariaDB Ubuntu 源

# 官方安装源密钥及依赖库
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo apt-get install software-properties-common
# MariaDB 10.4 on Ubuntu 16.04
sudo add-apt-repository 'deb [arch=amd64,arm64,i386,ppc64el] http://downloads.mariadb.com/MariaDB/mariadb-10.4/repo/ubuntu xenial main'
# MariaDB 10.4 on Ubuntu 18.04
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://downloads.mariadb.com/MariaDB/mariadb-10.4/repo/ubuntu bionic main'

MariaDB YUM 源

国内使用官方生成的 yum 源安装 MariaDB 速度过慢。以下配置引用高速官方源为 CentOS 7 安装 MariaDB 10.2，其他版本可以参考这里调整 URL。

[mariadb]
name = MariaDB
baseurl = https://downloads.mariadb.com/MariaDB/mariadb-10.2/yum/centos7-amd64/
gpgkey = https://downloads.mariadb.com/MariaDB/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

阿里云服务器可以参考以下配置：

# MariaDB 10.11 CentOS repository list - created 2023-03-20 03:49 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
# rpm.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# baseurl = https://rpm.mariadb.org/10.11/centos/$releasever/$basearch
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.11/centos/$releasever/$basearch
# gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1


# MariaDB 10.6 CentOS repository list - created 2023-03-20 03:49 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
# rpm.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# baseurl = https://rpm.mariadb.org/10.6/centos/$releasever/$basearch
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.6/centos/$releasever/$basearch
# gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1


[mariadb]
name = MariaDB
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.2/centos7-amd64
gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck = 1

macOS

MySQL 5.7 开始官方支持 macOS。

macOS 兼容最后版本
Sierra (10.12) 5.7.23 / 8.0.12
High Sierra (10.13) 5.7.x / 8.0.18

Brew 安装后指示： 

We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

mysql@5.7 is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have mysql@5.7 first in your PATH, run:
  echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.zshrc

For compilers to find mysql@5.7 you may need to set:
  export LDFLAGS="-L/usr/local/opt/mysql@5.7/lib"
  export CPPFLAGS="-I/usr/local/opt/mysql@5.7/include"

For pkg-config to find mysql@5.7 you may need to set:
  export PKG_CONFIG_PATH="/usr/local/opt/mysql@5.7/lib/pkgconfig"


To restart mysql@5.7 after an upgrade:
  brew services restart mysql@5.7
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mysql@5.7/bin/mysqld_safe --datadir=/usr/local/var/mysql

Windows

解压数据库到安装目录，比如C:\MySQL，随后进行初始化，参考以下相应版本： 

MySQL 5.7+

# 命令行进入数据库安装目录
# 初始化数据文件
mysqld --initialize-insecure --console

# 启动数据库
mysqld --console

# 设置 root 密码
mysql_secure_installation

安装目录下新建 my.ini 文件，存入以下初始化内容：

[mysqld]
explicit_defaults_for_timestamp = 1
max_allowed_packet = 32M

更新 root 密码

MySQL 5.7+ 安装时会给 root 账户设置一个过期密码，登录客户端后，需要用以下命令更新密码：

ALTER USER root@localhost IDENTIFIED BY "root";
# 设置 root 密码
mariadb-secure-installation

性能确认

time mysqlslap -av -uroot -p -c 20 --number-of-queries=10000

文件/文件夹

配置文件位置可用 mysqld --verbose --help 进行巡查。

macOS

位置 作用
/usr/local/mysql/etc/my.cnf 官方 MySQL 5.7+ Client 及 Server 配置文件
/usr/local/mysql/support-files/mysql.server Server 起停脚本

Windows

位置 作用
C:\MySQL\my.ini 客户端/服务器配置文件

Docker

IntelJ Plugin

.run/MySQL-8.0.run.xml

<component name="ProjectRunConfigurationManager">
    <configuration default="false"
        name="MySQL-8.0"
        type="docker-deploy"
        factoryName="docker-image"
        server-name="Docker">
        <deployment type="docker-image">
            <settings>
                <option name="imageTag"
                    value="mysql:8.0" />
                <option name="containerName"
                    value="mysql-8.0" />
                <option name="envVars">
                    <list>
                        <DockerEnvVarImpl>
                            <option name="name"
                                value="MYSQL_ROOT_PASSWORD" />
                            <option name="value"
                                value="root" />
                        </DockerEnvVarImpl>
                        <DockerEnvVarImpl>
                            <option name="name"
                                value="MYSQL_DATABASE" />
                            <option name="value"
                                value="default" />
                        </DockerEnvVarImpl>
                        <DockerEnvVarImpl>
                            <option name="name"
                                value="MYSQL_ROOT_HOST" />
                            <option name="value"
                                value="%" />
                        </DockerEnvVarImpl>
                    </list>
                </option>
                <option name="portBindings">
                    <list>
                        <DockerPortBindingImpl>
                            <option name="containerPort"
                                value="3306" />
                            <option name="hostIp"
                                value="0.0.0.0" />
                            <option name="hostPort"
                                value="3806" />
                        </DockerPortBindingImpl>
                    </list>
                </option>
                <option name="commandLineOptions"
                    value="--restart=always" />
                <option name="volumeBindings">
                    <list>
                        <DockerVolumeBindingImpl>
                            <option name="containerPath"
                                value="/etc/mysql" />
                            <option name="hostPath"
                                value="C:\W\mysql\8.0\etc" />
                            <option name="readOnly"
                                value="true" />
                        </DockerVolumeBindingImpl>
                        <DockerVolumeBindingImpl>
                            <option name="containerPath"
                                value="/etc/mysql/my.cnf" />
                            <option name="hostPath"
                                value="C:\W\mysql\8.0\etc\my.cnf" />
                            <option name="readOnly"
                                value="true" />
                        </DockerVolumeBindingImpl>
                        <DockerVolumeBindingImpl>
                            <option name="containerPath"
                                value="/var/lib/mysql" />
                            <option name="hostPath"
                                value="C:\W\mysql\8.0\data" />
                        </DockerVolumeBindingImpl>
                        <DockerVolumeBindingImpl>
                            <option name="containerPath"
                                value="/var/log/mysql" />
                            <option name="hostPath"
                                value="C:\W\mysql\8.0\logs" />
                        </DockerVolumeBindingImpl>
                    </list>
                </option>
            </settings>
        </deployment>
        <method v="2" />
    </configuration>
</component>

升级

升级前需关闭服务器快关功能，以清空 redo 日志。

SET GLOBAL innodb_fast_shutdown = 0;

常用配置

相关路径

/Library/Application Support/appsolute/MAMP PRO/db MAMP MySQL 数据库目录
/Applications/MAMP/Library/scripts/mysql_install_db MAMP MySQL 数据库初始化脚本

开启 utf8mb4 字符集支持

MySQL 5.7 之前的版本需要手动开启 utf8mb4 支持，在 MySQL Server 的配置文件中加入以下配置即可：

[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

Drupal 需添加 charset 和 collation 设置确保以 utf8mb4 字符集存取数据：

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'databasename',
  'username' => 'username',
  'password' => 'password',
  'host' => 'localhost',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_general_ci',
);
 

Drupal 7 开启 utf8mb4 支持

禁用 hostname 反查

[mysqld]
skip-name-resolve

x 插件

# 激活 x 插件
mysqlsh -u root -h localhost -p --mysql --dba enableXProtocol

监控

 

Prometheus

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'exporter'@'%';
GRANT SELECT ON performance_schema.* TO 'exporter'@'%';

花絮

brew(Ubuntu)

默认无 Linux 下启动配置。

$ brew install mysql             
Updating Homebrew...
==> Installing dependencies for mysql: patchelf, openssl@1.1, zlib, gmp, isl, mpfr, libmpc, gcc@6, protobuf, ncurses, libedit and gcc@7
==> Installing mysql dependency: patchelf
==> Downloading https://linuxbrew.bintray.com/bottles/patchelf-0.10.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/98/988b224305ff3fecadf9bbf5fbe1b18061077be6c896935b2cef29298526f5b8?__gda__=exp=1579501174~hmac=99d111c3c2b6e2c7eb98ef5b3aa606f925a6ee8d9ba06b3ccb8
######################################################################## 100.0%
==> Pouring patchelf-0.10.x86_64_linux.bottle.tar.gz
?  /home/linuxbrew/.linuxbrew/Cellar/patchelf/0.10: 8 files, 921.5KB
==> Installing mysql dependency: openssl@1.1
==> Downloading https://linuxbrew.bintray.com/bottles/openssl@1.1-1.1.1d_1.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/01/010a4e5257eb685ac700f495208bed1e05155663f8083ab6ab2e4a91ef69c1cd?__gda__=exp=1579501187~hmac=6bfbc57a2623ab9518984e820365cf6ede14865b9b3da6b3b84
######################################################################## 100.0%
==> Pouring openssl@1.1-1.1.1d_1.x86_64_linux.bottle.tar.gz
==> Downloading https://curl.haxx.se/ca/cacert-2019-10-16.pem
######################################################################## 100.0%
==> Caveats
A CA file has been bootstrapped using certificates from the system
keychain. To add additional certificates, place .pem files in
  /home/linuxbrew/.linuxbrew/etc/openssl@1.1/certs

and run
  /home/linuxbrew/.linuxbrew/opt/openssl@1.1/bin/c_rehash

openssl@1.1 is keg-only, which means it was not symlinked into /home/linuxbrew/.linuxbrew,
because this is an alternate version of another formula.

If you need to have openssl@1.1 first in your PATH run:
  echo 'export PATH="/home/linuxbrew/.linuxbrew/opt/openssl@1.1/bin:$PATH"' >> ~/.zshrc

For compilers to find openssl@1.1 you may need to set:
  export LDFLAGS="-L/home/linuxbrew/.linuxbrew/opt/openssl@1.1/lib"
  export CPPFLAGS="-I/home/linuxbrew/.linuxbrew/opt/openssl@1.1/include"

==> Summary
?  /home/linuxbrew/.linuxbrew/Cellar/openssl@1.1/1.1.1d_1: 7,985 files, 21.2MB
==> Installing mysql dependency: zlib
==> Downloading https://linuxbrew.bintray.com/bottles/zlib-1.2.11.x86_64_linux.bottle.tar.gz
######################################################################## 100.0%
==> Pouring zlib-1.2.11.x86_64_linux.bottle.tar.gz
?  /home/linuxbrew/.linuxbrew/Cellar/zlib/1.2.11: 12 files, 424KB
==> Installing mysql dependency: gmp
==> Downloading https://linuxbrew.bintray.com/bottles/gmp-6.1.2_3.x86_64_linux.bottle.1.tar.gz
==> Downloading from https://akamai.bintray.com/a2/a21f68e8df094a2bccca845911eae1cf52bdefd19ca53cfd04aacf2a8112ece9?__gda__=exp=1579501223~hmac=450b62f0aba942c3e8a7777de9d8d385ea1736ee4b36aa76994
######################################################################## 100.0%
==> Pouring gmp-6.1.2_3.x86_64_linux.bottle.1.tar.gz
?  /home/linuxbrew/.linuxbrew/Cellar/gmp/6.1.2_3: 20 files, 3.8MB
==> Installing mysql dependency: isl
==> Downloading https://linuxbrew.bintray.com/bottles/isl-0.22.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/07/07df0023ff3c6b9f545258564ac58066b4bc9f5370c83d66ba3e2c95f5149fd5?__gda__=exp=1579501229~hmac=34ae80236c57be301a12f1853aa5a30f3790e2b9fc02176222f
######################################################################## 100.0%
==> Pouring isl-0.22.x86_64_linux.bottle.tar.gz
?  /home/linuxbrew/.linuxbrew/Cellar/isl/0.22: 74 files, 6.4MB
==> Installing mysql dependency: mpfr
==> Downloading https://linuxbrew.bintray.com/bottles/mpfr-4.0.2.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/cf/cffaa9976a516130ac5b979eae7bdd62180ed8924bb52bf0b30935174b1cbab0?__gda__=exp=1579501238~hmac=9d14b6209c1c1bb5da1688ce7eb3972fb9bde0cab971506ec05
######################################################################## 100.0%
==> Pouring mpfr-4.0.2.x86_64_linux.bottle.tar.gz
?  /home/linuxbrew/.linuxbrew/Cellar/mpfr/4.0.2: 29 files, 5.2MB
==> Installing mysql dependency: libmpc
==> Downloading https://linuxbrew.bintray.com/bottles/libmpc-1.1.0.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/b3/b31648a86228a042aaa8e7d58faba7859059910a427f9ef4211b06bd9152f8c2?__gda__=exp=1579501248~hmac=0b7e1fa7c1e2c0b8249f35f42007f6a2ee889a59d0b00331a30
######################################################################## 100.0%
==> Pouring libmpc-1.1.0.x86_64_linux.bottle.tar.gz
?  /home/linuxbrew/.linuxbrew/Cellar/libmpc/1.1.0: 13 files, 1.5MB
==> Installing mysql dependency: gcc@6
==> Downloading https://linuxbrew.bintray.com/bottles/gcc@6-6.5.0_4.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/d6/d67445571d72bd8696dfb4a019b3204be46e6705a7d8ed1113ed02887303c66a?__gda__=exp=1579501255~hmac=182d26c506ebca33abdb0d287df277c35696e833a97aeb6255a
######################################################################## 100.0%
==> Pouring gcc@6-6.5.0_4.x86_64_linux.bottle.tar.gz
==> Creating the GCC specs file: /home/linuxbrew/.linuxbrew/Cellar/gcc@6/6.5.0_4/lib/gcc/6/gcc/x86_64-pc-linux-gnu/6.5.0/specs
?  /home/linuxbrew/.linuxbrew/Cellar/gcc@6/6.5.0_4: 1,396 files, 190.7MB
==> Installing mysql dependency: protobuf
==> Downloading https://linuxbrew.bintray.com/bottles/protobuf-3.11.2.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/a8/a84b223cfd1fa2adc1336afb7a11a8bc22f42163766ae55905e04bdb095c81a8?__gda__=exp=1579501368~hmac=944693ce5d279732c6dd7137db8aadf170c688145e274f25a9c
######################################################################## 100.0%
==> Pouring protobuf-3.11.2.x86_64_linux.bottle.tar.gz
Warning: protobuf dependency gmp was built with a different C++ standard
library (libstdc++ from gcc-5). This may cause problems at runtime.
?  /home/linuxbrew/.linuxbrew/Cellar/protobuf/3.11.2: 272 files, 30.2MB
==> Installing mysql dependency: ncurses
==> Downloading https://linuxbrew.bintray.com/bottles/ncurses-6.1.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/5e/5eb929e69e3a2b71c2161551825fd16146efc164ab7dbea25d2a5b73b51cd1cb?__gda__=exp=1579501384~hmac=ae5a856d540dc0fd363859d3bf740f845e69499c11ce3e87f11
######################################################################## 100.0%
==> Pouring ncurses-6.1.x86_64_linux.bottle.tar.gz
?  /home/linuxbrew/.linuxbrew/Cellar/ncurses/6.1: 3,856 files, 15.4MB
==> Installing mysql dependency: libedit
==> Downloading https://linuxbrew.bintray.com/bottles/libedit-20191231-3.1.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/e9/e95fee11c5e2e861388bfe1ca575a9d845610cd41aaac23244ce841f02bf6fff?__gda__=exp=1579501399~hmac=9330c12a6988cd9b775d12f04094d3acc61a5eaec49e2f4ca58
######################################################################## 100.0%
==> Pouring libedit-20191231-3.1.x86_64_linux.bottle.tar.gz
?  /home/linuxbrew/.linuxbrew/Cellar/libedit/20191231-3.1: 56 files, 774.9KB
==> Installing mysql dependency: gcc@7
==> Downloading https://linuxbrew.bintray.com/bottles/gcc@7-7.5.0_1.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/3f/3fd23c5d356570f2cc08bfc106ca050c3daa668e497eca794d3b93a38a440571?__gda__=exp=1579501406~hmac=c2f813b93b158b4200e51653dde575d52f7fa35909220d51e0f
######################################################################## 100.0%
==> Pouring gcc@7-7.5.0_1.x86_64_linux.bottle.tar.gz
==> Creating the GCC specs file: /home/linuxbrew/.linuxbrew/Cellar/gcc@7/7.5.0_1/lib/gcc/7/gcc/x86_64-pc-linux-gnu/7.5.0/specs
?  /home/linuxbrew/.linuxbrew/Cellar/gcc@7/7.5.0_1: 1,434 files, 200.3MB
==> Installing mysql
==> Downloading https://linuxbrew.bintray.com/bottles/mysql-8.0.19.x86_64_linux.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/b3/b3bf85d99a4600e1111f2c107b583253c5ae654c61290fd7bf106947599ddba0?__gda__=exp=1579501517~hmac=4ef105484d041153b523458e43a7e2fca05499717e40a3b930e
######################################################################## 100.0%
==> Pouring mysql-8.0.19.x86_64_linux.bottle.tar.gz
Warning: mysql dependency gmp was built with a different C++ standard
library (libstdc++ from gcc-5). This may cause problems at runtime.
==> /home/linuxbrew/.linuxbrew/Cellar/mysql/8.0.19/bin/mysqld --initialize-insecure --user=ma3310 --basedir=/home/linuxbrew/.linuxbrew/Cellar/mysql/8.0.19 --datadir=/home/linuxbrew/.linuxbrew/var
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

Warning: mysql provides a launchd plist which can only be used on macOS!
You can manually execute the service instead with:
  mysql.server start
==> Summary
?  /home/linuxbrew/.linuxbrew/Cellar/mysql/8.0.19: 289 files, 241MB
==> Caveats
==> openssl@1.1
A CA file has been bootstrapped using certificates from the system
keychain. To add additional certificates, place .pem files in
  /home/linuxbrew/.linuxbrew/etc/openssl@1.1/certs

and run
  /home/linuxbrew/.linuxbrew/opt/openssl@1.1/bin/c_rehash

openssl@1.1 is keg-only, which means it was not symlinked into /home/linuxbrew/.linuxbrew,
because this is an alternate version of another formula.

If you need to have openssl@1.1 first in your PATH run:
  echo 'export PATH="/home/linuxbrew/.linuxbrew/opt/openssl@1.1/bin:$PATH"' >> ~/.zshrc

For compilers to find openssl@1.1 you may need to set:
  export LDFLAGS="-L/home/linuxbrew/.linuxbrew/opt/openssl@1.1/lib"
  export CPPFLAGS="-I/home/linuxbrew/.linuxbrew/opt/openssl@1.1/include"

==> mysql
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

Warning: mysql provides a launchd plist which can only be used on macOS!
You can manually execute the service instead with:
  mysql.server start
Author: njun
njun's picture
Updated: 2023/08/25