MySQL从入门到应用
安装
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.dllplugin_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
- /usr/local/mysql/bin/mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
****查看配置文件路径**
~~~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_tablesmysql 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 crond0 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
