Hi, anyone can help me out with what is the best practice to take the backup of production MySQL database?

|
Master Yoda 2020-07-03 16:27:22
2020-07-03 15:14:15
It would be grt if you could answer thi as soon as possible

Such simple exercises are meant to be solved by yourself

2020-07-03 16:27:43
Im a beginner
Master Yoda 2020-07-03 16:28:28
I don’t know anyone who will labor his time for this. If you had some issues or errors, people would have considered helping you
2020-07-03 16:29:32
Okay thnku sir for your precious time
Lince 2020-07-06 14:42:34
Hi, anyone can help me to script user permission for a database
RKChowdary9999 2020-07-06 17:10:30
I had a AUTO_INCREMENT column and it is incrementing by 7 rather than 1 although i set the AUTO_INCREMENT=1 in ddl.the values are like below
1
8
15
Fyi Using Innodb engine
Can someone help on this?
MasterZiv 2020-07-06 18:03:40
RKChowdary9999 2020-07-06 17:10:30
I had a AUTO_INCREMENT column and it is incrementing by 7 rather than 1 although i set the AUTO_INCREMENT=1 in ddl.the values are like below
1
8
15
Fyi Using Innodb engine
Can someone help on this?

This does not actually matter by what value autoincrement increments.

the value must only be unique.

MasterZiv 2020-07-06 18:04:58
RKChowdary9999 2020-07-06 17:10:30
I had a AUTO_INCREMENT column and it is incrementing by 7 rather than 1 although i set the AUTO_INCREMENT=1 in ddl.the values are like below
1
8
15
Fyi Using Innodb engine
Can someone help on this?

You can easy get this behavior if you roll some transactions back, and this is not an error, this is expected.

MasterZiv 2020-07-06 18:05:39
RKChowdary9999 2020-07-06 17:10:30
I had a AUTO_INCREMENT column and it is incrementing by 7 rather than 1 although i set the AUTO_INCREMENT=1 in ddl.the values are like below
1
8
15
Fyi Using Innodb engine
Can someone help on this?

If you really bother, just re-create the column, if it is possible.
If it is not possible to recreate the column, I don’t know what else you can do to fix this.

RKChowdary9999 2020-07-06 18:51:17
My requirement is to get sequence numbers incremented each by 1.
RKChowdary9999 2020-07-06 18:51:58
MasterZiv 2020-07-06 18:05:39
If you really bother, just re-create the column, if it is possible.
If it is not possible to recreate the column, I don’t know what else you can do to fix this.

Recreate column means to drop table and recreate, i tried that and its still not working as per my req

RKChowdary9999 2020-07-06 18:52:45
MasterZiv 2020-07-06 18:05:39
If you really bother, just re-create the column, if it is possible.
If it is not possible to recreate the column, I don’t know what else you can do to fix this.

I havent done any transactions just inserted the data using a job

MasterZiv 2020-07-06 18:55:29
RKChowdary9999 2020-07-06 18:52:45
I havent done any transactions just inserted the data using a job

You always do transactions, you can’t avoid this

MasterZiv 2020-07-06 18:56:01
RKChowdary9999 2020-07-06 18:51:58
Recreate column means to drop table and recreate, i tried that and its still not working as per my req

No, you can recreate the column only.

MasterZiv 2020-07-06 18:56:39
RKChowdary9999 2020-07-06 18:52:45
I havent done any transactions just inserted the data using a job

But first you must find out why you have gaps

RKChowdary9999 2020-07-06 18:57:20
Is there aby way to find out? Is it something to do with unique key declaration in ddl?
RKChowdary9999 2020-07-06 18:57:49
I declared auto increment xolumn as primary key and the one which has unique values as unique key
MasterZiv 2020-07-06 20:55:18
RKChowdary9999 2020-07-06 18:57:20
Is there aby way to find out? Is it something to do with unique key declaration in ddl?

No, keys are not related to this

MasterZiv 2020-07-06 20:58:45
RKChowdary9999 2020-07-06 18:57:20
Is there aby way to find out? Is it something to do with unique key declaration in ddl?

Can you show us DDL of your table ?

RKChowdary9999 2020-07-07 14:13:22
CREATE TABLE REFERENCE_MGR.ACCESS_RIGHTS(
rights_id int(11) NOT NULL AUTO_INCREMENT,
rights varchar(100),
access_type varchar(100),
is_active char(1),
created_by varchar(7),
created_date datetime,
updated_by varchar(7),
updated_time datetime,
PRIMARY KEY(rights_id)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
MasterZiv 2020-07-07 14:15:36
RKChowdary9999 2020-07-07 14:13:22
CREATE TABLE REFERENCE_MGR.ACCESS_RIGHTS(
rights_id int(11) NOT NULL AUTO_INCREMENT,
rights varchar(100),
access_type varchar(100),
is_active char(1),
created_by varchar(7),
created_date datetime,
updated_by varchar(7),
updated_time datetime,
PRIMARY KEY(rights_id)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Seems all ok….

RKChowdary9999 2020-07-07 14:17:09
Yes but not sure why it is incrementing by 7 instead of 1
adinath_kamode 2020-07-07 14:34:18
Try resetting auto_increment
RKChowdary9999 2020-07-07 14:48:26
how to reset that? truncate the table?
Gnation88 2020-07-07 15:57:05
Check autoincrement offset global variable.
Gnation88 2020-07-07 15:59:18
SHOW GLOBAL VARIABLES LIKE ‘auto_inc%’;
Master Yoda 2020-07-07 16:50:12
I think the variable is auto_increment_increment
Master Yoda 2020-07-07 16:50:20
Check that
Master Yoda 2020-07-07 16:50:45
Show global status like “auto_increment_increment”
Master Yoda 2020-07-07 16:52:46
It should be 7
Master Yoda 2020-07-07 16:53:07
First ask anyone in your team. They might have the master master or cluster replication set up
Gnation88 2020-07-07 16:53:40
Yes right, auto_increment_increment 👍
Master Yoda 2020-07-07 16:54:02
You can change it dynamically as” set global auto_incremement_increment=1″
Master Yoda 2020-07-07 16:54:17
It is dynamic variable if I remember correctly
Gnation88 2020-07-07 16:55:24
Yes its dynamic
2020-07-07 19:14:43
Hi, anyone can help me out with what is the best practice to take the backup of production MySQL database ?
Master Yoda 2020-07-07 19:15:30
2020-07-07 19:14:43
Hi, anyone can help me out with what is the best practice to take the backup of production MySQL database ?

Depends.

Master Yoda 2020-07-07 19:15:42
Full backup?
2020-07-07 19:15:54
Yes full backup
Master Yoda 2020-07-07 19:16:15
2020-07-07 19:15:54
Yes full backup

Logical or physical?

Master Yoda 2020-07-07 19:16:34
Mysqldump is slow
Master Yoda 2020-07-07 19:18:18
You can use xtrabackup
Master Yoda 2020-07-07 19:18:35
From percona
2020-07-07 19:19:51
Okay
2020-07-07 19:22:52
Thank you 👍
2020-07-08 13:13:13
we are using mysql on ubuntu  ( Digital ocean).
we are using a  transaction table and it gets records inserted/updated/deleted/select with a speed of 200 requests per second. we have few custom functions used.
we are facing issue with ram, the ram keeps on increasing and doesnt decrease untill we restart server, how can we make this limited or restricted, this increased ram is killing the performance of CURD operations with are creating wait time out.

this is working perfectly when the ram is upto 30%, once it cross 30 it is slowly increasing the timeout queries which again increase the ram.

smlkw 2020-07-08 14:00:27
2020-07-08 13:13:13
we are using mysql on ubuntu  ( Digital ocean).
we are using a  transaction table and it gets records inserted/updated/deleted/select with a speed of 200 requests per second. we have few custom functions used.
we are facing issue with ram, the ram keeps on increasing and doesnt decrease untill we restart server, how can we make this limited or restricted, this increased ram is killing the performance of CURD operations with are creating wait time out.

this is working perfectly when the ram is upto 30%, once it cross 30 it is slowly increasing the timeout queries which again increase the ram.

you need to get more mysql nodes and/or setup different write/select nodes

2020-07-08 14:03:36
smlkw 2020-07-08 14:00:27
you need to get more mysql nodes and/or setup different write/select nodes

Are you blocked my id

2020-07-08 14:03:44
Unbolck me we discuss on PM
smlkw 2020-07-08 14:05:03
2020-07-08 14:03:36
Are you blocked my id

It’s just privacy settings. We can discuss here, this group is for the MySQL topic.

|