主页 / dba / 问题 / 314183
Accepted
Paul
Asked: 2022-07-08 12:38:17 +0800 CST

使用 mysqldump 时跳过视图

  • 772

我想创建一个命令行脚本，它只备份我数据库中的表。有没有办法做到这一点？

我考虑过只指定表使用

SELECT table_name 
FROM information_schema.TABLES  
WHERE table_schema = 'flight_serve_png'  AND table_type = 'base table'

但希望有一个更简单的解决方案。

mysql mysqldump

2 个回答

  1. Best Answer

    从没有视图的给定数据库中转储表

    MYSQL_AUTH="-hlocalhost -u... -p..."
DB=mydb
SQL="SET group_concat_max_len = 10485760;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}' AND engine IS NOT NULL;"
TBLIST=$(mysql ${MYSQL_AUTH} -ANe"${SQL}")
mysqldump ${MYSQL_AUTH} --single-transaction ${DB} ${TBLIST} > ${DB}_tables.sql

    仅从给定数据库转储视图

    MYSQL_AUTH="-hlocalhost -u... -p..."
DB=mydb
SQL="SET group_concat_max_len = 10485760;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}' AND engine IS NULL;"
TBLIST=$(mysql ${MYSQL_AUTH} -ANe"${SQL}")
mysqldump ${MYSQL_AUTH} --single-transaction ${DB} ${TBLIST} > ${DB}_views.sql
    • 2

  2. 要仅备份表，请执行以下两个步骤：

    1.创建一个只有SELECT, LOCK TABLES和PROCESS 权限的用户：

    GRANT SELECT, LOCK TABLES ON `your_db_name`.* TO `your_user`@`host`;
GRANT PROCESS ON *.* TO `your_user`@`host`

    注意.PROCESS 是不能在特定模式中授予的权限。

    2.使用以下mysqldmp命令

     mysqldump -u your_user -p --force  your_db_name> your_db_name_only_tables.sql

    在终端中将打印以下错误，这些错误将被--force选项跳过：

    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

相关问题