|
Hi,
Could you please specify how exactly it does not get loaded – i.e. attach your cnf file and paste the error that you are getting (or, if it's not an error but the value is just ignored, paste the output of SHOW SLAVE '<main_connection>' STATUS;.
This works just fine for me (no quoting at all):
cat 1.cnf
[mysqld]
|
main_connection.replicate_do_db=main_database
|
show slave 'main_connection' status \G
|
|
*************************** 1. row ***************************
|
Slave_IO_State:
|
Master_Host: 127.0.0.1
|
Master_User: root
|
Master_Port: 3308
|
Connect_Retry: 60
|
Master_Log_File:
|
Read_Master_Log_Pos: 4
|
Relay_Log_File: wheezy-64-relay-bin-main_connection.000001
|
Relay_Log_Pos: 4
|
Relay_Master_Log_File:
|
Slave_IO_Running: No
|
Slave_SQL_Running: No
|
Replicate_Do_DB: main_database
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table:
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 0
|
Last_Error:
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 0
|
Relay_Log_Space: 1316
|
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: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 0
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: No
|
Gtid_IO_Pos:
|
|
|
It just appears to be ignored.
[mysqld1]
|
signup.replicate_wild_do_table=signup.%
|
signup.replicate_wild_do_table=mysql.%
|
signup_archive.replicate_wild_do_table=signup_archive.%
|
signup_archive.replicate_wild_do_table=mysql.%
|
|
MariaDB [(none)]> show slave 'signup_archive' status\G
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: hostname
|
Master_User: slaveuser
|
Master_Port: port
|
Connect_Retry: 60
|
Master_Log_File: mysql-bin.016021
|
Read_Master_Log_Pos: 32938
|
Relay_Log_File: relay-bin-signup_archive.001654
|
Relay_Log_Pos: 33225
|
Relay_Master_Log_File: mysql-bin.016021
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: Yes
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table:
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 0
|
Last_Error:
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 32938
|
Relay_Log_Space: 33574
|
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: 0
|
Master_SSL_Verify_Server_Cert: No
|
Last_IO_Errno: 0
|
Last_IO_Error:
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 3632211757
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: No
|
Gtid_IO_Pos:
|
1 row in set (0.00 sec)
|
|
MariaDB [(none)]> show slave 'signup' status\G
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: hostname
|
Master_User: users
|
Master_Port: port
|
Connect_Retry: 60
|
Master_Log_File: mysql-bin.016006
|
Read_Master_Log_Pos: 765715
|
Relay_Log_File: relay-bin-signup.001765
|
Relay_Log_Pos: 246889
|
Relay_Master_Log_File: mysql-bin.016006
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: Yes
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table: signup.%,mysql.%,signup.%,mysql.%
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 0
|
Last_Error:
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 765715
|
Relay_Log_Space: 247187
|
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: 0
|
Master_SSL_Verify_Server_Cert: No
|
Last_IO_Errno: 0
|
Last_IO_Error:
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 3632211740
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: No
|
Gtid_IO_Pos:
|
1 row in set (0.00 sec)
|
|
MariaDB [(none)]> select version();
|
+------------------------------+
|
| version() |
|
+------------------------------+
|
| 10.0.10-MariaDB-1~wheezy-log |
|
+------------------------------+
|
1 row in set (0.00 sec)
|
|
|
Still works for me, even with 'wild_do_table' and the wildcard (example below).
You output raises two questions.
First, your cnf section is named 'mysqld1'. Are you sure you run your server with the correct 'prefix' option?
Second, your output for 'signup' shows four wildcard values. At the very least, it means the config is additionally read from somewhere else. But add to that the ignored values for signup_archive and strange config section name, it might also mean that the config is only read from somewhere else.
For starters, could you please put in this very section someting simple and non-default, e.g. lock_wait_timeout=98, and check the value after you restart the mysqld?
[mysqld]
|
signup.replicate_wild_do_table=signup.%
|
signup.replicate_wild_do_table=mysql.%
|
signup_archive.replicate_wild_do_table=signup_archive.%
|
signup_archive.replicate_wild_do_table=mysql.%
|
************************** 1. row ***************************
|
Connection_name: signup
|
Slave_SQL_State:
|
Slave_IO_State:
|
Master_Host: 127.0.0.1
|
Master_User: root
|
Master_Port: 3306
|
Connect_Retry: 60
|
Master_Log_File:
|
Read_Master_Log_Pos: 4
|
Relay_Log_File: wheezy-64-relay-bin-signup.000001
|
Relay_Log_Pos: 4
|
Relay_Master_Log_File:
|
Slave_IO_Running: No
|
Slave_SQL_Running: No
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table: signup.%,mysql.%
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 0
|
Last_Error:
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 0
|
Relay_Log_Space: 515
|
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: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 0
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: No
|
Gtid_IO_Pos:
|
Retried_transactions: 0
|
Max_relay_log_size: 1073741824
|
Executed_log_entries: 0
|
Slave_received_heartbeats: 0
|
Slave_heartbeat_period: 1800.000
|
Gtid_Slave_Pos:
|
*************************** 2. row ***************************
|
Connection_name: signup_archive
|
Slave_SQL_State:
|
Slave_IO_State:
|
Master_Host: 127.0.0.1
|
Master_User: root
|
Master_Port: 8306
|
Connect_Retry: 60
|
Master_Log_File:
|
Read_Master_Log_Pos: 4
|
Relay_Log_File: wheezy-64-relay-bin-signup_archive.000001
|
Relay_Log_Pos: 4
|
Relay_Master_Log_File:
|
Slave_IO_Running: No
|
Slave_SQL_Running: No
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table: signup_archive.%,mysql.%
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 0
|
Last_Error:
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 0
|
Relay_Log_Space: 515
|
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: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 0
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: No
|
Gtid_IO_Pos:
|
Retried_transactions: 0
|
Max_relay_log_size: 1073741824
|
Executed_log_entries: 0
|
Slave_received_heartbeats: 0
|
Slave_heartbeat_period: 1800.000
|
Gtid_Slave_Pos:
|
2 rows in set (0.01 sec)
|
|
|
I am using mysqld_multi where you can have N instances managed by it all named mysqld1, mysqld2, mysqld3, etc, etc. (Ref: https://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html)
The duplication of replication filters is something I've taken as a fact of life with MySQL. I'm having a difficult time finding anything documented but it's always done that for as far back as I can remember. I don't think it's an issue, it's also on a functioning slave.
It is reading other values just fine:
MariaDB [(none)]> show global variables like 'lock_wait_timeout';
|
+-------------------+-------+
|
| Variable_name | Value |
|
+-------------------+-------+
|
| lock_wait_timeout | 98 |
|
+-------------------+-------+
|
1 row in set (0.00 sec)
|
|
MariaDB [(none)]> show global variables like 'slave_parallel_threads';
|
+------------------------+-------+
|
| Variable_name | Value |
|
+------------------------+-------+
|
| slave_parallel_threads | 16 |
|
+------------------------+-------+
|
1 row in set (0.00 sec)
|
|
|
Could you please attach all your config files which contain either this-instance-specific or common sections, and the complete output of SHOW VARIABLES and SHOW ALL SLAVES STATUS \G ? (Please remove/obfuscate the passwords and any private info).
And the server command line as returned by ps -ef | grep mysqld or alike.
Thanks.
|
|
Ah, looking at ps, I can see the mysqld portion of it looks like this:
--signup-archive.replicate-wild-do-table=mysql.%
|
You can see the _ has been replaced by a hyphen. The command is listed correctly on the mysqld_safe part of the process:
--signup_archive.replicate-wild-do-table=mysql.%
|
I think that is fairly telling.
|
|
Thanks.
So, the problem is the whole chain.
mysqld_multi converts the pseudo-cnf into a command line and feeds it to mysqld_safe.
mysqld_safe takes the options from the command line and "normalizes" them by replacing _ with -.
mysqld reads the options and acts accordingly.
If it wasn't mysqld_multi's pseudo config, but a normal config, standard server's section, it wouldn't have happened because the options would remain in the cnf file where mysqld would have read them from as they are.
Or, if mysqd_multi was calling mysqld directly rather than via mysqld_safe, it would have worked because mysqld_multi doesn't modify the options, and mysqld is able to process them.
You can use either of that as a workaround.
|
|
It does work correctly when using mysql (not mysqld_multi), so I think the problem is there.
|
|
serg,
This is something for you to decide, I'm not sure what would be a better (or less bad) fix.
The logic which mixes up slave identificators and option names doesn't look great, but since we've let it reach GA, I guess it will have to stay (I really don't remember how it made it there).
The logic which replaces '_' with '-' in mysqld_safe is intentional, it was introduced by the following revision:
revision-id: guilhem@mysql.com-20091118203617-3jfudef971r7bf2y
|
committer: Guilhem Bichot <guilhem@mysql.com>
|
branch nick: mysql-next-mr-bugfixing
|
timestamp: Wed 2009-11-18 21:36:17 +0100
|
message:
|
Backport of the fix for BUG#40368 "mysqld_safe not honouring underscore same as dash on server options" from 6.0
|
(revision-id:guilhem@mysql.com-20090505113602-l12kxupeatve18dh).
|
Such bug led "mysqld_safe --core_file_size=#" to not work because mysqld_safe wouldn't recognize
|
that "ulimit -c" is needed; only --core-file-size=# worked. Same for --open_files_limit and other
|
options with _ where mysqld_safe needs to do something more than passing to mysqld.
|
Original fix by Erik Ljungstrom erik at ibiblio dot org ; slightly modified here.
|
Tested on all internally accessible Unix.
|
So, while technically it's primitive to remove the conversion, probably we'll need to re-do core_file_size and the others. Since the "other options" are not listed, we might have to do that for every option which has special logic in mysqld_safe, and then it might make sense to do it a different way, e.g. to make a local copy of options for mysqld_safe's tampering, but keep the original ones to pass to mysqld, but it makes the change rather big. Or maybe you'll come up with a better idea, I don't like any of those.
|