Mysql

Enable MySQL Query Log File

MySQL has built-in functionality that allows you to log SQL queries to a file , You can enable the full SQL queries logs to a file or only slow running queries log.  It is easy for us to troubleshoot/ debug the sql statement if SQL queries log enable , The […]

Linux HugeTLBfs: Improve MySQL Database Application Performance

Applications that perform a lot of memory accesses (several GBs) may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses. HugeTLBfs is memory management feature offered in Linux kernel, which is valuable for applications that use a large virtual address space. It is especially […]

MySQL Error: Unknown table engine ‘InnoDB’

Error: Unknown table engine ‘InnoDB’ What happened ? I edited the file /etc/mysql/my.cnf by adding these lines: innodb_file_per_table innodb_buffer_pool_size=12G innodb_additional_mem_pool_size=20M innodb_flush_method=O_DIRECT Then I restarted mysql and after that I noted the website was not working correctly due an error in the mysql related to the innodb engine not found. So […]

mysqldump: Got error: 1045: Access denied for user

Today working on a client project, a shell script which uses MySQL to dump the database, has got me into this error mysqldump: Got error: 1045: Access denied for user   I was typically amazed getting this error because all my configurations were correct. I have created proper users to […]

Copy MySQL Database From One Server To Another Remote Server

Usually you run mysqldump to create a database copy and backups as follows: $ mysqldump -u user -p db-name > db-name.out Copy db-name.out file using sftp/ssh to remote MySQL server: $ scp db-name.out user@remote.box.com:/backup Restore database at remote server (login over ssh): $ mysql -u user -p db-name < db-name.out […]

List of Basic MySQL Commands

This is a list of handy MySQL commands that I use time and time again. At the bottom are statements, clauses, and functions you can use in MySQL. Below that are PHP and Perl API functions you can use to interface with MySQL. To use those you will need to […]

invalid byte sequence for encoding “UTF8″

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 […]

HowTo: Migrate / Move MySQL Database And Users To New Server

I already wrote about how to move or migrate user accounts from old Linux / UNIX server to a new server including mails and home directories. However, in reality you also need to move MySQL database which may host your blog, forum or just your data stored in MySQL database. […]