Open Hub
Black Duck Open Hub

 Settings |  Report Duplicate

I Use This!
High Activity

News

Analyzed about 18 hours ago. based on code collected 1 day ago.
Debezium MySQL Snapshot From Read Replica With GTID
Posted over 4 years ago by Bhuvanesh R
When you installed the Debezium MySQL connector, then it’ll start read your historical data and push all of them into the Kafka topics. This setting can we changed via snapshot.mode parameter in the connector. But if you are going to start a new ... [More] sync, then Debezium will load the existing data its called Snapshot. Unfortunately, if you have a busy transactional MySQL database, then it may lead to some performance issues. And your DBA will never agree to read the data from Master Node.[Disclaimer: I’m a DBA :) ]. So I was thinking of figuring out to take the snapshot from the Read Replica, once the snapshot is done, then start read the realtime data from the Master. I found this useful information in a StackOverflow answer. If your binlog uses GTID, you should be able to make a CDC tool like Debezium read the snapshot from the replica, then when that’s done, switch to the master to read the binlog. But if you don’t use GTID, that’s a little more tricky. The tool would have to know the binlog position on the master corresponding to the snapshot on the replica. Source: https://stackoverflow.com/a/58250791/6885516 Then I tried to implement in a realtime scenario and verified the statement is true. Yes, we made this in our system. Here is the step by step details from our PoC. Requirements: Master and Slave should be enabled with GTID. Debezium Connector Node can talk to both master and slave. log-slave-updates must be enabled on the slave(anyhow for GTID its requires). A user account for Debezium with respective permissions. Install Debezium connector. Sample data: Create a new database to test this sync and insert some values. create database bhuvi; use bhuvi; create table rohi ( id int, fn varchar(10), ln varchar(10), phone int); insert into rohi values (1, 'rohit', 'last',87611); insert into rohi values (2, 'rohit', 'last',87611); insert into rohi values (3, 'rohit', 'last',87611); insert into rohi values (4, 'rohit', 'last',87611); insert into rohi values (5, 'rohit', 'last',87611); Create the MySQL Connector Config: File Name: mysql.json { "name": "mysql-connector-db01", "config": { "name": "mysql-connector-db01", "connector.class": "io.debezium.connector.mysql.MySqlConnector", "database.server.id": "1", "tasks.max": "1", "database.history.kafka.bootstrap.servers": "YOUR-BOOTSTRAP-SERVER:9092", "database.history.kafka.topic": "schema-changes.mysql", "database.server.name": "mysql-db01", "database.hostname": "IP-OF-READER-NODE", "database.port": "3306", "database.user": "bhuvi", "database.password": "****", "database.whitelist": "bhuvi", "snapshot.mode": "initial", "snapshot.locking.mode": "none", "key.converter": "org.apache.kafka.connect.json.JsonConverter", "value.converter": "org.apache.kafka.connect.json.JsonConverter", "key.converter.schemas.enable": "false", "value.converter.schemas.enable": "false", "internal.key.converter": "org.apache.kafka.connect.json.JsonConverter", "internal.value.converter": "org.apache.kafka.connect.json.JsonConverter", "internal.key.converter.schemas.enable": "false", "internal.value.converter.schemas.enable": "false", "transforms": "unwrap", "transforms.unwrap.add.source.fields": "ts_ms", "tombstones.on.delete": "false", "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState } } Watch the status of the connector: Open three terminal windows and start listening to the following topics. NOTE: change the bootstrap-server as per your cluster’s IP. connect-configs connect-status --Terminal-1 kafka-console-consumer –bootstrap-server localhost:9092 –topic connect-configs –from-beginning --Terminal-2 kafka-console-consumer –bootstrap-server localhost:9092 –topic connect-status –from-beginning Install the Connector: curl -X POST -H "Accept: application/json" -H "Content-Type: application/json" http://localhost:8083/connectors -d @mysql.json Once you installed, from your connect-configs topic, you will get the following output. {"properties":{"connector.class":"io.debezium.connector.mysql.MySqlConnector","snapshot.locking.mode":"none","database.user":"bhuvi","database.server.id":"1","tasks.max":"1","database.history.kafka.bootstrap.servers":"172.31.40.132:9092","database.history.kafka.topic":"schema-changes.mysql","database.server.name":"mysql-db01","internal.key.converter.schemas.enable":"false","database.port":"3306","key.converter.schemas.enable":"false","internal.key.converter":"org.apache.kafka.connect.json.JsonConverter","task.class":"io.debezium.connector.mysql.MySqlConnectorTask","database.hostname":"172.31.25.99","database.password":"*****","internal.value.converter.schemas.enable":"false","name":"mysql-connector-db01","value.converter.schemas.enable":"false","internal.value.converter":"org.apache.kafka.connect.json.JsonConverter","value.converter":"org.apache.kafka.connect.json.JsonConverter","database.whitelist":"bhuvi","key.converter":"org.apache.kafka.connect.json.JsonConverter","snapshot.mode":"initial"}} {"tasks":1} And then from your connect-statustopic, you’ll get the status of your MySQL connector. {"state":"RUNNING","trace":null,"worker_id":"172.31.36.115:8083","generation":2} {"state":"RUNNING","trace":null,"worker_id":"172.31.36.115:8083","generation":3} Snapshot Status from the log file: By default, the Kafka connector’s logs will go to syslog. You can customize this log location. So wherever you have the log file, you can see the snapshot progress there. [2019-12-28 11:06:04,246] INFO Step 7: scanning contents of 1 tables while still in transaction (io.debezium.connector.mysql.SnapshotReader) [2019-12-28 11:06:04,252] INFO Step 7: - scanning table 'bhuvi.rohi' (1 of 1 tables) (io.debezium.connector.mysql.SnapshotReader) [2019-12-28 11:06:04,252] INFO For table 'bhuvi.rohi' using select statement: 'SELECT * FROM `bhuvi`.`rohi`' (io.debezium.connector.mysql.SnapshotReader) [2019-12-28 11:06:04,264] INFO Step 7: - Completed scanning a total of 31 rows from table 'bhuvi.rohi' after 00:00:00.012 (io.debezium.connector.mysql.SnapshotReader) [2019-12-28 11:06:04,265] INFO Step 7: scanned 5 rows in 1 tables in 00:00:00.018 (io.debezium.connector.mysql.SnapshotReader) [2019-12-28 11:06:04,265] INFO Step 8: committing transaction (io.debezium.connector.mysql.SnapshotReader) [2019-12-28 11:06:04,267] INFO Completed snapshot in 00:00:01.896 (io.debezium.connector.mysql.SnapshotReader) [2019-12-28 11:06:04,348] WARN [Producer clientId=connector-producer-mysql-connector-db01-0] Error while fetching metadata with correlation id 7 : {mysql-db01.bhuvi.rohi=LEADER_NOT_AVAILABLE} (org.apache.kafka.clients.NetworkClient) [2019-12-28 11:06:04,460] INFO Transitioning from the snapshot reader to the binlog reader (io.debezium.connector.mysql.ChainedReader) [2019-12-28 11:06:04,492] INFO GTID set purged on server: 88726004-2734-11ea-ae86-0e7687279b85:1-7 (io.debezium.connector.mysql.BinlogReader) [2019-12-28 11:06:04,492] INFO Attempting to generate a filtered GTID set (io.debezium.connector.mysql.MySqlTaskContext) [2019-12-28 11:06:04,492] INFO GTID set from previous recorded offset: 88726004-2734-11ea-ae86-0e7687279b85:1-11 (io.debezium.connector.mysql.MySqlTaskContext) [2019-12-28 11:06:04,492] INFO GTID set available on server: 88726004-2734-11ea-ae86-0e7687279b85:1-11 (io.debezium.connector.mysql.MySqlTaskContext) [2019-12-28 11:06:04,492] INFO Final merged GTID set to use when connecting to MySQL: 88726004-2734-11ea-ae86-0e7687279b85:1-11 (io.debezium.connector.mysql.MySqlTaskContext) [2019-12-28 11:06:04,492] INFO Registering binlog reader with GTID set: 88726004-2734-11ea-ae86-0e7687279b85:1-11 (io.debezium.connector.mysql.BinlogReader) Snapshot Complete: Once your’ snapshot process is done, then the connect-offsets topic will have the binlog information of till where it’s consumed. {"file":"ip-172-31-25-99-bin.000001","pos":1234,"gtids":"88726004-2734-11ea-ae86-0e7687279b85:1-11"} Then it’ll start applying the ongoing replication changes as well. {"ts_sec":1577531225,"file":"ip-172-31-25-99-bin.000001","pos":1299,"gtids":"88726004-2734-11ea-ae86-0e7687279b85:1-11","row":1,"server_id":1,"event":2} Now we have verified that the database’s snapshot has been done. Its time to swap the nodes. We’ll start consuming from the Master. If you enable the Monitoring for the Debezium connector, then you see the lag from the JMX or Premetheus metrics. Reference: Configuring monitoring for Debezium MySQL Connector. curl localhost:7071 | grep debezium_metrics_SecondsBehindMaster debezium_metrics_SecondsBehindMaster{context="binlog",name="mysql-db01",plugin="mysql",} 299.577536699E9 Sometimes the metrics take a few more minutes to update. So once you are able to see the last binlog information from the connet-offsets and the lag <10, then the snapshot is done. Switch to Master: The main important thing is to STOP the slave thread in your Read replica. This will prevent the changing the GTID in your connect-offsets topic. mysql-slave> STOP SLAVE; To simulate the sync, we can add 1 new row in our MySQL table. So this will never replicate to your slave. But once you switch the node, it should start reading from this row. mysql-master> insert into rohi values (6, 'rohit', 'last','87611'); We need to update the existing MySQL connector’s config and just change the "database.hostname" parameter. Note: this JSON file format is different from the one which we used to register the connector. So make sure the syntax. { "connector.class": "io.debezium.connector.mysql.MySqlConnector", "snapshot.locking.mode": "none", "tasks.max": "3", "database.history.kafka.topic": "schema-changes.mysql", "transforms": "unwrap", "internal.key.converter.schemas.enable": "false", "transforms.unwrap.add.source.fields": "ts_ms", "tombstones.on.delete": "false", "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState", "value.converter": "org.apache.kafka.connect.json.JsonConverter", "database.whitelist": "bhuvi", "key.converter": "org.apache.kafka.connect.json.JsonConverter", "database.user": "bhuvi", "database.server.id": "1", "database.history.kafka.bootstrap.servers": "YOUR-KAFKA-BOOTSTRAP-SERVER:9092", "database.server.name": "mysql-db01", "database.port": "3306", "key.converter.schemas.enable": "false", "internal.key.converter": "org.apache.kafka.connect.json.JsonConverter", "database.hostname": "MASTER-IP-ADDRESS", "database.password": "****", "internal.value.converter.schemas.enable": "false", "name": "mysql-connector-db01", "value.converter.schemas.enable": "false", "internal.value.converter": "org.apache.kafka.connect.json.JsonConverter", "snapshot.mode": "initial" } Run the below command to update the config file. File Name: mysql-update.json curl -X PUT -H "Accept: application/json" -H "Content-Type: application/json" http://localhost:8083/connectors/mysql-connector-db01/config -d @mysql-update.json Once its updated, from the connect-offsets topic, you can see that the Debezium starts reading the data from the Next GTID. {"ts_sec":1577531276,"file":"mysql-bin.000008","pos":1937,"gtids":"88726004-2734-11ea-ae86-0e7687279b85:1-13","row":1,"server_id":1,"event":2} Also from your topic, you can see the last row has been pushed. kafka-console-consumer --bootstrap-server localhost:9092 --topic mysql-db01.bhuvi.rohi --from-beginning {"id":1,"fn":"rohit","ln":"last","phone":87611,"__ts_ms":0} {"id":2,"fn":"rohit","ln":"last","phone":87611,"__ts_ms":0} {"id":3,"fn":"rohit","ln":"last","phone":87611,"__ts_ms":0} {"id":4,"fn":"rohit","ln":"last","phone":87611,"__ts_ms":0} {"id":5,"fn":"rohit","ln":"last","phone":87611,"__ts_ms":0} {"id":6,"fn":"rohit","ln":"last","phone":87611,"__ts_ms":1577531276000} This method helped us to sync the historical data from the Read replica to the Kafka topic without affecting the transactions on the Master node. Still, we are exploring this for more scenarios. I’ll keep posting new articles about this. [Less]
Comment on Add,Edit and Delete Record using Bootgrid , PHP and MySQL by Parvez
Posted over 4 years ago by parvez alam
try this ID
Comment on Exporting Data to Excel with PHP and MySQL by Parvez
Posted over 4 years ago by parvez alam
yes, you can add
MySQL InnoDB Cluster Tutorial 1 ( Group Replication + MySQL Shell )
Posted over 4 years ago by Sri Sakthivel Durai Pandian
MySQL InnoDB Cluster has introduced by the MySQL team for the High Availability ( HA ) purpose . It provides a complete high availability solution for MySQL. Alright, I am planning to write the series of the blogs about the InnoDB Cluster ... [More] configurations / Management with MySQL Shell / Monitoring etc … In this blog I am going to show the InnoDB Cluster configuration with three nodes . What is InnoDB Cluster ? MySQL InnoDB Cluster is the Combination of, MySQL shell Group Replication ( GR ) MySQL Router Lab Environment : I have prepared my lab with three servers, OS : Centos 7.7 MySQL 8.0.18 ( latest version ) The server details are , 192.168.91.11 ( hostname : sakthilabs11 ) 192.168.91.12 ( hostname : sakthilabs12 ) 192.168.91.13 ( hostname : sakthilabs13 ) Step 1 : Need to allow the complete communication between the cluster nodes based on the hostname and IP . The below entry needs to be made on all the cluster nodes individually . [root@sakthilabs11 ~]# cat /etc/hosts | grep 192 192.168.33.11 sakthilabs11 sakthilabs11 192.168.33.12 sakthilabs12 sakthilabs12 192.168.33.13 sakthilabs13 sakthilabs13 Step 2 : In this step, we need to prepare the MySQL server for the InnoDB Cluster . The below step needs to be individually executed on all the cluster nodes . cmd : dba.configureLocalInstance("username@userhost:3306"); When executing the above command , it will print the informations and ask the actions to configure the InnoDB Cluster . I just highlight them in the below output section . output ; MySQL localhost:33060+ ssl JS > dba.configureLocalInstance("root@localhost:3306");Please provide the password for 'root@localhost:3306': *Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): yConfiguring local MySQL instance listening at port 3306 for use in an InnoDB cluster… 1) Create remotely usable account for 'root' with same grants and password 2) Create a new admin account for InnoDB cluster with minimal required grants 3) Ignore and continue 4) Cancel Please select an option [1]: 2 Please provide an account name (e.g: icroot@%) to have it created with the necessary privileges or leave empty and press Enter to cancel. Account Name: InnoDBCluster Password for new account: *** Confirm password: *** NOTE: Some configuration options need to be fixed:+--------------------------+---------------+----------------+--------------------------------------------------+| Variable | Current Value | Required Value | Note |+--------------------------+---------------+----------------+--------------------------------------------------+| binlog_checksum | CRC32 | NONE | Update the server variable || enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server || gtid_mode | OFF | ON | Update read-only variable and restart the server || server_id | 1 | | Update read-only variable and restart the server |+--------------------------+---------------+----------------+--------------------------------------------------+ Some variables need to be changed, but cannot be done dynamically on the server. Do you want to perform the required configuration changes? [y/n]: y Do you want to restart the instance after configuring it? [y/n]: y Cluster admin user 'InnoDBCluster'@'%' created.Configuring instance…The instance 'localhost:3306' was configured for InnoDB cluster usage.Restarting MySQL…NOTE: MySQL server at localhost:3306 was restarted. Step 3 : After prepare all the nodes , need to login any one of the MySQL Shell with the InnoDB Cluster account ( which was created during the preparing phase ) . cmd : shell.connect('[email protected]:3306'); output : MySQL localhost:33060+ ssl JS > shell.connect('[email protected]:3306'); Creating a session to '[email protected]:3306' Please provide the password for '[email protected]:3306': *** Save password for '[email protected]:3306'? [Y]es/[N]o/Ne[v]er (default No): y Fetching schema names for autocompletion… Press ^C to stop. Closing old connection… Your MySQL connection id is 9 Server version: 8.0.18 MySQL Community Server - GPL No default schema selected; type \use to set one. MySQL 192.168.33.11:3306 ssl JS > Step 4 : Create your first node of the InnoDB Cluster . cmd : cluster = dba.createCluster('first_InnoDB_cluster'); output : MySQL 192.168.33.11:3306 ssl JS > cluster = dba.createCluster('first_InnoDB_cluster'); A new InnoDB cluster will be created on instance '192.168.33.11:3306'. Validating instance at 192.168.33.11:3306… This instance reports its own address as sakthilabs11:3306 Instance configuration is suitable. Creating InnoDB cluster 'first_InnoDB_cluster' on '192.168.33.11:3306'… Adding Seed Instance… Cluster successfully created. Use Cluster.addInstance() to add MySQL instances. At least 3 instances are needed for the cluster to be able to withstand up to one server failure. MySQL 192.168.33.11:3306 ssl JS > Step 5 : Now we have successfully created the single node cluster , Have to add the other nodes as well . When adding the other nodes, it will ask the recovery method, we need to choose them . Clone plugin is the default one . cmd : cluster.addInstance('[email protected]:3306'); output : MySQL 192.168.33.11:3306 ssl JS > cluster.addInstance('[email protected]:3306'); Please provide the password for '[email protected]:3306': *** Save password for '[email protected]:3306'? [Y]es/[N]o/Ne[v]er (default No): y Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): clone Validating instance at 192.168.33.12:3306… This instance reports its own address as sakthilabs12:3306 Instance configuration is suitable.A new instance will be added to the InnoDB cluster. Depending on the amount ofdata on the cluster this might take from a few seconds to several hours. Adding instance to the cluster… Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.Clone based state recovery is now in progress. NOTE: A server restart is expected to happen as part of the clone process. If theserver does not support the RESTART command or does not come back after awhile, you may need to manually start it back. Waiting for clone to finish…NOTE: 192.168.33.12:3306 is being cloned from sakthilabs11:3306** Stage DROP DATA: Completed** Clone TransferFILE COPY ############################################################ 100% CompletedPAGE COPY ############################################################ 100% CompletedREDO COPY ############################################################ 100% Completed** Stage RECOVERY: \NOTE: 192.168.33.12:3306 is shutting down… Waiting for server restart… ready sakthilabs12:3306 has restarted, waiting for clone to finish… Clone process has finished: 59.55 MB transferred in about 1 second (~59.55 MB/s) Incremental distributed state recovery is now in progress. Waiting for distributed recovery to finish…NOTE: '192.168.33.12:3306' is being recovered from 'sakthilabs11:3306' Distributed recovery has finished The instance '192.168.33.12:3306' was successfully added to the cluster. Similarly , I have added the third node as well . Finally , We can check the cluster status with the below command . cmd : cluster.status(); output : MySQL 192.168.33.11:3306 ssl JS > cluster.status(); { "clusterName": "first_InnoDB_cluster", "defaultReplicaSet": { "name": "default", "primary": "sakthilabs11:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "sakthilabs11:3306": { "address": "sakthilabs11:3306", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.18" }, "sakthilabs12:3306": { "address": "sakthilabs12:3306", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.18" }, "sakthilabs13:3306": { "address": "sakthilabs13:3306", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.18" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "sakthilabs11:3306" } Hope this blog will helps someone who is trying to learning the MySQL InnoDB Cluster . I will be coming back with new blog soon . Thanks !! [Less]
MySQL Docker Containers: Quick Async Replication Test Setup
Posted over 4 years ago by MySQL Performance Blog
This blog discusses a few concepts about Docker and how we can use it to run a MySQL async replication environment. Docker is a tool designed to make it easier for developers and sysadmins to create/develop, configure, and run applications with ... [More] containers. The container allows us to package all parts of the application it needs, such as libraries, dependencies like code, configurations, and runtime engine. Docker runtime containers are platform-independent so the package created can be shipped one platform to another platform. Dockerhub is the repository where you can find containerized docker images for applications like MySQL, Percona Server for MySQL, and MariaDB. Using the example below, I will show you how to set up a docker container from the Pecona Server for MySQL docker image that I download from the repository. Custom Network Instead of the Default First, I will create a network that my docker instances will use. I will be using a user-defined network instead of the default one. It is recommended to use the user-defined bridge networks to control which containers can communicate with each other. Docket daemon automatically takes care of DNS name resolution. By creating your own network, every single container using that network will have DNS resolution automagically. MacBook-Pro: $ docker network create --driver bridge isolated_nw f8cd8f09b4042b39b04a6a43fd9dc71507af22dfd6ee2817fa80360577b24d6f MacBook-Pro: $ Storage for Persisting the Data The second step is to provision the storage which my docker instances will be using. In docker, storage can be provisioned in two ways, by using a bind mount or by using a docker volume. Bind mounts are dependent on the directory structure of the host machine while docker volumes are completely managed by Docker. In my example, I am using bind mounts for the primary instance and docker volumes for the replica to illustrate how either of these options can be used. MacBook-Pro: $ mkdir /Users/myuser/mysql_primary_data Configuration File for the Primary Instance I will proceed with the creation of the configuration file which my primary instance will be using. MacBook-Pro: $ cat /Users/myuser/primaryconf/my-custom.cnf [mysqld] server-id = 100 log_bin Provisioning the Primary Instance In the fourth step, we will provision the primary instance. The docker run command will download the latest percona server image if the image does not already exist in the local repository. In this example, we already have the downloaded image so the docker run does not need to download it again. MacBook-Pro: $ docker run --network=isolated_nw -v /Users/myuser/mysql_primary_data:/var/lib/mysql -v /Users/myuser/primaryconf:/etc/percona-server.conf.d/ -p 3308:3306 -p 33061:33060 --name percona_primary -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD=MysqlTestInstance -d percona --default-authentication-plugin=mysql_native_password deb40d6941db74d845cbc5ec550572d37d4763740e3a72c015ada0c7520a0fd7 MacBook-Pro: $ I intend to set up an async replication environment, so I will get the binary log details from the primary instance. MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "show master status;" Enter password: +-------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-------------------+ | deb40d6941db-bin.000003 | 154 | | | | +-------------------------+----------+--------------+------------------+-------------------+ MacBook-Pro: $ For setting the replication we need a user and the below command connects the primary instance and grants the privilege. MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'replpass';" Enter password: MacBook-Pro: $ Docker Volume for Replication Storage Before I create my replica instance, I will provision the docker volume which my replica instance will use for storage. MacBook-Pro: $ docker volume create mysqlreplicavol mysqlreplicavol MacBook-Pro: $ Configuration File for the Replica Instance Create a custom MySQL configuration file for the replica instance. MacBook-Pro: $ cat /Users/myuser/replicaconf/my-custom.cnf [mysqld] server-id = 101 Provisioning the Replica Instance The next step is to set up a replica instance using the docker volume that I created above. MacBook-Pro: $ docker run --network=isolated_nw -v mysqlreplicavol:/var/lib/mysql -v /Users/myuser/replicaconf:/etc/percona-server.conf.d/ -p 3309:3306 -p 33062:33060 --name percona_replica -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD=MysqlTestInstance -d percona --default-authentication-plugin=mysql_native_password 98c109998a522a51c4ceca7d830a1f9af2abdd408c5e1d6d12cfd55af13d170d MacBook-Pro: $ To set up the replication, apply the change master command in the replica instance and start the replica. MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "CHANGE MASTER TO MASTER_HOST='percona_primary',MASTER_USER='repl_user', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='deb40d6941db-bin.000003', MASTER_LOG_POS=154;" Enter password: MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "start slave"; Enter password: MacBook-Pro: $ Verify the Replication Status MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "show slave status \G" |egrep 'Running|Master' | egrep -v 'SSL|TLS' Enter password: Master_Host: percona_primary Master_User: repl_user Master_Port: 3306 Master_Log_File: deb40d6941db-bin.000003 Read_Master_Log_Pos: 434 Relay_Master_Log_File: deb40d6941db-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 434 Seconds_Behind_Master: 0 Master_Server_Id: 100 Master_UUID: 49451bba-2627-11ea-be09-0242ac120002 Master_Info_File: /var/lib/mysql/master.info Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: MacBook-Pro: $ Create a test DB to confirm it is replicating to the replica instance. MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "create database mytestdb;" Enter password: MacBook-Pro: $ MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "show databases like 'mytestdb';" Enter password: +---------------------+ | Database (mytestdb) | +---------------------+ | mytestdb | +---------------------+ MacBook-Pro: $ To check the logs we can use the docker logs command, for example : MacBook-Pro: $ docker logs percona_primary Initializing database 2019-12-24T08:27:54.395857Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). ... .... 2019-12-24T08:28:19.792475Z 0 [Note] mysqld: ready for connections. Version: '5.7.26-29-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 29, Revision 11ad961 2019-12-24T08:45:04.071744Z 7 [Note] Start binlog_dump to master_thread_id(7) slave_server(101), pos(deb40d6941db-bin.000003, 154) MacBook-Pro: $ The setup that I have shown above can be used as a quick setup of an async replication configuration to run demos or test experiments. Such a configuration is not suitable for production and will not be supported for production use by Docker. Note: Percona Monitoring and Management (PMM) is also distributed as an appliance in the form of a docker image. [Less]
MySQL Docker Containers: Quick Async Replication Test Setup
Posted over 4 years ago by MySQL Performance Blog
This blog discusses a few concepts about Docker and how we can use it to run a MySQL async replication environment. Docker is a tool designed to make it easier for developers and sysadmins to create/develop, configure, and run applications with ... [More] containers. The container allows us to package all parts of the application it needs, such as libraries, dependencies like code, configurations, and runtime engine. Docker runtime containers are platform-independent so the package created can be shipped one platform to another platform. Dockerhub is the repository where you can find containerized docker images for applications like MySQL, Percona Server for MySQL, and MariaDB. Using the example below, I will show you how to set up a docker container from the Pecona Server for MySQL docker image that I download from the repository. Custom Network Instead of the Default First, I will create a network that my docker instances will use. I will be using a user-defined network instead of the default one. It is recommended to use the user-defined bridge networks to control which containers can communicate with each other. Docket daemon automatically takes care of DNS name resolution. By creating your own network, every single container using that network will have DNS resolution automagically. MacBook-Pro: $ docker network create --driver bridge isolated_nw f8cd8f09b4042b39b04a6a43fd9dc71507af22dfd6ee2817fa80360577b24d6f MacBook-Pro: $ Storage for Persisting the Data The second step is to provision the storage which my docker instances will be using. In docker, storage can be provisioned in two ways, by using a bind mount or by using a docker volume. Bind mounts are dependent on the directory structure of the host machine while docker volumes are completely managed by Docker. In my example, I am using bind mounts for the primary instance and docker volumes for the replica to illustrate how either of these options can be used. MacBook-Pro: $ mkdir /Users/myuser/mysql_primary_data Configuration File for the Primary Instance I will proceed with the creation of the configuration file which my primary instance will be using. MacBook-Pro: $ cat /Users/myuser/primaryconf/my-custom.cnf [mysqld] server-id = 100 log_bin Provisioning the Primary Instance In the fourth step, we will provision the primary instance. The docker run command will download the latest percona server image if the image does not already exist in the local repository. In this example, we already have the downloaded image so the docker run does not need to download it again. MacBook-Pro: $ docker run --network=isolated_nw -v /Users/myuser/mysql_primary_data:/var/lib/mysql -v /Users/myuser/primaryconf:/etc/percona-server.conf.d/ -p 3308:3306 -p 33061:33060 --name percona_primary -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD=MysqlTestInstance -d percona --default-authentication-plugin=mysql_native_password deb40d6941db74d845cbc5ec550572d37d4763740e3a72c015ada0c7520a0fd7 MacBook-Pro: $ I intend to set up an async replication environment, so I will get the binary log details from the primary instance. MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "show master status;" Enter password: +-------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-------------------+ | deb40d6941db-bin.000003 | 154 | | | | +-------------------------+----------+--------------+------------------+-------------------+ MacBook-Pro: $ For setting the replication we need a user and the below command connects the primary instance and grants the privilege. MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'replpass';" Enter password: MacBook-Pro: $ Docker Volume for Replication Storage Before I create my replica instance, I will provision the docker volume which my replica instance will use for storage. MacBook-Pro: $ docker volume create mysqlreplicavol mysqlreplicavol MacBook-Pro: $ Configuration File for the Replica Instance Create a custom MySQL configuration file for the replica instance. MacBook-Pro: $ cat /Users/myuser/replicaconf/my-custom.cnf [mysqld] server-id = 101 Provisioning the Replica Instance The next step is to set up a replica instance using the docker volume that I created above. MacBook-Pro: $ docker run --network=isolated_nw -v mysqlreplicavol:/var/lib/mysql -v /Users/myuser/replicaconf:/etc/percona-server.conf.d/ -p 3309:3306 -p 33062:33060 --name percona_replica -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD=MysqlTestInstance -d percona --default-authentication-plugin=mysql_native_password 98c109998a522a51c4ceca7d830a1f9af2abdd408c5e1d6d12cfd55af13d170d MacBook-Pro: $ To set up the replication, apply the change master command in the replica instance and start the replica. MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "CHANGE MASTER TO MASTER_HOST='percona_primary',MASTER_USER='repl_user', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='deb40d6941db-bin.000003', MASTER_LOG_POS=154;" Enter password: MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "start slave"; Enter password: MacBook-Pro: $ Verify the Replication Status MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "show slave status \G" |egrep 'Running|Master' | egrep -v 'SSL|TLS' Enter password: Master_Host: percona_primary Master_User: repl_user Master_Port: 3306 Master_Log_File: deb40d6941db-bin.000003 Read_Master_Log_Pos: 434 Relay_Master_Log_File: deb40d6941db-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 434 Seconds_Behind_Master: 0 Master_Server_Id: 100 Master_UUID: 49451bba-2627-11ea-be09-0242ac120002 Master_Info_File: /var/lib/mysql/master.info Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: MacBook-Pro: $ Create a test DB to confirm it is replicating to the replica instance. MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "create database mytestdb;" Enter password: MacBook-Pro: $ MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "show databases like 'mytestdb';" Enter password: +---------------------+ | Database (mytestdb) | +---------------------+ | mytestdb | +---------------------+ MacBook-Pro: $ To check the logs we can use the docker logs command, for example : MacBook-Pro: $ docker logs percona_primary Initializing database 2019-12-24T08:27:54.395857Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). ... .... 2019-12-24T08:28:19.792475Z 0 [Note] mysqld: ready for connections. Version: '5.7.26-29-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 29, Revision 11ad961 2019-12-24T08:45:04.071744Z 7 [Note] Start binlog_dump to master_thread_id(7) slave_server(101), pos(deb40d6941db-bin.000003, 154) MacBook-Pro: $ The setup that I have shown above can be used as a quick setup of an async replication configuration to run demos or test experiments. Such a configuration is not suitable for a typical production install and you need to test it well before any other use. Note: Percona Monitoring and Management (PMM) is also distributed as an appliance in the form of a docker image. [Less]
Using Referential Constraints with Partitioned Tables in InnoDB
Posted over 4 years ago by MySQL Performance Blog
One of our support customers approached us with the following problem the other day: mysql> CREATE TABLE child_table ( `id` int unsigned auto_increment, `column1` varchar(64) NOT NULL, parent_id int unsigned NOT NULL, PRIMARY KEY (`id`) ... [More] , CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent_table (id)); ERROR 1215 (HY000): Cannot add foreign key constraint They could not create a table with an FK relation! So, of course, we asked to see the parent table definition, which was: CREATE TABLE `parent_table` ( `id` int unsigned auto_increment, `column1` varchar(64) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB PARTITION BY HASH (id) PARTITIONS 4; The parent table is partitioned!  This immediately explained the problem; partitioned tables can not be part of an FK relationship, as described (in point 10) here – MySQL Error Code 1215: “Cannot add foreign key constraint”. Quoting the official MySQL manual for completeness: Partitioned tables using the InnoDB storage engine do not support foreign keys. More specifically, this means that the following two statements are true: No definition of an InnoDB table employing user-defined partitioning may contain foreign key references; no InnoDB table whose definition contains foreign key references may be partitioned. No InnoDB table definition may contain a foreign key reference to a user-partitioned table; no InnoDB table with user-defined partitioning may contain columns referenced by foreign keys. So, after verifying it was impossible to guarantee referential integrity using CONSTRAINTs, we turned to an old alternative from MyISAM era of MySQL: using a set of triggers that would intercept the DML statements before they execute, and verify if the parent row actually exists. So for this, we would create child_table without the constraint: CREATE TABLE child_table ( `id` int unsigned auto_increment, `column1` varchar(64) NOT NULL, parent_id int unsigned NOT NULL, PRIMARY KEY (`id`)); And then we create 4 triggers: BEFORE INSERT and BEFORE UPDATE  on the child table, and BEFORE UPDATE and BEFORE DELETE on the parent table. DELIMITER // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_INS // CREATE TRIGGER PARTITIONED_TABLE_CHECK_INS BEFORE INSERT ON child_table FOR EACH ROW BEGIN     DECLARE fk_check INT;     DECLARE fk_error_msg VARCHAR(200);     IF (@DISABLE_TRIGGERS IS NULL) THEN       SELECT COUNT(*) FROM parent_table WHERE id=new.parent_id INTO fk_check;       IF fk_check < 1 THEN           SELECT CONCAT("Foreign key constraint fails for table `parent_table`, can't find row matching `id='", new.parent_id, "`") INTO fk_error_msg;           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg;       END IF;     END IF; END // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_UPD // CREATE TRIGGER PARTITIONED_TABLE_CHECK_UPD BEFORE UPDATE ON child_table FOR EACH ROW BEGIN     DECLARE fk_check INT;     DECLARE fk_error_msg VARCHAR(200);     IF (@DISABLE_TRIGGERS IS NULL) THEN       SELECT COUNT(*) FROM parent_table WHERE id=new.parent_id INTO fk_check;       IF fk_check < 1 THEN           SELECT CONCAT("Foreign key constraint fails for table `child_table`, can't find row matching `id='", new.parent_id, "`") INTO fk_error_msg;           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg;       END IF;     END IF; END // -- DELETE is checked the other way around and the trigger has to be attached to parent_table (I kept naming the same for consistency)  DELIMITER // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_DEL // CREATE TRIGGER PARTITIONED_TABLE_CHECK_DEL BEFORE DELETE ON parent_table FOR EACH ROW BEGIN     DECLARE fk_check INT;     DECLARE fk_error_msg VARCHAR(200);     IF (@DISABLE_TRIGGERS IS NULL) THEN       SELECT COUNT(*) FROM child_table WHERE parent_id=old.id INTO fk_check;       IF fk_check > 0 THEN           SELECT CONCAT("Foreign key constraint fails for table `parent_table`, child table has ", fk_check," row(s) matching condition `parent_id='", old.id, "`") INTO fk_error_msg;           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg;       END IF;     END IF; END // -- Check UPDATE on parent_id.id; If PK differs we must check it's not referenced DELIMITER // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_PARENT_PK_UPDATE // CREATE TRIGGER PARTITIONED_TABLE_CHECK_PARENT_PK_UPDATE BEFORE UPDATE ON parent_table FOR EACH ROW BEGIN DECLARE fk_check INT; DECLARE fk_error_msg VARCHAR(200); IF (@DISABLE_TRIGGERS IS NULL) THEN IF old.id <> new.id THEN SELECT COUNT(*) FROM child_table WHERE parent_id=old.id INTO fk_check; IF fk_check > 0 THEN SELECT CONCAT("Foreign key constraint fails for table `parent_table`, child table has ", fk_check," row(s) matching condition `parent_id='", old.id, "`") INTO fk_error_msg; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg; END IF; END IF; END IF; END // DELIMITER ;   Testing the Triggers: Populate parent_table: mysql> INSERT INTO parent_table (id, column1) VALUES (1, "column1"); Query OK, 1 row affected (0.03 sec) Test insert: -- Insert is valid mysql> INSERT INTO child_table (id, column1, parent_id) VALUES (null, "value1", 1); Query OK, 1 row affected (0.01 sec) -- Insert fails with FK check mysql> INSERT INTO child_table (id, column1, parent_id) VALUES (null, "value2", 2); ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, can't find row matching `id='2` So far so good! For valid child ids, inserts are accepted, and for invalid child ids, trigger rejects the insert. Test Update: --Test invalid update on child: mysql> UPDATE child_table SET parent_id='2' WHERE parent_id='1'; ERROR 1644 (45000): Foreign key constraint fails for table `child_table`, can't find row matching `id='2` -- Test invalid update on parent mysql> UPDATE parent_table SET id=5; ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, child table has 1 row(s) matching condition `parent_id='1` -- Test valid update on parent and child mysql> INSERT INTO parent_table VALUES (10, "column1"); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO child_table VALUES (10, "column1", 1); Query OK, 1 row affected (0.01 sec) mysql> UPDATE parent_table SET id = 9 WHERE id = 10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE child_table SET parent_id = 9 WHERE id = 10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Test Delete: -- Invalid delete mysql> DELETE FROM parent_table WHERE id=1 ; ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, child table has 1 row(s) matching condition `parent_id='1` For both delete and update, we also tested trigger is working as expecting and checking FK integrity. Insert new row on parent_table which we should be able to delete without failing the “constraint” (as it will have no child rows) : -- Test valid delete INSERT INTO parent_table (id, column1) VALUES (2, "column2"); Query OK, 1 row affected (0.03 sec) mysql> DELETE FROM parent_table WHERE id=2 ; Query OK, 1 row affected (0.03 sec) Unfortunately, the non-standard REPLACE INTO is not compatible with the above method, as it actually consists of two operations – a DELETE and a subsequent INSERT INTO, and doing the DELETE on the parent table for a referenced row would trigger the FK error: mysql> REPLACE INTO parent_table (id, column1) VALUES (1, "column2"); ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, child table has 1 row(s) matching condition `parent_id='1` REPLACE INTO the child_table should work without issues. On the other hand, INSERT…ON DUPLICATE KEY CHECK will work as expected as the trigger on the UPDATE will work correctly and prevent breaking referential integrity. For convenience FK triggers can be disabled on the session; This would be the equivalent of SET foreign_key_checks=0.  You can disable by setting the following variable: mysql > SET @DISABLE_TRIGGERS=1; Query OK, 0 rows affected (0.00 sec) Disclaimer: The above is a proof of concept and while it should work for the vast majority of uses, there are two cases that are not checked by the triggers and will break referential integrity: TRUNCATE TABLE parent_table and DROP TABLE parent_table,  as it will not execute the DELETE trigger and hence will allow all child rows to become invalid at once.   And in general, DDL operations which can break referential integrity (for example ALTER TABLE modifying column type or name) are not handled as these operations don’t fire TRIGGERs of any kind, and also it relies on you writing the correct query to find the parent rows (for example if you have a parent table with a multi-column primary key, you must check all the columns in the WHERE condition of the triggers) Also, keep in mind added performance impact; Triggers will add overhead, so please make sure to measure impact on the response time of the DML in these two tables. Please test thoroughly before deploying to production! [Less]
Using Referential Constraints with Partitioned Tables in InnoDB
Posted over 4 years ago by MySQL Performance Blog
One of our support customers approached us with the following problem the other day: mysql> CREATE TABLE child_table ( `id` int unsigned auto_increment, `column1` varchar(64) NOT NULL, parent_id int unsigned NOT NULL, PRIMARY KEY (`id`) ... [More] , CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent_table (id)); ERROR 1215 (HY000): Cannot add foreign key constraint They could not create a table with an FK relation! So, of course, we asked to see the parent table definition, which was: CREATE TABLE `parent_table` ( `id` int unsigned auto_increment, `column1` varchar(64) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB PARTITION BY HASH (id) PARTITIONS 4; The parent table is partitioned!  This immediately explained the problem; partitioned tables can not be part of an FK relationship, as described (in point 10) here – MySQL Error Code 1215: “Cannot add foreign key constraint”. Quoting the official MySQL manual for completeness: Partitioned tables using the InnoDB storage engine do not support foreign keys. More specifically, this means that the following two statements are true: No definition of an InnoDB table employing user-defined partitioning may contain foreign key references; no InnoDB table whose definition contains foreign key references may be partitioned. No InnoDB table definition may contain a foreign key reference to a user-partitioned table; no InnoDB table with user-defined partitioning may contain columns referenced by foreign keys. So, after verifying it was impossible to guarantee referential integrity using CONSTRAINTs, we turned to an old alternative from MyISAM era of MySQL: using a set of triggers that would intercept the DML statements before they execute, and verify if the parent row actually exists. So for this, we would create child_table without the constraint: CREATE TABLE child_table ( `id` int unsigned auto_increment, `column1` varchar(64) NOT NULL, parent_id int unsigned NOT NULL, PRIMARY KEY (`id`)); And then we create 4 triggers: BEFORE INSERT and BEFORE UPDATE  on the child table, and BEFORE UPDATE and BEFORE DELETE on the parent table. DELIMITER // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_INS // CREATE TRIGGER PARTITIONED_TABLE_CHECK_INS BEFORE INSERT ON child_table FOR EACH ROW BEGIN     DECLARE fk_check INT;     DECLARE fk_error_msg VARCHAR(200);     IF (@DISABLE_TRIGGERS IS NULL) THEN       SELECT COUNT(*) FROM parent_table WHERE id=new.parent_id INTO fk_check;       IF fk_check < 1 THEN           SELECT CONCAT("Foreign key constraint fails for table `parent_table`, can't find row matching `id='", new.parent_id, "`") INTO fk_error_msg;           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg;       END IF;     END IF; END // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_UPD // CREATE TRIGGER PARTITIONED_TABLE_CHECK_UPD BEFORE UPDATE ON child_table FOR EACH ROW BEGIN     DECLARE fk_check INT;     DECLARE fk_error_msg VARCHAR(200);     IF (@DISABLE_TRIGGERS IS NULL) THEN       SELECT COUNT(*) FROM parent_table WHERE id=new.parent_id INTO fk_check;       IF fk_check < 1 THEN           SELECT CONCAT("Foreign key constraint fails for table `child_table`, can't find row matching `id='", new.parent_id, "`") INTO fk_error_msg;           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg;       END IF;     END IF; END // -- DELETE is checked the other way around and the trigger has to be attached to parent_table (I kept naming the same for consistency)  DELIMITER // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_DEL // CREATE TRIGGER PARTITIONED_TABLE_CHECK_DEL BEFORE DELETE ON parent_table FOR EACH ROW BEGIN     DECLARE fk_check INT;     DECLARE fk_error_msg VARCHAR(200);     IF (@DISABLE_TRIGGERS IS NULL) THEN       SELECT COUNT(*) FROM child_table WHERE parent_id=old.id INTO fk_check;       IF fk_check > 0 THEN           SELECT CONCAT("Foreign key constraint fails for table `parent_table`, child table has ", fk_check," row(s) matching condition `parent_id='", old.id, "`") INTO fk_error_msg;           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg;       END IF;     END IF; END // -- Check UPDATE on parent_id.id; If PK differs we must check it's not referenced DELIMITER // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_PARENT_PK_UPDATE // CREATE TRIGGER PARTITIONED_TABLE_CHECK_PARENT_PK_UPDATE BEFORE UPDATE ON parent_table FOR EACH ROW BEGIN DECLARE fk_check INT; DECLARE fk_error_msg VARCHAR(200); IF (@DISABLE_TRIGGERS IS NULL) THEN IF old.id <> new.id THEN SELECT COUNT(*) FROM child_table WHERE parent_id=old.id INTO fk_check; IF fk_check > 0 THEN SELECT CONCAT("Foreign key constraint fails for table `parent_table`, child table has ", fk_check," row(s) matching condition `parent_id='", old.id, "`") INTO fk_error_msg; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg; END IF; END IF; END IF; END // DELIMITER ;   Testing the Triggers: Populate parent_table: mysql> INSERT INTO parent_table (id, column1) VALUES (1, "column1"); Query OK, 1 row affected (0.03 sec) Test insert: -- Insert is valid mysql> INSERT INTO child_table (id, column1, parent_id) VALUES (null, "value1", 1); Query OK, 1 row affected (0.01 sec) -- Insert fails with FK check mysql> INSERT INTO child_table (id, column1, parent_id) VALUES (null, "value2", 2); ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, can't find row matching `id='2` So far so good! For valid child ids, inserts are accepted, and for invalid child ids, trigger rejects the insert. Test Update: --Test invalid update on child: mysql> UPDATE child_table SET parent_id='2' WHERE parent_id='1'; ERROR 1644 (45000): Foreign key constraint fails for table `child_table`, can't find row matching `id='2` -- Test invalid update on parent mysql> UPDATE parent_table SET id=5; ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, child table has 1 row(s) matching condition `parent_id='1` -- Test valid update on parent and child mysql> INSERT INTO parent_table VALUES (10, "column1"); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO child_table VALUES (10, "column1", 1); Query OK, 1 row affected (0.01 sec) mysql> UPDATE parent_table SET id = 9 WHERE id = 10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE child_table SET parent_id = 9 WHERE id = 10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Test Delete: -- Invalid delete mysql> DELETE FROM parent_table WHERE id=1 ; ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, child table has 1 row(s) matching condition `parent_id='1` For both delete and update, we also tested trigger is working as expecting and checking FK integrity. Insert new row on parent_table which we should be able to delete without failing the “constraint” (as it will have no child rows) : -- Test valid delete INSERT INTO parent_table (id, column1) VALUES (2, "column2"); Query OK, 1 row affected (0.03 sec) mysql> DELETE FROM parent_table WHERE id=2 ; Query OK, 1 row affected (0.03 sec) Unfortunately, the non-standard REPLACE INTO is not compatible with the above method, as it actually consists of two operations – a DELETE and a subsequent INSERT INTO, and doing the DELETE on the parent table for a referenced row would trigger the FK error: mysql> REPLACE INTO parent_table (id, column1) VALUES (1, "column2"); ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, child table has 1 row(s) matching condition `parent_id='1` REPLACE INTO the child_table should work without issues. On the other hand, INSERT…ON DUPLICATE KEY CHECK will work as expected as the trigger on the UPDATE will work correctly and prevent breaking referential integrity. For convenience FK triggers can be disabled on the session; This would be the equivalent of SET foreign_key_checks=0.  You can disable by setting the following variable: mysql > SET @DISABLE_TRIGGERS=1; Query OK, 0 rows affected (0.00 sec) Disclaimer: The above is a proof of concept and while it should work for the vast majority of uses, there are two cases that are not checked by the triggers and will break referential integrity: TRUNCATE TABLE parent_table and DROP TABLE parent_table,  as it will not execute the DELETE trigger and hence will allow all child rows to become invalid at once.   And in general, DDL operations which can break referential integrity (for example ALTER TABLE modifying column type or name) are not handled as these operations don’t fire TRIGGERs of any kind, and also it relies on you writing the correct query to find the parent rows (for example if you have a parent table with a multi-column primary key, you must check all the columns in the WHERE condition of the triggers) Also when using “ON CASCADE DELETE” FKs, triggers won’t be fired on delete child rows. Keep this in mind if you have complex FK + triggers tables. Last, keep in mind added performance impact; Triggers will add overhead, so please make sure to measure impact on the response time of the DML in these two tables. Because of all the above, triggers are NOT suggested to be used to maintain data consistency, but using triggers is the only possible workaround when using partitioned tables with InnoDB. Please test thoroughly and be aware of the caveats before deploying to production! [Less]
ClickHouse and ProxySQL queries rewrite (Cross-post from ProxySQL)
Posted over 4 years ago by MinervaDB
MySQL query rewrite for ClickHouse using ProxySQL  Introduction ProxySQL in September 2017 announced support for ClickHouse as backend. ProxySQL is a popular open source, high performance and protocol-aware proxy server for MySQL and its ... [More] forks. ClickHouse is an open source column-oriented database management system capable of real time generation of analytical data reports using SQL queries. To support ClickHouse as a backend, ProxySQL acts as a data bridge between MySQL protocol and ClickHouse protocol, allowing MySQL clients to execute queries in ClickHouse through it. ClickHouse’s SQL query syntax is different than MySQL’s syntax, and migrating application from MySQL to ClickHouse isn’t just a matter of changing connections endpoint but it also requires modifying some queries. This needs development time, but not always possible. One of ProxySQL most widely used feature is indeed the ability of rewriting queries, so often it is just a matter of writing the right query rules. In this blog post we have explained step-by-step MySQL query rewrite for ClickHouse using ProxySQL: How to implement ProxySQL query rewrite for ClickHouse ? The below is MySQL query we are considering for query rewrite: SELECT COUNT(`id`), FROM_UNIXTIME(`created`, '%Y-%m') AS `date` FROM `tablename` GROUP BY FROM_UNIXTIME(`created`, '%Y-%m') ClickHouse doesn’t support FROM_UNIXTIME, but it supports toDate and toTime. ClickHouse also supports toYear and toMonth , useful to format the date the same FROM_UNIXTIME does. Therefore, it is possible to rewrite the query as: SELECT COUNT(`id`), concat(toString(toYear(toDate(created))), '-', toString(toMonth(toDate(created)))) AS `date` FROM `tablename` GROUP BY toYear(toDate(created)), toMonth(toDate(created)); To perform the above rewrite, we will need two rules, one for the first FROM_UNIXTIME, and one for the second one. Or we can just use one rewrite rules to replace FROM_UNIXTIME(created, ‘%Y-%m’) no matter if on the retrieved fields or in the GROUP BY clause, generating the following query: SELECT COUNT(`id`), concat(toString(toYear(toDate(created))), '-', toString(toMonth(toDate(created)))) AS `date` FROM `tablename` GROUP BY concat(toString(toYear(toDate(created))), '-', toString(toMonth(toDate(created)))); Does it look great? No, not yet! For the month of March, concat(toString(toYear(toDate(created))), ‘-‘, toString(toMonth(toDate(created)))) will return 2018-3 : not what the application was expecting, as MySQL would return 2018-03 . The same applies for all the first 9 months of each year. Finally, we rewrote the query as the follow, and the application was happy: SELECT COUNT(`id`), substring(toString(toDate(created)),1,7) AS `date` FROM `tablename` GROUP BY substring(toString(toDate(created)),1,7); Note: because of the datatypes conversions that ClickHouse needs to perform in order to execute the above query, its execution time is about 50% slower than executing the following query: SELECT COUNT(`id`), concat(toString(toYear(toDate(created))), '-', toString(toMonth(toDate(created)))) AS `date` FROM `tablename` GROUP BY toYear(toDate(created)), toMonth(toDate(created)); Architecting using two ProxySQL Great, we now know how to rewrite the query! Although, the ClickHouse module in ProxySQL doesn’t support query rewrite. The ClickHouse module in ProxySQL is only responsible to transform data between MySQL and ClickHouse protocol, and viceversa. Therefore the right way of achieving this solution is to configure two ProxySQL layers, one instance responsible for rewriting the query and sending the rewritten query to the second ProxySQL instance, this one responsible for executing the query (already modified) on ClickHouse. Architecting using only one ProxySQL Does the above architecture seems complex? Not really, it is reasonable straightforward. Can it be improved? As you can see from the previous chart, the ClickHouse module and the MySQL module listen on different ports. The first ProxySQL instance is receiving traffic on port 6033, and sending traffic on the second PorxySQL instance on port 6090. Are two instances really required? The answer is no. In fact, a single instance can receive MySQL traffic on port 6033, rewrite the query, and send the rewritten query to itself on port 6090, to finally execute the rewritten query on ClickHouse. This diagram describes the architecture: Configuration For reference, below is the step to configure one single ProxySQL to send traffic to ClickHouse, and use itself as a backend. Create ClickHouse user: INSERT INTO clickhouse_users (username,password) VALUES ('clicku','clickp'); LOAD CLICKHOUSE USERS TO RUNTIME; SAVE CLICKHOUSE USERS TO DISK; Create MySQL user (same as ClickHouse): INSERT INTO mysql_users(username,password) SELECT username, password FROM clickhouse_users; LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; Configure ProxySQL itself as a backend for MySQL traffic: INSERT INTO mysql_servers(hostname,port) VALUES ('127.0.0.1',6090); SAVE MYSQL SERVERS TO DISK; LOAD MYSQL SERVERS TO RUNTIME; Create a query rule for rewriting queries: INSERT INTO mysql_query_rules (active,match_pattern,replace_pattern,re_modifiers) VALUES (1,"FROM_UNIXTIME\(`created`, '%Y-%m'\)", 'substring(toString(toDate(created)),1,7)',"CASELESS,GLOBAL"); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; This is a very simple example to demonstrate how to perform query rewrite from MySQL to ClickHouse using just one ProxySQL instance. In a real world scenarios you will need to create more rules based on your own queries. Conclusion Not only ProxySQL allows to send queries to ClickHouse, but it also allows to rewrite queries to solve issues related to different SQL syntax and available functions. To achieve this, ProxySQL uses its ability to use itself as a backend: rewrite the query in the MySQL module, and execute it in the ClickHouse module. References https://www.proxysql.com/blog/clickhouse-and-proxysql-queries-rewrite  https://minervadb.com/index.php/2019/12/25/how-to-use-proxysql-to-work-on-clickhouse-like-mysql/  The post ClickHouse and ProxySQL queries rewrite (Cross-post from ProxySQL) appeared first on The WebScale Database Infrastructure Operations Experts. [Less]
How to use ProxySQL to work on ClickHouse like MySQL ?
Posted over 4 years ago by MinervaDB
Use ClickHouse like MySQL with ProxySQL Introduction We have several customers on ClickHouse now for both columnar database analytics and archiving MySQL data, You can access data from ClickHouse with clickhouse-client but this involves some ... [More] learning  and also limitations technically. Our customers are very comfortable using MySQL so they always preferred a MySQL client for ClickHouse query analysis and reporting, Thankfully ProxySQL works as a optimal bridge between ClickHouse and MySQL client, This indeed was a great news for us and our customers worldwide. This blog post is about how we can use MySQL client with ClickHouse. Installation https://github.com/sysown/proxysql/releases/  (**Download the package starting with clickhouse ) Dependencies installation: yum -y install perl-DBD-MySQL   Start ProxySQL once completed installation successfully.   # The default configuration file is this: /etc/proxysql.cnf # There is no such data directory by default: mkdir / var / lib / proxysql # start up proxysql --clickhouse-server # ProxySQL will default to daemon mode in the background Creating ClickHouse user Create a user for ClickHouse in the ProxySQL with password, The password is not configured for ClickHouse but for accessing ProxySQL:   # ProxySQL port is 6032, the default username and password are written in the configuration file [email protected]: / root # mysql -h 127.0.0.1 -P 6032 -uadmin -padmin Welcome to the MariaDB monitor. Commands end with; or \ g. Your MySQL connection id is 3 Server version: 5.6.81 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\ h' for help. Type '\ c' to clear the current input statement. MySQL [(none)]> INSERT INTO clickhouse_users VALUES ('chuser', 'chpasswd', 1,100); Query OK, 1 row affected (0.00 sec) MySQL [(none)] > select * from clickhouse_users; + ---------- + ---------- + -------- + ----------------- + | username | password | active | max_connections | + ---------- + ---------- + -------- + ----------------- + | chuser | chpasswd | 1 | 100 | + ---------- + ---------- + -------- + ----------------- + 1 row in set (0.00 sec) MySQL [(none)]> LOAD CLICKHOUSE USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> SAVE CLICKHOUSE USERS TO DISK; Query OK, 0 rows affected (0.00 sec) Connecting to ClickHouse from MySQL Client  By default ProxySQL opens the port 6090 to receive user access to ClickHouse:   # Use username and password above # If it is a different machine, remember to change the IP [email protected]: / root # mysql -h 127.0.0.1 -P 6090 -uclicku -pclickp --prompt "ProxySQL-To-ClickHouse>" Welcome to the MariaDB monitor. Commands end with; or \ g. Your MySQL connection id is 64 Server version: 5.6.81 (ProxySQL ClickHouse Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\ h' for help. Type '\ c' to clear the current input statement. ProxySQL-To-ClickHouse > Querying ClickHouse like MySQL  MySQL [(none)] > select version (); + ------------------- + | version | + ------------------- + | 5.6.81-clickhouse | + ------------------- + 1 row in set (0.00 sec) MySQL [(none)] > select now (); + --------------------- + | now () | + --------------------- + | 2019-12-25 20:17:14 | + --------------------- + 1 row in set (0.00 sec) MySQL [(none)] > select today (); + ------------ + | today () | + ------------ + | 2019-12-25 | + ------------ + 1 row in set (0.00 sec) # Our table is over 55 billion ProxySQL-To-ClickHouse > select count (*) from mysql_audit_log_data; + ------------- + | count () | + ------------- + | 539124837571 | + ------------- + 1 row in set (8.31 sec) Limitations This ProxySQL solution works only when it is on the local ClickHouse (Note.- ClickHouse instance cannot have password in this ecosystem / recommended solution) ProxySQL query rewrite limitations – The simple queries work seamless, The complex query rewrite are quite expense and there might some levels of SQL semantics limitations  Conclusion – ProxySQL Version 2.0.8 new features and enhancements Changed default max_allowed_packet from 4M to 64M Added support for mysqldump 8.0 and Admin #2340 Added new variable mysql-aurora_max_lag_ms_only_read_from_replicas : if max_lag_ms is used and the writer is in the reader hostgroup, the writer will be excluded if at least N replicas are good candidates. Added support for unknown character set , and for collation id greater than 255 #1273 Added new variable mysql-log_unhealthy_connections to suppress messages related to unhealty clients connections being closed Reimplemented rules_fast_routing using khash Added support for SET CHARACTERSET #1692 Added support for same node into multiple Galera clusters #2290 Added more verbose output for error 2019 (Can’t initialize character set) #2273 Added more possible values for mysql_replication_hostgroups.check_type #2186 read_only | innodb_read_only read_only & innodb_read_only Added support and packages for RHEL / CentOS 8 References:  http://jackpgao.github.io/2017/12/19/Using-ClickHouse-like-MySQL-by-ProxySQL/ https://www.proxysql.com/blog/clickhouse-and-proxysql-queries-rewrite https://www.altinity.com/blog/2018/7/13/clickhouse-and-proxysql-queries-rewrite https://github.com/sysown/proxysql/releases  The post How to use ProxySQL to work on ClickHouse like MySQL ? appeared first on The WebScale Database Infrastructure Operations Experts. [Less]