Re: Show master status - "mysqld_multi" - distribution master[SOLVED]
On 23/08/16 11:26, sujeet gupta wrote:
Hi Dear,
It's not a mysql issue, show me every step you followed to create master
slave hierarchy.
Its like you missed some steps while configuring server.
Show master configuration and slave configuration separately.
On Aug 23, 2016 6:45 PM, "Jose Julian Buda"
<jb...@noticiasargentinas.com <mailto:jb...@noticiasargentinas.com>> wrote:
Hi, i have a server acting as a replication distribution
master(taking from the real master), with several mysql instances
(instance 1,instance 2...) with mysqld_multi script, and many
replication filters just to avoid using bandwidth to the remote
datacenters with useless data.
It's working fine, but when i need to see the log pos of an instance
to sync the replication or anything else(for example a delay in the
links):
mysql -P instance_1_port -u user -ppassword
mysql>show master status;
It show me the wrong logfile name and position, in fact it show
always the same log file name, not the file that the slave is taking
to replicate("show slave status" on slave), regardeless the mysql
instance port.
And "show slave status" on master dont show me the Replicate_Do_DB/
Replicate_Do_Table rules applied in each instance, but everything is
working fine.
How can i see the right logfile name/position(show master status)
for each mysql instance on the replication distribution master?
Is it a mysql client issue?
The log distribution server is running MySql 5.5.50
Thank you in advance.
--
Jose Julian Buda
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
Thank you, it was a mysql client issue.
The client always connects through default sockets unless you specify a
host, so this commands work fine:
mysql -h 127.0.0.1 -P instance_1_port -u user -ppassword
mysql -h 127.0.0.1 -P instance_2_port -u user -ppassword
...
...
and so for all instances port,
it show me the right log filename and its position and every
Replicate_Do* settings, for each mysql instances.
Thank you
Jose Julian Buda
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Show master status - "mysqld_multi" - distribution master
Hi, i have a server acting as a replication distribution master(taking
from the real master), with several mysql instances (instance 1,instance
2...) with mysqld_multi script, and many replication filters just to
avoid using bandwidth to the remote datacenters with useless data.
It's working fine, but when i need to see the log pos of an instance to
sync the replication or anything else(for example a delay in the links):
mysql -P instance_1_port -u user -ppassword
mysql>show master status;
It show me the wrong logfile name and position, in fact it show always
the same log file name, not the file that the slave is taking to
replicate("show slave status" on slave), regardeless the mysql instance
port.
And "show slave status" on master dont show me the Replicate_Do_DB/
Replicate_Do_Table rules applied in each instance, but everything is
working fine.
How can i see the right logfile name/position(show master status) for
each mysql instance on the replication distribution master?
Is it a mysql client issue?
The log distribution server is running MySql 5.5.50
Thank you in advance.
--
Jose Julian Buda
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 30/08/14 09:39, william drescher wrote:
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on
Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 107 | | |
+--+--+--+--+
1 row in set (0.00 sec)
Slave
* 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 107
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query.
Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29727610
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on
query. Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
1 transaction entered:
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 1837 | | |
+--+--+--+--+
1 row in set (0.00 sec)
SLave
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 1837
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query.
Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29729340
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on
query. Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
Well , it seems that you have a 'Duplicate entry ' issue on slave, so
the Slave_SQL_Running threads is locked :) , there's something wrong
here with the initial slave status, maybe you have to rebuild the slave
from scratch(backup or a dump from master), and be sure that you don't
change anything on slave( phpmyadmin? )
Bye
Julian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem -solved
On 30/08/14 12:56, william drescher wrote: Thanks for pointing out the importance of the last error I resynced the slave to the master, reset the master position, and restarted the slave. Now all works fine and I am much better equipped next time to debug the loss of the link. When is the Last Error data deleted from the show slave data ? --bill Just after you resync the slave, last error should be empty. Bye Julian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How can avoid 2 selects and 2 while?
table_1:
product_id features .
1aaa
2bbb
3ccc
table_2 :
product_idname sizeprice
1 111 111
1 12 2 221
1 133 331
2 21 1 112
2 22 2222
3 31 1113
3 32 2223
3 33 3333
What i want is the next report for example the size =2
.
Report of products with size =2
Product features : aaa
Name 12Price 221
Product features : bbb
Name 22 Price 222
Product features : ccc
Name 32 Price 223
...
basically what i do now with php is :
...
$cliente=mysql_query(select * from table_1);
while($row = mysql_fetch_array($cliente))
{
printf(\nProduct features : %s\n,$row[features]);
$cliente2=mysql_query(select name,price from table_2 where
product_id='$row[product_id]' and size=2);
while($row2 = mysql_fetch_array($cliente2))
{
. print(Name %s --- Price %s\n,$row2[name],$row2[price],);
}
}
.
How can i do this without 2 select and whitout 2 while and the features be
printed just one time?
Thank you in advance
Jose Julian Buda
Re: How can avoid 2 selects and 2 while? SOLVED
- Original Message -
From: Brent Baisley brentt...@gmail.com
To: Jose Julian Buda jb...@noticiasargentinas.com
Sent: Thursday, February 12, 2009 1:21 PM
Subject: Re: How can avoid 2 selects and 2 while?
On Thu, Feb 12, 2009 at 10:43 AM, Jose Julian Buda
jb...@noticiasargentinas.com wrote:
table_1:
product_id features .
1aaa
2bbb
3ccc
table_2 :
product_idname sizeprice
1 111 111
1 12 2 221
1 133 331
2 21 1 112
2 22 2222
3 31 1113
3 32 2223
3 33 3333
What i want is the next report for example the size =2
.
Report of products with size =2
Product features : aaa
Name 12Price 221
Product features : bbb
Name 22 Price 222
Product features : ccc
Name 32 Price 223
...
basically what i do now with php is :
...
$cliente=mysql_query(select * from table_1);
while($row = mysql_fetch_array($cliente))
{
printf(\nProduct features : %s\n,$row[features]);
$cliente2=mysql_query(select name,price from table_2 where
product_id='$row[product_id]' and size=2);
while($row2 = mysql_fetch_array($cliente2))
{
. print(Name %s --- Price %s\n,$row2[name],$row2[price],);
}
}
.
How can i do this without 2 select and whitout 2 while and the features
be printed just one time?
Thank you in advance
Jose Julian Buda
Whenever you have a query in a loop, you know you are doing something
wrong. Your query will pull repeated values, but there is no way
around that. You'll then check if the repeated value changes in PHP.
$cliente = mysql_query(SELECT table_1.*, table_2.name, table_2.price
FROM table_2 JOIN table_1 ON table_2.product_id=table_1.product_id
WHERE table_2.size=2 ORDER BY table_1.product_id);
$prev_prod_id = -1;
while($row = mysql_fetch_array($cliente))
{
if ( $row['product_id']!=$prev_prod_id ) {
printf(\nProduct features : %s\n,$row[features]);
$prev_prod_id = $row['product_id'];
}
print(Name %s --- Price %s\n,$row[name],$row[price],);
}
Note I have not tested it, but you get the idea.
Brent Baisley
Brent , it work fine with just 1 select query
Thank you very much
Jose Julian Buda
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql Replication out of sync
maybe if you put on the slave server : mysql STOP SLAVE; mysql SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql START SLAVE; then just to check take a look at this lines : mysql show slave status\G; ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... it will restart the replication, but you have to check then why this happened, just in case... - Original Message - From: Shain Miley To: mysql@lists.mysql.com Sent: Friday, January 09, 2009 3:17 PM Subject: Mysql Replication out of sync Hello all, I noticed that my two Mysql slave servers were running out of sync this morning. After looking into it...it appears that yesterday when I tried to change the username of a user on the master server...it caused some issues on the slaves. Here is what I was able to pull from the slave error logs: 090108 16:46:05 [ERROR] Slave SQL: Error 'Operation CREATE USER failed for 'root'@'127.0.0.1'' on query. Default database: 'mysql'. Query: 'CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*3313E27C7AD7F792A60D5D651029375E60'', Error_code: 1396 090108 16:46:05 [Warning] Slave: Operation CREATE USER failed for 'root'@'127.0.0.1' Error_code: 1396 090108 16:46:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'master-bin.08' position 970652826 Can anyone tell me if/how I can remove that statement from the log file...so I can restart the slave? Is there a better way to fix this? Thanks, Shain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jb...@noticiasargentinas.com __ Información de NOD32, revisión 3749 (20090107) __ Este mensaje ha sido analizado con NOD32 antivirus system http://www.nod32.com
