Category: MySQL Basics
-
MySQL Full Vs Incremental Backups
A full backup includes all data managed by a MySQL server at a given point in time. An incremental backup consists of the changes made to the data during a given time span (from one point in time to another). MySQL has different ways to perform full backups, such as those described earlier in this…
//
-
MySQL Full Versus Point-in-Time (Incremental) Recovery
A full recovery restores all data from a full backup. This restores the server instance to the state that it had when the backup was made. If that state is not sufficiently current, a full recovery can be followed by recovery of incremental backups made since the full backup, to bring the server to a…
//
-
SHOW PROCESSLIST Statement
The MySQL process list indicates the operations currently being performed by the set of threads executing within the server. The SHOW PROCESSLIST statement is one source of process information. For a comparison of this statement with other sources, see Sources of Process Information. If you have the PROCESS privilege, you can see all threads, even those belonging to other users.…
//
-
MySQL Uptime
MySQL Uptime SELECT VARIABLE_VALUE AS Uptime_seconds, NOW() AS “Now”, NOW() – INTERVAL VARIABLE_VALUE SECOND AS “Up since”, DATEDIFF(NOW(), NOW() – INTERVAL VARIABLE_VALUE SECOND) AS “Uptime_days” FROM performance_schema.session_status WHERE VARIABLE_NAME = ‘Uptime’; Output Example: +—————-+———————+—————————-+————-+ | Uptime_seconds | Now | Up since …
//
-
MySQL join_buffer_size, sort_buffer_size, read_buffer_size and read_rnd_buffer_size
join_buffer_size, sort_buffer_size, read_buffer_size read_rnd_buffer_size These four buffers are allocated per connection. So think twice before alerting. For example, if you set join buffer size=2 M and max connections=800, MySQL will allocate an extra 2 M per connection (2 M x 800). The same is true for the remaining three buffers. Again, everything is per-connection. In almost…
//
-
innodb_change_buffer_max_size
With MySQL 5.6.2, you can set the maximum size of the change buffer as a percentage of the total size of the buffer pool using the innodb change buffer max size configuration option. The default value for innodb change buffer max size is 25. The maximum number of characters is 50. On a MySQL server…
//
-
CPU Components
CPU consists of 6 main components: 1. CU Control unit 2. ALU Arithmetic logic unit 3. Registers 4. Cache 5. Buses 6. Clock All the components work together to allow processing and system control. Control Unit (CU) It fetches, decodes, and executes instructions. It issues control signals that control hardware. It moves data around the…
//
-
Cache Memory
Hey guys, In this blog I am going to explain you about Cache Memory Cache is a small amount of high-speed random-access memory (RAM) built directly within the processor. It is used to temporarily hold data and instructions that the processor is likely to reuse. This allows for faster processing as the processor does not…
//
-
Slow query log purge in MySQL
There are manly 3 steps… 1. Rename existing log file with _old. 2. Connect mysqladmin using socket and use flush-logs. 3. Delete _old file created in first step. Get the show query log location: mysql> show variables like slow_query_log %’; Get the socket file location: mysql> show variables like ‘socket%’; In my case I got…
//
-
Enable Data Load Local InFile
We were getting below error at the time of loading data into MySQL. Error: Programming Error: 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides. Solution: Check the status of local_infile variable in MySQL… > show variables like ‘%local%’; +—————+——-+ | Variable_name | Value | +—————+——-+ |…
//
-
MySQL master – slave replication setup
Hi guys, I hope you are doing great. Today here I am going to explain about MySQL master – slave replication setup. For the setup I have two servers clota-labl01 and clota-labl02. Here on clota-labl01 I am going to setup master and clota-labl02 as slave DB. Connect master server… ++++++++++++++++++++ clota-labl01 ++++++++++++++++++++ my.cnf configuration… =================…
//
-
Out of Memory Killer in Linux OS
In Linux systems, MySQL generally has a concept called Out of Memory killer OOM controlled by the kernel. This is to prevent the possible runaway process in OS to avoid race conditions and a server crash. Since MySQL and its optimized memory buffers are memory hogs, the OS may often kill the mysqld process to…
//