MySQL : Dumping Data in SQL Format with mysqldump By default, mysqldump writes information as SQL statements to the standard output. You can save the output in a file: shell> mysqldump [arguments] > file_name To dump all databases, invoke mysqldump with the –all-databases option: shell> mysqldump –all-databases > dump.sql To dump only specific databases, name them on the command line … Read more
RANGE Partitioning in MySQL What is Partitioning? Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables, but still get treated as a single table by the SQL layer. When partitioning, it’s a good idea to find a natural partition key. You want to ensure … Read more
MySQL engine type for a specific database SHOW GLOBAL VARIABLES LIKE ‘storage_engine’ [xyz-ihs snippet=”Discuss”]
mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation. mysqld, is MySQL server daemon program which runs quietly in background on MySQL Database Server. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the … Read more
To rename a table in MySQL you just need to run a command named RENAME TABLE, the syntax is very easy to use. RENAME TABLE table1 TO table2; The RENAME TABLE command will rename the table atomically, which means your table will be locked during the command. You can also rename more than one … Read more
LAMP is a combination of operating system and open-source software stack. The acronym LAMP is derived from first letters of Linux, Apache HTTP Server, MySQL database, and PHP/Perl/Python. Installation of Apache Apache is an open-source multi-platform web server. It provides a full range of web server features including CGI, SSL and virtual domains. To install … Read more
Very often we need to extract last 30 days, 7 days data from mysql DB. Here we will show easy way to get the data of given interval. SELECT DATE_FORMAT(create_date, ‘%m/%d/%Y’) FROM mytable WHERE create_date BETWEEN CURDATE() – INTERVAL 30 DAY AND CURDATE() Also note that CURDATE() returns only the DATE portion of the date, so … Read more
MySQL interval values are used mainly for date and time calculations. INTERVAL {value of unit} {types of interval} INTERVAL is a MySQL keyword and this is case sensitive. This should be used in CAPITAL Later Only {value of unit} is a expression or value like 1,2,3,4 or any expression which result in value. {type of … Read more
Very often we need to extract last 1 month, 2 months data from mysql DB. Here we will show easy way to get the data of given interval. SELECT * from testtable WHERE startdate BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND DATE_SUB(NOW(), INTERVAL 2 MONTH) The above query will return records whose order date is between one and … Read more
The myisamchk utility gets information about your database tables or checks, repairs, or optimizes them. myisamchk works with MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes). The use of myisamchk with partitioned tables is not supported. How to run this Utility – myisamchk myisamchk [options] tbl_name The options specify … Read more