LinuxHostingSupport

In case you remove a lot of data from the tables OR change the database structure, a de-fragmentation/optimizing of the database is necessary to avoid performance loss, especially while running queries. The above changes results in a performance loss, so make sure you run the “optimizer” on the database.

SSH to your server and execute:

mysqlcheck -o <databasename>

where, -o stands for optimize which is similar to defragmentation. You should look to defragment the tables regularly when using VARCHAR fields since these coloumns get fragmented too often.

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