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 the skip-grant-tables method.

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

How to reset root Mysql password in Linux?

First stop the mysql service

# /etc/init.d/mysql stop

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

# killall -9 mysqld

Next, start mysql is safe mode i.e. the privileges will be skipped while connecting to the mysql server

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

now, connect to your Mysql prompt using the ‘mysql’ command

# mysql
 Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 23056
 mysql>

Goto the ‘mysql’ database

mysql> use mysql;

Now set a password for user ‘root’

mysql> update user set password=PASSWORD("passhere") where user='root';

OR you can also set a blank password for user ‘root’

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 to your 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