mysql dump of data only – no table and database schema

Mates, I want to dump the data from the tables only rather than its definition and data.

The problem with mysqldump is it checks if database/table exist  or not. if the table exist it drops the table create the blank table and then enters the insert into statements. I don’t want to use this.

goggled and found

dump the database with options –no-create-db and –no-create-info
Advertisements

Mysql Database Backup script

Create a file called database_backup.sh and also an empty directory called mysql_backup.

The database_backup.sh script should have the following info :

#!/bin/sh 
date=`date '+%m-%d-%y'`
PATH="~/mysql_backup/database_name.$date"
mysqldump -u database_username -pdatabase_password database_name > $PATH


Run the script that performs the backup job.

Using Cron  :

Set this script up to run every night, etc. as a cron job. It will save an sql dump of your database every night in the mysql_backup.
e.g

* 23 * * * your_userid /path/to/backup/script

Backing Up MySQL Database

MySQL database backup can be accomplished in two ways:

a) Copying the raw mysql database files &
b) Exporting tables to text files



Copying the Mysql database Files :

MySQL uses the same table format on different platforms, so it’s possible to copy MySQL table and index files from one platform and use them on another without any difficulties (assuming, of course, that you’re using the same version of MySQL on both platforms).

Exporting tables to text files.

The MySQLDump is handy utility that can be used to quickly backup the MySQL Database to the text files. To use the MySQLDump utility it is required to logon to the System running the MySQL Databse. You can use Telnet to remotely logon to the system if you don’t have the physical access to the machine.
The syntax for the command is as follows.

mysqldump -u [Username] -p [password] [databasename] > [backupfile.sql]

[username] – this is your database username
[password]- this is the password for your database
[databasename] – the name of your database
[backupfile.sql] – the filename for your database backup

Let’s discuss the example of backing up MySQL Database named “accounts” into text file accounts.sql. Here are the scenarios of taking the backup assuming that both user name and password of the database is “admin”.

a) Taking the full backup of all the tables including the data.

Use the following command to accomplish this:
mysqldump -u admin -p admin accounts > accounts.sql

b) Taking the backup of table structures only.

Use the following command to accomplish this:
mysqldump -u admin -p admin –no-data accounts > accounts.sql

c) Taking the backup data only.

Use the following command to accomplish this:
mysqldump -u admin -p admin –no-create-info accounts > accounts.sql

Restoring MySQL Database

Restoring the MySQL is very easy job. You can use the following to command to restore the accounts database from accounts.sql backup file.

mysql – u admin -p admin accounts < accounts.sql

In this tutorial you learned how to take the backup of your MySQL Database and restore the same in the event of some database crash or on some other machine.

How to check how much free space we have in Mysql database

fire the query
show table status like ‘org’;

The last column Comments gives the size of the database.

If you need to have a shell script to check InnoDB_free space of Mysql database here is the code

Query="show table status like 'org';"
#freespace=${echo "$Query" | mysql -t --host=tiber4 mvc | /bin/awk '/InnoDB free:/ {print $38}' }
freespace=$(echo "$Query" | mysql -t --host=$host $db | /bin/awk '/InnoDB free:/ {print $38}' )

echo $host has $freespace kB free

TRIMMIG IN MYSQL

Some time back I got stuck at the point in Mysql task ,I wanted to trip a part of already existing column in table and then insert into other column.

Reference is the column that contains the Reference number e.g. ‘090216-000016’
RDate is the column is which stores the date of the reference,  and first part of Reference contains the date. So I wanted to trip the Reference number to get the data for RDate column.

Here is how I did it,

mysql> select Reference from RNS  where Reference=’090216-000016′;
+—————+
| Reference     |
+—————+
| 090216-000016 |
+—————+
1 row in set (0.00 sec)

mysql> select LEFT (Reference,6) from RNS  where Reference=’090216-000016′;
+——————–+
| LEFT (Reference,6) |
+——————–+
| 090216             |
+——————–+
1 row in set (0.01 sec)

mysql> update RNS set RDATE=LEFT(Reference,6) where Reference=’090205-000154′;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select Reference,RDATE from RNS  where Reference=’090205-000154′;
+—————+————+
| Reference     | RDATE      |
+—————+————+
| 090205-000154 | 2009-02-05 |
+—————+————+

Hope it will be useful.

Adding user to Mysql

mysql> GRANT ALL ON cacti.* TO cactiuser@localhost IDENTIFIED BY ‘somepassword’;
mysql> flush privileges;

or adding a new user to MySQL you just need to add a new entry to user table in database mysql. Below is an example of adding new user phpcake with SELECT, INSERT and UPDATE privileges with the password  mypass  the SQL query is :
mysql> use mysql;
Database changed

mysql> INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES (‘localhost’, ‘phpcake’, PASSWORD(‘mypass’), ‘Y’, ‘Y’, ‘Y’);
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = ‘phpcake’;
+———–+———+——————+
| host      | user    | password         |
+———–+———+——————+
| localhost | phpcake | 6f8c114b58f2ce9e |
+———–+———+——————+
1 row in set (0.00 sec)

When adding a new user remember to encrypt the new password using PASSWORD() function provided by MySQL. As you can see in the above example the password mypass is encrypted to 6f8c114b58f2ce9e.

Notice the the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don’t use it then you won’t be able to connect to mysql using the new user account (at least until the server is reloaded).

You can also specify other privileges to a new user by setting the values of these columns in user table to ‘Y’ when executing the INSERT query :

    * Select_priv
    * Insert_priv
    * Update_priv
    * Delete_priv
    * Create_priv
    * Drop_priv
    * Reload_priv
    * Shutdown_priv
    * Process_priv
    * File_priv
    * Grant_priv
    * References_priv
    * Index_priv
    * Alter_priv

I think you can guess what those privileges serve by reading it’s name

I was just working on a new Bugzilla installation, and noticed their grant command example, and thought I’d share it here as another example:

mysql> GRANT SELECT, INSERT,
       UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,
       CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.*
       TO bugs@localhost IDENTIFIED BY ‘$db_pass’;

mysql> FLUSH PRIVILEGES;

They also show this syntax for changing the MySQL root user password, which is a little different than the syntax I normally use, so I thought I’d share that here as well:

mysql> UPDATE user
       SET password = password(‘new_password’)
       WHERE user = ‘root’;

mysql> FLUSH PRIVILEGES;