Error: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BL OB prefix of 768 bytes is stored inline. Suggestions to fix the errors: The error is about a row exceeding mysql max Full Article…
Steps to restore an innodb database
Below are the steps to restore mysql database with innodb tables , without contacting r1soft. Set up a temporary mysql “sandbox” instance using the innodb files and the fil ebased backups of the database and create a mysql dump of the database. 1. restore these files to a temporary directory /dbrestore /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1 (restore to “alternate Full Article…
Optimize database using phpMyAdmin
Over a time as your website add contents to it’s database, the files/tables will become fragmented resulting in slower performance. Optimizing the database will essentially defragment the files, and is something you should perform regularily if you do have a busy website. Log into cPanel, and click on the PHPMyAdmin icon in the database section, Full Article…
Reset mysql password if root password is not known
One of the common issue encountered by server admins while manging mysql is that, the root passwords are unknown. We can reset the root password for getting into the mysql prompt by following the below steps. Stop mysql service /etc/init.d/mysql stop Start to MySQL server w/o password mysqld_safe –skip-grant-tables & Connect to mysql server Full Article…
Performance Tuning Scripts for Mysql
Performance Tuning Primer Script is the shell script which gets performance related information from MySQL engine and produce some recommendations for tuning of MySQL server variables. It’s easy to use and offers valuable output for MySQL performance optimization. tuning-primer.sh Performance Tuning Primer Script Usage [*] Download the shell script here. [*] Upload the shell Full Article…
How to find cpanel, ensim, plesk and direct admin mysql root password
Cpanel ♦ You can get the mysql root password in a cpanel password from the file /root/.my.cnf Ensim ♦ To get the mysql password in an ensim server, you need to run the following command in the server # ensim-python -c “import sys;sys.path.append(\”/usr/lib/opcenter/mysql\”);import mysqlbe;print mysqlbe.read_mysqlpass()” Plesk On server with Parallels Plesk Panel versions 8.x, 9.x Full Article…
InnoDB
MyISAM and InnoDB are two most commonly used storage engines of MySQL database. However, MyISAM is the default storage engine chosen by MySQL database, when creating a new table. The default engine is MyISAM which is very fast but does not support higher level functionality such as foreign keys or transactions. # mysql> SHOW Full Article…
Enable Innodb in cPanel server
To Enable innodb in cpanel server♦ First check if Innodb is enabled or not. # mysqladmin variables | grep have_innodb ( To check Innodb enabled or not ) | have_innodb | No OR mysql> show engines; ♦ Edit the configuration file # vi /etc/my.cnf #skip-inndob ( comment the line to activate Innodb) ♦ Restart Mysql # /etc/init.d/mysql restart Set Full Article…
MySQL optimization
MySQL OPTIMIZATION MySQL is a popular choice of database for use in web applications. Fully optimizing MySQL takes both time and effort since every application has different requirements. The MySQL database server performance depends on the number of factors. The Optimized Query is one of the factors for the MySQL robust performance. The MySQL Full Article…
Disable port 3306 listening to server
Here goes the steps for disabling inbound MySQL connections to a linux server. ♦ Login to the server as root ♦ open the file /etc/my.cnf ♦ add the directive skip-networking ♦ save and quit from the file. Now you may need to disable the port 3306 in TCP_IN section in firewall(if firewall enabled). Make Full Article…