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?
 
UmeshMySQL Principle 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...

0
 
UmeshMySQL Principle 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');
0
 
UmeshMySQL Principle 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');
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jmsloanAuthor Commented:
Why isn't it working then?

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Pls get me both my.cnf (master, slave)

can you post the output of below commands

on slave
=========

show slave status;
0
 
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 |
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Sorry.. pls send the output of .. I'm unable to read output of show slave status;

show slave status\G
0
 
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

0
 
jmsloanAuthor Commented:
So replication only works if you're in the default database?  No way around it?
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
That is what Manual says...  

0
 
UmeshMySQL Principle 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..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.