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/

20 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. We remind you that the consultation is always free. A qualified professional will gladly assist you and guide you throughout the process. Benefit from our great prices of computer experts in Madrid at the best quality.

    if you want more just look here "perito informático forense"

    ReplyDelete
  7. 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
  8. Gclub Casino Online in the name that we know very well. Is a legal casino that is based in Cambodia That has standards and is allowed to open a casino In order to enable the service for general customers There are many styles that are ready to impress every customer. Inside, the place is luxurious and clean. With the convenience along with the comfort that Gclub name is the number 1 casino That is known for its services And because all the managers and employees are Thai, so there is no problem in communication

    if you want more just look here "GCLUB"

    ReplyDelete
  9. 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
  10. 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
  11. A leading online gambling website, UFA affiliated with another website that is equivalent to a gambling website. That serves like a relative And is by your side 24 hours a day, making you feel safe and free from worry about fraud And also providing a full range of online gambling services, including lotteries, boxing, football betting, playing fish shooting games Play online baccarat Play online casino slots games which have high standards.

    if you want more just look here "UFABET"

    ReplyDelete
  12. 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
  13. CBD is a short form of Cannabidiol. CBD introduced by William Brooke O’Shaughnessy in 1939 to American pharmacies. And it got popularity as a remedy for health problems. In recent times it gains popularity for its numerous health benefits. This oil is extracted from a tree called the cannabis Sativa plant.

    if you want more just look here "https://bestcbdoilbusiness.wordpress.com/"

    ReplyDelete
  14. 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
  15. Nothing ruins your garden or yard like weeds, those uninvited guests who rob your plants of space and nutrients. So kill the weeds that are the worst, but without harmful chemicals that you can take care of too.

    if you want more just look here "Unkraut"

    ReplyDelete
  16. 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
  17. 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