Monthly Archive: January 2020

Failed to remote connect to MySQL because of many connection errors;unblock witj ‘mysqladmin flush-hosts’

Problem: My window 10 want to remote connect to Linux MySQL server, but popup with the following error:

This is because the command uses host cache tables to exceed the maximum number of connections for a particular host, MySQL server will not able to make new connections.

Solution: enter Linux server command prompt, enter below command:

mysqladmin flush-hosts

or enter MySQL command prompt, enter below query:

FLUSH HOSTS;

Enable remote MySQL/MariaDB connection

Problem: Want an outside notebook/website able to remote connect AWS server’s database.

Solution: First allow all IP to access 3306 in the security group on the AWS portal.

then change my.cnf file, add bind-address:

[mysqld]
    ...
    skip-networking
    ...
    bind-address = <some ip-address>
    ...

the ip address should be 127.0.0.1 or localhost

At last, go to the mysql command-line client by entering mysql, and enter below command to grant access for outside IP address

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD' with grant option;

so that specific user from specific IP can remote connect to this database.

Add basic authentication in phpMyAdmin in CentOS 7

Problem: Want to increase phpMyAdmin security, for example adding one more gateway before entering username and password in xxx/phpmyadmin

This is possible and can be achieved in two way:

Solution: first find out where the PHPMyAdmin folder

find /etc/httpd/ -print0 | xargs -0 grep phpmyadmin

then create a .htpasswd  file for the user you want, like user1.

sudo htpasswd -c /etc/httpd/.htpasswd user1

the output will look like this:

user1:$apr1$0r/2zNGG$jopiWY3DEJd2FvZxTnugJ/

Way 1 -- edit /etc/httpd/conf/httpd.conf , change into below content:

<Directory /usr/share/phpmyadmin>
    AuthType Basic
    AuthName "Restricted Content"
    AuthUserFile /etc/httpd/.htpasswd
    Require valid-user
</Directory>

Way 2 -- go to phpMyAdmin folder that finds out via the previous command, add .htaccess file with below content:

AuthType Basic
AuthName "Admin Login"
AuthUserFile /etc/httpd/pma_pass
Require valid-user

Inside multistore + subdomain of Opencart, the new store still displaying the default store’s product and URL

Problem: Applying the multistore + subdomain inside Opencart. For example:

  • default store domain is opencart.com
  • new store domain is second.opencart.com

but here is the problem -- in the new store, the product page can display the default store’s product only, and the product URL still from the default store domain (opencart.com)

Solution: Check out this Youtube video, double-check the new Store URL should start with http:// and at the end append “/

Extend AWS EBS volumes after extending current volume in EC2 instance with no downtime

Problem: well there is a guide from AWS official website but I want to make a simplified version.

This post mainly takes this website as a reference, I saw the website have some indentation problem so I make a refined version here.

Solution: we mainly using “lsblk” and “growpart” command to extend drive storage.

First, after extending current volume in EC2 instance, enter “lsblk” to get block devices information:

NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvda 202:0 0 20G 0 disk
└─xvda1 202:1 0 8G 0 part /

You can see the original file system size is 8G, and we extend the space from 8G to 20G. But the 12G new, fresh storage still hasn’t applied to the current system.

Second, using “growpart” to extend a partition in a partition table,  we know that the current system file partition is xvda1 so enter below command:

sudo growpart /dev/xvda 1

now check again with “lsblk”

NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvda 202:0 0 20G 0 disk
└─xvda1 202:1 0 20G 0 part /

Although the partition now reflects the increased volume size, last we need to extend the filesystem itself. If your filesystem is an ext2, ext3, or ext4, type:

sudo resize2fs /dev/xvda1

If your filesystem is an XFS, then type:

sudo xfs_growfs /dev/xvda1

Error “mount: unknown filesystem type ‘(null)'” occurred in CentOS 7 AWS

Problem: Attach a volume to AWS EC2 instance, after enter a command for mount drive:

sudo mount /dev/xxxx /xxx

An error occurred:

mount: /dev/xxxx is write-protected, mounting read-only mount: unknown filesystem type ‘(null)’

Solution: the volume is unformatted and you should format it first. Enter below command to format volume to ext4 file system :

sudo mkfs.ext4 /dev/xxxx

Then mount again:

sudo mount /dev/xxxx /xxx

By the way, you can use lsblk command to retrieve hard disk partitions and disk space information.

Android app violate policy – “Interstitials that unexpectedly launch” and how to solve it

Problem: According to Google policy, my app violates and needs to fix it before a certain date.

It is because of the interstitials launches when opening app.

Instead, It should be implemented at logical breaks in between your app’s content (e.g. pages, stages, or levels), so the user will not be confused since they are doing some action.

Solution: pre-load the interstitial in advance / Showing Interstitial Ads With Timer (in Splash Screen only)

Source in pdf

Run backup script to backup MySQL Database using cron

Problem: already add backup command in cron:

mysql -s -r -u root -e ‘show databases’ -N | while read dbname; do mysqldump -u root --complete-insert --single-transaction “$dbname” > /backup/sql/”$dbname”.sql; done

and add dbusername and dbpassword in ~/.my.cnf

[client]
user = dbusername
password = dbpassword

but fail to backup due to ‘INFORMATION_SCHEMA.GLOBAL_VARIABLES’ feature is disabled.

According to suggestion, enable MySQL 5.6 compatibility.

Solution: enter MySQL mode and enter

set global show_compatibility_56=on;

or add below directive to /etc/my.cnf :

show_compatibility_56=ON