duplicate a mysql database using the command line

Question

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:

create database
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.

what the purpose of sudo?, you need to provide username and password to mysql anyway — Puggan Se
– Puggan Se, Commented Jul 10, 2012 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
– tim peterson, Commented Jul 10, 2012 at 19:53

LSerni · Accepted Answer · 2012-07-10 19:41:49Z

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.

answered Jul 10, 2012 at 19:41

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

tim peterson Over a year ago

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'

LSerni Over a year ago

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?

tim peterson Over a year ago

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.

LSerni Over a year ago

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 Over a year ago

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.

|

Jason Swett · Accepted Answer · 2012-07-10 19:40:39Z

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.

answered Jul 10, 2012 at 19:40

Jason Swett

45.4k70 gold badges231 silver badges360 bronze badges

4 Comments

tim peterson Over a year ago

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'

LSerni Over a year ago

Try issuing this command from the MySQL command line: SELECT user();

tim peterson Over a year ago

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.

Jason Swett Over a year ago

The permission denied error tells me you probably aren't actually root.

Collectives™ on Stack Overflow

duplicate a mysql database using the command line

2 Answers 2

8 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related