Complete uninstallation of Mysql Server on centos

Step 1: Check list the mysql rpm which is installed on server


rpm -qa | grep mysql
or
yum list installed | grep mysql


Step 2 : Removing all mysql-related packages (with “yum command”)


yum remove mysql-client mysql-server mysql-common mysql-devel


Step 3: Delete the databases folder


rm -rf /var/lib/mysql/
rm -rf /etc/my.cnf


Congratulation’s! You have successfully removes MySQL.

Read More

Finding and preventing ddos attack on Linux Server

A distributed denial-of-service (DDoS) attack occurs when multiple systems flood the bandwidth or resources of a targeted system, usually one or more web servers. Such an attack is often the result of multiple compromised systems flooding the targeted system with traffic.A denial-of-service (DoS) attack is an attempt to make a machine or network resource unavailable to its intended users, such as to temporarily or indefinitely interrupt or suspend services of a host connected to the Internet.

check ddos attack

netstat -anp|grep 'tcp\|udp' | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort –n

This command will show you the list of IP's which have logged in is maximum number of connections to your server.

ddos becomes more complex as attackers  use fewer connections with more number of attacking IP's.In such cases, you should get less number of connections even when your server is under ddos.One important thing that you should check is the number of active connections that your server currently has by executing below command:

netstat -n | grep :80 |wc –l

netstat -n  | grep :80 | grep SYN |wc –l

Result of active connections from the first command will vary but if it shows connections more than 500, then you will be definitely having problems. If the result after you fire second command is 100 or above then you are having problems with sync attack.
This command is useful to find out how many active SYNC_REC are occurring on the server. The number should be pretty low, preferably less than 5. On DoS attack incidents or mail bombs, the number can jump to pretty high. However, the value always depends on system, so a high value may be average on another server.

netstat -n -p|grep SYN_REC | wc -l

netstat -n -p | grep SYN_REC | sort -u

Once you find the ip attacking your server, you can easily block it.Fire the following command to block that ip or any other specific ip:

check ip blocked or not by using the following command:

route -n |grep IPaddress

You can also block a IP with iptables on the server by using the following command.

iptables -A INPUT 1 -s IPADRESS -j DROP/REJECT

service iptables restart

service iptables save

After firing the above command, KILL all httpd connection and than restart httpd service by
using following command:

killall -KILL httpd

service httpd startssl

Read More

Tracing the execution of processes using strace

Strace is quite simply a tool that traces the execution of system calls. It can trace the execution of a binary from start to end, and output a line of text with the name of the system call, the arguments and the return value for every system call over the lifetime of the process.

   1.Find out which config files a program reads on startup

strace php 2>&1 | grep php.ini

open("/usr/local/bin/php.ini", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/usr/local/lib/php.ini", O_RDONLY) = 4

lstat64("/usr/local/lib/php.ini", {st_mode=S_IFLNK|0777, st_size=27, ...}) = 0

readlink("/usr/local/lib/php.ini", "/usr/local/Zend/etc/php.ini", 4096) = 27

lstat64("/usr/local/Zend/etc/php.ini", {st_mode=S_IFREG|0664, st_size=40971, ...}) = 0

2.What is that process doing RIGHT NOW?

Ever had a process suddenly eating lots of CPU? Or had a process seem to be hanging? Then you find the pid, and do this:

root@dev:~# strace -p 15427

Process 15427 attached - interrupt to quit

futex(0x402f4900, FUTEX_WAIT, 2, NULL

Process 15427 detached

3.MySQL with strace

Start MySQL Daemon

service mysqld start

Connect strace to mysqld

ps -elf | grep mysqld (and find out that mysqld is PID 1234)

sudo strace -p 1234 -f

Read More

SELinux mechanism in linux environment.

Security-Enhanced Linux (SELinux) is a mandatory access control (MAC) security mechanism implemented in the kernel. SELinux was first introduced in CentOS 4 and significantly enhanced in later CentOS releases. 

SELinux follows the model of least-privilege more closely. By default under a strict enforcing setting, everything is denied and then a series of exceptions policies are written that give each element of the system  only the access required to function. If a service, program or user subsequently tries to access or modify a file or resource not necessary for it to function, then access is denied and the action is logged.

SELinux has three basic modes of operation discussed below:

• Enforcing: The default mode which will enable and enforce the SELinux security policy on the system, denying access and logging actions
• Permissive: In Permissive mode, SELinux is enabled but will not enforce the security policy, only warn and log actions. Permissive mode is useful for troubleshooting SELinux issues
• Disabled: SELinux is turned off

Some useful commands for understanding SELinux;

1.check SELinux status

  sestatus

2.Check port is open

   netstat -tlpn | grep 3306

3.check port is blocked by SELinux

   semanage port -l | grep 3306

4.Open port in SELinux

semanage port -a -t http_port_t -p tcp 3306

5.Drop port in SELinux

semanage port -d -t http_port_t -p tcp 3306

6.check SELinux status

cat /var/log/audit/audit.log|grep -i "mysql"

Hope this bring some basic understanding about SELinux !

Read More

Simple bash script's for managing mysql

1. Monitoring replication health

#!/bin/bash

sql_thread_running=$(mysql -e "show slave status\G" | awk -F":" '/Slave_SQL_Running/ { print $2 }' | tr -d ' ')
io_thread_running=$(mysql -e "show slave status\G" | awk -F":" '/Slave_IO_Running/ { print $2 }' | tr -d ' ')
seconds_late=$(mysql -e "show slave status\G" | awk -F":" '/Seconds_Behind_Master/ { print $2 }' | tr -d ' ')
seconds_late=$(($seconds_late+0))

if [ "$sql_thread_running" = "No" ] || [ "$io_thread_running" = "No" ] || [ $seconds_late -gt 3600 ]||[ "$io_thread_running" = "Connecting" ]; then

mysql -e "show slave status\G"|mail -s "Slave Database Issue" xxx.gmail.com

fi

2.Monitoring mysql status

 #!/bin/bash

 /usr/bin/mysqladmin  ping| grep 'mysqld is alive' > /dev/null 2>&1

 if [ $? != 0 ]

 then

     echo 'Mysql Service is Stop'|mail -s "Slave Mysql service down" xxx@gmail.com 

  else

     echo 'Mysql Service Running'

 fi

3.Backup mysql data

#!/bin/bash

databases=`mysql -e "SHOW DATABASES;" | grep -E "(test1|test2)"`

for db in $databases; do

  mysqldump  --databases $db | gzip > /home/mysql/data/$db`date +%Y%m%d`.sql.gz;

  mysqldump  --no-data --databases $db | gzip > /home/mysql/schema/$db`date +%Y%m%d`.sql.gz;

  done

find /home/mysql/data  -iname "*.gz" -mtime +7 -exec rm {} \;

find /home/mysql/schema  -iname "*.gz" -mtime +7 -exec rm {} \;

echo 'Dump completed Successfully'|mail -s "Dump completed" xxx@gmail.com

This script will hold last 7 days backup.Change it according to your need.

Hope this article helps !

Read More

Running multiple mysql instance on single server(Centos&Red Hat)

 Follow Below Steps:-

Step (1): Create a new MySQL data directory.

mkdir -p /var/lib/mysql2

Step 2:  Give permission and ownership .Now we are taking ownership and permission reference from original /var/lib/mysql data directory

chmod --reference /var/lib/mysql /var/lib/mysql2
chown --reference /var/lib/mysql /var/lib/mysql2

Step 3: Now create a new my1.cnf file and paste the below given contents. We will run the new mysql instance in port no. 3307.

Copy the my.cnf file and make it blank

cp -p /etc/my.cnf /etc/my1.cnf

Now edit with vi editor.

vi /etc/my1.cnf

Edit in [mysqld] and [mysqld_safe] section.

[mysqld]
datadir=/var/lib/mysql2
socket=/var/lib/mysql/mysql2.sock
port=3307

[mysqld_safe]
log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid

Step 4: Now install the database in the new mysql data directory

mysql_install_db --user=mysql --datadir=/var/lib/mysql2

Step 5: Now the new instance is ready.Now start and stop with the below commands

To start new mysql instance,use below given command

mysqld_safe --defaults-file=/etc/my1.cnf &

Now check the port no. 3337 is listening or not

netstat -tanp |grep 3307

To connect new mysql instance

Syntax: mysql -u username -p -h 127.0.0.1 -P port-no

Now set root password 

mysql > use mysql;
mysql > update user set password=PASSWORD("password") where User='root';
mysql > flush privileges;
mysql > exit

To stop new mysql instance use below given command

mysqladmin -S /var/lib/mysql/mysql2.sock shutdown -p

Read More

Mysql Installation using rpm package

    1.Download the required rpm filess

• MySQL-client-community-5.1.25-0.rhel5.i386.rpm
• MySQL-server-community-5.1.25-0.rhel5.i386.rpm
• MySQL-devel-community-5.1.25-0.rhel5.i386.rpm

   2. Remove the existing default MySQL that came with the Linux distro

  [local-host]# rpm -qa | grep -i mysql

    mysql-5.0.22-2.1.0.1

   mysqlclient10-3.23.58-4.RHEL4.1

  [local-host]# rpm -e mysql --nodeps

   warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

   [local-host]# rpm -e mysqlclient10

 3. Install the downloaded MySQL package

Install the MySQL Server and Client packages as shown below.

[local-host]# rpm -ivh MySQL-server-community-5.1.25-0.rhel5.i386.rpm MySQL-client-community-5.1.25-0.rhel5.i386.rpm

Preparing...                ########################################### [100%]

1:MySQL-client-community ########################################### [ 50%]

2:MySQL-server-community ########################################### [100%]

This will also display the following output and start the MySQL daemon automatically.

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h medica2 password 'new-password'

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available at http://www.mysql.com/

Support MySQL by buying support/licenses from http://shop.mysql.com/

Starting MySQL.[  OK  ]

Giving mysqld 2 seconds to start

Install the “Header and Libraries” that are part of the MySQL-devel packages.

[local-host]# rpm -ivh MySQL-devel-community-5.1.25-0.rhel5.i386.rpm

Preparing...                ########################################### [100%]

1:MySQL-devel-community  ########################################### [100%]

Note: When I was compiling PHP with MySQL option from source on the Linux system, it failed with the following error. Installing the MySQL-devel-community package fixed this problem in installing PHP from source.

configure: error: Cannot find MySQL header files under yes.

Note that the MySQL client library is not bundled anymore!

4.  Perform post-install security activities on MySQL.

[local-user]# /usr/bin/mysqladmin -u root password 'password'

The best option is to run the mysql_secure_installation script that will take care of all the typical security related items on the MySQL as shown below. 

• Change the root password
• Remove the anonymous user
• Disallow root login from remote machines
• Remove the default sample test database

[local-host]# /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL

SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current

password for the root user.  If you've just installed MySQL, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL

root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] Y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

By default, a MySQL installation has an anonymous user, allowing anyone

to log into MySQL without having to have a user account created for

them.  This is intended only for testing, and to make the installation

go a bit smoother.  You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] Y

... Success!

Normally, root should only be allowed to connect from 'localhost'.  This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y

... Success!

By default, MySQL comes with a database named 'test' that anyone can

access.  This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n] Y

- Dropping test database...

... Success!

- Removing privileges on test database...

... Success!

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] Y

... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL

installation should now be secure.

Thanks for using MySQL!

5.  Verify the MySQL installation:

You can check the MySQL installed version by performing mysql -V as shown below:

[local-host]# mysql -V

mysql  Ver 14.14 Distrib 5.1.25-rc, for redhat-linux-gnu (i686) using readline 5.1

Connect to the MySQL database using the root user and make sure the connection is successfull.

[local-host]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Follows the steps below to stop and start MySQL

[local-host]# service mysql status

MySQL running (12588)                                      [  OK  ]

[local-host]# service mysql stop

Shutting down MySQL.                                       [  OK  ]

[local-host]# service mysql start

Starting MySQL.                                                  [  OK  ]

Read More

Check and Repair Mysql Tables

 Check a Specific Table in a Database

If your application throws an error message saying that a specific table is corrupted, execute the mysqlcheck command to check it.

The following example checks t1 table in test database.

# mysqlcheck -c test t1 -u root -p

Enter password:

test.t1    OK

Kindly Note, that myisamchk command  works similar to the mysqlcheck command. The advantage of mysqlcheck command is that it can be executed when the mysql daemon is running. So, using mysqlcheck command you can check and repair corrupted table while the database is still running.

Check All Tables in a Database

To check all the tables in a particular database follow this

The following example checks all the tables in the test database.

# mysqlcheck -c test  -u root -p

Enter password:

test.t1                            OK

test.t2                            Ok

Check All Tables and All Databases

To check all the tables and all the databases use the “–all-databases” along with -c option as shown below.

# mysqlcheck -c  -u root -p --all-databases

Enter password:

test.employee                              OK

live.JBPM_ACTION                           OK

dev.emp

error    : Table emp required. Please do "REPAIR TABLE `emp`" or dump/reload to fix it!

Check all tables of few databases

# mysqlcheck -c  -u root -p --databases test live

Enter password:

test.employee                              OK

live.student                               OK

 Analyze Tables using Mysqlcheck

# mysqlcheck -a test t1 -u root -p

Enter password:

test.t1   Table is already up to date

Internally mysqlcheck command uses “ANALYZE TABLE” command. While mysqlcheck is executing the analyze command the table is locked and available for other process only in the read mode

Optimize Tables using Mysqlcheck

# mysqlcheck -o test t1 -u root -p

Enter password:

test.t1         OK

Internally mysqlcheck command uses “OPTIMIZE TABLE” command. When you delete lot of rows from a table, optimizing it helps to get the unused space and defragment the data file. This might improve performance on huge tables that has gone through several updates.

Repair Tables using Mysqlcheck

# mysqlcheck -r test t1 -u root -p

Enter password:

test.t1        OK

Internally mysqlcheck command uses “REPAIR TABLE” command. This will repair and fix a corrupted MyISAM and archive tables.

Combine Check, Optimize, and Repair Tables

Instead of checking and repairing separately. You can combine check, optimize and repair functionality together using “–auto-repair” as shown below.

# mysqlcheck -u root -p --auto-repair -c -o test

You an also check, optimize and repair all the tables across all your databases using the following command.

# mysqlcheck -u root -p --auto-repair -c -o --all-databases

 add the –debug-info as shown below for getting more information

# mysqlcheck --debug-info -u root -p --auto-repair -c -o test t1

Enter password:

test.t1  Table is already up to date

 User time 0.00, System time 0.00

Maximum resident set size 0, Integral resident set size 0

Non-physical pagefaults 344, Physical pagefaults 0, Swaps 0

Read More