Update MySQL database for centos web panel

Here are simple steps to update MySQL database server 5.1 to MySQL database server 5.6 for Centos web panel:

Login to your server with root username and password from SSH:

1.  Create full database backup (In case mysql server break we have backup to restore).

mysqldump --events --all-databases | gzip > /home/alldatabases.sql.gz

2. Install Remi repo

cd /etc/yum.repos.d
wget http://rpms.famillecollet.com/enterprise/remi.repo

Read More

The mod_security plugin could not connect to the database. Please verify that MySQL is running. Error: Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) – Solution

If you’re getting following error while accessing mod_sec from WHM, here is the quick solution for this.

The Mod Security plugin could not connect to the database. Please verify that MySQL is running. Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Login to your WHM with root access.
Go to Service Manager from left menu.
See if MySQL Service is enabled and being monitored.
Check the boxes if unchecked.

Now access Mod Security from WHM. It should be working fine now.

Feedback are appreciated 🙂

Cron job for backing up MYSQL database from SSH

Backing up MYSQL databases became very necessary especially if you own a shopping cart or e commerce website.

Hackers are always trying to gain access to your database and sometimes they gain access and the most action they perform is to modify databases and change the information stored. In that case you cannot restore your database if you do not have backup and if you have backups mostly they are daily or weekly backups taken by cPanel or by any other control panel.

There is no feature in any hosting panel which allows you to take backup every minute or by custom time span.

The following script allows you to configure an time interval for taking backup of mysql database and it does not replace any backup stored already. This means you can restore any backup available accordingly.

Features are discussed in the comment# lines of the scripts:

Just copy and paste this script in any file like: backup.sh and upload it in any folder giving root access to it.

 

#!/bin/bash -x
#backup file location, it is the location where backup will be stored, change it to your own
backupDir=/home/USERNAME/public_html/backups/
myDate=$(date '+%Y-%m-%d'-'%I-%M-%S-%p')

#this is the backup file name, generates different names of files to avoid rewriting
dbName=backupSQL-$myDate

# This is temporary directory, change it accordingly if you have this somewhere else.
cd /tmp

# MYSQL command to take backup with valid username/password
mysqldump -u root -pPASSWORD_HERE DATABASE_NAME_HERE > $dbName

# it compresses the file into tar to save the disk space.
tar -cvf $dbName.tar $dbName

# moves the file to backup directory from temporary directory
mv $dbName.tar $backupDir

# delete backups older than 2 days, change -mtime +2 to any digit like +7 for files older than 7 days.
find $backupDir -mtime +2 | xargs rm -rf

<br/ >
Now come to a cron job, Follow the following steps to set a cron job for auto backups.
<br/ >
Login to SSH with root access.

Execute following command:

$ crontab -e

A text editor will be opened just write the following cron job line at the end of file:

*/5 * * * * /FILE-PATH/backup.sh

In above code */5 is the time interval for auto backups. Auto backup script will run every 5 minutes. You can change it accordingly.
Just save the cron job and you’re done.

Feedback are welcome 🙂

Best mysql configurations

In this post i will describe what are the best configurations for mysql server, you can edit your my.cnf file to reflect the changes. To do so follow the below steps:

  • Login to your server via putty or shell/terminal with root access.
  • Execute following commands:
# cd /etc/mysql
# nano my.cnf
  • Now you are in text editor with opened file my.cnf here you can copy and paste the below code into it and then save it.
#skip-innodb
[mysqld]
#socket=/path/to/mysql.sock
#datadir=/var/lib/mysql
skip-locking
local-infile=0

# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
max_connections=60
interactive_timeout=100
wait_timeout=100
connect_timeout=100
table_cache=512
thread_cache=32

[mysqldump]
quick
max_allowed_packet = 2M
[mysql]
no-auto-rehash

[isamchk]
key_buffer=50M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 4M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 4M
  • Now press CTRL+O and the press [Enter] settings will be saved and then execute following below command:
/etc/init.d/mysql restart
OR
service mysql restart
OR
restart mysql
  • You can find your my.cnf file in mysql directory it might be different from the location that i mentioned above.
You are welcome to ask any question regarding above tutorial…