导入导出
1
2
3
| mysqldump -u root -p123456 --all-databases
# 或指定数据库
mysqldump -u root -p123456 test
|
Problems
1
2
3
4
5
6
7
8
| mysql> ALTER USER 'root'@localhost IDENTIFIED BY '123' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER USER 'root'@localhost IDENTIFIED WITH mysql_native_password BY '123';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
|
1
2
3
4
5
6
7
8
9
10
| # 修改全局
set @@global.sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
# 修改当前
set @@sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
改完 check 一下，修改sql_mode(将上述查询到的sql_mode中的NO_ZERO_DATE和NO_ZERO_IN_DATE删除即可)
# 查看当前sql_mode
select @@sql_mode;
# 查看全局sql_mode
select @@global.sql_mode;
|
create table as
破防了，谁懂啊家人们：记一次mysql问题排查 (qq.com)
故事层层递进。知识点其实就一条，记录一下自用。大家可以看原文。
create table as 这个语句建表导致:
表主键、索引、auto_increment 丢失
