Skip views when using mysqldump

All we need is an easy explanation of the problem, so here it is.

I want to create an command line script that backs up just the tables from my database. Is there an option to do this?

I thought about specifying just the tables using

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

but would like an easier solution of possible.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Dump Tables From a Given Database Without Views

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

Dump Views Only From a Given Database

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

Method 2

To backup only the tables , follow below two steps :

1.Create a user with only SELECT, LOCK TABLES and PROCESS
permissions:

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

Note.PROCESS is a permission which can’t be given in a specific schema.

2.Use the following mysqldmp command

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

In terminal will be printed the following error which will be skipped by --force options:

[email protected]:~# 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)

The file created will contain create table and insert statement commands.

Tested on 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 [email protected];
+-----------------------------------------------------------------+
| Grants for [email protected]                                    |
+-----------------------------------------------------------------+
| GRANT PROCESS ON *.* TO `no_view`@`localhost`                   |
| GRANT SELECT, LOCK TABLES ON `gesti`.* TO `no_view`@`localhost` |
+-----------------------------------------------------------------+
2 rows in set (0.01 sec)


[email protected]:~# 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)

[email protected]:~# cat /root/gesti_no_view.sql | grep "CREATE VIEW"  --- > empty result

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply