Mysql replication with dbname

I have setup replication between 2 machines.  I am replicating database "test" from machine1 to machine2.

To test here is what I did

$ mysql -utest -ptest
mysql> use test;
mysql> insert into users values('myfirstname','mylastname');

The replication from machine1 to machine2 worked with the previous statement.  So my question is why doesn't the following steps replicate the data to machine2?

$ mysql -utest -ptest
mysql> use otherdatabasetest;
mysql> insert into test.users values('myfirstname','mylastname');

obviously I am selecting a different database, however I am stil inserting data into "test" database.

Thanks,

jmsloan
LVL 3
jmsloanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshSenior Principal Technical Support EngineerCommented:
You can "use" or select the DB using "dot notation" as you did in below statement

insert into test.users values('myfirstname','mylastname');
UmeshSenior Principal Technical Support EngineerCommented:
Just wanted to say both are same...

mysql> use test;
mysql> insert into users values('myfirstname','mylastname');

and

mysql> insert into test.users values('myfirstname','mylastname');
jmsloanAuthor Commented:
Why isn't it working then?

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

UmeshSenior Principal Technical Support EngineerCommented:
Pls get me both my.cnf (master, slave)

can you post the output of below commands

on slave
=========

show slave status;
jmsloanAuthor Commented:
MASTER my.cnf
[mysqld]
log-bin=mysql-bin
binlog-do-db=test
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
datadir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock
port=3306

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
 
[mysql.server]
user=mysql
basedir=/usr/local
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid




SLAVE my.cnf
[mysqld]
server-id=2
master-host=xxx.xxx.xxx.xxx
master-user=testuser
master-password=testpassword
master-connect-retry=60
replicate-do-db=test
datadir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
 
[mysql.server]
user=mysql
basedir=/usr/local
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid





SLAVE STATUS

| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB   | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | xxx.xxx.xxx.xxx | testuser      |        3306 |            60 | mysql-bin.000001 |               36556 | mysqld-relay-bin.000009 |           235 | mysql-bin.000001      | Yes              | Yes               | test,test |                     |                    |                        |                         |                             |          0 |            |            0 |               36556 |             235 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
UmeshSenior Principal Technical Support EngineerCommented:
Sorry.. pls send the output of .. I'm unable to read output of show slave status;

show slave status\G
jmsloanAuthor Commented:

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: xxx.xxx.xxx.xxx
                Master_User: testuser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 36556
             Relay_Log_File: mysqld-relay-bin.000009
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: test,test
        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: 36556
            Relay_Log_Space: 235
            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
1 row in set (0.00 sec)

Open in new window

UmeshSenior Principal Technical Support EngineerCommented:
Here is the contents from MySQL manual.. ( http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html )

 Tell the server to restrict binary logging to updates for which the default database is db_name (that is, the database selected by USE). All other databases that are not explicitly mentioned are ignored. If you use this option, you should ensure that you do updates only in the default database.

There is an exception to this for CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements. The server uses the database named in the statement (not the default database) to decide whether it should log the statement.

An example of what does not work as you might expect: If the server is started with binlog-do-db=sales, and you run USE prices; UPDATE sales.january SET amount=amount+1000;, this statement is not written into the binary log.

So in your case....

MASTER my.cnf
================

binlog-do-db=test

Here comes your INSERT STUFF - which is not being replicated..

mysql> use otherdatabasetest;
mysql> insert into test.users values('myfirstname','mylastname');

If we believe the MySQL manual then your insert will not be replicated...  i.e below statement is not written into the binary log.

 insert into test.users values('myfirstname','mylastname');

In that case, your replication i/o thread is unable to read it from master's binary log file.. hence it's not getting replicated...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmsloanAuthor Commented:
So replication only works if you're in the default database?  No way around it?
UmeshSenior Principal Technical Support EngineerCommented:
That is what Manual says...  

UmeshSenior Principal Technical Support EngineerCommented:
There might be some chances if we can do some testing... this weekend I'll give a try and let you know..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.