Creating mysql user and database without prefix

Create mysql user and database without prefix.

By default cPanel used to create database and DB user with cPanel username _as prefix. But now cPanel version 68 allows you to disable prefixing so you can create single DBs and DB users.

You can enable disable prefix feature from

WHM > Tweak Setting >  Require a username prefix on names of new databases and database user

Example: If you create database with name wp as cPanel user india. It will create database as india_wp but if you disable this then it will allow you to create database with name wp only.

Automated Backup of mysqlDB on cpanel

Here we will learn how to backup all databases of cPanel server in specified folder say /home/mysqlbackup

First of all we will list all databases in a file using command

  • echo “show databases” | mysql > /home/DB_list.txt

By default first line of the output is DATABASE and rest of lines are name of databases.

Second command will help to get rid of first line.

  • tail -n +2 /home/DB_list.txt > /home/DB_list2.txt

So we have DB_list2.txt with list of all databases.

Here we will use for loop to backup all databases listed in DB_list2.txt

  • for m in `cat /home/DB_list2.txt`;do mysqldump $m > /home/mysqlbackup/$m.sql;done

 

Listing cPanel users UID more than 1000

Listing cPanel users ( UID more than 1000) from hash prompt.

Command to list all cpanel users from hash prompt

awk -F: ‘{if ($3 > 1000) {print $1}}’ /etc/passwd

To execute some shell script we need list of all cPanel users.   On cPanel server default setting is in such way it will create cPanel user with UID more than 1000.  That make very easy to list all cPanel users from /etc/passwd file using awk command.

1st column and 3rd column of /etc/passwd file contain username and UID. Above command checks if UID (3rd column value) is more than 1000 , if yes then print 1st column that is cPanel username.  As all columns are separated by “:” hence we used it as separator in command (-F:).