MySQL错误解决集合

1、mysql登录错误 ERROR 1045 (28000):Access denied for user 'root'@'localhost' (using password: YES)

重置mysql user root密码即可

mysql -u root mysql
mysql>UPDATE user SET Password=PASSWORD('123456') where USER='root';
mysql> FLUSH PRIVILEGES;

 2、mysql导入错误

mysql> load data infile '/root/SQL6/in.txt' into table employee;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

是因为mysql默认的安全保护机制，需要修改mysql配置文件 /etc/mysql/my.cnf ，修改后需要重启mysql服务

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
secure-file-priv = ""     # 添加这一行代码

继续执行导入：

mysql> load data infile '/tmp/in.txt' into table employee;
ERROR 29 (HY000): File '/tmp/in.txt' not found (Errcode: 13)
我勒个fuck,居然还报错.....还是因为mysql安全保护机制，继续修改配置文件/etc/apparmor.d/usr.sbin.mysqld

/usr/sbin/mysqld {
  #include <abstractions/base>
  #include <abstractions/nameservice>
  #include <abstractions/user-tmp>
  #include <abstractions/mysql>
  #include <abstractions/winbind>

  capability dac_override,
  capability sys_resource,
  capability setgid,
  capability setuid,

  network tcp,

  /etc/hosts.allow r,
  /etc/hosts.deny r,

  /etc/mysql/*.pem r,
  /etc/mysql/conf.d/ r,
  /etc/mysql/conf.d/* r,
  /etc/mysql/*.cnf r,
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/lib/mysql-files/ r,
  /var/lib/mysql-files/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /var/run/mysqld/mysqld.pid rw,
  /var/run/mysqld/mysqld.sock w,
  /run/mysqld/mysqld.pid rw,
  /run/mysqld/mysqld.sock w,
  /sys/devices/system/cpu/ r,
  /tmp/* rw,     # 添加这两行
  /tmp/ r,    # 添加这两行

然后刷新配置 # sudo /etc/init.d/apparmor reload

再进行数据导入，成功导入

mysql> load data infile '/tmp/in.txt' into table employee;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0