
MySQL / MariaDB 安装指引
官方源
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。
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
Windows
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;
常用配置
相关路径
开启 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',
);
禁用 hostname 反查
[mysqld]
skip-name-resolve
x 插件
# 激活 x 插件
mysqlsh -u root -h localhost -p --mysql --dba enableXProtocol
监控
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