My Daily Works Archive

[client]
	port = 3306
	socket = /var/run/mysqld/mysqld.sock

	# This was formally known as [safe_mysqld]. Both versions are currently parsed.
	[mysqld_safe]
	socket = /var/run/mysqld/mysqld.sock
	nice = 0
	syslog

	[mysqld]
	# Basic Settings
	user = mysql
	pid-file = /var/run/mysqld/mysqld.pid
	socket = /var/run/mysqld/mysqld.sock
	port = 3306
	basedir = /usr
	datadir = /var/lib/mysql
	tmpdir = /tmp

	lc-messages-dir = /usr/share/mysql
	skip-external-locking

	# Engine
	default-storage-engine = InnoDB

	# Listening IP
	bind-address = 0.0.0.0

	# Safety
	max-connect-errors = 1000000
	max_allowed_packet = 64M
	skip-name-resolve
	sysdate-is-now = 1
	innodb = FORCE
	innodb-strict-mode = 1

	wait_timeout = 60
	interactive_timeout = 60

	# Buffers
	sort_buffer_size = 4M
	read_buffer_size = 2M
	join_buffer_size = 8M
	read_rnd_buffer_size = 16M

	thread_concurrency = 8 # Max CPU * 2

	# MyISAM
	key-buffer-size = 32M
	myisam-recover = FORCE,BACKUP
	myisam_sort_buffer_size = 64M

	# CACHES AND LIMITS #
	tmp-table-size = 128M
	max-heap-table-size = 128M
	query-cache-type = 0
	query-cache-size = 0
	query_cache_limit = 1M
	max-connections = 5000
	thread-cache-size = 50
	thread_stack = 192K
	open-files-limit = 65535
	table-definition-cache = 1024
	table-open-cache = 2048

	# Bin logs
	binlog-format = ROW
	log-bin = /var/lib/mysql/mysql-bin
	log-slave-updates = 1
	expire-logs-days = 5
	sync-binlog = 1
	max_binlog_size = 100M

	server-id = 1 # randomize it incase of multiple servers

	# InnoDB
	innodb-buffer-pool-size = 2048M
	innodb_buffer_pool_instances = 8
	innodb_additional_mem_pool_size = 20M
	innodb_log_buffer_size = 8M
	innodb-log-files-in-group = 2
	innodb-log-file-size = 256M
	innodb-file-per-table = 1
	innodb-flush-log-at-trx-commit = 1
	innodb-flush-method = O_DIRECT

	# With virtual synchrony redundancy, make write queries faster
	innodb_doublewrite = 1

	# This is a recommended tuning variable for performance
	innodb_locks_unsafe_for_binlog = 1

	# LOGGING
	general_log_file = /var/log/mysql/mysql.log
	log-error = /var/log/mysql/mysql-error.log
	log-queries-not-using-indexes = 1

	[mysqldump]
	quick
	quote-names
	max_allowed_packet = 16M

	[isamchk]
	key_buffer = 16M

	#
	# * IMPORTANT: Additional settings that can override those from this file!
	# The files must end with '.cnf', otherwise they'll be ignored.
	#
	!includedir /etc/mysql/conf.d/

billing, freeradius, mikrotik

checkmac.php

22:38:00 ITExam_Blog No comments

cat /temp/checkmac.php
<?php
// PHP page to check if MAC is not aleady there for the user, then INSERT it for MAC VALIDATION,
// it will add mac for 1st time login user only
// 31-OCT-2017

$link = mysql_connect('localhost', 'root', 'roott123');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
// Default DB is radius
mysql_select_db('radius');
// Look for MAC entry for this user
$result=mysql_query("select * FROM radcheck WHERE `UserName`='$argv[1]' AND attribute='Calling-Station-Id' order by Username limit 1");
$val = mysql_num_rows($result);
if ($val > 0) {
printf ("MAC Entry already found by Suntech");
}
else {
printf ("Seems to be New User, adding its MAC address in table ...");
mysql_query("INSERT into radcheck (UserName, Attribute, op, Value) values ('$argv[1]', 'Calling-Station-Id', ':=', '$argv[2]')");
}
?>

cat /temp/checkmac.php7
<?php
// PHP page to check if MAC is not aleady there for the user, then INSERT it for MAC VALIDATION,
// it will add mac for 1st time login user only
// 31-OCT-2017

$link = mysqli_connect('localhost', 'root', 'roott123', 'radius');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
// Default DB is radius
// mysqli_select_db('radius');
// Look for MAC entry for this user
$result=mysqli_query($link, "select * FROM radcheck WHERE `UserName`='$argv[1]' AND attribute='Calling-Station-Id' order by Username limit 1");
$val = mysqli_num_rows($result);
if ($val > 0) {
printf ("MAC Entry already found by ZAiBBBBBBBBBBBBBBBB");
}
else {
printf ("Seems to be New User, adding its MAC address in table ...");
mysqli_query($link, "INSERT into radcheck (UserName, Attribute, op, Value) values ('$argv[1]', 'Calling-Station-Id', ':=', '$argv[2]')");
}
?>

MySQL, php

How to change date format in PHP?

02:40:00 ITExam_Blog No comments

To convert the date-time format PHP provides strtotime() and date() function. We change the date format from one format to another. For example - we have stored date in MM-DD-YYYY format in a variable, and we want to change it to DD-MM-YYYY format.

We can achieve this conversion by using strtotime() and date() function. These are the built-in functions of PHP. The strtotime() first converts the date into the seconds, and then date() function is used to reconstruct the date in any format. Below some examples are given to convert the date format.

Change YYYY-MM-DD to DD-MM-YYYY

In the below example, we have date 2019-09-15 in YYYY-MM-DD format, and we will convert this to 15-09-2019 in DD-MM-YYYY format.

<?php
$orgDate = "2019-09-15";
$newDate = date("d-m-Y", strtotime($orgDate));
echo "New date format is: ".$newDate. " (MM-DD-YYYY)";
?>

Output

New date format is: 15-09-2019 (DD-MM-YYYY)

Change YYYY-MM-DD to MM-DD-YYYY

In the below example, we have date 2019-02-26 in YYYY-MM-DD format, and we will convert this to 02-26-2019 (MM-DD-YYYY) format.

<?php
$orgDate = "2019-02-26";
$newDate = date("m-d-Y", strtotime($orgDate));
echo "New date format is: ".$newDate. " (MM-DD-YYYY)";
?>

Output

New date format is: 02-26-2019 (MM-DD-YYYY)

Change DD-MM-YYYY to YYYY-MM-DD

In the below example, we have date 17-07-2012 in DD-MM-YYYY format, and we will convert this to 2012-07-17 (YYYY-MM-DD) format.

<?php
$orgDate = "17-07-2012";
$newDate = date("Y-m-d", strtotime($orgDate));
echo "New date format is: ".$newDate. " (YYYY-MM-DD)";
?>

Output

New date format is: 2012-07-17 (YYYY-MM-DD)

Change DD-MM-YYYY to YYYY/MM/DD

Suppose we have date 17-07-2012 in DD-MM-YYYY format separated by dash (-) sign. We want to convert this to 2012/07/17 (YYYY/MM/DD) format, which will be separated by the slash (/). In the below example, DD-MM-YYYY format is converted to the YYYY-MM-DD format, and also dashes (-) will be replaced with slash (/) sign.

<?php
$orgDate = "17-07-2012";
$date = str_replace('-"', '/', $orgDate);
$newDate = date("Y/m/d", strtotime($date));
echo "New date format is: ".$newDate. " (YYYY/MM/DD)";
?>

Output

 date format is: 2012/07/17 (YYYY/MM/DD)

Change date time to another format

Here in the below example, we will convert the date format MM-DD-YYYY to YYYY-DD-MM format and 12 hours time clock to 24 hours time clock.

<?php
$date = "06/13/2019 5:35 PM";
//converts date and time to seconds
$sec = strtotime($date);
//converts seconds into a specific format
$newdate = date ("Y/d/m H:i", $sec);
//Appends seconds with the time
$newdate = $newdate . ":00";
// display converted date and time
echo "New date time format is: ".$newDate;
?>

Output

New date time format is: 2019/13/06 17:35:00

MySQL

mysql error 1067 invalid default value for timestamp

10:51:00 ITExam_Blog No comments

Start Cmd.
Type mysql -u root -p Then Press Enter.
And Select Your Database (Ex "USE DATABASE <DATABASE NAME>").
Tpe this SET @@session.sql_mode ="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
And Then Press Enter.

error, MySQL

Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

14:56:00 ITExam_Blog No comments

If you have that same problem in MySql 5.7.+ :

Access denied for user 'root'@'localhost'

it's because MySql 5.7 by default allow to connect with socket, which means you just connect with sudo mysql. If you run sql :

SELECT user,authentication_string,plugin,host FROM mysql.user;

then you will see it :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

To allow connection with root and password, then update the values in the table with command :

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;

Then run the select command again and you'll see it has changed :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *2F2377C1BC54BE827DC8A4EE051CBD57490FB8C6 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

And that's it. You can run this process after running and completing the sudo mysql_secure_installation command.

MySQL backup script

17:19:00 ITExam_Blog No comments


#! /bin/bash
### MySQL Server Login Info ###
MUSER="root"
MPASS="roott123"
MHOST="localhost"
MPORT="3306"
DBNAME="radius"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"


### File Info ###
# Save backup in temp directory before upload to server
TEMPDIR="/root/backup_radius" #Exp: ./bkp_temp

# Name of bkp file (before the date)
FILENAME="Radius_Backup_" #Exp: my_prj_daily_bkp_
NOW=$(date +%F)


#Create and clean temp directory
[ ! -d "$TEMPDIR" ] && mkdir -p "$TEMPDIR"
rm -f "$TEMPDIR"/*.*

# Create bkp
TFILENAME="$FILENAME""$NOW".sql
FILE=$TEMPDIR/$TFILENAME
$MYSQLDUMP -u $MUSER -h $MHOST -P $MPORT  -p$MPASS $DBNAME > $FILE
~

Centos, Linux, MySQL, php, rpm

How to Install PHP 7 in CentOS 6

11:30:00 ITExam_Blog No comments

The CentOS 6 official software repositories has PHP 5.3 which has reached end of life and no longer actively maintained by the developers.
To keep up with the latest features and security updates, you need a newer (probably the latest) version of PHP on your CentOS 6 system.

For the purpose of this guide, we will be operating the system as root, if that is not the case for you, make use of the sudo command to acquire root privileges.

Installing PHP 7 on CentOS 7

1. To install latest PHP 7, you need to add EPEL and Remi repository to your CentOS 6 system like so.

# yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
# yum install http://rpms.remirepo.net/enterprise/remi-release-6.rpm

2. Now install yum-utils, a group of useful tools that enhance yum’s default package management features.
Yum-utils can be used for manipulating package repositories and additional package management operations such as enabling or disabling packages without any manual configuration by a system administrator.
You can install it as follows:

# yum install yum-utils

3. In this step, you need to enable Remi repository using yum-config-manager utility, as the default repository for installing PHP.

# yum-config-manager --enable remi-php70   [Install PHP 7.0]

If you want to install PHP 7.1 or PHP 7.2 on CentOS 6, just enable it as shown.

# yum-config-manager --enable remi-php71   [Install PHP 7.1]
# yum-config-manager --enable remi-php72   [Install PHP 7.2]

4. Then finally install PHP 7 on CentOS 6 with all necessary PHP modules using the following command.

# yum install php php-mcrypt php-cli php-gd php-curl php-mysql php-ldap php-zip php-fileinfo

Install PHP 7 on CentOS 6

If you are curious, double check the installed version of PHP on your system as follows.

# php -V

Check PHP Version in CentOS 6

You might also want to check out these useful PHP articles:

Centos

Updated and tested (as of this post date) version of /etc/yum.repos.d/CentOS-Base.repo for Centos 5.11 (final):

18:01:00 ITExam_Blog No comments

# CentOS-Base.repo
#
# The mirror system uses the connecting IP address of the client and the
# update status of each mirror to pick mirrors that are updated to and
# geographically close to the client.  You should use this for CentOS updates
# unless you are manually picking other mirrors.
#
# If the mirrorlist= does not work for you, as a fall back you can try the 
# remarked out baseurl= line instead.
#
#

[base]
name=CentOS-$releasever - Base
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
baseurl=http://vault.centos.org/5.11/os/$basearch
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#released updates 
[updates]
name=CentOS-$releasever - Updates
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
baseurl=http://vault.centos.org/5.11/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#additional packages that may be useful
[extras]
name=CentOS-$releasever - Extras
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=extras
#baseurl=http://mirror.centos.org/centos/$releasever/extras/$basearch/
baseurl=http://vault.centos.org/5.11/extras/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#additional packages that extend functionality of existing packages
[centosplus]
name=CentOS-$releasever - Plus
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=centosplus
#baseurl=http://mirror.centos.org/centos/$releasever/centosplus/$basearch/
baseurl=http://vault.centos.org/5.11/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#contrib - packages by Centos Users
[contrib]
name=CentOS-$releasever - Contrib
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=contrib
#baseurl=http://mirror.centos.org/centos/$releasever/contrib/$basearch/
baseurl=http://vault.centos.org/5.11/contrib/$basearch/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

My Daily Works Archive

Example MySQL my.cnf optimized

checkmac.php

How to change date format in PHP?

Change YYYY-MM-DD to DD-MM-YYYY

Change YYYY-MM-DD to MM-DD-YYYY

Change DD-MM-YYYY to YYYY-MM-DD

Change DD-MM-YYYY to YYYY/MM/DD

Change date time to another format

mysql error 1067 invalid default value for timestamp

Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

MySQL backup script

How to Install PHP 7 in CentOS 6

Installing PHP 7 on CentOS 7

Updated and tested (as of this post date) version of /etc/yum.repos.d/CentOS-Base.repo for Centos 5.11 (final):

Popular Posts

Recent Posts

Categories

Blog Archive

About

Privacy Policy