Shulou(Shulou.com)06/01 Report--

Let's talk about the operation flow of Mysql. The secret of the text is that it is close to the topic. So, no gossip, let's read the following directly, I believe you will benefit from reading this article of Mysql operation flow.

The premise is that you need to open the binlog log of the table

Log-bin=test-bin

First, check the start and end positions of binlog. Pos is the start and End_log_pos is the end.

[root@slave mysql] # mysql-uroot-e "use test;show binlog events"

+-+

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-+

| | test-bin.000001 | 4 | Format_desc | 134 | 245 | Server ver: 5.5.52-MariaDB, Binlog ver: 4 |

| | test-bin.000001 | 245 | Query | 1 | 360 | use test; create table T44 (id int not null,name char (20)) | |

| | test-bin.000001 | 360 | Query | 134,465 | use test; DROP TABLE T44 / generated by server / | |

| | test-bin.000001 | 465 | Stop | 134 | 484 |

+-+

[root@master mysql] # mysqlbinlog test-bin.000002

The screenshot above is based on the binlog location for data recovery.

2. Full backup

[root@master mysql] mysqldump-uroot-p-B-F-R-x-- master-data=2 ops | gzip > / opt/backup/ops_$ (date +% F) .sql.gz

Enter password:

[root@master mysql] # ls / opt/backup/

Ops_2016-09-25.sql.gz

Parameter description:

-B: specify the database

-F: refresh the log

-R: backup stored procedures, etc.

-x: lock the table

-- master-data: add CHANGE MASTER statement, binlog file and location information to the backup statement

3. How many IP are used to query the connection

Select SUBSTRING_INDEX (host,':',1) as ip, count (*) from information_schema.processlist group by ip

Fourth, query the situation of locking tables.

Show status like 'Table%'

Table_locks_immediate indicates the number of table locks to be released immediately, and Table_locks_waited indicates the number of table locks to wait.

If Table_locks_immediate / Table_locks_waited > 5000, it is best to use InnoDB engine

Because InnoDB is a row lock and MyISAM is a table lock, InnoDB works better for applications with high concurrent writes.

In the example, the CVM Table_locks_immediate / Table_locks_waited = 235 and MyISAM is sufficient.

5. About reporting errors

The error report of this is a problem of permission. Because the number of binlog stored in each other's master and slave is different, I passed it directly from another database and did not change the permission.

Is there anything you don't understand about the above Mysql operation process? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.