MARKS TUTORIAL

If you are responsible for maintaining database objects structure and want to know what objects are added or dropped from previous application version to the next version, then you have come to the right place. You may need to know the schema difference for any number of purposes like for Application upgrade, Product documentation etc. In this tutorial we will mention how to compare schema of two databases in MySQL. You will need below mentioned tools to do this. MySQL built-in utility: mysqldump Text Editor supporting Regular Expression: Notepad++ File comparison tool: Chose as per your preference, I used Beyond Compare 1. Take the backup of schema for the databases For the purpose of this tutorial we will assume that we need to compare DB schema for two databases appVer1 and appVer2. Take the backup as follows: (Provide password when prompted and press Enter key.) For appVer1 : mysqldump -h your_db_host -u your_db_user -p --single-transaction --skip-comment...

MySQL tables, including MyISAM and InnoDB, two of the most common types, can experience fragmentation. In order to understand fragmentation, let us consider How MySQL stores data for a table on disk in order to make it persistent. The data is stored on the disk in the form of files that are divided into fixed size extents, pages or blocks. The number of files that will be created to store data related to a single table, depends on the table's storage engine as well as the setting of related system variables. Hence one part of table's fragmentation is dependent on its storage engine, if it stores data in a table by using the techniques as shown below, that leads to fragmentation. Another part is external to MySQL and is platform/OS dependent. Table fragmentation means gaps in the information stored in the table, which comes into picture due to the following reasons: Logical fragmentation Some index types stores the record in a particular order of  their index key colu...

MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0 ...

Sometimes you need to analyze slow queries from a MySQL database server that has been deployed as a Amazon RDS service. The slow queries are going into the table slow_log inside the mysql system database. This will happen when you have enabled logging of slow queries and log_output system variable is set to TABLE . We will use pt-query-digest - a percona tool used to analyze MySQL queries from logs, process-list and tcpdump. To export the slow queries, run the following command from a machine/jump-box that is allowed to connect to the target database and has mysql client installed. The data is exported into a MySQL slow-log format, which pt-query-digest can analyze: mysql -h your_amazon_rds_endpoint -u your_master_user_name -p -D mysql -s -r -e " SELECT CONCAT( '# Time: ', DATE_FORMAT(start_time, '%y%m%d %H%i%s'), '\n', '# User@Host: ', user_host, '\n', '# Query_time: ', TIME_TO_SEC(query_time),  '  Lock_time: ...

The MySQL server spawns a separate background thread for each enabled protocol that it supports on the target platform. This single thread per protocol further manages each client connection and helps to deliver a separate thread for each connection request. There can be different supported protocol depending upon the platform like Unix socket on Unix like platform, Named Pipe and Shared Memory on Windows platform. The MySQL server listens for TCP/IP connections (remote connections) from the client on all platforms. The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. To specify an address, use the --bind-address=addr option at server startup, where addr is an IPv4 or IPv6 address or a host name. If addr is a host name, the server resolves the name to an IP address and binds to that address. If a host name resolves to multiple IP addresses, th...

SQL (Structured Query Language) is a mechanism that is used to manage as well as store and retrieve information from a RDBMS (Relational Data Base Management Service). One way to retrieve information from the database is to use the SELECT query. To be able to execute a select query inside a database server, it needs to be correct both syntactically and semantically. Syntax and Semantics A language is a set of valid sentences. What makes a sentence valid? You can break validity down into two things: syntax and semantics . The term syntax refers to grammatical structure whereas the term semantics refers to the meaning of the vocabulary symbols arranged with that structure. Grammatical (syntactically valid) does not imply sensible (semantically valid), however. For example, the grammatical sentence "cows flow supremely" is grammatically ok (subject verb adverb) in English, but makes no sense. Similarly, in a programming language, your grammar (syntax rules) may al...

Sometimes you find yourself in a situation where your Network connection is not consistent or you have to execute a time consuming task but do not have all the time to be available in front of your desk, like your shift is going to be over. In situation of these kinds you can use the screen command. With screen, you can reconnect to that server and pick up where you left off with your last command. Screen is a full-screen window manager that multiplexes a physical terminal between several processes. When you call the screen command, it creates a single window where you can work as normal. You can open as many screens as you need, switch between them, detach them, list them, and reconnect to them. Introduction Screen is a console application that allows you to use multiple terminal sessions within one window. The program operates within a shell session and acts as a container and manager for other terminal sessions, similar to how a window manager manages windows. Check ve...

Sometimes you restore a SQL dump in a MySQL database server and bump into the following error: ERROR 1227 (42000) at line NNNNN: Access denied; you need (at least one of) the SUPER privilege(s) for this operation This happens when the SQL dump file contains DEFINER clause (to create specific database objects) with user account (a combination of user name and host name) other than yours and your account does not have the SUPER privilege granted. Let us assume that: SQL dump file is: mysql_dump_file.sql Account that is specified with DEFINER clause in SQL dump file is: `pdxnnxxp1`@`%` Your MySQL User Account is: `your_user_name`@`your_host_name` To resolve the issue you can: Remove the DEFINER clause sed -i 's/DEFINER=`pdxnnxxp1`@`%`//g' mysql_dump_file.sql Remove the DEFINER account with your account sed -i 's/`pdxnnxxp1`@`%`/` your_user_name `@` your_host_name `/g' mysql_dump_file.sql Replace your_user_name and your_host_name with actual value...