innodb tables doesn’t exist

Posted: August 27, 2017 in Databases

Sometimes, InnoDB crashed can happened during MySQL upgrade from 5.5 to 5.6 or some other reason like database crash. So you might get the following error:

+++

InnoDB: Could not find a valid tablespace file for ‘pmtest/slave_master_info’

[ERROR] InnoDB: Could not find a valid tablespace file for ‘pmtest/slave_relay_log_info’

[ERROR] InnoDB: Could not find a valid tablespace file for ‘pmtest/slave_worker_info’

InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.

+++

This is a known bug in MySQL 5.6 https://bugs.mysql.com/bug.php?id=67179

According to the replies to the bug report, you can manually create the missing tables.

1) Drop these tables from Mysql: (these files will located in the /var/lib/mysql/mysql/)

 innodb_index_stats
 innodb_table_stats
 slave_master_info
 slave_relay_log_info
 slave_worker_info


You can check if these tables are present using the command desc:

+++

mysql> desc slave_master_info;
ERROR 1146 (42S02): Table ‘mysql.slave_master_info’ doesn’t exist
mysql> desc slave_relay_log_info;
ERROR 1146 (42S02): Table ‘mysql.slave_relay_log_info’ doesn’t exist
mysql> desc slave_worker_info;
ERROR 1146 (42S02): Table ‘mysql.slave_worker_info’ doesn’t exist

+++

 

2) Delete *.frm and *.ibd files for the 5 tables above.

3) Create the tables by running the following queries:

 

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;


CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';



CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

 

4) Restart your MySQL server.

Advertisements

Linux Malware Detect (LMD) is a malware scanner for Linux released under the GNU GPLv2 license, that is designed around the threats faced in shared hosted environments. It uses threat data from network edge intrusion detection systems to extract malware that is actively being used in attacks and generates signatures for detection.

 

In addition, threat data is also derived from user submissions with the LMD checkout feature and from malware community resources. The signatures that LMD uses are MD5 file hashes and HEX pattern matches, they are also easily exported to any number of detection tools such as ClamAV.

 

Note: This guide assumes you are familiar with SSH and basic command line navigation. These instructions apply primarily to customers who have Virtual Private Servers or Dedicated servers. If you do not have root-level access you will not be able to make these changes.

 

Installation

Installation is very straightforward. Ensure to change the version number below to the one you have actually downloaded:

# cd /usr/local/src
# wget http://www.rfxn.com/downloads/maldetect-current.tar.gz
# tar -zxvf maldetect-current.tar.gz
# cd maldetect-1.4.2
# ./install.sh

 

After the installation has been completed succesfully, you will be presented with the following output.

Linux Malware Detect v1.4.2
            (C) 2002-2011, R-fx Networks 
            (C) 2011, Ryan MacDonald 
inotifywait (C) 2007, Rohan McGovern 
This program may be freely redistributed under the terms of the GNU GPL

installation completed to /usr/local/maldetect
config file: /usr/local/maldetect/conf.maldet
exec file: /usr/local/maldetect/maldet
exec link: /usr/local/sbin/maldet
exec link: /usr/local/sbin/lmd
cron.daily: /etc/cron.daily/maldet

maldet(6073): {sigup} performing signature update check...
maldet(6073): {sigup} local signature set is version 2013102428301
maldet(6073): {sigup} new signature set (2013102428301) available
maldet(6073): {sigup} downloaded http://www.rfxn.com/downloads/md5.dat
maldet(6073): {sigup} downloaded http://www.rfxn.com/downloads/hex.dat
maldet(6073): {sigup} downloaded http://www.rfxn.com/downloads/rfxn.ndb
maldet(6073): {sigup} downloaded http://www.rfxn.com/downloads/rfxn.hdb
maldet(6073): {sigup} downloaded http://www.rfxn.com/downloads/maldet-clean.tgz
maldet(6073): {sigup} signature set update completed
maldet(6073): {sigup} 10849 signatures (8981 MD5 / 1868 HEX)

 

Configuration

Maldet can be configured by editing the file below:

# vi /usr/local/maldetect/conf.maldet

The default file looks as follows:

# [ EMAIL ALERTS ]
##
# The default email alert toggle
# [0 = disabled, 1 = enabled]
email_alert=1

# The subject line for email alerts
email_subj="maldet alert from $(hostname)"

# The destination addresses for email alerts
# [ values are comma (,) spaced ]
email_addr="youremail@yourdomain.com"

# Ignore e-mail alerts for reports in which all hits have been cleaned.
# This is ideal on very busy servers where cleaned hits can drown out
# other more actionable reports.
email_ignore_clean=0

##
# [ QUARANTINE OPTIONS ]
##
# The default quarantine action for malware hits
# [0 = alert only, 1 = move to quarantine & alert]
quar_hits=1

# Try to clean string based malware injections
# [NOTE: quar_hits=1 required]
# [0 = disabled, 1 = clean]
quar_clean=1

# The default suspend action for users wih hits
# Cpanel suspend or set shell /bin/false on non-Cpanel
# [NOTE: quar_hits=1 required]
# [0 = disabled, 1 = suspend account]
quar_susp=0
# minimum userid that can be suspended
quar_susp_minuid=500

 

You may edit the following values to configure Maldet to your needs

  • email_alert : If you would like to receive email alerts, then it should be set to 1.
  • email_subj : Set your email subject here.
  • email_addr : Add your email address to receive malware alerts.
  • quar_hits : The default quarantine action for malware hits, it should be set 1.
  • quar_clean : Cleaing detected malware injections, must set to 1.
  • quar_susp : The default suspend action for users wih hits, set it as per your requirements.
  • quar_susp_minuid : Minimum userid that can be suspended.

 

Cronjob for automated scan

During installation, a daily cron job script is installed in /etc/cron.daily/maldet.

The cronjob installed by LMD is used to perform daily update of signature files, keep the session, temp and quarantine data to no more than 14 days old and it runs a daily scan of recent file system changes. If inotify-based real time monitoring is enabled, the daily cron job also scans the recently updated/created files for malware. The folder structures for the most popular control panel configurations: Ensim, Plesk, DirectAdmin, Cpanel, Interworx, have been imcluded.

You should ensure compatibility with your servers’ structure of homedirs and make sure it corresponds with this cron file. Take special note of the control panel specific sections in this cron file.

#!/bin/bash

# clear quarantine/session/tmp data every 14 days
/usr/sbin/tmpwatch 336 /usr/local/maldetect/tmp >> /dev/null 2>&1
/usr/sbin/tmpwatch 336 /usr/local/maldetect/sess >> /dev/null 2>&1
/usr/sbin/tmpwatch 336 /usr/local/maldetect/quarantine >> /dev/null 2>&1
/usr/sbin/tmpwatch 336 /usr/local/maldetect/pub/*/ >> /dev/null 2>&1

# check for new release version
/usr/local/maldetect/maldet -d >> /dev/null 2>&1

# check for new definition set
/usr/local/maldetect/maldet -u >> /dev/null 2>&1

# if were running inotify monitoring, send daily hit summary
if [ "$(ps -A --user root -o "comm" | grep inotifywait)" ]; then
        /usr/local/maldetect/maldet --alert-daily >> /dev/null 2>&1
else
        # scan the last 2 days of file changes
        if [ -d "/home/virtual" ] && [ -d "/usr/lib/opcenter" ]; then
                # ensim
                /usr/local/maldetect/maldet -b -r /home/virtual/?/fst/var/www/html 2 >> /dev/null 2>&1
                /usr/local/maldetect/maldet -b -r /home/virtual/?/fst/home/?/public_html 2 >> /dev/null 2>&1
        elif [ -d "/etc/psa" ] && [ -d "/var/lib/psa" ]; then
                # psa
                /usr/local/maldetect/maldet -b -r /var/www/vhosts/?/httpdocs 2 >> /dev/null 2>&1
                /usr/local/maldetect/maldet -b -r /var/www/vhosts/?/subdomains/?/httpdocs 2 >> /dev/null 2>&1
        elif [ -d "/usr/local/directadmin" ]; then
                # DirectAdmin
                /usr/local/maldetect/maldet -b -r /var/www/html/?/ 2 >> /dev/null 2>&1
                /usr/local/maldetect/maldet -b -r /home?/?/domains/?/public_html 2 >> /dev/null 2>&1
        else
                # cpanel, interworx and other standard home/user/public_html setups
                /usr/local/maldetect/maldet -b -r /home?/?/public_html 2 >> /dev/null 2>&1
        fi
fi

In order to activate email alerts when malware is detected, you need to open the Maldet configuration file, which is located at:
/usr/local/maldetect/conf.maldet .

email_alert=1
email_subj="Maldet alert from $(hostname)"
email_addr="email@domain.com"

 

iNotify Monitoring

The inotify monitoring feature is designed to monitor users in real-time for file creation/modify/move operations. This option requires a kernel that supports inotify_watch (CONFIG_INOTIFY) which is found in kernels 2.6.13+ and CentOS/RHEL 5 by default. If you are running CentOS 4 you should consider an inbox upgrade with: http://www.rfxn.com/upgrade-centos-4-8-to-5-3/

There are three modes that the monitor can be executed with and they relate to what will be monitored, they are USERS|PATHS|FILES.

  • e.g: maldet –monitor users
  • e.g: maldet –monitor /root/monitor_paths
  • e.g: maldet –monitor /home/mike,/home/ashton

The options break down as follows:

  • USERS – The users option will take the homedirs of all system users that are above inotify_minuid and monitor them. If inotify_webdir is set then the users webdir, if it exists, will only be monitored.
  • PATHS – A comma spaced list of paths to monitor
  • FILE – A line spaced file list of paths to monitor

you can run maldet as a daemon as follows. The example below displays the syntax for a comma spaced list of paths to monitor

maldet -m /var,/home
maldet(5330): {mon} set inotify max_user_instances to 128
maldet(5330): {mon} set inotify max_user_watches to 61440
maldet(5330): {mon} added /var to inotify monitoring array
maldet(5330): {mon} added /home/xmodulo to inotify monitoring array
maldet(5330): {mon} starting inotify process on 1 paths, this might take awhile...
maldet(5330): {mon} inotify startup successful (pid: 4154)
maldet(5330): {mon} inotify monitoring log: /usr/local/maldetect/inotify/inotify_log

 

Usage

To scan a folder, for example /home you should enter:

maldet -a /home.

you can examine the malware scan report by running the following command and appending the scan report ID.

maldet --report number-xxxx.xxxxx

To quarantine the infected files, run the following command with the scan report ID. The infected files will then be quarantined for cleaning.

# maldet -q SCAN ID
# maldet –quarantine SCANID

Clean all malware results from a previous scan

# maldet -n SCAN ID
# maldet --clean SCAN ID

Restore a file that you have already quarantined

# maldet -s FILENAME
# maldet --restore FILENAME

 

Ignore Files

There are three ignore files available in Linux Malware Detect. These can be used to exclude files from daily malware scans.

ignore_paths

This is a line spaced file for paths that are to be execluded from search results

/usr/local/maldetect/ignore_paths

 

ignore_sigs

This is a line spaced file for signatures that should be removed from file scanning

/usr/local/maldetect/ignore_sigs

 

ignore_inotify

This is a line spaced file for paths that are to be excluded from inotify monitoring

/usr/local/maldetect/ignore_inotify

There may be some situations where you have to move the location of MySQL, for example, if you’re out of disk space or perhaps looking to host it on another device to increase performance. Whatever the reason, moving MySQL is simple and has no impact  on cPanel’s functionality.

 

1.Create a backup

Please make full database backup(including system tables) before moving your data directory. This action will prevent data losing in case if something goes wrong.

# tar -cvf mysql.tar /var/lib/mysql

2.Edit the my.cnf file

# vi /etc/my.cnf

Now in the mysqld section add the following. Don’t restart MySQL after adding new entry.

datadir=/home/mysql

3.Create the new MySQL data directory

# mkdir /home/mysql

4.Now migrate the data to the new location using rsync command.

# nohup rsync -avp /var/lib/mysql/ /home/mysql

The nohup will keep rsync running even when your session with the server end, the other part “

# tail -f nohup.out

Notice that you have to do the syncing process twice because when moving large size of data can take some time to complete and the tables may have changed in between. When we run it the second time we hopefully get it so that when the switch over happens there is very little, if any, lost data. If you can afford the downtime simply shut down MySQL before running this command.If you cannot though running it twice then quickly copy/pasting the other commands is a valid substitute.

5.Typically you want to stop MySQL for syncing data completely.

# /etc/init.d/mysqld stop

6.Start the re-sync process once again to copy data’s completely.

# rsync -avp --delete /var/lib/mysql/ /home/mysql/

7. Change ownership of new created MySQL data directory to MySQL.

# chown -R mysql:mysql /home/mysql/

8. Now, re-link the socket file to /tmp:

# rm -rf /tmp/mysql.sock
# ln -sf /home/mysql/mysql.sock /tmp/mysql.sock

9. Since you already added the data directory entry to my.cnf , all you need to do is restart again and everything should be working.

# /etc/init.d/mysqld start

Check whether your MySQL logs are written at the new location (Eg: /home/mysql/hostname.err)

10. Create a sample database named “test” for checking.

# mysqladmin create test

11. Check whether the new database is created at new data directory.

# ls -d /home/mysql/test

12. After confirming everything works properly.You can remove the old data directory.

# rm -rf  /var/lib/mysql

Is it possible?

Yeah, definitely! You can create a complete cPanel account backup by excluding the home directory. This is very important, because, if the home directory of cPanel accounts have a large amount of data, this will increase the total time for the account migration.

Generating backup and restoring it for a large account is the much time-consuming process. If you need a faster solution, exclude the home directory then complete the account migration without its home directory. After that start syncing the home directory from source to destination server 🙂

cPanel provides necessary scripts to do this kind of operations via server CLI (command line interface). Here I am listing those scripts for account migration.

Command to create a cPanel backup file

/scripts/pkgacct

Package account.


Important cPanel scripts

How to create cPanel backup without its home directory?

/scripts/pkgacct --skiphomedir user

Replace user with cPanel user name.

The cPanel script to restore an account is pasted below:

/scripts/restorepkg $backup.file.name.

I guess you all are familiar with those scripts. Here we go!!


This is a simple for loop to generate backups, sync that to remote host and remove that backup file from the source server after syncing them to remote host.

Here is the script for doing so:

for x in `\ls -A1 /var/cpanel/users/`; do /scripts/pkgacct --skiphomedir ${x} ; rsync -aP /home/cpmove-${x}.tar.gz -e ssh root@destination.server:/home/ ; rm -f /home/cpmove-${x}.tar.gz ; done

Replace destination.server with the IP address of destination server.

If you want to maintain those backups on the source server, remove the last entry.

for x in `\ls -A1 /var/cpanel/users/`; do /scripts/pkgacct --skiphomedir ${x} ; rsync -aP /home/cpmove-${x}.tar.gz -e ssh root@NEW-SERVER-IP:/home/ ; done

Yeah, you have successfully created backup files and synced those files to the remote server. Now you need to restore those backups to the destination server.

Execute the following command on destination server.

for x in `ls -A1 /home/cpmove*` ; do /scripts/restorepkg /home/${x} ; done

That’s it!!!

Wait wait, where is home directories? Yeah you need to sync home directories to destination server. Syncing directories is not a big task. Please execute the following command:

rsync -aP /home/ -e ssh root@destination.server:/home/

Rsync commands in details

Yup! You’ve successfully completed the account migration.
Change the name servers of those domains to load it from the new server.


This can be done by executing the “whmapi1” command via Linux Shell. WHM API 1 accesses the WHM interface’s features. You can use this API to perform server administration tasks, administrate cPanel and WHM reseller accounts, and manage cPanel & WHM services.

You can create a temporary root WHM session by executing the following command:

bash-4.1# whmapi1 create_user_session user=root service=whostmgrd


data:
  cp_security_token: /cpsess3485551384
  expires: ‘1492600874’
  service: whostmgrd
  session: root:b8AC4rvpcYcKo5YF:create_user_session,90f2017bccccd915920e051c2960a3d6
  url: https://vps.domainname.com:2087/cpsess3485551384/login/?session=root%3ab8AC4rvpcYcKo5YF%3acreate_user_session%2c90f2017bccccd915920e051c2960a3d6
metadata:
  command: create_user_session
  reason: Created session
  result: 1
  version: 1

This is a one-time-use session to access WHM panel. Just copy the WHM URL and paste it on web browser. This link will bypass the need to enter a root password. That’s it!

 

Whmapi command to create a WHM session for reseller account

To create a WHM session for a reseller account, just change the “user” name from root to reseller account’s user name. That’s it!

Same you can login to the cPanel without password by using following command:

whmapi1 create_user_session user=USER_NAME service=cpaneld

 


Plesk is not accessible. The error below is shown:

ERROR: PleskFatalException
Unable to connect to database: Unknown database 'psa'

0: common_func.php3:150
    psaerror(string 'Unable to connect to database: Unknown database 'psa'')
1: auth.php3:111

Cause

The psa database does not exist.

Resolution

  1. Log in to MySQL and check if the psa database exists:
    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin
    
    mysql> show databases like 'psa';
    Empty set (0.00 sec)
    

    So, at this point, you need to restore the database from the latest available backup on the server.

  2. Check if backups exist in the dump directory:
    ~# grep dump /etc/psa/psa.conf
    DUMP_D /var/lib/psa/dumps
    
    ~# ls -la  # ls -lt /var/lib/psa/dumps/mysql.daily.dump*
    -rw------- 1 root root 239658 Dec 10 03:37 /var/lib/psa/dumps/mysql.daily.dump.0.gz
    -rw------- 1 root root 241242 Dec  9 03:37 /var/lib/psa/dumps/mysql.daily.dump.1.gz
    -rw------- 1 root root 241376 Dec  8 03:37 /var/lib/psa/dumps/mysql.daily.dump.2.gz
    -rw------- 1 root root 241887 Dec  7 03:38 /var/lib/psa/dumps/mysql.daily.dump.3.gz
    -rw------- 1 root root 237143 Dec  6 03:38 /var/lib/psa/dumps/mysql.daily.dump.4.gz
    -rw------- 1 root root 233966 Dec  5 03:36 /var/lib/psa/dumps/mysql.daily.dump.5.gz
    -rw------- 1 root root 233495 Dec  4 03:38 /var/lib/psa/dumps/mysql.daily.dump.6.gz
    -rw------- 1 root root 232856 Dec  3 03:36 /var/lib/psa/dumps/mysql.daily.dump.7.gz
    -rw------- 1 root root 232710 Dec  2 03:36 /var/lib/psa/dumps/mysql.daily.dump.8.gz
    
  3. Restore the latest daily dump (from Dec. 10 in this case) using the commands below:
    # gunzip /var/lib/psa/dumps/mysql.daily.dump.0.gz 
    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin  -e "create database psa"
    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa < /var/lib/psa/dumps/mysql.daily.dump.0
 

Custom php.ini file with FastCGI

Posted: December 15, 2016 in other

  1. Login to your server via SSH.
  2. Make a backup copy of your cPanel PHP wrapper script with the following command:

    cp -frp /usr/local/cpanel/cgi-sys/php5 /usr/local/cpanel/cgi-sys/php5-BACKUP

  3. Now edit the cPanel PHP wrapper script with your favorite text editor:

    vi /usr/local/cpanel/cgi-sys/php5

    By default this script should look like:

    #!/bin/sh

    # If you customize the contents of this wrapper script, place
    # a copy at /var/cpanel/conf/apache/wrappers/php5
    # so that it will be reinstalled when Apache is updated or the
    # PHP handler configuration is changed

    exec /usr/bin/php

  4. Above the line exec /usr/bin/php add the following code:

    [[ -f ~/public_html/php.ini ]] && exec /usr/bin/php -c ~/public_html/php.ini

    Now the cPanel PHP wrapper script should look like:

    #!/bin/sh

    # If you customize the contents of this wrapper script, place
    # a copy at /var/cpanel/conf/apache/wrappers/php5
    # so that it will be reinstalled when Apache is updated or the
    # PHP handler configuration is changed

    [[ -f ~/public_html/php.ini ]] && exec /usr/bin/php -c ~/public_html/php.ini
    exec /usr/bin/php

    What this does is uses the Bash syntax for seeing if a file exists [[ -f ]] and in this case we’re looking for the file ~/public_html/php.ini. The ~ symbol would represent the current user calling the script, so this would be the same as entering in either /home/user1/public_html/php.ini or /home/user2/public_html/php.ini as the full path.

    The rest of the code simply executes the PHP binary at /usr/bin/php with the -c flag which sets the location where you’d like to load a php.ini from from. In this case we are telling the server we’d like to use the php.ini file inside the user’s /public_html/ directory if one exists, instead of /usr/local/lib/php.ini which would be the server’s default.

  5. Now you’ll want to copy the cPanel PHP wrapper script to a more permanent location, so that your settings are saved if you ever recompile Apache down the road. This can be done using the following command:

    mkdir -p /var/cpanel/conf/apache/wrappers
    cp -frp /usr/local/cpanel/cgi-sys/php5 /var/cpanel/conf/apache/wrappers/php5

  6. Now restart Apache for the settings to become active:

    service httpd restart

  7. In order to verify your settings have been applied you’ll want to create a PHP info script, the simplest way of doing this is simply copying any of your files to a file such as info.php, and then overwriting it with the phpinfo(); function:

    echo ‘<?php phpinfo(); ?>’ > ~user1/public_html/info.php

    Now when you visit your website and access the newly crated info.php script, you should see your custom php.ini being loaded next to the Loaded Configuration File section
    php-ini-loaded-configuration-file