MySQL 允许任意ip进行访问，配置方法

开启MySQL远程访问权限 允许远程连接
更新于: 2022-03-19 07:08:13

基本思路

  1. 登录 MySQL
  2. 更新 user 表里的对应 user 信息(本地通常是  root 用户) 
  3. 更新权限信息，使刚才的修改生效

命令如下

# 1. 登录 mysql
mysql -u root -p

# 2. 选择 mysql 库，更新 user 表
use mysql;
UPDATE `mysql`.`user` SET `Host` = '%' WHERE `Host` = 'localhost' AND `User` = 'root';

# 3.更新权限信息，使刚才的修改生效
flush privileges;

如果出现 Duplicate entry '%-root' for key 'PRIMARY'

  • 说明已经存在
select host,user from user where user='root';

修改密码

use mysql;
set password for root @localhost = password(YOURPASS);
flush privileges;

mysqladmin -u root password "newpass"

删除远程用户

delete from mysql.user where user = 'root' and host = '%';

论备份数据库的重要性

  • 某一天，设置了弱密码，被黑客盯上了
  • 可惜，我这数据库基本不更新的，而且有备份
/*
 Navicat Premium Data Transfer

 Source Server         : west@mysql
 Source Server Type    : MySQL
 Source Server Version : 50737
 Source Host           : 103.96.148.144:3306
 Source Schema         : z_readme_to_recover

 Target Server Type    : MySQL
 Target Server Version : 50737
 File Encoding         : 65001

 Date: 15/03/2022 22:50:20
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for recover_your_data
-- ----------------------------
DROP TABLE IF EXISTS `recover_your_data`;
CREATE TABLE `recover_your_data` (
  `text` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of recover_your_data
-- ----------------------------
BEGIN;
INSERT INTO `recover_your_data` (`text`) VALUES ('All your data is a backed up. You must pay 0.13 BTC to 1Eft22NkrFiqDCptXAH4qUMakKBYc8ie1N 48 hours for recover it. After 48 hours expiration we will sell all your data on dark markets and the database dump will be dropped from our server!');
INSERT INTO `recover_your_data` (`text`) VALUES ('You can buy bitcoin here, https://localbitcoins.com or https://buy.moonpay.io/ After paying write to us in the mail with your DB IP: recmydb+2sijb@onionmail.org and you will receive a link to download your database dump.');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

参考