Can someone help me, how to setup active active MySQL database on Google cloud platform?

|
huiuneed 2020-06-09 23:27:47
I used this command on a slave instance to find why MySQL had a high disk IO. Unfortunately, during running this command, MySQL cannot accept new connections, I had to kill pstack. When pstack exit, the mysqld also restart.
MasterZiv 2020-06-10 16:45:16
huiuneed 2020-06-09 14:32:13
If this mysql is not a master and can be crashed, when the latch is appear, the command pstack $mysqld_pid can trace all thread stack in mysqld, which is a way to find what’s happening in mysqld and which thread are block your thread. Basing on trace log, you can read and change the mysql server source code to fix the problems

pstack hardly can tell anything to a usual mortal user, not a MySQL developer.

MasterZiv 2020-06-10 16:47:21
huiuneed 2020-06-09 23:19:58
pstack is a dangerous gdb command
. It will cause mysqld crash or hung. It is last way to find the problem. Pstack can be unsafe for production due missed signals & interrupts if you get unlucky. 

Crash is not a very bad thing for an ACID database, you know.
But definitely not for production, yes.

2020-06-10 20:55:38
How to get started with relational db like. Mysql
MasterZiv 2020-06-10 23:50:24
2020-06-10 20:55:38
How to get started with relational db like. Mysql

Read Kodd’s book

MasterZiv 2020-06-10 23:51:36
2020-06-10 20:55:38
How to get started with relational db like. Mysql

Or read Garsia-Molino, Ulman, Widom.

2020-06-11 12:12:38
huiuneed 2020-06-09 23:27:47
I used this command on a slave instance to find why MySQL had a high disk IO. Unfortunately, during running this command, MySQL cannot accept new connections, I had to kill pstack. When pstack exit, the mysqld also restart.

thanks @huiuneed for heads-up.

joshtee4 2020-06-12 11:23:53
How do I restore postgres database in MySQL Workbench
joshtee4 2020-06-12 11:34:13
mysql_en-3774.jpg
This is the error message I got when I ran the code from the database I got from postgres
Master Yoda 2020-06-12 12:18:57
Comment set statement-timeout and check. Try. I ain’t sure😁
Master Yoda 2020-06-12 12:19:37
Or you comment out all set statments , they may not be compatible with MySQL
joshtee4 2020-06-12 13:13:18
I don’t understand
moh98ala 2020-06-12 13:27:46
joshtee4 2020-06-12 11:34:13
This is the error message I got when I ran the code from the database I got from postgres

show us the columns

MasterZiv 2020-06-12 13:31:58
joshtee4 2020-06-12 11:23:53
How do I restore postgres database in MySQL Workbench

No way, actually.

You can script the database on PG and try to load the script into mySQL, but the SQL dialects are different and this is NOT an AUTOMATIC process, you shall have to correct a lot of things.

MasterZiv 2020-06-12 13:32:31
moh98ala 2020-06-12 13:27:46
show us the columns

No, useless

joshtee4 2020-06-12 14:07:34
MasterZiv 2020-06-12 13:31:58
No way, actually.

You can script the database on PG and try to load the script into mySQL, but the SQL dialects are different and this is NOT an AUTOMATIC process, you shall have to correct a lot of things.

Correction like because have little experience

MasterZiv 2020-06-12 14:22:32
joshtee4 2020-06-12 14:07:34
Correction like because have little experience

This task cannot be done if you don’t have much experience, it requires good experience both in PG and MySQL.

joshtee4 2020-06-12 15:42:39
Ok
wini 2020-06-15 00:34:23
Excuse me … I want to ask about a cluster database, for example I have one master and one slave database cluster. If my master is down, can the slave be write? Or even still read only?

If the answer is written, what will happen if the master returns? Can new data entered on the slave be replicated to the up master?

wini 2020-06-15 00:39:24
Sorry about the sentences, I’m not a native english speakers
2020-06-15 09:01:07
Can someone help me, how to setup active active MySQL database on Google cloud platform?
Talluri Narendra 2020-06-15 09:15:02
Anyone has documenting of MySQL encryption database
Talluri Narendra 2020-06-15 09:15:09
Or how to perform
Talluri Narendra 2020-06-15 09:15:26
It would help me a lot
huiuneed 2020-06-15 09:49:32
Talluri Narendra 2020-06-15 09:15:02
Anyone has documenting of MySQL encryption database

https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html

huiuneed 2020-06-15 09:52:12
wini 2020-06-15 00:34:23
Excuse me … I want to ask about a cluster database, for example I have one master and one slave database cluster. If my master is down, can the slave be write? Or even still read only?

If the answer is written, what will happen if the master returns? Can new data entered on the slave be replicated to the up master?

this is a fail over situation. You could refer the MHA

Talluri Narendra 2020-06-15 10:04:31
OK.. Let me verify. Thanks
MasterZiv 2020-06-15 11:33:35
Send errors, logs as a text, ask full questions.
MasterZiv 2020-06-15 11:36:09
Read docs on dev.mysql.com with installation instructions.
Do step by step. All is very simple
MasterZiv 2020-06-15 11:45:46
This way no one will be able to help you.
MasterZiv 2020-06-15 11:50:04
DON’T go to Russian MySQL group , it is for russian-speaking only
MasterZiv 2020-06-15 12:19:50
А чего тогда сюда пишешь?
MasterZiv 2020-06-15 12:20:45
Понятно.
MasterZiv 2020-06-15 12:21:18
Главное — чтобы ты хорошо учился!
kingmaker004 2020-06-15 12:31:34
How to resolve this?
Gnation88 2020-06-15 12:35:12
Login with root user or any other user you have created on database.
MasterZiv 2020-06-15 12:44:35
kingmaker004 2020-06-15 12:31:34
How to resolve this?

don’t send pictures please, send text.

kingmaker004 2020-06-15 12:45:32
MasterZiv 2020-06-15 12:44:35
don’t send pictures please, send text.

Okay, but it easy to ask question

MasterZiv 2020-06-15 12:45:47
kingmaker004 2020-06-15 12:45:32
Okay, but it easy to ask question

but uneasy to read

mohite2804 2020-06-16 08:08:52
MySQL stored procedure how to return multiple result set
MasterZiv 2020-06-16 11:10:17
It is very simple, write SELECT several times in the body of the procedure. All will be sent to the client.

Those SELECTs that have only variables as it target in the select list go to the procedure, those SELECTs that don’t have variables in the select list go to the client.

MasterZiv 2020-06-16 11:10:25
mohite2804 2020-06-16 08:08:52
MySQL stored procedure how to return multiple result set

^

MasterZiv 2020-06-16 11:12:02
mohite2804 2020-06-16 08:08:52
MySQL stored procedure how to return multiple result set

You must also specify, what you mean by this “to return … result set”.
Possible meanings are to return to the client application, or to return to another procedure.

Master Yoda 2020-06-16 17:35:05
Do anyone over here know about auditing MySQL/MariaDB? Or any plungin like mariadbaudit plugin?
madno 2020-06-16 17:36:11
Master Yoda 2020-06-16 17:35:05
Do anyone over here know about auditing MySQL/MariaDB? Or any plungin like mariadbaudit plugin?

McAfee audit plugin for MySQL

Master Yoda 2020-06-16 17:40:22
madno 2020-06-16 17:36:11
McAfee audit plugin for MySQL

Thanks, I shall explore it

stef_kkk 2020-06-17 19:44:07
Help, please, to understand what is going on:(
I’m using docker image percona:5.7
Have the next problem, I’m dumping db’s correctly:

#!/bin/bash

for db in mysql aff_profits_test
do
time
mydumper
–host=127.0.0.1
–user=root
–password=pass
–outputdir=/home/ec2-user/full/$db
–rows=10000
-G -E -R
–compress
–build-empty-files
–threads=1
–verbose=3
–compress-protocol
–database=$db
done
Dumping proccess was successful and without any errors

Then I tried to restore mysql and aff_profits_test db’s for example:

#!/bin/bash

for db in mysql aff_profits_test
do
time
myloader
–host=127.0.0.1
–user=root
–password=pass
–directory=/home/ec2-user/full/$db
–threads=1
–verbose=3
–queries-per-transaction=100
–compress-protocol
–overwrite-tables
–database=$db
done
During restoring, I’m getting some errors:

** Message: 16:30:03.145: Thread 1 restoring `mysql`.`columns_priv` part 0
** Message: 16:30:03.145: Thread 1 restoring `mysql`.`db` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`event` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`func` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`ndb_binlog_index` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`proc` part 0

** (myloader:22186): CRITICAL **: 16:30:03.151: Error restoring mysql.proc from file mysql.proc.sql.gz: Table ‘mysql.proc’ doesn’t exist
Creating table `mysql`.`plugin`
** Message: 16:30:02.994: Dropping table or view (if exists) `mysql`.`proc`
** Message: 16:30:02.995: Creating table `mysql`.`proc`

** (myloader:22186): CRITICAL **: 16:30:02.996: Error restoring mysql.proc from file mysql.proc-schema.sql.gz: Invalid default value for ‘modified’

When restoring my own db, get the next errors:

Error restoring aff_profits_test.default_rate_shave_history from file aff_profits_test.default_rate_shave_history.sql.gz: Duplicate entry ‘1’ for key ‘PRIMARY’
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_countries` part 0
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_rate_tables` part 0
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_rates` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`department_site_alias` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`department_sites` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`details` part 0

* (myloader:3844): CRITICAL *: 12:50:08.426: Error restoring aff_profits_test.details from file aff_profits_test.details.sql.gz: Duplicate entry ’59eef12677075′ for key ‘PRIMARY’
** Message: 12:50:08.426: Thread 1 restoring `aff_profits_test`.`emails` part 0

* (myloader:3844): CRITICAL *: 12:50:08.447: Error restoring aff_profits_test.emails from file aff_profits_test.emails.sql.gz: Duplicate entry ‘1’ for key ‘PRIMARY’
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`emails_parts` part 0
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`errors` part 0
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`faq_items` part 0

stef_kkk 2020-06-17 19:44:45
my issue: https://github.com/maxbube/mydumper/issues/255

Can’t restore mysql or own db properly via myloader · Issue #255 · maxbube/mydumperGitHub
Help, please, to understand what is going on:( I’m using docker image percona:5.7 Have the next problem, I’m dumping db’s correctly: #!/bin/bash for db in mysql aff_profits_…
MasterZiv 2020-06-17 22:06:17
stef_kkk 2020-06-17 19:44:07
Help, please, to understand what is going on:(
I’m using docker image percona:5.7
Have the next problem, I’m dumping db’s correctly:

#!/bin/bash

for db in mysql aff_profits_test
do
time
mydumper
–host=127.0.0.1
–user=root
–password=pass
–outputdir=/home/ec2-user/full/$db
–rows=10000
-G -E -R
–compress
–build-empty-files
–threads=1
–verbose=3
–compress-protocol
–database=$db
done
Dumping proccess was successful and without any errors

Then I tried to restore mysql and aff_profits_test db’s for example:

#!/bin/bash

for db in mysql aff_profits_test
do
time
myloader
–host=127.0.0.1
–user=root
–password=pass
–directory=/home/ec2-user/full/$db
–threads=1
–verbose=3
–queries-per-transaction=100
–compress-protocol
–overwrite-tables
–database=$db
done
During restoring, I’m getting some errors:

** Message: 16:30:03.145: Thread 1 restoring `mysql`.`columns_priv` part 0
** Message: 16:30:03.145: Thread 1 restoring `mysql`.`db` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`event` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`func` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`ndb_binlog_index` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`proc` part 0

** (myloader:22186): CRITICAL **: 16:30:03.151: Error restoring mysql.proc from file mysql.proc.sql.gz: Table ‘mysql.proc’ doesn’t exist
Creating table `mysql`.`plugin`
** Message: 16:30:02.994: Dropping table or view (if exists) `mysql`.`proc`
** Message: 16:30:02.995: Creating table `mysql`.`proc`

** (myloader:22186): CRITICAL **: 16:30:02.996: Error restoring mysql.proc from file mysql.proc-schema.sql.gz: Invalid default value for ‘modified’

When restoring my own db, get the next errors:

Error restoring aff_profits_test.default_rate_shave_history from file aff_profits_test.default_rate_shave_history.sql.gz: Duplicate entry ‘1’ for key ‘PRIMARY’
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_countries` part 0
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_rate_tables` part 0
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_rates` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`department_site_alias` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`department_sites` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`details` part 0

* (myloader:3844): CRITICAL *: 12:50:08.426: Error restoring aff_profits_test.details from file aff_profits_test.details.sql.gz: Duplicate entry ’59eef12677075′ for key ‘PRIMARY’
** Message: 12:50:08.426: Thread 1 restoring `aff_profits_test`.`emails` part 0

* (myloader:3844): CRITICAL *: 12:50:08.447: Error restoring aff_profits_test.emails from file aff_profits_test.emails.sql.gz: Duplicate entry ‘1’ for key ‘PRIMARY’
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`emails_parts` part 0
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`errors` part 0
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`faq_items` part 0

These are duplicates, you must manually provide guarantee that there is no duplicate data in the destination database, that is, it must be empty.

MasterZiv 2020-06-17 22:07:22
stef_kkk 2020-06-17 19:44:07
Help, please, to understand what is going on:(
I’m using docker image percona:5.7
Have the next problem, I’m dumping db’s correctly:

#!/bin/bash

for db in mysql aff_profits_test
do
time
mydumper
–host=127.0.0.1
–user=root
–password=pass
–outputdir=/home/ec2-user/full/$db
–rows=10000
-G -E -R
–compress
–build-empty-files
–threads=1
–verbose=3
–compress-protocol
–database=$db
done
Dumping proccess was successful and without any errors

Then I tried to restore mysql and aff_profits_test db’s for example:

#!/bin/bash

for db in mysql aff_profits_test
do
time
myloader
–host=127.0.0.1
–user=root
–password=pass
–directory=/home/ec2-user/full/$db
–threads=1
–verbose=3
–queries-per-transaction=100
–compress-protocol
–overwrite-tables
–database=$db
done
During restoring, I’m getting some errors:

** Message: 16:30:03.145: Thread 1 restoring `mysql`.`columns_priv` part 0
** Message: 16:30:03.145: Thread 1 restoring `mysql`.`db` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`event` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`func` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`ndb_binlog_index` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`proc` part 0

** (myloader:22186): CRITICAL **: 16:30:03.151: Error restoring mysql.proc from file mysql.proc.sql.gz: Table ‘mysql.proc’ doesn’t exist
Creating table `mysql`.`plugin`
** Message: 16:30:02.994: Dropping table or view (if exists) `mysql`.`proc`
** Message: 16:30:02.995: Creating table `mysql`.`proc`

** (myloader:22186): CRITICAL **: 16:30:02.996: Error restoring mysql.proc from file mysql.proc-schema.sql.gz: Invalid default value for ‘modified’

When restoring my own db, get the next errors:

Error restoring aff_profits_test.default_rate_shave_history from file aff_profits_test.default_rate_shave_history.sql.gz: Duplicate entry ‘1’ for key ‘PRIMARY’
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_countries` part 0
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_rate_tables` part 0
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_rates` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`department_site_alias` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`department_sites` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`details` part 0

* (myloader:3844): CRITICAL *: 12:50:08.426: Error restoring aff_profits_test.details from file aff_profits_test.details.sql.gz: Duplicate entry ’59eef12677075′ for key ‘PRIMARY’
** Message: 12:50:08.426: Thread 1 restoring `aff_profits_test`.`emails` part 0

* (myloader:3844): CRITICAL *: 12:50:08.447: Error restoring aff_profits_test.emails from file aff_profits_test.emails.sql.gz: Duplicate entry ‘1’ for key ‘PRIMARY’
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`emails_parts` part 0
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`errors` part 0
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`faq_items` part 0

You put too much info, try to filter info somehow next time, please

|