Database dump commands

DB Dump Commands:

 now start ur sql
from commandprompt
 set this variable
 set global max_allowed_packet =  50000000;
 and
 set global wait_timeout = 864000; and
create new datbase
 create database [dbname];
[dbname] to be replaced with databse name
 keep this window open
 and go to the bin directory of mysql
and here u give this command
 mysql -vvv -u root -p [dbname] < [backupfile.sql]
 [dbname] to be replaced with ur dbname
 [backupfile.sql] to be replaced with dump file
 example
mysql -vvv -u root -p liveinfo < "c:/dblife01_firelportalprod 02102003 22.sql"
 this will start import


Exporting dump of Local DB to .sql file:
----------------------------------------
mysqldump -u cisconciqa -p cisconciqa > QADatabaseDump-26th-May-2011.sql


Importing dump to Local DB from .sql file:
------------------------------------------
mysql -vvv -u root -p cisconci < E:\Projects\CISCO\DB Dumps\QA_DBDump_31_March_2011.sql
mysql -vvv -u root -p cisconciqa < C:\Documents and Settings\username\Desktop\temp\QADatabaseDump-26th-May-2011.sql


mysql -vvv -u cisconewsroom -p cisconewsroom < cisconci_local_DBDump_20_March_2011.sql


Connecting to Remote DB:
--------------------------
mysql -h 192.168.2.18 -u cisconciqa -pcisconciqa -D 'cisconciqa';


Taking dump of Remote DB to .sql file:
--------------------------------------
mysqldump -h 192.168.2.18 -u cisconciqa -pcisconciqa 'cisconciqa' > QADatabaseDump-26th-May-2011.sql


mysqldump -u root -p info4fireLivedb user_commenting_table > dblife01_firelportalprod_user_commenting_table.sql


mysql -vvv -u root -p cisconci < E:\Projects\CISCO\DB Dumps\cisconci_QA_DBDump_31_March_2011.sql


mysql -u root -p [database_name] < dumpfilename.sql


mysql -vvv -u root -p info4firelivedb < dblife01_firelportalprod.sql


mysql -vvv -u root -p expo_uat < expo-backup-090810.sql

--------------------------------------------------------------------

MySQL Tutorial - Import or Export A Database

This tutorial outlines the steps for importing and exporting MySQL databases via command line (SSH). Click Here for information about getting SSH access.

1. Export A MySQL Database

This example shows you how to export a database. It is a good idea to export your data often as a backup.
Using SSH, execute the following command:
mysqldump -u username -p database_name > dbname.sql
You will be prompted for a password, type in the password for the username and press Enter. Replace username, password and database_name with your MySQL username, password and database name.
File dbname.sql now holds a backup of your database and is ready for download to your computer.

2. Import A MySQL Database

The file must be in .sql format. It can not be compressed in a .zip or .tar.gz file.
Start by uploading the .sql file onto the Bluehost server
If you haven't already done so, create the MySQL database via the cpanel. Click Here for further instructions
Using SSH, navigate to the directory where your .sql file is.
Next run this command:
 mysql -p -u username database_name < file.sql
Note: The -p will prompt for your account's password.

Note: username is the user with rights to the database. If you are unsure what the username is you can use the same username and password used to sign into SSH.
Note: Make sure your database name has your Bluehost username prefix with the _ (underscore) after it and the database name.


MySQL DB Permission Commands:



C:\Documents and Settings\username>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 114
Server version: 5.5.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'root'@'%' identified by 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\Documents and Settings\username>mysql -uroot -proot --host=192.168.4.25
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 115
Server version: 5.5.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

C:\Documents and Settings\username>