• Sales: 1-800-961-2888
  • Support: 1-800-961-4454

Feedback

Let us know what you think of our Knowledge Center!

Open Cloud Community

Learn about Rackspace products, ask questions, and share your knowledge in our Open Cloud Community.

Importing Data to Cloud Databases

  • Article ID: 1504
  • Last updated on May 16, 2013
  • Authored by: Rackspace Support
  • (25 Comments)

This article describes how to import the data of an existing MySQL database into a Cloud Database. If you haven't already created a Cloud Database instance and an empty database to receive the imported data, you'll need to do that first.

The import process includes the following steps:

  1. Use the next generation Cloud Control Panel to create a Cloud Database instance with an empty database and a username and password to access it.
  2. When the database instance is created, click on it and take note of the Hostname. You'll use it in a later step. The Hostname looks something like this:

Database Hostname

  1. On the machine where your existing database is currently located, run the following MySQL command to export your database:
mysqldump -u username -p database_name > database_name.sql

database_name is the name of your existing datatabase. database_name.sql will be the name of the exported database file.  Replace "username" with the username you use to access the original database.

  1. Using SFTP, copy the exported .sql file to the Cloud Server that will access your Cloud Database.
  2. With the .sql file copied to your Cloud Server, use ssh to log into the server.
  3. If you don't have a mysql client installed on your server, install it now.

On Ubuntu and Debian, install the client with the following command:

sudo apt-get install mysql-client

On Fedora and CentOS, install the client with this command:

sudo yum install mysql
  1. Run the following MySQL import command, substituting that long hostname you copied from the control panel for the address in the command, your database username for "username", and your database name for "database_name":
mysql -h 31blah2d.rackspaceclouddb.com -u username -p database_name < database_name.sql

The database is imported and ready to accept new data.

Related Information

How Do I Import a Large MYSQL Database

External Links

MYSQL Documentation



© 2011-2013 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License


See license specifics and DISCLAIMER

25 Comments

Hi rackspace team,
I was trying to restore a db to my brand new cloud database and got this error.

mysql -h myhost.rackspaceclouddb.com -u myuser -p --max_allowed_packet=2G dbname < ~/dump.sql
Enter password:
ERROR 1153 (08S01) at line 602: Got a packet bigger than 'max_allowed_packet' bytes

This is strange i guess since i run it with the option --max_allowed_packet=2G

Can you help me out?
thanks in advance
By Anonymous on Aug. 2, 2012

Looking at the MySQL docs, it looks like the maximum value for max_allowed_packet is 1G. That value describes the maximum size of an SQL statement, rather than the size of the import as a whole, so hopefully dropping that value to 1G will let you complete the import.
By jered.heeschen on Aug. 2, 2012

Hi again rackspace team,
Sorry to disturb again but this is something i am not getting done and it's getting me really frustrated.
I am pretty sure it is mysql server that is responsible for accepting more or lesse allowed packet size.
What it seems is that my cloud db server is rejecting that i overide that max_allowed_packet and it is ignoring the value turning this into an impossible db to restore.
Which i think is clearly not the kind of service you want ot provide.
So tell me please what other options i might have? split the dump ?

Best regards
André

By Anonymous on Aug. 6, 2012

I'll ask the devs to see if they can provide more insight into the problem. On the database you're trying to import, do you know how large the packet size has to be? The packet size describes the largest SQL statement it will import, not the size of the entire database. Do you have a 2GB SQL statement in the import?
By jered.heeschen on Aug. 7, 2012

I'm really looking forward to this being fixed. Thanks to all those on the project!
By Daniel McDermott on Aug. 10, 2012

You may need to use the option that says:
"INSERT INTO tbl_name VALUES (1,2,3)"
This will break up the size of the "insert" statements in your exported SQL file.

I think that might just help. Another options may be to use a CSV file with the LOAD file command.
By doctorfox on Aug. 16, 2012

In step 7 above when you show the command to "Run the following MySQL import command", you suggest that people use the root user for the import. I don't think there is a way for an admin to get the root user's password. Perhaps this would be a better way to write it:

mysql -h hostid.rackspaceclouddb.com -u user_name -p database_name < database_name.sql

You will be prompted for user_name's password. Use the user_name and password values you used to create your database in step 1.

Hope that helps.
By Lance Birtcil on Sep. 12, 2012

When I try to import database I am getting below error. can any body have solution for that..

ERROR 1227 (42000) at line 7: Access denied; you need the SUPER privilege for this operation

Thanks,
Kalpesh
By Kalpesh on Sep. 15, 2012

ERROR 2013 (HY000) at line 1054: Lost connection to MySQL server during query

This occures during import mysql -h hostid.rackspaceclouddb.com -u root -p database_name < database_name.sql
By Aladdin on Mar. 26, 2013

Are you able to run the command without the import? I mean just connecting to a shell with the mysql command, to make sure the connection itself can be made.
By jered.heeschen on Mar. 26, 2013

Yes. It connects.

root@*****:~# mysql -h *****.rackspaceclouddb.com -u ***** -p -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| ***** |
+--------------------+
By Aladdin Shchaslyvyi on Mar. 26, 2013

You might be running into a packet size issue. Per the above, you can try using the "--max_allowed_packet=1G" option if you have some large pieces of data that you're trying to import.
By jered.heeschen on Mar. 26, 2013

I cannot import any database, I get the error below. I am using PHPMyAdmin
MySQL said:

#1227 - Access denied; you need the SUPER privilege for this operation
By Russ on Apr. 5, 2013

That error message is usually the result of connecting with a user with less than full privileges for the database you're importing to. You might need to grant the user additional privileges for the database, or may need to use a main/root user to do the import.
By jered.heeschen on Apr. 5, 2013

I realise I do not have required privs, but the problem is how can I do that, I have not been given a root user and I do not have the ability to change the privileges of any other user. The only option I have is to create a user or delete a user.
By Russ on Apr. 5, 2013

Hm, I wonder if the .sql file created for the restore tries to create the database instead of just populating the target you specify.

You might want to call our support number or create a support ticket for this issue. The support techs would be able to take a look at your account and might be able to see what's causing the problem with their greater access.

Otherwise, I'd take a look at the first few lines of the .sql file to see what instructions it tries to executed when you send it through the mysql import.
By jered.heeschen on Apr. 5, 2013

The error is when trying to create a stored procedure, which obviously requires SUPER privilege, this is a Joomla database.
I used the API to create a ROOT user and restored the database as root instead, which has solved the problem.

By Russ Michaels on Apr. 5, 2013

Thanks Russ, glad you fixed the problem (and sorry I wasn't more help). I know better user permission management for the control panel is in the works, but I don't know when it will be ready for implementation.
By jered.heeschen on Apr. 5, 2013

I tried following these steps. I have my .sql file on my Cloud Server in the DFW region and am trying to use it to populate my new database server, also in the DFW region. When I issue the mysql -h command from my Cloud Server referencing the lengthy hostid.rackspaceclouddb.com address, I get ERROR 2003 (HY000): Can't connect to MySQL server.

What do I need to do to overcome this?
By Russ McGuire on May. 4, 2013

I guess the first thing to check would be the hostname used. You might try re-copying that long address, just in case a letter got left out. The article needs to be edited to make it more clear that instead of "hostid", it should be the address specific to your instance, like:

3115b7a10372b173bcbec7bea9edc5101aae32b2.rackspaceclouddb.com

Also double-check that you're entering your root password correctly.
By jered.heeschen on May. 4, 2013

Thanks. I have the hostname right - I copy and pasted it from the control panel. I thought the problem might be that I didn't have enough disk space on the database server, but I upsized it to have plenty and got the same error.

I'm not sure what you mean when you say "root password". I don't have a root password for the database server. On my cloud server, my database user is root, but I wasn't able to create a user on the database server named root, so in the mysql -h command I put the username that I created on the database server and then typed in the password for that user when prompted.
By Russ McGuire on May. 5, 2013

The problem isn't limited to loading the database. When I SSH into my cloud server in DFW and then type mysql -h bcd3088479bc5c18656ab428ec756f1f4cd5d4d8.rackspaceclouddb.com -u the_user_I_created -p
and type in the password for that created user, I get the same error. It takes a long time before I get the error, so it appears to be a timeout.

Any guess what the problem is?
By Russ McGuire on May. 5, 2013

Sorry about the "root user" thing - what you did with the username is what I'd meant. I misspoke (mistyped?) there.

A timeout might be what I'd expect if the server and DB instance were in different regions, but since they're both in DFW that shouldn't be an issue. From what you've said, you're doing things right.

I suggest opening a support ticket via the Cloud Control Panel describing the problem. That would get the issue to support personnel who have access to your account, so they can troubleshoot more directly than I can.
By jered.heeschen on May. 5, 2013

I submitted a ticket and got a response that pointed me in the right direction. I'm copying the response here for anyone else that encounters the same problem:

The problem is most likely one of routes for the database instances missing from your cloud servers. Please review the following knowledge base article to add the missing routes to your cloud servers so they can communicate to database instances in the same datacenter:

http://www.rackspace.com/knowledge_center/article/database-as-a-service-network-routes

Once you know the routes are configured, you can try to connect to the database instance as long as it's created in the same datacenter. If you continue to experience problems after adding the route please let us know and we'll be glad to help you with this.
By Russ McGuire on May. 8, 2013

Thanks Russ! Glad to see they helped you get the problem fixed, and definitely appreciate you sharing the solution here.
By jered.heeschen on May. 8, 2013

Add new comment