Balu's Technical Blog: MySQL

Showing posts with label MySQL. Show all posts

Sunday, March 30, 2014

MYSQL: Restoring DB from .sql backup

To restore a mysql database from Unix shell
mysql -u my_user_name -p db_name < my_sql_file.sql

mysql: dropping all tables

I just chatted with our server support and they gave me a simple script to drop all of the tables without removing database permissions which we can use when refreshing the UAT and STAGE databases:

mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | \

gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname

MySQL Database backup (mysqldump) from command line

As the database grows in size the convenient GUI tools became too slow, at some point it is easier to log in to the database server and do it from the command line...

mysqldump --user MY_USER_NAME -p DB_NAME> /var/www/UNIQUE_NAME_AND_DATE.sql

Enter password: MY_PASSWORD

#compress, you can also use gzip

tar cvzf UNIQUE_NAME_AND_DATE.sql.tar UNIQUE_NAME_AND_DATE.sql

# delete sql

rm /var/www/UNIQUE_NAME_AND_DATE.sql

#download:

http://www.DOMAIN_NAME.com/UNIQUE_NAME_AND_DATE.sql.tar

# after transfer delete

rm /var/www/UNIQUE_NAME_AND_DATE.sql.tar

Changing mysql password

mysqladmin -u root -p'oldpassword' password newpass

Execute SQL script (MySQL dump) from command line

1) Find you mysql installation directory:

root: ~ $ cd /usr/local/mysql/bin

2) Start mysql shell

root: ~ $ ./mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 238

Server version: 5.5.9 MySQL Community Server (GPL)

...

3) Select database you want to execute script agaist

mysql> use my_database_name

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

4) Execute SQL script
in the next line notice "slash dot space"

mysql> \. /Users/uki/Documents/script_name.sql

Select longest records from the database

To find the longest record in your table you can use this query:

SELECT max(length(column_name)) from table_name

This can help you in optimizing the size of your tables.

SELECT column_name FROM table_nameWHERE length(column_name) =
( SELECT max(length(( SELECT max(length(column_name)) from table_name );

Result is a list of records with the longest length

Linux: MySQL case sensitivity

As a matter of principle when writing SQL for mix-OS systems:

USE LOWER CASE ONLY

Example proper case SQL statement:

CREATE TABLE `address` (

   `id` bigint(20) NOT NULL auto_increment,

   `attn_care_of` varchar(255) default NULL,

     PRIMARY KEY (`id`)

   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This is especially important when developing on Windows systems where case does not matter. It is possible to configure MySQL to be case sensitive/insensitive, but that is seldom done.

mysql: dropping all tables

I just chatted with our server support and they gave me a simple script to drop all of the tables without removing database permissions which we can use when refreshing the UAT and STAGE databases:

mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | \

gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname

Thursday, January 12, 2012

MySQL: MYISAM vs INNODB

MYISAM:

does not support transactions rollback and row level locking.

INNODB:

supports above features as well as ACID (Atomicity, consistency, isolation, and durability).

To convert from MyISAM to InnoDB use script:

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE table_name1 TYPE = innodb;
ALTER TABLE table_name2 TYPE = innodb;
SET FOREIGN_KEY_CHECKS = 1;