mysqldump注意事项

参考三思笔记,需要重视这个无效view问题可能导致mysqldump失败.

http://space.itpub.net/7607759/viewspace-697899

 

自己也先演练一下.

mysql> create table kin(id int,sel varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> create view kin_view as select * from kin;
Query OK, 0 rows affected (0.00 sec)

mysql> rename table kin to jay;
Query OK, 0 rows affected (0.00 sec)

mysql> desc views;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG        | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA         | varchar(64)  | NO   |     |         |       |
| TABLE_NAME           | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION      | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION         | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE         | varchar(3)   | NO   |     |         |       |
| DEFINER              | varchar(77)  | NO   |     |         |       |
| SECURITY_TYPE        | varchar(7)   | NO   |     |         |       |
| CHARACTER_SET_CLIENT | varchar(32)  | NO   |     |         |       |
| COLLATION_CONNECTION | varchar(32)  | NO   |     |         |       |
+----------------------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

 mysql> select table_name,view_definition,is_updatable from views;\g
+------------+-----------------------------------------------------------------------------+--------------+
| table_name | view_definition                                                             | is_updatable |
+------------+-----------------------------------------------------------------------------+--------------+
| kin_view   | select `KIN`.`kin`.`id` AS `id`,`KIN`.`kin`.`sel` AS `sel` from `KIN`.`kin` | NO           |
+------------+-----------------------------------------------------------------------------+--------------+
1 row in set, 1 warning (0.01 sec)

[root@MASTER tmp]# mysqldump -uroot -p --databases KIN > /tmp/bak.sql
Enter password:
mysqldump: Got error: 1356: View 'KIN.kin_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES

 

参考解决方案:

1..修正view

2.在命令添加参数force

# mysqldump -uroot -p --databases -f KIN > /tmp/bak.sql