Skip views when using mysqldump

Question

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.

Answer 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

Answer 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:

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)

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 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