Thursday, June 21, 2012

Backup and Restore MySQL Database

The mysqldump is a backup program used to dump a database or collection of database for a backup
Syntax: Table level
$ mysqldump [options] db_name [tbble_name ...]
Database level
$ mysqldump [options] --databases database_name ...
All Databases
$ mysqldump [options] --all-databases

The mysqlimport is used to import the data into database.
Syntax:
$mysqlimport [options] db_name dumpfile1 [dumpfile2 ...]

You can load the dumpfile into server using mysql command.

Backup Mysql Database using mysqldump1. Take a backup of single database on the same server
$mysqldump -u user_name -d database_name –p > database.sql

2. Backup single database from remote server
$ mysqldump -u user_name -h host_name -d database_name –p > database.sql

3. If you are running more than one MYSQL instance on different ports them you need to mention the port to backup database
$ mysqldump -u user_name -h host_name -P port -d database_name –p > database.sql

4. Backup a table from a database
$ mysqldump -u username database_name table_name > table.sql

5. Backup all databases on the server as ROOT
$ mysqldump -u root --all-databases > all_databases.sql

Restore MySQL Database using “mysql” command
If you are moving the database from one server to another server or remotely then use below commands

1. Restore a database on the same server
$mysql -u user_name database_name –p < database.sql
Or
$ mysql -e "source /tmp/database.sql " database_name

2. Restore database from remote server
$ mysql -u user_name -h host_name database_name –p < database.sql

3. If you are running more than one MYSQL instance on different ports them you need to mention the port of Instance to restore
$ mysql -u user_name -h host_name -P port database_name –p < database.sql

4. Restore a table from a database
$ mysql -u username database_name < table.sql

5. Restore all databases from the dump as ROOT
$ mysql -u root < all_databases.sql

Restore database using mysqlimportThe database should exist in order to import dump using mysqlimport.
$ mysqlimport -u user_name -p password database_name /tmp/dumpfile.sql

Write MySQL table data to a file
Use below syntax to write table data/contents to a file
mysql> SELECT * FROM . ORDER BY DESC LIMIT 0,99000 INTO OUTFILE '/tmp/table_name.txt'

If you don’t have access to write from MySQL to a file then you will receive below error.
ERROR 1045 (28000): Access denied for user 'xxxxxxx'@'%' (using password: YES)

If you dont have permission the use below alternate option
$ echo "SELECT * FROM database_name.table_name" mysql -u -h --port= --password= > /tmp/table_name.txt

Rename Mysql Database
Either you can backup and restore the database into new database or follow below steps
mysql>create database new_database;
$ mysqldump -u user_name -d old_database -p | mysql -u user_name -d new_database -p
mysql> drop database old_database

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

15 comments:

  1. Online calculator to convert money and currency using today exchange rate.
    if you want more just look here "convert money"

    ReplyDelete
  2. The islands of Ksamil in Albania are definitely one of the most beautiful islands you will ever visit. You will surely want to come back again.

    if you want more just look here "Albanian Islands"

    ReplyDelete
  3. The best Maui wedding photographers since 2004. Voted number one in wedding photography on Maui by magazines around the world. Our award-winning Maui wedding photography team have hundreds of five-star reviews and the most affordable wedding photography packages on Maui

    if you want more just look here "Maui wedding photographers"

    ReplyDelete
  4. Nuestra agencia de Detectives en Sevilla ofrece servicios de investigación privada en cualquier ámbito. Tenemos un despacho de detectives en Sevilla, que además pertenece a una de las mayores agencias de detectives del país, pertenecemos a Grupo Arga Detectives.

    if you want more just look here "Detectives sevilla"

    ReplyDelete
  5. The Arga Detective Agency has been for many years,
    developing research as detectives in the field of the community of Madrid, nationally and internationally. Our experience and recognition guarantee us as the most prestigious Detective Agency in Spain

    if you want more just look here "mystery shopping españa"

    ReplyDelete
  6. Free app, no login no card required, auto-refresh news and updates about the marketing industry.

    if you want more just look here "Free app"

    ReplyDelete
  7. We provide top reviews of the latest shower filters ,Water filters and top shower heads. See Our website for top quality shower information .

    if you want more just look here "Best Shower filters"

    ReplyDelete
  8. All K managers are composed of 20 managers who work and are high-quality educated managers. It is a place full of uniqueness differentiated from other companies. From the thoughts and perspectives of your customers, K's business trip massage
    Because it is an operational strategy, the satisfaction of managers is high, so if you believe and call me, thank you

    if you want more just look here "Ulsan Branch Massage"

    ReplyDelete
  9. As experienced language service providers we are one of the established translation agencies in Germany. For over 30 years our customers have been benefiting from our professionalism and our high standards of quality in the fields of translation and interpreting.

    if you want more just look here "language"

    ReplyDelete
  10. Purchase, sale of land, hotels, villas, resorts, businesses, shops, restaurants, bars, residential, commercial and other properties on Koh Rong Island and Koh Rong Samloem Island (aka Sihanoukville, Westport Islands) , Rental, Cambodia Islands). We serve clients, developers, tenants, landlords and investors from all over the world. We provide the following services: purchase, sale and lease of any real estate, real estate and investment consulting, operating documents and arrangements to purchase, lease, protect, lease real estate agreements, due diligence report on real estate you want to buy or lease, property rights application guide, Lawyer support, business and company registration guides, arranging an architecture and design team that suits your needs.

    if you want more just look here "kohrongre"

    ReplyDelete
  11. Looker is a data-discovery app that provides innovative data exploration functionalities for businesses both large and small. With it, they can access a web-based interface where they can easily get real-time insights on their operations via data analytics.

    if you want more just look here "Looker training"

    ReplyDelete
  12. While its unrefined leaves can be chomped, foamed, or immersed with tea, dried to smoke, or its concentrates, dried, changed into a powder and crushed into holders or tablets to get many amazing

    if you want more just look here "Kratom"

    ReplyDelete

  13. I really appreciate this great post that you have provided us. I guarantee this will benefit most people and myself. thank you very much!
    health magazine
    the
    twitch dark mode
    How many centimeters in a meter

    ReplyDelete