关于MySQL的注意事项

今天上班的时候想起需要备份一下公司的数据库防止数据的丢失，这里使用MySQL自带的mysqldump进行备份。使用的是idea的图形化界面进行备份操作（当然你也可以使用命令的方式）

注意点1：备份的时候出现

mysqldump:Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS

 　因为新版的mysqldump默认启用了一个新标志，通过- -column-statistics=0来禁用他，加上之后的命令就变成了这样：

--databases "数据库名" -h域名 -p3306 -uroot -p密码 --ssl-mode DISABLED --column-statistics=0 --result-file= "D:\db\db.sql"

注意点2:

我们在备份导出的时候发现新的报错，1194-table “xxx“ is marked as crashed and should be repaired。这里的解决方案是：使用Navicat for MySQL工具修复表：
选中需要修复的表，右击选择维护，维护选项弹出选择项，选择修复表，弹出选择框，选择快速即可。

注意点3：

备份完之后我们就要进行数据库的恢复操作了，这里直接使用idea自带的工具

 我的数据库的字符集是 utf8mb4，需要参数--default-character-set明确指明，否则会导入失败。

到这里一步我们的数据库就备份完成了。接下来就开始愉快的启动项目吧。

注意点4：

启动项目的时候疯狂报错：

java.sql.SQLException: Duplicate foreign key constraint name 'ACT_FK_BYTEARR_DEPL'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
    at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:3010)
    at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2484)
    at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
    at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:3008)
    at com.alibaba.druid.wall.WallFilter.statement_execute(WallFilter.java:421)

大致意思是说外键有重复了，通过观察发现我们数据库中有很多大写字母的表名的表。通过show variables like '%case%' 命令查看lower_case_table_names=0，所以才会产生这种现象。我们现在需要把这个属性设置成0，就需要手动在配置文件里面加入一句话：lower_case_table_names=1并重启MySQL服务。但是我们发现MySQL服务重启失败了，我这里使用的是docker容器的MySQL8.0版本。可是我明明已经设置了并且也重启了，为啥没效果呢？？？

于是我们查看了官网的解释：

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names

-- linux未设置默认是0 

The default value of this variable is platform-dependent (see lower_case_file_system). On Linux and other Unix-like systems, the default is 0. On Windows the default value is 1. On macOS, the default value is 2. On Linux (and other Unix-like systems), setting the value to 2 is not supported; the server forces the value to 0 instead.

-- 禁止使用与服务器初始化时使用的设置不同的lower_case_table_names启动服务器 

It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are determined by the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

-- 在初始化服务器之前，有必要将lower_case_table_names配置为所需的设置

It is therefore necessary to configure lower_case_table_names to the desired setting before initializing the server. In most cases, this requires configuring lower_case_table_names in a MySQL option file before starting the MySQL server for the first time.

 原来需要MySQL8.0初始化的时候就需要设置好，并且后续不支持修改。于是我又重新用Docker新创了一个新的MySQL8.0容器继续测试。docker命令如下：

docker run --name mysql -e MYSQL_ROOT_PASSWORD=xxxxxx -p 3306:3306 -v /tmp/mysql8.0/conf/hmy.cnf:/etc/mysql/conf.d/hmy.cnf -v /tmp/mysql8.0/data:/var/lib/mysql -v /home/ssj/soft/data:/var/lib/mysql-files  -d mysql:8.0

参数说明

• lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的。

• lower_case_table_names=1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写。

• lower_case_table_names=2 表名存储为给定的大小写，但是比较的时候是小写的 。

• Unix,Linux下lower_case_table_names默认值为 0 ；Windows下默认值是 1 ；Mac OS X下默认值是 2。

总结

可见我们在使用MySQL8.0前需要根据自己的使用场景评估是否要开启忽略大小写，如果需要开启忽略大小写，初始化前需要把lower-case-table-names=1写入到my.cnf配置文件中，这样才不会影响后续的使用。

新的问题：

到这里相必我们应该解决问题了，但是我们发现新的问题接连产生：

1045 Access denied for user ‘root‘@‘localhost‘ (using password: YES)

数据库怎么会登录不上了呢！

原来我们为了跟公司数据库的密码一致修改了密码，但是密码一旦被篡改，再次登录就会被MySQL屏蔽。这个时候要去解决这个问题可以采用很多种方案，我们列举其中一种方案：

1，先在配置文件里加上 skip-grant-tables 重启mysql后免密登陆进入mysql
2，查看当前认证方式以及用户，默认是 caching_sha2_password 认证，
select host, user, authentication_string, plugin from user;

3，如果2是默认认证方式，则修改为密码认证为 mysql_native_password先， 在配置文件里加上
default_authentication_plugin=mysql_native_password
后再次重启mysqld，免密登陆

4，不能直接修改密码，会报错。要先清空root密码：
update user set authentication_string='' where user='root';

flush privileges;

5，退出mysql,
删除/etc/my.cnf文件里的 skip-grant-tables ，再一次重启 mysql 服务，再次登陆的时候是空密码登陆：

mysql -u root -p

6.登陆后即可修改密码了：
ALTER USER 'root'@'%' IDENTIFIED BY '123456';

flush privileges;

7.完成。

注意不同MySQL版本修改的方法都不同，这里的方法MySQL8适用。