10 tut - SHARING TIP AND TUT: tháng 3 2014

Thứ Năm, 27 tháng 3, 2014

Calculating Maximum Connections for MySQL Server

Backup your my.cnf file

The my.cnf file is the MySQL configuration file that needs to be modified to optimize the performance of MySQL database servers. Make sure you always backup a copy of this very important configuration before changing it.

Determine the advisable value for max_connections

How high should we set max_connections? Well, it depends on how much memory (RAM) do you have on the server.
You might want to use the formula to come up with the value we are looking for.

max_connections = (Available RAM - Global buffers) / Thread buffers

But before that, we need to know the values of the variables on our right side - available RAM, global buffers, and thread buffers.

Determine the Available RAM

To determine the Available RAM, issue the following command.

free -tb

Here's the result of the command in my case:
The number in red is the available memory measured in bytes.

Determine the Global and Thread Buffers

In a mysql console, issue the following statement:

SHOW VARIABLES LIKE '%buffer%';

The following are considered global buffers:

key_buffer_size
innodb_buffer_pool
innodb_log_buffer
innodb_additional_mem_pool
net_buffer_size

The following are considered thread specific buffers:

sort_buffer_size
myisam_sort_buffer_size
read_buffer_size
join_buffer_size
read_rnd_buffer_size

Delete IPTables Rules By Line Number

First off list all IPTable's rules by chain number:

iptables -vnL --line-numbers

Then delete the line you want:

iptables -D INPUT 22

This will delete chain line number 22

Change MySQL Table Prefix Tools

Description

This script makes it possible for you to change table prefix for MySql databases.

Features

Add prefix to unprefixed tables
Rename/change prefix
Remove prefix

This is a quick modification of another script found at the below address.
http://www.nilpo.com/2009/01/web-development/mysql-table-prefix-changer-tool/.

Download
Change MySQL Table Prefix Tools

Find and Delete Duplicate Posts (Title) in WordPress

The first and fastest (in other words: simplest) thing to do is to search for duplicate entries in the database. For example with the following SQL query.

Find Duplicate Posts

SELECT a.ID, a.post_title, a.post_type, a.post_status
FROM wp_posts AS a
   INNER JOIN (
      SELECT post_title, MIN( id ) AS min_id
      FROM wp_posts
      WHERE post_type = 'post'
      AND post_status = 'publish'
      GROUP BY post_title
      HAVING COUNT( * ) > 1
   ) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish'

The result shows us all post that we have in our database, which share their title at least with one other post. To make the result faster and easier readable, our SQL statement only shows us the ID, post_title, post_type and post_status columns from the wp_posts table. Here are our fictional results.

ID	post_title	post_type	post_status
12109	Aus dem Leben eines Taugenichts	post	publish
23	Aus dem Leben eines Taugenichts	post	publish
12123	Narziß und Goldmund	post	publish
121	Narziß und Goldmund	post	publish
2123	Narziß und Goldmund	post	publish
145	Jakob der Lügner	post	publish
12	Jakob der Lügner	post	publish

(Yes, these are all German language literature classics.)

You could now double check by performing a search for “Narziß und Goldmund” in the WordPress admin UI posts list screen. There you should get the same post duplicates as you’ve found by using the previous SQL query. As you’ll soon encounter, the reason why we choose to use the SQL admin UI over the WordPress admin UI is that we can search for all duplicate titles at once instead of running one search query for each title. And that is a huge time saver.

Delete Duplicate Posts

To proceed further with our task of cleaning out duplicates in our database, we only need to change two minor things: First we change SELECT to DELETE to actually remove the entries. And second, we switch from selecting only a few columns, that we needed for a quick glance at the duplicates, to all columns: *. Take a look at our new SQL query.

DELETE a.*
FROM wp_posts AS a
   INNER JOIN (
      SELECT post_title, MIN( id ) AS min_id
      FROM wp_posts
      WHERE post_type = 'post'
      AND post_status = 'publish'
      GROUP BY post_title
      HAVING COUNT( * ) > 1
   ) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish'

After you run this query from your database UI (that would mostly be phpMyAdmin or Adminer) or a console, you’re mostly done. Keep in mind, we recommend that you back up your database before running such a query.

Backup and Restore a single table using mysqldump

Backup a single table from a database

mysqldump -u -p database_one table_name1 table_name2 table_name3 > /var/www/backups/table_name.sql

Restore the table into another database

mysql -u -p database_two < /var/www/backups/table_name.sql

You can dump a query as csv like this:

SELECT * from myTable
INTO OUTFILE '/tmp/querydump.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

How to get $1/yr Exabytes Ebiz Power Hosting - Save 99%

Host your Business Email and Website and enjoy 99.9% OFF !

With $1 you get 1 YEAR Hosting

Using Promotional Code: POWER99

Visit Exabytes Now

http://angel.exabytes.com/idevaffiliate.php?id=3301850

When you order, maybe you can get error :
"We are placing an automated call to your phone number now. This is part of our fraud checking measures. You will be given a 4 digit security code which you need to enter below to complete your order."
Send an email to sales@exabytes.com with order number or your domain.You will be receiving an order comfirmation. Just feel free to contact Exabytes
I've ordered and first look everything seem to be good except Virtual Memory Usage Limit: 512 MB

Hosting package: eBiz Power-5

How to Create/Restore Backup In Kloxo

Backup/Restore is a core part of Kloxo. You can take backup of your entire hosting and you can trivially restore it exactly on another server. In a cluster setup, you can take backup in one cluster configuration, and when restoring, you can tell Kloxo to re-arrange the resources on different servers.
Backup/Restore is also an extremely important aspect of web-hosting, and delivers peace of mind, and in fact, one of the main advantages of having a control Panel on your server.

It is recommended, though not necessary, that both the source and destination Kloxo be the latest version, but Kloxo should be transparently able to handle older versions.To take backup of your entire system:

 /script/backup --class=client --name=admin

Kloxo is 100% hierarchical. So if you take the backup of a top level client, automatically every resource under him is included, including his clients, domains, ssl certificates, or whatever resources he has. In the above case, you are taking the backup for admin, which actually includes your entire hosting.

To restore:

#/script/restore --restore --accounts=all <backup-file-path>

To list the contents of a backup file.

#/script/restore --list <backup-file-path>

To restore only certain domains/clients

#cd /usr/local/lxlabs/kloxo/httpdocs/

#lphp.exe ../bin/common/restore.php --restore --accounts=domain:domain.com,client:clientname <backup-file-path>

If you have taken the backup under a specific client, and want to restore under the same client, you can provide the client's class and name via --class and --name respectively. You have to first make sure that this client exists in the system. That is, you have to first add a client with the clientname, and then you can restore the backup under him.

#cd /usr/local/lxlabs/kloxo/httpdocs/

#lphp.exe ../bin/common/restore.php --class=client --name=clientname --restore <backup-file-path>

[Step by step]Create a MySQL Database, Username, Password, and Privileges from the command line

Create a MySQL Database, Username, Password, and Privileges from the command line

Step 1: Login to MySQL ( you will need an account )

user@server:~$ mysql -u mysql_user -p
Enter password:

Step 2: Create the Database

mysql > create database db_name;

Step 3: Verify that it’s there

mysql > show databases;

Step 4: Create the User

mysql > create user db_user;

Step 5: Grant privileges while assigning the password

mysql > grant all on db_name.* to 'db_user'@'localhost' identified by 'db_password';

*Note: The localhost field usually doesn’t have to be edited, but you can set it to the specific address.

The above example grants all privileges, obviously. But you will likely want to limit privileges under many circumstances. These parameters include select, insert, and delete.
Choose all that apply and separate by comma, thusly:

mysql > grant select, insert, delete on db_name.* to 'db_user'@'localhost' identified by 'db_password';

Fix Cpanel Email Disk Space Usage show incorrectly

By default , In Cpanel Email Disk space usage is recalculated every four hours.
To update Email Disk space usage, try running /scripts/generate_maildirsize on the affected account. The syntax would be:

# /scripts/generate_maildirsize --confirm --allaccounts --verbose $USER

Replace $USER with the actual cPanel account username (the cPanel user, not the domain).

Fix Kloxo awstats 500 internal server error

If you get a 500 error when accessing awstats, then the fix is to reset permissions, NOT by using the permission fix tool within the admin console, but you will need root access to fix this.
So if your host uses kloxo and you are getting 500 errors then chances are you wont be able to fix it from a client account.
Run command below:

    #cd /home/kloxo/httpd/awstats/wwwroot/
    #chmod 755 cgi-bin -R
    #chmod 755 icon -R
Once this has been changed then awstats will work again.

Update PHP 5.3 for Kloxo

You can follow the steps below to update your php version to 5.3 for Kloxo.

Install repository

Creat file /etc/yum.repos.d/ius.repo

[ius]
name=IUS Community Packages for Enterprise Linux 5 - $basearch
baseurl=http://dl.iuscommunity.org/pub/ius/stable/Redhat/5/$basearch
enabled=1
gpgcheck=0

Creat file /etc/yum.repos.d/epel.repo

[epel]
name=Extra Packages for Enterprise Linux 5 - $basearch
baseurl=http://download.fedoraproject.org/pub/epel/5/$basearch
enabled=1
gpgcheck=0
exclude=nginx*

Install yum replace

yum install yum-plugin-replace

Replace php with php53u

yum replace php --replace-with=php53u

* Other way: remove php and install php53u

yum -y remove php php-common
yum -y install php53u php53u-cli php53u-devel php53u-gd php53u-imap php53u-mbstring php53u-mysql php53u-pdo php53u-pear php53u-xml

Create a php.ini template for php5.3

# cp /usr/local/lxlabs/kloxo/file/phpini/php.ini.template-5.2 /usr/local/lxlabs/kloxo/file/phpini/php.ini.template-5.3
# chown -R lxlabs:lxlabs /usr/local/lxlabs/kloxo/file/phpini/php.ini.template-5.3

Verify the new PHP version.

# php -v

How to change Mysql data directory location?

Mysql is most famous open source RDBMS (Relational database management system) software used by millions of websites.
Most of website with very large Mysql database experience storage problem because /var partition is to small which holds Mysql data files and folder by default.
If you are experiencing such a problem then you have to move your Mysql data directory to other linux partition (eg: /home).
To change your directory without affecting Mysql configuration. Follow these steps.

Note: before changing Mysql data directory please backup all your database

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

First stop Mysql service

/etc/init.d/mysqld stop

create a folder 'mysql' in /home partition

copy whole Mysql data directory to 'mysql' in preserve mode

cp –pr /var/lib/mysql/ /home/mysql

-p means preserve the specified attributes (default: mode, ownership, timestamps)

-r means copy each directory sub-directory and files

then create a symbolic link from new data directory to old data directory

ln -s /home/mysql/ /var/lib/mysql/

Now start Mysql service

/etc/init.d/mysqld start

If its working properly then delete each file and folder from old data directory

cd /var/lib/mysql rm –rf *

Mass convert a project to UTF-8 using Notepad++

Lately, I had to convert the encoding of a multimodule maven project from our default Cp-1252 encoding to UTF-8.
The most dificult task however was converting all our source files from Cp-1252 to UTF-8 and preferably on Windows :) .
I’ve been looking into applications that would auto-convert everything for me, but none of them actually converted to content, resulting in garbage files. I almost started converting all the files by hand using Notepad++ when I discovered this process could be automated !
First of all you’ll need to install the Python Script plugin using the Notepad++ Plugin Manager. Then, after installing and restarting, you have to create a new script with the following code:

import os;

import sys;

filePathSrc="C:\\Temp\\UTF8"

for root, dirs, files in os.walk(filePathSrc):

for fn in files:

  if fn[-4:] != '.jar' and fn[-5:] != '.ear' and fn[-4:] != '.gif' and fn[-4:] != '.jpg' and fn[-5:] != '.jpeg' and fn[-4:] != '.xls' and fn[-4:] != '.GIF' and fn[-4:] != '.JPG' and fn[-5:] != '.JPEG' and fn[-4:] != '.XLS' and fn[-4:] != '.PNG' and fn[-4:] != '.png' and fn[-4:] != '.cab' and fn[-4:] != '.CAB' and fn[-4:] != '.ico':

    notepad.open(root + "\\" + fn)

    console.write(root + "\\" + fn + "\r\n")

    notepad.runMenuCommand("Encoding", "Convert to UTF-8 without BOM")

    notepad.save()

    notepad.close()

If you have problems running the script, then first open the console (Plugins > Python Script > Show Console). Chances are that the indents got messed up (for those who don’t know Python, it doesn’t use curly brackets to identify a code block, it uses correct indentation instead).

P/S: You first have to create the script via “Plugins > New Script”, then it’ll be available in the menu:

Source: http://pw999.wordpress.com/2013/08/19/mass-convert-a-project-to-utf-8-using-notepad/