bahattab
05-12-2008, 12:08 AM
Backup/Restore MySQL via SSH
From Site5Wiki
Jump to: navigation (http://wiki.site5.com/Backup/Restore_MySQL_via_SSH#column-one), search (http://wiki.site5.com/Backup/Restore_MySQL_via_SSH#searchInput)
Creating a dump of your Database via SSH
If you have not already done so, first enable SSH (http://wiki.site5.com/SSH). For this tutorial we used Putty (http://www.chiark.greenend.org.uk/%7Esgtatham/putty/download.html).
Login into the server via SSH using your FTP username/password. This information will be located in your Welcome email as well as the IP address to login with. If you no longer have your welcome email you can view your IP address and username on the main page of SiteAdmin.
Once you are logged in, change to the public_html directory:
cd public_htmlNow you are ready to create your Backup (dump file), type:
mysqldump --opt -u dbuser -p dbname > filename.back.dumpie:
mysqldump --opt -u site5com_Laurey -p site5com_forums > 03-Jun-2008-Forums.back.dumpNote: All usernames and databases on Site5 are prefixed with your account username_, this information is required or your backup will fail.
It will then ask you for your database password, provide it and your backup should now be created inside your public_html directory for you to either FTP to your home computer or access via the web (ie: http://yourdomain.tld/03-Jun-2008-Forums.back.dump (http://yourdomain.tld/03-Jun-2008-Forums.back.dump)). Please note, this dump file is accessible to any when located in your public_html directory - it is highly suggested you immediately download this backup and then delete the instance on the server.
Note: Using the date in the filename will enable you to quickly see which is your most recent dump file.
The --opt switch states to optimize the dump file for use during restore , -u specifies user, - p requests a prompt for password.
Restoring your Database via SSH
Restoring your database is also a simple task, log into your server via SSH and switch to the public_html directory (where your dump file is located):
cd public_htmlOnce you are in the directory your dump file is located in, you will then type:
mysql -u username -p dbname < filename.back.dumpie:
mysql -u site5com_Laurey -p site5com_forums < 03-Jun-2008-Forums.back.dumpThis command will log into the database named "site5com_forums" using username site5com_Laurey and restore the information from 03-Jun-2008-Forums.back.dump to it once you have entered the database password that will be requested.
Basic SSH Commands
A listing of Some Of The Most Commonly Used SSH/Shell Commands (http://wiki.site5.com/SSH#Some_Of_The_Most_Commonly_Used_SSH.2FShell_Com mands)
http://wiki.site5.com/Backup/Restore_MySQL_via_SSH
use ssh and mysql commands
for importting the database :
How do I manage a MySQL to import or export my database using SSH?
Your MySQL databases can be administered through an SSH session via command line (especially if you have large sized data to import/export). To access your account using SSH you must subscribe to a hosting plan that supports this feature. Please follow these steps to check if SSH can be enabled on your account:
ENABLE SSH FOR YOUR ACCOUNT:
1. Login to the Netfirms Control Panel at http://www.netfirms.com
2. Click Site Tools
3. Click SSH
4. Click Enable
Then find an SSH client. Here are a couple of suggested free SSH tools:
Putty: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html (http://www.chiark.greenend.org.uk/%7Esgtatham/putty/download.html)
WinSCP: http://prdownloads.sourceforge.net/winscp/winscp380.exe?download (http://prdownloads.sourceforge.net/winscp/winscp380.exe?download)
SSH connections can be established with the following login credentials:
Username
Password
Host: ssh.netfirms.com (port 22)
CONNECTING TO A MYSQL DATABASE:
To connect to a mysql database via a SSH session: (where Username is the database username and dbid is the database id):
%mysql -A -q -uUsername -pPassword DatabaseID
$ mysql -uUser_vbforum -pPass_vbForum < /home/emad/public_html/databaseName_Backup_06-12-1429_11-55.sql
$ mysql -uVbforum -pVbForum2 < /home/emad/public_html/bahattab_06-12-1429_11-55.sql
NOTE: Your mysql Username, Password, DatabaseID information is stored in your Control Panel at https://controlpanel.netfirms.com. (https://controlpanel.netfirms.com./) Refer to Site Tools, Database Manager, and then click Admin for the database you are trying to administer.
BACKING UP YOUR MYSQL DATABASE:
1. Using Netfirms File Manager or the touch command in SSH, create a file called dbBackupFile.sql. The file may reside anywhere inside your /www folder.
2. To make a backup copy of your mysql database via a SSH session (backs up your database in extended format using the -e switch):
%/usr/local/nf/bin/mysqldump -e --force --quick -h mysqlhost -uUsername -pPassword DatabseID > dbBackupFile.sql
for exporting the database :
mysqldump database name > backup.sql
for Exa. :
mysqldump emad_vbForum > backup_10.sql
(You will then be prompted to enter your database password.)
DUMPING A TABLE FROM YOUR MYSQL DATABASE:
1. Using Netfirms File Manager or the touch command in SSH, create a file called tables.sql. The file may reside anywhere inside your /www folder.
2. Execute the following command in SSH (backs up your database in extended format using the -e switch):
/usr/local/nf/bin/mysqldump -e --force --quick -h mysqlhost -u Username -pPassword DatabaseID tablename > tables.sql
for exporting the database :
mysqldump database name > backup.sql
for Exa. :
mysqldump emad_vbForum > backup_10.sql
IMPORTING DATA INTO YOUR MYSQL DATABASE:
To import a sql file to an existing mysql database hosted on your account:
%/usr/local/nf/bin/mysql --force --quick -h mysqlhost -u Username -pPassword DatabaseID < File.sql
$ mysql -u user_vbforum -p pass_vbForum < /home/emad/public_html/databaseName_Backup_06-12-1429_11-55.sql
$ mysql -u vbforum -p vbForum2 < /home/emad/public_html/bahattab_06-12-1429_11-55.sql
NOTE: If you encounter a "max_questions" error, you should redo your mysql backup using the -e switch as in the example above. Then try the import command again.
IMPORTING TABLES INTO YOUR MYSQL DATABASE:
To import a table to an existing mysql database hosted on your account:
% /usr/local/nf/bin/mysql --force --quick -h mysqlhost -u Username -pPassword DatabaseUD tablename < tables.sql
NOTE 1: For additional help in mysql commands: %mysql -h
NOTE 2: Commands such as myisamchk and grant are not permitted by users for security reasons.
SCHEDULING YOUR DATABASE BACKUPSTo create a cron job to schedule your database backups please click here (http://support.netfirms.com/article.php?id=710) for further instructions.
http://support.netfirms.com/idx.php/75/691/DATABASE-MANAGEMENT/article/How-do-I-manage-a-MySQL-to-import-or-export-my-database-using-SSH.html
From Site5Wiki
Jump to: navigation (http://wiki.site5.com/Backup/Restore_MySQL_via_SSH#column-one), search (http://wiki.site5.com/Backup/Restore_MySQL_via_SSH#searchInput)
Creating a dump of your Database via SSH
If you have not already done so, first enable SSH (http://wiki.site5.com/SSH). For this tutorial we used Putty (http://www.chiark.greenend.org.uk/%7Esgtatham/putty/download.html).
Login into the server via SSH using your FTP username/password. This information will be located in your Welcome email as well as the IP address to login with. If you no longer have your welcome email you can view your IP address and username on the main page of SiteAdmin.
Once you are logged in, change to the public_html directory:
cd public_htmlNow you are ready to create your Backup (dump file), type:
mysqldump --opt -u dbuser -p dbname > filename.back.dumpie:
mysqldump --opt -u site5com_Laurey -p site5com_forums > 03-Jun-2008-Forums.back.dumpNote: All usernames and databases on Site5 are prefixed with your account username_, this information is required or your backup will fail.
It will then ask you for your database password, provide it and your backup should now be created inside your public_html directory for you to either FTP to your home computer or access via the web (ie: http://yourdomain.tld/03-Jun-2008-Forums.back.dump (http://yourdomain.tld/03-Jun-2008-Forums.back.dump)). Please note, this dump file is accessible to any when located in your public_html directory - it is highly suggested you immediately download this backup and then delete the instance on the server.
Note: Using the date in the filename will enable you to quickly see which is your most recent dump file.
The --opt switch states to optimize the dump file for use during restore , -u specifies user, - p requests a prompt for password.
Restoring your Database via SSH
Restoring your database is also a simple task, log into your server via SSH and switch to the public_html directory (where your dump file is located):
cd public_htmlOnce you are in the directory your dump file is located in, you will then type:
mysql -u username -p dbname < filename.back.dumpie:
mysql -u site5com_Laurey -p site5com_forums < 03-Jun-2008-Forums.back.dumpThis command will log into the database named "site5com_forums" using username site5com_Laurey and restore the information from 03-Jun-2008-Forums.back.dump to it once you have entered the database password that will be requested.
Basic SSH Commands
A listing of Some Of The Most Commonly Used SSH/Shell Commands (http://wiki.site5.com/SSH#Some_Of_The_Most_Commonly_Used_SSH.2FShell_Com mands)
http://wiki.site5.com/Backup/Restore_MySQL_via_SSH
use ssh and mysql commands
for importting the database :
How do I manage a MySQL to import or export my database using SSH?
Your MySQL databases can be administered through an SSH session via command line (especially if you have large sized data to import/export). To access your account using SSH you must subscribe to a hosting plan that supports this feature. Please follow these steps to check if SSH can be enabled on your account:
ENABLE SSH FOR YOUR ACCOUNT:
1. Login to the Netfirms Control Panel at http://www.netfirms.com
2. Click Site Tools
3. Click SSH
4. Click Enable
Then find an SSH client. Here are a couple of suggested free SSH tools:
Putty: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html (http://www.chiark.greenend.org.uk/%7Esgtatham/putty/download.html)
WinSCP: http://prdownloads.sourceforge.net/winscp/winscp380.exe?download (http://prdownloads.sourceforge.net/winscp/winscp380.exe?download)
SSH connections can be established with the following login credentials:
Username
Password
Host: ssh.netfirms.com (port 22)
CONNECTING TO A MYSQL DATABASE:
To connect to a mysql database via a SSH session: (where Username is the database username and dbid is the database id):
%mysql -A -q -uUsername -pPassword DatabaseID
$ mysql -uUser_vbforum -pPass_vbForum < /home/emad/public_html/databaseName_Backup_06-12-1429_11-55.sql
$ mysql -uVbforum -pVbForum2 < /home/emad/public_html/bahattab_06-12-1429_11-55.sql
NOTE: Your mysql Username, Password, DatabaseID information is stored in your Control Panel at https://controlpanel.netfirms.com. (https://controlpanel.netfirms.com./) Refer to Site Tools, Database Manager, and then click Admin for the database you are trying to administer.
BACKING UP YOUR MYSQL DATABASE:
1. Using Netfirms File Manager or the touch command in SSH, create a file called dbBackupFile.sql. The file may reside anywhere inside your /www folder.
2. To make a backup copy of your mysql database via a SSH session (backs up your database in extended format using the -e switch):
%/usr/local/nf/bin/mysqldump -e --force --quick -h mysqlhost -uUsername -pPassword DatabseID > dbBackupFile.sql
for exporting the database :
mysqldump database name > backup.sql
for Exa. :
mysqldump emad_vbForum > backup_10.sql
(You will then be prompted to enter your database password.)
DUMPING A TABLE FROM YOUR MYSQL DATABASE:
1. Using Netfirms File Manager or the touch command in SSH, create a file called tables.sql. The file may reside anywhere inside your /www folder.
2. Execute the following command in SSH (backs up your database in extended format using the -e switch):
/usr/local/nf/bin/mysqldump -e --force --quick -h mysqlhost -u Username -pPassword DatabaseID tablename > tables.sql
for exporting the database :
mysqldump database name > backup.sql
for Exa. :
mysqldump emad_vbForum > backup_10.sql
IMPORTING DATA INTO YOUR MYSQL DATABASE:
To import a sql file to an existing mysql database hosted on your account:
%/usr/local/nf/bin/mysql --force --quick -h mysqlhost -u Username -pPassword DatabaseID < File.sql
$ mysql -u user_vbforum -p pass_vbForum < /home/emad/public_html/databaseName_Backup_06-12-1429_11-55.sql
$ mysql -u vbforum -p vbForum2 < /home/emad/public_html/bahattab_06-12-1429_11-55.sql
NOTE: If you encounter a "max_questions" error, you should redo your mysql backup using the -e switch as in the example above. Then try the import command again.
IMPORTING TABLES INTO YOUR MYSQL DATABASE:
To import a table to an existing mysql database hosted on your account:
% /usr/local/nf/bin/mysql --force --quick -h mysqlhost -u Username -pPassword DatabaseUD tablename < tables.sql
NOTE 1: For additional help in mysql commands: %mysql -h
NOTE 2: Commands such as myisamchk and grant are not permitted by users for security reasons.
SCHEDULING YOUR DATABASE BACKUPSTo create a cron job to schedule your database backups please click here (http://support.netfirms.com/article.php?id=710) for further instructions.
http://support.netfirms.com/idx.php/75/691/DATABASE-MANAGEMENT/article/How-do-I-manage-a-MySQL-to-import-or-export-my-database-using-SSH.html