2

I'd like to duplicate a mysql database I've named db1 using the command line. In doing so, I'd like to grab all the tables and fields from db1, but not its values.

I've seen many SO Q&A on this topic. For example, Copy/duplicate database without using mysqldump. Unfortunately, I'm unable to accomplish two-step process suggested in this link:

  1. create database
  2. mysqldump from db1 to db2

here's my code starting at bash terminal:

ubuntu@ip-xx-xxx-xxx-xx:~$ sudo -i
root@ip-xx-xxx-xxx-xx:~# mysql -h mysite.com -u timpeterson -p
Enter password: 
mysql> create database `db2`;
ERROR 1044 (42000): Access denied for user 'timpeterson'@'%' to database 'db2'

It seems worth noting that I can create db2 as long as I don't become root by typing sudo i. Unfortunately, if I don't become root I can't figure out how to access my db2 remotely which is what I ultimately need to do. That is, I don't know how to access db2 from my web app, mysite.com.

Community
  • 1
  • 1
tim peterson
  • 23,653
  • 59
  • 177
  • 299
  • what the purpose of sudo?, you need to provide username and password to mysql anyway – Puggan Se Jul 10 '12 at 19:44
  • I don't know what the purpose of sudo is in this case. If I don't need it I won't use it. Can you comment on the last paragraph in my question above? Without using sudo I can't figure out how to access `db2` remotely. – tim peterson Jul 10 '12 at 19:53

2 Answers2

3

Well, of course you need to use a login with the rights for CREATing a database.

From the mysql admin account (often root or mysqladm[in]),

CREATE DATABASE db2;
-- instead of ALL PRIVILEGES, see what privileges you really need for the application
GRANT ALL PRIVILEGES ON db2.* TO 'timpeterson'@'%' IDENTIFIED BY 'timspassword';
FLUSH PRIVILEGES;

then from the command line

mysqldump --opt --no-data db1 --user=USERWITHACCESSTODB1 --password | mysql db2 --user=timpeterson --password

The FLUSH PRIVILEGES is particularly tricky -- on some installations, privileges aren't reloaded automatically, so you might find yourself GRANTing Clark Kent's superpowers on a user, and that user still being unable to do anything.

LSerni
  • 55,617
  • 10
  • 65
  • 107
  • by typing: `GRANT ALL PRIVILEGES ON db2.* TO 'timpeterson'@'%' IDENTIFIED BY 'timspassword';` i'm still getting error `ERROR 1044 (42000): Access denied for user 'timpeterson'@'%' to database 'db2'` – tim peterson Jul 10 '12 at 19:56
  • db2 has been created (by root), and as root, after GRANT, you also execute FLUSH PRIVILEGES, and later on you use the correct password (here 'timspassword'), right? – LSerni Jul 10 '12 at 20:01
  • Yes, that all makes sense. The problem is I can't create db2 when i'm root. Sorry if this isn't clear in the code in my question above. – tim peterson Jul 10 '12 at 20:04
  • I had understood that you COULD create it, just that if you did, then you were no longer able to access it from outside. GRANT PRIVILEGES should fix that. – LSerni Jul 10 '12 at 20:05
  • 1
    Anyway, can you create the database as timpeterson, then quit MySQL client, log back in as user root, ad heap more privileges on timpeterson? That also should take care of the problem. – LSerni Jul 10 '12 at 20:07
  • Ok, I was able to `GRANT ALL PRIVILEGES ON...` and created db2. Now I'm getting errors with using my password in your 2nd command. Can you tell exactly what I should type instead of `--password` if my password is `timspassword`? Unfortunately, I need to be away from my computer for the next 2 hours but i'll check back then. thanks for all your help. – tim peterson Jul 10 '12 at 20:34
  • You still have to type --password (or -p, which is the same), to be asked your password. You can also write: "--password=timspassword", but that's not a very good idea, the password will remain in the shell history. – LSerni Jul 10 '12 at 20:43
  • The problem i encounter when I type `-p` is that get prompted to enter the password **twice** but I can only then enter my password **once** because the prompts are concatenated like this: `Enter password: Enter password:`. So when I type my password **once** I get the following error: `mysqldump: Got error: 1045: Access denied for user 'timpeterson'@'localhost' (using password: YES) when trying to connect` – tim peterson Jul 10 '12 at 23:37
1

It looks like your first problem might be a permissions issue. Try logging into the MySQL console as root and doing GRANT ALL PRIVILEGES ON db2.* TO 'timpeterson'@'%';. Then try again.

Jason Swett
  • 43,526
  • 67
  • 220
  • 351
  • I'm logged in as root and when I type: `GRANT ALL PRIVILEGES ON db2.* TO 'timpeterson'@'%';` i get the error `ERROR 1044 (42000): Access denied for user 'timpeterson'@'%' to database 'db2'` – tim peterson Jul 10 '12 at 19:57
  • Try issuing this command from the MySQL command line: SELECT user(); – LSerni Jul 10 '12 at 20:24
  • thanks @Iserni, can you see the comment above for the current status of this question? Sorry I have to step out for a bit but will check back in 2hours. – tim peterson Jul 10 '12 at 20:35
  • The permission denied error tells me you probably aren't actually root. – Jason Swett Jul 11 '12 at 00:18