Example MySQL my.cnf optimized

 

[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/

Continue reading Example MySQL my.cnf optimized

checkmac.php

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]')");
}
?>

Continue reading checkmac.php

How to change date format in PHP?

 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.

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

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.

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

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.

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

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.

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

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.

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

Output

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

Continue reading How to change date format in PHP?

mysql error 1067 invalid default value for timestamp

1. Start Cmd.
2. Type mysql -u root -p Then Press Enter.
3. And Select Your Database (Ex "USE DATABASE <DATABASE NAME>").
4. 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";
5. And Then Press Enter.

Continue reading mysql error 1067 invalid default value for timestamp

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

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.

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

MySQL backup script

#! /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
~

Continue reading MySQL backup script