Using Mysqltuner

Download/Installation
Simple

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
# chmod +x mysqltuner.pl
# ./mysqltuner.pl

You can download the entire repository by using ‘git clone’ followed by the cloning URL above. The simplest and shortest method is:

wget mysqltuner.pl
perl mysqltuner.pl

Change the permissions you can execute it without calling perl directly.

[root@austin ~]# chmod +x mysqltuner.pl

[root@austin ~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4M (Tables: 230)
[--] Data in InnoDB tables: 12M (Tables: 419)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 423

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16s (25 q [1.562 qps], 8 conn, TX: 62K, RX: 1K)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 58.0M global + 1.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 303.4M (3% of installed RAM)
[OK] Slow queries: 0% (0/25)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.1M
[!!] Key buffer hit rate: 77.6% (98 cached / 22 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
[OK] Temporary tables created on disk: 20% (1 on disk / 5 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 68% (15 open / 22 opened)
[OK] Open file limit used: 3% (32/1K)
[OK] Table locks acquired immediately: 100% (29 immediate / 29 locks)
[!!] Connections aborted: 25%
[OK] InnoDB data size / buffer pool: 12.1M/16.0M

[stextbox id=”warning”]——– Recommendations —————————————————–[/stextbox]

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)

 

Optimize tables

See – http://geekdecoder.com/mysql-checkrepair-database/

Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
query_cache_size (>= 8M)
thread_cache_size (start at 4)

 

[root@austin ~]# nano /etc/my.cnf

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
interactive_timeout=1800
wait_timeout=1800

Add the following if mysql is 5.6.1 or higher

slow_query_log = 1;
slow_query_log_file = '/var/log/mysql-slow.log';
query_cache_type = 1
query_cache_size = 32M
thread_cache_size = 4

Other links and Info:

I would recommend making sure the following is optimized.

Your mysql queries in your code to make sure the queries are being closed.
Your mysql tables are optimized.
Your mysql configuration is optimized for your web site needs.

Here are some helpful links on mysql optimization.

http://www.codero.com/knowledge-base/questions/319/How+to+install+mytop+for+database+performance+monitoring%3A
http://www.codero.com/knowledge-base/questions/298/How+can+I+view+currently+running+MySQL+queries%3F
http://www.codero.com/knowledge-base/questions/274/How+to+check+and+repair+MySQL+database+tables
http://www.codero.com/knowledge-base/questions/96/How+do+I+optimze+mysql%3F
http://www.codero.com/knowledge-base/questions/195/How+can+I+trouble+shoot+my+MySQL+database%3F
http://dev.mysql.com/doc/refman/5.0/en/optimization.html

Mysql Check/Repair Database

Check a Specific Table in a Database

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

The following are some of the key options that you can use along with mysqlcheck.

  • -A, –all-databases Consider all the databases
  • -a, –analyze Analyze tables
  • -1, –all-in-1 Use one query per database with tables listed in a comma separated way
  • –auto-repair Repair the table automatically it if is corrupted
  • -c, –check Check table errors
  • -C, –check-only-changed Check tables that are changed since last check
  • -g, –check-upgrade Check for version dependent changes in the tables
  • -B, –databases Check more than one databases
  • -F, –fast Check tables that are not closed properly
  • –fix-db-names Fix DB names
  • –fix-table-names Fix table names
  • -f, –force Continue even when there is an error
  • -e, –extended Perform extended check on a table. This will take a long time to execute.
  • -m, –medium-check Faster than extended check option, but does most checks
  • -o, –optimize Optimize tables
  • -q, –quick Faster than medium check option
  • -r, –repair Fix the table corruption

-c check
-o optimize
-r reapir
-a analyze

Login to mysql on Plesk

[root@austin ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

Check a single table in a database on linux server without plesk

[root@austin ~]# mysqlcheck -c database_name table -u root -p

[stextbox id=”alert” defcaption=”true” mode=”css”]For Plesk[/stextbox]

For repairing a single table

[root@austin ~]# mysqlcheck -r database_name table_name -uadmin -p`cat /etc/psa/.psa.shadow`

Here is the command to run to repair all tables in a database.

[root@austin ~]# mysqlcheck -r database_name -uadmin -p`cat /etc/psa/.psa.shadow`

Here is the command to run for all databases and tables

[root@austin ~]# mysqlcheck --all-databases -u admin -p`cat /etc/psa/.psa.shadow` --auto-repair

For cPanel

# mysqlcheck --all-databases -r #repair
# mysqlcheck --all-databases -a #analyze
# mysqlcheck --all-databases -o #optimize 

Simple:


# mysql -u username -p databasename
# password: ********
mysql> check table tablename;
mysql> repair table tablename;

cPanel

In cPanel, you can repair a database table by:

cPanel -> Databases -> MySQL Databases

You may need to go to Page 2, so under Current Databases [2] or >> to go to the next page. Under Modify Databases click [Check DB] or [Repair DB].

Plesk

Open the database in phpMyAdmin. To do that, go to your Plesk control panel, click “Websites and Domains,” click “Databases,” and then click the database in question. From there, click “Webadmin” under “Tools.” You will most likely be presented with phpMyAdmin.

Next, click “Databases” in the upper right, click the database name, then scroll all the way down and click “Check All” to check all the tables. From the drop down menu, select “Check Tables” and your tables will immediately be checked. After that, you can decide which tables to select and choose “repair” from the drop down menu

PHP Sessions causing Error

Currently your messages log is filling up with errors and the system is unstable. Check the message log:

$ server# tail -f /var/log/messages
Jul 28 08:57:30 mail kernel: EXT4-fs warning (device sda3): ext4_dx_add_entry: Directory index full!

Check the sessions directory

 $ server# php -i | grep session.save_path
session.save_path => /var/lib/php/session => /var/lib/php/session

$ server# du -shcx /var/lib/php/session
1000.0M    /var/lib/php/session
1000.0M    total

A cron job similar to the following ran every day, or periodically, should prevent those files from accumulating.


# find /var/lib/php/session -type d -mtime -15 -delete

Manually

# find /var/lib/php/session -depth -mindepth 1 -maxdepth 1 -type f -cmin +120 -delete;

rsync from one server to another

Basically,


$ rsync options source destination

Synchronize Files From Local to Remote (Plesk to cPanel)


$ rsync -avz /var/www/vhosts/domain.com/httpdocs/ sshuser@192.168.200.99:/home/user/public_html/

Synchronize Files From Remote to Local )cPanel to Plesk)


$ rsync -avz sshuser@198.50.162.99:/home/username/public_html/ /var/www/vhosts/domain.com/httpdocs/

Here is a short summary of the options available in rsync. Please refer to the detailed description below for a complete description.

-v, –verbose increase verbosity
-q, –quiet suppress non-error messages
–no-motd suppress daemon-mode MOTD (see caveat)
-c, –checksum skip based on checksum, not mod-time & size
-a, –archive archive mode; equals -rlptgoD (no -H,-A,-X)
–no-OPTION turn off an implied OPTION (e.g. –no-D)
-r, –recursive recurse into directories
-R, –relative use relative path names
–no-implied-dirs don’t send implied dirs with –relative
-b, –backup make backups (see –suffix & –backup-dir)
–backup-dir=DIR make backups into hierarchy based in DIR
–suffix=SUFFIX backup suffix (default ~ w/o –backup-dir)
-u, –update skip files that are newer on the receiver
–inplace update destination files in-place
–append append data onto shorter files
–append-verify –append w/old data in file checksum
-d, –dirs transfer directories without recursing
-l, –links copy symlinks as symlinks
-L, –copy-links transform symlink into referent file/dir
–copy-unsafe-links only “unsafe” symlinks are transformed
–safe-links ignore symlinks that point outside the tree
-k, –copy-dirlinks transform symlink to dir into referent dir
-K, –keep-dirlinks treat symlinked dir on receiver as dir
-H, –hard-links preserve hard links
-p, –perms preserve permissions
-E, –executability preserve executability
–chmod=CHMOD affect file and/or directory permissions
-A, –acls preserve ACLs (implies -p)
-X, –xattrs preserve extended attributes
-o, –owner preserve owner (super-user only)
-g, –group preserve group
–devices preserve device files (super-user only)
–specials preserve special files
-D same as –devices –specials
-t, –times preserve modification times
-O, –omit-dir-times omit directories from –times
–super receiver attempts super-user activities
–fake-super store/recover privileged attrs using xattrs
-S, –sparse handle sparse files efficiently
-n, –dry-run perform a trial run with no changes made
-W, –whole-file copy files whole (w/o delta-xfer algorithm)
-x, –one-file-system don’t cross filesystem boundaries
-B, –block-size=SIZE force a fixed checksum block-size
-e, –rsh=COMMAND specify the remote shell to use
–rsync-path=PROGRAM specify the rsync to run on remote machine
–existing skip creating new files on receiver
–ignore-existing skip updating files that exist on receiver
–remove-source-files sender removes synchronized files (non-dir)
–del an alias for –delete-during
–delete delete extraneous files from dest dirs
–delete-before receiver deletes before transfer (default)
–delete-during receiver deletes during xfer, not before
–delete-delay find deletions during, delete after
–delete-after receiver deletes after transfer, not before
–delete-excluded also delete excluded files from dest dirs
–ignore-errors delete even if there are I/O errors
–force force deletion of dirs even if not empty
–max-delete=NUM don’t delete more than NUM files
–max-size=SIZE don’t transfer any file larger than SIZE
–min-size=SIZE don’t transfer any file smaller than SIZE
–partial keep partially transferred files
–partial-dir=DIR put a partially transferred file into DIR
–delay-updates put all updated files into place at end
-m, –prune-empty-dirs prune empty directory chains from file-list
–numeric-ids don’t map uid/gid values by user/group name
–timeout=SECONDS set I/O timeout in seconds
–contimeout=SECONDS set daemon connection timeout in seconds
-I, –ignore-times don’t skip files that match size and time
–size-only skip files that match in size
–modify-window=NUM compare mod-times with reduced accuracy
-T, –temp-dir=DIR create temporary files in directory DIR
-y, –fuzzy find similar file for basis if no dest file
–compare-dest=DIR also compare received files relative to DIR
–copy-dest=DIR … and include copies of unchanged files
–link-dest=DIR hardlink to files in DIR when unchanged
-z, –compress compress file data during the transfer
–compress-level=NUM explicitly set compression level
–skip-compress=LIST skip compressing files with suffix in LIST
-C, –cvs-exclude auto-ignore files in the same way CVS does
-f, –filter=RULE add a file-filtering RULE
-F same as –filter=’dir-merge /.rsync-filter’
repeated: –filter=’- .rsync-filter’
–exclude=PATTERN exclude files matching PATTERN
–exclude-from=FILE read exclude patterns from FILE
–include=PATTERN don’t exclude files matching PATTERN
–include-from=FILE read include patterns from FILE
–files-from=FILE read list of source-file names from FILE
-0, –from0 all *from/filter files are delimited by 0s
-s, –protect-args no space-splitting; wildcard chars only
–address=ADDRESS bind address for outgoing socket to daemon
–port=PORT specify double-colon alternate port number
–sockopts=OPTIONS specify custom TCP options
–blocking-io use blocking I/O for the remote shell
–stats give some file-transfer stats
-8, –8-bit-output leave high-bit chars unescaped in output
-h, –human-readable output numbers in a human-readable format
–progress show progress during transfer
-P same as –partial –progress
-i, –itemize-changes output a change-summary for all updates
–out-format=FORMAT output updates using the specified FORMAT
–log-file=FILE log what we’re doing to the specified FILE
–log-file-format=FMT log updates using the specified FMT
–password-file=FILE read daemon-access password from FILE
–list-only list the files instead of copying them
–bwlimit=KBPS limit I/O bandwidth; KBytes per second
–write-batch=FILE write a batched update to FILE
–only-write-batch=FILE like –write-batch but w/o updating dest
–read-batch=FILE read a batched update from FILE
–protocol=NUM force an older protocol version to be used
–iconv=CONVERT_SPEC request charset conversion of filenames
–checksum-seed=NUM set block/file checksum seed (advanced)
-4, –ipv4 prefer IPv4
-6, –ipv6 prefer IPv6
–version print version number
(-h) –help show this help (see below for -h comment)

Export/Import Mysql Database

Export a database mysql


# mysqldump -u -p username database_name > dbname.sql

Plesk Server


# mysqldump  -u admin -p`cat /etc/psa/.psa.shadow` wordpress_database > domain_backup_7.16.14.sql

To export a single table from your database you would use the following command:


# mysqldump -p --user=username database_name tableName > tableName.sql

Import a database or table


# mysql -p -u username database_name < file.sql 

For Plesk server


# mysql -u admin -p`cat /etc/psa/.psa.shadow` database < /tmp/database.sql

To import a single table into an existing database you would use the following command:


#mysql -u username -p -D database_name < tableName.sql

Find All The Files Owned By a Particular User / Group

Find file owned by a group

find directory-location -group {group-name} -name {file-name}

directory-location : directory path.
-group {group-name} : group-name.
-name {file-name} : The file name or a search pattern

Issue: Plesk server will not allow updating plugins and the site is running as fastcgi – which uses the ftp user as the root user ( coldriverw:psacln) is the user:group for this account.

In this example, locate or find all files belongs to a group called “apache” in the /var/www/vhosts/coldriverdata.com/httpdocs/ directory:


[root@austin plugins]# find /var/www/vhosts/domain.com/httpdocs/ -group apache
/var/www/vhosts/domain.com/httpdocs/.htaccess
/var/www/vhosts/domain.com/httpdocs/wp-content/plugins/enhanced-tooltipglossary
/var/www/vhosts/domain.com/httpdocs/wp-content/plugins/enhanced-tooltipglossary/readme.txt

Change the files to the correct user:

 [root@austin plugins]# chown -R domain:psacln /var/www/vhosts/domain.com/httpdocs/wp-content

Retry the wordpress upload

Update Plugin
Downloading update from https://downloads.wordpress.org/plugin/enhanced-tooltipglossary.zip…

Unpacking the update…

Installing the latest version…

Removing the old version of the plugin…

Plugin updated successfully.

More information:
http://www.cyberciti.biz/faq/how-do-i-find-all-the-files-owned-by-a-particular-user-or-group/

Find reason for linux server crash

Check the logs

/var/log/messages, which stores logs from many native CentOS services, such as the kernel logger, the network manager, and many other services that don’t have their own log files. This log file tells you if there are kernel problems (kernel panic messages) or kernel limits violations, such as the number of currently open files, which can cause system problems. You can fix kernel misconfigurations by editing the file /etc/sysctl.conf and changing the value for the corresponding error.

/var/log/dmesg, which contains information about hardware found by the kernel drivers. It can help you troubleshoot hardware problems and missing drivers. You can also use the command /bin/dmesg for similar purposes. /bin/dmesg provides more detailed information in real time, while the log file keeps less information for historical purposes.

/var/log/audit/audit.log, which is the file in which the Linux Auditing System (auditd) writes its logs, including all SELinux information. If auditd is disabled, SELinux sends its logs to /var/log/messages. SELinux is a common suspect for any strange behavior and problems in CentOS. It is enabled by default in CentOS 6 and should not be frivolously disabled, as it is important for security. You can check its status with the command sestatus. A Wazi article about Linux server hardening covers the basics of SELinux, including how to adjust its policies in order to avoid problems.

Service- and application-specific logs – Many applications create logs in other places, and have options that control where and what to log. By default in CentOS the Apache web server logs in the directory /var/log/httpd/, mail servers log in /var/log/maillog, and MySQL logs in /var/log/mysqld.log. However, not all logs are located in the logs directory. Some applications, such as user-space programs, may not have privileges to write there. Others prefer to log inside their own root directory. You may need to consult an application’s manual to learn where it writes its logs.

If it’s gone down without logging anything, it might be power related so it’s not had the chance to log anything.