LinuxHostingSupport

You may receive the “Access denied for user ‘root’@'localhost’” message while accessing mysql from the command prompt. The error message states that the Mysql password for user ‘root’ is incorrect and need to reset the password using skip-grant-tables option.

ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO)

How to reset a Mysql password for ‘root’?

# /etc/init.d/mysql stop

Make sure all the mysql processes are stopped by executing the killall command

# killall -9 mysqld

Next, connect to mysql server using the skip-grant-tables method.

# /usr/bin/mysqld_safe –skip-grant-tables &

now, execute ‘mysql’ and you will be at the mysql prompt

# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23056
Server version: xx.xx-community MySQL Community Edition (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Goto the ‘mysql’ database and update the password for user ‘root’ in the “user” table.

mysql> use mysql;

To set a password, execute

mysql> update user set password=PASSWORD(“passhere”) where user=’root’;

OR to set a blank password, execute the mysql ‘update user’ query

mysql> update user set password=PASSWORD(“”) where user=’root’;

Once done, reload privileges and quit

mysql> flush privileges;
mysql> quit

Now, restart the mysql service

# /etc/init.d/mysql restart

and you should be able to connect mysql server:

# mysql
OR
# mysql -uroot -p

General Query Log is used to keep track of mysql status i.e. it writes the information when a client connects/disconnects OR a query is executed. It is useful when the number of people managing the database is high. In order to enable ‘General Query Log’,

edit the Mysql configuration file

vi /etc/my.cnf

enable the log under the ‘mysqld’ section

log=/var/log/mysql.general.log

Save the file. Now create the log file and set the mysql ownership

touch /var/log/mysql.general.log
chown mysql.mysql /var/log/mysql.general.log

Now, restart the mysql service

/etc/init.d/mysql restart

You can now execute the queries using phpMyAdmin OR 3rd party sql software and watch the logs

tail -f /var/log/mysql.general.log

Error:

root@host [~]#  psql databasename < dump.sql

ERROR:  invalid byte sequence for encoding “UTF8″: 0xd1e9
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by “client_encoding”.

The above error is received while restoring a pgsql dump and when the clicnt_encoding of the database is mismatched.

Solution:

1) Edit the dump.sql file

vi dump.sql

2) Change the line

SET client_encoding = ‘SQL_ASCII’;

to

SET client_encoding = ‘latin-1′;

i.e. you need to change the client_encoding from ‘SQL_ASCII’ to ‘latin-1′.

3) Save the file and restore the database once again.

How to enable “log-queries-not-using-indexes” in Mysql?

Edit /etc/my.cnf and place the following line under the [mysqld] section:

log-queries-not-using-indexes

Save the file and restart the mysql service:

# /etc/init.d/mysql restart

To verify if it is set to ON, use the following command:

# mysqladmin variables |grep  log_queries_not_using_indexes

You need to use the “mysqldump” command to backup a Mysql database.

Backup/Dump a Mysql database on a Linux Or Linux/cPanel server:

root@host [~]# mysqldump /var/lib/mysql/dbname > dbname.sql

Restore the .sql file in a database:

root@host [~]# mysql /var/lib/mysql/dbname < /path/to/filename.sql

Backup/Dump a Mysql database on a Linux/Plesk server:

root@host [~]# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` /var/lib/mysql/dbname > dbname.sql

Restore the .sql file on a Plesk server :

root@host [~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow` /var/lib/mysql/dbname < /path/to/filename.sql