Jul 05

#!/bin/bash
#destination directory named with daily date
DIR=”/var/backup/mysql/$(date +%m%d%Y)”
if [ -d $DIR ]; then
    echo “Dir $DIR exists”;
else
    echo “Creating $DIR”;
    mkdir $DIR
fi

USER=”root”
ROOT_PASSWORD=”root_password”

#get list of databases
LIST=`mysql -u $USER -p$ROOT_PASSWORD -e”show databases” -s |egrep -v ‘Database|information_schema|mysql|temp’`

Continue reading »

written by MG \\ tags: , ,

May 19

Plesk disables the ‘root’ account when Plesk is installed. To get root privileges please login with the ‘admin’ username. The password is the same as the admin’s password in Plesk.

You can find it in /etc/psa/.psa.shadow.

written by MG \\ tags: ,

Apr 11
  • 1. Stop the mysqld daemon process.
  • 2. Start the mysqld daemon process with the –skip-grant-tables option.
  • 3. Start the mysql client with the -u root option.
  • 4. Execute the UPDATE mysql.user SET Password=PASSWORD(‘password’) WHERE User=’root’;
  • 5. Execute the FLUSH PRIVILEGES; command.

These steps reset the password for the “root” account to “password”. To change the password for a different account, or to set a different password, just edit the variables in single-quotes in step 4.

If you know your existing MySQL root password, steps 1-3 are not necessary.

 

written by MG \\ tags: , ,

Jan 11

If you want to get optimized and speedy response from your MySQL server then you need to add following two configurations directive to your MySQL server:

query_cache_size=SIZE
The amount of memory (SIZE) allocated for caching query results. The default value is 0, which disables the query cache.

query_cache_type=OPTION
Set the query cache type. Possible options are as follows:
0 : Don’t cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE

You can setup caching as follows:
$ mysql -u root –p

Output:

Enter password:
mysql>

Now setup cache size 16Mb:

mysql> SET GLOBAL query_cache_size = 16777216;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';

Output:

+——————+———–+
| Variable_name     | Value         |
+——————+———–+
| query_cache_size| 16777216   |
+——————+———–+  

You can setup them in /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file:
# vi /etc/my.cnf
Append config directives as follows:

query_cache_size = 268435456 query_cache_type=1 query_cache_limit=1048576

In above example the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. Memory size in Kb.

written by MG