Xrvel

Tag: MySQL

  • cPanel Can Not Add MySQL User to Database

    Recently i could not add MySQL user to MySQL Database in cPanel. There was no error message displayed on screen. The operation just failed.

    Then i tried to use cPanel MySQL Database Wizard to create a new database, new user, and add that new user to the new database. Then i saw the error message in my browser.
    (more…)

  • Regularly Runs MySQL FLUSH QUERY CACHE

    In some situation, like suggested in some mysql optimization script, you may want to regularly run “FLUSH QUERY CACHE”.

    First, open your terminal and try to run this command :

    mysql -e 'FLUSH QUERY CACHE'

    If there is no error, you can add that command in your cron job, such as this command to run it every 10 minutes :

    */10 * * * * mysql -e 'FLUSH QUERY CACHE'

  • Setting MySQL innodb_buffer_pool_size

    I was confused how big i should set my “innodb_buffer_pool_size” value.

    Then i find this post in stackexchange.

    I can run this query to get what is the suggestion of “innodb_buffer_pool_size” value.

    SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

    The result will be in GB.

  • Exporting MySQL Databases Command Line in Linux

    To export a mysql database by linux command line,
    for example your database name is my_database, your mysql username is my_username, and your mysql password is my_password, you can use this command :

    mysqldump --opt --single-transaction -umy_username -pmy_password my_database > /your/path/here/export.sql

    Note: after “-u” and “-p”, there is NO space. 🙂

  • MySQL Table “In Use” Repair

    Recently when i optimized some mysql tables from phpmyadmin, some of the tables were “locked” and showed as “in use” on phpmyadmin. The tables could not be used, read, etc. So the script was stopped working.
    I have tried to repair / check / analyze from phpmyadmin but i couldn’t repair it.
    So i tried to repair the mysql tables from SSH.

    Go to your database directory. Usually it is located on
    /etc/var/lib/mysql/YOUR_DATABASE_NAME/

    So use this command first
    cd /etc/var/lib/mysql/YOUR_DATABASE_NAME/

    Next, use this command
    myisamchk --safe-recover --force YOUR_TABLE_NAME

    And the MySQL table should be repaired now.

  • Multilanguage Programming with UTF-8 Encoding

    Multilanguage non-ASCII site? Sometimes we need to make website that can handle languages which contains non-ASCII characters, for example chinese, russian, french, germany, etc.
    To handle this, we need to handle the language on 3 parts : on PHP itself, HTML, and on the database (i use MySQL as example here).
    Basically, we need to adjust the charset (character set) into UTF-8.
    (more…)