Consider a table with data as below. + — — — + — — — — — — + — — — — — — +| ID | FIRSTNAME | LASTNAME |+ — — — + — — — — — — + — — — — — — +|
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
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
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
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
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
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:
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 |
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’;