MySQL

Configure MySQL Master-Master replication

Posted on by Neelesh Gurjar

Server1: 10.0.0.1Server2: 10.0.0.2Both have same version of Mysql server installed Steps to configure MySQL Master-Master replication: On Server1:1. Enable server listens on all Network Interfaces, also enable binary logs and other mysql config:You should have below entries in /etc/my.cnf auto_increment_increment = 2 auto_increment_offset = 1 bind-address=0.0.0.0 log-bin=mysql-bin-log binlog-do-db=<dbName> expire_logs_days=2 max_binlog_size=100M innodb_flush_log_at_trx_commit=1 sync_binlog=1 server-id=12. Restart mysql…

MySQL incremental backup on S3 Storage bucket

Posted on by Neelesh Gurjar

Steps to take incremental backup of MySQL database and upload it on S3 bucket: 1. Enable binary loging in mysql for our database. Added below entries in /etc/my.cnf under [mysqld] log-bin=mysql-bin-log binlog-do-db=Database_name expire_logs_days=2 max_binlog_size=100M 2. Restart mysql server systemctl restart mysqld 3. Installed s3cmd & configured it yum install s3cmd s3cmd –configure Enter AWS access_key…

Script to Kill MySQL Sleeping Processes

Posted on by Neelesh Gurjar

Create Script /bin/mysql_kill_sleepProc.sh #!/bin/bash echo ” ” echo ‘###########################################’ date mysqladmin -u root –password=<mysqlPassword> processlist|grep Sleep for i in `mysqladmin -u root –password=<MysqlPASSWORD> processlist|grep Sleep|awk ‘{print $2}’`; do mysqladmin -u root –password=<MysqlPASSWORD> kill $i; done > /dev/null 2>&1 wc=`echo $?` if test $wc == 0 then echo “Mysql sleeping queries killed succesfully” else echo “Command…

Tuning MySQL

Posted on by Neelesh Gurjar

 1. To know current default values for mysqld give               $ mysqld –verbose –help 2. While Tuning MySQL, 2 values are most important      a. key_buffer_size  –> key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.The maximum permissible setting for…

Compiling Apache, mysql and PHP

Posted on by Neelesh Gurjar

Compiling Apache for fully dynamic web server 1. Download Apache sourcecode from http://apache.org 2. Extract it and enter into the extracted directory 3. ./configure –prefix=/usr/local/apache2 –enable-mods-shared=all make ; make install 4. Dowload PHP from php.net. Then extract it and enter the extracted directory 5. ./configure –with-apxs2=/usr/local/apache2/bin/apxs –with-mysql –prefix=/usr/local/apache2/php –with-config-file-path=/usr/local/apache2/php –enable-force-cgi-redirect –with-zlib –with-gettext –with-gdbm –enable-mbstring=all make…