MySQL

Prepared statement with IN Query clause

 

MySQL’s SHOW CREATE TABLE for Oracle

Ever wondered, how to see create table code in Oracle?
as available in MySQL “show create table”

Here is how to do it

SQL> set pages 0
SQL> set long 999999
SQL> select dbms_metadata.get_ddl(‘TABLE’, ‘EMPLOYEES’, ‘EMP’) from dual;

MySQL: dropping all tables of a database with ease

So there is a need to delete all tables from database?

Possibilities:

  1. Drop database and recreate, what if you don’t want to drop database, could be many reasons.
  2. Fire drop table [tbl_name ...] command on MySQL prompt but then who is going to type all tables names.

Solution:
Run following command from command line

mysqldump -u [USERNAME] -p[PASSWORD] –add-drop-table –no-data [DATABASE] | grep ^DROP | mysql -u [USERNAME] -p[PASSWORD] -D [DATABASE]

Explained:

  1. First one takes dump of all tables of database without any data along with drop table command.
  2. Second part fetches lines starting with Drop from the output of first command.
  3. Third command executes queries fetched by second command which are only drop table commands.

MySQL: Load timezones info in MySQL Server

Run following command on command line:

mysql_tzinfo_to_sql  /usr/share/zoneinfo | mysql -u root -p mysql

and to change timezone of the mysql server, add following line in the my.cnf file (/etc/my.cnf)

default_time_zone=Asia/Kolkata

or run following query on MySQL prompt (this will be valid for current session only)

SET time_zone = ‘Asia/Kolkata’;

Common MySQL Data Types

Text Data Types
CHAR( ) fixed from 0 to 255 characters long
VARCHAR( ) variable from 0 to 255 characters long
TINYTEXT maximum length of 255 characters
TEXT maximum length of 65535 characters
BLOB maximum length of 65535 characters
MEDIUMTEXT maximum length of 16777215 characters
MEDIUMBLOB maximum length of 16777215 characters
LONGTEXT maximum length of 4294967295 characters
LONGBLOB maximum length of 4294967295 characters

Numeric Data Types
TINYINT( ) -128 to 127 SIGNED or 0 to 255 UNSIGNED
SMALLINT( ) -32768 to 32767 SIGNED or 0 to 65535 UNSIGNED
MEDIUMINT( ) -8388608 to 8388607 SIGNED or 0 to 16777215 UNSIGNED
INT( ) -2147483648 to 2147483647 SIGNED or 0 to 4294967295 UNSIGNED
BIGINT( ) -9223372036854775808 to 9223372036854775807 SIGNED or 0 to 18446744073709551615 UNSIGNED
FLOAT small number with a floating decimal point
DOUBLE( , ) large number with a floating decimal point
DECIMAL( , ) DOUBLE stored as a string , allowing for a fixed decimal point

Date Data Types
DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MM:SS
TIMESTAMP YYYYMMDDHHMMSS
TIME HH:MM:SS

Copy MySQL database from one server to another remote server

Usually you run mysqldump to create database copy:
$ mysqldump -u user -p db-name > db-name.out

Copy db-name.out file using sftp/ssh to remote MySQL server:
$ scp db-name.out user@remote.box.com:/backup

Restore database at remote server (login over ssh):
$ mysql -u user -p db-name <>
How do I copy a MySQL database from one computer/server to another?

Short answer is you can copy database from one computer/server to another using ssh or MySQL client.

You can run all the above 3 commands in one pass using mysqldump and mysql commands (insecure method, use only if you are using VPN or trust your network):
$ mysqldump db-name | mysql -h remote.box.com db-name

Use ssh if you don’t have direct access to remote mysql server (secure method):
$ mysqldump db-name | ssh user@remote.box.com mysql db-name

You can just copy table called foo to remote database (and remote mysql server remote.box.com) called bar using same syntax:
$ mysqldump db-name foo | ssh user@remote.box.com mysql bar

This will not just save your time but you can impress your friend too ;) .
Almost all commands can be run using pipes under UNIX/Linux oses.

MySQL connections can be made securely to a remote server via SSL. Just thought I would mention that as it is an additional option to consider. http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html

 Scroll to top