Checking & Reparing MySQL Script


MySQL Status Summary
mysqladmin -u root -pthepasswd process status

Check Database Mydb
/etc/init.d/mysqld stop; cd /var/lib/mysql; myisamchk –silent –force mydb/*.MYI

Repair Mydb  
/etc/init.d/mysqld start; mysqlcheck -u root -pthepassed –auto-repair –check –optimize –databases mydb

Copy MySQL Database to New Database


Dump Old-db
mysqldump –compress -u user -pPassword -h –skip-quote-names –databases olddb > olddb.dump

Dump Stored Procedures  
mysqldump –compress -u user -pPassword -h –routines –no-create-info –no-data –no-create-db –skip-opt olddb  > olddb_routines.dump

Create New User & Permissions  
CREATE USER ‘newuser’ IDENTIFIED BY ‘newPAssword’;
GRANT ALL ON newdb.* TO ‘newuser’@’%’;

Replace Both Dumps olddb(newdb), olduser(newuser)
perl -p -i.bak -e “s/DEFINER=`\w.`@`\d[0-3].[0-3]`//g” olddb_routines.dump

Create New db
cp olddb.dump olddb.sql
cp olddb_routines.dump olddb_routines_routines.sql

mysql -u user -pPassword -h < olddb.sql
mysql -u user -pPassword -h < olddb_routines_routines.sql

MySQL Creating User & Grant Access


Ask User to Enter Database Name
read -p “Please Enter Database Name:” dbname

Checking if Database Exist
mysql -Bse “USE $dbname” 2> /dev/null
if [ $? -eq 0 ]; then
read -p “Please enter the username you wish to create : ” username
read -p “Please Enter Host To Allow Access Eg: %,ip or hostname : ” host
read -p “Please Enter the Password for New User ($username) : ” password

MySQL Query Will Create New User & Grant Privileges
query=”GRANT ALL PRIVILEGES ON $dbname.* TO $username@’$host’ IDENTIFIED BY ‘$password'”;

Ask User to Confirm Entered Data
read -p “Executing Query : $query , Please Confirm (y/n) : ” confirm
if [ “$confirm” == ‘y’ ]; then
mysql -e “$query”

Update Privileges
mysql -e “flush privileges”
read -p “Aborted, Press any key to continue..”
echo “The Database: $dbname does not exist, please specify a database that exists”;

MySQL Creating User Account


Create a full user account
/usr/sbin/useradd -m -k /home/template -g apache $1
echo “$2” | /usr/bin/passwd –stdin $1
chmod 750 /home/$1
echo “create database $1; GRANT all on $1.* to $1@’localhost’ identified by ‘$2′” | \
/usr/local/mysql/bin/mysql -hlocalhost -uroot -p$3
echo “set password for ‘$1’@’localhost’ = OLD_PASSWORD(‘$2’)” | \
/usr/local/mysql/bin/mysql -hlocalhost -uroot -p$3

Multiple Database Backup & Clean Up Older Files Script


Intializing Date
DATE=date +%Y%m%d

Using Mysqldump taking Multiple Backups
mysqldump -h localhost –opt -u backups –password=some_pass website1dbname > /home/username/backups/database/website1dbname-backup-$DATE.sql
zip /home/username/backups/database/website1dbname-backup-$ /home/username/backups/database/website1dbname-backup-$DATE.sql
rm /home/username/backups/database/website1dbname-backup-$DATE.sql

mysqldump -h localhost –opt -u backups –password=some_pass website2dbname > /home/username/backups/database/website2dbname-backup-$DATE.sql
zip /home/username/backups/database/website2dbname-backup-$ /home/username/backups/database/website2dbname-backup-$DATE.sql
rm /home/username/backups/database/website2dbname-backup-$DATE.sql

Deleting Older Files
find /home/username/backups/database/* -type f -mtime +2 -delete

MySQL Backup Database Using Shell Script


    Create Directory with Date where Database backup will be stored
month=$(date | awk ‘{print $2}’)
day=$(date | awk ‘{print $3}’ )
year=$(date | awk ‘{print $6}’)
foldername=$(echo $day$month$year”_backups”)

    List all the databases in /usr/local/dblist file.
mysql -u root -p’mysqlpassword’ -e ‘show databases’ >/usr/local/dblist
list=$(cat /usr/local/dblist)
echo $foldername

    Create Backup Directory in /Backup/mysqlbackup
mkdir -p /Backup/mysqlbackup/$foldername
for i in $list
echo $i
mysqldump -u root -p’mysqlpassword’ $i | gzip > /Backup/mysqlbackup/$foldername/$i.sql.gz
echo ” “$i”.sql.gz file saved..”

MySQL Backup Databases In Compressed Format


Check if user input hostname, if not – exit from script
if [ “$#” = “0” ]; then
echo “Usage: basename $0
exit 2

we save hostname that user input to MySQL variable

Folder to keep backups

we getting current timestamp, to use in filenames
NOW=date +%Y%m%d-%H%M%S


We use MySQL access Without Password

This will create folder, where we will keep all dumps
mkdir ${BACKUP_DIR}/${NAME}

Telling user that we start to process hostnname
echo “Processing ${MYSQL}…”

Database name without description and database with name information_schema
DBS=echo "show databases;" | mysql --host=${MYSQL} --user=root --password=${PASSWD} | \
awk '{if ($1 != "Database" &amp;&amp; $1 != "information_schema") {print $1}}'

Now we will process every database, we will create dump for each
for DB in ${DBS}; do

telling user that we starting to dump each database
echo -n ” Dumping ${DB}… ”

Something to Database
if [ ${DB} != “mysql” ]; then

Command that will Create Dump
mysqldump –host=${MYSQL} –user=root –password=${PASSWD} –force \
–allow-keywords –complete-insert –add-drop-table –add-locks –flush-logs \
${LOCKS} –result-file=${BACKUP_DIR}/${NAME}/${DB}-date +%Y%m%d-%H%M%S.sql ${DB} < /dev/null

Telling User that Process Done For this Database
echo “Done”

Tar command we Compress Directory
tar -czf ${BACKUP_DIR}/mysql-${NAME}.tar.gz ${BACKUP_DIR}/${NAME} >/dev/null 2>&1
echo Done

Removing UNeeded Directory
rm -rf ${BACKUP_DIR}/${NAME}

MySQL Database Connection Issue

Warning: mysql_connect(): Access denied for user: ‘user1_name1@localhost’ (Using password: YES) /home/public_html/catalog/includes/functions/database.php on line 19
Unable to connect to database server

Configuration File
Edit Configuration File
define(‘DB_NAME’, ‘radius’);
define(‘DB_USER’, ‘radius_02’);
define(‘DB_PASSWORD’, ‘5Jmfde%53L’);
define(‘DB_HOST’, ‘localhost’);

Update CPanel Username
define(‘DB_NAME’, ‘radius’);

Replace Current DB_USER & DB_PASSWORD
define(‘DB_USER’, ‘radius_03’);
define(‘DB_PASSWORD’, ‘nvGHdCC0dCMz’);

MySQL Error 1146 “table doesn’t exist”

mysqldump: Got error: 1146: Table ‘db_name.table_name’ doesn’t exist when using LOCK TABLES

Checking Error
mysqlcheck -u mysql_username -p database_name

Checking & Repair Tables
Error: Table ‘database_name.table_name’ doesn’t exist
status: Operation failed

Re-Entering The Data in MySQL
mysql -u mysql_user -p
mysql> use database_name
mysql> show tables;
mysql> quit