Skip to main content

Posts

MySQL backup using shell script mysqldump utility

#!/bin/bash # Error handling function error() { echo -e "[ `date` ] $(tput setaf 1)$@$(tput sgr0)" exit $2 } ### Set Bins Path ### RM=/bin/rm GZIP=/bin/gzip GREP=/bin/grep MKDIR=/bin/mkdir MYSQL=/usr/bin/mysql MYSQLDUMP=/usr/bin/mysqldump MYSQLADMIN=/usr/bin/mysqladmin ### Enable Log = 1 ### LOGS=1 ### Default Time Format ### TIME_FORMAT='%d%b%Y%H%M%S' ### Setup Dump And Log Directory ### MYSQLDUMPPATH=/var/www/mysqldump MYSQLDUMPLOG=/var/log/mysqldump.log EXTRA_PARAMS=$1 ##################################### ### ----[ No Editing below ]------### ##################################### [ -f ~/.my.cnf ] || error "Error: ~/.my.cnf not found" ### Make Sure Bins Exists ### verify_bins(){ [ ! -x $GZIP ] && error "File $GZIP does not exists. Make sure correct path is set in $0." [ ! -x $MYSQL ] && error "File $MYSQL does not exists. Make sure correct path is set in $0." [ ! -x $MYSQLDUMP ] && error "Fil...
Post a Comment
Read more

MySQL Creating a SSH tunnel using PuTTY

Prerequisites: • MySQL is installed. • MySQL is configured to listen on localhost (127.0.0.1). This is enabled by default. How to Access MySQL Remotely by Creating an SSH Tunnel with PuTTYPermalink This section will show you how to create an SSH tunnel to MySQL on Windows, using the PuTTY tool. Setting Up the Tunnel First, you need to establish a basic connection to your node: 1. Download PuTTY. 2. Save PuTTY to your desktop. 3. Double-click the PuTTY file to begin - no need to install. You will see the following window: 1. Enter the hostname or IP address of your Linode in the Host Name (or IP address) field. 2. In the left-hand menu, go to Connection -> SSH -> Tunnels. 3. In the Source port field, enter 3306. 4. In the Destination field, enter 127.0.0.1:3306. See the final configuration below: 1. Click Open to start the SSH session. 2. If you haven’t logged in to this system with PuTTY before, you will receive a warning simila...
Post a Comment
Read more

MySQL thread error code of 1032- Translate binlog completely to avoid error

Could not execute Update_rows event on table db.table; Can't find record in 'table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's   master log mysql-bin.262297, end_log_pos 1983208 As of my knowledge the causing stop of slave SQL thread error code of 1032, due to lack of sync between Master - Slave. In our case, slave got an Update event form master, but that particular record was not available in the slave. (Of course, that was deleted on Master n Slave separately) You would get this error only if the binlog_format is set to ROW_BASED or MIXED mode. So, now check that particular binlog at that position where replication stopped with this error. When you convert the binlog with mysqlbinlog command, you may see some junk characters   where you were expecting some DMLs which caused the error(In our case its an Update statement). So you can use below command to translate binlog completely: mysqlbinlog --base64-output=DECO...
Post a Comment
Read more

MySQL Database Storage Engines

Storage Engine: A storage engine is a software that is used by a database management system to create, read, and update data from a database. Most DBMS use APIs (Application Programming Interface) to enable interactions of users with the storage engines. There are two types of storage engines; Transactional and Non-transactional storage engines. Transactional Databases Transactional databases mean that the write operations on these databases are able to be rolled back if they do not complete. These operations are known as transactions. Most of the modern databases are transactional databases.  Non-Transactional Databases The impact of no Rollback/Commit is felt. In order to perform rollback operation the user will need to do it manually with codes. By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk. If you are using a transaction-safe...
Post a Comment
Read more

MySQL Cluster Set Up

1) Management node 192.168.0.1 -Management (MGM) node MySQL-ndb-tools-5.1.11-0. glibc23.rpm MySQL-ndb-management-5.1.11-0. glibc23.rpm 192.168.0.2 - MySQL server (SQL) node  MySQL-server-5.1.11-0.glibc23. rpm MySQL-shared-5.1.11-0.glibc23. rpm MySQL-client-5.1.11-0.glibc23. rpm 192.168.0.3 - Data (NDBD) node "A"  MySQL-ndb-tools-5.1.11-0. glibc23.rpm MySQL-client-5.1.11-0.glibc23. rpm MySQL-ndb-storage-5.1.11-0. glibc23.rpm MySQL-server-5.1.11-0.glibc23. rpm ( Optional ) 192.168.0.4 - Data (NDBD) node "B"  MySQL-ndb-tools-5.1.11-0. glibc23.rpm MySQL-client-5.1.11-0.glibc23. rpm MySQL-ndb-storage-5.1.11-0. glibc23.rpm MySQL-server-5.1.11-0.glibc23. rpm( Optional ) Step by Step configuration: Each data node or SQL node requires a my.cnf file that provides two pieces of information: - A connect string to find the MGM node - A line which says the MySQL server on this host to run in NDB mode. The  my.cnf  file for data node (ndb) For each data node and S...
Post a Comment
Read more

Monitoring MySQL performance

If monitoring MySQL performance by analyzing its status values Performance Monitoring of MySQL Server: Following are the command which we can use for session or server level performance for MySQL server. SHOW GLOBAL STATUS – shows global server status SHOW LOCAL STATUS  - This is used for session level server status Have to check following values to know how server works. Aborted_clients:   Usually no need to worry for this because many programs/application don’t close connection properly. Aborted_connects:  This means authentication failure, network timeout or any other error. If the value is high than its possible that someone tries to break the password or something. Com_XXX:  This can be used to check server load that which statements are running most on server. §   Temporary Objects Created_tmp_tables:  Temporary tables can often be avoided by query optimization. Created_tmp_disk_tables:  Not enough memory is allo...
Post a Comment
Read more