sed -e '/MatchThisString/ s/^/#/' -i file
This will add a # in front of all the lines that are matching the string “MatchThisString“
PHPMyAdmin on Centos/Ubuntu
BASIC SETUP (Apache)
>> Set password authentication
/etc/httpd/conf.d/phpMyAdmin.conf (or apache.conf on Ubuntu)
<Directory /usr/share/phpMyAdmin/>
# <IfModule mod_authz_core.c>
# Apache 2.4
# <RequireAny>
# Require ip 127.0.0.1
# Require ip ::1
# </RequireAny>
# </IfModule>
# <IfModule !mod_authz_core.c>
# Apache 2.2
# Order Deny,Allow
# Deny from All
# Allow from 127.0.0.1
# Allow from ::1
# </IfModule>
AuthUserFile /etc/httpd/.htpasswdfile
AuthName Restricted
AuthType Basic
require valid-user
</Directory>
>> Generate random password
PASS=$(tr -cd ‘[:alnum:]’ < /dev/urandom | fold -w12 | head -n1)
>> Set password automatically
htpasswd -bmc /etc/httpd/.htpasswdfile phpadminuser $PASS
>> Set password manually
htpasswd -c /etc/httpd/.htpasswdfile phpadminuser
(FYI ‘phpadminuser’ it’s the username)
>> To ADD users, just remove the -c flag
=====================================================================
Troubleshooting
curl -I http://<URL>/phpmyadmin/ –basic –user <username>:<password>
Example: (with error)
# curl -I http://<SERVERIP>/phpmyadmin/ –basic –user serverinfo:mxuYr35TTD5rgT3SR9ND
HTTP/1.1 500 Internal Server Error
Date: Thu, 25 Sep 2014 13:14:44 GMT
Server: Apache
Connection: close
Content-Type: text/html; charset=UTF-8
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
UBUNTU
>> Install the package
# apt-get update && apt-get -y install phpmyadmin
# ln -s /etc/phpmyadmin/apache.conf phpmyadmin.conf
# a2enconf phpmyadmin
>> Open firewall
ufw allow 80
>> When/if it asks the following:
> Please choose the web server that should be automatically configured to run phpMyAdmin => select apache2
> Configure database for phpmyadmin with dbconfig-common? => NO!!!
>> Enable mcrypt
php5enmod mcrypt
service apache2 graceful
>> Create phpmyadmin database and pmaadmin user
cd /usr/share/doc/phpmyadmin/examples
gunzip create_tables.sql.gz
mysql < create_tables.sql
mysql -e “GRANT SELECT, INSERT, DELETE, UPDATE ON phpmyadmin.* TO ‘pmaadmin’@’%’ IDENTIFIED BY ‘<PASSWORD>'”
>> Configuration file for phpmyadmin /etc/dbconfig-common/phpmyadmin.conf
mv /etc/dbconfig-common/phpmyadmin.conf{,.orig} ; vim /etc/dbconfig-common/phpmyadmin.conf
dbc_install=’false’
dbc_upgrade=’true’
dbc_remove=”
dbc_dbtype=’mysql’
dbc_dbuser=’pmaadmin’
dbc_dbpass='<PASSWORD>’
dbc_dbserver='<CLOUD_DB_HOST>’
dbc_dbport=”
dbc_dbname=’phpmyadmin’
dbc_dbadmin=’pmaadmin’
dbc_basepath=”
dbc_ssl=”
dbc_authmethod_admin=”
dbc_authmethod_user=”
>> Apply configuration
/usr/sbin/dbconfig-generate-include/etc/dbconfig-common/phpmyadmin.conf -f php > /etc/phpmyadmin/config-db.php
>> Disable MySQL different library warning
echo “\$cfg[‘ServerLibraryDifference_DisableWarning’] = true;” >> /etc/phpmyadmin/config.inc.php
>> Fix DB table references
sed -i.orig ‘s/pma_/pma__/g’ /etc/phpmyadmin/config.inc.php
>> Secure the main page (this should be under SSL)
htpasswd -c /etc/phpmyadmin/htpasswd.setup phpadminuser
ADD this into Directory for /usr/share/phpmyadmin
<IfModule mod_authn_file.c>
AuthType Basic
AuthName “phpMyAdmin Setup”
AuthUserFile /etc/phpmyadmin/htpasswd.setup
</IfModule>
Require valid-user
————————————————————–
-> Example:
<Directory /usr/share/phpmyadmin>
Options FollowSymLinks
DirectoryIndex index.php
<IfModule mod_php5.c>
AddType application/x-httpd-php .php
php_flag magic_quotes_gpc Off
php_flag track_vars On
php_flag register_globals Off
php_admin_flag allow_url_fopen Off
php_value include_path .
php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/javascript/
</IfModule>
<IfModule mod_authn_file.c>
AuthType Basic
AuthName “phpMyAdmin Setup”
AuthUserFile /etc/phpmyadmin/htpasswd.setup
</IfModule>
Require valid-user
</Directory>
————————————————————–
====================================================================
Multiple DBs (Ubuntu) => /etc/phpmyadmin/config-db.php
/* Servers configuration */
$i = 0;
/* Server: db01 [1] */
$i++;
$cfg[‘Servers’][$i][‘verbose’] = ‘db01’;
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘port’] = ”;
$cfg[‘Servers’][$i][‘socket’] = ”;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’;
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
$cfg[‘Servers’][$i][‘user’] = ”;
$cfg[‘Servers’][$i][‘password’] = ”;
/* Server: db02 [2] */
$i++;
$cfg[‘Servers’][$i][‘verbose’] = ‘db02’;
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘port’] = ”;
$cfg[‘Servers’][$i][‘socket’] = ”;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’;
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
$cfg[‘Servers’][$i][‘user’] = ”;
$cfg[‘Servers’][$i][‘password’] = ”;
====================================================================
PHP-FPM (Ubuntu):
vim /etc/apache2/conf-enabled/phpmyadmin.conf
ProxyPassMatch ^/phpmyadmin/(.*\.php(/.*)?)$ fcgi://127.0.0.1:9001/usr/share/phpmyadmin/$1
ProxyPassMatch ^/phpmyadmin/(.*\.php(/.*)?)$ fcgi://127.0.0.1:9001/usr/share/phpmyadmin$1index.php
Multiple DBs (Ubuntu) /etc/phpmyadmin/config-db.php
/* Servers configuration */
$i = 0;
/* Server: db01 [1] */
$i++;
$cfg[‘Servers’][$i][‘verbose’] = ‘db01’;
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘port’] = ”;
$cfg[‘Servers’][$i][‘socket’] = ”;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’;
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
$cfg[‘Servers’][$i][‘user’] = ”;
$cfg[‘Servers’][$i][‘password’] = ”;
/* Server: db02 [2] */
$i++;
$cfg[‘Servers’][$i][‘verbose’] = ‘db02’;
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘port’] = ”;
$cfg[‘Servers’][$i][‘socket’] = ”;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’;
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
$cfg[‘Servers’][$i][‘user’] = ”;
$cfg[‘Servers’][$i][‘password’] = ”;
=====================================================================
Error: The mcrypt extension is missing. Please check your PHP configuration.
php5enmod mcrypt
sudo updatedb
locate mcrypt.ini
>> Verify that new files exists here (they should be auto created from the issue above)
ls -al /etc/php5/cli/conf.d/20-mcrypt.ini
ls -al /etc/php5/apache2/conf.d/20-mcrypt.ini
>> Otherwise… create symbol links now
ln -s /etc/php5/mods-available/mcrypt.ini/etc/php5/cli/conf.d/20-mcrypt.ini
ln -s /etc/php5/mods-available/mcrypt.ini/etc/php5/apache2/conf.d/20-mcrypt.ini
>> Restart Apacahe
service apache2 restart
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
CENTOS
>>Install right RH repositories (if not present):
yum install epel-release
yum install httpd php php-mycrypt phpmyadmin
> Centos 5/6
chkconfig httpd on
service httpd start
-> open port 80 in /etc/sysconfig/iptables
> Centos 7
systemctl enable httpd.service
systemctl start httpd.service
firewall-cmd –add-service http –permanent
firewall-cmd –list-services
firewall-cmd –permanent –zone=public –add-service=http
firewall-cmd –reload
cd /usr/share/doc/phpMyAdmin-4.0.10.9/examples/
mysql < create_tables.sql
mysql -e “GRANT SELECT, INSERT, DELETE, UPDATE ON phpmyadmin.* TO ‘pmaadmin’@’%’ IDENTIFIED BY ‘<PASSWORD>'”
cp config.sample.inc.php /etc/phpMyAdmin/config.inc.php
>> Change these accordingly
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
/* User used to manipulate with storage */
$cfg[‘Servers’][$i][‘controlhost’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘controluser’] = ‘pmaadmin’;
$cfg[‘Servers’][$i][‘controlpass’] = ‘<PASSWORD>’;
/* Storage database and tables */
$cfg[‘Servers’][$i][‘pmadb’] = ‘phpmyadmin’;
$cfg[‘Servers’][$i][‘bookmarktable’] = ‘pma__bookmark’;
$cfg[‘Servers’][$i][‘relation’] = ‘pma__relation’;
$cfg[‘Servers’][$i][‘table_info’] = ‘pma__table_info’;
$cfg[‘Servers’][$i][‘table_coords’] = ‘pma__table_coords’;
$cfg[‘Servers’][$i][‘pdf_pages’] = ‘pma__pdf_pages’;
$cfg[‘Servers’][$i][‘column_info’] = ‘pma__column_info’;
$cfg[‘Servers’][$i][‘history’] = ‘pma__history’;
$cfg[‘Servers’][$i][‘table_uiprefs’] = ‘pma__table_uiprefs’;
$cfg[‘Servers’][$i][‘tracking’] = ‘pma__tracking’;
$cfg[‘Servers’][$i][‘designer_coords’] = ‘pma__designer_coords’;
$cfg[‘Servers’][$i][‘userconfig’] = ‘pma__userconfig’;
$cfg[‘Servers’][$i][‘recent’] = ‘pma__recent’;
>> Add these two lines at the bottom of /etc/phpMyAdmin/config.inc.php to disable the remaining 2 warnings
>> MySQL different library warning
$cfg[‘ServerLibraryDifference_DisableWarning’] = true;
>> A newer version of phpMyAdmin is available and you should consider upgrading
$cfg[‘VersionCheck’] = false;
=====================================================================
Multiple DBs (Centos) =>/etc/phpMyAdmin/config.inc.php
(example of 2 servers – comment out the below lines)
// Server db01
$i++;
/* Authentication type */
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
/* Server parameters */
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘compress’] = false;
// Server db02
$i++;
/* Authentication type */
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
/* Server parameters */
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘compress’] = false;
#$i++;
#$cfg[‘Servers’][$i][‘host’] = ‘localhost’; // MySQL hostname or IP address
$cfg[‘Servers’][$i][‘port’] = ”; // MySQL port – leave blank for default port
$cfg[‘Servers’][$i][‘socket’] = ”; // Path to the socket – leave blank for default socket
#$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’; // How to connect to MySQL server (‘tcp’ or ‘socket’)
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’; // The php MySQL extension to use (‘mysql’ or ‘mysqli’)
#$cfg[‘Servers’][$i][‘compress’] = FALSE; // Use compressed protocol for the MySQL connection
// (requires PHP >= 4.3.0)
If you’d like to install this from source, use this link.
Apache loop with WordPress and SSL cert installed on a Cloud Load Balancer
- Terminate SSL onto the CLB
- Change the main site URL to use HTTPS in the WordPress configuration
- Add “SetEnvIf x-forwarded-proto https HTTPS=on” in the vhost configuration
- add these in wp-config: [OPTIONAL]
define(‘FORCE_SSL_ADMIN’, false);define(‘FORCE_SSL_LOGIN’, false);
if (strpos($_SERVER[‘HTTP_X_FORWARDED_PROTO’], ‘https’) !== false)
$_SERVER[‘HTTPS’]=’on’;
- a good test to make sure PHP is receiving HTTPS are these lines in a test.php file. If should return “on” if PHP is getting HTTPS properly, or if it returns no value, PHP is not aware it’s being called over HTTPS.
<?php printf($_SERVER['HTTPS']) ?>
PHP test pages
Basic PHP page
cat > test.php <<EOF <?php echo "<h1>This is a test page</h1>"; ?> EOF
PHPinfo page
<?php // Show all information, defaults to INFO_ALL phpinfo(); ?>
(command line)
php -r "phpinfo();"
php-fpm/users php page check
cat > test.php <<EOF
<?php
echo '<br><br>This website is running as: <b>' . exec('/usr/bin/whoami') . '</b>';
echo '<br><br>From path: <b><i>' . getcwd() . '</i></b><br><br>';
echo '<br><b><font size="5" color="red">DELETE THIS ONCE TESTED!</font></b>' . "\n";
?>
MySQL – How much memory for InnoDB buffer?
Use this to find out how much storage each engine uses:
mysql -e "select engine, SUM(DATA_LENGTH)/1024 as data_size, SUM(INDEX_LENGTH)/1024 as index_size, ((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024) as total_size from information_schema.tables group by engine;" +--------------------+-----------+------------+------------+ | engine | data_size | index_size | total_size | +--------------------+-----------+------------+------------+ | CSV | 0.0000 | 0.0000 | 0.0000 | | InnoDB | 480.0000 | 0.0000 | 480.0000 | | MEMORY | 0.0000 | 0.0000 | 0.0000 | | MyISAM | 0.0000 | 1.0000 | 1220.5859 | | PERFORMANCE_SCHEMA | 0.0000 | 0.0000 | 0.0000 | +--------------------+-----------+------------+------------+
From the data above, it may seem like a good idea to keep innodb_buffer_pool low, because we have so much MyISAM data.
BUT if I get one query a minute against that MyISAM data, and 500 queries per second against the considerably smaller InnoDB data-set, that doesn’t make sense anymore.
Then we’d want all of the InnoDB data in memory, hence a buffer pool of 512M+
It’s down to data usage and where the hotspots are!
Percona Toolkit to ‘get the hotspots’
wget http://www.percona.com/downloads/percona-toolkit/2.2.12/tarball/percona-toolkit-2.2.12.tar.gz tar -zxvf percona-toolkit-2.2.12.tar.gz -C /opt/ cd /opt/percona-toolkit-2.2.12/ perl Makefile.PL make
Get the hotspots:
you can set NUMPACKETS or keep the tcpdump running for 20 minutes. JUST keep an eye on the file size!
NUMPACKETS=1000 tcpdump -s 65535 -x -nn -q -tttt -i any -c $NUMPACKETS port 3306 > /tmp/mysql.tcp.txt bin/pt-query-digest --type tcpdump /tmp/mysql.tcp.txt
FYI: https://bugs.launchpad.net/percona-toolkit/+bug/1402776 official bug for that database being wrong thing
Import/Export single MySQL table
>> Extract single table from a running database mysqldump dbname tableName > tableName.sql >> Extract table from a MySQL dump: > Option 1) Using 'sed' sed -n "/^-- Table structure for table \`TABLE_NAME_HERE\`/,/^-- Table structure for table/p" DATABASEDUMP.SQL > TABLENAME-TABLE.sql > Option 2) Using dbsake: https://github.com/abg/dbsake cat file.sql | dbsake split-mysqldump -C /tmp/sql >> Import the table: # mysql -D dbname < tableName.sql
Sources:
http://blog.tsheets.com/2008/tips-tricks/extract-a-single-table-from-a-mysqldump-file.html
https://github.com/JoyceBabu/MySQL-Dump-Table-Extractor
http://blog.tsheets.com/2008/tips-tricks/extract-a-single-table-from-a-mysqldump-file.html#
MySQL Reset admin root password
Recommended method:
1) Create file /var/lib/mysql/mysql.init with contents:
SET PASSWORD FOR 'root'@'localhost'=PASSWORD('mynewpassword');
2) Add this line to /etc/my.cnf in the [mysqld] section:
init_file=/var/lib/mysql/mysql.init
3) Restart the MySQL service
4) At your leisure, delete the mysql.init file and remove the init_file line
from /etc/my.cnf
With MARIADB
Source: https://stroobants.io/reset-a-root-password-in-mariadb-on-linux/
service mysql stop
mysqld_safe --skip-grant-tables --skip-networking &
# mysql
use mysql;
update user set password=PASSWORD("<PASSWORD>") where User='root'
flush privileges;
exit
service mysql restart
Restore database from Holland XtraBackup using dbsake
How it works?
XtraBackup basically does a copy of ‘/var/lib/mysql’ – no dumps. So, it’s good for a full restore but a bit tricky for a specific db restore.
So, the following steps will create a second MySQL instance using dbsake utility (the version can be specified, but the below example uses the version currently running on the server), extracting only the specific backup (to save space) and from there, you will be able to extract the dump with the well known ‘mysqldump‘.
>> Download dbsake. Bear in mind it requires python2.6 which can be installed from IUS. mkdir /tmp/dbsake cd $_ curl -s http://get.dbsake.net > dbsake chmod +x dbsake ./dbsake --version >> Install pigz from EPEL repo yum install pigz >> Use dbsake to unpack Percona XtraBackup stream extracting only the DB required screen -S xtrabackup_restore ./dbsake sandbox --sandbox-directory=/tmp/sandbox/ \ --data-source=/var/spool/holland/xtrabackup/newest/backup.tar.gz -t <database_name>.* >> Start a dbsake instance and dump the required database. /tmp/sandbox/sandbox.sh start /tmp/sandbox.sh mysqldump <database_name> | gzip -1 ><database_name>.sql.gz >> Clean up /tmp/sandbox/sandbox.sh stop rm -rf /tmp/sandbox
Space utilised in MySQL tables
mysql> use information_schema; mysql> select table_schema, sum(DATA_LENGTH)/(1024*1024), sum(INDEX_LENGTH)/(1024*1024) from tables group by table_schema;
For a table mydb.mytable run this for:
BYTES
SELECT (data_length+index_length) tablesize FROM information_schema.tables WHERE table_schema='mydb' and table_name='mytable';
KILOBYTES
SELECT (data_length+index_length)/power(1024,1) tablesize_kb FROM information_schema.tables WHERE table_schema='mydb' and table_name='mytable';
MEGABYTES
SELECT (data_length+index_length)/power(1024,2) tablesize_mb FROM information_schema.tables WHERE table_schema='mydb' and table_name='mytable';
GIGABYTES
SELECT (data_length+index_length)/power(1024,3) tablesize_gb FROM information_schema.tables WHERE table_schema='mydb' and table_name='mytable';
GENERIC
Here is a generic query where the maximum unit display is TB (TeraBytes)
SELECT CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE, CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE, CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE FROM ( SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM ( SELECT data_length DAT,index_length NDX,data_length+index_length TBL, FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px, FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py, FLOOR(LOG(IF(data_length+index_length=0,1,data_length+index_length))/LOG(1024)) pz FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable' ) AA ) A,(SELECT 'B KBMBGBTB' units) B;
MySQL DB creation and user management
Useful commands:
>>Create DB and user
create database $DB;
grant all privileges on $DB.* to $DBUSER@localhost identified by '$PASS';
>> Check GRANT permissions:
SHOW GRANTS FOR 'root'@'localhost';
>> How to grant privileges
GRANT ALL PRIVILEGES ON *.* TO 'admin1'@'localhost' IDENTIFIED BY '$password';
GRANT ALL PRIVILEGES ON 'db01.* TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON 'db02.* TO 'db02admin'@'localhost' WITH GRANT OPTION;
>> Grant read-only
GRANT SELECT ON $database_name.* TO $user@$host IDENTIFIED BY '$password';
>> Set password:
set password for 'root'@'%' = password('yournewpassword');
set password for 'root'@'localhost' = password('yournewpassword');
>> Show all users
mysql> use mysql;
mysql> SELECT User,host FROM mysql.user;
>> Remove user
mysql> use mysql;
mysql> DROP USER 'bob'@'localhost';
>> Update user
SELECT User,host FROM mysql.user where user='chris';
update host set host='%' FROM mysql.user where user='chris';
>> Check grants/permission for a user
show grants for 'chris'@'%';