安装
Windows
mysql 5.7.40
创建my.ini
[mysql]
default_character_set=utf8mb4
[mysqld]
port=3306
basedir=D:/usr/local/mysql/
datadir=D:/usr/local/mysql/data
max_connections=200
character_set_server=utf8mb4
default_storage_engine=INNODB
lower_case_table_names=1
default_time_zone='+8:00'
初始化数据库
D:\>cd D:\usr\local\mysql\bin
D:\usr\local\mysql\bin>mysqld --initialize --user=mysql --console --explicit_defaults_for_timestamp
2025-06-20T13:25:08.995251Z 0 [Warning] InnoDB: New log files created, LSN=45790
2025-06-20T13:25:09.081107Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2025-06-20T13:25:09.175719Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: fcb9b279-4dd9-11f0-bcff-3c970e3c7ec6.
2025-06-20T13:25:09.197778Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2025-06-20T13:25:10.723739Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2025-06-20T13:25:10.727148Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2025-06-20T13:25:10.730535Z 0 [Warning] CA certificate ca.pem is self signed.
2025-06-20T13:25:10.937104Z 1 [Note] A temporary password is generated for root@localhost: if<d/xGoY6#?
if<d/xGoY6#?为自动生成的root密码。
注册服务
D:\usr\local\mysql\bin>mysqld --install MySQL --defaults-file="D:\usr\local\mysql\my.ini"
Service successfully installed.
启动服务
D:\usr\local\mysql\bin>net start mysql
删除服务
D:\usr\local\mysql\bin>net stop mysql
D:\usr\local\mysql\bin>mysqld --remove MySQL
mysql 8.0
创建my.ini
[mysql]
default_character_set=utf8mb4
[mysqld]
port=3306
basedir=D:/usr/local/mysql/
datadir=D:/usr/local/mysql/data
max_connections=200
character_set_server=utf8mb4
default_storage_engine=INNODB
lower_case_table_names=1
default_time_zone='+8:00'
server_id=1
log_bin=D:/usr/local/mysql/log/bin
log_bin_index=D:/usr/local/mysql/log/bin.index
expire_logs_days=7
binlog_format=ROW
#binlog_do_db=mysql
#binlog_ignore_db=mysql
max_binlog_size=100M
binlog_cache_size=4M
max_binlog_cache_size=512M
plugin_load_add=validate_password.dll
plugin_load_add=validate_password.dll开启密码策略管理。
查看密码策略是否生效：
mysql> show variables like 'validate%';
初始化数据库
D:\usr\local\mysql\bin>mysqld --initialize --user=mysql --console
2025-06-20T14:42:31.134143Z 0 [System] [MY-013169] [Server] D:\usr\local\mysql\bin\mysqld.exe (mysqld 8.0.31) initializing of server in progress as process 6016
2025-06-20T14:42:31.169182Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-06-20T14:42:32.051844Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-06-20T14:42:32.690248Z 0 [Warning] [MY-013501] [Server] Ignoring --plugin-load[_add] list as the server is running with --initialize(-insecure).
2025-06-20T14:42:35.467199Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 9lc?BR72%c?L
注册服务
D:\usr\local\mysql\bin>mysqld install MySQL
启动服务
D:\usr\local\mysql\bin>net start MySQL
CentOS 7.9
mysql 5.6.37
安装mysql
[root@ecs ~]# sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
[root@ecs ~]# setenforce 0
[root@ecs ~]# groupadd mysql
[root@ecs ~]# useradd -s /sbin/nologin -g mysql -M mysql
[root@ecs ~]# tar -zxvf mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz
[root@ecs ~]# mv mysql-5.6.37-linux-glibc2.12-x86_64 /usr/local/mysql
[root@ecs ~]# chown -R mysql:mysql /usr/local/mysql
[root@ecs ~]# chmod -R 770 /usr/local/mysql
[root@ecs ~]# chmod -R 777 /tmp
[root@ecs ~]# yum install -y 'perl(Data::Dumper)' libaio-devel
[root@ecs ~]# /usr/local/mysql/scripts/mysql_install_db \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--user=mysql
[root@ecs ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
[root@ecs ~]# sed -i "/^basedir=/cbasedir=/usr/local/mysql/" /etc/init.d/mysql
[root@ecs ~]# sed -i "/^datadir=/cdatadir=/usr/local/mysql/data" /etc/init.d/mysql
[root@ecs ~]# cp -f /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
[root@ecs ~]# cat >> /etc/my.cnf << EOF
user=mysql
lower_case_table_names=1
EOF
[root@ecs ~]# chkconfig mysql on
[root@ecs ~]# service mysql start
修改密码
[root@ecs ~]# [root@ecs ~]# /usr/local/mysql/bin/mysql -uroot \
-e "grant all privileges on *.* to 'root'@'localhost' identified by 'changeme' with grant option;flush privileges;"
放行端口
[root@ecs ~]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
[root@ecs ~]# firewall-cmd --reload
mysql 5.7.31
安装mysql
[root@ecs ~]# sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
[root@ecs ~]# setenforce 0
[root@ecs ~]# groupadd mysql
[root@ecs ~]# useradd -s /sbin/nologin -g mysql -M mysql
[root@ecs ~]# cd /usr/local/src
[root@ecs ~]# wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -P /usr/local/src --progress=bar
[root@ecs ~]# tar -zxvf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@ecs ~]# mv mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql
[root@ecs ~]# mkdir -p /usr/local/mysql/log
[root@ecs ~]# chown -R mysql:mysql /usr/local/mysql
[root@ecs ~]# chmod -R 770 /usr/local/mysql
[root@ecs ~]# chmod -R 777 /tmp
[root@ecs ~]# yum install -y libaio-devel
[root@ecs ~]# /usr/local/mysql/bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--explicit_defaults_for_timestamp=1
[root@ecs ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@ecs ~]# sed -i "/^basedir=/cbasedir=/usr/local/mysql" /etc/init.d/mysqld
[root@ecs ~]# sed -i "/^datadir=/cdatadir=/usr/local/mysql/data" /etc/init.d/mysqld
[root@ecs ~]# sed -i "/^datadir=/cdatadir=/usr/local/mysql/data" /etc/my.cnf
[root@ecs ~]# sed -i "/^socket=/csocket=/tmp/mysql.sock" /etc/my.cnf
[root@ecs ~]# sed -i "/^log-error=/clog-error=/usr/local/mysql/data/mysql.err" /etc/my.cnf
[root@ecs ~]# sed -i "/^pid-file=/cpid-file=/usr/local/mysql/data/mysql.pid" /etc/my.cnf
[root@ecs ~]# sed -i "/^\[mysqld\]$/a\lower_case_table_names=1" /etc/my.cnf
[root@ecs ~]# systemctl enable --now mysqld
放行端口
[root@ecs ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@ecs ~]# firewall-cmd --reload
mysql 8.0.19源码安装
创建my.cnf
[root@ecs ~]# cat > /etc/my.cnf << EOF
[mysql]
default-character-set=utf8mb4
[mysqld]
socket=/tmp/mysql.sock
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
max_connections=200
character_set_server=utf8mb4
default_storage_engine=INNODB
lower_case_table_names=1
log_timestamps=SYSTEM
default_time_zone='+8:00'
default_authentication_plugin=mysql_native_password
server_id=1
log_bin=/usr/local/mysql/log/bin
log_bin_index=/usr/local/mysql/log/bin.index
[mysqld_safe]
log_error=/usr/local/mysql/data/mysql.log
pid_file=/usr/local/mysql/data/mysql.pid
EOF
安装mysql
[root@ecs ~]# sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
[root@ecs ~]# setenforce 0
[root@ecs ~]# groupadd mysql
[root@ecs ~]# useradd -s /sbin/nologin -g mysql -M mysql
[root@ecs ~]# tar -Jxvf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
[root@ecs ~]# mv mysql-8.0.19-linux-glibc2.12-x86_64 /usr/local/mysql
[root@ecs ~]# mkdir -p /usr/local/mysql/log
[root@ecs ~]# chown -R mysql:mysql /usr/local/mysql
[root@ecs ~]# chmod -R 770 /usr/local/mysql
[root@ecs ~]# chmod -R 777 /tmp
[root@ecs ~]# yum install -y libaio-devel
[root@ecs ~]# /usr/local/mysql/bin/mysqld \
--defaults-file=/etc/my.cnf \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--user=mysql --initialize-insecure
[root@ecs ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@ecs ~]# systemctl enable --now mysqld
[root@ecs ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@ecs ~]# firewall-cmd --reload
--initialize-insecure初始化空密码，否则会生成随机密码。
mysql 8.0.19 rpm安装
安装mysql
[root@ecs ~]# tar -xvf mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar
[root@ecs ~]# rpm -ivh mysql-community-common-8.0.19-1.el7.x86_64.rpm
[root@ecs ~]# yum remove -y mysql-libs
[root@ecs ~]# rpm -ivh mysql-community-libs-8.0.19-1.el7.x86_64.rpm
[root@ecs ~]# rpm -ivh mysql-community-client-8.0.19-1.el7.x86_64.rpm
[root@ecs ~]# yum install -y libaio-devel
[root@ecs ~]# rpm -ivh mysql-community-server-8.0.19-1.el7.x86_64.rpm
[root@ecs ~]# rpm -qa | grep mysql
[root@ecs ~]# mysqld --initialize
[root@ecs ~]# chown -R mysql:mysql /var/lib/mysql
[root@ecs ~]# systemctl start mysqld.service
[root@ecs ~]# systemctl enable mysqld
查看初始密码
[root@ecs ~]# cat /var/log/mysqld.log | grep password
Docker
mysql 5.6
创建配置文件
[root@ecs ~]# mkdir -p /data/mysql/{data,etc}
[root@ecs ~]# cat > data/mysql/etc/my.cnf << EOF
[mysqld]
lower_case_table_names=1
basedir=/usr/
datadir=/var/lib/mysql/
socket=/tmp/mysql.sock
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[mysql]
default-character-set=utf8mb4
EOF
创建容器
[root@ecs ~]# docker run -d --name mysql \
--privileged=true \
-e MYSQL_ROOT_PASSWORD=root \
-e MYSQL_DATABASE=maisi_db \
-e MYSQL_USER=maisi \
-e MYSQL_PASSWORD=123456789 \
-p 3306:3306 \
-v /data/mysql/etc/my.cnf:/etc/my.cnf \
-v /data/mysql/data:/var/lib/mysql \
maisi/mysql:5.7
登录测试
[root@ecs ~]# docker exec -it mysql mysql -u root -p
mysql 8.0
创建配置文件
[root@ecs ~]# mkdir -p /data/mysql/{data,etc}
[root@ecs ~]# cat > /data/mysql/etc/my.cnf << EOF
[mysql]
default-character-set=utf8mb4
[mysqld]
lower_case_table_names=1
basedir=/usr/
datadir=/var/lib/mysql/
socket=/tmp/mysql.sock
default_authentication_plugin=mysql_native_password
EOF
创建容器
[root@ecs ~]# docker run -d --name mysql \
-e MYSQL_ROOT_PASSWORD=root \
-p 3306:3306 \
-v /data/mysql/etc/my.cnf:/etc/my.cnf \
-v /data/mysql/data:/var/lib/mysql \
maisi/mysql:8.0
查看版本
[root@ecs ~]# docker exec -it mysql mysql --version
mysql Ver 8.0.42 for Linux on x86_64 (MySQL Community Server - GPL)
[root@ecs ~]# docker exec -it mysql mysql -uroot -p -e "select version();"
+-----------+
| version() |
+-----------+
| 8.0.42 |
+-----------+
Ubuntu
安装mysql
root@ecs:~# cd /usr/local/src && tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
root@ecs:/usr/local/src# mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql
root@ecs:/usr/local/src# groupadd mysql
root@ecs:/usr/local/src# useradd -s /sbin/nologin mysql -g mysql
root@ecs:/usr/local/src# chown -R mysql:mysql /usr/local/mysql
root@ecs:/usr/local/src# chmod -R 770 /usr/local/mysql
root@ecs:/usr/local/src# chmod -R 777 /tmp
root@ecs:/usr/local/src# ln -s /usr/lib/x86_64-linux-gnu/libncurses.so.6 /usr/lib/x86_64-linux-gnu/libncurses.so.5
root@ecs:/usr/local/src# ln -s /usr/lib/x86_64-linux-gnu/libtinfo.so.6 /usr/lib/x86_64-linux-gnu/libtinfo.so.5
root@ecs:/usr/local/src# /usr/local/mysql/bin/mysqld --initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--explicit_defaults_for_timestamp=1
[Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
常见报错：
- /usr/local/mysql/bin/mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
解决方法：ln -s /usr/lib/x86_64-linux-gnu/libncurses.so.6 /usr/lib/x86_64-linux-gnu/libncurses.so.5 - /usr/local/mysql/bin/mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
解决方法：ln -s /usr/lib/x86_64-linux-gnu/libtinfo.so.6 /usr/lib/x86_64-linux-gnu/libtinfo.so.5
****查看配置文件路径**
~~~shell
root@ecs:~# /usr/local/mysql/bin/mysql --help | grep "my.cnf"
~~~
配置my.cnf
root@ecs:~# cat > /etc/my.cnf <<EOF
[mysqld]
lower_case_table_names=1
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
[mysqld_safe]
log-error=/usr/local/mysql/log/mysql.log
pid-file=/usr/local/mysql/data/mysql.pid
!includedir /etc/my.cnf.d
EOF
root@ecs:~# mkdir /etc/my.cnf.d
配置服务
root@ecs:~# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
root@ecs:~# sed -i "/^basedir=/cbasedir=/usr/local/mysql" /etc/init.d/mysql
root@ecs:~# sed -i "/^datadir=/cdatadir=/usr/local/mysql/data" /etc/init.d/mysql
root@ecs:~# mkdir -p /usr/local/mysql/log
root@ecs:~# touch /usr/local/mysql/log/mysql.log
root@ecs:~# chown -R mysql:mysql /usr/local/mysql/log
启动服务
root@ecs:~# /etc/init.d/mysql start
或者：
root@ecs:~# service mysql start
root@ecs:~# systemctl enable --now mysql
参数配置
| 参数 | 说明 |
|---|
| character_set_server | 服务器级别字符集设置，没有明确指定字符集的情况下，数据库、表和列所使用的默认字符集 |
| default_character_set | 创建数据库的默认字符集
default_character_set和default-character-set等效 |
| server_id=1 | 集群内mysql服务器id，设置log_bin后必须设置server_id，否则无法启动mysql |
| log_bin | 开启Binlog并指定日志存放目录 |
| log_bin_index | 指定Binlog索引存放目录 |
| expire_logs_days | Binlog过期清理时间（天） |
| binlog_format | Binlog日志模式 |
| binlog_do_db | 设置收集日志的数据库，不设置则收集全部数据库 |
| binlog_ignore_db | 设置忽略收集日志的数据库 |
| max_binlog_size | 单个日志最大文件大小 |
| binlog_cache_size | Binlog缓存大小，事务大于缓存大小，则使用磁盘 |
| max_binlog_cache_size | 最大Binlog缓存大小，事务大于最大缓存大小，则事务失败 |
| skip_name_resolve | 默认值：OFF，ON表示禁止DNS解析，这样连接服务器的客户端的主机名必须和mysql.user表中授权的主机名一致，否则将无法连接
主机名授权的优点：如果一个客户端的IP地址经常变化，那么基于IP的授权将会很繁琐，而客户端的主机名一般是不会变化的，因此基于主机名的授权只须授权一次。 |
操作
登录数据库
[root@ecs ~]# mysql -uroot -p
查看配置信息
[root@ecs ~]# show variables like '%character%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)
创建数据库
mysql> create database maisi_db character set utf8mb4;
修改数据库编码
mysql> alter database maisi_db character set utf8mb4;
查看数据库编码
mysql> show variables like 'character_set_database';
创建用户
mysql> create user 'maisi'@'%' identified by 'XGu0ZWNSLGcp3nmu';
mysql> grant select,insert,update,delete,create,drop,index,alter on maisi_db.* to 'maisi'@'%' identified by 'XGu0ZWNSLGcp3nmu';
mysql> flush privileges;
删除用户
mysql> drop user maisi;
修改表编码
mysql> alter table app_user character set utf8mb4;
修改字段编码
mysql> alter table app_user change username username varchar(20) character set utf8mb4 not null;
导入数据
mysql> use maisi_db;
mysql> source /data/maisi_db.sql;
修改root密码
Mysql初始化时如果没有使用--initialize-insecure，初始化会生成一个随机root密码，默认root密码是处于过期状态的，因此必须修改密码后才能正常使用，否则会报错：ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
修改密码的方法较多，支持的方法任选其一：
方法1（仅适用于mysql 8.0以下版本）：
[root@ecs ~]# /usr/local/mysql/bin/mysql -uroot -e "grant all privileges on *.* to 'root'@'localhost' identified by 'changeme' with grant option;flush privileges;"
或者：
[root@ecs ~]# mysql -uroot -h 127.0.0.1 -p
mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'changeme' with grant option;
mysql> flush privileges;
注：
- 如果开启过远程登录，须将
localhost修改为%。 changeme须修改为实际root密码。- mysql 8.0及之后版本不再支持grant授权时创建用户，必须分步操作，先创建用户再进行授权，参考方法7。
方法2：
mysql> set password=password('changeme');
mysql> flush privileges;
- 即使不用password函数，效果也是一样的。
set password='changeme'与set password=password('changeme');等效。 - mysql 8.0及之后版本不再支持password函数，直接使用
set password='changeme'。
方法3（仅适用于mysql 5.6及以下版本）：
mysql> update mysql.user set password=password('changeme') where user='root';
mysql> flush privileges;
方法4（仅适用于mysql 5.7版本）：
mysql> update mysql.user set authentication_string=password('changeme') where user='root';
mysql> flush privileges;
- mysql 5.7开始存储密码的字段由
password改为authentication_string，mysql 8.0开始不再支持password函数，因此此方法仅适用于mysql 5.7版。 - mysql 8.0+不支持password函数，只能设置
authentication_string=''空密码。
方法5（适用于mysql 5.7及以上版本）：
mysql> alter user 'root'@'localhost' identified by 'changeme';
或者：
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'changeme';
如果修改当前登录用户，可以使用user()替换'root'@'localhost'。
方法6：
[root@ecs ~]# mysqladmin -uroot -pchangeme password "changeme"
方法7（仅适用于mysql 8.0及以后版本）：
mysql> create user 'root'@'%' identified by 'changeme';
mysql> grant all privileges on *.* to 'root'@'%' with grant option;
mysql> alter user 'root'@'%' identified with mysql_native_password by 'changeme';
mysql> flush privileges;
注：
grant all privileges on等效grant all on；with grant option允许用户将其拥有的权限授予给其他用户，因此必须添加，否则新增的root用户无法给其他用户授权；create user默认使用caching_sha2_password身份认证，客户端如果不支持caching_sha2_password，可以修改成mysql_native_password；- mysql 8.0及之后版本
create user和grant必须分步操作，mysql 8.0之前创建用户、设置密码、授权可以使用grant一步到位。
开启远程登录
mysql> update mysql.user set host='%' where user='root';
mysql> flush privileges;
设置密码有效期
MySQL自5.6.6版开始，添加了password_expired功能，可以用来设置用户密码是否过期。账户密码过期后，用户可以登录到mysql服务器，但是不能执行任何命令，修改密码后才可执行账户权限内的所有命令。这个特性添加在mysql.user表的password_expired字段（默认值：N）。
mysql> alter user user() identified with mysql_native_password by 'changeme' password expire interval 180 day failed_login_attempts 3 password_lock_time 2;
密码有效期180天，启用登录失败跟踪（连续3次输入错误密码则锁定账户2天）
MySQL自5.7.4版开始，可以通过全局变量default_password_lifetime（单位：天）来设置密码过期策略，MySQL会从启动时开始计算所有用户的密码过期时间。
[mysqld]
default_password_lifetime=120
设置密码永不过期
mysql> alter user 'root'@'localhost' password expire never;
强制密码立即失效
mysql> alter user 'root'@'localhost' password expire;
无授权登录
mysql 8.0之前版本，在配置文件中添加skip_grant_tables：
[mysqld]
skip_grant_tables
mysql 8.0及之后版本，不再支持配置文件skip_grant_tables项，须使用--skip-grant-tables参数启动mysql服务：
[root@ecs ~]# mysqld --console --skip-grant-tables --shared-memory
打开新会话窗口登录：
[root@ecs ~]# mysql -uroot -p
常见报错
1251 - Client does not support authentication protocol reuqested by server; consider upgrading MySQL client
mysql 8.0.11之后使用caching_sha2_password插件进行身份验证，而一些低版本客户端不支持caching_sha2_password，依旧使用的是mysql_native_password插件进行身份验证，因此验证失败。
解决方式：升级客户端，使其支持caching_sha2_password，或者将mysql的用户密码加密规则改为mysql_native_password。
方法1：指定mysql_native_password认证插件设置密码（推荐）
mysql> alter user 'root'@'%' identified with mysql_native_password by 'changeme';
mysql> flush privileges;
方法2：手动更新身份认证插件
查看授权信息
mysql> select host,user,authentication_string,plugin from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | $A$005$HYFpxsnfU[n|"&yh48JKDV..KTNA/H55wuaICzsXLRGYQr2/gybGCdV1i5 | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
YEq0QvCJD2A2pT3oubzhEVlDAoZ549WM0hjVds4zZKK/ | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
更换验证插件
mysql> update mysql.user set plugin='mysql_native_password' where user='root';
mysql> update mysql.user set authentication_string='' where user='root';
mysql> flush privileges;
mysql> alter user user() identified by 'changeme';
mysql> alter user 'root'@'%' identified by 'changeme';
mysql 8开始不再支持password函数，因此无法直接给authentication_string设置mysql_native_password加密方式的密文。
注：
- 不能直接使用
alter user user() identified by 'changeme';
更新mysql.user表某个用户的plugin为mysql_native_password后，内存中的plugin信息并未变更，执行alter user user() identified by 'changeme';修改密码依旧是caching_sha2_password加密方式； - 执行
flush privileges;会重新加载权限表（mysql.user、mysql.db等）到内存，此时用户的plugin为mysql_native_password，但authentication_string却是caching_sha2_password密文，导致无法验证，执行alter user user() identified by 'changeme';会报错：ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'，因此必须将authentication_string置空以避免验证； alter user user() identified by 'changeme';修改的只是当前会话用户的密码，如果root用户有多个host，必须给所有的root用户都重置一下密码。
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
解决方法：
mysql> flush privileges;
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'skip-grants host'
解决方法：
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'changeme';
host不一定是localhost，也有可能是%，必须根据查询后的值来设置。
mysql> select host from mysql.user where user='root';
ERROR 1130 (HY000): Host '::1' is not allowed to connect to this MySQL server
mysql.user中root用户的host默认为localhost，而当前连接的主机名是IPv6的::1，两者不一致，因此拒绝访问。
解决方法：
方法1：开启DNS解析，mysql会解析localhost和::1的IP地址，只要IP地址一致即允许连接。
[mysqld]
skip_name_resolve=OFF
方法2：跳过授权验证，修改host为%。
修改配置文件
[mysqld]
skip_grant_tables
登录mysql，无需输入密码，直接回车登录：
[root@ecs ~]# mysql -uroot -p
修改root用户的host值：
mysql> update mysql.user set host='%' where user='root';
mysql> flush privileges;
mariadb-libs is obsoleted by mysql-community-libs-8.0.19-1.el7.x86_64
CentOS 7安装了被废弃的mariadb-libs，需要先卸载mariadb-libs。
[root@ecs ~]# yum remove -y mysql-libs
存储引擎
InnoDB
存储结构
| 版本 | frm | ibd | opt | |
|---|
| <5.7 | √ | × | | 数据默认存在系统表空间ibdata1（包含innodb表元数据、undo日志、修改buffer和双写buffer） |
| 5.7 | √ | √ | √ | 每个库有独立的opt文件存储库的信息（如字符集等）
ibd存储表数据（独立表空间） |
| 8.0 | × | √ | × | frm被sdi取代，sdi（Serialized Dictionary Information）是指表结构元数据。 |
mysql5.7以下版本开启独立表空间
my.ini：
innodb_file_per_table=1
查看是否生效：
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)
ibdata1
ibdata1是一个用来构建InnoDB系统表空间的文件，这个文件包含了innodb表的元数据、undo日志、修改buffer和双写buffer。随着数据库的使用，ibdata1文件会越来越大，innodb_autoextend_increment选项则指定了该文件每次自动增长的步进，默认是8M。
当在MySQL中对InnoDB表进行更改时，这些更改首先存储在InnoDB日志缓冲区的内存中，然后写入通常称为重做日志（redo logs）的InnoDB日志文件中。
MySQL开启独享表空间后，会为每个Innodb表创建一个.ibd的文件。开启方法：innodb_file_per_table=1，这样业务数据将使用独享表空间，而不用全部写入系统表空间ibdata1。
innodb_data_home_dir指定系统表空间文件存放目录，默认为datadir目录。
innodb_data_file_path配置多个innodb tablespace的文件名及文件大小：
innodb_data_file_path=ibdata1:18M;ibdata2:12M:autoextend，只有最后一个表空间文件可以被指定为:autoextend（自动扩展）。
表空间大小必须与当前文件的实际大小匹配，否则会报错：
InnoDB: Error: data file .\ibdata1 is of a different size
InnoDB: 1152 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 16384 pages!
计算公式：1152(pages) / 64(pages/M) = 18(M)。
数据恢复
- 删除ibdata1、ib_logfile0和ib_logfile1后，启动mysql；
- 新建库，按照需要恢复库中的表名新建表。如果没有表结构，可以使用mysql-utilities工具读取frm文件获取表结构；
停止mysql服务，把原来表的frm文件覆盖到新建的库，修改my.ini后重启mysql服务：
[mysqld]
innodb_force_recovery=6
删除.ibd文件：
mysql> use maisi_db;
mysql> alter table app_user discard tablespace;
如果提示：Cannot delete or update a parent row: a foreign key constraint fails，则需要先关闭外键约束检查，执行删除.ibd命令后，再开启外键约束检查。
mysql> set foreign_key_checks=0;
mysql> alter table app_user discard tablespace;
mysql> set foreign_key_checks=1;
- 停止mysql，将原表的ibd文件拷贝到当前库；
启动mysql，绑定表结构和表数据：
mysql> use maisi_db;
mysql> alter table app_user import tablespace;
自动备份
创建备份脚本backup.sh
#!/bin/sh
/usr/local/mysql/bin/mysqldump -umaisi -pXGu0ZWNSLGcp3nmu maisi_db --default-character-set=utf8 --opt --skip-lock-tables > /data/backup/maisi_db_`date +%Y-%m-%d`.sql
find /data/backup/ -name "maisi_db_*" -type f -mtime +7 -exec rm {} \;
docker部署mysql
#!/bin/sh
MYSQLDUMP="docker exec mysql /usr/bin/mysqldump"
$MYSQLDUMP -h127.0.0.1 -u maisi -pXGu0ZWNSLGcp3nmu maisi_db > /data/backup/maisi_db_$(date +%Y-%m-%d).sql
自动运行
[root@ecs ~]# echo "0 0 */1 * * root /data/backup/backup.sh" >> /etc/crontab
[root@ecs ~]# systemctl restart crond
0 0 */1 * *：表示每天00:00（0分0小时）执行一次。
或者：
[root@ecs ~]# cat >> /etc/cron.d/backup << EOF
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
0 0 */1 * * root /data/backup/backup.sh
EOF
[root@ecs ~]# systemctl restart crond