mysql dump 版本错误_关于mysqldump不同mysql版本出现的一个小问题

今天在测试mysqldump远程备份的时候，发现同样的备份权限、语句在远程无法使用，提示:

mysqldump: Couldn't execute 'show create table `general_log`':

Table 'mysql.general_log' doesn't exist

mysqldump: Couldn't execute 'show create table `slow_log`': Table

'mysql.slow_log' doesn't exist

推测是客户端mysqldump的版本与mysql-server版本差异导致，解决方法为往mysql里面插入这2张表；

mysql -p*****

use mysql;

CREATE TABLE IF NOT EXISTS general_log (

event_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

user_host mediumtext NOT NULL,

thread_id int(11) NOT NULL,

server_id int(10) unsigned NOT NULL,

command_type varchar(64) NOT NULL,

argument mediumtext NOT NULL

) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';

CREATE TABLE IF NOT EXISTS slow_log (

start_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

user_host mediumtext NOT NULL,

query_time time NOT NULL,

lock_time time NOT NULL,

rows_sent int(11) NOT NULL,

rows_examined int(11) NOT NULL,

db varchar(512) NOT NULL,

last_insert_id int(11) NOT NULL,

insert_id int(11) NOT NULL,

server_id int(10) unsigned NOT NULL,

sql_text mediumtext NOT NULL

) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';

quit;

然后就可以远程备份了。

mysqldump备份的用户不需要很高的权限，只要赋予

select,LOCK TABLES 即可，如:

grant SELECT,LOCK TABLES on *.* to backMysqltest@'192.168.1.9' identified by 'fNm@!cgPJKr94$0y';

转发至微博

转发至微博