Using REGEX Case Sensitively

Consider a table with data as below. + — — — + — — — — — — + — — — — — — +| ID | FIRSTNAME | LASTNAME |+ — — — + — — — — — — + — — — — — — +|

Understanding Checksum Table in MySQL

CHECKSUM is a concept that is used to verify if the related files/contents are not tampered with or corrupted. On similar lines, MySQL provides CHECKSUM TABLE to verify the CHECKSUM of a table. You can do this before taking a table backup and after restoring the table to ensure that

Renaming a User in MySQL

There are instances in which a user is created according to current requirements. However, as the design progress and requirements freezes or changes, there is a possible need to change the user name itself. Let us look at the example where a user named PERFORMANCE has to be renamed to

Setting Up a PROXY User in MySQL

In MySQL, a user is allowed to have a PROXY user. This means that a user can behave the same way as the original user with the same permissions as the original user. Granting PROXY Access GRANT PROXY ON original_user TO proxy_user Revoking PROXY Access REVOKE PROXY ON original_user FROM

Disabling Binary Logs in MySQL

Logs often come in handy for problem resolution. However, there are times when there are too many logs that are not useful from the developer’s perspective. One such log is the binary log in the MySQL database. First, convert the file my.cnf in location /etc/my.cnf to unix format in case

How to Change the Storage Engine in MySQL

Every table in MySQL will have a storage engine that offers certain capabilities. Some storage engines offer performance, some of them transaction support, and so on. By default, all tables will be created with InnoDB, which supports transactions. Your requirement could be such that for a given table, or set

Understanding What Grants a User Has in MySQL

MySQL allows various permissions to be set for users during (or after) creation. The following command comes handy to understand what GRANTS a user has. Command: SHOW GRANTS FOR user; In the above command, replace the user with the real username to know the GRANTS associated with the user. Sample:

How to Use the Show ProcessList in MySQL

This command is similar to that of an operating system command that shows all the processes. In MySQL, this command, when executed, lists all of the processes that hold a valid connection to the database. mysql SHOW PROCESSLIST Typical output is as below +——-+—————–+——————+—————-+———+——-+—————————–+——————+| Id | User | Host |

Using the Insert on Duplicate Key Update in MySQL

An interesting statement in MySQL is the INSERT ON DUPLICATE KEY UPDATE. This statement tries to INSERT?a record, and on finding a duplicate key, performs the action provided alongside. Example: INSERT INTO CITIES(ID, CITY, COUNTRY, CONTINENT)VALUES(10, ‘Ottawa’, ‘Canada’, ‘North America’)ON DUPLICATE KEY UPDATE ID = VALUES(ID) + 1, CITY=’Ottawa City’;

No more posts to show