I am looking for a way to install keyring_aws plugin in mysql community edition. Is it possible? I gone through couple of docs, > https://dev.mysql.com/doc/refman/5.7/en/keyring-aws-plugin.html > https://dev.mysql.com/doc/refman/5.7/en/keyring-installation.html And also tried to findout keyring_aws.so file, but couldn't find it anywhere. I checked in mysql dir also. root@server:~# ls -l /usr/lib/mysql/plugin/ total 644 -rw-r--r-- 1 root root 21224 Jan 22 17:26 adt_null.so -rw-r--r-- 1 root root 6288 Jan 22 17:26 auth_socket.so -rw-r--r-- 1 root root 44144 Jan 22 17:26 connection_control.so -rw-r--r-- 1 root root 108696 Jan 22 17:26 innodb_engine.so -rw-r--r-- 1 root root 88608 Jan 22 17:26 keyring_file.so -rw-r--r-- 1 root root 154592 Jan 22 17:26 libmemcached.so -rw-r--r-- 1 root root 9848 Jan 22 17:26 locking_service.so -rw-r--r-- 1 root root 10840 Jan 22 17:26 mypluglib.so -rw-r--r-- 1 root root 6288 Jan 22 17:26 mysql_no_login.so -rw-r--r-- 1 root root 56064 Jan 22 17:26 rewriter.so -rw-r--r-- 1 root root 56936 Jan 22 17:26 semisync_master.so -rw-r--r-- 1 root root 14768 Jan 22 17:26 semisync_slave.so -rw-r--r-- 1 root root 27568 Jan 22 17:26 validate_password.so -rw-r--r-- 1 root root 31296 Jan 22 17:26 version_token.so Can we build this .so file from scratch? If yes, where can I find the source?

I have a PXC cluster (5.7) with three nodes running on CentOS 7 VMs, each with the following system info: mysql_db# free total used free shared buff/cache available Mem: 8009248 2591576 358504 146040 5059168 4875716 Swap: 5242876 3592 5239284 mysql_db# free -g total used free shared buff/cache available Mem: 7 2 0 0 4 4 Swap: 4 0 4 mysql_db# nproc 8 mysql_db# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/cl-root 44G 20G 25G 46% / devtmpfs 3.9G 0 3.9G 0% /dev tmpfs 3.9G 0 3.9G 0% /dev/shm tmpfs 3.9G 146M 3.7G 4% /run tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup /dev/sda1 1014M 272M 743M 27% /boot tmpfs 783M 0 783M 0% /run/user/1318214225 tmpfs 783M 0 783M 0% /run/user/0 mysql_db# top top - 07:39:38 up 50 days, 4:54, 2 users, load average: 0.46, 0.61, 0.61 Tasks: 227 total, 1 running, 226 sleeping, 0 stopped, 0 zombie %Cpu(s): 20.3 us, 4.0 sy, 0.0 ni, 75.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 8009248 total, 357176 free, 2592348 used, 5059724 buff/cache KiB Swap: 5242876 total, 5239284 free, 3592 used. 4874700 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1574 mysql 20 0 9241472 2.3g 238932 S 104.8 30.1 6343:02 mysqld 3988 root 20 0 172268 2348 1580 R 19.0 0.0 0:00.12 top 13164 root 20 0 177264 94680 4676 S 19.0 1.2 2693:42 mysqld_exporter 1 root 20 0 191404 4420 2612 S 0.0 0.1 187:50.00 systemd mysql_db# iostat Linux 3.10.0-862.14.4.el7.x86_64 (dev_mysql_02.pd.local) 11/20/2018 _x86_64_ (8 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 2.35 0.00 0.36 0.05 0.00 97.24 Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn sda 3.83 153.19 153.17 664744979 664666279 dm-0 3.74 139.26 153.17 604320443 664647691 dm-1 0.00 0.00 0.00 2236 2460 mysql_db#lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 8 On-line CPU(s) list: 0-7 Thread(s) per core: 1 Core(s) per socket: 1 Socket(s): 8 NUMA node(s): 1 Vendor ID: AuthenticAMD CPU family: 21 Model: 1 Model name: AMD Opteron(TM) Processor 6276 Stepping: 2 CPU MHz: 2300.028 BogoMIPS: 4600.05 Hypervisor vendor: VMware Virtualization type: full L1d cache: 16K L1i cache: 64K L2 cache: 2048K L3 cache: 12288K NUMA node0 CPU(s): 0-7 Below is some important InnoDB variables in the my.cnf config file: ... back_log = 65535 binlog_format = ROW character_set_server = utf8 collation_server = utf8_general_ci default_storage_engine = InnoDB enforce-gtid-consistency = 1 expand_fast_index_creation = 1 gtid_mode = ON innodb_autoinc_lock_mode = 2 innodb_buffer_pool_instances = 3 innodb_buffer_pool_size = 3G innodb_data_file_path = ibdata1:64M;ibdata2:64M:autoextend innodb_file_format = Barracuda innodb_file_per_table innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_io_capacity = 1600 innodb_large_prefix innodb_log_file_size = 256M innodb_print_all_deadlocks = 1 innodb_read_io_threads = 64 innodb_stats_on_metadata = FALSE innodb_write_io_threads = 64 long_query_time = 1 log_bin_trust_function_creators = 1 master_info_repository = TABLE max_allowed_packet = 64M max_connect_errors = 4294967295 max_connections = 2600 max_user_connections = 2500 min_examined_row_limit = 1000 relay_log_info_repository = TABLE relay-log-recovery = TRUE skip-name-resolve slave_parallel_workers = 8 slow_query_log = 1 #slow_query_log_timestamp_always = 1 #thread_cache = 1024 tmpdir = /srv/tmp transaction_isolation = READ-COMMITTED updatable_views_with_limit = 0 user = mysql wait_timeout = 60 userstat table_open_cache = 4096 innodb_open_files = 10240 open_files_limit = 10240 connect_timeout=60 thread_cache_size = 4096 sql_mode = NO_ENGINE_SUBSTITUTION query_cache_size = 0 slave_pending_jobs_size_max=32M range_optimizer_max_mem_size=8M log_timestamps=SYSTEM server-id= 172162 #use IP userstat=1 ... As seen above, i have set innodb_buffer_pool_size to 3GB, but not sure if this is a good value given the system info provided above, especially that Total RAM is 7.6GB, with about 5GB allocated to SWAP. We have noticed in the mysql error logs a lot of timeouts and deadlocks frequently happening, and running SHOW ENGINE INNODB STATUS shows this: Status: ===================================== 2018-11-20 07:46:14 0x7f6acc0f9700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 8 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 201141 srv_active, 0 srv_shutdown, 2836117 srv_idle srv_master_thread log flush and writes: 3037214 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 32658 OS WAIT ARRAY INFO: signal count 33393 RW-shared spins 0, rounds 156414, OS waits 27395 RW-excl spins 0, rounds 222081, OS waits 1171 RW-sx spins 11313, rounds 167795, OS waits 1583 Spin rounds per wait: 156414.00 RW-shared, 222081.00 RW-excl, 14.83 RW-sx ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2018-11-20 07:06:51 0x7f6aad2eb700 Transaction: TRANSACTION 25842563, ACTIVE 1 sec updating or deleting mysql tables in use 1, locked 1 12 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3 MySQL thread id 577793, OS thread handle 140096148780800, query id 3550710 10.168.103.11 slashire_dev update INSERT INTO member_bank_acct (ba_member_id,ba_create_user,ba_update_user,ba_country,ba_name,ba_currency,ba_bank_name,ba_bank_acct_num,ba_swift_code,ba_city,ba_branch,ba_iban,ba_beneficiary_bank) VALUES ('00000000000000000252','SYSTEM','SYSTEM','','','','','','','','','','') ON DUPLICATE KEY UPDATE ba_country = '',ba_name = '',ba_currency = '',ba_bank_name = '',ba_bank_acct_num = '',ba_swift_code = '',ba_city = '',ba_branch = '',ba_iban = '',ba_beneficiary_bank = '' Foreign key constraint fails for table slashire_dev.member_bank_acct: , CONSTRAINT member_bank_acct_fk02 FOREIGN KEY (ba_country) REFERENCES country (c_code) Trying to add in child table, in index member_bank_acct_fk02 tuple: DATA TUPLE: 2 fields; 0: len 0; hex ; asc ;; 1: len 20; hex 3030303030303030303030303030303030323532; asc 00000000000000000252;; But in parent table slashire_dev.country, in index PRIMARY, the closest match we can find is record: PHYSICAL RECORD: n_fields 9; compact format; info bits 0 0: len 2; hex 3030; asc 00;; 1: len 6; hex 00000000566c; asc Vl;; 2: len 7; hex b0000001eb0110; asc ;; 3: len 3; hex 416c6c; asc All;; 4: len 2; hex 8000; asc ;; 5: len 5; hex 999f0c0000; asc ;; 6: len 6; hex 53595354454d; asc SYSTEM;; 7: len 5; hex 999f0c0000; asc ;; 8: len 6; hex 53595354454d; asc SYSTEM;; ------------ TRANSACTIONS ------------ Trx id counter 25989367 Purge done for trx's n:o < 25989079 undo n:o < 0 state: running but idle History list length 27 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421576625901616, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625897008, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625900464, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625899312, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625895856, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625898160, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625890096, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625893552, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625888944, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625867056, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625894704, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625892400, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625891248, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625886640, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625887792, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625885488, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625884336, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625883184, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625882032, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625880880, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625879728, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625878576, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625877424, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625876272, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625875120, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625873968, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625872816, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625871664, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625870512, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625869360, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421576625868208, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (read thread) I/O thread 19 state: waiting for completed aio requests (read thread) I/O thread 20 state: waiting for completed aio requests (read thread) I/O thread 21 state: waiting for completed aio requests (read thread) I/O thread 22 state: waiting for completed aio requests (read thread) I/O thread 23 state: waiting for completed aio requests (read thread) I/O thread 24 state: waiting for completed aio requests (read thread) I/O thread 25 state: waiting for completed aio requests (read thread) I/O thread 26 state: waiting for completed aio requests (read thread) I/O thread 27 state: waiting for completed aio requests (read thread) I/O thread 28 state: waiting for completed aio requests (read thread) I/O thread 29 state: waiting for completed aio requests (read thread) I/O thread 30 state: waiting for completed aio requests (read thread) I/O thread 31 state: waiting for completed aio requests (read thread) I/O thread 32 state: waiting for completed aio requests (read thread) I/O thread 33 state: waiting for completed aio requests (read thread) I/O thread 34 state: waiting for completed aio requests (read thread) I/O thread 35 state: waiting for completed aio requests (read thread) I/O thread 36 state: waiting for completed aio requests (read thread) I/O thread 37 state: waiting for completed aio requests (read thread) I/O thread 38 state: waiting for completed aio requests (read thread) I/O thread 39 state: waiting for completed aio requests (read thread) I/O thread 40 state: waiting for completed aio requests (read thread) I/O thread 41 state: waiting for completed aio requests (read thread) I/O thread 42 state: waiting for completed aio requests (read thread) I/O thread 43 state: waiting for completed aio requests (read thread) I/O thread 44 state: waiting for completed aio requests (read thread) I/O thread 45 state: waiting for completed aio requests (read thread) I/O thread 46 state: waiting for completed aio requests (read thread) I/O thread 47 state: waiting for completed aio requests (read thread) I/O thread 48 state: waiting for completed aio requests (read thread) I/O thread 49 state: waiting for completed aio requests (read thread) I/O thread 50 state: waiting for completed aio requests (read thread) I/O thread 51 state: waiting for completed aio requests (read thread) I/O thread 52 state: waiting for completed aio requests (read thread) I/O thread 53 state: waiting for completed aio requests (read thread) I/O thread 54 state: waiting for completed aio requests (read thread) I/O thread 55 state: waiting for completed aio requests (read thread) I/O thread 56 state: waiting for completed aio requests (read thread) I/O thread 57 state: waiting for completed aio requests (read thread) I/O thread 58 state: waiting for completed aio requests (read thread) I/O thread 59 state: waiting for completed aio requests (read thread) I/O thread 60 state: waiting for completed aio requests (read thread) I/O thread 61 state: waiting for completed aio requests (read thread) I/O thread 62 state: waiting for completed aio requests (read thread) I/O thread 63 state: waiting for completed aio requests (read thread) I/O thread 64 state: waiting for completed aio requests (read thread) I/O thread 65 state: waiting for completed aio requests (read thread) I/O thread 66 state: waiting for completed aio requests (write thread) I/O thread 67 state: waiting for completed aio requests (write thread) I/O thread 68 state: waiting for completed aio requests (write thread) I/O thread 69 state: waiting for completed aio requests (write thread) I/O thread 70 state: waiting for completed aio requests (write thread) I/O thread 71 state: waiting for completed aio requests (write thread) I/O thread 72 state: waiting for completed aio requests (write thread) I/O thread 73 state: waiting for completed aio requests (write thread) I/O thread 74 state: waiting for completed aio requests (write thread) I/O thread 75 state: waiting for completed aio requests (write thread) I/O thread 76 state: waiting for completed aio requests (write thread) I/O thread 77 state: waiting for completed aio requests (write thread) I/O thread 78 state: waiting for completed aio requests (write thread) I/O thread 79 state: waiting for completed aio requests (write thread) I/O thread 80 state: waiting for completed aio requests (write thread) I/O thread 81 state: waiting for completed aio requests (write thread) I/O thread 82 state: waiting for completed aio requests (write thread) I/O thread 83 state: waiting for completed aio requests (write thread) I/O thread 84 state: waiting for completed aio requests (write thread) I/O thread 85 state: waiting for completed aio requests (write thread) I/O thread 86 state: waiting for completed aio requests (write thread) I/O thread 87 state: waiting for completed aio requests (write thread) I/O thread 88 state: waiting for completed aio requests (write thread) I/O thread 89 state: waiting for completed aio requests (write thread) I/O thread 90 state: waiting for completed aio requests (write thread) I/O thread 91 state: waiting for completed aio requests (write thread) I/O thread 92 state: waiting for completed aio requests (write thread) I/O thread 93 state: waiting for completed aio requests (write thread) I/O thread 94 state: waiting for completed aio requests (write thread) I/O thread 95 state: waiting for completed aio requests (write thread) I/O thread 96 state: waiting for completed aio requests (write thread) I/O thread 97 state: waiting for completed aio requests (write thread) I/O thread 98 state: waiting for completed aio requests (write thread) I/O thread 99 state: waiting for completed aio requests (write thread) I/O thread 100 state: waiting for completed aio requests (write thread) I/O thread 101 state: waiting for completed aio requests (write thread) I/O thread 102 state: waiting for completed aio requests (write thread) I/O thread 103 state: waiting for completed aio requests (write thread) I/O thread 104 state: waiting for completed aio requests (write thread) I/O thread 105 state: waiting for completed aio requests (write thread) I/O thread 106 state: waiting for completed aio requests (write thread) I/O thread 107 state: waiting for completed aio requests (write thread) I/O thread 108 state: waiting for completed aio requests (write thread) I/O thread 109 state: waiting for completed aio requests (write thread) I/O thread 110 state: waiting for completed aio requests (write thread) I/O thread 111 state: waiting for completed aio requests (write thread) I/O thread 112 state: waiting for completed aio requests (write thread) I/O thread 113 state: waiting for completed aio requests (write thread) I/O thread 114 state: waiting for completed aio requests (write thread) I/O thread 115 state: waiting for completed aio requests (write thread) I/O thread 116 state: waiting for completed aio requests (write thread) I/O thread 117 state: waiting for completed aio requests (write thread) I/O thread 118 state: waiting for completed aio requests (write thread) I/O thread 119 state: waiting for completed aio requests (write thread) I/O thread 120 state: waiting for completed aio requests (write thread) I/O thread 121 state: waiting for completed aio requests (write thread) I/O thread 122 state: waiting for completed aio requests (write thread) I/O thread 123 state: waiting for completed aio requests (write thread) I/O thread 124 state: waiting for completed aio requests (write thread) I/O thread 125 state: waiting for completed aio requests (write thread) I/O thread 126 state: waiting for completed aio requests (write thread) I/O thread 127 state: waiting for completed aio requests (write thread) I/O thread 128 state: waiting for completed aio requests (write thread) I/O thread 129 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 8353 OS file reads, 2256885 OS file writes, 649203 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 6.37 writes/s, 1.75 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 108 merges merged operations: insert 1, delete mark 1, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 796871, node heap has 46 buffer(s) Hash table size 796871, node heap has 8 buffer(s) Hash table size 796871, node heap has 7 buffer(s) Hash table size 796871, node heap has 14 buffer(s) Hash table size 796871, node heap has 23 buffer(s) Hash table size 796871, node heap has 16 buffer(s) Hash table size 796871, node heap has 15 buffer(s) Hash table size 796871, node heap has 94 buffer(s) 234.22 hash searches/s, 8.87 non-hash searches/s --- LOG --- Log sequence number 1427669147 Log flushed up to 1427669147 Pages flushed up to 1427669147 Last checkpoint at 1427669138 Max checkpoint age 434154333 Checkpoint age target 420587011 Modified age 0 Checkpoint age 9 0 pending log flushes, 0 pending chkp writes 452361 log i/o's done, 1.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 3353346048 Dictionary memory allocated 11634313 Internal hash tables (constant factor + variable factor) Adaptive hash index 54687040 (50999744 + 3687296) Page hash 1107112 (buffer pool 0 only) Dictionary cache 24384249 (12749936 + 11634313) File system 1676640 (812272 + 864368) Lock system 8004712 (7969496 + 35216) Recovery system 0 (0 + 0) Buffer pool size 196584 Buffer pool size, bytes 3220832256 Free buffers 146566 Database pages 49795 Old database pages 18321 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 10000, not young 35358 0.00 youngs/s, 0.00 non-youngs/s Pages read 7697, created 42280, written 1645380 0.00 reads/s, 0.00 creates/s, 5.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 49795, unzip_LRU len: 0 I/O sum:cur, unzip sum:cur ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 65528 Buffer pool size, bytes 1073610752 Free buffers 48863 Database pages 16588 Old database pages 6103 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3655, not young 13239 0.00 youngs/s, 0.00 non-youngs/s Pages read 2611, created 14049, written 334207 0.00 reads/s, 0.00 creates/s, 0.50 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 16588, unzip_LRU len: 0 I/O sum:cur, unzip sum:cur ---BUFFER POOL 1 Buffer pool size 65528 Buffer pool size, bytes 1073610752 Free buffers 48627 Database pages 16823 Old database pages 6190 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3253, not young 12647 0.00 youngs/s, 0.00 non-youngs/s Pages read 2476, created 14408, written 1147163 0.00 reads/s, 0.00 creates/s, 3.87 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 16823, unzip_LRU len: 0 I/O sum:cur, unzip sum:cur ---BUFFER POOL 2 Buffer pool size 65528 Buffer pool size, bytes 1073610752 Free buffers 49076 Database pages 16384 Old database pages 6028 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3092, not young 9472 0.00 youngs/s, 0.00 non-youngs/s Pages read 2610, created 13823, written 164010 0.00 reads/s, 0.00 creates/s, 0.62 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 16384, unzip_LRU len: 0 I/O sum:cur, unzip sum:cur -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB 0 RW transactions active inside InnoDB Process ID=1574, Main thread ID=140097892615936, state: sleeping Number of rows inserted 25413524, updated 63914, deleted 6172, read 1035018804 65.87 inserts/s, 0.12 updates/s, 0.00 deletes/s, 97.36 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT Are there any tell tell signs from the above info causing these timeouts and deadlocks? How can i find out the percentage of utilized InnoDB buffer pool against total RAM? # Ping Results 64 bytes from 10.1.5.100: icmp_seq=1 ttl=64 time=0.958 ms 64 bytes from 10.1.5.100: icmp_seq=2 ttl=64 time=1.09 ms 64 bytes from 10.1.5.100: icmp_seq=4 ttl=64 time=1.22 ms 64 bytes from 10.1.5.100: icmp_seq=5 ttl=64 time=2.24 ms DEADLOCK INFO *** Priority TRANSACTION: TRANSACTION 165015546, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 MySQL thread id 14, OS thread handle 140101363758848, query id 11358891 System lock *** Victim TRANSACTION: TRANSACTION 165015100, ACTIVE 1 sec , undo log entries 1 MySQL thread id 1027074, OS thread handle 140096133596928, query id 11358888 10.168.103.11 membership wsrep: initiating replication for write set (-1) COMMIT *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2481 page no 17 n bits 80 index PRIMARY of table membership.member_token trx id 165015100 lock_mode X locks rec but not gap 2018-12-06T03:12:23.732058-00:00 14 [Note] WSREP: --------- CONFLICT DETECTED -------- 2018-12-06T03:12:23.732119-00:00 14 [Note] WSREP: cluster conflict due to high priority abort for threads: 2018-12-06T03:12:23.732331-00:00 14 [Note] WSREP: Winning thread: THD: 14, mode: applier, state: executing, conflict: no conflict, seqno: 152121 SQL: (null) 2018-12-06T03:12:23.732392-00:00 14 [Note] WSREP: Victim thread: THD: 1027074, mode: local, state: committing, conflict: no conflict, seqno: -1 SQL: COMMIT

In MySQL/Aurora I see from time to time a spike in connections number (from about 100 to 4000): MySQL doc says: > Connections > > The number of connection attempts (successful or not) to the MySQL server. So I was thinking those coming from failed connection, however I don't see failures: MySQL [(none)]> SHOW GLOBAL STATUS like 'Conn%'; +-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | Connection_errors_accept | 0 | | Connection_errors_internal | 0 | | Connection_errors_max_connections | 0 | | Connection_errors_peer_address | 0 | | Connection_errors_select | 0 | | Connection_errors_tcpwrap | 0 | | Connections | 34388970 | +-----------------------------------+----------+ 7 rows in set (0.00 sec) MySQL [(none)]> show global variables like '%conn%'; +-----------------------------------------------+-------------------+ | Variable_name | Value | +-----------------------------------------------+-------------------+ | aurora_max_connections_limit | 16000 | | connect_timeout | 10 | | disconnect_on_expired_password | ON | | init_connect | | | max_connect_errors | 100 | | max_connections | 4000 | | max_user_connections | 0 | | performance_schema_session_connect_attrs_size | 512 | +-----------------------------------------------+-------------------+ I would very much like to understand what those are and when they are coming from.

I have this set of user accounts that I desire to create with default passwords then force the users to change passwords at first login. Plus, I wanted to force all user accounts to be resetting passwords after every 90 days. Is this supported in MySQL 5.7.x?

I am joining two tables on varchar columns having CHARACTER SET utf8 COLLATE utf8_bin and one with CHARACTER SET latin1. Indexing is not working when i am joining these two tables on this column, I am only storing alphanumeric values english characters a-z, A-Z and digits 0-9, no lengthy strings (chinese characters/UTF16) or characters that takes more space . I am working with Mysql InnoDB. is this the normal behaviour?

I had an issue with my MySQL replication from Master to Slave. An App we had by mistake wrote to the Slave and deleted some data as well, causing errors during the replication (delete errors, primary key not found errors etc.- error codes 1032, 1062 etc.). Skipping all the errors didn't work as it seemed the data was beyond repair and once all the errors from multiple tables were skipped the replication continued failing as above. To this end we exported the data from the Master using MySQL Workbench (after following all the steps of stopping, granting permissions, flushing privileges, locking the master and then exporting the data through MySQL Workbench and unlocking the databases). https://www.lexiconn.com/blog/2014/04/how-to-set-up-selective-master-slave-replication-in-mysql/ (example guide) The .sql files were ported to the Slave, Slave was stopped, changed the MASTER to MASTER_HOST and imported all the data through the MySQL Workbench on the Slave. The database is rather large (30 odd Gigs) so this process takes over 24 hours. Once the Slave is then Started, again the errors appear (Duplication errors, cannot delete errors, Primary Key errors etc.). Initially we used mysqldump to extract the data and MySQL to to re-import the data but this failed on the MySql importing complaining of errors in the SQL files (which are too large to open in any notepad and investigate). So we went for the WorkBench as an export and import (which seems to both function without any errors). I cannot find any research online that deals with something like this, a failure of replication after clean export and import or where to go from here. The .sql files created by the WorkBench do all have Drop Tables commands before create tables, so surely the tables are deleted and created fresh so should not have any Primary Key concerns etc. Does anyone have any guidance for this as any test we do has to run for 24 hours before we see the failure. Much appreciated.

So I will preface this by saying that I'm not all that skilled in MySQL (or regular SQL for that matter), but I'm trying to load data from a .txt file into my table that I keep for ticket sales. When I do that using: LOAD DATA LOCAL INFILE '/path/presales1.txt' INTO TABLE presales I get the '> prompter. And I have no idea what to do. I tried looking up solutions and the one I thought would work was: --local-infile[=1]; because that is straight out the MySQL handbook. It didn't work (nor did any variation on it...such as local-infile=1;, local-infile=1 etc....) Did I miss a simple step? My version of MySQL is 5.7

I have an web application that has a 1TB MySQL 5.7 production database. We are running into performance issues due to table data size. We are planning to archive old data from different tables. Checking for best options. ### Requirement Users do CRUD operations every day with minimal data so we plan to remove old data (1 year back data) to separate archive DB server and remove the old data from the production DB, which will make the users CRUD operations fast. ### Challenge Production DB to archive separate DB sync will happen every day. Production DB to separate Archive DB server sync only Create, Updated data should sync. Where users use separate archive DB server when required to see all data.

I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query. This query is taking **75 seconds** to run in the new dev environment: SELECT orders.*, users.user_flag FROM orders LEFT JOIN users ON users.id = orders.user_id WHERE cancelled = '0' AND is_estimate = '0' ORDER BY orders.updated_date DESC, orders.order_date DESC, orders.creation_date DESC LIMIT 30; - It is a new installation of MySQL via MAMP Pro on a Mac - The version of MySQL is **5.7.25** ... **Exactly the same query** on **exactly the same database schema** takes only **24 ms** on my old machine which has this version: mysql Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using EditLine wrapper So presumably something about the configuration of MySQL on the new machine is causing this slow query with a join and ORDER BY. **What my.cnf settings should I be looking for?** --- **Further tests** (I cannot change the code as it is production code but did these tests to narrow down the problem) Removing the JOIN brings the query time down to about **13ms** SELECT orders.* FROM orders WHERE cancelled = '0' AND is_estimate = '0' ORDER BY orders.updated_date DESC, orders.order_date DESC, orders.creation_date DESC LIMIT 30; Removing the ORDER BY statement brings the time down to about **200ms** (still too slow) SELECT orders.*, users.user_flag FROM orders LEFT JOIN users ON users.id = orders.user_id WHERE cancelled = '0' AND is_estimate = '0' LIMIT 30; (No JOIN or ORDER BY and it runs in 1.5ms) ---- The old MySQL config contains: [mysqld] # Only allow connections from localhost bind-address = 127.0.0.1 max_allowed_packet = 64M The new MySQL config has lots in it: # The following options will be passed to all MySQL clients [client] password = MAMP_root_password_MAMP #port = 9999 socket = /Applications/MAMP/tmp/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] #port = 9999 socket = /Applications/MAMP/tmp/mysql/mysql.sock key_buffer_size = 16M max_allowed_packet = 64M explicit_defaults_for_timestamp = 1 table_open_cache = 64 MAMP_bind-address_MAMP # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # MAMP_skip-networking_MAMP # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 secure_file_priv="" server-id = 1 innodb_buffer_pool_instances = 8 innodb_flush_method = O_DIRECT sync_binlog = 0 [mysqldump] password = MAMP_root_password_MAMP quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M myisam_sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout

CREATE TABLE request ( id int(11) unsigned NOT NULL AUTO_INCREMENT, created_by int(11) unsigned DEFAULT NULL, content text, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE user ( id int(11) unsigned NOT NULL AUTO_INCREMENT, name varchar(255) DEFAULT '', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; explain select * from request r left join user u on (r.created_by = u.id) +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------------------------------------------+ |id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra | +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------------------------------------------+ |1 |SIMPLE |r |NULL |ALL |NULL |NULL|NULL |NULL|2 |100 |NULL | |1 |SIMPLE |u |NULL |ALL |PRIMARY |NULL|NULL |NULL|1 |100 |Using where; Using join buffer (Block Nested Loop)| +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------------------------------------------+ Here is the content of the tables: +--+----------+-------+ |id|created_by|content| +--+----------+-------+ |1 |NULL |Test | |2 |1 |Bar baz| +--+----------+-------+ +--+------+ |id|name | +--+------+ |1 |Thomas| +--+------+ Why is it a nested loop ? If I add a condition on join like this: (r.created_by > 0 AND r.created_by = u.id) there is no more nested loop. My version is MySQL 5.7.34.

I'm using a dockerized MySQL 5.7.13 as part of an integration test of an application. The way the system works is as follows: 1. I start up the main application. It opens up a connection to MySQL, but does not perform any queries. 2. I start the integration test setup and drop the old table if it was left behind by the previous run: DROP TABLE IF EXISTS unit_device 3. I then go on and create the table like this: `CREATE TABLE IF NOT EXISTS unit_device ( id CHAR(36) NOT NULL, unit_id CHAR(36), device_id CHAR(36), deleted_at DATETIME, PRIMARY KEY (id) )` 4. I then insert some values into the table and trigger work in the main application which makes one SELECT: SELECT unit_id, device_id FROM unit_device WHERE deleted_at IS NULL 5. Then I drop the table: DROP TABLE IF EXISTS unit_device However, when I try to run this query, it hangs seemingly forever for some reason. If I close the connection from the main application, the query resumes. One thing that seems suspicious is that once the main application has made its select once, I can see the following information in the output of show engine innodb status: 1 read views open inside InnoDB I must be doing something seriously wrong here, but after hours of research I was still unable to figure out what's causing the hang, or whether the problem is in the test or the main application.

I have a user 'jay'@'localhost' and following are the grants for that user. mysql> show grants for 'jay'@'localhost'; +-------------------------------------------------------------------------------------------+ | Grants for jay@localhost | +-------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.searches TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.conversations TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.feed_entries TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.checksum_audit_logs TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.local_authority_entries TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.domain_terms_local_authorities TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.trophies TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.version_committers TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.follows TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.devise_multi_auth_authentications TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.schema_migrations TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.orcid_profile_requests TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.subject_local_authority_entries TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.change_manager_changes TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.receipts TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.proxy_deposit_rights TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.bookmarks TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.local_authorities TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.domain_terms TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.notifications TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.single_use_links TO 'jay'@'localhost' | | GRANT SELECT ON curate.users TO 'jay'@'localhost' | | GRANT ALL PRIVILEGES ON curate.help_requests TO 'jay'@'localhost' | +-------------------------------------------------------------------------------------------+ 24 rows in set (0.00 sec) As you can see, 'jay'@'localhost' has SELECT privileges only on the table 'curate'.'users'. But the user could still run the UPDATE query on that table. mysql> select user(); +---------------+ | user() | +---------------+ | jay@localhost | +---------------+ 1 row in set (0.00 sec) mysql> select * from users; +----+--------------------------+--------------------------------------------------------------+----------------------+------------------------+---------------------+---------------+---------------------+---------------------+--------------------+-----------------+---------------------+---------------------+-------+-----------------+----------------+-------------------+------+---------+------------+------------+-------+--------+---------+---------+-------------+-----------+------------------+---------------------+------------------+-------------------+------------+--------------------+--------------------------------------+-----------------+----------+------+------------+-----------+----------+--------------+---------------------+ | id | email | encrypted_password | reset_password_token | reset_password_sent_at | remember_created_at | sign_in_count | current_sign_in_at | last_sign_in_at | current_sign_in_ip | last_sign_in_ip | created_at | updated_at | guest | facebook_handle | twitter_handle | googleplus_handle | name | address | admin_area | department | title | office | chat_id | website | affiliation | telephone | avatar_file_name | avatar_content_type | avatar_file_size | avatar_updated_at | group_list | groups_last_update | user_does_not_require_profile_update | repository_id | provider | uid | first_name | last_name | ucstatus | ucdepartment | waived_welcome_page | +----+--------------------------+--------------------------------------------------------------+----------------------+------------------------+---------------------+---------------+---------------------+---------------------+--------------------+-----------------+---------------------+---------------------+-------+-----------------+----------------+-------------------+------+---------+------------+------------+-------+--------+---------+---------+-------------+-----------+------------------+---------------------+------------------+-------------------+------------+--------------------+--------------------------------------+-----------------+----------+------+------------+-----------+----------+--------------+---------------------+ | 1 | manydeposits@example.com | $2a$10$xqNke5vKUvKP8rIjtUSeD.l2L1nZpc8rx.L6mV2K2lr8XNmPpfGdy | NULL | NULL | NULL | 2 | 2017-10-12 22:20:17 | 2017-10-12 21:45:52 | 127.0.0.1 | 127.0.0.1 | 2017-10-12 21:36:25 | 2017-10-12 22:23:21 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | sufia:rb68xk02w | NULL | NULL | Many | Deposit | NULL | NULL | 1 | | 2 | nodeposits@example.com | $2a$10$wTZvQjTyHqSAD9B7LYxsc.9e8W9TMZ2tVv834p6bk0mTs8E/73lOm | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 2017-10-12 21:36:28 | 2017-10-12 21:36:29 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | sufia:rb68xk04f | NULL | NULL | No | Deposits | NULL | NULL | NULL | | 3 | manager@example.com | $2a$10$j5If0Yv0bMguADFfOgRZYetE76r8rL76lSuHbyXB1j3t77bgTQa42 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 2017-10-12 21:36:29 | 2017-10-12 21:36:30 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | sufia:rb68xk060 | NULL | NULL | Repository | Manager | NULL | NULL | NULL | | 4 | delegate@example.com | $2a$10$odYs2pjBrKJIu6aVA/5SsuiyBJw27oyMQacroxG7RB1r4JHLdR5gW | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 2017-10-12 21:36:31 | 2017-10-12 21:36:32 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | sufia:rb68xk08j | NULL | NULL | Student | Delegate | NULL | NULL | NULL | +----+--------------------------+--------------------------------------------------------------+----------------------+------------------------+---------------------+---------------+---------------------+---------------------+--------------------+-----------------+---------------------+---------------------+-------+-----------------+----------------+-------------------+------+---------+------------+------------+-------+--------+---------+---------+-------------+-----------+------------------+---------------------+------------------+-------------------+------------+--------------------+--------------------------------------+-----------------+----------+------+------------+-----------+----------+--------------+---------------------+ 4 rows in set (0.00 sec) mysql> UPDATE users SET last_name = 'Deposits', updated_at = '2017-10-12 22:23:21' WHERE users.id = 1; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> What am I missing here? Edit 1: Result from @Rick James's answer. mysql> SELECT Host, User, Db, Table_name FROM mysql.tables_priv -> WHERE table_priv LIKE '%Update%'; +-----------+------+--------+-----------------------------------+ | Host | User | Db | Table_name | +-----------+------+--------+-----------------------------------+ | localhost | jay | curate | version_committers | | localhost | jay | curate | trophies | | localhost | jay | curate | subject_local_authority_entries | | localhost | jay | curate | single_use_links | | localhost | jay | curate | searches | | localhost | jay | curate | schema_migrations | | localhost | jay | curate | receipts | | localhost | jay | curate | proxy_deposit_rights | | localhost | jay | curate | orcid_profile_requests | | localhost | jay | curate | notifications | | localhost | jay | curate | local_authority_entries | | localhost | jay | curate | local_authorities | | localhost | jay | curate | help_requests | | localhost | jay | curate | follows | | localhost | jay | curate | feed_entries | | localhost | jay | curate | domain_terms_local_authorities | | localhost | jay | curate | domain_terms | | localhost | jay | curate | devise_multi_auth_authentications | | localhost | jay | curate | conversations | | localhost | jay | curate | checksum_audit_logs | | localhost | jay | curate | change_manager_changes | | localhost | jay | curate | bookmarks | +-----------+------+--------+-----------------------------------+ 22 rows in set (0.02 sec) mysql> SELECT Host, User, Db FROM mysql.db WHERE Update_priv = 'Y'; +-----------+------+--------+ | Host | User | Db | +-----------+------+--------+ | localhost | | curate | +-----------+------+--------+ 1 row in set (0.00 sec) mysql> SELECT Host, User FROM mysql.db WHERE Update_priv = 'Y'; +-----------+------+ | Host | User | +-----------+------+ | localhost | | +-----------+------+ 1 row in set (0.00 sec) mysql> SELECT * FROM mysql.user WHERE User = 'jay'; +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | localhost | jay | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *DD431E425EB586BD1C8173743C46FD14F76C03ED | N | 2017-10-12 17:23:39 | NULL | N | +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ 1 row in set (0.03 sec) mysql> SELECT * FROM mysql.db WHERE User = 'jay' AND Db = 'curate'; Empty set (0.01 sec) mysql> SELECT * FROM mysql.user WHERE Host = 'localhost'; +-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | +-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | localhost | jay | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *DD431E425EB586BD1C8173743C46FD14F76C03ED | N | 2017-10-12 17:23:39 | NULL | N | | localhost | mysql.session | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017-10-12 11:39:23 | NULL | Y | | localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017-10-12 11:39:23 | NULL | Y | | localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *EC64FACB5BA6F06DCFB981AF3C1AC6963393B8BE | N | 2017-10-12 11:41:57 | NULL | N | +-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM mysql.db WHERE Host = 'localhost' AND Db = 'curate'; +-----------+--------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv | +-----------+--------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | localhost | curate | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | +-----------+--------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM mysql.tables_priv WHERE table_name = 'users'; +-----------+--------+------+------------+----------------+---------------------+------------+-------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+--------+------+------------+----------------+---------------------+------------+-------------+ | localhost | curate | jay | users | root@localhost | 0000-00-00 00:00:00 | Select | | +-----------+--------+------+------------+----------------+---------------------+------------+-------------+ 1 row in set (0.00 sec) mysql>

**This is a production setup and I have to perform a failover from Master A to new potential master B.** **MySQL Version is 5.7 and CentOS 7 is the OS.** I have a simple master slave setup with A as Master and B,C,D,E,F as async slaves. ProxySQL Cluster is also configured. Master A (writer in ProxySQL) and Slaves B and C (readers in ProxySQL) are added to ProxySQL. On all the slaves **log_slave_updates** is **ON** and **log_bin** is also **ON**. On **Master A** **read_only=0** and **Slaves B,C,D,E,F** have **read_only=1**. This is a **GTID** based replication with **master_auto_position = 0**. **A --> B,C,D,E,F this is the current replication topology** Now, I know for failover I have to choose either slave B or slave C because they are on proxy. I choose B as my new potential master. So, I have to move all the slaves from current Master A to new Master C. So the replication topology will be like this **B --> A,C,D,E,F -- proposed new topology** **My question is how do I move the slaves from Master A to new potential Master B?** **How should I use the coordinates from new Master B? What procedure should I follow?** Once I move the slaves I guess failover will be easy, I just tweak the **read_only** flag on old master and new master and proxy will take care of the connections. I would really appreciate if a detailed procedure with explanation is provided. I have gone through following links but couldn't understand the process https://stackoverflow.com/questions/43960577/mysql-failover-how-to-choose-slave-as-new-master https://serverfault.com/questions/303669/how-to-actually-use-mysql-slave-as-soon-the-master-is-failover-or-got-burnt https://dev.mysql.com/doc/refman/8.0/en/replication-solutions-switch.html

I am using innnodb cluster. It is good that every time a R/W node failed the R/O node will being promoted to R/W. However I have to restart scheduler and alter event enabled (originally slave side disabled) manually for the cluster again. It pretty error prone and troublesome. I always have to run the script to manually fill the losing data for the time event is not running. Is there anyway make it automized so I can be hands off?

I search for a variable that I can increase in the server that I don't get any more this error: Node 2 missed heartbeat 2 Node 3 Disconnected Node 2 Disconnected I don't find anything. Regards,

I get the following when I try to recover the cluster from a complete outage. Can anyone advise what's going on > cluster = dba.rebootClusterFromCompleteOutage() > > Dba.rebootClusterFromCompleteOutage: Invalid value for localAddress, > string value cannot be empty. (ArgumentError) Thanks

New user here, excuse me if that isn't grammatically correct, but this is what I want to do - I have a server set-up with multiple sites on one VM instance - I want to create an admin mysql database which only the account holder has access to and multiple "child" databases for each individual site on the instance which do not have root user but can be accessed via the parent/admin user. Is there a way to do this?

Since some time ago, I've started with MySQL so I don't know Workbench very well. That little red x next to the number of the line that indicates an error does not disappear, even though everything is okay and I fixed what was wrong. A simple example: select * project where name='Anna' and I can execute it but x won't disappear unless I delete everything and type again. This is very annoying and I can't concentrate because of always thinking about what should be wrong. Could somebody tell me what to do to fix this? :(

I'm trying implement MySQL upgrade approach that would require either no downtime at all or it would be counted in seconds. One of the problems is that these databases are RDS MySQL databases so there is no SUPER permissions that gives less control on what you can do with replicas. Anyway, this is how I see it can work. Let's have simplified cluster schema: M-->R-->--DMS-->--RE Where: * M - main read/write database * R - read replica that used for external replication via AWS DMS * RE - external replication database where we put that data We need to upgrade database M (M1). That would also require to update read replica R (R1), but we don't want to re-sync everything for DMS so we would like to continue that replication RE from the place where we were before upgrade, do the final schema should look like: M1-->R1-->--DMS-->--RE So, to achieve that we are starting new read replica from M, promote it, create new DMS (DMS1) replication that would sync changes from M1, so the schema will look like:

M-->R-->--DMS-->--RE
|
DMS1-->M1

Then from M1 I can start R1 replica and then continue DMS replication from R1 already, so we will have something like:

M-->R
|
DMS1-->M1-->R1-->--DMS-->--RE

and after that we can get rid of M and R. Now the question. As in the new cluster I'll continue from R1 binlog, which has completely different positions rather than R. How can I get the right position in R1 that would represent the same position in R?

How can we identify service shutdown time in MySQL community version 5.7.20. I presume that "statement/sql/shutdown" and "statement/com/Shutdown" are the events that occurs while shutdown a service, using this events I tried to set triggers to load the time. But I am unable to set the triggers too. The way I identified is through error log. Do we have any other variable or a way to identify the time of last service down.