Backup and Restore MySQL - so easy that caveman can do it...

There are various ways to perform backup and restore the MySQL database. However, I always found command line to be very easy and powerful.

Command: mysqldump

Usage:

1. Backup only one database

mysqldump -h [hostname] -u [username] -p [password] [databasename] > [backupMySQLfile.sql]

[hostname] - the hostname or IP address or loopback address i.e 127.0.0.1

[username] - the database username

[password] - the password for your database

[databasename] - the name of your database

[backupMySQLfile.sql]- the file to which the backup should be written.

The dump file will contain the SQL statements required to create the table and populate the table. Let's see one example of backing up database 'AsteriskCDR' with the username 'root' and password 'abcd1234' to a file 'DailyBackupCDR.sql'

#mysqldump -h 127.0.0.1 -u root -pabcd1234 AsteriskCDR > DailyBackupCDR.sql

[Caution: there shouldn't be the space between -p option and password]
[Note: Your system may not require to use '-h 127.0.0.1' ]


If you want to create the backup that will automatically overwrite the existing database while restoring the database without having to delete the older database manually first, you need to use the option --add-drop-table.

#mysql --add-drop-table -u root -pabcd1234 AsteriskCDR > DailyBackupCDR.sql

2. Backup with compression

Dump file are usually huge, so it's better idea to apply compression while dumping the file.

Using GZIP compression
#mysql --add-drop-table -u root -pabcd1234 AsteriskCDR gzip > DailyBackupCDR.sql.gz

or

Using ZIP compression
#mysql --add-drop-table -u root -pabcd1234 AsteriskCDR gzip > DailyBackupCDR.sql.zip

or

Using BZIP2 compression
#mysql --add-drop-table -u root -pabcd1234 AsteriskCDR bzip2 > DailyBackupCDR.sql.bz2

3. Restore the database

The backup dump file can be easily restored using the mysql command. In fact, it rebuilds the database from the scratch.

mysql -u [username] -p [password] [database_to_restore] < [backupfile]

#msyql -u root -pabcd1234 AsteriskCDR < DailyBackupCDR. sql


Compressed dump files need to be uncompressed before restore.

#unzip DailyBackupCDR.sql.zip [for zipped file]
#gunzip DailyBackupCDR.sql.gz [for gzipped file]
#bunzip2 DailyBackupCDR.sql.bzip2 [for compression using bzip2]

unzip is the counterpart of zip
gunzip is the counterpart of gzip
bunzip2 is the counterpart of bzip2

Once the file is uncompressed, it will result in the regular dump file which can be restored using msyql command
#msyql -u root -pabcd1234 AsteriskCDR < DailyBackupCDR. sql



4. Backup entire database

#mysqldump --all-databases > alldatabases.sql

5. Backup only the database structure

#mysqldump --no-data --all-databases > alldatabases.sql


For more : http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/

MySQL Basics

This Tutorial will help u gain confidence in MySQL. Just make sure that u will follow each steps. If you find any problem, just leave comment,I will reply asap.

Packages required to be installed

DEVAst02:~ # rpm -qa|grep mysql

mysql-shared-5.0.26-12.18

mysql-client-5.0.26-12.18

mysql-devel-5.0.26-12.18

mysql-5.0.26-12.18

perl-DBD-mysql-3.0002-15.2

Default config file /etc/my.conf

Post Installation:

1> Create Password for root user

Admin user id: root


Default password: blank

The first task is to assign a password:

[prompt]$ mysqladmin -u root password 'new-password'

Note: the following SQL commands will also work:

mysql> USE mysql; mysql> UPDATE user SET Password=PASSWORD('new-password') WHERE user='root'; mysql> FLUSH PRIVILEGES;

2> Delete blank user (Security Issue)

MySql has blank user for test purpose. It should be removed.

DEVAst02:~ # mysql -h localhost -u -p

Running above command gives access to mysql. So, it needs to be deleted

mysql> show databases;

mysql> use mysql;

mysql> SELECT User,Password,Host from user;

mysql> DELETE FROM user WHERE User=' ';

mysql> FLUSH PRIVILEGES;

Some Fundamental Operations:

1> Create Database, create table, add entries, update entries, delete entires; drop table and drop database;

DEVAst02:~ # mysql -h localhost -u root -p

mysql> show databases;

mysql> create database asterisk;

mysql> show databases;

mysql> use asterisk;

mysql> show tables;

mysql> create table Records (Number char(20),Name char(20), email varchar(40));

mysql> show tables;

mysql> describe Records;

mysql> INSERT INTO Records(Number,Name,email) VALUES('25962','Dev Shah','dev.shah@something.edu');

mysql> SELECT * FROM Records;

mysql> UPDATE Records SET Name='Deven Shah' WHERE Number='25962';

mysql> SELECT * FROM Records;

mysql> DELETE FROM Records WHERE Number='25962';

mysql> drop table Records;

mysql> drop database asterisk;

Security:

Security and database access is controlled by the GRANT tables. Access to connect to the database and access to process the transaction (table and column access, etc.) are both required. Privileges are searched in the following order:

user table

db and host table

tables_priv

columns_priv

DEVAst02:~ # mysql -h localhost -u root -p

mysql> show databases;

mysql> use mysql;

mysql> show tables;

Mysql> SELECT * FROM User;

Use the user table to grant connection privileges to database by a user (host, user name and password). Grant database and table access for transaction access. i.e. grant "SELECT", "UPDATE", "CREATE", "DELETE", "ALTER" etc. permission for database, table, field (columns) or database server access.

Add a user and grant the access:

mysql> CREATE DATABASE asteriskdb;

mysql> USE mysql;

mysql> SHOW TABLES;

+-----------------+

| Tables_in_mysql |

+-----------------+

| columns_priv |

| db |

| func |

| host |

| tables_priv |

| user |

+-----------------+

mysql> INSERT INTO user (Host, User, Password, Select_priv) VALUES ('', 'asteriskuser', password('redhat'), 'Y');

mysql> FLUSH PRIVILEGES; - Required each time one makes a change to the GRANT table

mysql> GRANT ALL PRIVILEGES ON asteriskdb.* TO asteriskuser;

mysql> FLUSH PRIVILEGES; - Required each time one makes a change to the GRANT table

mysql> quit

Now try:

DEVAst02:~ # mysql -h localhost -u asteriskuser -predhat

mysql> use mysql;

ERROR 1044 (42000): Access denied for user 'asteriskuser'@'%' to database 'mysql'

mysql> use asteriskdb;

Database changed

Since you are accessing mysql as asteriskuser,you are granted only to access asteriskdb.

If you need to grant access to all databases, you should Grant

mysql> GRANT ALL PRIVILEGES ON *.* TO asteriskuser;

mysql> FLUSH PRIVILEGES;

Assignment: Create a user 'dbreader' with password 'redhat' that has read-only permission on database 'phones'.

Step1: Create user

msyql>CREATE USER 'dbreader'@'%' IDENTIFIED BY 'redhat';

Step2: Grant rights for read-only

mysql>GRANT SELECT ON phones.* TO 'dbsreader'@'%';

[Note: Granting SELECT command is enough for READ operation]

Now you should be able to access 'phones' database from any machine using user 'dbreader'.

For More: http://www.yolinux.com/TUTORIALS/LinuxTutorialMySQL.html