Mysqldump导出数据库出现1449的错误
一台InnoDB数据库快挂了，这两天忙着做备份。在导出数据库的时候总会遇到这个错误：
mysqldump: Got error: 1449: The user specified as a definer (’splender’@'%’) does not exist when using LOCK TABLES
这个错误和视图的创建方法有关，一时半会还真不知道怎么绕过去。Google查看后和 mysqldump 的原有用户权限有关！
我的数据库表不怎么多，不到1000个，就先逐个dump出来了。dump的时候顺便把有此报错的表名字列出来，再逐个处理掉。虽然有点麻烦，但能保护主要数据是正确的。
另一个错误样例及处理方法：
By default, views in MySQL 5.x are created with a security definer set to the root user. However, Plesk drops the root user from MySQL and replaces it with the admin user. When this happens, your views cannot by dumped by mysqldump since the root user (the security definer for the view) doesn’t exist in the mysql.user table.
You receive an error similar to the following:
mysqldump: Couldn't execute 'SHOW FIELDS FROM `some_tablename`': There is no 'root'@'localhost' registered (1449)
Usually, if you run a SHOW CREATE VIEW `tablename`, you’ll see something like this:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `some_tablename` AS select distinct `some_database`.`some_tablename`.`some_column` AS `alias` from `some_tablename`
You have two options in this situation:
- Change the security definer for each of your views to ‘admin’@’localhost’. Any new views you create will need to be adjusted as well.
- Create a root user in MySQL with the same privileges as the admin user and use the root user’s login to run mysqldump.
不明白 Security Definer 是什么意思 -。。-
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
