• Introduction to MySQL Source command


    Reflections on an online problem At work today, my development colleagues took a. Zip compressed package file and said that they wanted to pour the data into the database. Originally, they thought it was formed SQL. They just need to copy and paste it and pour it into the database. When I got it, I […]

  • Optimized implementation of count() for MySQL large tables


    The following is my judgment based on the combination of the data structure of the B + tree and the speculation of the experimental results. If there is any error, please correct it! Today, I experimented with the count () operation optimization of MySQL. The following discussion is based on mysql5.7 InnoDB storage engine. X86 […]

  • Implementation method of adding field annotation to MySQL create table


    Direct Po codes and cases #Write comments when creating tables CREATE TABLE userinfo( Id int comment ‘number’, Uname varchar (40) comment ‘user name’, Address varchar (120) comment ‘home address’, Hobby varchar (200) comment ‘hobby’ )Comment = ‘user information table’; #Modify comments for tables Alter table userinfo comment ‘user information table’; #Modify the comments of the […]

  • Method for cleaning data in MySQL online database


    01 scenario analysis This afternoon, the development colleagues asked to delete some data records online. After a brief look at the data distribution, it is about to delete more than 27 million records in the data table, and the total records in the data table are more than 28 million, that is, the records to […]

  • MySQL online log database migration instance


    Let’s talk about a recent case. There has been a problem with a game log library on the online alicloud RDS recently. With the increase of the number of games, the amount of data in the online log library is increasing. The latest log library has reached the size of 50g, and the online change […]

  • Non standard implementation code of MySQL UPDATE statement


    Today, I’d like to introduce a difference between the update statement in MySQL database and the implementation of SQL standard (and other databases). If we don’t pay attention to this problem, it may lead to unexpected results. Let’s first create a simple example table: CREATE TABLE t1( id int, col1 int, col2 int ); INSERT […]

  • Solution to MySQL replication failure caused by disk full


    Case scenario Today, a problem was found online. Because the monitoring was not covered, the disk of a machine was full, resulting in the problem of online MySQL master-slave replication. The questions are as follows: localhost.(none)>show slave status\G *************************** 1. row ***************************                Slave_IO_State:                   Master_Host: 10.xx.xx.xx                   Master_User: replica                   Master_Port: 5511                 Connect_Retry: 60               Master_Log_File:           Read_Master_Log_Pos: 4                Relay_Log_File: relay-bin.001605                 Relay_Log_Pos: 9489761         Relay_Master_Log_File:              Slave_IO_Running: No             Slave_SQL_Running: No                    Last_Errno: 13121                    Last_Error: Relay log read failure: Could not parse relay log event entry.  The possible reasons are: the master’s binary log is corrupted (you can check this by running  ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by  running ‘mysqlbinlog’ on the relay log), a network problem, the server was unable to fetch a  keyring key required to open an encrypted relay log file, or a bug in the master’s or  […]

  • Steps to obtain connection string from sqlserver and MySQL


    Steps: 1、 Create a new text document xxx.txt and modify the file type to. UDL 2、 Open the file and find the corresponding service in provide data. 3、 Select the required database in the connection and fill in relevant information. 4、 Test the connection. If it succeeds, skip to the next step. If it fails, […]

  • Repair scheme for inconsistency between active and standby MySQL gtids


    Scheme 1: rebuild replicas MySQL version 5.6 and above introduces new global transaction ID (gtid) support in replication. When performing MySQL and MySQL 5.7 backups with gtid mode enabled, percona xtrabackup will automatically store the gtid value in xtrabackup_ binlog_ Info. This information can be used to create new (or repair damaged) gtid based copies. […]

  • Sample code of how MySQL / MariaDB implements PivotTable report


    The previous article introduced several methods to implement PivotTable in Oracle. Today, let’s see how to implement the same functions in MySQL / MariaDB. The sample data used in this article can be downloaded here. Use case expressions and group aggregation The essence of PivotTable report is to group data according to different combinations of […]

  • Comprehensive analysis of MySQL master-slave replication mechanism


    As a relational database, MySQL provides a built-in data replication mechanism, which makes it possible to realize advanced features such as high availability architecture based on its replication mechanism, so that MySQL can be suitable for the production environment without the help of additional plug-ins or other tools. This is one of the conditions for […]

  • CentOS 6.2 Yum install MySQL initialization warning solution


    yum install mysql-server The following warnings occurred during initialization [[email protected] etc]# /etc/rc.d/init.d/mysqld start Initializing MySQL database: WARNING: The host ‘fengzhige’ could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that […]