1000 FAQs, 500 tutorials and explanatory videos. Here, there are only solutions!
Importing a large MySQL database on to the server admin1
The most common way of importing a database on to our servers is to use phpMyAdmin as explained in this guide.The following import method is a little more complex, but in principle is only required for databases which are too large to be imported directly via phpMyAdmin.
Actions to be carried out
Log into your control panel
Click on Tools > Databases
Add a database (or delete/re-create the existing database if the import is to restore this database)
From List of Existing Users, create a MySQL account and give it all rights to the database.
Name the dump files with the target database name and .sql extension (e.g. domainxyz.sql)
Log into FTP with the admin account sys_xxx
Place the files into the /data directory
Place a file named my.cnf into the /data directory (note the dot before my)
The file must be ASCII encoded and not UTF-8 etc.
This file must contain 5 lines:
- the # .my.cnf instruction (as is - do not modify)
- the [client] instruction (as is - do not modify)
- the address of the MySQL server (control panel -> Tools -> Database)
- the MySQL username
- the password for the MySQL user
<code># .my.cnf[client]host=mysql.domain.xyzuser=totopassword=1234</code>Apply CHMOD 600 to my.cnf, i.e. give it read/write access for the owner only.
Wait a few minutes for our robot to come round and pick up the MySQL dump in the data directory and import it into the database.
After processing, the file "MySQL database name"-"YYYYMMDDHHMMSS".log will contain the result of the statements as well as any import errors.If it is empty then everything is OK and the dump file target_database_name.sql will have been renamed to target_database_name.old.
Automatic MySQL import
The dump must be unzipped. It is important not to rename the zipped file but rather its content.
Dumps must not contain CREATE DATABASE, CONNECT or USE commands, otherwise the dump will not be imported correctly.
The "source" command cannot be used on our servers as the server on which your databases are hosted cannot be accessed from the file system.
We do not delete existing tables completely from active databases before importing the dump left in the /data directory. Rather, your SQL dump must contain the relevant statements to delete any table(s) if these are to be deleted prior to import. However, you may also simply delete and recreate the database from your control panel in order to empty it.
If the robot comes round while the .sql file is still being uploaded, it is possible that the robot will process the file while it is still incomplete. To avoid this, you can upload the file with an extension other than .sql so that it is not picked up directly by the robot, then rename this file once the upload is complete.
You may also use the bigdump.php script which splits the dump file and performs the import.