(1) log
1.1 error log
- introduce
- Concept: it records the relevant information when mysqld starts and stops, and when any serious errors occur in the running process of the server. When the database fails, it is recommended to view the secondary log first.
- grammar
show variables like '%log_error%' Associated files are:/var/log/mysqld.log
- case
- View the file location corresponding to the error log
mysql> show variables like '%log_error%'; +----------------------------+----------------------------------------+ | Variable_name | Value | +----------------------------+----------------------------------------+ | binlog_error_action | ABORT_SERVER | | log_error | /var/log/mysqld.log | | log_error_services | log_filter_internal; log_sink_internal | | log_error_suppression_list | | | log_error_verbosity | 2 | +----------------------------+----------------------------------------+ 5 rows in set (0.00 sec)
- Modify the id configuration file of mysql service
-- Modify profile [root@hadoop ~]# vim /var/lib/mysql/auto.cnf server-uuid=175f2c08-651f-11ec-967b-000c29569efa -- restart mysqld Service, and then report an error [root@hadoop ~]# systemctl restart mysqld Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
- Check the error log with details
[root@hadoop ~]# tail -f /var/log/mysqld.log 2022-08-29T19:16:57.818881Z 0 [ERROR] [MY-010073] [Server] The server_uuid stored in auto.cnf file is not a valid UUID. 2022-08-29T19:16:57.818932Z 0 [ERROR] [MY-010076] [Server] Initialization of the server's UUID failed because it could not be read from the auto.cnf file. If this is a new server, the initialization failed because it was not possible to generate a new UUID. 2022-08-29T19:16:57.818959Z 0 [ERROR] [MY-010119] [Server] Aborting 2022-08-29T19:16:57.819421Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.26) MySQL Community Server - GPL.
1.2 binary log
- introduce
-
Concept: binary binlog records all DDL and DML languages, but does not include select and show statements
-
effect:
- Data recovery in case of disaster
- Master slave replication of MySQL
- grammar
show variables like '%log_bin%';
mysqlbinlog [ Parameter options ] logfilename Parameter options: -d Specify the database name and list only the operations related to the specified database. -o Ignore the front in the log n Line command. -v Line event(Data change)Reconstituted as SQL sentence -vv Line event(Data change)Reconstituted as SQL Statement and output comment information
- case
- Log file location
mysql> show variables like '%log_bin%'; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | Default on state | log_bin_basename | /var/lib/mysql/binlog | File location | log_bin_index | /var/lib/mysql/binlog.index | Indexes
-- Look at the file [root@hadoop ~]# cd /var/lib/mysql [root@hadoop mysql]# ll total 322960 -rw-r----- 1 mysql mysql 56 Aug 30 03:20 auto.cnf -rw-r----- 1 mysql mysql 179 Aug 15 04:43 binlog.000001 -rw-r----- 1 mysql mysql 179 Aug 15 05:55 binlog.000002 -rw-r----- 1 mysql mysql 179 Aug 15 06:01 binlog.000003 -rw-r----- 1 mysql mysql 208 Aug 30 03:20 binlog.index -- Look at the index file [root@hadoop mysql]# cat binlog.index ./binlog.000001 ./binlog.000002 ./binlog.000003
- Under the log format of ROW: change the ROW data and view the binary file
mysql> update course set name = 'PHP' where id =2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
[root@hadoop mysql]# mysqlbinlog -v binlog.000013 - V reconstructs row events (data changes) into SQL statements ### UPDATE `itcast`.`course` ### WHERE ### @1 = 2 -- before update ### @2='SpringBoot' ### SET -- after update ### @1=2 ### @2='PHP' # at 444
- Under the log format of statement: change the row data and view the binary file
-- go mysql Configuration file modification log format of [root@hadoop mysql]# vim /etc/my.cnf #Change the log format to "statement" binlog_format=STATEMENT -- Modify row data mysql> update course set name = 'SpringBoot' where id =2; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- Viewing binary files [root@hadoop mysql]# mysqlbinlog binlog.000014 does not use - v because it records sql statements update course set name = 'SpringBoot' where id =2 select Add, delete or modify records if they are not recorded, DDL and DML meeting /*!*/; # at 469
- Log format:
- statement: log record based on SQL statements. SQL statements are recorded. Modifications to data are recorded in the log file
- Row: row based log, which records the data change of each row (default). It will be recorded before and after the change
- Mixed: statement and row format are mixed, and statement is adopted. Some special cases are automatically converted into row for recording
- grammar
show variables like '%binlog_format%';
- case
mysql> show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec)
- Log deletion
- Reason: a busy business system generates huge binlog data every day. If it is not cleared for a long time, it will occupy a large amount of disk space
- grammar
- reset master: delete all binlog logs. After deletion, binlog will start again from binlog.00000 1
- purge master logs to 'binlog. No': delete all logs before the specified number
- purge master logs before 'yyyy MM DD hh24: Mi: Ss': delete logs generated before the log is' yyyy MM DD hh24: Mi: Ss'
- You can also configure the expiration time of the binary log in the mysql based configuration file. After setting, the expiration time of the binary log will be automatically deleted
mysql> show variables like '%binlog_expire_logs_seconds%'; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | binlog_expire_logs_seconds | 2592000 | +----------------------------+---------+
- case
mysql> purge master logs to 'binlog.000002'; Query OK, 0 rows affected (0.01 sec)
[root@hadoop mysql]# ll total 322948 -rw-r----- 1 mysql mysql 56 Aug 30 03:20 auto.cnf -rw-r----- 1 mysql mysql 179 Aug 15 05:55 binlog.000002 -rw-r----- 1 mysql mysql 179 Aug 15 06:01 binlog.000003
1.3 query log
- introduce
- Concept: it contains all the operation statements of the client (add, delete, change and query), while the binary log does not contain the SQL statements of query data. Default off
- grammar
show variables like '%general%';
- case
- Check whether it is turned on
mysql> show variables like '%general%'; +------------------+---------------------------+ | Variable_name | Value | +------------------+---------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/hadoop.log | +------------------+---------------------------+ 2 rows in set (0.00 sec)
- Change the default closing of the query log, open the query log and modify the query log name
[root@hadoop mysql]# vim /etc/my.cnf #Open query log general_log=1 #Modify query log file name general_log_file=/var/lib/mysql/mysql_query.log [root@hadoop mysql]# systemctl restart mysqld
- See if there is mysql_query.log file
[root@hadoop mysql]# cd /var/lib/mysql [root@hadoop mysql]# ll -rw-r----- 1 mysql mysql 179 Aug 30 04:49 mysql_query.log Really
- View MySQL_ Contents of query.log
[root@hadoop mysql]# tail -f mysql_query.log /usr/sbin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument 2022-08-29T20:54:39.492052Z 8 Connect root@localhost on using Socket 2022-08-29T20:54:39.492461Z 8 Init DB itcast 2022-08-29T20:54:39.493048Z 8 Query show databases 2022-08-29T20:54:39.495374Z 8 Query show tables 2022-08-29T20:54:44.542363Z 8 Query show tables 2022-08-29T20:54:57.807081Z 8 Query select * from course 2022-08-29T20:55:49.831565Z 8 Query update course set name = 'redis' where id = 2
1.3 slow query log
I won't repeat it. Let's go back to the operations related to the slow query log in the advanced 2 index
However, it still needs to be supplemented
-
By default, management statements are not recorded, and queries that do not use indexes for lookup are not recorded.
You need to create a MySQL configuration file in / etc / my The following parameters are configured in CNF:
You can use log_slow_admin_statements and change this behavior log_queries_not_using_indexes
#Record management statements that execute slowly log_slow_admin_statements =1 #Records statements that execute slowly and do not use indexes log_queries_not_using_indexes = 1
(2) master slave replication
2.1 overview
- meaning:
-
Master-slave replication refers to transferring the DDL and DML operations of the master database to the slave database through binary logs, and then re executing (redoing) these logs on the slave database to keep the data of the slave database and the master database synchronized
-
MySQL supports the replication of one master database to multiple Slave databases at the same time. The Slave database can also become the master database of other servers to realize chain replication. The master library is called Matser and the Slave library is called Slave.
- effect
- If there is a problem with the master library, you can quickly switch to the slave library to provide services
- Realize the separation of reading and writing, reduce the access pressure of the master library, read from the slave library and write from the master library
- Backup can be performed from the secondary library to avoid affecting the primary library service during backup
2.2 principle
- The transaction commit, addition, deletion and modification of the master database will be written to the log file binlog
- There are two threads in the slave library. One is called IOthread. It sends a request to connect to the master database, reads the binlog in the master, and then writes it to its relay log
- The slave has another thread, called SQLthread, which is mainly responsible for reading the data of the relay log just converted from the master's binlog by IOthread, and reflecting the data changes in this log to the changes in its own database
2.3 erection
-
Basic configuration: two servers, and open the specified port 3306, or close the server firewall directly, and MySQL is installed on both servers
-
Main library configuration
- Modify the configuration file / etc/my.cnf
#mysql service ID, which is unique in the whole cluster environment. The value range is 1 – 232-1. The default value is 1 server-id=1 #Read only, 1 for read-only, 0 for read-write read-only=0 #Ignored data refers to the database that does not need to be synchronized. Unset means that all databases need to be synchronized #binlog-ignore-db=mysql #Specify the database to synchronize #binlog-do-db=db01
[root@hadoop ~]# vim /etc/my.cnf #mysql service ID, which is unique in the whole cluster environment. The value range is 1 – 232-1. The default value is 1 server-id=1 #Read only, 1 for read-only, 0 for read-write read-only=0
From now on, my heart will not move..............