backup all databases
mysqldump –user=root –password –all-databases > backup__all.sql
mysqldump –user=root –password –all-databases > backup_1082016_all.sql
backup a specific database
mysqldump –user=root –password > backup__.sql
backup multiple databases
mysqldump –user=root –password , > backup_.sql
backup a table from a database
mysqldump –user=root –password > backup_.sql
backup some specific data
mysqldump –user=root –password –where “last_name=’VALLE’ order by first_name > backup_.sql
dumping from one database to another
mysqldump –databases | mhysql -h
select into outfile / load data infile ## dump of the accounts table
select * into outfile ‘/tmp/accounts.txt’ from accounts;
load the dump
load data infile ‘/tmp/accounts.txt’ into table accounts;
mk-parallel-dump, mk-parallel-restore
backup a database
mk-parallel-dump –basdir=/backups
restore a database
mk-parallel-restore /backups
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
- Backup single database from remote server
$ mysqldump -u user_name -h host_name -d database_name ñp > database.sql - 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 - Backup a table from a database
$ mysqldump -u username database_name table_name > table.sql - 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
- 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 - Restore database from remote server
$ mysql -u user_name -h host_name database_name ñp < database.sql - 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 - Restore a table from a database
$ mysql -u username database_name < table.sql - Restore all databases from the dump as ROOT
$ mysql -u root < all_databases.sql
Restore database using mysqlimport The 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
