Can't select a user and unable to assign privileges in MYSQL GUI Administrator

Problem

Running MySQL Administrator (in the GUI bundle) on MySQL 5.x-community-nt, I cannot use the User Administration: although I do see the list of currently defined users, whichever I select, the page says "No user selected" and all fields are grayed. I tried running the Administrator both as root and myroot (which was Granted all privileges), but it’ as always the same: "No user selected".

Resolution

Step-1      

Connect Mysql admin console via command prompt as shown in below screenshot

After connect by entering root password the console is ready to pass query as shown in screenshot.

Step-2

Enter the below query as shown in the screenshot (this query create a new table called user_info)

The above Query in screenshot is given below for your reference.

CREATE TABLE  `mysql`.`user_info` ( `User` varchar(16) COLLATE utf8_bin NOT NULL,  `Full_name` varchar(60) COLLATE utf8_bin DEFAULT NULL,  `Description` varchar(255) COLLATE utf8_bin DEFAULT NULL,  `Email` varchar(80) COLLATE utf8_bin DEFAULT NULL,  `Contact_information` text COLLATE utf8_bin,  `Icon` blob,  PRIMARY KEY (`User`),  KEY `user_info_Full_name` (`Full_name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Stores additional user information';

Note: you need to drop the table if already “user_info” is exist by using a below query

DROP TABLE IF EXISTS `mysql`.`user_info`;

Step-3

Now connect Mysql GUI admin console as localhost.

Now check “User Administration” tab you will able to see the users in active editable stage

And also you can able to edit the Schema Privileges for particular DB to particular user.

Posted by Unknown 0 comments

Read More

Automatic Mysql DB Backup Scripts

 Automatic Mysql DB Backup Scripts With Compress and auto Deletion of Old Archive Files

:: UPDATE 3.30.2012  Added error logging to help trouble shoot databases backup errors.   --log-error="c:\MySQLBackups\backupfiles\dumperrors.txt"

::If the time is less than two digits insert a zero so there is no space to break the filename

set year=%DATE:~10,4%

set day=%DATE:~7,2%

set mnt=%DATE:~4,2%

set hr=%TIME:~0,2%

set min=%TIME:~3,2%

IF %day% LSS 10 SET day=0%day:~1,1%

IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%

IF %hr% LSS 10 SET hr=0%hr:~1,1%

IF %min% LSS 10 SET min=0%min:~1,1%

set backupdate=%year%-%day%-%mnt%-%hr%-%min%

:: MySQl DB user

set dbuser=aaa

:: MySQl DB users password

set dbpass=yyy

:: Switch to the MySQL data directory and collect the folder names

pushd "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data"

:: Loop through the folders and use the fnames for the sql filenames, collects all databases automatically this way

echo "hello"

echo "Pass each name to mysqldump.exe and output an individual .sql file for each"

FOR /D %%F IN (*) DO (

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases --log-error="c:\MySQLBackups\backupfiles\dumperrors.txt" %%F > "c:\MySQLBackups\backupfiles\%%F.%backupdate%.sql"

)

echo "Zipping all files ending in .sql in the folder"

"c:\MySQLBackups\zip\7zG.exe" a -tzip "c:\MySQLBackups\backupfiles\FullBackup.%backupdate%.zip" "c:\MySQLBackups\backupfiles\*.sql"

echo "Deleting all the files ending in .sql only"

del "c:\MySQLBackups\backupfiles\*.sql"

echo "Deleting zip files older than 30 days now"

Forfiles -p c:\MySQLBackups\backupfiles\ -s -m *.* -d -30 -c "cmd /c del /q @path"

echo "All done, pretty slick eh"

Posted by Unknown 0 comments

Read More