Why am I seeing “[ERROR] /usr/sbin/mysqld: Can’t open file: ‘./databasename/tablename.frm’ (errno: 24)”?

Problem: MySQL stops responding for a few minutes and during that time, the logs show messages like “Error in accept: Too many open files” and “Can’t open file”: Dec 2 07:14:34 www mysqld: 141202 7:14:34 [ERROR] Error in accept: Too many open files Dec 2 07:15:06 www mysqld: 141202 7:15:06 [ERROR] /usr/sbin/mysqld: Can’t open file: …

Continue reading ‘Why am I seeing “[ERROR] /usr/sbin/mysqld: Can’t open file: ‘./databasename/tablename.frm’ (errno: 24)”?’ »

Why am I seeing “ERROR 1290 (HY000): The MySQL server is running with the –read-only option so it cannot execute this statement”?

Why am I seeing “ERROR 1290 (HY000): The MySQL server is running with the –read-only option so it cannot execute this statement”? If I look in the “ps” output, the mysqld server is NOT running with the –read-only option! The mysql “root” user can write to the database, but other users only have read-only access, …

Continue reading ‘Why am I seeing “ERROR 1290 (HY000): The MySQL server is running with the –read-only option so it cannot execute this statement”?’ »

Why am I seeing “ERROR 1130 (HY000): Host ‘192.168.0.2’ is not allowed to connect to this MySQL server”?

When you connect to a MySQL server, it checks it’s grants table (the “user” table in the “mysql” database on the MySQL server) against the IP address of the connecting MySQL client machine. If there are NO MATCHING ENTRIES in the “host” column in the “user” table in the “mysql” database, mysqld will IMMEDIATELY CLOSE …

Continue reading ‘Why am I seeing “ERROR 1130 (HY000): Host ‘192.168.0.2’ is not allowed to connect to this MySQL server”?’ »

How big is my MySQL database?

1) To see how much disk space is the database is using: mysql> SELECT SUM(data_length+index_length) FROM information_schema.tables WHERE engine <> “MEMORY”; +——————————-+ | SUM(data_length+index_length) | +——————————-+ | 4151912352 | +——————————-+ 1 row in set (8.53 sec) mysql> 2) To see all of the databases: mysql> SELECT table_schema AS “Database”, SUM(data_length + index_length) / 1024 / …

Continue reading ‘How big is my MySQL database?’ »

Help! I forgot my MySQL root password!

1) Stop the mysqld daemon process. 2) Start the mysqld daemon process with the –skip-grant-tables option. 3) Start the mysql client with the -u root option. 4) Execute: UPDATE mysql.user SET Password=PASSWORD(‘password’) WHERE User=’root’; 5) Execute the FLUSH PRIVILEGES; command.