mysql创建用户并赋权，添加白名单，和mysqldump整库迁移

1、mysql创建用户

[root@master ~]# mysql -u root -p1w6666@R

# 创建用户test_v 和密码test1@9BY
mysql>  CREATE USER 'test_v' IDENTIFIED BY 'test1@9BY';
Query OK, 0 rows affected (0.05 sec)

# 创建数据库
mysql> create database test_1;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_ods           |
| test_1             |
+--------------------+
6 rows in set (0.00 sec)

2、赋权

# 报错
mysql> grant all on test_1  to test_v;
ERROR 1046 (3D000): No database selected
mysql> 
mysql> 
# 切换数据库
mysql> use test_1;
Database changed
# 赋权
mysql> grant all on test_1 to test_v;
Query OK, 0 rows affected (0.03 sec)

# 如果在Navicat建表报错，CREATE command denied to user for table，可以如下操作

或
mysql> grant all privileges on test_1.* to test_v;
Query OK, 0 rows affected (0.02 sec)

# 刷新系统权限表。
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

3、mysql添加白名单

# 切换至mysql库
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

# 查看当前允许登录IP及用户
mysql> select Host,User from user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | test_vv       |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

# 允许所有机器能访问root用户
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 查看当前允许登录IP及用户
mysql> select Host,User from user;
+------------------+---------------+
| Host             | User          |
+------------------+---------------+
| %                | root          |
| %  		       | test_v        |
| localhost        | mysql.session |
| localhost        | mysql.sys     |
+------------------+---------------+
4 rows in set (0.00 sec)

# 授权 root 用户所有权限
mysql> grant all privileges on *.* to 'root'@'%' identified by '12!@#QWEsd' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 1、赋予ip 192.168.1.1 使用用户test_vv所有的权限
mysql> grant all on test to 'test_vv'@'192.168.1.1' identified by '!Q@Wqwer23' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 2、没有密码则不用填
mysql> grant all on test to 'test_vv'@'192.168.1.1';
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 3、赋予部分权限
mysql> grant select,create,drop,update,alter on test to 'test_vv'@'192.168.1.1' identified by '!Q@Wqwer23' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 4、删除白名单用户的权限
mysql> DELETE FROM user WHERE User='test_vv' and Host='192.168.1.1';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#  ‘test_vv’@‘%’; 表示所有IP都可以用test_vv账号访问，需要把它删掉
mysql> DELETE FROM user WHERE User='test_vv' and Host='%';
Query OK, 1 row affected (0.01 sec)

# 赋予当前网段权限
mysql>  grant all on test to 'test_vv'@'localhost' identified by '!Q@Wqwer23' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 验证
mysql> select Host,User from user;
+-----------------+---------------+
| Host            | User          |
+-----------------+---------------+
| 192.168.1.1     | test_vv       |
| localhost       | test_vv       |
| localhost       | mysql.session |
| localhost       | mysql.sys     |
| %               | root          |
+-----------------+---------------+
4 rows in set (0.00 sec)

如果添加白名单后，用户能看到的数据库超出范围的多，可以如下修改

# 查看所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

# 撤销已经赋予给 test_vv用户的所有权限
mysql> revoke  all on *.* from 'test_vv'@'localhost' ;
Query OK, 0 rows affected (0.01 sec)

# 查询test_vv用户权限    
# 其中，USAGE ON *.*表示该用户对任何数据库和任何表都没有权限
mysql> SHOW GRANTS FOR 'test_vv'@'localhost';
+-------------------------------------------------------------+
| Grants for test_vv@localhost                                  |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_vv'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

4、mysql 整库迁移 – mysqldump

# 将数据库test_ods  表和数据导出
[root@master ~]#  mysqldump -uroot -p1w6666@R  test_ods > /data/test_ods.sql;
[root@master ~]# cd /data/
[root@master data]# ll
总用量 312
drwxr-xr-x. 8 root root    106 5月   5 23:08 davinci
-rw-r--r--. 1 root root 318275 6月  17 01:07 test_1.sql
# test_v 用户登录mysql
[root@master data]# mysql -u test_v -ptest1@9BY
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 788
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_1             |
+--------------------+
2 rows in set (0.00 sec)

mysql> use test_1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
# 导入test_ods 数据库的数据
mysql> source  /data/test_1.sql
....
mysql> show tables;
+------------------------------------+
| Tables_in_test_1		             |
+------------------------------------+
| branch_tistics                     |
| member_statistics     			 |
| member_statistics_bak  			 |
+------------------------------------+
3 rows in set (0.00 sec)

5、mysql增加端口号

# 1、改之前
[root@master ~]# mysql -u root -p1w6666@R
# 查看mysql端口号
mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.01 sec)

# 2、修改端口号
[root@localhost mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
port=3310 

[root@master mysql]# systemctl stop mysqld
[root@master mysql]# systemctl start mysqld
[root@master mysql]# systemctl status mysqld
[root@master ~]# mysql -u root -p1w6666@R
# 查看mysql端口号
mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3310  |
+---------------+-------+
1 row in set (0.00 sec)


# 3、防火墙添加端口号
# 添加永久开放的端口
[root@localhost ~]# firewall-cmd --add-port=3306/tcp --permanent
success
[root@localhost ~]# firewall-cmd --add-port=3310/tcp --permanent
success
# 查看所有永久开放的端口
[root@localhost ~]# firewall-cmd --list-ports --permanent
3306/tcp 3310/tcp 
# 重载
[root@localhost ~]# firewall-cmd --reload
success
# 重启防火墙
[root@localhost ~]# systemctl restart firewalld
# 查看所有永久开放的端口
[root@localhost ~]# firewall-cmd --list-ports --permanent
3306/tcp 3310/tcp 

用Navicat连接 3310 跟 3306都可以连通，如果报错，对应查看，有可能是赋权问题