root@ergesttstsrv:~# mysqldump -u your_user -p --force your_db_name> your_db_name_only_tables.sql
Enter password:
mysqldump: Couldn't execute 'show create table `my_view`': SHOW VIEW command denied to user `your_user`@`host` for table 'my_view' (1142)
创建的文件将包含创建表和插入语句命令。
在 8.0.25 测试。
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)
mysql> create user `no_view`@`localhost` identified by 'BashA0!!0';
Query OK, 0 rows affected (0.05 sec)
mysql> GRANT SELECT, LOCK TABLES ON `gesti`.* TO `no_view`@`localhost`;
Query OK, 0 rows affected (0.04 sec)
mysql> GRANT PROCESS ON *.* TO `no_view`@`localhost`;
Query OK, 0 rows affected (0.04 sec)
mysql> show grants for no_view@localhost;
+-----------------------------------------------------------------+
| Grants for no_view@localhost |
+-----------------------------------------------------------------+
| GRANT PROCESS ON *.* TO `no_view`@`localhost` |
| GRANT SELECT, LOCK TABLES ON `gesti`.* TO `no_view`@`localhost` |
+-----------------------------------------------------------------+
2 rows in set (0.01 sec)
root@ergesttstsrv:~# mysqldump -u no_view -p --force gesti > gesti_no_view.sql
Enter password:
mysqldump: Couldn't execute 'show create table `my_view`': SHOW VIEW command denied to user 'no_view'@'localhost' for table 'my_view' (1142)
root@ergesttstsrv:~# cat /root/gesti_no_view.sql | grep "CREATE VIEW" --- > empty result
从没有视图的给定数据库中转储表
仅从给定数据库转储视图
要仅备份表,请执行以下两个步骤:
1.创建一个只有
SELECT, LOCK TABLES
和PROCESS
权限的用户:注意.PROCESS 是不能在特定模式中授予的权限。
2.使用以下mysqldmp命令
在终端中将打印以下错误,这些错误将被
--force
选项跳过:创建的文件将包含创建表和插入语句命令。
在 8.0.25 测试。