mysql backup and recovery

HOW TO RESET MYSQL PASSWORD?

1. Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.

2. Connect to the mysqld server with this command:

shell> mysql

3. Issue the following statements in the mysql client. Replace the password with the password that you want to use.

mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')

-> WHERE User='root';

mysql> FLUSH PRIVILEGES;

CREATE DATABASE in MySQL

mysqladmin -h <IP of MySQL server> -u<username> -p<password> create <databasename>

e.g. mysqladmin -h localhost -uroot -padmin create asterisk

DROP DATABASE in MySQL

mysqladmin -h <IP of MySQL server> -u<username> -p<password> drop <databasename>

e.g. mysqladmin -h localhost -uroot -padmin drop asterisk

MySQL BACKUP

MYSQLHOTCOPY command is used to backup the mysql databases files.

MYSQLHOTCOPY --USER=<USERNAME> --PASSWORD=<PASSWORD> <DATABASENAME> <DESTINATION DIRECTORY>

e.g. mysqlhotcopy --user=root --password=dialnet mysql /home/sandip

Use the command if you want to transfer mysql data to another server with SSH

e.g. mysqlhotcopy --method=SCP --user=<username> --password=<password> <database name> ssh-username@ssh-server:{path}

Using MySQLDUMP for backup and restore.

• BACKUP

mysqldump -u<username> --password=<password> <databasename> <tablename> <filename with path>

mysqldump -uroot --password=admin asterisk test_table > /home/test.sql

• RESTORE

mysql -h<database> -u<user> -p<password> <databasename> < <filename with path>

mysqldump -hlocalhost -uroot -padmin asterisk < /home/test.sql

How to Recover a Drop table or Drop Database to Backup the Database.

i) mysqlhotcopy --user=root --password=dialnet asterisk /home/sandip

Now Drop the Database

ii) mysqladmin -hlocalhost -uroot -padmin Drop asterisk

Now Restore the backups under /var/lib/mysql/asterisk(Default Path) using OS command shell

iii) cp -r /home/sandip/asterisk /var/lib/mysql

Now Change the permission of the Directory to mysql

chown -R mysql /var/lib/mysql/asterisk

chgrp mysql /var/lib/mysql/asterisk

Its all done now use the tables.

LOAD DATA QUERY TO LOAD DATA IN MySQL Server from .txt/.csv file

LOAD DATA LOCAL INFILE '/home/importfile.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' LINES TERMINCATED BY '\n';

SPOOL COMMAND in MySQL -> tee/notee

tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.

E.g. tee /home/testdata.txt

Select * from user;

notee

MYISAMCHK

What does myisamchk do?

It compressed the MyISAM tables, which reduces their disk usage.

e.g. myisamchk /home/sandip/asterisk/*.MYI (for Indexes)

e.g. myisamchk /home/sandip/asterisk/*.MYD (for datas)

e.g. myisamchk /home/sandip/asterisk/* (for both indexes and datas)

MYSQLHOTCOPY VS MYSQLDUMP

The mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce.It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to take a backup of the database or single tables. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables.

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores.