mysql access via mysql command works while via mysqldump does not for same user -
i have brandnew mysql instance deployed , i'm attempting port elements development environment production enviroment.
i created user , granted privileges.
this user can access database command line using:
mysql -u oper -p mydb and create tables, insert, delete, truncate, etc.
now, use command mysqldump create script generate tables (and insert initial contents of them).
now, i'm trying inject script new database using command:
mysqldump -u oper -h localhost -p mydb < tables_only.sql and i'm getting error:
got error: 1045: access denied user 'oper'@'localhost' (using password: yes) when trying connect. when querying granted privileges:
show grants 'oper'@'localhost' ; +-----------------------------------------------------------+ | grants oper@localhost | +-----------------------------------------------------------+ | grant usage on *.* 'oper'@'localhost' | | grant privileges on `mydb`.* 'oper'@'localhost' | +-----------------------------------------------------------+ 2 rows in set (0.00 sec) can tell me i'm doing wrong?
thanks.
this guess , part of confusion seen in comment above
why issuing command
mysqldump -u oper -h localhost -p mydb < tables_only.sql?
mysqldump generating output. use script file later mysql command line tool.
what have done ask mysqldump start -p switch prompt password, gets input redirection .sql !
so passwords don't match, wasn't intent begin it, , bombs.
user management
-- below 2 lines catching fact have user create user 'oper'@'localhost' identified 'i_forgot'; -- user exists grant on mydb.* 'oper'@'localhost'; -- need tweak box catch -- create second user , set password create user 'oper2'@'localhost' identified 'newpassword'; grant on mydb.* 'oper2'@'localhost'; -- fix first user set password 'oper'@'localhost' = password('hispassword'); select user,host,password mysql.user user in ('oper','oper2'); +-------+-----------+-------------------------------------------+ | user | host | password | +-------+-----------+-------------------------------------------+ | oper2 | localhost | *ec760840cdac6af05321a7ddd05a84e18892aa97 | | oper | localhost | *1175038192340e5dffd6452e540b133e28b3e135 | +-------+-----------+-------------------------------------------+ 2 users, 2 different hashed password show grants 'oper'@'localhost' ; +-------------------------------------------------------------------------------------------------------------+ | grants oper@localhost | +-------------------------------------------------------------------------------------------------------------+ | grant usage on *.* 'oper'@'localhost' identified password '*1175038192340e5dffd6452e540b133e28b3e135' | | grant privileges on `mydb`.* 'oper'@'localhost' | +-------------------------------------------------------------------------------------------------------------+ show grants 'oper2'@'localhost' ; +--------------------------------------------------------------------------------------------------------------+ | grants oper2@localhost | +--------------------------------------------------------------------------------------------------------------+ | grant usage on *.* 'oper2'@'localhost' identified password '*ec760840cdac6af05321a7ddd05a84e18892aa97' | | grant privileges on `mydb`.* 'oper2'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ test 2 users @ command line. db table import new db
create database mydb; grant on mydb.* 'oper'@'localhost'; use mydb; create table t1 ( id int auto_increment primary key, varchar(20) not null ); insert t1(something) values ('frog'),('chopstick'); create database mydb2; grant on mydb2.* 'oper'@'localhost'; now @ command line (note user own path accordingly, not mine):
mysqldump -uoper -p mydb > c:\nate\out2.sql mysql -uroot -p mydb2 < c:\nate\out2.sql that table 2 rows in mydb2 database.
note: there time-wasting gotcha having space after -u switch @ command line. why jam db user name right next -uoper.
Comments
Post a Comment