Skip to main content

Connect MySQL from Remote CentOS7

Connect MySQL from Remote CentOS7

I just Installed CentOS7 and faced some issues in installing and connecting MySQL from remote location. I used to do a yum install mysql-server, which is no more supported and for that I have to install mysql repo rpm first. i will write that some other time, login to centos7 box with root and disable the […]

Change MySQL Data Directory

Change MySQL Data Directory

Changing MySQL data directory in CentOS is a very 6 simple steps. You need root access to your CentOS. Login and do followings stop the mysql copy all the current data to your destination directory (you need to decide where you want to move your mysql data) give your new directory proper rights Add new […]

ERROR 1206 (HY000): The total number of ...

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

while running queries on a Large number of rows and your DB engine is InnoDB, you will face this error. Basic reason for this is, Mysql is out of Lock Space, You need to increase the value of “innodb_buffer_pool_size” edit my.cnf see the highlighted line “innodb_buffer_pool_size=2G” for me I needed to manage much rows so […]

MySQL – Show Running Queries

MySQL – Show Running Queries

MySQL Show Running Queries it is deadly simple :D. login to your MySQL , and use this query “show processlist” it will only show the first 100 characters of the query. if you need to see the complete query To show the full query run to get data in rows instead of columns,

MySQL Substring Count

MySQL Substring Count

I needed to count the parts of a string (varchar) which are separated by a `;` (semi colon) if I use PHP, i always need to fetch the rows and then explode or any thing else. which was really time consuming A very useful function for counting parts of string using Mysql. and here is […]

MySQL Master Slave Replication

MySQL Master Slave Replication

I am not going to discuss the advantages and disadvantages of MySQL replication, for that I will write some other time, but some basics are needed “MySQL Master” is a MySQL database server which we need to replicate to some other place. (you can say Source database server) “MySQL Slave” is a MySQL database server […]

MySQL my.cnf not found – CentOS

MySQL my.cnf not found – CentOS

I installed MySQL and needed to configure it for a test replication. I needed to edit the my.cnf. I tried to find the my.cnf on my CentOS BOX but file was not there. after looking around on net for hours, Found that my.cnf is not created automatically, and one has to create it himself. Now […]

Install or update MySQL 5.5 on CentOS

Install or update MySQL 5.5 on CentOS

You need to download MySQL RPM from MySQL Community. For CentOS 6 you need to download something like this (pay attention to Oracle & Red hat Linux 6 ) You need to download these things MySQL-server MySQL-client MySQL-shared MySQL-devel (optional) Login to your CentOS box and get these first thing you need to check is […]

MySQL Illegal mix of collations

MySQL Illegal mix of collations

A character encoding details how each symbol is represented in binary (and therefore stored in the computer). For example, the symbol é (U+00E9, latin small letter E with acute) is encoded as 0xc3a9 in UTF-8 (which MySQL calls utf8) and 0xe9 in Windows-1252 (which MySQL calls latin1). A character set is the alphabet of symbols […]