MySQL 问题笔记

mysqldump 导出提示 Couldn't execute SELECT COLUMN_NAME...

mysqldump 命令

导出数据库：mysqldump -h ip -u root -p dbname > db.sql;

导出数据库中的某个表：mysqldump -h ip -u root -p dbname tablename > tablename.sql;

错误提示

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

原因

因为新版的 mysqldump 默认启用了一个新标志，通过 --column-statistics=0 来禁用

解决方法

mysqldump --column-statistics=0 -h ip -u root -p dbname > db.sql;

远程访问 Linux 上 MySQL 出错解决方案

1. 修改 /etc/init.d/my.cnf 文件

修订：文件也可能在 /etc/my.cnf，/etc/mysql/my.cnf

修订：新 Linux 自带的 mariadb 在 /etc/mysql/mariadb.conf.d/50-server.cnf 中修改。

将文件中的bind-address = 127.0.0.1改为bind-address = 0.0.0.0，让所有 IP 都能访问

2. 修改 user 表

使用

sudo mysql

输入密码

use mysql<ENTER>
select user,host from user;<ENTER>将看到以下结果：
-------------------------------------------------------------------------
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| root             | debian    |
| debian-sys-maint | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
-------------------------------------------------------------------------

修改最后一行中的 localhost 为 %

update user set host="%" where user="root" and host="localhost";
update user set password=password('123') where user='root' and host='%';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;

3. 重启 mysql 服务

sudo systemctl restart mysqld