Category: Database

Project GitHub address: https://github.com/siddontang/go-mysql-elasticsearch The basic principle of go-mysql-elasticsearch is: when the program starts for the first time, it first uses the mysqldump tool to perform a full sync of the source MySQL database, then writes data to ES through the elasticsearch client; then it implements a mysql client that connects to the source MySQL as a slave. The source MySQL as master will synchronize all data update operations to the slave via binlog events. By parsing the binlog events, you can obtain the updated content of the data, and then write it to ES.

1.Setting during table creation:
create table table123(
`id` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Test Table';
2. …

Trigger Synchronization Between Two Tables in the Same Database:

#Insert
delimiter ||
DROP TRIGGER IF EXISTS t_afterinsert_on_tab1 ||
CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON test1
FOR EACH ROW
BEGIN
insert into test2 …

Due to a database upgrade, an error occurs when importing an SQL file from a lower version database into a higher version database:

The reason is the database SQL mode: mysql> show variables like 'sql_mode'; #Check current mode
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_T …

To verify the plugin issue, the administrator should log in and perform the following checks:

use mysql;

SELECT `user`, `host`, `authentication_string`, `plugin` FROM mysql.user;

To fix the user (please use the actual values):

ALTER USER 'root'@'%' IDENTIFIED …

Below is an example of creating user user123 with database user123 and password a123456.

1. Modify the mysqld configuration file my.cnf to adjust the value of max_allowed_packet. Setting it to 5M is usually appropriate. [mysqld]port = 3308socket = /dev/shm/mysqld.sockskip-external-lockingkey_buffer_size = 16Mmax_allowed_ …

Query the logs to locate the recovery starting point and export the SQL: Here we use the 10_163_0_72-bin.000009 binlog as an example:

mysqlbinlog –no-defaults –start-datetime="2019-01-11 18:38:56" –database=superstar /home/10_163_0_72-bi …

MySQL timeout error as follows:

The last packet successfully received from the server was

Use the following command to query the timeout duration (in seconds, default is 8 hours):

show global variables like 'wait_timeout';

In my.ini, mys …

MySQL 5.7 Key Features: Better Performance
With better optimization for multi-core CPUs, solid-state drives, and locking, 1 million QPS is no longer MySQL’s goal—whether the next version can reach 2 million QPS is what users care more about. Better InnoDB Storage Engine and More Robust Replication
Replication brings data integri …